MySQL学习18_视图&存储过程&触发器

视图

概念

• 视图是一个虚拟表，其内容由select查询语句定义；
• 和真实的表一样，视图也包含行和列，对视图的操作与对表的操作基本一致；
• 图中的数据是在使用视图时动态生成，视图中的数据都存储在基表中。

特点

1. 可读性：简化了复杂的查询，使复杂的查询更易于理解和使用；
2. 安全性：视图可以隐藏一些敏感的信息，可以把权限限定到行列级别；
3. 重用性：视图是对复杂查询语句的封装，对数据库重构，不会影响程序的运行。

注意项

1. 视图一般用于多张表，由比较复杂的select查询语句定义；
2. 视图表和基表的通字段数据修改会互相影响；
3. 一般会对视图进行权限限制，只读；
4. 最好不要修改视图表数据，在一些简单的视图上可以修改数据，但是一些复杂的视图在上面修改数据会失败，比如创建视图的select语句有聚合函数时。

操作

# 创建视图
create view 视图名 as select查询语句;

# 修改视图
alter view 视图名 as select查询语句;

# 查看视图创建语句
show create view 视图名;

# 查看有哪些视图
show table status where comment='view';

# 删除视图
drop view 视图名;

————————————————————————————————————————————

存储过程

概念

• 存储过程（Stored Procedure）是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，
  用户可通过指定存储过程的名字并给定参数(需要时)来调用执行，类似于编程语言中的方法或函数。

优点

1. 存储过程是对SQL语句的封装，增强可复用性；
2. 存储过程可以隐藏复杂的业务逻辑、商业逻辑；
3. 存储过程支持接收参数，并返回运算结果（视图不具备）；

缺点

1. 存储过程的可移植性较差，如果更换数据库，要重写存储过程；
2. 存储过程难以调试和扩展；
3. 无法使用Explain对存储过程进行分析；

创建存储过程

关键语法

# 1、声明语句结束符，可以自定义；没有这个，默认是以分号;结束
# 存储过程函数中可能有;作为一条语句的结束，为避免被认为是存储过程的结束符，所以最好重新声明语句结束符
DELIMITER $$  # 将语句的结束符号从分号;临时改为两个$$
	...
$$
DELIMITER ; # 将语句的结束符号恢复为分号

# 2、存储过程开始和结束符号
BEGIN 
...
END; 

# 3、变量定义
declare var int unsigned default 10;

# 4、变量赋值
SET @var=1;

# 5、条件语句
if condition then
	...
else
	...
end if;

# 6、case语句
case var
when value1 then
	...
when value2 then
	...
else
	...
end case;

# 7、循环语句
# while-do-end while
while condition do
	...
end while;

# repeat-until-end repeat
repeat
	...
until condition
end repeat;

创建存储过程

delimiter //  # 将语句的结束符号从分号;临时改为两个//(可以是自定义)
create procedure 存储过程名([[ in | out | inout ] 参数名 数据类型...])
begin
	# 处理语句
end
//
delimiter ; # 将语句的结束符号恢复为分号

• in 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）;
• out 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）;
• inout 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

查看存储过程

# 查看所有的存储过程
show procedure status;

# 查看特定数据库存储过程
show procedure status where db='数据库名';

# 查看存储过程的创建代码
show create procedure 存储过程名;

调用存储过程

call 存储过程名(参数)

————————————————————————————————————————————

触发器

概念

• 触发器（trigger）用于监视某种情况并触发某种操作；
• 与表事件相关的sql语句集合，相当于一种隐藏的存储过程；
• 不是由程序调用，而是由事件来触发。例如，当对某张表进行insert、delete、update操作时就会触发执行它。

语法

create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_stmt

• trigger_name：触发器名称；
• trigger_time：触发时间，取值有before、after；
• trigger_event：触发事件，取值有insert、update、delete；
• table_name：触发器监控的表名；
• trigger_stmt：触发执行的语句，可以使用OLD、NEW来引用变化前后的记录内容
  NEW.columnName：获取INSERT触发事件中新插入的数据
  OLD.columnName：获取UPDATE和DELETE触发事件中被更新、删除的数据
  

# 查看触发器 
show triggers [from database_name];

# 删除触发器
drop trigger trigger_name;

---

知识总结，交流学习，不当之处敬请指正，谢谢！