SQL必知必會學習筆記大總結

第一課：了解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)
           

附錄 關鍵字

ADD	EXIT	PRIMARY
ALL	FETCH	PRINT
ALTER	FILE	PRIVILEGES
AND	FILLFACTOR	PROC
ANY	FLOPPY	PROCEDURE
AS	FOR	PROCESSEXIT
ASC	FOREIGN	PUBLIC
AUTHORIZATION	FREETEXT	RAISERROR
AVG	FREETEXTTABLE	READ
BACKUP	FROM	READTEXT
BEGIN	FULL	RECONFIGURE
BETWEEN	GOTO	REFERENCES
BREAK	GRANT	REPEATABLE
BROWSE	GROUP	REPLICATION
BULK	HAVING	RESTORE
BY	HOLDLOCK	RESTRICT
CASCADE	IDENTITY	RETURN
CASE	IDENTITY_INSERT	REVOKE
CHECK	IDENTITYCOL	RIGHT
CHECKPOINT	IF	ROLLBACK
CLOSE	IN	ROWCOUNT
CLUSTERED	INDEX	ROWGUIDCOL
COALESCE	INNER	RULE
COLUMN	INSERT	SAVE
COMMIT	INTERSECT	SCHEMA
COMMITTED	INTO	SELECT
COMPUTE	IS	SERIALIZABLE
CONFIRM	ISOLATION	SESSION_USER
CONSTRAINT	JOIN	SET
CONTAINS	KEY	SETUSER
CONTAINSTABLE	KILL	SHUTDOWN
CONTINUE	LEFT	SOME
CONTROLROW	LEVEL	STATISTICS
CONVERT	LIKE	SUM
COUNT	LINENO	SYSTEM_USER
CREATE	LOAD	TABLE
CROSS	MAX	TAPE
CURRENT	MIN	TEMP
CURRENT_DATE	MIRROREXIT	TEMPORARY
CURRENT_TIME	NATIONAL	TEXTSIZE
CURRENT_TIMESTAMP	NOCHECK	THEN
CURRENT_USER	NONCLUSTERED	TO
CURSOR	NOT	TOP
DATABASE	NULL	TRAN
DBCC	NULLIF	TRANSACTION
DEALLOCATE	OF	TRIGGER
DECLARE	OFF	TRUNCATE
DEFAULT	OFFSETS	TSEQUAL
DELETE	ON	UNCOMMITTED
DENY	ONCE	UNION
DESC	ONLY	UNIQUE
DISK	OPEN	UPDATE
DISTINCT	OPENDATASOURCE	UPDATETEXT
DISTRIBUTED	OPENQUERY	USE
DOUBLE	OPENROWSET	USER
DROP	OPTION	VALUES
DUMMY	OR	VARYING
DUMP	ORDER	VIEW
ELSE	OUTER	WAITFOR
END	OVER	WHEN
ERRLVL	PERCENT	WHERE
ERROREXIT	PERM	WHILE
ESCAPE	PERMANENT	WITH
EXCEPT	PIPE	WORK
EXEC	PLAN	WRITETEXT
EXECUTE	PRECISION
EXISTS	PREPARE

第三課：排序檢索資料----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 ;