NO IMAGE

第一課:瞭解SQL

  1. 資料庫:儲存有組織的資料的容器(通常是一個檔案或一組檔案)。
  2. 注意誤用混淆:資料庫軟體被稱為DBMS,資料庫是通過DBMS建立和操縱的容器
  3. 模式:關於資料庫和表的佈局及特性的資訊。
  4. 主鍵:一列或一組列,其值能夠唯一標識表中的每一行。,保證一組列是唯一的 
  5. 行,列,表,資料型別基本知識。

注:學習原始資料—基於mysql5.0

-- ----------------------
-- Create Customers table
-- ----------------------
CREATE TABLE Customers
(
cust_id      char(10)  NOT NULL ,
cust_name    char(50)  NOT NULL ,
cust_address char(50)  NULL ,
cust_city    char(50)  NULL ,
cust_state   char(5)   NULL ,
cust_zip     char(10)  NULL ,
cust_country char(50)  NULL ,
cust_contact char(50)  NULL ,
cust_email   char(255) NULL 
);
-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
order_num  int          NOT NULL ,
order_item int          NOT NULL ,
prod_id    char(10)     NOT NULL ,
quantity   int          NOT NULL ,
item_price decimal(8,2) NOT NULL 
);
-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
order_num  int      NOT NULL ,
order_date datetime NOT NULL ,
cust_id    char(10) NOT NULL 
);
-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
prod_id    char(10)      NOT NULL ,
vend_id    char(10)      NOT NULL ,
prod_name  char(255)     NOT NULL ,
prod_price decimal(8,2)  NOT NULL ,
prod_desc  text          NULL 
);
-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
vend_id      char(10) NOT NULL ,
vend_name    char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city    char(50) NULL ,
vend_state   char(5)  NULL ,
vend_zip     char(10) NULL ,
vend_country char(50) NULL 
);
-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);
-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', '[email protected]');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', '[email protected]');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', '[email protected]');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');
-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');
-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2012-02-08', '1000000001');
-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

第二課:檢索資料—select 檢索一個或者多個列

  1. 多條SQL語句必須以“ ;”分隔。
  2. SQL語句不區分大小寫,SELECT和select是相通的,不過表名,列名和值可能有所不同(依賴於具體點DBMS及其如何配置)。
  3. 處理SQL語句時,其中所有 空格都被忽略(將SQL語句分成多行有利於閱讀和除錯)。
  4. 檢索多個列,列名之間必須以“ ,”分隔,但是最後一個列不用加,第一個檢索的行是0行,limit 4 offset 3—簡化版:limit 3,4
  5. 檢索所有列使用(*)萬用字元,用於檢索名字未知的列或者需要知道表中的每一列,效能低,不同的資料庫sql語言有差異!!
  6. 檢索不同的值使用DISTINCT關鍵字 ,放在列名前面,其不僅作用於緊跟其後的那一列。 
    例:SELECT DISTINCT vend_id,prod_price FROM Products; 除非兩行相同,否則所有的列都會被檢索出來。
  7. 使用註釋    –之後的文字都是註釋
    #一行的開始使用(不常用)
    /*  */多行的註釋,還有–形式註釋
  8. sql關鍵字彙總:
  9. select –想選擇什麼?從哪裡選擇
  10. 限制檢索內容:sql server :  top —select top 5 prod_name from Products;  limit—mysql;DB2:fetch first 5 rows only;
select prod_name from Products limit 5;
--------------------- 
| prod_name           |
--------------------- 
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
--------------------- 
5 rows in set (0.00 sec)
從第五行開始的5行
select prod_name from Products limit 5 offset 5;
-------------------- 
| prod_name          |
-------------------- 
| 18 inch teddy bear |
| Raggedy Ann        |
| King doll          |
| Queen doll         |
-------------------- 
4 rows in set (0.00 sec)

附錄 關鍵字

ADDEXITPRIMARY
ALLFETCHPRINT
ALTERFILEPRIVILEGES
ANDFILLFACTORPROC
ANYFLOPPYPROCEDURE
ASFORPROCESSEXIT
ASCFOREIGNPUBLIC
AUTHORIZATIONFREETEXTRAISERROR
AVGFREETEXTTABLEREAD
BACKUPFROMREADTEXT
BEGINFULLRECONFIGURE
BETWEENGOTOREFERENCES
BREAKGRANTREPEATABLE
BROWSEGROUPREPLICATION
BULKHAVINGRESTORE
BYHOLDLOCKRESTRICT
CASCADEIDENTITYRETURN
CASEIDENTITY_INSERTREVOKE
CHECKIDENTITYCOLRIGHT
CHECKPOINTIFROLLBACK
CLOSEINROWCOUNT
CLUSTEREDINDEXROWGUIDCOL
COALESCEINNERRULE
COLUMNINSERTSAVE
COMMITINTERSECTSCHEMA
COMMITTEDINTOSELECT
COMPUTEISSERIALIZABLE
CONFIRMISOLATIONSESSION_USER
CONSTRAINTJOINSET
CONTAINSKEYSETUSER
CONTAINSTABLEKILLSHUTDOWN
CONTINUELEFTSOME
CONTROLROWLEVELSTATISTICS
CONVERTLIKESUM
COUNTLINENOSYSTEM_USER
CREATELOADTABLE
CROSSMAXTAPE
CURRENTMINTEMP
CURRENT_DATEMIRROREXITTEMPORARY
CURRENT_TIMENATIONALTEXTSIZE
CURRENT_TIMESTAMPNOCHECKTHEN
CURRENT_USERNONCLUSTEREDTO
CURSORNOTTOP
DATABASENULLTRAN
DBCCNULLIFTRANSACTION
DEALLOCATEOFTRIGGER
DECLAREOFFTRUNCATE
DEFAULTOFFSETSTSEQUAL
DELETEONUNCOMMITTED
DENYONCEUNION
DESCONLYUNIQUE
DISKOPENUPDATE
DISTINCTOPENDATASOURCEUPDATETEXT
DISTRIBUTEDOPENQUERYUSE
DOUBLEOPENROWSETUSER
DROPOPTIONVALUES
DUMMYORVARYING
DUMPORDERVIEW
ELSEOUTERWAITFOR
ENDOVERWHEN
ERRLVLPERCENTWHERE
ERROREXITPERMWHILE
ESCAPEPERMANENTWITH
EXCEPTPIPEWORK
EXECPLANWRITETEXT
EXECUTEPRECISION 
EXISTSPREPARE


第三課:排序檢索資料—-order by

  1. 子句:SQL語句由子句構成,有些子句是必須的,有些則是可選的。一個子句通常是由一個關鍵字加上所提供的資料組成。
  2. ORDER BY 可以取一個或多個列的名字,據此對輸出進行排序。如果他不是最後的子句,將會出現錯誤資訊,並且可以使用非檢索的列排序。
  3. 按多個列進行排序: ..ORDER BY xx,yy;  先按xx,在xx都相同時再按yy,若xx中都是唯一的則不會按yy排列
         ..ORDER BY 2,3;    指定的是選擇列的相對位置而不是列名
  4. 指定排序方向:DESC(降序)關鍵字只應用到直接位於其前面的列名,在多個列上進行降序排序,必須對每一列指定DESC關鍵字,ASC(升序)預設的無用。
  5. A與a預設相同,無法簡單使用ORDER BY改變,注意資料庫命名方式。
 select prod_name from Products order by prod_name;
--------------------- 
| prod_name           |
--------------------- 
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
--------------------- 
9 rows in set (0.00 sec)

注:1.orderby  子句應該放在最後的位置

        2.非檢索列排序同樣合法
        3.可以多個列排序,第一列先排序,預設遞增(遞減為關鍵字desc,加在order by 的後面),相同再第二列排序
 select prod_id,prod_price,prod_name from Products order by prod_price,prod_name;
--------- ------------ --------------------- 
| prod_id | prod_price | prod_name           |
--------- ------------ --------------------- 
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
--------- ------------ --------------------- 
9 rows in set (0.00 sec)

          4.可以按照列的對應位置排序!上面等價於如下:(當然按非選擇列排序時候不可以這樣)

select prod_id,prod_price,prod_name from Products order by 2,3;
--------- ------------ --------------------- 
| prod_id | prod_price | prod_name           |
--------- ------------ --------------------- 
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
--------- ------------ --------------------- 
9 rows in set (0.00 sec)

             5.desc關鍵字

 select prod_id,prod_price,prod_name from Products order by prod_price desc,prod_name;
--------- ------------ --------------------- 
| prod_id | prod_price | prod_name           |
--------- ------------ --------------------- 
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
--------- ------------ --------------------- 
9 rows in set (0.00 sec)


第四課:過濾資料—-where

  1. 使用WHERE子句:資料根據WHERE子句中指定的搜尋條件進行過濾,接在FROM子句之後給出。
  2. 資料最好不要再應用層過率,客戶端應用處理資料,會使伺服器發動多餘資料,造成頻寬的浪費。
  3. WHERE子句操作符  
    操作符說明操作符說明
    =等於>大於
    <>不等於>=大於等於
    !=不等於!>不大於
    BETWEEN在指定的兩個值之間IS NULL為NULL值
    !<不小於<

    小於

     

  4. 關於引號:字串用單引號限制,用來與數值列比較不需要用引號,order by 位於where之後;
  5. between例: SELECT prod_name,prod_price
    FORM Products
    WHERE BETWEEN 5 AND 10
  6. NULL:無值。它與欄位包含0,空字串,或僅包含空格不同;
  7. 確定值是否為NULL,不能簡單的檢查是否=NULL,應該用特殊的IS NULL
select prod_id,prod_name,prod_price from Products where prod_price=3.49;
--------- --------------------- ------------ 
| prod_id | prod_name           | prod_price |
--------- --------------------- ------------ 
| BNBG01  | Fish bean bag toy   |       3.49 |
| BNBG02  | Bird bean bag toy   |       3.49 |
| BNBG03  | Rabbit bean bag toy |       3.49 |
--------- --------------------- ------------ 
3 rows in set (0.01 sec)

select prod_id,prod_name,prod_price from Products where prod_price<10;
--------- --------------------- ------------ 
| prod_id | prod_name           | prod_price |
--------- --------------------- ------------ 
| BNBG01  | Fish bean bag toy   |       3.49 |
| BNBG02  | Bird bean bag toy   |       3.49 |
| BNBG03  | Rabbit bean bag toy |       3.49 |
| BR01    | 8 inch teddy bear   |       5.99 |
| BR02    | 12 inch teddy bear  |       8.99 |
| RGAN01  | Raggedy Ann         |       4.99 |
| RYL01   | King doll           |       9.49 |
| RYL02   | Queen doll          |       9.49 |
--------- --------------------- ------------ 
8 rows in set (0.00 sec)


第五課:高階資料過濾—NOT–IN 邏輯操作(and or )

  1. 操作符:用來連線過改變WHERE子句中的子句的官架子,也叫邏輯操作符。
  2. AND:檢索滿足條件的所有指定的行
  3. OR:  與AND相反,許多DBMS在第一個條件得到滿足的情況下就不在計算第二個條件了(第一個滿足時,不管是否滿足,相應的行都將被檢索出來)
  4. AND再求值優先順序大於OR,可以使用()改變結合順序避免出錯。
  5. IN WHERE子句中用來指定要匹配值的清單的關鍵字,功能與OR相當。
    例:WHERE vend_id  IN  (‘DLL01′,’BRS01′) 等同於WHERE vend_id=’DLL01’ OR vend_id=‘BRS01’
    最大的優點是可以包含其他SELECT語句,能夠更動態地建立WHERE子句。
  6. NOT:WHERE子句中用來否定其後條件關鍵字。
    例:WHERE NOT vend_id=’DLL01′ 等同於 WHERE vend_id<>’DLL01′
    優勢是與IN操作符聯合使用時,NOT可以非常簡單的找出與條件列表不匹配的行。

第六課:萬用字元進行過濾—like

       1.%,代表,Fish%,F%y,記住加上‘’;
select prod_id,prod_name from Products where prod_name like 'F%';
--------- ------------------- 
| prod_id | prod_name         |
--------- ------------------- 
| BNBG01  | Fish bean bag toy |
--------- ------------------- 
1 row in set (0.01 sec)
        2.——匹配單個字元,[]匹配其中單個字元,‘[jm]%’
 select prod_id,prod_name from Products where prod_name like '_ish%';
--------- ------------------- 
| prod_id | prod_name         |
--------- ------------------- 
| BNBG01  | Fish bean bag toy |
--------- ------------------- 
1 row in set (0.01 sec)

7.建立計算欄位–基於select語句建立

  1.兩個符號, 和||

8.函式處理資料

  left(),length(),lower(),ltrim()去掉左面的字串,right(),      ptrim(),soundex()–查詢發音相似的值,upper()


9.聚集函式

10.分組和排序

11.聯結表–left—right–out

12.聯合查詢–union

13.增刪改查insert–select—update—
14.檢視—view–抽取部分表
15.儲存過程。—一組sql語句–簡單,安全,高效能,執行execute
16.遊標
open cursor custcursor開啟遊標

MySQL中,建立儲存過程的基本形式如下:

  1. CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
    [characteristic ...] routine_body 

其中,sp_name引數是儲存過程的名稱;proc_parameter表示儲存過程的引數列表; characteristic引數指定儲存過程的特性;routine_body引數是SQL程式碼的內容,可以用BEGIN…END來標誌SQL程式碼的開始和結束。

proc_parameter中的每個引數由3部分組成。這3部分分別是輸入輸出型別、引數名稱和引數型別。其形式如下:

  1. [ IN | OUT | INOUT ] param_name type 


其中,IN表示輸入引數;OUT表示輸出引數; INOUT表示既可以是輸入,也可以是輸出; param_name引數是儲存過程的引數名稱;type引數指定儲存過程的引數型別,該型別可以是MySQL資料庫的任意資料型別。

characteristic引數有多個取值。其取值說明如下:

LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成,這也是資料庫系統預設的語言。

[NOT] DETERMINISTIC:指明儲存過程的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行儲存過程時,相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的,相同的輸入可能得到不同的輸出。預設情況下,結果是非確定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程式使用SQL語句的限制。CONTAINS SQL表示子程式包含SQL語句,但不包含讀或寫資料的語句;NO SQL表示子程式中不包含SQL語句;READS SQL DATA表示子程式中包含讀資料的語句;MODIFIES SQL DATA表示子程式中包含寫資料的語句。預設情況下,系統會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }:指明誰有許可權來執行。DEFINER表示只有定義者自己才能夠執行;INVOKER表示呼叫者可以執行。預設情況下,系統指定的許可權是DEFINER。

COMMENT ‘string’:註釋資訊。

技巧:建立儲存過程時,系統預設指定CONTAINS SQL,表示儲存過程中使用了SQL語句。但是,如果儲存過程中沒有使用SQL語句,最好設定為NO SQL。而且,儲存過程中最好在COMMENT部分對儲存過程進行簡單的註釋,以便以後在閱讀儲存過程的程式碼時更加方便。

【示例14-1】 下面建立一個名為num_from_employee的儲存過程。程式碼如下:

  1. CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  
    READS SQL DATA  
    BEGIN  
    SELECT  COUNT(*)  INTO  count_num  
    FROM  employee  
    WHERE  d_id=emp_id ;  
    END 

上述程式碼中,儲存過程名稱為num_from_employee;輸入變數為emp_id;輸出變數為count_num。SELECT語句從employee表查詢d_id值等於emp_id的記錄,並用COUNT(*)計算d_id值相同的記錄的條數,最後將計算結果存入count_num中。程式碼的執行結果如下:

  1. mysql> DELIMITER &&  
    mysql> CREATE  PROCEDURE  num_from_employee
    (IN emp_id INT, OUT count_num INT )  
    -> READS SQL DATA  
    -> BEGIN  
    -> SELECT  COUNT(*)  INTO  count_num  
    -> FROM  employee  
    -> WHERE  d_id=emp_id ;  
    -> END &&  
    Query OK, 0 rows affected (0.09 sec)  
    mysql> DELIMITER ; 

       程式碼執行完畢後,沒有報出任何出錯資訊就表示儲存函式已經建立成功。以後就可以呼叫這個儲存過程,資料庫中會執行儲存過程中的SQL語句。

說明:MySQL中預設的語句結束符為分號(;)。儲存過程中的SQL語句需要分號來    結束。為了避免衝突,首先用”DELIMITER &&”將MySQL的結束符設定為&&。最後再用”DELIMITER ;”來將結束符恢復成分號。這與建立觸發器時是一樣的。

14.1.2 建立儲存函式

在MySQL中,建立儲存函式的基本形式如下:

  1. CREATE FUNCTION sp_name ([func_parameter[,...]])  
    RETURNS type  
    [characteristic ...] routine_body 

其中,sp_name引數是儲存函式的名稱;func_parameter表示儲存函式的引數列表;RETURNS type指定返回值的型別;characteristic引數指定儲存函式的特性,該引數的取值與儲存過程中的取值是一樣的,請讀者參照14.1.1小節的內容;routine_body引數是SQL程式碼的內容,可以用BEGIN…END來標誌SQL程式碼的開始和結束。

func_parameter可以由多個引數組成,其中每個引數由引數名稱和引數型別組成,其形式如下:

  1. param_name type 

其中,param_name引數是儲存函式的引數名稱;type引數指定儲存函式的引數型別,該型別可以是MySQL資料庫的任意資料型別。

【示例14-2】 下面建立一個名為name_from_employee的儲存函式。程式碼如下:

  1. CREATE  FUNCTION  name_from_employee (emp_id INT )  
    RETURNS VARCHAR(20)  
    BEGIN  
    RETURN  (SELECT  name  
    FROM  employee  
    WHERE  num=emp_id );  
    END 

上述程式碼中,儲存函式的名稱為name_from_employee;該函式的引數為emp_id;返回值是VARCHAR型別。SELECT語句從employee表查詢num值等於emp_id的記錄,並將該記錄的name欄位的值返回。程式碼的執行結果如下:

  1. mysql> DELIMITER &&  
    mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
    -> RETURNS VARCHAR(20)  
    -> BEGIN  
    -> RETURN  (SELECT  name  
    -> FROM  employee  
    -> WHERE  num=emp_id );  
    -> END&&  
    Query OK, 0 rows affected (0.00 sec)  
    mysql> DELIMITER ; 

結果顯示,儲存函式已經建立成功。該函式的使用和MySQL內部函式的使用方法一樣。

14.1.3  變數的使用

在儲存過程和函式中,可以定義和使用變數。使用者可以使用DECLARE關鍵字來定義變數。然後可以為變數賦值。這些變數的作用範圍是BEGIN…END程式段中。本小節將講解如何定義變數和為變數賦值。

1.定義變數

MySQL中可以使用DECLARE關鍵字來定義變數。定義變數的基本語法如下:

  1. DECLARE  var_name[,…]  type  [DEFAULT value] 

其中, DECLARE關鍵字是用來宣告變數的;var_name引數是變數的名稱,這裡可以同時定義多個變數;type引數用來指定變數的型別;DEFAULT value子句將變數預設值設定為value,沒有使用DEFAULT子句時,預設值為NULL。

【示例14-3】 下面定義變數my_sql,資料型別為INT型,預設值為10。程式碼如下:

  1. DECLARE  my_sql  INT  DEFAULT 10 ; 

2.為變數賦值

MySQL中可以使用SET關鍵字來為變數賦值。SET語句的基本語法如下:

  1. SET  var_name = expr [, var_name = expr] … 

其中,SET關鍵字是用來為變數賦值的;var_name引數是變數的名稱;expr引數是賦值表示式。一個SET語句可以同時為多個變數賦值,各個變數的賦值語句之間用逗號隔開。

【示例14-4】 下面為變數my_sql賦值為30。程式碼如下:

  1. SET  my_sql = 30 ; 

MySQL中還可以使用SELECT…INTO語句為變數賦值。其基本語法如下:

  1. SELECT  col_name[,…]  INTO  var_name[,…]  
  2.     FROM  table_name  WEHRE  condition 

其中,col_name參數列示查詢的欄位名稱;var_name引數是變數的名稱;table_name引數指表的名稱;condition引數指查詢條件。

【示例14-5】 下面從employee表中查詢id為2的記錄,將該記錄的d_id值賦給變數my_sql。程式碼如下:

  1. SELECT  d_id  INTO  my_sql  
  2.   FROM  employee  WEHRE  id=2 ; 

 

 

14.1.4  定義條件和處理程式

定義條件和處理程式是事先定義程式執行過程中可能遇到的問題。並且可以在處理程式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題,並提出解決辦法。這樣可以增強程式處理問題的能力,避免程式異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程式。本小節中將詳細講解如何定義條件和處理程式。

1.定義條件

MySQL中可以使用DECLARE關鍵字來定義條件。其基本語法如下:

  1. DECLARE  condition_name  CONDITION  FOR  condition_value  
  2. condition_value:  
  3.       SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

其中,condition_name參數列示條件的名稱;condition_value參數列示條件的型別;sqlstate_value引數和mysql_error_code引數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

【示例14-6】 下面定義”ERROR 1146 (42S02)”這個錯誤,名稱為can_not_find。可以用兩種不同的方法來定義,程式碼如下:

  1. //方法一:使用sqlstate_value  
  2. DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  ’42S02′ ;  
  3. //方法二:使用mysql_error_code  
  4. DECLARE  can_not_find  CONDITION  FOR  1146 ; 

2.定義處理程式

MySQL中可以使用DECLARE關鍵字來定義處理程式。其基本語法如下:

  1. DECLARE handler_type HANDLER FOR 
    condition_value[,…] sp_statement  
  2. handler_type:  
  3.     CONTINUE | EXIT | UNDO  
  4. condition_value:  
  5.     SQLSTATE [VALUE] sqlstate_value |
    condition_name  | SQLWARNING  
  6.        | NOT FOUND  | SQLEXCEPTION  | mysql_error_code 

其中,handler_type引數指明錯誤的處理方式,該引數有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不進行處理,繼續向下執行;EXIT表示遇到錯誤後馬上退出;UNDO表示遇到錯誤後撤回之前的操作,MySQL中暫時還不支援這種處理方式。

注意:通常情況下,執行過程中遇到錯誤應該立刻停止執行下面的語句,並且撤回前面的操作。但是,MySQL中現在還不能支援UNDO操作。因此,遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤型別,並且進行相應的處理,那麼可以執行CONTINUE操作。

condition_value引數指明錯誤型別,該引數有6個取值。sqlstate_value和mysql_error_code與條件定義中的是同一個意思。condition_name是DECLARE定義的條件名稱。SQLWARNING表示所有以01開頭的sqlstate_value值。NOT FOUND表示所有以02開頭的sqlstate_value值。SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些儲存過程或函式的執行語句。

【示例14-7】 下面是定義處理程式的幾種方式。程式碼如下:

  1. //方法一:捕獲sqlstate_value  
  2. DECLARE CONTINUE HANDLER FOR SQLSTATE ’42S02′
    SET @info=’CAN NOT FIND’;  
  3. //方法二:捕獲mysql_error_code  
  4. DECLARE CONTINUE HANDLER FOR 1146 SET @info=’CAN NOT FIND’;
     
  5. //方法三:先定義條件,然後呼叫  
  6. DECLARE  can_not_find  CONDITION  FOR  1146 ;  
  7. DECLARE CONTINUE HANDLER FOR can_not_find SET 
    @info=’CAN NOT FIND’;  
  8. //方法四:使用SQLWARNING  
  9. DECLARE EXIT HANDLER FOR SQLWARNING SET @info=’ERROR’;
     
  10. //方法五:使用NOT FOUND  
  11. DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’CAN NOT FIND’;
     
  12. //方法六:使用SQLEXCEPTION  
  13. DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’ERROR’; 

上述程式碼是6種定義處理程式的方法。第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02,執行CONTINUE操作,並且輸出”CAN NOT FIND”資訊。第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146,執行CONTINUE操作,並且輸出”CAN NOT FIND”資訊。第三種方法是先定義條件,然後再呼叫條件。這裡先定義can_not_find條件,遇到1146錯誤就執行CONTINUE操作。第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值,然後執行EXIT操作,並且輸出”ERROR”資訊。第五種方法是使用NOT
FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值,然後執行EXIT操作,並且輸出”CAN NOT FIND”資訊。第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值,然後執行EXIT操作,並且輸出”ERROR”資訊。

注:

資料庫設計三大正規化

為了建立冗餘較小、結構合理的資料庫,設計資料庫時必須遵循一定的規則。在關係型資料庫中這種規則就稱為正規化。正規化是符合某一種設計要求的總結。要想設計一個結構合理的關係型資料庫,必須滿足一定的正規化。             

在實際開發中最為常見的設計正規化有三個:

1.第一正規化(確保每列保持原子性)

第一正規化是最基本的正規化。如果資料庫表中的所有欄位值都是不可分解的原子值,就說明該資料庫表滿足了第一正規化。

第一正規化的合理遵循需要根據系統的實際需求來定。比如某些資料庫系統中需要用到“地址”這個屬性,本來直接將“地址”屬性設計成一個資料庫表的欄位就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分,那麼就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行儲存,這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了資料庫的第一正規化,如下表所示。

上表所示的使用者資訊遵循了第一正規化的要求,這樣在對使用者使用城市進行分類的時候就非常方便,也提高了資料庫的效能。         

2.第二正規化(確保表中的每列都和主鍵相關)

第二正規化在第一正規化的基礎之上更進一層。第二正規化需要確保資料庫表中的每一列都和主鍵相關,而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。也就是說在一個資料庫表中,一個表中只能儲存一種資料,不可以把多種資料儲存在同一張資料庫表中。

比如要設計一個訂單資訊表,因為訂單中可能會有多種商品,所以要將訂單編號和商品編號作為資料庫表的聯合主鍵,如下表所示。

 訂單資訊表

這樣就產生一個問題:這個表中是以訂單編號和商品編號作為聯合主鍵。這樣在該表中商品名稱、單位、商品價格等資訊不與該表的主鍵相關,而僅僅是與商品編號相關。所以在這裡違反了第二正規化的設計原則。

而如果把這個訂單資訊表進行拆分,把商品資訊分離到另一個表中,把訂單專案表也分離到另一個表中,就非常完美了。如下所示。

這樣設計,在很大程度上減小了資料庫的冗餘。如果要獲取訂單的商品資訊,使用商品編號到商品資訊表中查詢即可。            

3.第三正規化(確保每列都和主鍵列直接相關,而不是間接相關)

第三正規化需要確保資料表中的每一列資料都和主鍵直接相關,而不能間接相關。

比如在設計一個訂單資料表的時候,可以將客戶編號作為一個外來鍵和訂單表建立相應的關係。而不可以在訂單表中新增關於客戶其它資訊(比如姓名、所屬公司等)的欄位。如下面這兩個表所示的設計就是一個滿足第三正規化的資料庫表。

注:遊標

新建 level ;

create table test.level (name varchar(20));
再 insert 些資料 ;

程式碼初始化

drop procedure if exists useCursor //

建立 儲存過程 create

複製程式碼程式碼如下:

CREATE PROCEDURE useCursor()

BEGIN


區域性變數的定義 declare

複製程式碼程式碼如下:

declare tmpName varchar(20) default ” ;  
declare allName varchar(255) default ” ;  
declare cur1 CURSOR FOR SELECT name FROM test.level ;  


MySQL 遊標 異常後 捕捉

並設定 迴圈使用 變數 tmpname 為 null 跳出迴圈。

複製程式碼程式碼如下:

declare CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET tmpname = null; 


開遊標

複製程式碼程式碼如下:

OPEN cur1;


遊標向下走一步

複製程式碼程式碼如下:

FETCH cur1 INTO tmpName;


迴圈體 這很明顯 把MySQL 遊標查詢出的 name 都加起並用 ; 號隔開

複製程式碼程式碼如下:

WHILE ( tmpname is not null) DO 
set tmpName = CONCAT(tmpName ,”;”) ; 
set allName = CONCAT(allName ,tmpName) ; 


遊標向下走一步

複製程式碼程式碼如下:

FETCH cur1 INTO tmpName;


結束迴圈體: 

複製程式碼程式碼如下:

END WHILE;


關閉遊標

複製程式碼程式碼如下:

CLOSE cur1;


選擇資料

複製程式碼程式碼如下:

select allName ;


結束儲存過程

複製程式碼程式碼如下:

END;//


呼叫儲存過程:

複製程式碼程式碼如下:

call useCursor()//


執行結果:

複製程式碼程式碼如下:

mysql> call useCursor()//

————————————–

| allName                              |

————————————–

| f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |

————————————–

1 row in set (0.00 sec)


loop迴圈遊標:

複製程式碼程式碼如下:

DELIMITER $$  

DROP PROCEDURE IF EXITS cursor_example$$  
CREATE PROCEDURE cursor_example()  
     READS SQL DATA  
BEGIN  
     DECLARE l_employee_id INT;  
     DECLARE l_salary NUMERIC(8,2);  
     DECLARE l_department_id INT;  
     DECLARE done INT DEFAULT 0;  
     DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  

     OPEN cur1;  
     emp_loop: LOOP  
         FETCH cur1 INTO l_employee_id, l_salary, l_department_id;  
         IF done=1 THEN  
             LEAVE emp_loop;  
         END IF;  
     END LOOP emp_loop;  
     CLOSE cur1;  
END$$  
DELIMITER ;  


repeat迴圈遊標:

複製程式碼程式碼如下:

/*建立過程*/
DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a VARCHAR(200) DEFAULT ”;
    DECLARE c VARCHAR(200) DEFAULT ”;

    DECLARE mycursor CURSOR FOR SELECT  fusername FROM uchome_friend;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN mycursor;

    REPEAT 
        FETCH mycursor INTO a;
        IF NOT done THEN
            SET c=CONCAT(c,a);/*字串相加*/
        END IF;

    UNTIL done END REPEAT;

    CLOSE mycursor;

    SELECT c;
END //
DELIMITER ;


複製程式碼程式碼如下:

/*建立過程*/
DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a VARCHAR(200) DEFAULT ”;
    DECLARE c VARCHAR(200) DEFAULT ”;

    DECLARE mycursor CURSOR FOR SELECT  fusername FROM uchome_friend;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN mycursor;

    REPEAT 
        FETCH mycursor INTO a;
        IF NOT done THEN
            SET c=CONCAT(c,a);/*字串相加*/
        END IF;

    UNTIL done END REPEAT;

    CLOSE mycursor;

    SELECT c;
END //
DELIMITER ;