文章目录
视图 /存储过程/触发器
1. 视图
1.1 基本语法
- 视图创建:
create [or replace] view 视图名 as select语句 [with [cascaded/local] check option];
- 视图修改: 1)
create or replace view 视图名 as select语句(修改版) [with [cascaded/local] check option];
2)alter view 视图名 as select语句(修改版) [with [cascaded/local] check option];
- 视图查询: 1) 查看创建视图语句
show create view 视图名;
(类似查表)
2) 查看视图数据select * from 视图名;
(类似查表) - 视图删除:
drop view 视图名1[,视图名2,...];
- 插入数据:
insert into 视图名 values(数据);
(类似表操作)
1.2 检查选项(check option)
- cascaded级联(默认检查选项)
套娃:下一层依赖上一层(create v2 from v1),如下图三层,第二层语句后带有with cascaded check option
,即使第一层没有检查选项,也会按照cascaded检查选项操作(相当于“复制”了一份check option给上层)。上层不会对下层这样操作(第二层不会“复制”给第三层)。
向视图v3插入insert(更新update同理)数据。
cascaded:id得满足id<=20 and id>=10
(即满足第一、二层where条件,因为语句后面带了检查选项);id不需要满足id<=15
(即不需要满足第三层条件,因为语句后没带检查选项)。 - local本地
顾名思义,只管本层的检查选项。
套娃:下一层依赖上一层,第二层语句后带有with local check option
,与cascaded不同的是,第二层只管自己这层(有选项就按选项操作),不会给第一层“复制”。
向视图v3插入insert(更新update同理)数据。
local:id满足带检查选项的层就行,id>=10
即可。
1.3 视图的更新与作用
-
视图更新: 视图更新的条件是视图和其依赖的基表每一行的字段一一对应,视图字段可以少于基表。
包含以下任意一项,视图不可更新:(1)聚合函数(sum()、min()、count()等) (2)distinct (3)group by, having (4)union/union all -
视图作用
(1)视图是一个虚拟的表(并非表的查询结果,只是查询过程的封装),可以基于基表的查询创建一个视图(虚拟的新表),供以后使用(from 该视图
,相当于基于虚拟表进行操作) ;
(2)将创建视图时的条件(如where后的条件)封装了,若有check option,以后只要基于该视图(from 该视图
),自带条件;
(3)安全(可以给用户看到指定字段的数据,其他字段数据不可见)
(4)数据独立(基表表结构发生更改,视图的数据不会发生变化)
2. 存储过程
存储过程就是封装一段SQL语句,便于以后直接调用,提高复用性,其可以接收参数,也可以返回数据。
2.1 基础语法
- 创建:
create procedu 存储过程名([参数列表])
begin
--SQL语句;
end;
- 调用:
call 存储过程名([参数列表]);
- 查看: 1)查指定数据库的存储过程及状态
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名';
2)查储存过程的定义show create procedure 存储过程名;
- 删除:
drop procedure [if exists] 存储过程名;
注: 在命令终端里执行创建存储过程操作时,需要通过关键字delimiter
指定mysql的结束符。如在终端中mysql> delimiter $$
,表示以后的SQL语句都得用$$
结尾,而不是;
结尾。这解决了--SQL语句;
和end;
后同时带分号的情况。现只要用end$$
即可在终端里创建存储过程。(创建完后记得改回分号mysql> delimiter ;
)
2.2 变量
1.系统变量
系统变量是MySQL服务器提供,非用户定义。分为全局变量(global)、会话变量(session(默认))。(DataGrip中一个控制台console里的变量就是会话变量,在一个控制台更改一个会话系统变量,另一个控制台的该系统变量不会改变) MySQL重启后所有系统变量置回初值。
- 查看系统变量: 1)模糊查看
show [global/session] variables [like '...'];
(不加like 查看所有系统变量)
2)指定查看select @@[global./session.]系统变量名;
- 设置系统变量:
set [global/session] 系统变量名=值;
set @@[global./session.]系统变量名=值;
2.自定义变量
自定义变量是用户自己定义的变量,在当前会话生效。
- 赋值:
set @变量名1 := 值1[, @变量名2 := 值2, ...];
select @变量名1 := 值1[, @变量名2 := 值2, ...];
select 字段名 into @变量名 from 表名;
- 使用:
select @变量名;
注: 若变量未赋值,使用时为NULL
。
3.局部变量
局部变量是根据需要定义的在局部生效的变量,访问前需要声明。可以用作存储过程内的局部变量和输入参数(一般在begin和end内使用,且只在其间有效)。
- 声明:
declare 变量名 变量类型[default 值];
(可给默认值) - 赋值:
set 变量名:=值;
select 字段名 into 变量名 from 表名;
- 使用:
select 变量名;
2.3 条件判断(if)
- 语法:
if 条件1 then
语句1;
elseif 条件2 then
语句2;
else
语句3;
end if;
2.4 参数(in/out/inout)
- 类型: (1)
in
输入参数(默认) (2)out
输出参数 (3)inout
既可作输入,又可作输出 - 使用: 在创建存储过程时
create procedure p1(in/out/inout 变量名 变量类型) ...
2.5 条件选择(case)
- 语法:
case 判定值
when 值1 then 语句1; #判断值=值1,走语句1
when 值2 then 语句2;
else 语句3;
end case;
case
when 条件1 then 语句1; #条件1成立,走语句1
when 条件2 then 语句2;
else 语句3;
end case;
2.6 循环(while/repeat/loop)
- while语法:
while 条件 do
语句; #条件满足,走语句,不满足,结束循环
end while;
实例:
create procedure p7(in n int, out res int) -- 输入n,输出从1加到n
begin
declare i int default 1;
set res := 0;
while i<=n do
set res := res + i;
set i := i + 1;
end while;
end;
call p7(4,@result);
select @result;
- repeat语法:
和while相反,不满足条件继续循环,知道满足条件才退出循环(类似do…while)
repeat
语句; #条件不满足,继续循环(不管条件,开始就会执行一次循环语句)
until 条件 #满足条件,退出循环
end repeat;
- loop语法:
loop相当于死循环,需要标记配合leave
(类似break)、iterate
(类似continue)使用。
标记:loop
语句;
end loop 标记;
实例:
create procedure p8(in n int) -- 输入n,输出从1加到n
begin
declare i int default 1;
declare res int default 0;
flag:loop
if(i>n) then
leave flag;
end if;
set res := res + i;
set i := i + 1;
end loop flag;
select res;
end;
call p8(4);
2.7 游标(cursor)
游标是用来存储查询结果的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标使用过程如下:
- 游标声明:
declare 游标名 cursor for select语句;
- 游标打开:
open 游标名;
- 获取游标记录:
fetch 游标名 into 变量1[, 变量2, ...];
- 游标关闭
close 游标名;
实例:
create procedure p9(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age<=uage; #1.声明游标
declare exit handler for SQLSTATE '02000' close u_cursor; #条件处理程序handler
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 varchar(100)
);
open u_cursor; #2.开启游标
while true do
fetch u_cursor into uname,upro; #3.获取游标记录
insert into tb_user_pro values(null,uname,upro); #将游标记录循环插入表中
end while;
close u_cursor; #4.关闭游标
end;
call p9(30);
2.8 存储函数
存储函数是必须有返回值的存储过程,存储函数的参数只能说in
类型,一般存储函数都可以用存储过程替代。
实例:
create function fun1(n int) #输入n,输出从1加到n
returns int deterministic #返回值类型,存储函数的特性是deterministic
begin
declare res int default 0;
declare i int default 1;
while i<=n do
set res := res + i;
set i := i + 1;
end while;
return res;
end;
select fun1(4);
3. 触发器
触发器是与表相关的数据库对象,当表执行insert/update/delete
操作之前或之后,可触发并执行触发器中定义的SQL语句。(MySQL只支持行级触发:一次SQL语句操作影响了几行,就触发几次触发器;不支持语句级触发:一次SQL语句操作不管影响了几行,只触发一次触发器)
3.1 基础语法
- 创建:
create trigger 触发器名
before/after insert/update/delete #何时触发以及触发器类型
on 表名 for each row #行级触发器
begin
触发器中的SQL语句(触发时执行);
end;
- 查看: 查看当前数据库中所有触发器:
show triggers;
- 删除: 删除[指定数据库下的]触发器:
drop trigger [数据库名] 触发器名;
3.2 触发器使用
当表执行insert/update/delete
操作之前或之后,触发器触发。以表的更新为例,利用触发器完成表的更新日志。
-- 创建更改数据的触发器
create trigger tb_user_update_trigger
after update on tb_user for each row #在tb_user表更新数据后触发
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES #触发后将数据插入更新日志表user_logs中
(null, 'update', now(), new.id,
concat('更新前的数据为:id= ',old.id,', phone= ',old.phone,', email= ',old.email,', profession= ',old.profession,
'| 更新后的数据为:id= ',new.id,', phone= ',new.phone,', email= ',new.email,', profession= ',new.profession));
end;
现只要tb_user
表中更新数据,更新日志表中就会记录更新前后的数据。(更新操作影响了几行,在日志表user_logs
中就会有几条数据)