视图
概念
- 视图是一个虚拟表,其内容由select查询语句定义;
- 和真实的表一样,视图也包含行和列,对视图的操作与对表的操作基本一致;
- 图中的数据是在使用视图时动态生成,视图中的数据都存储在基表中。
特点
- 可读性:简化了复杂的查询,使复杂的查询更易于理解和使用;
- 安全性:视图可以隐藏一些敏感的信息,可以把权限限定到行列级别;
- 重用性:视图是对复杂查询语句的封装,对数据库重构,不会影响程序的运行。
注意项
- 视图一般用于多张表,由比较复杂的select查询语句定义;
- 视图表和基表的通字段数据修改会互相影响;
- 一般会对视图进行权限限制,只读;
- 最好不要修改视图表数据,在一些简单的视图上可以修改数据,但是一些复杂的视图在上面修改数据会失败,比如创建视图的select语句有聚合函数时。
操作
# 创建视图
create view 视图名 as select查询语句;
# 修改视图
alter view 视图名 as select查询语句;
# 查看视图创建语句
show create view 视图名;
# 查看有哪些视图
show table status where comment='view';
# 删除视图
drop view 视图名;
————————————————————————————————————————————
存储过程
概念
- 存储过程(Stored Procedure)是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,
用户可通过指定存储过程的名字并给定参数(需要时)来调用执行,类似于编程语言中的方法或函数。
优点
- 存储过程是对SQL语句的封装,增强可复用性;
- 存储过程可以隐藏复杂的业务逻辑、商业逻辑;
- 存储过程支持接收参数,并返回运算结果(视图不具备);
缺点
- 存储过程的可移植性较差,如果更换数据库,要重写存储过程;
- 存储过程难以调试和扩展;
- 无法使用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;
知识总结,交流学习,不当之处敬请指正,谢谢!