数据库(MySQL)-视图、存储过程、触发器

一、视图

视图的定义、作用

           视图是从一个大概几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。但是视图只能用来查看表，不能做增删改查。            视图的作用：①简化查询  ②重写格式化数据  ③频繁访问数据库  ④过滤数据                      创建视图

        语法：create view 视图名【view_xxx/v_xxx】as 查询语句

1. create view v_stu_man as
2. SELECT * from student where ssex='男'

复制代码

使用视图

         语法：SELECT * FROM 视图名

1. SELECT * FROM v_stu_man

复制代码

结果：

 案例1：查看男同学的信息和班级

1. create view v_stuman_class as
2. select v_stu_man.*,classname from v_stu_man
3. left join class on v_stu_man.classid=class.classid

复制代码

结果： 

         留意：student 表改变，视图会跟着一起改变

1. UPDATE student SET sname='赵蕾蕾' WHERE sid=1

复制代码

        表中数据已改：
 

        查看视图中的信息：

1. SELECT * FROM v_stu_man

复制代码

         结果：

 查看库中所有视图

1. SELECT * from information_schema.views
2. where table_schema='myschool'

复制代码

结果：

 
删除视图

1. drop VIEW v_stu_man

复制代码

二、存储过程

存储过程的定义

           存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后，再次调用不需要重复编译，用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数）来执行它。   创建存储过程

语法

CREATE PROCEDURE 存储过程名【proc_xxx】(形参列表)
BEGIN
    一组SQL语句集
END
案例

案例1：创建最简单的存储过程
delimiter $$定订定界符，delimiter ;;恢复默认定界符

1. delimiter $$
2. create procedure proc_test()
3. begin
4.     select * from student;
5. end $$
6. delimiter ;
7. -- 使用存储过程
8. CALL proc_test();

复制代码

结果：

案例2：创建带参的存储过程 

1. delimiter $$
2. create PROCEDURE proc_test2(
3.     in a int,  -- 只入参
4.     out b int,  -- 只出参
5.     inout c int -- 出入参
6. )
7. begin
8.    set a=a+1;
9.    set b = b+100;
10.    set c = c+1000;
11. end $$
12. delimiter ;
14. #设置环境变量：@局部环境变量  @@全局环境变量
15. set @x=10;
16. set @y = 20;   
17. set @z = 30;
19. select @x, @y, @z;
21. -- 运行结果为图1
22. -- 使用存储过程
23. call proc_test2(@x,@y,@z)
25. select @x, @y, @z;
26. -- 运行结果为图2

复制代码

结果1：

结果2：

 
        为什么使用了存储过程后会得到这样的结果呢？
        由于在我们定义形参a、b、c时，前面给他们加了修饰符:in、out、inout。所以a是只入参数：即只能将参数传入，而不参与运算,所以a为10。b是只出参，即无法给它传入值，所以它为Null。c是收支参，即既可以将参数传入，也可以参与运算输出，所以c为1030。
案例3：使用存储过程到达分页结果

1. delimiter $$
2. create procedure proc_stuPage(
3.     in curpage int,
4.     in sizepage int,
5.     -- 学生数
6.     out stucount int,
7.     -- 页数
8.     out pagecount int
9. )
10. begin
11.     #declare:声明局部变量
12.     declare cp int;
13.     set cp=(curpage-1)*sizepage;
14.     -- 把表中的值存入int类型的变量(此表只能有一条数据)
15.     select count(*) from student into stucount;
16.     set pagecount=ceiling(stucount / sizepage);
17.     select * from student limit cp,sizepage;
18. end $$
19. delimiter ;
20. set @a = 0; -- stucount
21. set @b = 0; -- pagecount
22. call proc_stuPage(2,3,@a,@b); -- 结果1
24. -- 查看学生数和页数
25. select @a,@b -- 结果2

复制代码

结果1：

结果2：

 
存储过程与函数的区别

语法：关键字不同，存储过程是procedure， 函数是function；
执行：存储过程可以独立执行，函数必须依赖表达式的调用；
返回值：存储过程可以定义多个返回结果， 函数只有一个返回值；
功能：函数不易做复杂的业务逻辑，但是存储过程可以。
存储过程的缺陷

维护性：存储过程的维护本钱高，修改调试较为贫苦。
移植性：大多数关系型数据库的存储过程存在渺小差别
协作性：没有相干的版本控制大概IDE，团队中对于存储过程的使用大多是依赖文档。
三、触发器

触发器定义

        触发器是数据库中针对数据库表操作触发的 特别的存储过程。
创建触发器

语法

delimiter $$
CREATE TRIGGER 触发器名【trig_xxx】
BEFORE/AFTER -- 执行次序
INSERT/UPDATE/DELETE -- 触发事件
ON 表名
案例

案例1：删除学生 sid 为 1  在此之前把学天生绩删除

1. -- 创建触发器
2. delimiter $$
3. create trigger trig_delstu_delsc
4. before delete on student for each row
5. begin
6.     delete from sc where sid = old.sid;
7. end $$
8. delimiter ;
10. -- 触发事件
11. DELETE FROM student WHERE sid=1;
13. -- 查看学生表
14. select * from student;
15. -- 查看成绩表
16. select * from sc;

复制代码

学生表结果：
 

成绩表结果：
 

查看所有触发器

1. SELECT * FROM information_schema.`TRIGGERS`
2. WHERE trigger_schema = 'myschool'

复制代码

结果：

 
删除触发器 

1. DROP TRIGGER trig_delstu_delsc

复制代码

删除后查看所有触发器：

 
存储过程和触发器的区别

语法：关键字不同，存储 过程是procedure， 触发器是trigger
执行：存储过程需要调用才执 行，触发器自动执行
返回值：存储过程可以定义返回值， 但是触发器没有返回值；
功能：存储过程是一组特定功能的 SQL语句，触发器则是SQL语 句前后执行，本身不影响原功 能。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。