【示例】MySQL-视图、存储过程、触发器

前言

本文主要讲述视图、存储过程、触发器三个概念

视图

概念 | 什么是视图？

视图（View）是一种虚拟存在的表。

我们定义的视图只包含查询的存储逻辑，不保存查询的结果集，所以创建视图的时候，重心落在SQL查询语句的创建上

概念 | 视图的基本语法

# 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS 完整的select语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

# 查询视图
SHOW CREATE VIEW 视图名称;  # 查看创建视图的语句
SELECT * from 视图名称;		# 查看视图查询的数据结果

# 修改视图
# OR REPLACE参数带上了
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS 完整的select语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ];
ALTER VIEW 视图名称[(列名列表)] AS 完整的select语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

# 删除视图
DROP VIEW [IF EXISTS] 视图名称 [, 视图名称,...];

应用 | 视图的示例

单视图示例 | 查询

可以在定义好的视图上，继续筛选数据

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;

-- 查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;

-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;

-- 删除视图
drop view if exists stu_v_1;

单视图示例 | 插入数据-不带检查

可以基于视图向定义视图对应的表中插入数据。

要想插入成功，需要保证：插入的行列和视图的行列保持一致，不能多也不能少

create or replace view stu_v_1 as select id,name from student where id <= 10;
select * from stu_v_1;

insert into stu_v_1 values(6,'Tom');		# 插入成功且可通过视图展示出来
insert into stu_v_1 values(17,'Tom22');		# 插入成功但不可通过视图展示（限制条件：id<=10）
insert into stu_v_1 values(6,'Tom',2000100104);		# 插入失败，列不匹配

单视图示例 | 插入数据-带检查

想要限制更新、修改、删除等操作，可以在定义视图的时候带上检查选项：

检查选项的参数有两个（CASCADE是默认参数）：

• CASCADED 级联，如果该视图以另一个视图为基准创建且带有该参数，则检查的时候也会去判断另一个视图的判断条件
• LOCAL 本地，如果该视图以另一个视图为基准创建且带有该参数，则检查的时候只检查该视图的判断条件，不检查另一个视图的。

create or replace view stu_v_1 as select id,name from student where id <= 10 WITH CHECK OPTION;
select * from stu_v_1;

insert into stu_v_1 values(6,'Tom');		# 插入成功且可通过视图展示出来
insert into stu_v_1 values(17,'Tom22');		# 插入失败：检查不通过。要求id<=10
insert into stu_v_1 values(6,'Tom',2000100104);		# 插入失败，列不匹配

单视图示例 | 更新视图

使用INSET、UPDATE、DELETE语句作用到视图上，都算更新视图。

上述示例讲了INSERT情况下，成功和不成功更新视图的案例。

还有以下情况，不能更新视图：当视图定义语句中出现：聚合函数（SUM()、MIN()）、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL

总结 | 视图的作用

• 简化操作：经常用来查询的表字段可以写成视图，一次填写，方便后续查询

• 安全：数据库如果授权给别人使用，但是需要对特定行、列保密（手机号、身份证号等等），就可以通过视图，使得别人只能访问不涉密的行列

• 数据独立：视图所带的判断条件，可以屏蔽真实表结构变化带来的影响（反正只看范围内的数据，范围外的怎么变动都不影响）

存储过程

概念 | 什么是存储过程？

什么是存储过程？

存储过程就是事先编译好并存储在数据库中的一段SQL语句集合，存储过程在思想上就是：SQL语言代码的封装和重用。

存储过程的特点（好处）：

• 封装、复用：简化操作，直接调用即可
• 减少网络交互、提高效率：不封装的话，需要一条条SQL语句的网络传输；封装之后，只需要交互一次调用命令即可
• 可以接受参数、可以返回数据：类似函数

概念 | 存储过程的基本语法

# ================================= 创建仅输入参数的存储过程 =====================================
# 该过程中：参数列表不加IN的原因是IN是默认的参数类型
# 参数总共的类型有三种：IN：输入参数；OUT：输出参数；INOUT：即是输入也是输出参数

# === 创建存储过程：在命令行创建
DELIMITER $$		# 若是在命令行创建存储过程，则碰到分号，就会直接执行命令，所以需要先更改命令的结束标志
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END;$$
DELIMITER ;			# 定义好之后，将命令的结束标志更改回来

# === 创建存储过程：脚本创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END;

# ================================= 创建带有输入输出参数的存储过程：以脚本创建为例 =====================================
CREATE PROCEDURE 存储过程名称 ([ IN 参数1, IN 参数2, ..., OUT 参数1, OUT 参数2])
BEGIN
-- SQL语句
END;
# ==============================================================================================

# 调用存储过程
CALL 存储过程名称 ([ 参数列表 ]);

# 查看存储过程
SHOW CREATE PROCEDURE 存储过程名称;	# 查询某个存储过程的定义

# 删除存储过程
DROP PROCEDURE [ IF EXISTS ] 存储过程名称；

知识 | 存储过程用到的知识

变量 | 系统变量、局部变量、用户自定义变量

在MySQL中，变量分为三个类型：系统变量、局部变量和自定义变量

系统变量

系统变量是MySQL服务器提供的，属于服务器层面。分为两类：全局变量和会话变量

• 全局变量，GLOBAL：针对所有会话（但MySQL服务重启后，全局变量也会失效，需要写入cnf配置文件才能保证一直有效）
• 会话变量，SESSION：默认的系统变量类型。当新建一个窗口的时候，会话变量就失效了。

相关语法：

# 查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; 					-- 查看所有系统变量（没有点）
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; 		-- 可以通过LIKE模糊匹配方式查找变量（没有点）
SELECT @@[SESSION. | GLOBAL.] 系统变量名; 				-- 查看指定变量的值（有个点）：select @@session.autocommit;

# 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;	-- 没有点：set session autocommit = 1;
SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;	-- 有个点：set @@session.autocommit = 1;

局部变量

局部变量就是定义在局部生效的变量，其声明、生效范围在BEGIN...END...之间。

相关语法：

# 声明局部变量
xxx
BEGIN
	DECLARE 变量名 变量类型 [DEFAULT 默认值 ] ;	# 变量类型就是常用的表字段类型
	xxx
END;

# 局部变量赋值
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 ...;	# 将select语句的查询结果赋值给变量，需要查询结果为标量类型（即是一个值，不能是行、列数据）

用户自定义变量

用户根据需要定义的变量，不需要提前声明，直接在用的时候用就行了。

# 定义
在用的地方直接  @变量名

# 赋值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;					# 保证select查询的结果是一个标量

# 使用
SELECT @var_name;	# 不需要初始化，如果没有赋值，则获取到的结果为null

流程语句 | if、case、while、repeat

在存储过程的BEGIN...END代码块里面，需要用到各种各样的流程语句。以下是他们的用法示例：

if

IF 条件1 THEN
	.....
[ELSEIF 条件2 THEN] 	-- 可选
	[.....]
[ELSE] 				  -- 可选
	[.....]
END IF;

case

CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;

while

WHILE 条件 DO
	SQL代码
END WHILE;

repeat

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL代码
	UNTIL 条件
END REPEAT;

流程语句 | loop、CURSOR游标、Handler

有更好的示例再总结吧

概念 | 什么是存储函数？

存储函数就是有返回值的存储过程，且存储函数的参数只能是IN类型。

语法：

# 存储函数定义
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
	-- SQL语句
	RETURN ...;
END;

# ========== 参数介绍 ==========
# - 参数列表：只能是IN类型
# - characteristic：有三个选项，可以多选：
# 		deterministic	相同的输入参数必须返回相同的结果
# 		NO SQL			表示返回结果不包含SQL语句
# 		READS SQL DATA	包含读取数据的SQL语句，不包含写入数据的语句

触发器

概念 | 什么是触发器？

概念：触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。

特点：

• 触发器可以协助应用在数据库端：确保数据的完整性、进行日志记录、进行数据校验等操作。

• 使用别名OLD和NEW来引用触发器中发生变化前和放生变化后的内容，这与其他的数据库是相似的。

• 现在触发器还只支持行级触发，不支持语句级触发。

触发器的类型有三种：

触发器类型	NEW 和 OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

触发器的触发方式有两种：

• BEFORE：在执行修改之前触发
• AFTER：在执行修改之后触发

相关语法：

# 创建触发器
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE			# 空格前后，任选一个
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	相关SQL语句;
END;

# 查看触发器
SHOW TRIGGERS;

# 删除触发器
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定schema_name，默认为当前数据库，即默认找当前数据库中名字为trigger_name的触发器

案例 | 触发器的应用

记录tb_user表的数据变更情况，并将变更日志写入到user_logs表中

create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params)
	VALUES
	(null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=',
     NEW.email, ',profession=', NEW.profession));
end;