【MySQL】视图➕存储条件➕触发器

---

前言

学生党如何过周末？我学MySQL

---

一、视图

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

# 创建视图（id/name两个字段）
create or replace view stu_v_1 as select id, name from student where id <= 10;

# 查询创建视图语句
show create view stu_v_1;
# 查询视图数据
select * from stu_v_1 where id < 3;

# 修改（id/name/no三个字段）
create or replace view stu_v_1 select id,name,no from student where id <= 10;
# 或者
alter view stu_v_1 as select id,name,no from student where id <= 10;

# 删除
drop view if exists stu_v_1;

# 插入（insert）：视图是一张虚拟的表，其实是插入到真实的表中
insert into stu_v_1 values(10, 'Tom'); # 插入成功，视图查询成功
insert into stu_v_1 values(30, 'Tom'); # 插入成功，但是视图查询不到，因为id<=10

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图是一张虚拟存在的表，可以像操作表一样操作视图

🍔检查选项：with cascaded/local check option，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则（就是会一级一级往上找）以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和LOCAL，默认值为CASCADED

而local选项不递归找依赖，只检查当前语句，除非依赖的（上一级）语句也有检查选项

🍔视图的更新：要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新

1. 聚合函数或窗口函数，SUM()、MIN()、MAX()、COUNT()等
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

🍔视图的作用：

1. 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2. 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3. 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响（起个别名）

🌰根据实际需求，定义视图：

# 1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
creat view tb_user_view as select id, name , profession,age ,gender,status,createtime from tb_user;

select * from tb_user_view;

# 2．查询每个学生所选修的课程（三张表联查)，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
select s.name, s.no ,c.name from student s, student_course sc , course c whene s.id = sc.studentid and sc.courseid = c.id;

creat view tb_stu_course_view as select s.name student_name, s.no student_no,c.name course_name from student s , student_course sc , course c whene s.id = sc.studentid and sc.courseid = c.id;

select * from tb_stu_course_view;

二、存储过程

1. 基本知识

🍔介绍：存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用（类似函数）

🍔特点：

1. 封装，复用
2. 可以接收参数，也可以返回数据
3. 减少网络交互，效率提升

🍔参数：

🍔基本语法：

# 创建（可以传参）
create procedure p1()
begin
	select count(*) from student;
end;

# 调用
call p1();

# 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINES_SCHEMA = 'itcast';

# 查询某个存储过程的定义
show create procedure p1;

# 删除
drop procedure if exists p1;

注意：
在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符（begin之后的SQL语句加逗号会结束该命令），例如：输入delimiter $$后，SQL语句以$$结束，而不是分号结束

2. 变量

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

# 查看所有系统变量(默认会话级别)
show variables;

# 查看所有自动提交的系统变量(默认会话级别)
show variables like 'auto%';
show global variables like 'auto%'; 

# 准确查看系统变量(全局)的值
select @@global.autocommit;

# 设置系统变量(全局级别的重启又恢复默认值)
set autocommit = 0;

注意：

1. 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
2. mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf 中配置。
3. 两个@符号是系统变量，一个是自定义变量

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

# 赋值
set @myname = 'ygy';
set @myage := 20;
set @mygender := '男',@myhobby = 'ctrl';

selec count(*) into @mycount from tb_user;

# 使用
select @myname,@myage;

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

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

# 声明/赋值
create procedure p2()
begin
	declare stu_count int default 0;
	select count(*） into stu_count from student;
	select stu_count;
end;

3. 条件判断语句

🌰if语法与Shell命令类似，直接上练习：

create procedure p3(in score int, out result varcher(10))
begin
	declare result varchar(20);
	
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else 
		set result := '不及格';
	end if;
end;

# 调用
call p3(99, @result);
select @result;

# 需求升级：传入200分制，进行换算，然后返回分数 -- > inout
create procedure p4(inout score double)
begin
	set score := score * 0.5;
end;

# 调用
set @result := 178;
call p4(@result);
select @result;

🍔case语法：

🌰上练习：

create procedure p5(in month int)
begin
	declare result varchar(20);
	
	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;
	select concat('月份：',month,'所属的季度为:',result);
end;

4. 条件循环语句

🌰while上练习：

create procedure p6(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;

🍔repeat语法：

🌰同上，改用repeat：

create procedure p7(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;

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

• LEAVE：配合循环使用，退出循环。
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

🌰练习：

create procedure p8(in n int)
begin
	declare total int default 0;
	
	sum:loop
		if n<= 0 then
			leave sum;
		end if;
		
		set total := total + n;
		set n := n - 1;
	end loop sum;
	
	select total;
end;

# 需求升级，如单词累加的数据是奇数，则直接进入下一次循环(iterate)
create procedure p9(in n int)
begin
	declare total int default 0;
	
	sum:loop
		if n<= 0 then
			leave sum;
		end if;
		
		if n%2 = 1 then
			set n := n - 1;
			iterate sum;
		end if;
		
		set total := total + n;
		set n := n - 1;
	end loop sum;
	
	select total;
end;

5. 游标

🍔游标（CURSOR）：是用来存储查询结果集的数据类型，接受一个表或者一个结果集，在存储过程和函数中可以使用游标对结果集进行循环的处理。
🍔游标通常用于以下情况：

1. 需要逐行处理查询结果：当需要按行遍历查询结果，并对每一行进行特定的操作时，可以使用游标进行逐行处理。
2. 处理大量数据：当查询结果包含大量数据时，一次性将所有数据取出可能会导致内存溢出。使用游标可以分批处理数据，节省内存并提高性能。
3. 允许随机访问：游标允许在查询结果中进行前进、后退或定位到指定位置的操作，以满足特定需求。

🍔游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下

🌰练习：

# 逻辑:
# A.声明游标，存储查询结果集
# B.准备:创建表结构
# c.开启游标
# D.获取游标中的记录
# E.插入数据到新表中
# F.关闭游标

create procedure p10(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_use where age <= uage;
	
	drop table if exists tb_user_pro;
	creat table if not exists 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;

但是while循环没有退出条件，游标指向为空时还在循环里，会导致报错

6. 条件处理程序

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

🌰针对上一个例子优化：

create procedure p10(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_use where age <= uage;
	
	declare exit handler for SQLSTATE '02000' close u_cursor;
	# declare exit handler for not found close u_cursor;
	
	drop table if exists tb_user_pro;
	creat table if not exists 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;

三、存储函数

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

🌰从1累加到n的存储函数：

create function f1(in n int)
return 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 f1(49);

存储函数用的少，因为存储函数能做到的事情，存储过程也能做到，且存储函数必须要有返回值

四、触发器

🍔触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

行级触发器，例如执行一条update语句，影响了五行，这个时候触发器会被触发五次
语句级触发器，例如执行一条update语句，触发器只会被触发一次

🍔语法：

🌰练习：

# 插入数据的触发器
create trigger tb_user_insert_trigger
	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,concat('插入数据内容为：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession)
end;

# 查看
show triggers;

# 删除
drop trigger tb_user_insert_trigger;

# 修改数据的触发器
create trigger tb_user_update_trigger
	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,concat(
	'更新之前数据内容为：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession,
	'更新之前数据内容为：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession));
end;


# 删除数据触发器
create trigger tb_user_delete_trigger
	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,concat(
	'删除数据内容为：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession)
end;

---

总结

这一节学习了MySQL的视图、存储过程、触发器，下一节锁🔒