Mysql视图、存储过程、存储函数、触发器

一、视图

1.1 概念

视图（View）：虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表（基表），是在使用视图时动态生成的。

视图只是保存了查询的 SQL 逻辑，不保存查询结果。

1.2 使用

-- 创建视图
create [ or replace] view 视图名称[(列名列表)] as select 语句 [with [ cascaded | local ] check option]

-- 查看视图
show create view 视图名称;

-- 修改视图
create [ or replace] view 视图名称[(列名列表)] as select 语句 [with [ cascaded | local ] check option]

alter view 视图名称[(列名列表)] as select 语句 [with [ cascaded | local ] check option]

-- 删除视图
drop view [ if exists ] 视图名称 [,视图名称,…];

1.3视图检查选项

当使用 with check option 子句创建视图时，MySQL 会通过视图检查正在更改（增、删、改）的每个行，以使其符合视图的定义。

MySQL 允许基于另一个视图1来创建新的视图2，它还会检查依赖视图1中的规则以保持一致性。

为了确定检查范围，MySQL 提供了两个选项：cascaded(及联）和 local ，默认：cascaded

cascaded：及联，检查当前视图及当前视图所依赖的所有视图的 where 条件

	create view v1 as select id, name from student where id <=20;

		v1 视图没有添加 with check option 语句，当执行 insert into v1 values(30,'尼古拉斯'); 时，可以插入成功，数据会入 student 表。


	create view v2 as select id, name from v1 where id >= 10 with cascaded check option;

		v2 视图添加 with check option 语句，当执行 insert into v1 values(30,'尼古拉斯'); 时，插入失败。检查条件： 1、id >= 10    2、 id <= 20


	create view v3  as select id, name from v2 where id <= 15;
		v3 视图依赖 v2 视图，未添加with check option 语句，所以不校验 id <= 15 的条件。检查条件： 1、id >= 10    2、 id <= 20



local：检查当前视图的 where 条件，检查所依赖视图是否存在 with check option 语句，存在，则检查依赖视图的 where 条件是否满足；不存在，则不进行检查

1.4 视图更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一关系。如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数（sum()、min()、max()、count()等）

distinct

group by

having

union / union all

1.5 视图的作用

简单：简化用户对数据对理解及操作。将经常使用的查询（例，多表联查）定义为视图，不必每次都指定全部条件进行数据查询

安全：数据库不能授权到特定的行/列上。通过视图可以使用户只能查看/修改所见数据

数据独立：可帮助用户屏蔽真实表结构变化带来的影响。例如，修改表名/表字段名，直接修改视图中的表名/表字段名即可

二 、存储过程

2.1 概念

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以减轻开发人员工作，减少数据在数据库和应用服务之间的传输，可以提高数据处理效率。

是数据库 SQL 语言层面的代码封装与重用。

2.2 特点

封装、复用	

可以接收参数，可以返回数据 	

减少网络交互，提升效率

2.3 使用

-- 创建
create procedure 存储过程名称([ in/out/inout 参数名 参数类型])

begin

	SQL 语句

end;

in：默认类型。该类参数作为输入，即需要调用时传入值
out：该类参数作为输出，即该参数可作为返回值
inout：即可作为输入参数，也可作为输出参数


-- 调用
call 存储过程名称([参数]);

-- 查看指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = '${数据库名}';

-- 查询某个存储过程定义
show create procedure 存储过程名称;

-- 删除
 drop procedure [ if exists] 存储过程名称;

注意：在命令行中，执行创建存醋过程的SQL时，需要通过关键字 delimiter 指定 SQL 语句的结束符 （例如， delimiter $$)

2.4 变量

2.4.1 系统变量

系统变量：MySQL 服务器提供，非用户定义，属于服务器层面。分为 全局变量（GLOBAL）、会话变量（SESSION）。不指定时，默认为 session

注意：MySQL 服务重启后，所设置的全局参数会失效。可在 /etc/my.cnf 中配置

--查看所有系统变量
show [ session | global ] variables;

-- 模糊匹配查询变量
show [ session | global ] variables like 'xxx';

-- 查看指定变量值
select @@[ session | global ].系统变量名;

-- 设置系统变量
set [ session | global ] 系统变量名 = 值;

set @@[ session | global ].系统变量名 = 值;

2.4.2 用户自定义变量

用户定义变量：用户变量不用提前声明，在使用时直接用 “@变量名” 使用就可以。
作用域：当前连接

注意：用户定义的变量无需对其进行声明或初始化，无非获取到的值会为Null

-- 赋值
set @变量名 = 变量值 [,@变量名 = 变量值] ……;

set @变量名 := 变量值  [,@变量名 = 变量值] ……;

select @变量名 :=  [,@变量名 = 变量值] ……;

-- 指定查询结果赋值给变量
select 字段名 into @变量名 from 表名;

-- 使用
select @变量名 [ ,@变量名1 ,@变量名2…… ];

2.4.3 局部变量

局部变量：局部生效的变量，访问之前，需要使用 declare 关键字进行声明。可用作存储过程内的局部变量和输入参数。
作用域：其内声明的 begin … end 块中

-- 声明
declare 变量名 变量类型 [ default ....] ;   declare stu_cout int default 0;

变量类型（数据库字段类型）：int、bigint、char、varchar、date、time 等

-- 赋值
set 变量名 = 值;

set 变量名 := 值;

select 字段名 into 变量名 from 表名;

2.5 游标

游标（cursor）：是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。

游标的使用包括 游标的声明、open、fetch 和 close，语法如下：

-- 游标声明
declare 游标名称 cursor for 查询语句;

-- 打开游标
open 游标名称;

-- 获取游标记录
fetch 游标名称 into 变量[,变量...];

-- 关闭游标
close 游标名称;

2.6 条件处理程序

条件处理程序（Handler）：用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

-- 语法
declare ${handler_action} handler for ${conditon_value}[, ${conditon_value}] ... statement;

handler_action

	continue：继续执行当前程序
	
	exit：终止执行当前程序


conditon_value

	sqlstate sqlstate_value；状态码，如 02000
	
	sqlwarning：所有以 01 开头的 sqlstate 代码的简写

	not found：所有以 02 开头的 sqlstate 代码的简写

	sqlexception：所有没有被 sqlwarning 或 not found 捕获的 sqlstate 代码的简写

官网状态码：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

2.7 案例

-- 案例 1
create procedure t1(in score int, out result varchar(10))
begin
	if score >= 85 then
		set result := '优秀';
	elseif score > 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
end;

-- 将返回结果声明为用户自定义变量
call t1(98, @result);

select @result;

-- 案例 2
create procedure t2(inout score int)
begin
	set score := score * 0.5;
end;

set @score := 78;
call t2(@score);
select @score;


-- 案例 3
create procedure t3(in uage int)
begin
	-- 注意：要先声明普通变量，再声明游标
	declare uname varchar(100);
	declare upro varchar(100;
	-- 声明游标
	declare cur for select name, profession from tb__user where age <= uage;
	-- 声明一个条件处理程序，当满足 sqlstate 状态码为 02000 时，触发退出并关闭游标
	-- declare exit handler for sqlstate '02000' close cur;
	declare exit handler for not found close cur;

	-- 创建表结构
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		profession verchar(100)
	);

	-- 开启游标
	open cur;

	-- 循环体
	while true do
		-- 获取游标并赋值
		fetch cur into uname, upro;
		insert into tb_user_pro values (null, uname, upro);
	end while;

	-- 关闭游标
	close cur;
end;

三、存储函数

3.1 概念

存储函数：是有返回值的存储过程，其入参只能是 in 类型。

3.2 使用

create function 存储函数名称([参数列表])
returns type [characteristic ...]
begin
	-- SQL 语句
	return ...;
end;


characteristic 说明

	deterministic：相同的输入参数总是产生相同的结果

	no SQL；不包含 SQL 语句

	reads sql data：包含读取数据的语句，但不包含写入数据的语句

3.3 案例

-- 实现存储函数从1到n的累加
create function fun1(n int)
returns int deterministic
begin
	declare total int default 0;
	while n>0 do
		set total = total + n;
		set n := n - 1;
	end while;
	return total;
end;

-- 函数调用
select fun1(50);

四、触发器

4.1 概念

触发器是与表有关的数据库对象，指在 insert/update/delete 之前/之后触发并执行的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 old / new 来引用触发器中发生变化的记录内容，这与其它数据库是相似的。

触发器只支持行级触发，不支持语句级触发

行级触发：update修改5行数据，触发器执行5次。

语句级触发：执行update语句，无论影响多少行数据，触发器只执行一次。

触发类型	new / old
insert型触发器	new表示将要或者已经新增的数据
update型触发器	old表示修改之前的数据，new表示将要/已经修改的数据
delete型触发器	old表示将要/已经删除的数据

4.2 使用

-- 创建
create trigger 触发器名称
before/after insert/update/delete on ${表名} for each row -- 行级触发器
begin
	-- 触发器逻辑SQL
end;

-- 查看
show triggers;

-- 删除
-- 如果没有指定 schema_name（数据库名），默认为当前数据库
drop trigger [schema_name.]触发器名;