🐒自己重新梳理一遍,也是一个消化的过程。学习一定要注重输入和输出,如果只输入的话,很多时候只是大脑此刻懂了,但并未记住。这样在实际做题或应用时,就无从下笔了。
存储 Stored
存储过程
和存储函数
可以将复杂的SQL逻辑封装在一起
,应用程序无需关注存储过程和函数内部复杂的SQL逻辑,只需要简单地调用存储过程和函数即可。
1. 存储过程 PROCEDURE
思想
- 一组经过
预先编译
的SQL语句的封装
执行过程
- 存储过程预先存储在MySQL服务器上
- 需要执行的时候,客户端只需要向服务器发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行。
优点:
- 可以一次编译多次使用——减少开发人员的压力
- 良好的封装性——减少失误,提高效率
- 减少网络传输量
- 存储过程的安全性强——减少SQL语句暴露的风险
缺点:
- 可移植性差
- 调试困难
- 存储过程的版本管理很困难
- 不适合高并发的场景
使用
- 像使用函数一样简单
- 可以
直接操作底层数据表
存储过程 没有 返回值
(1)创建存储过程
DELIMITER $ -- 新的结束标记
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, IN|OUT|INOUT 参数名 参数类型, ...)
[characteristics ...] -- 存储过程的约束条件
--LANGUAGE SQL
--| [NOT] DETERMINISTIC
--| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
--| SQL SECURITY { DEFINER | INVOKER } -- 安全级别(定义者可以调用 | 有权限就可以调用),默认为DEFINER
--| COMMENT 'string'
BEGIN
查询语句
END $
DELIMITER ; -- 恢复结束标记
- 常用的两种结束标记:
$
、//
SET
:赋值语句,对变量进行赋值。SELECT INTO
:将赋值语句的查询结果存放到变量中,也就是为变量赋值。- 五种输入参数
①没有参数(无参数无返回)
②IN
(由参数无返回)
③OUT
(无参数有返回)
④IN和OUT
(有参数有返回)
⑤INOUT
(有参数有返回)
(2)调用存储过程
CALL 存储过程名(实参列表)
(3)查看存储过程
①查看存储过程的创建信息
SHOW CREATE PROCEDURE 存储过程名
②查看存储过程的状态信息
SHOW PROCEDURE STATUS [LIKE '...']
(4)修改存储过程
ALTER PROCEDURE 存储函数名
(5)删除存储过程
DROP PROCEDURE IF EXIST 存储函数名;
2. 存储函数 FUNCTION
-
用于
查询
-
有且仅有一个返回值
(1)创建存储函数
DELIMITER //
CREATE FUNCTION 存储函数名(参数名, 参数类型)
RETURNS 返回值类型
# 为了避免报错,需要加上下面三行
DETERMINISTIC --
CONTAINS SQL -- 包含SQL
READS SQL DATA
BEGIN
RETURN(SELECT查询语句);
END //
DELIMITER; --把结束符号重新设为`;`
或
# 为避免报错,需要加这一句
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION 存储函数名(参数名1 参数类型, 参数名2 参数类型, ...)
RETURNS 返回值类型
BEGIN
RETURN(SELECT查询语句);
END //
DELIMITER; --把结束符号重新设为`;`
(2)调用存储函数
SELECT 存储函数名(); -- 调用函数
SET @emp_id := 102; -- 定义变量
# SET @emp_id = 102; `:=`和`=`都可以
SELECT 存储函数名(@emp_id); --调用函数
巧记:因为
存储函数
适用于查询,所以调用它也用SELECT
。
(3)查看存储函数
①查看存储函数的创建信息
SHOW CREATE FUNCTION 存储函数名
②查看存储函数的状态信息
SHOW FUNCTION STATUS [LIKE '...']
(4)修改存储函数
ALTER FUNCTION 存储函数名
(5)删除存储函数
DROP FUNCTION IF EXIST 存储函数名;
存储的补充知识
1. 变量
两种赋值符号都可以,效果一样
=
:=
(1)系统变量
系统变量用@@
开头
两种默认值
- 编译MySQL时参数的默认值
my.ini
MySQL配置文件中的参数值
修改系统变量的值
- 在MySQL服务运行期间,使用
SET
命令重新设置系统变量的值。 - 修改
my.ini
MySQL配置文件,继而修改MySQL系统变量的值(该方法需要重启MySQL服务)
默认为会话系统变量
①全局系统变量(简称:全局变量)
global
关键字
max_connections
:只能是全局系统变量,限制服务器的最大连接数- 查看所有全局变量
SHOW GLOBAL VARIABLES;
- 查看满足条件的部分全局变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';
- 查看指定全局变量
SELECT @@global.变量名;
为全局变量赋值
- 方式1
SELECT @@global.变量名; SET GLOBAL 变量名=变量值;
- 方式2
SELECT @@global.变量名; SET @@global.变量名=变量值;
②会话系统变量(简称:local变量)
session
关键字
-
会话系统变量的初始值是全局系统变量值的复制。
-
pseudo_thread_id
:作用于只能是当前会话,用于标记当前会话的MySQL连接ID。 -
查看所有会话变量
SHOW SESSION VARIABLES;
或SHOW VARIALBLES;
(因为变量
默认是会话系统变量
) -
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
-
查看指定会话变量
SELECT @@session.变量名;
或SELECT @@变量名;
为会话变量赋值
- 方式1
SELECT @@session.变量名; SET SESSION 变量名=变量值;
- 方式2
SELECT @@session.变量名; SET @@session.变量名=变量值;
(2)用户自定义变量
用户自定义变量用@
开头:会话用户变量(不用指明类型),局部变量(需要指明类型)
①会话用户变量
-
定义与赋值(需要加
@
,不需要指定类型)
1.手动赋值SET @用户变量 = 值; SET @用户变量 := 值;
2.赋
表中的字段值
SELECT @用户变量 := 表达式 [FROM 等字句]; SELECT 表达式 INTO @用户变量 [FROM 等字句];
-
作用域
只对当前会话有效
可以加在会话的任何地方 -
查看用户变量
SELECT @用户变量
②局部变量
-
定义(需要指定类型)
DECLARE 局部变量名 类型 [default 值]; # 如果没有default子句,初始值为NULL
-
赋值(不需要加
@
)
1.手动赋值SET 变量名=值 SET 变量名:=值
2.赋
表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
-
作用域
只在
BEGIN和END语句块
中有效,且只能放在第一句
。
只能在存储过程和函数中使用 -
使用变量
SELECT 局部变量名
DELIMITER //
CLEAR PROVEDURE/FUNCTION 存储过程名/存储函数名
BEGIN
# 声明局部变量
DECLARE 局部变量名 类型 DEFAULT 默认值;
# 为局部变量赋值
SET 局部变量名 = 值;
SELECT 字段名 INTO 局部变量名 FROM 表名 WHERE 筛选条件;
# 查看局部变量的值
SELECT 变量1, 变量2, ...;
END //
DELIMITER ;
2. “异常”处理
为了避免程序一报错就停止运行,可以实现将可能发生的错误列出来,当错误出现时,只用报相应的错误代码,而保证程序能够顺利执行完毕。
(1)定义条件
即:给
实现定义程序执行过程中可能遇到的问题命名
。将一个错误名字
和指定的错误条件
关联起来。
# MySQL_error_code
DECLARE 错误名 CONDITION FOR 数值类型错误码
# sqlstate_value
DECLARE 错误名 CONDITION FOR SQLSTATE 字符串类型错误码
例:在ERROR 1418(HY000)
中:
1418
是MySQL_error_code
(数值类型错误代码
),
HY000
是sqlstate_value
(长度为5的字符串类型错误代码
)
DECLARE field_Not_Be_NULL CON
(2)处理程序
即:定义在遇到问题时应当采取的处理方式,保证存储过程或函数
在遇到警告或错误时能继续执行。
- 增强存储程序处理问题的能力,避免程序异常停止运行。
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
处理方式
CONTINUE
:遇到错误不处理
,继续执行。EXIT
:遇到错误马上退出
错误类型
SQLSTATE
‘字符串错误码’:表示长度为5的sqlstate_value类型的错误代码MySQL_error_code
:匹配数值类型错误代码错误名称
:表示DECLARE...CONDITION
定义的错误条件名称SQLWARNING
:匹配所有以01
开头的SQLSTATE
错误代码;NOT FOUND
:匹配所有以02
开头的SQLSTATE
错误代码;SQLEXCEPTION
:匹配所有没有被SQLWARNING
或NOT FOUND
捕获的SQLSTATE
错误代码;
处理语句
SET 变量 = 值
BEGIN...END
定义处理程序的几种方式,代码如下:
#方法1:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2:捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3:先定义条件,再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
3. 流程控制
(1)条件判断语句IF
CASE
①IF
用在BEGIN...END
中
DELIMITER //
CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()
[标记名:]BEGIN
IF ...
THEN ... -- LEAVE 标记名
ELSEIF ...
THEN ...
ELSE
END IF;
END //
DELIMITER ;
②CASE
用于BEGIN...END
中
-
情况1(类似与switch)
CASE 表达式 WHEN 值1 THEN 结果1或语句1(语句1的结尾要加;) WHEN 值2 THEN 结果2或语句2(语句1的结尾要加;) ... ELSE 结果n或语句n(语句n的结尾要加;) END CASE
CASE 变量 WHEN 值1 THEN 结果1或语句1(语句1的结尾要加;) WHEN 值2 THEN 结果2或语句2(语句1的结尾要加;) ... ELSE 结果n或语句n(语句n的结尾要加;) END AS 别名
-
情况2(类似于多重IF)
CASE WHEN 条件1 THEN 结果1或语句1(语句1的结尾要加;) WHEN 条件2 THEN 结果2或语句2(语句1的结尾要加;) ... ELSE 结果n或语句n(语句n的结尾要加;) END CASE
(2)循环语句LOOP
WHILE
REPEAT
三种循环都可以省略名称。
但如果循环中添加了循环控制语句(LEAVE或ITERATE),则必须添加名称
。
①LOOP(一般用于实现简单的"死"循环。(通常要结合LEAVE使用))
DELIMITER //
CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()
BEGIN
# 定义变量,用于记录循环次数
DECLARE loop_count INT DEFAULT 0;
# ①初始化循环条件
SELECT ...
[标记名:]Loop
# ②循环条件(何时结束循环)
[IF ... THEN LEAVE [标记名];
END IF;]
[IF ... THEN ITERATE [标记名];
END IF;]
# ③循环体——循环要完成的任务
... -- 根据题目要求写
# ④迭代条件
...; -- 重新计算循环条件(与初始化循环条件一致)
SET loop_count = loop_count +1; -- 循环次数+1
END LOOP loop名;
END //
DELIMITER ;
②WHILE(先判断后执行)
DELIMITER //
CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()
BEGIN
# ①初始化循环条件
# ②循环条件
[标记名]:WHILE 循环条件 DO
# ③循环体
# ④迭代条件
[IF ...
LEAVE 标记名;
END IF;]
END WHILE;
END //
DELIMITER ;
③REPEAT(先执行后判断,无条件至少执行一次)
DELIMITER //
CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()
BEGIN
REPEAT
循环
UNTIL -- 结束循环的条件。注意:这句没有`;`
END REPEAT;
END //
DELIMITER ;
(3)跳转语句ITERATE
LEAVE
①LEAVE
类似于break
LEAVE 标记名
②ITERATE
类似于continue
只能用在LOOP
/REPEAT
/WHILE
循环语句中
ITERATE 标记名
4. 触发器
事件:用户的动作或触发某项行为,包括INSERT
, UPDATE
, DELETE
。
当执行INSERT
, UPDATE
, DELETE
事件时,会自动激发触发器执行相应的操作。
(1)触发器的创建
DELIMITER //
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 -- `表名`为触发器监控的对象
FOR EACH ROW
BEGIN
触发器执行的语句块;
-- VALUE(NEW.字段名)
-- VALUE(OLD.字段名)
END //
DELIMITER ;
BEFORE
表示在事件之前
触发AFTER
表示在事件之后
触发INSERT
表示插入记录
时触发UPDATE
表示更新记录
时触发DELETE
表示删除记录
时触发NEW
关键字用于调用最新插入
的数据(用在触发器执行的语句块
的VALUE
语句中,如VALUE(NEW.字段名)
可用于查看该字段最新插入的数据值。)OLD
关键字用于调用最近删除
的数据(用在触发器执行的语句块
的VALUE
语句中,如VALUE(OLD.字段名)
可用于查看该字段最近删除的数据值。)
验证触发器是否起作用
注:与上方创建触发器的代码中的BEGIN...END
中对应
BEGIN
触发器执行的语句块这个语句块可用于将触发后的操作记录在
指定表
中。
.
这样,当被触发器监控的对象触发操作时,这个指定表
就会记录相应的内容。
.
通过SELECT * FROM 指定表;
,可以查看是否触发了触发器。
END
(2)查看触发器
- 查看当前数据库的
所有
触发器的定义
SHOW TRIGGERS
- 查看当前数据库中
某个
触发器的定义
SHOW CREATE TRIGGER 触发器名
- 从系统库
information_schema
的TRIGGER
表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;
(3)删除触发器
DROP TRIGGER IF EXISTS 触发器名称
(4)触发器的优缺点
优点
- 确保数据的完整性
- 帮助我们记录操作日志
- 在操作数据前,对数据进行合法性检查
缺点
- 可读性差(触发器由事件驱动,不受应用层的控制)
- 相关数据的变更,可能会导致触发器出错
- 如果在子表中定义了外键约束,并且外键指定了DML操作,此时修改父表也会引起子表的相应操作。但是,基于子表的相应
触发器不会被激活
。