一、触发器
触发器：在满足某种条件的时候，被动执行的SQL语句。
特性：
有begin、end的结构体（针对于多条sql语句）
需要指定触发的条件(触发时机)：INSERT，UPDATE，DELETE
有指定的触发时间：BEFORE，AFTER
使用时机说明：
BEFORE|AFTER INSERT用于获取将要插入的数据
BEFORE|AFTER UPDATE|DELETE用于获取已经修改或删除的数据
提前准备测试表
DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
1.1、创建触发器
单条业务逻辑
单条业务逻辑：即触发条件成立执行一条sql语句
-- 添加触发器：插入到student表后，触发插入课程记录一条 CREATE TRIGGER trigger_insert -- 触发器名称 AFTER INSERT -- 插入后触发 ON student -- 指定表 FOR EACH ROW INSERT INTO course(name) VALUES('英语课') -- 执行触发器的操作 -- 测试语句 insert student(name,sex) values('xiaotian','男')
多条业务逻辑
多条业务逻辑：即触发条件成立执行多条sql语句，需要使用begin end包裹多条sql语句
-- 添加触发器：在插入学生表一条记录前插入course表两条记录 DELIMITER $ -- 设置默认结束符为$，原本是;表示提交结束符 CREATE TRIGGER trigger_insert_before -- 触发器名称 BEFORE INSERT -- 插入前触发 ON student -- 指定表 FOR EACH ROW BEGIN INSERT INTO course(name) VALUES('数学课'); INSERT INTO course(name) VALUES('语文课'); END;$ -- 将触发器进行提交 -- 测试语句 insert student(name,sex) values('xiaotian','男')
注意点：默认结束提交符号为;，在多条业务逻辑中SQL语句需要以;为结尾，所以需要使用DELIMITER $来设置结束符号。
区别：单条业务的话不需要设置结束符号；多条业务的话需要使用begin end来包裹多条业务语句并且需要修改结束符号。
1.2、删除触发器
语法：DROP TRIGGER 触发器名称
实际使用：
-- 删除前面创建的两个触发器 drop TRIGGER trigger_insert; drop TRIGGER trigger_insert_before;
二、存储过程
2.1、认识变量
2.1.1、系统变量
由mysql数据库管理系统提供的，变量名称固定，可以修改和查看值，分为全局变量和会话变量。
全局变量：当mysql服务没有重启时，我们可以查看和修改的变量。
会话变量：和MySQL连接形成的会话，生命周期在整个会话过程中。
全局变量用global修饰，会话变量用session修饰，通常session可以省略(即省略默认为session)。
查看系统变量：mysql5.7.32版本
SHOW GLOBAL variables; -- 查看所有全局变量(503) SHOW SESSION variables; -- 查看所有会话变量(517) SHOW variables; -- 查看所有会话变量 SHOW GLOBAL variables like '%dir%'; -- 模糊查询环境变量 SELECT @@datadir; -- 查看全局系统变量 SELECT @@session_track_transaction_info;
修改系统变量：
-- ①设置自动提交事务改为手动提交(全局) SHOW GLOBAL variables like 'autocommit'; -- 全局系统变量中为自动提交事务 SET GLOBAL autocommit=0; -- 将全局的自动提交的事务改为手动提交 -- ①设置自动提交事务改为手动提交(会话) SHOW SESSION variables like 'autocommit'; -- 查看会话变量中自动提交事务 SET SESSION autocommit=0; -- 将会话变量中自动提交的事务改为手动提交 -- 其他方式设置全局、会话变量 SET @@session.autocommit=1; -- 设置会话变量 SET @@global.autocommit=1;-- 设置全局变量
注意：
全局变量在修改后，在不同的会话中都会立即生效，但是在重新启动mysql服务后，全局变量会恢复为默认值，如果想让全局变量依旧有效，需要去修改.ini文件（MySQL配置文件）。
会话变量在修改后只对当前会话有效。一般在开发过程中修改会话变量。如：字符编码格式等可以在ini文件中进行设置。
2.1.2、用户变量
MySQL允许用户自定义变量，分为用户变量和局部变量。
用户变量
作用域：当前会话有效。
语法：设置方式(两种)
-- 方式一：先去声明并初始化用户变量，赋值操作既可以使用=进行赋值，也可以使用:=进行赋值 SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; -- 注意使用select进行初始化与赋值会返回变量名及值 -- 方式二：查询好结果之后赋值到某个变量中 SELECT 字段 into @变量名 FROM 表名; -- 也相当于初始化以及赋值操作
实际演示：
-- 方式一 set @aa = 123456 set @aa:=456789 select @aa:= 789123 -- 初始化赋值并返回值 -- 测试：获取用户变量 select @aa -- 方式二 select count(*) into @bb FROM student; -- 测试：获取用户变量 SELECT @bb;
局部变量
作用域：在begin end的结构体中，声明必须是begin end结构体的第一句
语法：声明方式及赋值操作
#声明方式，必须在begin后面从第一行开始 DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值; -- 声明并赋值 # 局部变量的赋值操作 SET 变量名:=值; SELECT @变量名:=值; SELECT 字段 into 变量名 FROM 表名;
注意点：不能直接单独使用局部变量，一般用于在存储过程以及存储函数中使用！
2.2、存储过程创建
语法介绍
存储过程：一组已经预先编译好的sql语句的集合，理解为批处理语句（增加流程控制语句），一般在复杂逻辑中才会使用存储过程。
创建语法介绍：如果存储过程中只有一条SQL语句可以省略BEGIN END。
DELIMITER$ CREATE PROCEDURE 存储过程的名称(参数列表) -- 单个值为：参数模式 形参名称 参数类型 BEGIN 局部变量的定义多条sql 语句流程控制语句 END;$
参数列表：
in：表示输入参数(一般调用时传入)。out：表示输出参数，调用时可传入用户变量来获取。inout：前两个的结合。
调用存储过程：
-- 实参列表中包含由输出类型的参数 CALL 存储过程的名称(实参列表)
实操演示
下面是创建以及调用案例：
-- 创建存储过程： -- 参数2个：name->输入，插入student表一条记录的名称。 -- sum -> 输出，返回studnet、course表的总记录 DELIMITER$ CREATE PROCEDURE insertStu(in name varchar(20),out sum int) BEGIN DECLARE s1 int DEFAULT 0; DECLARE c1 int DEFAULT 0; INSERT into student(name,sex) VALUES(name,'男'); -- 1、插入一条记录到student中 -- 2、查询student、course表中的所有记录数合并到sum变量中 SELECT count(*) into s1 FROM student; SELECT count(*) into c1 FROM student; set sum:=s1+c1; -- 局部变量赋值操作 END;$ -- 测试存储过程 SET @sum:= 0; CALL insertStu('小天天',@sum); SELECT @sum; -- 获取用户变量
2.3、删除存储过程
语法：DROP PROCEDURE 存储过程名称
实操：删除上面创建的存储过程
drop PROCEDURE insertStu;
2.4、查看存储过程
语法：SHOW CREATE PROCEDURE 存储过程名称;
实操：查看上面创建的存储过程
show create PROCEDURE insertStu;
