【MySQL】视图、存储过程、触发器

目录

一、视图

1. 创建视图

2. 视图的使用

3. 查看视图

4. 删除视图

5. 视图的作用

二、存储过程 

1. 存储过程的创建

2. 使用存储过程

3. 带参数的存储过程

3.1 分页

4. 存储过程与函数的区别

5. 存储过程的缺陷

三、 触发器  -- 隐式执行

1. 创建触发器

2. 查看库中所有触发器

3. 删除触发器

4. 什么是触发器

5. 存储过程和触发器的区别

---

一、视图

        视图是从一个或者几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。

        视图只能用来查询，不能做增删改。

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;

--

select * from v_stu_man

left join class on v_stu_man.classid=class.classid;

select * from v_stu_man vsm             -- 别名vsm

left join class on vsm.classid=class.classid;

create view v_vstunman_class as

select v_stu_man.*,classname from v_stu_man

left join class on v_stu_man.classid=class.classid;

select * from v_vstunman_class;

update student set sname='赵雷雷' where sid=1;

select * from student;

3. 查看视图

（1）查看视图的创建

        show create view 视图名;

（2）查看库中所有的视图

        select * from information_schema.views

                where table_schema='myschool';

4. 删除视图

        drop view v_stu_man;

5. 视图的作用

（1）简化查询

（2）重写格式化数据

（3）频繁访问数据库

（4）过滤数据

二、存储过程 

        本质是一个函数

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

1. 存储过程的创建

-- create procedure 存储过程名【proc_xxx】(形参列表)

-- begin

--   一组sql语句集

-- end

-- 创建最简单存储过程

delimiter $$    -- 定制定界符

create procedure proc_test()

begin

    select * from student;

end $$

delimiter ;

2. 使用存储过程

        call proc_test();

3. 带参数的存储过程

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 ;

-- 环境变量  @局部变量,   @@ 全局变量

        set @x=10;

        set @y=20;

        set @z=30;

        call proc_test2(@x,@y,@z);

        select @x,@y,@z;

-- 面试题

3.1 分页

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;   -- （页码-1）*步长

    select count(*) from student into stucount;      -- into:count(*)值给stucount

    set pagecount = ceiling(stucount / sizepage);    -- ceiling 向上取整

    select * from student limit cp,sizepage;       -- 不允许写表达式

end $$

delimiter ;

-- 定义实参

        set @a = 0;

        set @b = 0;

        call proc_stuPage(2,3,@a,@b);

        select @a,@b；

-- 删除存储过程

        drop procedure proc_stuPage;

4. 存储过程与函数的区别

（1）语法

        关键字不同，存储过程是procedure，函数是function;

（2）返回值

        存储过程可以定义多个返回结果，函数只有一个返回值;

（3）执行

        存储过程可以独立执行，函数必须依赖表达式的调用;

（4）功能

        函数不易做复杂的业务逻辑，但是存储过程可以。

5. 存储过程的缺陷

（1）维护性

        存储过程的维护成本高，修改调试较为麻烦。

（2）移植性

        大多数关系型数据库的存储过程存在细微差异。

（3）协作性

        没有相关的版本控制或者IDE，团队中对于存储过程的使用大多是依赖文档。

三、 触发器  -- 隐式执行

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

1. 创建触发器

-- delimiter $$

-- create trigger 触发器【trig_xxx】

-- before/after   insert /update/delete

-- on 表名  for each row

-- begin

--     触发后执行的一组sql语句

-- end $$

-- delimiter ;

-- 删除学生表数据，先删从表sc的数据

delimiter $$

create trigger trig_delstu_delsc

before delete on student for each row

begin

    -- old 已经存在的数据   new 还不存在的数据

    delete from sc where sid=old.sid;

end $$

delimiter ;

delete from student where sid=16;

select * from sc;

select * from student;

2. 查看库中所有触发器

select * from information_schema.`triggers`

where trigger_schema='myschool';

3. 删除触发器

        drop trigger 触发器名;

drop trigger trig_delstu_delsc;

4. 什么是触发器

        

5. 存储过程和触发器的区别

（1）语法

        关键字不同，存储过程是procedure，触发器是trigger;

（2）返回值

        存储过程可以定义返回值，但是触发器没有返回值;

（3）执行

        存储过程需要调用才执行，触发器自动执行;

（4）功能

        存储过程是一组特定功能的SQL语句，触发器则是SQL语句前后执行，本身不影响原功能。