@[toc]

前言

本篇博客是MySQL的学习笔记，若文章中出现相关问题，请指出！

所有博客文件目录索引：博客目录索引(持续更新)

一、触发器

触发器：在满足某种条件的时候，被动执行的SQL语句。

特性：

1. 有begin、end的结构体（针对于多条sql语句）

2. 需要指定触发的条件(触发时机)：INSERT，UPDATE，DELETE

3. 有指定的触发时间：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;-- 设置全局变量

注意：

1. 全局变量在修改后，在不同的会话中都会立即生效，但是在重新启动mysql服务后，全局变量会恢复为默认值，如果想让全局变量依旧有效，需要去修改.ini文件（MySQL配置文件）。
2. 会话变量在修改后只对当前会话有效。一般在开发过程中修改会话变量。如：字符编码格式等可以在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;

---

三、存储函数

3.1、认识存储函数

存储函数：函数也是一组预先编译好的sql的集合，基本和存储过程相似。

对于存储函数与存储过程区别：

1. 存储过程可以有0个，1个或多个返回值，适用于insert、update、delete操作。
2. 函数只能有一个返回值，适用于在处理数据以后，返回一个已知的结果，使用return返回；在存储过程中需要对参数进行赋值操作。

---

3.2、创建存储函数

语法介绍

语法：

CREATE FUNCTION 函数名称(参数列表) RETURNS 返回类型 BINLOG参数
BEGIN
	函数体
	return xxx;
END

• 参数列表：参数名称参数类型。
• BINLOG参数：四个参数根据实际函数体来进行设定
  • NO SQL：函数体中没有sql语句，也不会改参数。
  • READS SQL DATE：函数体中存在sql语句，但是整个数据是只读的，不会修改数据。
  • MODIFIES SQL DATE：函数体中存在SQL语句，并且会修改数据。
  • CONTAINS SQL：函数体中包含有SQL语句。
• 函数体：在函数体汇总必须包含return语句，将return放在函数体最后一行执行

实例演示

目标功能：传入两个参数，将两个参数相加。

#写一个函数，用于求两数之和
DELIMITER $
CREATE FUNCTION sum_(input1 INT,input2 INT) RETURNS INT NO SQL  -- 设置函数名称以及返回类型
BEGIN 
	return input1+input2;
END$

• 旁边是fx标志。

调用函数测试：

SELECT sum_(12,15)

---

3.3、查看函数

语法：SHOW CREATE FUNCTION 函数名

实际案例：查看上面创建的函数

SHOW CREATE FUNCTION sum_;

---

3.4、删除函数

语法：drop FUNCTION 函数名;

实际案例：删除上面创建的函数

drop FUNCTION sum_;

---

四、流程控制语句

3.1、选择结构

IF函数

就是一个简单函数

语法：IF(逻辑表达式，表达式1，表达式2)

功能：三目运算。

---

IF结构

功能：实现多路选择。

语法：

IF逻辑表达式 THEN 
  语句1;
ELSEIF 逻辑表达式2 THEN 
  语句2;
  ...
ELSE 语句n;
ENDIF;

注意：只能用在BEGIN END结构体中，如应用在触发器、存储过程、存储函数中。

---

case结构

1、等值选择。

语法：

CASE 字段|变量|表达式
WHEN 值 THEN 值|语句
WHEN 值 THEN 值
...
ELSE 值
END

2、不等值选择

语法：

CASE
WHEN 逻辑表达式 THEN 语句1
...
ELSE 语句n
END

---

3.2、循环结构

while

语法：

WHILE 逻辑表达式 DO 
循环体
END WHILE;

实际案例见讲义！

---

loop

功能：设置指定情况时跳出该循环标签。

-- Loopnaem是定义的循环名称，为了跳出循环时指定跳出的循环
loopname:LOOP;
	IF 逻辑表达式 THEN
		...
		LEAVE loopname; -- 跳出当前指定的循环，类似于java中的break 标签
	ENDIF;
ENDLOOP;

---

REPEAT

语法：不就是java中的do while结构

REPEAT
    循环体 
UNTIL 逻辑表达式 -- 当满足逻辑表达式，跳出循环
ENDREPEAT;

实例依旧到讲义上看。

---

五、定时任务

5.1、认识定时任务

定时任务：用于生成日报，统计数据。将查出来的数据插入到报表中去。

用途：一般是半夜12点之后来计算前一天的数据。通过定时任务策略来去调用指定的函数。可以定时发送短信等操作。一般使用java写定时任务或者脚本写。

---

5.2、查看定时策略是否开启

查看是否开启：show variables like '%event_sche%';，默认是关闭的。

打开定时策略：set global event_scheduler=1;，设置1为开启，0为关闭。

---

5.3、创建定时任务

语法

语法：

create event 事件名  -- 创建事件名称
on schedule every 1 minute  -- 策略为每一分钟
on completion preserve disable  -- disable是不启动该定时任务
do call 调用函数;  -- 定时调用的函数

• 第二行的策略分为多种；第三行可以选择是否启动该定时任务。

---

5.4、操作定时任务(查看、开启关闭、删除)

1、查看所有的定时任务

SELECT * FROM information_schema.EVENTS;  -- 在information_schema数据库中的EVENTS表里面存储

2、开启或关闭定时任务

alter event 任务名 on completion preserve enable; -- 开启定时任务
alter event 任务名 on completion preserve disable; -- 关闭定时任务

3、删除定时任务

drop event 任务名;

---

5.5、定时规则(3种类型)

1、周期执行–关键字 EVERY

单位有：second、minute、hour、day、week(周)、quarter(季度)、month、year

示例：on schedule every 1 week

2、在具体某个时间执行–关键字 AT

on schedule at current_timestamp()+interval5day; -- 5天后执行
on schedule at'2019-01-0100:00:00'; -- 在2019年1月1日，0点整执行

3、在某个时间段执行–关键字STARTS ENDS，在指定的时间范围内

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month; -- 5天后开始直至到一个月每天执行
on schedule every 1 day STARTS current_timestamp() ends current_timestamp()+interval 5 day;  -- 从现在到之后5天每天都执行

注意：这里是将every、starts ends结合使用。

---

实际案例

案例描述：每秒钟插入一条记录到student表中。尽量将多个命令实际进行使用！

-- 1、查看定时策略是否开启
show VARIABLES like '%event_sche%';  
-- 2、若是未开启，设置开启
set global event_scheduler=1;

-- 3、创建定时任务
CREATE event inseve  -- 创建事件名称
on schedule every 1 SECOND  -- 策略为每一分钟
on completion preserve ENABLE  -- disable是不启动该定时任务
do call insertStu('晓丽');  -- 定时调用的函数

-- 4、关闭定时任务
alter event inseve on completion preserve disable; -- 关闭定时任务

-- 5、删除指定定时任务
DROP EVENT inseve;

说明：过程中你可以去查看student表看定时任务是否启动！