Mysql进阶-视图/存储过程/触发器

视图

1. 概念

虚拟的表，只保存了SQL逻辑。

2. 作用

• 简化操作
    将经常查询的数据定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
  
• 安全
    Mysql对用户的授权只能控制到表（就是哪些用户可以看到哪些表）。而通过视图可以控制到字段（哪些用户可以看见哪些字段）。
  
• 数据独立
    帮助用户屏蔽真实表结构带来的变化。（当我们表结构发生变化后，只需要改动视图的逻辑，而不需要改动我们的业务代码）

3. 检查选项 cascaded / local

• cascaded
    使用 with cascaded check option 检查选项，可以检查依赖视图的规则保持规则的一致性。
  例：v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

当我们直接使用 with check option，默认的也是cascaded（级联）。

• local
  例：v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。
• 视图的更新
    我们想要往视图中插入、修改、删除等操作，是需要满足视图中的数据和基表中的数据要是一一对应的关系。如果不满足一一对应是不能通过视图操作表中数据的。

当两个同时存在的时候，cascaede就会递归所有的，local的存在就可与可无了。

  无论是否使用"CASCADE"或"LOCAL"选项，MySQL都会递归地检查依赖于视图的所有对象是否有检查选项，以确保在执行任何数据更改之前，不会破坏数据库的完整性。
只是带有local选项的就会执行，没有local的就不会去执行。
但如果带了cascaded，那么从这一层开始，他就会执行所有依赖视图的条件。

---

存储过程

  存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

• 封装、复用
• 减少网络交互
• 可接受参数、返回数据

使用

1. 语法：

# 创建
	create procedure p1()
	 begin 
 		SQL语句
	end;
	
# 调用
	call p1();
	
# 查看
	select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名称';
	show create procedure 存储过程名称;
	
# 删除
	drop proceduce if exists 存储过程名称;

注意点：
  如果我们在命令行中定义这个存储过程，那么因为结束符分号；的问题，会导致执行失败，这里我们就可以使用delimiter关键字来定义结束符，来解决这个问题。

2. 变量

• 系统变量
   系统自带的变量，不需要用户自定义。分为 GLOBAL 和 SESSION两类。
  GLOBAL：在整个系统中存在的，
  SESSION：只在当前会话中存在的。

# 查看系统变量
	show variables; #(默认是session级别的变量)
	show global variables; # (全局的)
	show global variables like 'xxx'; # (适用于只记住部分变量名的情况)
	select @@session.系统变量名; #（查看指定变量的值）
# 设置系统变量
	set session 系统变量 = 值;

  如果我们设置了全局的系统变量，他就会在所有的会话中生效。但当我们重启Mysql服务的时候，他就会恢复它原有的值。想要永久的改变它的值，就得在/etc/my.cnf中修改。

• 自定义变量
   用户根据业务需求自定义的变量。使用的时候直接用@变量名就行。作用域是当前连接。

# 赋值
	set @var_name = expr;
	set @var_name:=expr;
	select @var_name:=expr; # （多个变量使用逗号间隔）
	select 字段名 into @var_name from 表名;
# 查询
	select @myname;

注意：自定义的变量是不需要声明的，若没赋值，值为NULL。

• 局部变量
  作用范围：
   存储过程的 begin 和 end；
  作为局部变量必须要声明。

# 声明
	declare 变量名 变量类型 （default 默认值） 
# 赋值
	set 变量名 = 值； 
	set 变量名:= 值；
	select 字段名 into 变量名 from 表名；
# 查询
	select 变量名；

3. 其他的一些表达式（if判断、case，while等）

# if语法：
	if 条件 then
		SQL语句
	elseif 条件 then
		SQL语句
	end if;
---------------------------------------------------------------------------------
# 存储过程中的参数
# 类型分为：IN（入参，默认的）、OUT（返回值参数）、INOUT（即是入参，又是返回值参数）
	create proceduce p1(in 参数名 参数类型，...)
	begin
		SQL语句
	end;
---------------------------------------------------------------------------------
# case语法：
#	法一：
	case value
		when value1 then SQL语句
		when value2 then SQL语句
		....
		else SQL语句
	end case;
#	法二：
	case
		when 条件 then SQL语句
		...
		else SQL语句
	end case;
---------------------------------------------------------------------------------
# 循环控制
#	while
	while 条件 do
		SQL语句
	end while;
---------------------------------------------------------------------------------
#	repeat(相较于 while ，它是满足条件时候退出循环。)
	repeat
		SQL逻辑
		until 条件
	end repaet;
---------------------------------------------------------------------------------
#	loop
# loop需要配合两个关键字来实现完整的循环逻辑，如果只是单独使用loop，那他就相当于一个死循环。
# leave：配合循环使用，退出循环
# iterate：必须放在循环中，作用是：跳过剩下语句。（很continue很像）
loop循环名称 loop
		SQL逻辑
	end loop loop循环名称；
	
# 举例：将1~n进行数值的累加。
	create proceduce （in n int）
	begin
		declare total int default 0;
		sum loop
			if n<= 0 then
				leave sum;
			end if;
			set total := total + n;
			set n :=  n - 1;
		end loop sum;
	end；

4. 游标
     是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。
     对比前边我们使用普通的类型来定义局部变量的数据类型，普通参数类型的局部变量只能用存储单个字段的结果。而游标这个类型，可以用来存储整个查询的结果集。

# 语法：
# 声明游标
declare 游标名 cursor for SQL语句;

# 开启游标
open 游标名;

# 获取游标记录
fetch 游标名 into 变量,变量,...;

# 关闭游标
close 游标名;

举例：（这里的循环使用了死循环，会报错，但执行成功了。解决方案是：使用条件处理程序Handler）

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

# 语法：
declare handler_action handler for condition_value ... SQL语句;

---------------------------------------------------------------------------------
# 值
handler_action:
	continue: 继续执行
	exit：中止当前程序
condition_value:
	SQLSTATE sqlstate_value：状态码，如02000
	SQLWARNING：所有以01开头的SQLSTATE代码的简写
	NOTFOUND：所有以O2开头的SQLSTATE代码的简写
	SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

举例：（针对于游标使用死循环的案例。）

状态码：

存储函数

存储函数和存储过程很像，但他的参数只能是IN类型的。

# 语法：
	create function 函数名称(参数列表)
	returns 返回值类型 [选项]
	begin
		SQL语句
		return xxx;
	end;

# 选项：
	deterministic：相同的输入参数总是产生相同的结果。
	no sql：不包含sql语句。
	reads sql data：包含读取数据的语句，但不包含写入数据的语句。

注意：在Mysql8.0版本中必须要写这个选项。

举例：1~n的累加

  存储函数和存储过程这两者，推荐使用存储过程。因为存储函数能做到的，存储过程也能做到，而且还不需要返回值。

触发器

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

作用：
  保证数据的完整性，日志记录，数据校验等操作。
Mysql 触发器只支持行级触发，不支持语句触发。

# 创建触发器
	create trigger trigger_name
	before/after insert/update/delete
	on table_name for each row
	begin
		SQL 语句；
	end;

# 查看触发器
	show triggers；

# 删除触发器
	drop trigger [数据库名称]trigger_name; (不指定数据库名称，默认为当前数据库)

案例：（insert）

案例：（update）

---

如果文章中有描述不准确或者错误的地方，还望指正。您可以留言📫或者私信我。🙏
最后希望大家多多 关注+点赞+收藏^_^，你们的鼓励是我不断前进的动力！！！
感谢感谢~~~🙏🙏🙏</f