数据库管理：掌握视图、存储过程与触发器

一、视图视图的应用

视图只能用来查询

视图是一个或着几个基本表导出的表，它与基本表不同，是一个虚表

1. 创建视图

格式：create view 视图名【view_xxx / v_xxx】 as ……

视图使得复杂的查询变得简单，是基于一个或多个基本表生成的虚表。以下示例展示了如何创建一个展示所有男性学生的视图

create view v_stu_man as
select * from student where ssex = '男'

2. 视图的使用：
   视图不仅能够简化对数据的访问，还能与其他表结合使用。
   例如，创建一个包含班级名称的视图：

select * from v_stu_man

create view v_vstuman_class as
select vsm.*,classname from v_stu_man vsm 
left join class on vsm.classid = class.classid

select * from v_vstuman_class 

3. 管理视图
   您可以通过以下查询查看数据库中所有视图，并通过简单的命令来删除不需要的视图：

-- 查看数据库中所有视图
select * from information_schema.VIEWS 
WHERE table_schema = 'study';
-- 删除视图
drop view v_stu_man

4. 视图的作用;
   • 简化查询
   • 重写格式化数据
   • 频繁的访问数据库
   • 过滤数据

二、存储过程

存储过程本质是一个函数

1. 为什么使用存储过程

• 业务流程复杂
• 频繁的访问数据库
• sql语句先编译后执行

2. 存储过程的缺点：

   1. 维护成本高
   2. 协作性差

3. 什么是存储过程

​ 存储过程就是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后在调用就不需要重复编译，用户通过指定存储过程的名字给出参数来执行

格式：

create procedure 存储过程名字【proc_xxx】(形参列表)
begin
		一组sql语句集 
end

4. 定界语句delimiter $$

delimiter $$
create procedure proc_test()
begin 
		select * from student;
		
end $$
delimiter ; -- 恢复默认的定界符

5. 使用存储过程

call proc_test();

6. 带参数的存储过程

delimiter $$
create procedure proc_test2(
in a int, -- in只入参
out b int, -- out 只出参
inout c int -- inout 出入参
)
begin
	set a=a+1;
	set b=b+100;
	set c=c+1000;
end $$
delimiter ;

7. 环境变量

• @ 局部环境变量
• @@ 全局环境变量

set @x = 10;   -- 11 10 null
set @y = 20;     -- 120 20 null
set @z = 30;     -- 1030 30 null 

select @x, @y, @z;

8. 分页查询的存储过程
   以下示例展示了如何通过存储过程实现学生表的分页查询：

drop procedure proc_stupage;

delimiter $$
create procedure proc_stupage(
in curpage int,
in sizepage int,
out stucount int, -- 总共的数据
out pagecount int -- 共分了几页
)
begin
	declare cp int;  -- 定义变量
	set cp = (curpage-1)*sizepage;
	select count(*) from student into stucount; -- 总共的数据
	set pagecount = ceiling(stucount/sizepage); -- ceiling 向上取整 floor 向下取整
	select * from student limit cp,sizepage;
end $$
delimiter ;

set @a = 0;
set @b = 0;
call proc_stupage(1,3,@a,@b); -- 从第一个开始查询每页3个数据
select @a,@b;

三、触发器的使用

也是一种存储过程，会自动执行

触发器是数据库针对数据库表操作触发的特殊存储过程

格式：

delimiter $$
create trigger 触发器名【trig_xxx】
before/after  insert/update/delete 事件
on 表名 for each row
begin
-- 事件触发后要写的语句
-- old 已经存在的数据、new 还不存在的数据
	delete from sc where sid = old.sid;
end $$
delimiter ;

1. 查看所有的触发器

SELECT * FROM information_schema.`TRIGGERS`
WHERE trigger_schema = ‘库名'

2. 删除触发器

DROP TRIGGER 触发器名

通过对视图、存储过程和触发器的掌握，您将大幅提升在数据库管理中的效率与准确性。希望本文能够帮助您更好地理解和应用这些强大的功能！