MySQL进阶篇3-视图和存储过程以及触发器的学习使用

视图/存储过程（函数）/触发器

视图：由表动态生成，虚拟的表，保存的是sql的逻辑。

创建视图：

​ create [or replace] view viewName【列名列表】 as select 语句 [with [cascaded|local] check option]

修改视图：

​ 1、【or replace】必须存在。

​ 2、alter view viewName as …

删除视图：

​ drop view 【if exists】 viewName;

with [cascade|local] check option

操作视图：

1、添加数据。

【insert into view1 values(…)】数据添加到了基表中。

​ 例如，视图是id < 30的进去视图。但是通过视图添加id为40的数据，数据添加成功，但是视图查询不到。因为id<30。

​ 解决： with cascaded check option；增加关联。

​ 通过视图1创建视图2,如果添加了限制条件，两个视图都要检查。给依赖的视图也添加了限制条件。

2、 with local check option :

当前的视图限制，递归查找之前的。但是限制不进行传递操作，依赖的视图有限制就有，没有限制就没有。

3、视图更新条件：

​ 视图更新必须与基础表存在行记录1:1的关系。

​ 例如：sum min max count distinct group by、having、union、union all

视图作用：

​ 1、简化操作，直接查询视图就行

​ 2、安全。【数据库可以进行用户授权，但是不能授权到特定行和特定列上。通过视图，用户只能查询和修改他们能见到的数据】

​ 3、数据独立。视图可以帮助用户屏蔽真实表结构变化带来的影响。

存储过程

特点：

​ 1、封装、复用

​ 2、可以接收参数，也可以返回数据

​ 3、减少网络交互，效率提升

创建

create procedure name(args)

begin

​ --sql

end;

create procedure p1()
begin
	select count(*) from students;
end;
1
2
3
4

调用

call procedure_name(args);

查看存储过程

select * from infomation_schema.routines where routine_schema = 'xxx'; --查询指定数据库的存储过程及状态信息
show create procedure p1;--查询某个存储过程的定义
1
2

删除存储过程

drop procedure [if exists] p1;
1

语法结构

变量

1、系统变量：是MySQL服务器提供，不是由用户定义的，属于服务器层面。分为全局变量Global、会话变量Session。

查看系统变量：

show [session|global] variables [like '_ _ _'];
select @@[session|global] 系统变量名
1
2

设置系统变量

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

虽然设置的是全局的，但是服务器重启后，又会初始化为默认值。

2、用户自定义变量

​ 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用**@变量名**使用就行，其作用域为当前连接。

赋值操作

--赋值操
set @var_name = xxxx
set @var_name := xxxx
select @var_name:= xxx
select 字段名 into @var_name from 表；
--使用
select @var_name;
1
2
3
4
5
6
7

随便一变量不会报错，是null。

3、局部变量

​ begin----end之内，用**declare 变量名 变量类型** 来声明。

create procedure p2()
begin
	declare myCount int default 0;
	set myCount = 1;
	select myCount;
end;
call p2();
1
2
3
4
5
6
7

语法if elseif end if

if score > 85 then 
	set result = '优秀'
elseif score > 60 then
	set result = '及格'
else
	set result = '不及格'
end if;sql
1
2
3
4
5
6
7

存储过程参数

create procedure p4(in score int,out result varchar(10))
.......
call p4(55,@result);
select @result;

inout
set @score = 75;
call p5(@score);
select @score;

1
2
3
4
5
6
7
8
9
10

case函数

case when value1='1' then '1'
	when value1='2' then '2'
	else 3
end case;
1
2
3
4

while循环

while 1=1 do 
		sql逻辑  set ...
end while;
1
2
3

repeat循环

repeat
	sql逻辑 set ...
	util 条件满足，退出循环。【满不满足，至少执行一次】
end repeat;
1
2
3
4

loop循环

loop实现简单的循环，如果不在sql逻辑中增加推出循环的条件，可以实现死循环。

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

iterate：必须在循环中，作用是跳过当前循环剩下的语句，直接进入到下一次循环。

create procedure p1(in n int)
begi
	declare total int default 0;
	sum:loop
		if n<=0 then
			leave loop;
		end if;
		if n%2=1 then
			set n = n-1;
			iterate sum;
		end if;
		set total = total+1;
		set n = n-1;
	end loop sum;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

sum相当于loop循环的名称。

游标

用来存储，查询结果集的。在存储过程和函数中，可以使用游标对结果集进行循环处理【因为是结果集，需要循环处理每一行】。

使用：declare 声明，open、fetch、close

decalre cursor_name cursor for 查询语句；

open cursor_name;

fetch cursor_name into 变量

close cursor_name;

条件处理程序

条件处理程序，就是当遇到sql异常时，需要做的事情。

declare exit handler for SQLSTATE ‘02000’ close u_cursor;

捕获异常02000时，退出exit，并且执行close cursor操作。

for not found 也可以，处理的是02开头的状态码；

create procedure p1(int uage int)
begin
	
	delcare uname varchar(100);
	declare upr varchar(100);
	declare u_cursor cursor for select name , profession from user where age <= uage; --游标必须放在后边
	declare exit handler for SQLSTATE '02000' close u_cursor;--捕获异常
	drop table if exists tb_user_pro;
	create table tb_user_pro(
    	id int primary key auto_increment,
        name 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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

存储函数

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

create function p1(args…)

returns type [characteristic…]

begin

​ --sql语句

​ return …;

end;

characteristic说明:

deterministic：相同的输入参数，总是能产生相同的输出结果。

no sql：不包含sql语句。

reads sql data：包含读取sql的语句，但是不包含写入树的语句。

create function f1(n int)
returns int deterministic
begin
	declare total int default 0;
	while n>0 then
		set total:= total + n;
		set n:= n-1;
	end while;
	return total;
end;

select f1(100);
1
2
3
4
5
6
7
8
9
10
11
12

弊端，必须得有返回值。

但是，存储过程也能有返回值。所以，存储过程可以完全替代存储函数。

触发器

​ 触发器与表有关，在insert/update/delete之前或者之后触发，执行触发器中定义的sql语句集合。

​ 协助应用在数据库确保**数据的完整性，日志记录，数据校验**等操作。

​ 使用别名**OLD和NEW**来引用触发器中发生变化的内容。现在触发器只支持行级触发，不支持语句级触发。【update影响了5行，触发5次触发器。而不是触发一次。】

定义触发器

create trigger trigger_name
before/after insert/update/delete
on table_name for each row --行级触发器
begin
	trigger_stmt;
end;
--查看
show triggers;
--删除
drop trigger [schema_name].trigger_name; --如果没有指定schema_name，默认是当前数据库。
1
2
3
4
5
6
7
8
9
10

触发器案例

 create trigger user_insert_trigger
 	after insert on user for each row
 begin
 	insert into user_logs values(null,'insert',now(),new.id,concat("插入的内容为:",new.id,new.name,new.phone,new.email));
 end;
1
2
3
4
5