MySQL数据库进阶知识（四）《视图、存储过程、触发器》

学习目标：

掌握数据库视图基础知识
掌握数据库存储过程原理
掌握数据库触发器相关知识

学习内容：

一. 视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

创建

create [or replace] view 视图名称[(列表名称)] as select语句 [with [cascaded | local] check option]

例：

-- 创建视图
create or replace VIEW stu_v_1 as select id,name from student where id<=10;

在这里插入图片描述

查询

查看创建视图语句：

show create view 视图名称;

例：

show create view stu_v_1;

在这里插入图片描述
查看视图数据：

select * from 视图名称……;

例：

select * from stu_v_1;

在这里插入图片描述

修改视图

-- 方式1
create or replace view 视图名称[(列表名称)] as select语句 [with [cascaded | local] check option];
-- 方式2
alter view 视图名称 as select语句;

例：未修改时字段列表如下：
在这里插入图片描述

-- 1、
create or replace VIEW stu_v_1 as select id,name,no from student where id<=10;

-- 2、
alter view stu_v_1 as select id,name,no from student where id<=10;

修改后（1、2都可以）：
在这里插入图片描述

删除视图

drop view [if exists] 视图名称;

例：

drop view if exists stu_v_1;

在这里插入图片描述

视图的检查选项

create or replace VIEW stu_v_1 as select id,name from student where id<=10;
select * from stu_v_1;

在这里插入图片描述
使用视图插入数据：

insert into stu_v_1 values (6,'Tom');

insert into stu_v_1 values (30,'Tom');

可以在视图使用到的原表中查看到使用视图增加的数据：
在这里插入图片描述
但是对于使用视图新增的id=30的数据，再次查询无法在视图中查到：

为了避免这种情况出现（使用视图插入的数据，有的数据使用视图查询不出），可以在创建语句中新增检查选项：
删掉原来id为6、30的数据，然后使用以下语句重建视图，依次插入id=6，id=30的数据并分别进行查询，测试过程可正常插入并查询到id=6的数据；但在查询id=30的数据时，系统出现报错，因为插入的数据不满足创建视图时的查询条件。

create or replace VIEW stu_v_1 as select id,name from student where id<=10 with cascaded check option ;

select * from stu_v_1;

insert into stu_v_1 values (6,'Tom');

insert into stu_v_1 values (30,'Tom');

在这里插入图片描述
当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：
CASCADED和LOCAL，默认值为CASCADED。
CASCADED：
在这里插入图片描述

create or replace VIEW stu_v_1 as select id,name from student where id<=20;

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

insert into stu_v_1 values (25,'Tom');

以上3条语句都可正常执行：
在这里插入图片描述
针对视图stu_v_1创建视图（设置检查选项）：

create or replace VIEW stu_v_2 as select id,name from stu_v_1 where id>=10 with cascaded check option ;

以下3种插入数据的执行情况：

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

报错：由于不满足stu_v_2条件
在这里插入图片描述

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

报错：由于不满足stu_v_1条件
在这里插入图片描述

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

正常：满足stu_v_1、stu_v_2条件
在这里插入图片描述
针对视图stu_v_1创建视图（未设置检查选项）：

create or replace VIEW stu_v_3 as select id,name from stu_v_2 where id<=15;

以下3种插入数据的执行情况：

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

正常：满足stu_v_1、stu_v_2、stu_v_3条件，不影响插入
在这里插入图片描述

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

正常：虽然不满足stu_v_3条件，但是由于stu_v_3未设置检查选项，且满足stu_v_1、stu_v_2条件，所以不影响插入在这里插入图片描述

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

报错：不满足stu_v_1条件，因此无法插入** 在这里插入图片描述
LOCAL

-- local
create or replace VIEW stu_v_4 as select id,name from student where id<=15;

insert into stu_v_4 values (5,'Tom'); -- 正常执行，满足v4条件

insert into stu_v_4 values (16,'Tom'); -- 正常执行，v4未设置检查选项

create or replace VIEW stu_v_5 as select id,name from stu_v_4 where id>=10 with local check option ;

insert into stu_v_5 values (13,'Tom'); -- 正常执行，满足v4、v5条件

insert into stu_v_5 values (17,'Tom'); -- 正常执行，满足v5条件，不满足v4条件，但v4未设置检查选项

create or replace VIEW stu_v_6 as select id,name from stu_v_5 where id<=20;

insert into stu_v_6 values (14,'Tom'); -- 正常执行，满足v6条件，满足v5条件，满足v4条件

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
1.聚合函数或窗口函数（sum()、min()、max()、count()等）
2.distinct
3.group by
4.having
5.union或者union all

作用

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

案例：
1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。

create or replace 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
where s.id=sc.studentid and sc.courseid=c.id;


create or replace view student_course_view as
select s.name student_name,s.no student_no,c.name course_name from
student s,student_course sc,course c
where s.id=sc.studentid and sc.courseid=c.id;

select * from student_course_view;

在这里插入图片描述

二. 存储过程

介绍

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

特点

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

创建

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。

delimiter $$
-- 表示在输入$$后为一个SQL语句结束

create procedure 存储过程名称([参数列表])
begin
    -- SQL语句
end;

例：

-- 创建
create procedure p1()
begin
    select count(*) from student;
end;

运行后：
在这里插入图片描述

调用

call 名称([参数]);
例：
call p1();
运行结果：
在这里插入图片描述

查看

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

show create procedure 存储过程名称;-- 查询某个存储过程的定义

例：

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='itcast';

show create procedure p1;

在这里插入图片描述

删除

drop procedure [if exists] 存储过程名称;

例：

drop procedure p1;

删除后左侧p1消失：
在这里插入图片描述

变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）。
1.查看系统变量

-- 查看所有系统变量
show [session|global] variables;

-- 可以通过like模糊匹配方式查找变量
show [session|global] variables like '……';

-- 查看指定变量的值
select @@[session|global] 系统变量名;

show variables ;

在这里插入图片描述

show session variables like 'auto%';

在这里插入图片描述

show global variables like 'auto%';

在这里插入图片描述

select @@autocommit;

在这里插入图片描述
2.设置系统变量

set [session|global] 系统变量名=值;

set @@[session|global.]系统变量名=值;

例：

set session autocommit=0;

该方式（session）不会改变另一程序中的会话autocommit值
在这里插入图片描述

set global autocommit = 0;

通过该方式（global）改变后，其他程序查询全局变量autocommit值也会改变。
在这里插入图片描述
注意：
如果没有指定session/global，默认是session，会话变量。
mysql服务器重启后，所设置的全局参数会失效，想要不失效，可以在/etc/my.cnf中配置。
用户定义变量是用户根据自己需要定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接（会话变量）。
1.赋值

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 表名;

例：

set @myname='itcast';
set @myage:=18;
set @mygender='女',@myhobby:='java';

select @mycolor:='red';
select count(*) into @mycount from tb_user;

2.使用

select @var_name;

select @myname,@myage,@mygender,@myhobby;

在这里插入图片描述

select @mycount;

在这里插入图片描述
注意：
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
局部变量是根据需要定义在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin……end块。
1.声明

declare 变量名 变量类型[default……];

变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等。
2.赋值

set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名……;

例：

create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;

call p2();

在这里插入图片描述

if

if 条件1 then
    ……
elseif 条件2 then      -- 可选
    ……
else                   -- 可选
    ……
end if;

例：

-- 根据定义的分数score变量，判定当前分数对应的分数等级。
-- score>=85，等级为优秀
-- score>=60且score<85，等级为及格
-- score<60，等级为不及格

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result = '优秀';
    elseif score>=60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
    select result;
end ;

call p3();

在这里插入图片描述

参数

在这里插入图片描述
用法：

create procedure 存储过程名称 ([in/out/inout 参数名 参数类型])
begin
    SQL语句
end;

-- 1、根据传入的分数score变量，判定当前分数对应的分数等级，并返回。
-- score>=85，等级为优秀
-- score>=60且score<85，等级为及格
-- score<60，等级为不及格
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;

在这里插入图片描述

-- 2、将传入的200分制分数转为100分制，并返回分数
create procedure p5(inout score double)
begin
    set score=score/2;
end;


set @score=160; -- 需要先定义参数
call p5(@score);
select @score;

在这里插入图片描述

case

1.语法一

case case_value
    when when_value1 then statement_list1
    [when when_value2 then statement_list2]……
    [else statement_list]
end case;

2.语法二

case
    when search_condition1 then statement_list1
    [when search_condition2 then statement_list2]……
    [else statement_list]
end case;

例：

-- 根据传入的月份，判定月份所属的季节（采用case结构）
-- 1-3第一季度
-- 4-6第二季度
-- 7-9第三季度
-- 10-12第四季度
create procedure p6(in month int)
begin
    declare result varchar(10);
    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;

call p6(2);

在这里插入图片描述

while

while循环是有条件的循环控制语句。满足条件后，在执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
    SQL逻辑……
end while;

例：

-- 计算从1累加到n的值，n为传入的参数值。
-- A.定义局部变量，记录累加之后的值；
-- B.每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p7(in n int)
begin
    declare sum int default 0;
    while n>0 do
        set sum=sum+n;
        set n=n-1;
    end while;
    select sum;
end;

call p7(3);

在这里插入图片描述

repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
    SQL逻辑
    until 条件
end repeat;

例：

-- 计算从1累加到n的值，n为传入的参数值。
-- A.定义局部变量，记录累加之后的值；
-- B.每循环一次，就会对n进行减1，如果n<=0，则退出循环
create procedure p8(in n int)
begin
    declare sum int default 0;
    repeat
        set sum=sum+n;
        set n=n-1;
    until n<=0 end repeat;
    select sum;
end;

call p8(3);

在这里插入图片描述

loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以使用其来实现死循环。loop可以配合以下两个语句使用：
leave：配合循环使用，退出循环。
iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

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

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

例：

-- loop
-- 计算从1累加到n的值，n为传入的参数值。
-- A.定义局部变量，记录累加之后的值；
-- B.每循环一次，就会对n进行减1，如果n<=0，则退出循环 ----> leave XX
create procedure p9(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;
    select total;
end;

call p9(4);

-- 计算从1累加到n的偶数值，n为传入的参数值。
-- A.定义局部变量，记录累加之后的值；
-- B.每循环一次，就会对n进行减1，如果n<=0，则退出循环 ----> leave XX
-- C.如果遇到奇数值，则跳出本次循环开始下一次循环
create procedure p10(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;
    select total;
end;

call p10(10);

游标

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

declare 游标名称 cursor for 查询语句;

2.打开游标

open 游标名称

3.获取游标记录

fetch 游标名称 into 变量[,变量];

4.关闭游标

close 游标名称;

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

create procedure p11(in uage int)
begin
    -- 声明
    declare uname,uprofession varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age<=uage;

    -- 建立表结构
    drop table if exists tb_user_pro;
    create table tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        prfession varchar(100)
    );
    -- 打开
    open u_cursor;
    -- 获取游标记录
    while true do
        fetch u_cursor into uname,uprofession;
        insert into tb_user_pro values (null,uname,uprofession);
        end while;
    -- 关闭游标
    close u_cursor;
end;

call p11(40);

该方式会报错，但查询生成的表，内容确实没问题：
在这里插入图片描述

条件处理程序

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

declare handler_action handler for condition_value [,condition_value]…… statement;

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

例：上个例子中出现报错，可在声明部分加入以下语句并重新创建p11：

declare exit handler for sqlstate '02000' close u_cursor;

再次运行未出现报错，并且数据成功写入：
在这里插入图片描述

三. 存储函数

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

create function 存储函数名称([参数列表])
returns type [characteristic ……]
begin
SQL语句
return……;
end;

characteristic说明：
deterministic：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
reads SQL data：包含读取数据的语句，但不包含写入数据的语句。

例：计算从1–n的累加值

-- 计算从1到n的累加值，并返回
create function fun1(n int)
returns 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 fun1(5);

在这里插入图片描述

四、触发器

介绍

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

语法

1.创建

create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row --行级触发器
begin
trigger_stmt;
end;

2.查看

show triggers;

3.删除

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

例：通过触发器记录user表的数据变更日志（user_logs），包含增加，修改，删除
（1）增加数据触发器

-- 需求：通过触发器记录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=utf8;

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('输入的数据为：（',NEW.id,NEW.age,NEW.profession,new.name,NEW.status,NEW.gender,NEW.createtime,NEW.email,NEW.phone,')'));
end;

-- 查看
show triggers ;

在这里插入图片描述
删掉触发器：

-- 删除
drop trigger tb_user_insert_trigger;

在插入一条新的数据后，触发器表格可观察到该数据详情：

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)

VALUES (25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

（2）修改数据触发器

-- 更改数据触发器
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('修改前的数据为：（',OLD.id,OLD.age,OLD.profession,OLD.name,OLD.status,OLD.gender,OLD.createtime,OLD.email,OLD.phone,')',
        '修改后的数据为：（',NEW.id,NEW.age,NEW.profession,new.name,NEW.status,NEW.gender,NEW.createtime,NEW.email,NEW.phone,')'));
end;

创建后查询触发器如下：
在这里插入图片描述

-- 修改数据
update tb_user set age = 32 where id = 23;

修改数据后：
在这里插入图片描述
查看触发器表记录：

update tb_user set age = 20 where id<5;

在这里插入图片描述
（3）删除数据触发器

-- 删除数据触发器
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,'update',now(),OLD.id,concat('删除的数据为：（',OLD.id,OLD.age,OLD.profession,OLD.name,OLD.status,OLD.gender,OLD.createtime,OLD.email,OLD.phone,')'));
end;