时间：2021-07-01 10:21:17 帮助过：18人阅读
显然数据已如期查询出来了，但是我们发现任何需要这个数据的人都必须了解相关联的表结构，并且需要知道如何创建查询和对表进行联结，为了检索其他用户的相同数据必须修改Where条件并带上一大段关联查询的sql语句。是的，每次这样的操作确实挺麻烦的，假如现在可以把这个除了where条件外的sql查询出来的数据包装成一个名为user_order_data的虚拟表，就可以使用以下方式检索出数据了。
select * from user_order_data where username=‘王五‘;按这样的方式每次查询不同的用户只需修改where条件即可也不同再写那段看起有点恶心的长sql了，而事实上user_order_data就是一张视图表，也可称为虚拟表，而这就是视图最显著的作用了。
了解完什么是视图后，我们先来看看如何创建视图和使用视图,使用以下语法：
CREATE VIEW 视图名(列名,...) AS SELECT 语句现在我们使用前面关联查询的orders表、orderdetail表、items表、user表来创建视图user_order_data
-- 创建视图虚拟表user_order_data
mysql> create view user_order_data(username,number,itemname,price,items_num) as select u.username , o.number , tm.name , tm.price , od.items_num from
-> (
-> (orders as o inner join orderdetail as od on o.id = od.orders_id )
-> inner join items as tm on od.items_id = tm.id
-> )
-> inner join user as u on o.user_id = u.id;
Query OK, 0 rows affected (0.01 sec)
-- 使用视图
mysql> select * from user_order_data;
+----------+---------+-------------+---------+-----------+
| username | number | itemname | price | items_num |
+----------+---------+-------------+---------+-----------+
| 王五 | 1000010 | MacBook Air | 8298.9 | 1 |
| 王五 | 1000010 | MacBook Pro | 10982.0 | 3 |
| 王五 | 1000011 | 背包 | 200.0 | 4 |
| 王五 | 1000011 | MacBook Pro | 10982.0 | 3 |
+----------+---------+-------------+---------+-----------+
4 rows in set (0.01 sec)
可以看出除了在select语句前面加上create view user_order_data as外，其他几乎没变化。在使用视图user_order_data时，跟使用数据库表没啥区别，因此以后需要查询指定用户或者所有用户的订单情况时，就不用编写长巴巴的一段sql了，还是蛮简洁的。除了上述的方式，还可以将视图虚拟表的字段别名移动到查询字段后面：
CREATE OR REPLACE VIEW user_order_data
AS
SELECT
u.username as username,
o.number as number ,
tm.name as name ,
tm.price as price ,
od.items_num as items_num
FROM
(
(orders as o INNER JOIN orderdetail as od ON o.id = od.orders_id )
INNER JOIN items as tm ON od.items_id = tm.id
)
INNER JOIN user as u ON o.user_id = u.id注意这里使用了CREATE OR REPLACE VIEW语句，意思就是不存在就创建，存在就替换。如果想删除视图可以使用以下语法：
DROP VIEW 视图名称在使用视图的过程还有些需要注意的点，如下
与创建表一样，创建视图的名称必须唯一
创建视图的个数并没限制，但是如果一张视图嵌套或者关联的表过多，同样会引发性能问题，在实际生产环节中部署时务必进行必要的性能检测。
在过滤条件数据时如果在创建视图的sql语句中存在where的条件语句，而在使用该视图的语句中也存在where条件语句时，这两个where条件语句会自动组合
order by 可以在视图中使用，但如果从该视图检索数据的select语句中也含有order by ，那么该视图中的order by 将被覆盖。
视图中不能使用索引，也不能使用触发器(索引和触发器后面会分析)
使用可以和普通的表一起使用，编辑一条联结视图和普通表的sql语句是允许的。
关于使用视图对数据的进行更新(增删改)，因为视图本身并没有数据，所以这些操作都是直接作用到普通表中的，但也并非所有的视图都可以进行更新操作,如视图中存在分组(group by)、联结、子查询、并(unoin)、聚合函数(sum/count等)、计算字段、DISTINCT等都不能对视图进行更新操作，因此我们前面的例子也是不能进行更新操作的，事实上，视图更多的是用于数据检索而更新，因此对于更新也没有必要进行过多阐述。
到此对于视图的创建和使用都比较清晰了，现在准备进一步认识视图的本质，前面我们反复说过，视图是一张虚拟表，是不带任何数据的，每次查询时只是从普通表中动态地获取数据并组合，只不过外表看起来像一张表罢了。其原理通过下图便一目了然：
事实上有些时候视图还会被用于限制用户对普通表的查询操作，对于这类用户只赋予对应视图的select操作权限，仅让他们只能读取特定的行或列的数据。这样我们也就不用直接使用数据库的权限设置限制行列的读取，同时也避免了权限细化的麻烦。
由于mysql在默认情况下，表中的数据记录是没有顺序可言的，也就是说在数据检索过程中，符合条件的数据存储在哪里，我们是完全不知情的，如果使用select语句进行查询，数据库会从第一条记录开始检索，即使找到第一条符合条件的数据，数据库的搜索也并不会因此而停止，毕竟符合条件的数据可能并不止一条，也就是说此时检索会把表中的数据全部检索一遍才结束，这样的检索方式也称为全表扫描。但假设表中存在上百上千万条数据呢？这样的检索效率就十分低了，为了解决这个问题，索引的概念就诞生了，索引是为检索而存在的。如一些书的末尾一般会提供专门附录索引，指明了某个关键字在正文中的出现的页码位置或章节的位置，这样只要找到对应页面就能找到要搜索的内容了，数据库的索引也是类似这样的原理，通过创建某个字段或者多个字段的索引，在搜索该字段时就可以根据对应的索引进行快速检索出相应内容而无需全表扫描了。
MySQL 索引可以分为单列索引、复合索引、唯一索引、主键索引等。下面分别介绍
单列索引，也称为普通索引，单列索引是最基本的索引，它没有任何限制，创建一个单列索引，语法如下:
CREATE INDEX index_name ON tbl_name(index_col_name);其中index_name为索引的名称，可以自定义，tbl_name则指明要创建索引的表，而index_col_name指明表中那一个列要创建索引。当然我们也可以通过修改表结构的方式添加索引：
ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name);还可在创建表时直接指定：
-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32) NOT NULL ,
...... -- 其他字段
PRIMARY KEY (`id`),
indexName (name(32)) -- 创建name字段索引
);下面为user表的username字段创建单列索引：
-- 创建username字段的索引名称为index_name，这就是基础的索引创建
mysql> create index index_name on user(username);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看user表存在的索引 \G 代表优化显示方式
mysql> show index from user \G;
*************************** 1. row ***************************
Table: user 表名称
Non_unique: 0 索引是否允许重复(1:可重复,0:不可重复)
Key_name: PRIMARY 索引名称
Seq_in_index: 1
Column_name: id 表中被创建索引的字段名称
Collation: A 是否排序(A:升序,Null:不排序)
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE 索引的类型
Comment:
Index_comment:
*************************** 2. row ***************************
Table: user
Non_unique: 1 索引是否允许重复(1:可重复,0:不可重复)
Key_name: index_name 索引名称
Seq_in_index: 1
Column_name: username 表中被创建索引的字段名称
Collation: A 是否排序(A:升序,Null:不排序)
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE 索引的类型
Comment:
Index_comment:
2 rows in set (0.00 sec)可见user表中的username字段的索引已被创建，在使用show index from user查看user的索引字段时，我们发现id字段也创建了索引，事实上，当user表被创建时，主键的定义的字段id就会自动创建索引，这是一种特殊的索引，也称为丛生索引，而刚才创建的index_name索引属于单列索引
复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。创建一个复合索引的语法如下：
-- index_name代表索引名称，而index_col_name1和index_col_name2为列名，可以多个
CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,...);
-- 同样道理，也可以通过修改表结构的方式添加索引，
ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name1,index_col_name2,...);
-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32) NOT NULL ,
‘pinyin‘ varchar(32) ,
...... -- 其他字段
PRIMARY KEY (`id`),
indexName (name(32),pinyin(32))
);为了方便演示，为user表添加名称拼音字段（pinyin）
-- 添加新字段pinyin
mysql> alter table user add pinyin varchar(32) after username;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0现在利用username和pinyin 两个字段为user表创建复合索引，先删除之前为username创建的索引，删除索引语法如下：
DROP INDEX 索引名称 ON 表名删除username的索引
-- 删除index_name索引
mysql> drop index index_name on user;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看user的索引
mysql> show index from user \G;
*************************** 1. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)ok,index_name 索引已被删除，现在联合username和pinyin创建索引如下：
-- 创建新索引多列组成，index_pinyin为复合索引名称
mysql> create index index_pinyin on user(username,pinyin);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 这里省略主键索引
mysql> show index from user \G;
*************************** 2. row ***************************
Table: user
Non_unique: 1
Key_name: index_pinyin 相同的索引名称
Seq_in_index: 1 显示列的顺序
Column_name: username
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: user
Non_unique: 1
Key_name: index_pinyin 相同的索引名称
Seq_in_index: 2 显示列的顺序
Column_name: pinyin
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)像这样由两个以上组成的索引称为复合索引，由于是复合索引因此索引的名称都相同，注意Seq_in_index代表索引字段的顺序，前面我们说过在查询条件中使用了复合索引的第一个字段（这里指username），索引才会被使用。因此，在复合索引中索引列的顺序至关重要。
创建唯一索引必须指定关键字UNIQUE，唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。如创建username为唯一索引，那么username的值是不可以重复的，
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON tbl_name(index_col_name[,...]);
-- 添加（通过修改表结构）
ALTER TABLE tbl_name ADD UNIQUE INDEX index_name ON (index_col_name[,...]);
-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32) NOT NULL ,
...... -- 其他字段
PRIMARY KEY (`id`),
UNIQUE indexName (name(32))
);下面为user表的username字段创建唯一索引：
-- 仅为演示
create unique index idx_name on user(username);事实上这里讲username设置为唯一索引是不合理的，毕竟用户可能存在相同username，因此在实际生产环节中username是不应该设置为唯一索引的。否则当有相同的名称插入时，数据库表将会报错。
主键索引也称丛生索引，是一种特殊的唯一索引，不允许有空值。创建主键索引语法如下：
ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name);一般情况下在创建表时，指明了主键时，主键索引就已自动创建了，因此无需我们手动创建。
-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
...... -- 其他字段
PRIMARY KEY (`id`), -- 主键索引
);where子句中的列可能最适合做为索引
不要尝试为性别或者有无这类字段等建立索引(因为类似性别的列，一般只含有“0”和“1”，无论搜索结果如何都会大约得出一半的数据)
如果创建复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列
不要过度使用索引。每一次的更新，删除，插入都会维护该表的索引，更多的索引意味着占用更多的空间
使用InnoDB存储引擎时，记录(行)默认会按照一定的顺序存储，如果已定义主键，则按照主键顺序存储，由于普通索引都会保存主键的键值，因此主键应尽可能的选择较短的数据类型，以便节省存储空间
不要尝试在索引列上使用函数。
ok~，关于索引暂且聊到这，由于索引细说起来内容还是相当多，本篇只对索引的主要知识点进行说明，让我们对索引有个清晰的了解并学会如何去使用索引。
迄今为止，我们所使用的大多数SQL语句都针对一个或多个表的单条语句，当需要通过处理流程来达到预期目标时，单条sql语句就很难做到了，这是因为sql语句无法编写处理流程的语句，所有的sql都只能通过一个个命令执行，比如想循环执行某个SQL语句，对于没有处理流程的sql显然是无法实现的，此时就需要通过存储过程来达到目的了，简单的理解存储过程就是数据库中保存的一系列SQL命令的集合，也就是说通过存储过程就可以编写流程语句，如循环操作语句等，下面看看如何使用存储过程。
可以通过以下语法创建存储过程：
CREATE PROCEDURE 存储过程名称( 参数的种类1 参数1 数据类型1
[,参数的种类2 参数2 数据类型2])
BEGIN
处理内容
END存储过程的名称可以自由定义，但不可与存在的函数或存储过程名称重复，命名时建议以【sp_】开头，需要处理的内容则编辑在BEGIN和END之间。参数的种类分3种，分别是IN、OUT、INOUT，其中IN为输入参数类型，OUT为输出参数类型，而INOUT既是输入类型又是输出类型，下面我们创建一个存储过程，以达到对user表的用户名称进行模糊查询的目的，存储过程名称为sp_search_user：
-- 改变分隔符
DELIMITER //
-- 创建存储过程
mysql> create procedure sp_search_user (in name varchar(20));
-> begin
-> if name is null or name=‘‘ then
-> select * from user;
-> else
-> select * from user where username like name;
-> end if;
-> end
-> // -- 执行sql
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ; -- 恢复分隔符其中DELIMITER可以用于改变分隔符，由于存储过程中流程语句需要使用分号结尾与mysql命令行的sql语句结尾的分号冲突，于是改变分隔符为//，执行完存储过程后再恢复为分号即可。从存储过程创建语句中，我们设置一个name的输出参数语句并在begin与end之间编写了流程语句，当名称为空时查询所有用户否则按传入的条件查询。现在可以使用该存储过程了，调用语法如下：
CALL 存储过程名称(参数，....)执行已创建存储函数
-- name传入null值，查询所有用户。
mysql> call sp_search_user(null);
+----+-----------+--------------+------------+------+--------------------+
| id | username | pinyin | birthday | sex | address |
+----+-----------+--------------+------------+------+--------------------+
| 1 | 王五 | wangwu | NULL | 1 | 北京市朝阳区 |
| 2 | 张曹宇 | zhangcaoyu | 1990-08-05 | 1 | 广东省汕头市 |
| 3 | 李达康 | lidakang | 1909-12-12 | 1 | 北京朝阳 |
| 10 | 张书记 | zhangshuji | 1999-06-06 | 0 | 北京市朝阳区 |
| 16 | 任在明 | renzaiming | 1996-12-01 | 1 | 广东省广州市 |
| 22 | 陈小明 | chenxiaoming | 1995-05-10 | 0 | 广东省深圳市 |
| 24 | 任传海 | renchuanhai | 1992-03-08 | 1 | 海南三亚 |
+----+-----------+--------------+------------+------+--------------------+
7 rows in set (0.00 sec)
-- 查询以name以任开头的用户
mysql> call sp_search_user(‘任%‘);
+----+-----------+-------------+------------+------+--------------------+
| id | username | pinyin | birthday | sex | address |
+----+-----------+-------------+------------+------+--------------------+
| 24 | 任传海 | renchuanhai | 1992-03-08 | 1 | 海南三亚 |
| 16 | 任在明 | renzaiming | 1996-12-01 | 1 | 广东省广州市 |
+----+-----------+-------------+------------+------+--------------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)前面提到了三种输入输出的参数类型，IN输入参数类型，OUT输出参数类型，而INOUT既是输入类型又是输出类型，所谓的IN输入参数类型就是把要传递的参数输入到存储过程的内部以便编写存储过程流程语句时可以使用。上述演示过的例子就是这种类型。关键字OUT则是指明相应参数用来从存储过程传出的一个值,也可以理解为存储过程的返回值，而对于INOUT则是两者结合体。现在我们创建一个存储过程，用于返回商品的最大值、最小值和平均值，命名为sp_item_price
DELIMITER //
-- 创建存储过程
mysql> create procedure sp_item_price(out plow decimal(8,2),
-> out phigh decimal(8,2),
-> out pavg decimal(8,2)
-> )
-> begin
-> select min(price) into plow from items;
-> select max(price) into phigh from items;
-> select avg(price) into pavg from items;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
-- 恢复分隔符
DELIMITER ;
-- 调用存储过程
mysql> call sp_item_price(@pricelow,@pricehigh,@priceavg);
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 查询执行结果
mysql> select @pricelow ;
+-----------+
| @pricelow |
+-----------+
| 78.00 |
+-----------+
1 row in set (0.01 sec)
mysql> select @pricehigh ;
+------------+
| @pricehigh |
+------------+
| 10982.00 |
+------------+
1 row in set (0.00 sec)
mysql> select @priceavg ;
+-----------+
| @priceavg |
+-----------+
| 3773.65 |
+-----------+
1 row in set (0.00 sec)正如我们所看到的，创建sp_item_price时，使用了3个out参数，在存储过程内部将会把执行结果分别存入这个三个变量中，存入关键字使用的是into，完成存储过程创建后，使用call sp_item_price(@pricelow,@pricehigh,@priceavg);调用sp_item_price，传入用于存储返回值的3个变量，注意mysql的用户变量必须以@开头，名称可自定义，但不能重复，调用完成后语句并没有显示任何数据，因为这些数据都存入了@pricelow,@pricehigh,@priceavg 三个变量中，我们可以使用select操作符查询这些变量。这里有点要明白的，上述存储过程中使用对输出变量的赋值语句：
SELECT 列名1，... INTO 变量名1，... FROM 表名 WHERE 语句等...请注意如果检索出多个列名，与之对应的赋值变量也必须有多个。ok，对输入输出参数类型有了清晰的了解后，为了加深理解，我们创建一个同时存在输入输出参数的存储过程，输入订单号，计算该订单的订单总价，名称为sp_order_sum_price
DELIMITER //
-- 创建存储过程
mysql> create procedure sp_order_sum_price(in number int ,out ptotal decimal(8,2))
-> begin
-> select sum(price * items_num) from items inner join orderdetail as od on items.id = od.items_id
-> where od.orders_id=number
-> into ptotal; --放到语句后面也可行
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
-- 恢复分隔符
DELIMITER ;
-- 执行存储过程
mysql> call sp_order_sum_price(3,@priceTotal)；
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 查询结果
mysql> select @priceTotal；
+-------------+
| @priceTotal |
+-------------+
| 41244.90 |
+-------------+
1 row in set (0.00 sec)
如果创建存储过程有误，可以删除后重新创建，当然也可以修改，不过感觉不如删除重建来得更简单，删除存储过程可以使用以下语句
DROP PROCEDURE [IF EXISTS] 存储过程名称;SHOW PROCEDURE STATUS [LIKE ‘pattern‘]如查看的sp_order_sum_price状态
-- 查看的sp_order_sum_price状态
mysql> SHOW PROCEDURE STATUS LIKE ‘sp_order_sum_price‘ \G;
*************************** 1. row ***************************
Db: webshop
Name: sp_order_sum_price
Type: PROCEDURE
Definer: root@localhost
Modified: 2017-04-17 09:38:52
Created: 2017-04-17 09:38:52
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)查看已创建的存储过程，可以使用以下语法
SHOW CREATE PROCEDURE 存储过程名;如下查看前面已创建的sp_order_sum_price存储过程：
-- 查看sp_order_sum_price存储过程 \G代表格式输出格式
mysql> show create procedure sp_order_sum_price \G ;
*************************** 1. row ***************************
Procedure: sp_order_sum_price -- 存储过程名称
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-- 存储过程创建语句
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_order_sum_price`(in number int ,out ptotal decimal(8,2))
begin
select sum(price * items_num) from items inner join orderdetail as od on items.id = od.items_id
where od.orders_id=number
into ptotal;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)以下是存储过程中可以使用的流程控制语句
IF 条件语句
IF 条件语句，事实上与流行编程的条件语句类似，其语法如下：
IF 条件表达式1 THEN
条件表达式1为true执行
[ELSEIF 条件表达式2 THEN
条件表达式2为true执行
]
[ELSE
全部条件为false时执行]
END IF；简单案例：
mysql> create procedure sp_search_user (in name varchar(20));
-> begin
-> if name is null or name=‘‘ then
-> select * from user;
-> else
-> select * from user where username like name;
-> end if;
-> end
-> // <------执行sql多分支条件语句
CASE 表达式1
WHEN 值1 THEN 表达式=值1时执行命令
[WHEN 值N THEN 表达式=值N时执行该语句]
[ELSE 上述值以外执行该语句]
END CASE 简单案例
mysql > DELIMITER //
mysql > CREATE PROCEDURE sp_insert_user (in parameter int)
-> begin
-> declare var int; -- 声明变量
-> set var=parameter+1; -- 设置变量值
-> case var
-> when 0 then
-> insert into user values(11,‘高玉兰‘,‘gaoyulan‘,‘1999-01-01‘,0,null);
-> when 1 then
-> insert into user values(11,‘高晓龙‘,‘gaoxiaolong‘,‘1999-01-01‘,1,null);
-> else
-> insert into user values(11,‘高余粮‘,‘gaoyuliang‘,‘1999-01-01‘,1,null);
-> end case;
-> end
-> //
mysql > DELIMITER ; repeat 循环控制语句
REPEAT
直至条件表达式为True时执行的语句
UNTIL 条件表达式 END REPEAT;有时需要测试查询性能，需要准备假数据，使用循环语句来插入是个不错的选择，如下向user表中插入2000条数据。
mysql > DELIMITER //
mysql > CREATE PROCEDURE sp_insert_user_repeat_2000 ()
-> begin
-> declare n int; -- 声明变量
-> set n=0; -- 设置变量值
-> repeat
-> insert into user values(30+n,concat(‘高余粮‘,n),‘gaoyuliang‘,‘1999-01-01‘,1,null);
-> set n=n+1;
-> until n>=2000 end repeat; -- 结束条件
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
mysql > DELIMITER ;
-- 执行存储过程
mysql> call sp_insert_user_repeat_2000();
Query OK, 1 row affected (0.01 sec)while循环控制语句
while 循环语句与repeat循环控制语句的区别是前者条件不符合一次循环体都不会执行，而后者无论条件是否符合，至少执行一次循环体，这点从前面的案例可以看出来。while循环语句语法如下：
WHILE 条件表达式 DO
系列语句
END WHILE使用while循环语句实现前面的repeat一样的功能。
mysql > DELIMITER //
mysql > CREATE PROCEDURE sp_insert_user_while_2000 ()
-> begin
-> declare n int; -- 声明变量
-> set n=0; -- 设置变量值
-> while n < 2000 do
-> insert into user values(30+n,concat(‘高余粮‘,n),‘gaoyuliang‘,‘1999-01-01‘,1,null);
-> set n=n+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
mysql > DELIMITER ; 在流程语句的分析中，我们在存储过程中使用变量的声明与设置，由于这些变量也只能在存储过程中使用，因此也称为局部变量，变量的声明可以使用以下语法：
DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value];
-- 定义变量num，数据类型为INT型，默认值为10
DECLARE num INT DEFAULT 10 ; 其中， DECLARE关键字是用来声明变量的；变量名即变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。声明后，我们就可以在存储过程使用该变量，设置变量值可以使用以下语法：
SET 变量名1 = expr [, 变量名2 = expr] ... 其中，SET关键字是用来为变量赋值的；expr参数是赋值表达式或某个值。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。除了这种赋值方式，前面我们还提到过使用SELECT…INTO语句为变量赋值，那也是可行的。
用户变量：以”@”开始，形式为”@变量名”，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，声明或者定义用户变量使用set语句，如 set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量。
全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.name，对所有客户端生效。只有具有super权限才可以设置全局变量。如下：
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;会话变量：只对连接的客户端有效。
mysql> SET SESSION sort_buffer_size=value;定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法，可以简单理解为异常处理，这种方式可以提前预测可能出现的问题，并提出解决办法，从而增强程序健壮性，避免程序异常停止。MySQL通过DECLARE关键字来定义条件和处理程序。
定义条件
MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：
-- 条件定义语法
DECLARE condition_name CONDITION FOR condition_value
-- condition_value的定义格式
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，简单案例如下：
-- 定义主键重复错误
-- ERROR 1062 (23000): Duplicate entry ‘60‘ for key ‘PRIMARY‘
-- 方法一：使用sqlstate_value
DECLARE primary_key_duplicate CONDITION FOR SQLSTATE ‘23000‘ ;
-- 方法二：使用mysql_error_code
DECLARE primary_key_duplicate CONDITION FOR 1062 ; 定义处理程序
前面定义的处理条件，可以在定义处理程序中使用,先了解一下定义语法：
DECLARE handler_type HANDLER FOR
condition_value[,...] sp_statement handler_type 参数的取值有三种：CONTINUE | EXIT | UNDO。
CONTINUE 表示遇到错误不进行处理，继续向下执行；
EXIT 表示遇到错误后马上退出；
UNDO 表示遇到错误后撤回之前的操作，但MySQL中暂时还不支持这种处理方式。
我们需要注意的是，大多数情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。由于MySQL目前并不支持UNDO操作。所以，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么就选择CONTINUE操作。
condition_value 参数指明错误类型，该参数有6个取值。语法如下：
-- condition_value的取值：
SQLSTATE [VALUE] sqlstate_value |
mysql_error_code |
condition_name |
SQLWARNING |
SQLEXCEPTION |
sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。如常见的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。与条件中参数是一样的。
condition_name是DECLARE定义的条件名称，就前面定义条件语句
NOT FOUND表示所有以02开头的sqlstate_value值。
SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。
sp_statement 参数表示要执行存储过程或函数语句。
以下定义了如何捕获和处理异常的简单例子
-- 捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且设置用户变量info。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02‘ SET @info=‘CAN NOT FIND‘;
-- 捕获mysql_error_code，如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且设置用户变量info。
DECLARE CONTINUE HANDLER FOR 1146 SET @info=‘CAN NOT FIND‘;
-- 先定义条件，然后定义处理程序调用
DECLARE can_not_find CONDITION FOR 1146 ;
-- 定义处理程序，并使用定义的can_not_find条件
DECLARE CONTINUE HANDLER FOR can_not_find SET @info=‘CAN NOT FIND‘;
-- SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT
DECLARE EXIT HANDLER FOR SQLWARNING SET @info=‘ERROR‘;
-- NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=‘CAN NOT FIND‘;
-- SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作。
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=‘ERROR‘; 为了加深理解，下面我们编写一个存储过程用于添加用户，借此来了解定义处理程序的作用，如下：
mysql > DELIMITER //
mysql> create procedure sp_insert_user()
-> begin
-> set @n=1