【数据库（MySQL）基础】以MySQL为例的视图、存储过程与触发器

跟着黑马程序员 黑马MySQL数据库进阶教程，深入讲解mysql视图，存储过程以及 触发器学习做的笔记

1. 视图

视图是虚拟存在的表，通过实际的数据库表来生成面向不同用户和不同角色的视图（不同角色所需内容不一样）

1.1 视图创建

Create [or replace] View 视图名称 As (Select语句) [WITH [CASCADED|LOCAL] CHECK OPTION]

其中replace表示替换视图，感觉应该就是更新视图的意思？

1.2 视图查询

查询创建视图语句：show create view 视图名称，查出来的雨具包括原始创建视图语句中没有写的一些默认参数

查看视图数据：select * from 视图名称（类似于查询表一样的操作）

1.3 视图修改

方式一：修改视图可以用创建视图的语句，但是重点主要在于Replace，把视图内容替换掉，Create or Replace View 视图名称 As (Select语句)

方式二：Alter View 视图名称 As (Select)语句

1.4 视图删除

Drop View [If Exists] 视图名称

1.5 视图检查选项

使用WITH CHECK OPTION自居创建视图时，MySQL会通过视图检查正在更改的每个行，如插入，更新，删除，使其符合视图定义。MySQL允许基于另一个视图创建视图，还会检查依赖视图中的规则以保持一致性。

1.5.1 cascaded检查选项

Cascaded选项表明操作是否满足当前视图约束及当前视图所依赖视图的约束（父视图，递归检查所有的父视图），并不检查它的子视图（依赖于它的视图）

如果子视图自身没有约束，也会向上检查父视图，只要有父视图存在约束就不断向上检查

1.5.1.1 这个选项存在为了避免什么问题？

假设我们建立视图的语句为：create or replace view stu_v_1 as select id, name from student where id <= 20

那么我们可以通过select * from stu_v_1来查询视图中的内容

如果我们向视图中插入数据insert into stu_v_1 values(6,'Tom')、insert into stu_v_1 values(30,'Tom')，因为视图实际上不是存储数据的载体，表实际上才是存储数据的载体，所以这里向视图插入的数据会存储到实际的表中去

再次通过select * from stu_v_1来查询视图，发现无法查到id=30的那条数据，但实际上，我们是希望语句能够“插入”到视图中去的，也就是通过视图查询语句，我们可以查到id=30的数据

1.5.1.2 怎么利用这个选项

建立视图的语句修改成create or replace view stu_v_1 as select id, name from student where id <= 20 with cascaded check option

这时候执行insert into stu_v_1 values(30,'Tom')会报错：CHECK OPTION failed ‘…’

1.5.1.3 子视图

假如创建视图stu_v_1的语句是：create or replace view stu_v_1 as select id, name from student where id <= 20

创建了一个源自stu_v_1的语句stu_v_2：create or replace view stu_v_2 as select id, name from stu_v_1 where id >= 10 with cascaded check option

下列插入语句：

• insert into stu_v_2 values(7,'Tom')

• insert into stu_v_2 values(26,'Tom')

• insert into stu_v_2 values(15,'Tom')

的执行，是成功还是失败？

语句1，id=7的会执行失败，因为其不满足id>=10的约束

语句2，id=26的会执行失败，因为其不满足父视图id<=20的约束

语句3，id=15同时满足>=10&&<=20，执行成功

假如再创建一个源自stu_v_2的视图stu_v_3：create or replace view stu_v_3 as select id, name from stu_v_2 where id <= 15

下列插入语句：

• insert into stu_v_3 values(11, 'Tom')

• insert into stu_v_3 values(17, 'Tom')

• insert into stu_v_3 values(28, 'Tom')

的执行，是成功还是失败？

语句1，id=11的会执行成功，向上检查stu_v_2和stu_v_1的约束，分别是>=20和<=20

语句2，id=17的会执行成功，因为stu_v_3没有加wiith cascaded check option，所以不会检查其自身的约束，而是向上检查2和1两个视图的约束，满足，则可以执行成功

语句3，id=28的会执行失败，因为不满足stu_v_1的约束。

1.5.2 local检查选项

若本级添加了with local check option，检查操作是否满足当前这一条的约束，向上递归检查所有父亲视图（包括父视图、父视图的父视图…），若父亲视图有with cascaded/local check option则根据父亲视图条件进行校验，若没有则不校验（基于MySQL 8.0）

1.5.2.1 local示例

视图1：create view stu_v_1 as select id, name from student where id <= 15

视图2：create view stu_v_2 as select id, name from stu_v_1 where id >= 10 with local check option

视图3：create view stu_v_3 as select id, name from stu_v_2 where id < 20

下列插入语句：

• insert into stu_v_1 values(5, 'Tom')

• insert into stu_v_1 values(16, 'Tom')

• insert into stu_v_2 values(13, 'Tom')

• insert into stu_v_2 values(17, 'Tom')

• insert into stu_v_3 values(14, 'Tom')

的执行，是成功还是失败？

语句1、2：执行成功，因为没有加入检查选项

语句3、4：执行成功，满足stu_v_2的条件，去找stu_v_1发现它没有加检查选项，所以不需要根据stu_v_1表的条件判断

语句5：执行成功，stu_v_3视图没有加入约束，所以不检查当前视图的条件；向上去找父亲视图stu_v_2的条件，发现有local的约束，则检查，满足约束；再向上找父亲视图stu_v_1，发现没有加检查约束，则不检查。说明满足约束，执行成功。

1.6 视图更新及作用

并不是所有的视图都可以更新，那么要考虑的是符合什么条件的的视图可以更新？

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

1. 聚合函数或窗口函数（SUM,MIN,MAX,COUNT等）；

2. DISTINCT

3. GROUP BY

4. HAVING

5. UNION或者UNION ALL

如create view stu_v_count as select count(*) from student创建的视图不可以更新

视图的作用：

• 简单：简化用户对数据的理解，简化他们的操作。经常被使用的查询可以被定义为视图，从而使得用户不必为以后得操作每次指定全部的条件。

• 安全：数据库可以授权，但不能授权到数据库特定行和特定列上。通过视图用户只能查询和修改他们所能见到的数据（如学生表有学生号、学生名、班级名，可以通过视图隐藏不想让用户看到的班级名，避免用户对班级名的操作）

• 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响（如学生表中的学生名属性要rename成名属性，可以通过select 名 as 学生名，使得用户看到属性的名称仍为学生名，表结构的变化不会对用户造成影响）

2. 存储过程

2.1 存储过程定义

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

2.2 存储过程特点

• 封装，复用

• 可以接收参数，也可以返回数据

• 较少网络交互，效率提升

2.3 存储过程基本语法

2.3.1 创建

Create Procedure 存储过程名称([参数列表])
Begin
    -- SQL语句
End

由于SQL语句可能有很多条，每一条结束都有";"，在执行过程中可能会报错，因为在遇到;时会认为语句已经结束了，解决办法为：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句结束符：

通过delimiter $$将结束符设置为$$，则在写存储过程中，我们只需要在最后加上结束符即可，例如：

create procedure p1()
begin
    select count(*) from student;
end$$

不需要之后，就通过delimiter ;将结束字符改为分号即可

2.3.2 调用

Call 名称([参数])

2.3.3 查看

查询指定数据库的存储过程及状态信息

Select * from information_schema,ROUTINES where ROUTINE_SCHEMA='数据库名称'

查询某个存储过程的定义：

Show Create Procedure 存储过程名称

2.3.4 删除

Drop Procedure [If Exsits] 存储过程名称 

2.4 存储过程中的变量

2.4.1 系统变量

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

全局变量全局有效，会话变量只在当前会话内有效

查看系统变量：

• Show [Session|Global] Variables;查看所有系统变量

• Show [Session|Global] Variables Like '...';可以通过模糊匹配方式查找变量

• Select @@[Session|Global.] 系统变量名; 查看具体变量的值

设置系统变量：

• set [Session|global] 系统变量名 = 值

• set @@[Session|Global.] 系统变量名 = 值

注意：

• 若没有指定Session/Global，则默认为Session级别

• MySQL服务重新启动后，设置的全局参数会失效，想要不失效，可以在/etc/my.cnf中配置

2.4.2 用户定义变量

用户定义变量是用户根据自身需求定义的变量，不需要提前声明，用时候使用@变量名就行，其作用域为当前Session

赋值：

• Set @var_name = expr [,@var_name = expr]...

• Set @var_name := expr [,@var_name := expr]...

• 以上的=和:=是一样的，推荐使用后者

• Select @var_name := expr [,@var_name := expr]...

• Select 字段名 Into @var_name From 表名，例如将查询出来的值赋值到自定义变量中，如Select count(*) into @mycount from 表名

使用：

• Select @var_name [,@var_name...]

若没有给变量赋值直接调用，则获取到的结果为NULL

2.4.3 局部变量

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

声明：

• Declare 变量名 变量类型 [Default ...]

例如：declare stu_count int default 0

赋值：

• Set 变量名 = 值

• Set 变量名 := 值

• Select 字段名 Into 变量名 From 表名 ...

2.5 语法结构

2.5.1 if判断

语法如下：

If 条件1 THEN
...
ElseIf 条件2 THEN -- 可选
...
Else -- 可选
...
End If;

以分数为条件，判断优秀、及格、不及格，存储过程写法如下：

create procedure p3()
begin

    declare score int default 58; -- 声明score变量
    declare result varchar(10); -- 声明结果变量

    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;

    select result; -- 查询结果

end

2.5.2 参数（IN/OUT/INOUT）

• IN：该类参数作为输入，需要调用时传入值

• OUT：该类参数作为输出，也就是该参数可以作为返回值

• INOUT：既可以作为输入参数，也可以作为输出参数

用法：

Create Procedure 存储过程([IN/OUT/INOUT 参数名 参数类型])
Begin
    -- SQL语句
End

例子：传入分数（int类型），根据参数将优秀、及格、不及格的result（varchar类型）返回。

create procedure p4(IN score int, OUT result varchar(10))
begin

    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;

end

调用存储过程：call p4(68, @result)

查询用户自定义变量，看结果 select @result为及格

例子：传入的是200分制的分数，进行换算成百分制后返回。

create procedure p5(INOUT score double)
begin
    set score :=  score * 0.5;
end

调用存储过程：

set @hscore := 150;
call p5(@hscore)

查询用户自定义变量，看结果select @result结果为75

2.5.3 case

语法一：

Case case_value
    When when_value1 Then statement_list1
    [When when_value2 Then statement_list2]...
    [Else statement list]
End Case;

语法二：

Case
    When search_condition1 Then statement_list1
    [When search_condition2 Then statement_list2]...
    [Else statement list]
End Case;

案例：根据传入月份，判定月份所属季节（第一、二、三、四季度）

create procedure p6(In month int, Out result varchar(10))
begin
    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 := '第四季度';
    end case;
end

调用存储过程：

set @month := 5
call p6(@month, @result)
select concat('输入月份为：',@month,'，所属季度为：',@result);

2.5.4 循环while

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL逻辑...
END WHILE

案例：计算从1累加到n的值，n为传入的参数值

create procedure p7(IN n int)
begin
    declare i int default 1;
    declare sum int default 0;

    while i < n Do
        set sum := sum + i;
        set i := i+1;
    end while;

    select concat('1-' , n , '的值为：' , sum);
end

2.5.4 循环repeat

repeat是有条件的循环控制语句，当满足条件时退出循环（while是满足条件继续循环），语法如下：

# 先执行一次逻辑，然后判断逻辑是否满足，如果满足则退出。不满足则继续下一次循环
Repeat
    SQL逻辑...
    Until 条件
End Repeat;

案例：计算从1累加到n的值，n为传入的参数值

create procedure p8(In n int)
begin
    declare sum int default 0;
    
    Repeat
        set sum := sum + i;
        set n := n - 1;
    until n <= 0;
    End Repeat;
    
    select sum;
end

调用存储过程：call p8(10)

2.5.5 循环loop

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

• LEAVE：配合循环使用，退出循环（类似Break）

• ITERATE：必须用在循环中，作用是跳过当前循环剩下语句，直接进入下一次循环（类似Continue）

语法：

[begin_label:]loop
    SQL逻辑...
end loop [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

案例1：计算从1累加到n的值，n为传入的参数值

Create procedure p9(IN n int)
begin
    declare sum int default 0;

    sum:loop
        if n <= 0 then
            leave sum;
        end if;

        set sum := sum + n;
        set n := n - 1;
    end loop sum;

    select sum;
end

案例2：计算从1到n之间的偶数累加的值，n为传入的参数值

Create procedure p10(IN n int)
begin
    declare sum 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 sum := sum + n;
        set n := n - 1;
    end loop sum;

    select sum;
end

调用存储过程：call p10(10)

2.5.6 游标与条件处理程序

游标（CURSOR） 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法如下：

• 声明游标：DECLARE 游标名称 CURSOR FOR 查询语句;

• 打开游标：OPEN 游标名称;

• 获取游标记录：FETCH 游标名称 INTO 变量[,变量];

• 关闭游标：CLOSE 游标名称;

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

Declare handler_action handler for condition_value[, condition_value]... statement;

handler_action
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
condition_value
    SQLSTATE sqlstate_value：状态码，如02000
    SQLWARNING：所有以01开头的SQLSTATE代码的简写
    NOT FOUND：所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION：所有没有被SQLWARINING或NOT FOUND捕获的SQLSTATE代码的简写

案例：根据传入参数uage，查询用户表tb_user中，所有用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到创建的新表(id,name,profession)中。

-- A: 声明游标，存储查询结果集
-- B：准备：创建表结构
-- C：开启游标
-- D：获取游标中的记录
-- E：插入数据到新表中
-- F：关闭游标
create procedure p12(in uage int)
begin
    -- 要先声明变量，再声明游标
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;
    declare EXIT handler for SQLSTATE '02000' close u_cursor -- 满足'02000'代码时候退出，并关闭游标
    
    drop table if exists tb_user_pro
    create 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

handler在declare之后好像会自动执行，不需要再在代码中再写什么。或者SQLSTATE '02000'可以替换为not found

2.5.7 存储函数

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

Create Funciton 存储函数名称([参数列表])
Returns type [characteristic ...]
Begin
    -- SQL语句
    Return ...;
End;

characteristic说明：

• Deterministic：相同输入参数总是产生相同结果

• NO SQL：不包含SQL语句

• Reads SQL Data：包含读取数据的语句，但不包含写入数据的语句

案例：完成从1到n的累加存储函数

create function fun1(n int)
returns int deterministic
begin
    declare sum int default 0;

    while n>0 do
        set sum := sum + n;
        set n := n - 1;
    end while;

    return sum;
end;

调用函数：fun1(100)

3. 触发器

触发器指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端保持数据完整性、日志记录、数据校验等操作。

使用别名OLD和NEW来引发触发器中发生变化的记录内容，这与其他数据库相似。现在触发器还只支持行级触发，不支持语句级触发（行级触发指的是假如执行了UPDATE语句，影响了5行，触发器会被触发5次；语句级不管影响了几行只执行1次）。

• Insert型触发器中，NEW表示将要或已经新增的数据

• Update型触发器中，OLD表示修改之前的数据，NEW表示将要或已经修改后的数据

• Delete型触发器中，OLD表示将要或已经删除的数据

3.1 触发器语法

创建：

Create Trigger trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
on tbl_name FOR EACH ROW - 行级触发器
Begin
    trigger_stmt;
End

查看：

Show triggers

删除：

Drop tigger [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库

案例：通过触发器记录tb_user表的数据变更日志，将变更日志插入到入职表user_logs中，包含增加，修改，删除，其中user_logs的建表语句如下：

create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型，insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作ID',
    operate_params varchar(500) comment '操作参数',
    primary key('id')
)engine=innodb default charset=utf-8;

3.2 Insert类型的触发器

插入数据触发器，根据3.1的案例：

-- 插入数据触发器
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....))
end;

3.3 Update类型的触发器

-- 修改数据触发器
create trigger tb_user_insert 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=',old.id,'name=',old.name....
                                            '更新之后的数据内容为：id=',new.id,'name=',new.name...))
end;

3.4 Delete类型的触发器

-- 删除数据触发器
create trigger tb_user_insert 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(), new.id, concat('删除之前的数据内容为：id=',old.id,'name=',old.name....))
end;