Mysql高级（四）---视图/存储过程/触发器

2.4视图/存储过程/触发器

2.4.1视图

2.4.1.1基本使用

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

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图时，主要的工作就落在创建这条SQL语句上

创建

create [or replace] view 视图名称[(列表名称)] as select语句 [with[cascaded | loacl] check option]
1

例：

#创建一个叫做stu_v_1的视图，tb_user表中 id<=3的用户的id和username
create or replace view stu_v_1 as select id,username from tb_user where id <=3
1
2

执行成功之后，可以看到视图下多了一张表：

里面只包含了两个字段：id、username

查询

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

#查看视图数据:当做一张表进行查询
select * from 视图名称....
1
2
3
4
5

例：

#查看创建视图语句
show create view stu_v_1;
1
2

#查看视图数据
select * from stu_v_1 where id = 2
1
2

修改视图

#方式一:和创建的一样，但是必须加上 or replace 
create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local ] check option]

#方式二
alter view 视图名称[(列名列表)] as select语句 [with[cascaded | local ] check option]
1
2
3
4
5

例：

create or replace view stu_v_1 as select id,username,age from tb_user where id <=10;
1

alter view stu_v_1 as select id,age  from tb_user where id <=10;
1

删除

drop view [if exists] 视图名称[,视图名称].....
1

例：

drop view if exists stu_v_1;
1

视图下已经没有视图表了

2.4.1.2视图的检查选项

创建一个视图：

#id <= 20
create or replace view stu_v_1 as select id,username from tb_user where id <=20
1
2

插入insert

可以把视图作为一个表来使用

#插入一个id为15的数据
insert into stu_v_1 values(15,'张三')
1
2

可以看到stu_v_1表内已经新增了该数据：

同时，该视图的基表tb_user，也增加了该数据：

再次插入一条数据：

#插入一个id为30的数据
insert into stu_v_1 values(30,'李四')
1
2

插入成功：

继续看视图：

并没有该条记录，再去看基表：

基表却显示了这条记录，这是为什么？因为一开始创建视图的时候，条件就是id<=20的才在视图内，那这些id>20的，自然不会在视图表内了

怎么避免？

#在创建视图的时候后面加上 with cascaded check option
create or replace view stu_v_1 as select id,username from tb_user where id <=20 with cascaded check option
1
2

这时当再插入id>20的数据的时候，会直接报错（CHECK OPTION failed ‘javatest.stu_v_1’），不会插入成功（基表也不会）

视图的检查选项

当使用with check option 子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以符合视图的定义。MySQL允许基于另一个视图创建视图，他还会检查依赖视图中的规则以保证一致性。为了确定检查范围，MySQL提供了两个选项：

• cascaded
• local

默认值为cascaded

cascaded

例：

v2视图依赖于v1视图，v2添加检查选项

此时视图二（v2）添加数据时，不仅要满足v2的条件，还要满足v1的条件，就算v1上没有后面的with，但是相当于加上了一样

local

v3依赖于v2，v2依赖于v1，v2添加检查选项

哪个添加检查选项，哪个视图操作数据时才会去查看条件是否满足，比如v3添加id为9的，也没问题

2.4.1.3视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系，如果视图包含以下几项，则视图不可更新（报错）

• 聚合函数或窗口类函数（SUM()、MIN() 、MAX() 、COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或者 UNION ALL

作用：

简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些经常使用的查询可以被定义为视图，从而使得用户不必作为以后的操作每次指定全部的条件

安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们能见到的数据

数据独立

视图可以帮助用户屏蔽真实表结构变化带来的影响

应用：

查询每个学生选修的课程（三张表联查），这个功能在很多的业务中都用到，为了简化操作，定一个视图

#将三张表联查的结果做封装成视图
create view tb_stu_course_view as select s.name studen_name , s.no student_no, c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
1
2

当多次进行多表联查时，不需要再重新进行多表联合查询，只需要查询这个视图就可以了，这就是视图的方便之处，当数据被更新时，此处的视图内的数据也会被更新，见2.4.1.2，可以查看视图内的数据：

select * from tb_stu_course_view;
1

2.4.2存储过程

2.4.2.1介绍

介绍

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

存储过程思想上很简单，就是对数据库SQL语言层面的代码封装与重用

特点

• 封装
• 复用

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

减少网络交互，效率提升

2.4.2.2基本使用

创建

create procedure 存储过程名称([参数列表])
begin 

--SQL语句

end;
1
2
3
4
5
6

例：

create procedure p1()

begin

select count(*) from tb_user;

end;
1
2
3
4
5
6
7

运行之后可以看到多了一个函数，下面有这个刚刚编写的内容

执行结果：

调用

CALL 名称([参数])
1

例子：

call p1();
1

执行结果：

查看

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

--查看某个存储过程的sql
show create procedure 存储过程名称
1
2
3
4
5

例子：

--查看 javatest数据库下的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'javatest';
1
2

运行结果：

--查看指定存储过程
show create procedure p1();
1
2

运行结果：

删除

drop procedure if exists p1;
1

注意：在命令行中，执行创建存储过的sql时，需要通过关键字delimiter执行sql语句的结束符

2.4.2.3变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器成名。分为全局变量（GLOBAL）、会话变量（SEESION）

比如navicat，新建两个查询：

#每个查询算一个会话（session），在session级别设置的对当前session内有效，另外一个session无效，还是原来的，不会影响其他会话#

#全局设置的话对两个都生效#

• 查看系统变量

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

--可以通过LIKE模糊匹配方式查找变量
show [session | global] variables like '.....';

--查看指定变量的值
select @@[session | global] 系统变量名
1
2
3
4
5
6
7
8

例：

-- 查看系统变量
show session variables;

-- 查看当前会话跟事务有关的变量
show session variables like 'auto%';

-- 查看全局跟事务有关的变量
show global variables like 'auto%';

-- 查看某个环境变量  1表示开启
select @@autocommit;
1
2
3
4
5
6
7
8
9
10
11

• 设置系统变量

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

set @@[session | global] 系统变量名=值;
1
2
3

例：

-- 关闭当前会话的autocommit，此时在对一张表插入数据时，会插入成功，但是不会更新表内容，需要手动提交（再次执行commit）
set session autocommit = 0;
select @@autocommit;

insert into tb_user(id,username) values (16,"add");
commit;

-- 设置全局的，会对全部会话生效，但是服务期重新启动之后，仍然会变为默认值
set global autocommit = 0;
select @@global.autocommit;
1
2
3
4
5
6
7
8
9
10

如果指定session\global，默认是session，会话变量。MySQL服务器重新启动之后，所设置的全局参数将会失效，要想永久使用不失效，可以在/etc/my.cnf下配置（修改配置文件）

用户自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，再用的时候直接用’@变量名’使用即可。其作用域为当前连接

• 赋值

set @var_name = expr[,@var_name = expe]...;

set @var_name := expr[,@var_name := expe]...;

select @var_name :=expr[,@var_name := expr]...;

select 字段名 into @var_name from 表名;
1
2
3
4
5
6
7

例：

set @myname = '张三';

set @myage :=10;

set @mygender := '男',@myhobby := 'java';

--另外一种赋值
select @mycolor := 'red';

-- 将tb_user查询出的结果赋值给@mycount
select count(*) into @mycount from tb_user;
1
2
3
4
5
6
7
8
9
10
11

-- 使用
select @myname,@myage,@mygender,@myhobby;
1
2

-- 另外一种
select @mycolor,@mycount 
1
2

• 使用

select @var_name;
1

注意：用户定义的变量无需对其进行声明或者初始化，只不过获取到的值为null

局部变量

局部变量是根据需要定义子局部生效的变量，访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN…END块

• 声明

declare 变量名 变量类型 [default...];
1

类型就是数据库字段类型[int、bigint、char、varchar等]

• 赋值

set 变量名 = 值;

set 变量名 := 值;

select 字段名 into 变量名 from 表名...;
1
2
3
4
5

例：

-- 创建存储过程
create procedure p2()
begin
	declare stu_count int default 0;
	select count(*) into stu_count from tb_user;
	select stu_count;
end;

-- 查看该存储过程
call p2();     -- stu_count = 9
1
2
3
4
5
6
7
8
9
10

2.4.2.4if条件判断

if

• 语法

if 条件1 then
	...

elseif 条件2 then		-- 可选
	...    

else				-- 可选
	...

end if;
1
2
3
4
5
6
7
8
9
10

例：

自定义存储过程，根据定义的分数score变量，判断当前分数对应的分数等级

1. score >= 85 分，等级为优秀
2. score >=60 且 score < 85 ，等级为及格
3. score <60 分，等级为不及格

create procedure p3()
begin
	declare score int default 58;
	declare result varchar(10);
	
	if score >= 85 then
		set result := '优秀';
	elseif score >=60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	select result;
end;

call p3();  -- 不及格
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

但是这样的是不是就写死了呢？我们需要可以控制分数，这时我们就需要用到参数了，类似java的带参方法

参数

• 语法

create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
	-- sql
end;
1
2
3
4

例：对上面的分数等级修改

-- 记录传入参数和传出参数
create procedure p4(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;
	
	-- 不在需要了，因为result是返回参数，会自动返回
	-- select result;
end;

-- 返回值属于用户自定义变量 自己定义的变量@result接收
call p4(68,@result);

select @result; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 再加一个需求：将传入的200分制的分数进行换算，换算成百分制然后返回

-- 使用inout 作为传入和传出
create procedure p5(inout score double)
begin
	set score := score * 0.5;
end;

-- 执行
set @score = 78;

call p5(@score);

select @score;  -- 39
1
2
3
4
5
6
7
8
9
10
11
12

2.4.2.5case函数

• 语法一

case case_value
	when when_value1 then statement_list1
	[when when_value then statement_list2]...
	[else statement_list]
end case;
1
2
3
4
5

• 语法二

case case_value
	when search_condition1 then statement_list1
	[when search_condition2 then statement_list2]...
	[else statement_list]
end case;
1
2
3
4
5

例：

根据传入的月份，判定所属的季节（要求采用case结构）

1. 1-3月份，为第一季度
2. 4-6月份，为第二季度
3. 7-9月份，为第三季度
4. 10-12月份，为第四季度

create procedure p6(in month int)
begin

	declare result varchar(10);
	
	case
		when month >=1 and month <=3 then
			set result := '第一季度';
		when month >=4 and month <=6 then
			set result := '第二季度';
		when month >=7 and month <=9 then
			set result := '第三季度';
		when month >=10 and month <=12 then
			set result := '第四季度';
		else 
			set result := '非法参数';
	end case;
	
	-- concat字符串拼接
	select concat('您输入的月份为：',month,'，所属的季度为：',result);
	
end;


call p6(7);

call p6(16);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.4.2.6循环

while循环

while循环时有条件的循环控制语句。满足条件后，再执行循环体中的sql语句，具体语法：

• 语法

while 条件 do
	-- sql逻辑
end while;
1
2
3

例：

计算从1累加到n的函数值

create procedure p7(in n int)
begin
	declare total int default 0;
	while n > 0 do
	
		set total := total + n;
		-- 退出条件
		set n := n-1;
	
	end while;
	
	select total;
end;

call p7(10); -- 55

call p7(100) -- 5050
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

repeat

repeat是有条件的循环控制语句，当满足条件时就会退出循环

• 语法

repeat
	-- sql逻辑
	until 条件
end repeat;
1
2
3
4

例

计算从1累加到n的函数值

create procedure p8(in n int)
begin
	declare total int default 0;
	repeat 
		set total := total + n;
		
		set n := n - 1;
		
	until n<=0
	end repeat;
	select total;
end;

call p8(10); -- 55
call p8(100); -- 5050
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用：

• leave：配合循环使用，退出循环

• iterate：必须在循环中使用，作用是跳过当前的循环，执行下一次的循环，相当于java中的continue

• 语法

[begin_label:] loop
	-- sql逻辑
end loop [end_label]

leave label;-- 退出指定标记的循环体

iterate label; -- 进行下次循环
1
2
3
4
5
6
7

例：

1. 计算从1累加到n的值，n为传入的参数值

create procedure p9(in n int)
begin
	declare total int default 0;
	
	sum:loop
		if n <= 0 then
		-- 满足条件退出sum循环
			leave sum;
		end if;
		
		set total := total + n;
		set n := n - 1;
		
	end loop sum;
	
	select total;
end;

call p9(10);  -- 55
call p9(100); -- 5050
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2. 计算从1到n之间的偶数累加的值

create procedure p10(in n int)
begin
	declare total int default 0;
	
	sum:loop
		if n <= 0 then
		-- 满足条件退出sum循环
			leave sum;
		end if;
		
		if n%2 != 0 then
		-- 执行下次循环
			set n := n - 1;
			iterate sum;
		end if;
		
		set total := total + n;
		set n := n - 1;
		
	end loop sum;
	
	select total;
end;

call p10(10); -- 30
call p10(100); -- 2550
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.4.2.7游标

游标是用来存储擦护心结果集的数据类型，在存储过程和函数中使用游标可以对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close

语法：

• 声明游标

declare 游标名称 cursor for 查询语句;
1

• 打开 游标

open  游标名称;
1

• 获取游标记录

fetch 游标名称 into 变量[,变量];
1

• 关闭游标

close 游标名称;
1

例：

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表（id,name,profession)中

create procedure p11(in uage int)
begin

 	-- 存游标内的值，基本变量的声明必须在游标的声明之前
    declare uname varchar(100);
    declare upro varchar(100);

	-- 声明游标，存储查询结果集
	declare u_cursor cursor for select username,profession from tb_user where age <= uage;
	
	-- 创建存储表
	create table if not exists tb_user_pro(
    	id int primary key auto_increment,
        username varchar(100),
        profession varchar(100)
    );
    
    -- 开启游标，获取游标内的数据，往新的表中插入
    open u_cursor;
    
    -- 循环获取数据
    while true do
    	-- 赋值
    	fetch u_cursor into uname,upro;
    	
    	-- 插入新表中
    	insert into tb_user_pro values(null,uname,upro);
    
    end while;

	close u_cursor;
end;

-- 测试
call p11(40);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

但是这样会有问题，因为循环的条件时true，是个死循环，报错信息如下，怎么办呢？

条件处理程序

条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，具体语法为：

declare hanler_action handler for condition_value [,condition_value].... statement;

handler_action
	continue: 继续执行当前程序
	exit:终止执行当前程序
condition_value
	sqlstate sqlstate_value:状态码，如02000
	sqlwarning：所有以01开头的sqlstate代码的简写
	not found:所有以02开头的sqlstate代码的简写
	sqlexception:所有没有被sqlwarnning或not found 捕获的sqlstate代码的简写
1
2
3
4
5
6
7
8
9
10

对上面的代码改进：

create procedure p11(in uage int)
begin

 	-- 存游标内的值，基本变量的声明必须在游标的声明之前
    declare uname varchar(100);
    declare upro varchar(100);

	-- 声明游标，存储查询结果集
	declare u_cursor cursor for select username,profession from tb_user where age <= uage;
	
	-- 报错信息的开头是02000,声明退出程序，退出时关闭游标
	declare exit handler for sqlstate '02000' close u_cursor;
	-- 以下几种方式也可以：
	-- declare exit handler for not found close u_cursor;

	
	-- 创建存储表
	create table if not exists tb_user_pro(
    	id int primary key auto_increment,
        username varchar(100),
        profession varchar(100)
    );
    
    -- 开启游标，获取游标内的数据，往新的表中插入
    open u_cursor;
    
    -- 循环获取数据
    while true do
    	-- 赋值
    	fetch u_cursor into uname,upro;
    	
    	-- 插入新表中
    	insert into tb_user_pro values(null,uname,upro);
    
    end while;

	close u_cursor;
end;

-- 测试
call p11(40);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

此时就没问题了

2.4.3存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型。具体语法如下：

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

-- 必须要指定其中一个
characteristic说明：
	deterministic：相同的输入参数总是产生相同的结果
	no sql：不包含sql语句
	reads sql data：包含读取数据的语句，但不包含写入数据的语句
1
2
3
4
5
6
7
8
9
10
11
12

例：

计算从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(100);  -- 5050
select fun1(50);   -- 1275
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

存储函数使用的较少，因为存储函数能做的，存储过程基本都可以做，并且存储函数必须要有返回值，有时候并不需要，所以能使用存储过的地方，尽量使用存储过程替代

2.4.4触发器

介绍

触发器是与表有关的数据库对象，指在insert\update\delete之前或之后，触发并执行触发器中定义的SQL语句的集合。触发器的这种特性可以协助在应用数据库端确保数据的完整性，记录日志，数据效验等操作

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

（触发器类似Spring AOP）

语法

• 创建

create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
 trigger_stmt; 
end;
1
2
3
4
5
6

• 查看

show triggers;
1

• 删除

drop trigger [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库
1

例：

通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除；

1. 建立日志表的表结构

create table user_logs(
	id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型,insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作id',
    operate_params varchar(500) comment '操作参数',
    primary key(id)
)engine = innodb default charset = utf8;
1
2
3
4
5
6
7
8

2. 定义触发器，在增加、修改、删除时都需要定义触发器

• insert的触发器

create trigger tb_user_insert_trigger
	-- 插入后记录数据,使用after，行级触发器
	after insert on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) 
	values 
	(null,
    'insert',
    now(),
    new.id,  -- 刚刚插入数据的id
     -- new 可以拿到更新后的记录,注意，new后的值在原表内不能为空，否则记录数会没有该字段的数据
    concat
    (
        '插入的数据内容为：id=',new.id,',name=',new.username,',phone=',new.phone,',profession=',new.profession)
    );
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 查看建立的触发器

show triggers;
1

• 删除触发器（演示触发器怎么删除，不代表此案例需要删除）

drop trigger tb_user_insert_trigger;
1

• 给tb_user插入数据

insert into tb_user(id,username,age,phone,profession,status) values (null,'李四',19,'12345678910','法学','软件工程')
1

此时查看日志表：

已经有数据了

2. update的触发器

create trigger tb_user_update_trigger
	-- 插入后记录数据,使用after，行级触发器
	after update on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) 
	values 
	(null,
    'update',
    now(),
    new.id,  
     -- old可以拿到更新前的数据
    concat(
        '更新前的数据：id=',old.id,',name=',old.username,',phone=',old.phone,',profession=',old.profession,
    	'|更新后的数据：id=',new.id,',name=',new.username,',phone=',new.phone,',profession=',new.profession)
    );
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 测试

update tb_user set profession = '软件工程' where id = 31;
1

• 日志表

• 另外一种情况

update tb_user set profession = '大数据' where id <= 7;
1

• 日志表

可以看到，也已经成功更新。

3. delete的触发器

create trigger tb_user_delete_trigger
	-- 插入后记录数据,使用after，行级触发器
	after delete on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) 
	values 
	(null,
    'delete',
    now(),
    old.id,  -- 删除前的id
     -- old可以拿到更新前的数据
    concat(
        '删除前的数据：id=',old.id,',name=',old.username,',phone=',old.phone,',profession=',old.profession)
    );
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 测试

delete from tb_user where id = 31;
1

• 日志表

可以看到，之前的数据也可以拿得到