MySQL之视图、存储过程

欧尼熊不懂

已于 2022-09-15 23:40:05 修改

阅读量826

点赞数

文章标签： mysql 数据库 sql

于 2022-09-15 00:09:06 首次发布

本文链接：https://blog.csdn.net/qq_66991094/article/details/126862865

版权

视图

基本概念

视图是一个虚拟表
- 内容由查询定义，和真实的表一样
视图根据基表创建
- 可以是多个基表
包含字段，数据源于对应的真实表（基表）
- 视图本身不存储数据

使用

语法

CREATE VIEW <视图名> AS <SELECT语句>;			-- 创建视图语法，使用 select 语句中获取的数据显示在视图中

create view view_name as select 语句;				-- 创建视图

alter view view_name as select 语句;				-- 修改视图

show create view view_name;						-- 显示视图创建指令

drop view view_name1,view_name2;				-- 删除（多个）视图

示例

-- 创建视图user_view， 只能查看 user 表中的 name、id、age 字段
create view user_view 
as select name, id, age from user

select name, id, age from user_view;			-- 从视图表 user_view 中查询数据
	
show create view user_view;						-- 查看创建视图 user_view 的指令
	
alter view user_view 
as select id,name from user;					-- 修改视图 user_view: 在视图中只显示 id 和 name 字段数据		
		
drop view user_view;							-- 删除视图

注意事项

视图创建后在磁盘中只有结构文件，没有数据文件
- 数据存储在基表中，select 语句定义视图的内容
- 每次使用视图都必须执行查询中所需的任何一个检索操作
  - 如果用多个连接和过滤条件创建复杂的视图或嵌套视图，性能消耗会非常大
视图的建立和删除只影响视图本身，不影响对应的基本表
- 通过视图可以修改基表数据，基表的数据改变也会影响视图
  - 因为视图中不存储数据，数据来自基表
视图可以嵌套，即从其他视图中检索数据的查询来创建视图
- 视图就是特殊的表
创建视图需要足够的访问权限
创建视图的数目没有限制
视图不能索引，也不能有关联的触发器、默认值或规则
视图可以和表一起使用

使用场景

视图可隐藏基表的细节，应用于保密性比较高的系统
数据库只对外开放相关视图
- Java 程序员对视图对象 CRUD
安全
- 表中有些字段存储重要信息，需要保密不能让用户直接看到
  - 可以创建视图只保留一部分字段对外公开
性能
- 关系数据库的数据通常会分表存储，使用外键建立表的联系
  - 此时查询操作通常用到 join，麻烦而且效率低
- 创建视图将相关表和字段结合在一起可避免使用 join 查询数据
灵活
- 原表因为设计问题即将废弃，但很多应用基于此表，不易修改
- 可创建视图，视图中的数据映射到原表，可以减少很多改动达到升级数据表的目的

存储过程

介绍

存储过程(Stored Procedure)：一组用于完成特定数据库功能的SQL语句集，经过编译后存储在数据库系统中
- 使用时用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作
即数据库 SQL 语言层面的代码封装与重用
- MySQL 5.0 版本开始支持存储过程
优点
1. 存储过程可封装，隐藏复杂的逻辑
2. 有助于提高性能
  - 创建存储过程被编译后存储在数据库中
    - MySQL实现的存储过程略有不同：按需编译
    - 编译存储过程后 MySQL 将其放入缓存，为每个连接维护自己的存储过程高速缓存
    - 应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询
3. 有助于减少应用程序和数据库服务器之间的流量
  - 应用程序不必发送多个冗长的SQL语句，只能发送存储过程的名称和参数
4. 存储的程序对任何应用程序都是可重用的和透明的
  - 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能
5. 存储的程序是安全的
  - 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限
缺点
1. 如果使用大量存储过程，那么使用的每个连接的内存使用量将会大大增加
  - 如果您存储过程中过度使用大量逻辑操作，则CPU使用率也会增加
    - 数据库服务器的设计不当于逻辑运算
2. 开发具有复杂业务逻辑的存储过程变得更加困难
  - 很难调试存储过程。只有少数数据库管理系统允许调试
    - MySQL不提供调试存储过程的功能
3. 开发和维护存储过程并不容易
  - 开发和维护存储过程通常需要专业技能

使用

基本语法

# 创建简单存储过程
delimiter $$ 			-- 修改语句结束符
	create procedure 存储过程名(参数类型 参数名 参数数据类型)
		begin			-- 开始语句
		-- 过程主体部分，若只有一条 SQL 语句，可省略 BEGIN-END 标志
		end && 			-- 结束语句
delimiter ; 			-- 恢复语句结束符

# 调用存储过程
call 存储过程名(参数)

# 删除存储过程
drop procedure [ IF EXISTS ] 存储过程名称;

# 查看存储过程
SHOW PROCEDURE STATUS LIKE '存储过程名';  
# 查看指定数据库中的存储过程
select name from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';

# 查看指定存储过程定义
SHOW CREATE PROCEDURE [数据库.]存储过程名;

# 修改存储过程
ALTER PROCEDURE 存储过程名 [ 特征 ... ];

特征：指定存储过程的特性，可能取值有：
- CONTAINS SQL：表示子程序包含 SQL 语句，但不包含读或写数据的语句
- NO SQL：表示子程序中不包含 SQL 语句
- READS SQL DATA：表示子程序中包含读数据的语句
- MODIFIES SQL DATA：表示子程序中包含写数据的语句
- SQL SECURITY { DEFINER |INVOKER }：指明谁有权限来执行
  - DEFINER：表示只有定义者自己才能够执行
  - INVOKER：表示调用者可以执行
- COMMENT 'string'：表示注释信息
ALTER PROCEDURE 语句修改存储过程的某些特征
- 修改存储过程的内容：先删除原存储过程，以相同的命名创建新的存储过程
- 修改存储过程的名称：先删除原存储过程，以不同的命名创建新的存储过程

示例

# 创建简单存储过程
delimiter $$						-- 修改标准语句分隔符为 &&
 create procedure Demo()			-- 没有参数也必须有 ()
   begin							-- 开始单个语句，直到 && 结束
   -- 主体部分，SQL 语句处理业务逻辑
   end $$							-- 结束语句，可以有多个语句
delimiter;							-- 恢复分隔符

# 调用存储过程
call Demo();

# 删除存储过程
drop procedure if exists Demo;

# 查看存储过程
show procedure status like 'Demo';
# 查看指定数据库中的存储过程
select name from mysql.proc where db='demo';
select routine_name from information_schema.routines where routine_schema='demo';
show procedure status where db='demo';

# 查看指定存储过程定义
SHOW CREATE PROCEDURE demo.Demo;  

# 修改存储过程特征：将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行
ALTER PROCEDURE Demo MODIFIES SQL DATA SQL SECURITY INVOKER;

参数

参数类型：IN、OUT、INOUT

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin ......... end

IN

输入参数；一般只用于传入，在调用过程中一般不作为修改和返回
表示调用者向过程传入值，可以是字面量或变量
- 参数的值必须在调用存储过程时指定
不显示指定参数类型默认是 in 类型

delimiter $$
 create procedure Demo(in num2 int)			-- 没有参数也必须有 ()
   begin	
		select num2;						-- 显示 null
		set num2 = 7;						-- 参数赋值
		select num2;						-- 显示 7
   end $$
delimiter;

# 调用存储过程
call Demo(@num2);							-- 显示 null，第一个查询
call Demo(5);								-- 显示 5，第一个查询
select @num2;								-- null，未赋值

OUT

输出参数；调用存储过程中，可改变其值并返回

表示过程向调用者传出值，可返回多个值
调用存储过程时 out 参数也需要指定，但必须是变量，不能是常量

delimiter $$
 create procedure Demo2(out num int)			-- 没有参数也必须有 ()
   begin	
		set num = 6;							-- 设置变量 num，修改变量值为 6
		select num;								-- 返回变量
   end $$
delimiter;

# 调用存储过程
call Demo2(@num);								-- 结果为 null
select @num										-- 变量值 @num = 6

INOUT

输入输出参数
- 既表示调用者向过程传入值，又表示过程向调用者传出值
- 调用时可传入值，调用过程中可修改其值，也可返回值
- 调用时传入的是变量，而不是常量
建议
- 输入值使用in参数，返回值使用out参数
  - inout参数就尽量的少用
- 参数的取名不要与数据表的列名相同
  - 尽管不会返回出错信息，但存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果
- 没有参数也必须在过程名后面写上小括号

变量

作用

变量具有数据类型和长度
- 与mysql的SQL数据类型保持一致
- 能制定默认值、字符集和排序规则等
通过 set 赋值变量
- SET 语句可同时为多个变量赋值，各变量的赋值语句之间用逗号隔开
- 或通过 select ... into ... 方式赋值
  - ```
  SELECT col_name [...] INTO var_name[,...]
  FROM table_name WEHRE condition
```
- 将查询结果赋值给变量时，该查询语句的返回结果只能是单行
使用 select 语句返回变量
- 如：select 变量名;

类型

局部变量

declare 声明变量
- 一个 declare 只声明一个变量
局部变量必须先声明后使用
- 仅用在 BEGIN ... END 复合语句中，且必须在开头任何其它语句之前
可用在嵌套的块中，相同名字声明变量的块除外
DEFAULT 子句设值默认值
- 值可以是常数，或指定为表达式)
- 默认初始值为 NULL
MySQL不支持数组作为局部变量

delimiter $$
create procedure demo(out num1 int)						-- 输出参数
begin
declare num2 int default(select count(*) from PLAYERS);	-- 定义局部变量，默认值为 sql 语句结果
set num1 = num2;										-- 赋值
end $$		
delimiter ;
call demo(@num);										-- 调用存储过程，虽然是输出参数但必须指定
select @num;											-- 查询结果

用户变量

与数据库连接有关
当前连接中声明的变量，连接断开的时消失
- 此连接中声明的变量无法在另一连接中使用
用户变量名一般以 @ 开头
- 用户变量随处可以定义，随处可以使用
  - 不定义也可以直接使用，值默认为 null
- 用户变量的变量名形式：@var_name，有 @ 符号

赋值

set 语句
- 可使用 = 或 := 作为分配符
- 变量可为整数、实数、字符串或者NULL值

select 语句

分配符必须为 :=，不能用 =
- 非 SET 语句中 = 被视比较操作符

set @a = 1;				-- 定义用户变量 @a，值为 1
select @a;				-- 查询变量 @a
select @b := 2;   		-- 定义用户变量 @b，值为 2，并查询
select @b;				-- 查询变量 @

区别局部变量
- 局部变量只有变量名字，没有 @ 符号
  - 用户变量名前有 @ 符号
- 都是先定义，再使用
  - 未定义的变量，select 值为 null
- 局部变量只在存储过程内部使用，在过程体外没有意义
  - begin-end块处理完后，局部变量消失
  - 用户变量可以用在存储过程的内部和外部
  - 在存储过程内部，使用局部变量，不要使用用户变量

系统变量

根据系统变量的作用域分为：全局变量 、会话变量
- 通过 @@ 或 global 操作系统变量
```
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;
```
- MySQL 变量类似于动态语言，变量值随所要赋的值的类型而改变
全局变量：@@global
- MySQL 启动时由服务器自动将全局变量初始化为默认值
- 默认值可通过更改配置文件 my.ini、my.cnf 修改
会话变量：@@session
- 每次建立新的连接时由 MySQL 初始化
- 建立会话后没有手动更改过会话变量与全局变量。那么这些变量的值都一样的
- 全局变量的修改影响到整个服务器，会话变量的修改，只影响当前的会话
set 系统变量时，不带作用域修饰默认指会话作用域
- 部分系统变量不带作用域修饰无法设置，因此最好都带上作用域设置系统变量

存储函数

定义

和存储过程一样，都是在数据库中定义一些 SQL 语句的集合
- 存储函数和存储过程的查看、修改、删除等操作几乎相同
存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值
- 存储过程没有直接返回值，主要用于执行操作
CREATE FUNCTION 语句创建存储函数
- ```
CREATE FUNCTION 函数名称 ([func_parameter [, ...]])
RETURNS type
[characteristic ...]
routine_body
```
  - func_parameter：函数的参数列表
    - 参数列表可由多个参数组成
    - 每个参数由参数名称和参数类型组成：[IN | OUT | INOUT] param_name type;
  - RETURNS type：指定返回值的类型
    - 如果 RETURN 语句返回类型不同于函数指定类型的值，返回值将被强制为恰当的类型
    - 例：函数返回 ENUM 或 SET 值，但 RETURN 语句返回整数
      - 对 SET 成员集的相应 ENUM 成员，从函数返回的值是字符串
  - characteristic 参数：指定存储函数的特性
    - 取值与存储过程一样
  - routine_body 参数：表示 SQL 代码的内容
    - 可用 BEGIN...END 来标示 SQL 代码的开始和结束
- 创建函数时函数名不允许重复
  - 推荐函数名命名（标识符）：function_xxx 或 func_xxx

查询

# 查询函数
SHOW FUNCTION STATUS LIKE 存储函数名;
SHOW CREATE FUNCTION 存储函数名;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 存储函数名;

# 修改函数
ALTER FUNCTION 存储函数名 [ 特征 ... ]；  -- 存储函数的特征与存储过程的基本一样

# 删除存储过程
DROP FUNCTION [ IF EXISTS ] <函数名>；

调用
- 存储函数的使用方法与 MySQL 内部函数的使用方法是一样的
  - 用户自己定义的存储函数与 MySQL 内部函数性质一样

示例

DELIMITER $$
CREATE FUNCTION func_student(id int(11))		-- 创建存储函数
RETURNS VARCHAR(20)								-- 指定返回值类型
COMMENT '查询某个学生的姓名'						 -- 指定注释特征
BEGIN											-- 开始
RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);		-- 函数体，执行sql
END $$
DELIMITER ;
-- 该函数拥有一个类型为 INT(11) 的参数 id，返回值为 VARCHAR(20) 类型
-- SELECT 语句从 tb_student 表中查询 id 字段值等于所传入参数 id 值的记录，同时返回该条记录的 name 字段值

SELECT func_student(3);   -- 调用自定义函数

定义条件

定义

事先定义程序执行过程中遇到的问题，定义遇到这些问题时应当采取的处理方式和解决办法
- 保证存储过程和函数在遇到警告或错误时能继续执行
- 增强程序处理问题的能力，避免程序出现异常被停止执行
DECLARE 关键字定义条件
- ```
DECLARE 条件名称 CONDITION FOR condition_value;
```
  - condition_name 参数表示条件的名称
  - condition_value 参数表示条件的类型
    - sqlstate_value ：表示长度为 5 的字符串类型错误代码
    - mysql_error_code：表示数值类型错误代码
      - 例：ERROR 1146(42S02) 中，sqlstate_value 值是 42S02，mysql_error_code 值是 1146

示例

# sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';

# mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;

处理程序

定义

DECLARE 关键字定义处理程序。其基本语法如下：
- ```
DECLARE handler_type HANDLER FOR condition_value[...] sp_statement
```
  - handler_type 参数：指明错误的处理方式
    - 该参数有 3 个取值：CONTINUE、EXIT 和 UNDO
      - CONTINUE：遇到错误不进行处理，继续向下执行
      - EXIT：遇到错误后马上退出
      - UNDO：遇到错误后撤回之前的操作，MySQL 暂不支持
    - 通常执行过程中遇到错误应该立刻停止执行并撤回操作
  - condition_value：指明错误类型，有 6 个取值
    - sqlstate_value：包含 5 个字符的字符串错误值
    - condition_name：表示 DECLARE 定义的错误条件名称
    - SQLWARNING：匹配所有以 01 开头的 sqlstate_value 值
    - NOT FOUND：匹配所有以 02 开头的 sqlstate_value 值
    - SQLEXCEPTION：匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_value 值
    - mysql_error_code：匹配数值类型错误代码
  - sp_statement 参数：程序语句段
    - 表示遇到定义的错误时，需要执行的一些存储过程或函数

示例

# 捕获 sqlstate_value：遇到 sqlstate_value 值为 42S02，执行 CONTINUE 操作并输出 CAN NOT FIND 信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';

# 捕获 mysql_error_code：遇到 mysql_error_code 值为 1146， 执行 CONTINUE 操作并输出 CAN NOT FIND 信息
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';

# 先定义条件，然后调用：先定义 can_not_find 条件，遇到 1146 错误就执行 CONTINUE 操作
DECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';

# 使用 SQLWARNING：捕获所有 01 开头的 sqlstate_value 值，然后执行 EXIT 操作并输出 ERROR 信息
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 操作并输出 ERROR 信息
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

游标

MySQL 中存储过程或函数的查询有时返回多条记录
- 简单的 SELECT 语句无法得到指定数据，使用游标逐条读取查询结果集中的记录
- 游标在部分资料中也被称为光标
一般通过游标定位到结果集的某一行进行数据修改
- 结果集是符合 SQL 语句的所有记录的集合
- 不像多数 DBMS，MySQL 游标只能用于存储过程和函数

声明

DECLARE 关键字声明游标，并定义相应的 SELECT 语句
- 根据需要添加 WHERE 和其它子句
- ```
DECLARE cursor_name CURSOR FOR select_statement;
```
  - cursor_name：游标名称
  - select_statement ：SELECT 语句，可以返回一行或多行数据

打开

想从游标中提取数据，必须首先打开游标
- MySQL 通过 OPEN 关键字打开游标
- OPEN cursor_name;
  - cursor_name：所要打开游标的名称
  - 打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边
程序中一个游标可以打开多次
- 用户打开游标后，其他用户或程序可能正在更新数据表，所以有时会导致用户每次打开游标后显示的结果都不同

使用

使用 FETCH...INTO 语句读取数据
- ```
FETCH cursor_name INTO var_name [,var_name]...
```
  - 将游标 cursor_name 中 SELECT 语句的执行结果保存到变量参数 var_name
    - 变量参数 var_name 必须在游标使用之前定义
使用游标类似高级语言中的数组遍历
- 当第一次使用游标时，此时游标指向结果集的第一条记录
MySQL 的游标是只读的
- 只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录

关闭

游标使用完毕后，要及时关闭
- MySQL 中使用 CLOSE 关键字关闭游标
- CLOSE cursor_name;
CLOSE 释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭
一个游标关闭后，如果没有重新打开，则不能使用它
- 但使用声明过的游标不需要再次声明，用 OPEN 语句打开即可
- 如果不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭
- 游标关闭后不能使用 FETCH 使用该游标

流程控制

if

进行条件判断
- ```
if search_condition then statement_list
    [elseif search_condition then statement_list] ...
[else statement_list]
end if
```
  - search_condition：条件判断语句
    - 返回值 TRUE 执行 then 后语句列表 statement_list
    - 返回值 FALSE，则 ELSE 子句的语句列表被执行
      - statement_list 可以包括一个或多个语句
  - 使用 end if 结束 if 语句
MySQL 中的 IF( ) 函数不同于 IF 语句

case

进行条件判断
- 提供了多个条件进行选择，可实现比 IF 语句更复杂的条件判断
- ```
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
```
  - case_value：条件判断的变量，决定哪一个 WHEN 子句会被执行
  - when_value：变量的取值
    - when_value 表达式与 case_value 变量值相同，执行对应 THEN 关键字后的 statement_list 中的语句
    - 没有条件匹配，ELSE 子句里的语句被执行
  - CASE 语句都要使用 END CASE 结束
另一种形式
- ```
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
```
  - 该语句中 WHEN 语句将被逐个执行
    - 直到 search_condition 表达式为真，执行对应 THEN 关键字后的 statement_list 语句
    - 没有条件匹配 ELSE 子句里的语句被执行
CASE 语句与 SQL CASE 表达式的 CASE 语句有轻微不同
- CASE 语句不能有 ELSE NULL 语句，且用 END CASE 替代 END 终止

loop

可以使某些特定的语句重复执行
- 只实现了一个简单的循环，并不进行条件判断
- LOOP 语句本身没有停止循环的语句，必须使用 LEAVE 语句等才能停止循环，跳出循环过程

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

begin_label 和 end_label ：循环开始和结束的标志
- 两个标志必须相同，且都可以省略
statement_list：需要循环执行的语句
LOOP 循环都以 END LOOP 结束

例

add_num:LOOP
    SET @count=@count+1;
END LOOP add_num;
-- 循环执行 count 加 1 操作，没有跳出循环的语句，循环成为死循环

leave

用于跳出循环控制，类似 Java 的 return
- LEAVE label
- label ：循环的标志，LEAVE 语句必须跟在循环标志前面

例

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
END LOOP add num;
-- 循环执行 count 加 1 的操作，当 count 的值等于 100 时，跳出循环

iterate

再次循环；用来跳出本次循环，直接进入下一次循环；类似 Java 的 continue
- ITERATE label
- label：循环的标志，ITERATE 语句必须跟在循环标志前面

例

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
    ELSE IF MOD(@count,3)=0 THEN
        ITERATE add_num;
    SELECT * FROM employee;
END LOOP add_num;
-- 循环执行 count 加 1 操作，count 值为 100 时结束循环
-- 如果 count 的值能够整除 3，则跳出本次循环，不再执行下面的 SELECT 语句

repeat

条件控制的循环语句；类似 Java 中 do … while()
- 每次语句执行完毕后对条件表达式进行判断
  - 表达式返回值为 TRUE，则循环结束
  - 否则重复执行循环中的语句
- ```
[begin_label:] repeat
  statement_list
  until search_condition
end repeat [end_label]
```
  - begin_label：为 REPEAT 语句的标注名称，可省略
  - REPEAT 内语句重复执行，直至 search_condition 返回值为 TRUE
  - REPEAT 循环都用 END REPEAT 结束

例

REPEAT
    SET @count=@count+1;
    UNTIL @count=100
END REPEAT;
-- 循环执行 count 加 1 的操作，count 值为 100 时结束循环

while

条件控制的循环语句
- 当满足条件时，执行循环内的语句，否则退出循环
- ```
[begin_label:] 
WHILE search_condition 
	DO statement list
END WHILE [end label]
```
  - search_condition：循环执行的条件，满足该条件时循环执行
  - WHILE 循环需要使用 END WHILE 结束

例

WHILE @count < 100 
	DO SET @count=@count+1;
END WHILE;
-- 循环执行 count 加 1 的操作，count 值小于 100 时执行循环。如果 count 值等于 100 了，则跳出循环

触发器

作用

嵌入到 MySQL 中的一段程序，MySQL 中管理数据的有力工具
- 执行存储过程使用 CALL 语句调用
- 触发器的执行不需要使用 CALL 语句调用，也不需要手工启动
  - 通过对数据表的相关操作来触发、激活从而实现执行
  - 例：对指定表进行操作（INSERT，DELETE 或 UPDATE）时激活执行
触发器与数据表关系密切，主要用于保护表中的数据
- 当多个表具有一定的相互联系时，触发器能够让不同的表保持数据的一致性
MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作才能激活触发器
- 其它 SQL 语句不会激活触发器
数据表发生更改时需要自动进行一些处理，可以使用触发器
- 对于一张表中记录进行操作后需要同步操作其他表中相关记录，使用触发器完成
优点
1. 自动执行
  - 当对触发器相关表的数据做出相应的修改后立即执行
2. 可实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作
3. 可实现表数据的级联更改
  - 一定程度上保证了数据的完整性。
缺点
1. 使用触发器实现的业务逻辑在出现问题时很难进行定位
  - 特别是涉及到多个触发器的情况下，会使后期维护变得困难
2. 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性
3. 如果需要变动的数据量较大时，触发器的执行效率会非常低

类型

触发器使用的过程中，MySQL 按照以下方式处理错误
- 对于事务性表：如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚
- 对于非事务性表：不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效
- 若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作
- 若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，将导致调用触发程序的整个语句失败
- 仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序

INSERT

INSERT 语句执行之前或之后响应的触发器
注意
1. 触发器代码内可引用名为 NEW（不区分大小写）的虚拟表来访问被插入的行
2. 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新
  - 允许更改被插入的值，需要具有对应的操作权限
3. 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值

UPDATE

UPDATE 语句执行之前或之后响应的触发器
注意
1. UPDATE 触发器代码内可引用名为 NEW（不区分大小写）的虚拟表访问更新的值
2. UPDATE 触发器代码内可引用名为 OLD（不区分大小写）的虚拟表访问 UPDATE 语句执行前的值
  - OLD 中的值全部是只读的，不能被更新
3. BEFORE UPDATE 触发器中，NEW 中的值可能也被更新
  - 允许更改将要用于 UPDATE 语句中的值，需要具有对应的操作权限
4. 触发器涉及对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许

DELETE

DELETE 语句执行之前或之后响应的触发器
注意：DELETE 触发器代码内可引用名为 OLD（不区分大小写）的虚拟表来访问被删除的行
- OLD 中的值全部是只读的，不能被更新

创建

语法

CREATE TRIGGER 语句创建触发器
```
CREATE TRIGGER <触发器名> 
< BEFORE | AFTER > <INSERT | UPDATE | DELETE >
ON <表名> 
FOR EACH Row
<触发器主体>
```
- 触发器名：触发器在当前数据库中必须具有唯一的名称
  - 如果要在某个特定数据库中创建，名称前面应该加上数据库的名称
- INSERT | UPDATE | DELETE：触发事件，指定激活触发器的语句的种类
  - INSERT：将新行插入表时激活触发器
    - 例：INSERT 的 BEFORE 触发器能被 MySQL 的 INSERT 语句和 LOAD DATA 语句激活
  - DELETE：从表中删除某一行数据时激活触发器、
    - 例：DELETE 和 REPLACE 语句
  - UPDATE：更改表中某一行数据时激活触发器
    - 例：UPDATE 语句
- BEFORE | AFTER：触发器被触发的时刻，表示触发器是在激活语句之前或之后触发
  - 若希望验证新数据是否满足条件使用 BEFORE 选项
  - 若希望在激活触发器的语句执行之后完成几个或更多的改变通常使用 AFTER 选项
- 表名
  - 与触发器相关联的表名，必须是永久性表
    - 不能将触发器与临时表或视图关联
  - 在该表上触发事件发生时才会激活触发器
    - 同一个表不能拥有两个具有相同触发时刻和事件的触发器
      - 每个表最多支持 6 个触发器
    - 每个表的每个事件每次只允许有一个触发器
      - 单一触发器不能与多个事件或多个表关联
- 触发器主体
  - 包含触发器激活时将要执行的 SQL 语句
  - 若要执行多个语句可使用 BEGIN…END 复合语句结构
- FOR EACH ROW：一般是指行级触发
  - 对于受触发事件影响的每一行都要激活触发器的动作

示例

# 创建 before 类型触发器 Demo
-- 触发条件：向表 tab_name 中插入数据前对新插入的 salary 字段值进行求和计算
create trigger Demo
before insert on tab_name
for each row
set @sum = @sum + NEW.salary;

# 创建 after 类型触发器 Demo2
-- 触发条件：向表 tab_name 中插入数据后，再向表 tab_name2 插入相同数据，且 salary 为 tab_name 中插入 salary 值的 2 倍
create trigger Demo2
after insert on tab_name
for each row
insert into tab_name2 values (NEW.id, NEW.name, deptId, 2*NEW.salary);

查看

SHOW TRIGGERS 语句查看数据库中的触发器及基本信息
- SHOW TRIGGERS 命令后添加 \G，显示信息比较有条理
- 无法查询指定的触发器
```
SHOW TRIGGERS [\G];    -- 查看当前数据库的触发器信息
```
- 触发器字段解释
  - Trigger：触发器的名称
  - Event：激活触发器的事件
  - Table：激活触发器的操作对象表
  - Statement：触发器执行的操作
  - Timing：触发器触发的时间
  - 还有其他信息，比如触发器的创建时间、SQL 的模式、触发器的定义账户和字符集等

triggers 表

MySQL 中所有触发器的信息都存在 information_schema 数据库的 triggers 表中
可以通过查询命令 SELECT 查看

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';  -- 指定要查看的触发器的名称
SELECT * FROM information_schema.triggers \G;   -- 不指定名称，查看所有触发器

删除

修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。
- 使用 DROP 语句将触发器从数据库中删除
  - 需要 SUPER 权限
```
DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>
```
  - 数据库名：指定触发器所在的数据库的名称
    - 若没有指定默认为当前数据库
删除表的同时会自动删除该表上的触发器
- 触发器不能更新或覆盖，修改一个触发器，必须先删除它再重新创建