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 ;