继续讲解mysql的高级特性包括存储过程、游标、流程控制、触发器、事务等知识。
1.存储过程和函数
什么是存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程简单的来说，就是为以后的使用而保存一条或者多条MySQL语句和集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
为什么要使用存储过程
总的来说，存储过程有3个主要的好处：简单、安全、高性能。
- 通过把处理封装在容易使用的单元中，简化复杂的操作。
- 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
- 简化对变动的管理。如果表名、列名或者业务逻辑有变化，只需要更改存储过程的代码。
- 提高性能。因为使用存储过程比使用单独的SQL语句要快。
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
存储过程和函数的区别：函数必须有返回值，而存储过程没有（有没有都可以）；存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。（ IN,表示该参数的值必须在调用存储过程时指定；OUT,表示该参数的值可以被存储过程改变，并且可以返回；INOUT,表示该参数的调用时指定，并且可以被改变和返回）
使用存储过程
1.执行存储过程
调用存储过程的语法如下：
1
2
3
|
call sp_name(parameter[,...])
#@所有的MySQL变量必须以@开始
call film_in_stock(2,2,@a)#@a是代表调用后的返回值
|
2.创建存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
#该存储过程用来检查film_id和store_id对应的inventory是否满足要求，并且返回满足要求的inventory_id以及满足要求的记录数
delimiter $$ #定义将sql语句结束的标志改为$$
#创建一个名为film_in_stock的存储过程，两个输入参数p_film_id和p_store_id，一个输出参数p_film_count
create procedure film_in_stock(IN p_film_id int, IN p_store_id int,OUT p_film_count int)
reads sql data #固定写法
begin
#第一条sql
select inventory_id
from inventory
where film_id=p_film_id
and store_id = p_store_id
#存储过程和函数中可以调用其他的过程或者函数，这里调用了名为inventory_in_stock的存储过程
and inventory_in_stock(inventory_id);
#第二条sql
select found_row() into p_film_count;
end $$
|
删除存储过程
一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的alter routine权限，具体语法如下：
1
2
3
|
drop { procedure | function} [if exists] sp_name;
#if exists 是仅当存在时删除
drop procedure film_in_stock;
|
查看存储过程
1
2
3
4
|
#查看存储过程中变量值
select @a
#查看存储过程的定义
show create procedure film_in_stock \G
|
2.游标
什么是游标？
由前面的结果可知，MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或者多行）。使用简单的select语句，没有办法得到第一行、下一行或者前10行。有时，需要在检索出来的行中前进或者一行或多行。这就是使用游标的原因。
游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。
在存储了游标之后，应用程序可以根据需要滚动或者浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据进行浏览或者作出更改。
使用游标
1
2
3
4
5
6
7
|
#在能够使用游标前，必须声明它
declare cursor_name cursor for select_statename(select语句)
#打开和关闭游标
open cursor_name
#游标处理完成后，应当关闭游标
#close释放游标使用的所有内存和资源
close cursor_name
|
下面是一个简单使用游标的过程，对payment表按照行进行循环的处理，按照staff_id值 的不同累加amount值，判断循环结束的条件是捕获NOT FOUND的条件，当FETCH游标找不到下一条记录的时候，就会关闭然后退出过程。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
#定义MySQL结束标志
delimiter $$
#创建存储过程
create procedure payment_stat（）
begin
#声明两个局部变量，该变量的作用范围只能在`begin...end`块中，可以用到嵌套块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。
declare i_staff_id int;
declare d_amount decimal(5,2)
#声明游标
#对于select查询后的结果集存放在cur_payment中
declare cur_payment cursor for select staff_id,amout from payment；
#定义条件和处理
declare exit handler for not found close cur_payment
#给两个变量使用set直接赋值，所有的MySQL变量必须以@开始
set @x1 = 0;
set @x2 = 0;
#打开游标
open cur_payment;
#重复循环
repeat
#其中fetch用来检索当前行的staff_id,amout列（将自动从第一行开始）分别到i_staff_id,d_amount局部声明的变量中，对检索出的数据不做任何处理
fetch cur_payment into i_staff_id,d_amount;
if i_staff_id = 2 then
set @x1 = @x1+d_amount;
else
set @x2 = @x2+d_amount;
end if;
until 0 end repeat;
#关闭游标
close cur_payment;
end;
$$
#对两个变量的结果查询
select @x1,@x2
@x1 @x2
33927.04 33489.47
|
3.流程控制
IF语句
1
2
3
4
5
|
if search_condition then
statement_list;
elseif search_condition then
statement_list;
end if
|
CASE语句
1
2
3
4
|
case case_value
when when_value then statement_list
else statement_list
end case
|
LOOP语句
LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：
1
2
3
|
[begin_label:] loop
statement_list
end loop [end_label]
|
LEAVE语句
LEAVE语句用来标注的流程构造中退出，通常和begin…end或者循环一起使用。
ITERATE语句
ITERATE语句必须使用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。
REPEAT语句
REPEAT语句是有条件的循环控制语句，当满足条件的时候退出循环。
1
2
3
4
|
[begin_label:]repeat
statement_list
until search_statement
end repeat [end_label]
|
WHILE语句
WHILE语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容。
1
2
3
|
while search_condition do
statement_list
end while
|
4.触发器
什么是触发器？
触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器定义的语句集合。
创建触发器
创建触发器时，需要给出4条信息：
- 唯一的触发器名（保持每个表的触发器名唯一，但不是在每个数据库中唯一）
- 触发器关联的表
- 触发器应该响应的活动（delete、insert或update）
- 触发器何时执行（处理之前或之后）
1
2
3
4
5
6
7
8
9
10
11
12
|
#trigger_name为触发器的名称，trigger_time是触发器的时间（before是指在检查约束前触发，而after是在检查约束后触发），trigger_event就是触发器的触发事件，可以是insert、update或delete，tbl_name为表名，for each row是固定写法，trigger_stmt是激发执行的应用程序
create trigger trigger_name trigger_time trigger_event
on tbl_name for each row trigger_stmt
#为film表创建了alter insert的触发器
#在对ins_film进行插入操作之后，也会触发向film_text表中也插入相应的记录
delimiter $$
create trigger ins_film
after insert on film for each row
begin
insert into film_text (film_id,title,description) values(new.film_id,new.title,new.description);
#使用别名old和new来引用触发器中发生的变化的记录内容
|
只有表才支持触发器，视图不支持（临时表也不支持）
删除触发器
为了删除一个触发器，可以用drop trigger语句，如下所示：
触发器不能更新或者覆盖。为了修改一个触发器，必须先删除它，然后在重新创建。
查看触发器
可以通过两种方式查看触发器
1
2
3
|
show trigger \G
#查询系统表information_schema.trigger表
select * from trigger where trigger_name = 'ins_film_bef' \G
|
MySQL数据库是按照before触发器、行操作、alter触发器的顺序执行的，其中任何一部操作发生错误都不会继续执行剩下的操作。如果是对事物表进行的操作，那么会整个作为一个事务回滚，但是如果是对非事务表进行操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。
5.事务控制和锁定语句
什么是事务处理？
事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
下面是关于事务处理需要知道的几个术语：
- 事务（transaction）指一组SQL语句；
- 回滚（rollback）指撤销指定SQL语句的过程；
- 提交（commit）指将为存储的SQL语句结果写入数据库表；
- 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）
事务控制
管理事务处理的关键在与将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#开始事务
start transaction;
#提交事务，当commit或rollback语句执行后，事务会自动关闭（将来的更改会隐含提交）
commit;
#事务回滚
rollback；
#使用保留点，保留点越多越好，保留点在事务处理完成（执行一条rollback或commit）后自动释放。也可以用release savepoint明确地释放保留点
savepoint
（ savepoint delete1 #每个保留点都标识它的唯一名字，以便在回退时，MySQL知道要回退到何处
rollback to delete1
）
#更改默认的提交行为，autocommit标志决定是否自动提交更改，不管有没有commit语句。设置autocommit为0不自动提交，1为自动提交。（autocommit标志是针对每个链接而不是服务器的）
set autocommit=0
|
事务处理用来管理insert、update和delete语句。你不能回退select语句。（这样做也没有任何意义）你不能回退create或drop。事务处理块中可以使用者两条语句，但如果你执行回退，它们不会被撤销。
隔离机制
MySQL事务隔离机制
6.SQL中的安全问题
SQL注入简介
SQL注入（SQL Injection）就是利用某些数据库的外部接口将用户数据插入到实际的数据库操作语言（SQL）当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据查询语句的执行。
SQL注入攻击具有很大的危害，攻击者可以利用它读取、修改或者删除数据库内的数据，获取数据库中用户名和密码等敏感信息，甚至可以获得数据库管理员的权限，而且，SQL注入也很难防范。
应用开发中可以采取的应对措施
下面介绍一下常用的防范方法
- PrepareStatement+Bind-Variable
- 使用应用程序提供的转换函数
- 自己定义函数进行校验
数据库面试题(开发者必看)