MySQL-视图/存储过程/存储函数/触发器

2022-03-15 本文已影响0人石头耳东

前置文章：
一、MySQL-Explain了解查询语句执行计划
 二、MySQL-索引
 三、MySQL-索引（简版）
四、MySQL-SQL优化

零、本文纲要

一、视图
二、存储过程
三、存储函数
四、触发器

tips：Ctrl + F快速定位到所需内容阅读吧。

一、视图

0、表&数据准备

-- 新建表格
create table tb_test
(
    id   int         not null
        primary key,
    name varchar(20) not null
)
    comment '测试插入表';
-- 插入数据
insert into tb_test (id, name)
values (1,'吕布');
insert into tb_test (id, name)
values (2,'曹操');
insert into tb_test (id, name)
values (3,'赵云');
insert into tb_test (id, name)
values (4,'孙悟空');
insert into tb_test (id, name)
values (5,'花木兰');

1、视图语法

① 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ];
② 查询
查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;
③ 修改
方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ];
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ];
④ 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...;

2、视图操作

-- 创建视图
create view test_v_1 as select id,name from tb_test where id <= 10;
create or replace view test_v_1 as select id,name from tb_test where id <= 10;

--查询视图
show create view test_v_1;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                                                                                                                                | character_set_client | collation_connection |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test_v_1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `test_v_1` AS select `tb_test`.`id` AS `id`,`tb_test`.`name` AS `name` from `tb_test` where (`tb_test`.`id` <= 10) | utf8mb4              | utf8mb4_0900_ai_ci   |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

select * from test_v_1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 吕布      |
|  2 | 曹操      |
|  3 | 赵云      |
|  4 | 孙悟空    |
|  5 | 花木兰    |
+----+-----------+
select * from test_v_1 where id <= 3;

-- 修改tb_test表
alter table tb_test modify no char(4) null comment '学号';

-- 修改视图
create or replace view test_v_1 as select id,name,no from tb_test where id <= 10;

alter view test_v_1 as select id,name from student where id <= 10;

-- 删除视图
drop view if exists test_v_1;

3、检查选项

-- 删除视图
drop view if exists test_v_1;

-- 创建视图
create or replace view test_v_1 as select id,name from tb_test where id <= 10;

-- 插入数据
insert into test_v_1 values(6,'黛绮丝');
insert into test_v_1 values(11,'谢逊');

--查询表格&视图
select * from tb_test;
select * from test_v_1;

视图操作结果对比.png

通过观察，不难发现通过test_v_1视图插入的数据成功添加到tb_test表格内。但是查询视图的时候，不满足where id <= 10条件的数据并没有展示出来。
视图可以帮助我们限制数据查阅者能查看哪些数据，那么检查选项的设置能帮助我们限制使用者能进行哪些操作。

1、CASCADED

with cascaded check option;有着级联的效果；

① 创建视图，设置CASCADED限制

-- 删除视图
drop view if exists test_v_1;

-- 创建视图
create view test_v_1 as select id,name from tb_test where id <= 20;
create view test_v_2 as select id,name from test_v_1 where id >= 10 with cascaded check option;
create view test_v_3 as select id,name from test_v_2 where id <= 15;

② 操作带CASCADED限制的视图

CASCADED级联效果.png

-- 插入数据
mysql> insert into test_v_2 values(12,'周杰');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_v_2 values(21,'刘备');
ERROR 1369 (HY000): CHECK OPTION failed 'test.test_v_2'

通过上述操作，不难看出我们对test_v_2视图所进行的插入操作，必须同时满足test_v_1视图和test_v_2视图的条件才能成功插入。

③ 操作不带CASCADED限制的视图

不带CASCADED限制.png

-- 插入数据
mysql> insert into test_v_3 values(16,'关羽');
Query OK, 1 row affected (0.00 sec)

而当我们继续操作test_v_3视图的数据插入时，由于没有CASCADED限制，此时数据又可以成功插入。

2、LOCAL

with local check option;更关注本地的限制；

① 创建视图，设置LOCAL限制

-- 创建视图
create view test_v_4 as select id,name from tb_test where id <= 15;
create view test_v_5 as select id,name from test_v_4 where id >= 10 with local check option;
create view test_v_6 as select id,name from test_v_5 where id <= 20;

② 操作带LOCAL限制的视图

LOCAL关注本地.png

mysql> insert into test_v_5 values(7,'西施');
ERROR 1369 (HY000): CHECK OPTION failed 'test.test_v_5'
mysql> insert into test_v_5 values(13,'张飞');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_v_5 values(17,'貂蝉');
Query OK, 1 row affected (0.01 sec)

不难看出，虽然test_v_5视图基于test_v_4视图创建，但是LOCAL限制更关注本地，所以此处只有where id >= 10的条件在test_v_5视图中生效。

③ 操作不带LOCAL限制的视图

不带LOCAL限制.png

mysql> insert into test_v_6 values(18,'孙膑');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_v_6 values(22,'张良');
Query OK, 1 row affected (0.00 sec)

同样，由于没有LOCAL限制，test_v_6视图的插入操作并没有限制，只有查看视图本身才会有where id <= 20;条件的限制。

4、视图可更新

视图不可更新的情形：
A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL

5、视图的作用

① 简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
② 安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
③ 数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。

二、存储过程

存储过程是数据库 SQL 语言层面的代码封装与重用。

特点
① 封装、复用；
② 接收参数、返回结果；
③ 减少网络传输，提升效率。

1、基本语法

基础准备：

-- 创建表格
create table student
(
    id   int auto_increment comment '主键ID'
        primary key,
    name varchar(10) null comment '姓名',
    no   varchar(10) null comment '学号'
)
    comment '学生表';

-- 插入数据
insert into student (id, name, "no") values (1,'黛绮丝',20220301);

① 创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;

② 调用

CALL 名称 ([ 参数 ]);

③ 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

④ 删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

示例：

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

-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';

show create procedure p1;

-- 删除
drop procedure if exists p1;

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

delimiter使用.png

2、变量

① 系统变量

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

查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

注意：如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
Ⅰ 全局变量(GLOBAL)：全局变量针对于所有的会话。
Ⅱ 会话变量(SESSION)：会话变量针对于单个会话，在另外一个会话窗口就不生效了。
示例：

-- 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';

select @@global.autocommit;
select @@session.autocommit;

-- 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;

② 用户定义变量

赋值

方式一

-- 推荐使用 :=
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 @var_name ;

注意: 如果用户定义的变量未声明或初始化，默认值则为NULL。
示例：

-- 赋值
-- 方式一
set @test_var1 = 'test';
set @test_var3 := 5,@test_var4 := 'hello';
-- 方式二
select @test_var5 := 'world';
select count(*) into @test_var6 from student;

-- 使用
select @test_var1,@test_var2,@test_var3,@test_var4,@test_var5;
select @test_var6;

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

声明

DECLARE 变量名 变量类型 [DEFAULT ... ] ;
-- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

赋值

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

示例：

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
-- 调用
call p2();

3、if

语法

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

示例：

-- 创建储存过程
create procedure p3()
begin
    -- 设置局部变量
    declare score int default 60;
    declare result char(3);
    -- if语句
    if score >= 90 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    -- 输出结果
    select result;
end;

-- 调用
call p3();

4、参数

参数类型

①IN：该类参数作为输入，也就是需要调用时传入值，默认；
②OUT：该类参数作为输出，也就是该参数可以作为返回值；
③INOUT：既可以作为输入参数，也可以作为输出参数。

语法

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

示例：

-- 创建储存过程
create procedure p4(in score int, out result char(3))
begin
    -- if语句
    if score >= 90 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

-- 自定义@result变量接收返回数据
call p4(58,@result);

-- 输出自定义变量
select @result;

5、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 p5(in score int)
begin
    declare result char(4);
    case
        when score >= 90 and score <= 100 then
            set result := '优秀';
        when score >= 60 and score < 90 then
            set result := '及格';
        when score >= 0 and score < 60 then
            set result := '不及格';
        else
            set result := '数据有误';
    end case;
    select concat('成绩',score,'的结果为：',result);
end;

-- 调用
call p5(59);

6、while

语法

-- 类似于Java语言的while语句
WHILE 条件 DO
SQL逻辑...
END WHILE;

示例：

-- 创建储存视图
create procedure p6(in n int)
begin
    declare res int default 0;
    while n > 0 do
        set res := res + n;
        set n := n - 1;
    end while;
    select res;
end;

-- 调用
call p6(10);

7、repeat

语法

-- 类似于Java语言的do..while语句
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

示例：

-- 创建储存视图
create procedure p7(in n int)
begin
    declare res int default 0;
    repeat
        set res := res + n;
        set n := n - 1;
    until  n <= 0
    end repeat;
    select res;
end;

-- 调用
call p7(10);

8、loop

语法

[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];

LEAVE ：配合循环使用，退出循环（类似于Java语言中的break）；
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环（类似于Java语言中的continue）。

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

示例：

-- 创建储存视图
-- leave类似于Java语言中的break
-- iterate类似于Java语言中的continue
create procedure p8(in n int)
begin
    declare res int default 0;
    sum:loop
        if n <= 0 then
            leave sum;
        end if;
        if n%2 = 0 then
            set n := n - 1;
            iterate sum;
        end if;
        set res := res + n;
        set n := n - 1;
    end loop sum;
    select res;
end;

-- 调用
call p8(10);

9、游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。

语法

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

-- 打开游标
OPEN 游标名称 ;

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

-- 关闭游标
CLOSE 游标名称 ;

示例：

create procedure p9(in n int)
begin
    declare stu_name char(3);
    declare stu_no int;
    -- 声明游标
    declare stu_cursor cursor for select name,no from student where id <= n;

    drop table if exists tb_stu;
    create table if not exists tb_stu(
        s_id int primary key auto_increment,
        s_name char(3),
        s_no int
    );

    -- 开启游标
    open stu_cursor;
    while true do
        -- 获取游标记录
        fetch stu_cursor into stu_name,stu_no;
        insert into tb_stu values(null,stu_name,stu_no);
    end while;
    -- 关闭游标
    close stu_cursor;
end;

call p9(3);

游标演示1.png

由于我们的循环语句没有终止条件，此处会报错。但是，可以看到实际的数据已经读取出来了。
10、条件处理程序

条件处理程序（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: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

示例：

create procedure p10(in n int)
begin
    declare stu_name char(3);
    declare stu_no int;
    -- 声明游标
    declare stu_cursor cursor for select name,no from student where id <= n;
    -- 方式一
    -- declare exit handler for SQLSTATE '02000' close stu_cursor;
    -- 方式二（推荐此方式）
    declare exit handler for not found close stu_cursor;

    drop table if exists tb_stu;
    create table if not exists tb_stu(
        s_id int primary key auto_increment,
        s_name char(3),
        s_no int
    );

    -- 开启游标
    open stu_cursor;
    while true do
        -- 获取游标记录
        fetch stu_cursor into stu_name,stu_no;
        insert into tb_stu values(null,stu_name,stu_no);
    end while;
    -- 关闭游标
    close stu_cursor;
end;

call p10(3);

三、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

语法

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;

characteristic说明
① DETERMINISTIC：相同的输入参数总是产生相同的结果；
② NO SQL ：不包含 SQL 语句；
③ READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

示例：

create function adds(n int)
returns int deterministic
begin
    declare res int default 0;
    while n >= 0 do
        set res := res + n;
        set n := n - 1;
    end while;
    return res;
end;

select adds(10);

四、触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作。

1、触发器类型

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

INSERT 型触发器

NEW 表示将要或者已经新增的数据；

UPDATE 型触发器

OLD 表示修改之前的数据；
NEW 表示将要或已经修改后的数据；

DELETE 型触发器

OLD 表示将要或者已经删除的数据。

2、语法

创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;

查看

SHOW TRIGGERS ;

删除

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

示例：
① 准备表格

-- 准备工作 : 日志表 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('插入的数据内容为：id=',NEW.id,',name=',NEW.name,',phone=',NEW.phone,
               ',email=',NEW.email,',profession=',NEW.profession)
          );
end;

测试插入数据：

-- 查看触发器
show triggers;

-- 插入数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (26, '三皇子', '18809091212', 'erhuangzi@163.com', '软件工程', 23, '1', '1', now());

插入数据.png

③ 修改数据触发器

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, 'insert', now(), NEW.id,
            concat('更新之前的数据：id=', OLD.id, ',name=', OLD.name, ',phone=', OLD.phone,
                   ',email=', OLD.email, ',profession=', OLD.profession,
                   '| 更新之后的数据：id=', NEW.id, ',name=', NEW.name, ',phone=', NEW.phone,
                   ',email=', NEW.email, ',profession=', NEW.profession));
end;

测试更新数据：

-- 查看
show triggers ;
-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

④ 删除数据触发器

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, 'insert', now(), OLD.id,
            concat('删除之前的数据内容为：id=', OLD.id, ',name=', OLD.name, ',phone=', OLD.phone,
                   ',email=', OLD.email, ',profession=', OLD.profession));
end;

测试删除数据：

-- 查看
show triggers ;
-- 删除数据
delete from tb_user where id = 26;

五、结尾

以上即为视图/存储过程/存储函数/触发器的基础内容，感谢阅读。