02 MySQL中的视图，存储过程/函数，触发器的基本知识点

1 视图（简化查询操作）

1-1 概述

视图的特点:

• 视图是SELECT语句执行后返回的结果集，是对SELECT语句的封装。
• 视图是虚拟存在的表(通过查询得到的表)，表中的行和列来自于真实存在的表。

视图相比普通表的优势

• 简单：使用视图的人只需要关注视图，无需关注视图背后真实存在的表的结构，关联条件，筛选条件。
• 安全：使用视图可以对访问用户(包括开发者)的查看权限进行限制，视图对表的权限限制能够具体到某行某列。
• 数据独立：视图与数据来源的表之间具有独立性，体现在：
  • 数据来源的表可以*增添删减记录
  • 数据来源的表的结构变化，只需更新视图定义所封装的SQL语句进行。

视图使用注意点

视图通常用于简化查询操作，可以通过更新视图来更新视图数据来源的表，但一般不推荐这样做。

1-2 视图的创建与修改

准备表

create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

• 注意city表中country_id是外键(外面表的主键)

进行多表查询：查询城市信息以及其关联的国家信息。

 SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;

视图的创建

目标：将上面的SELECT语句封装成视图。

CREATE view view_city_country as SELECT c.*,t.country_name from city c, country t WHERE c.country_id = t.country_id;

总结：上图中创建一个虚拟表view_city_country，封装之间定义的查询语句。

命令格式：CREATE VIEW <视图名> AS <SELECT语句>

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

• [WITH [CASCADED | LOCAL] CHECK OPTION]含义

更新视图时对更新条件的限制
LOCAL：只要满足本视图的条件就可以更新。
CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

视图的更改

命令格式：ALTER VIEW <视图名> AS <SELECT语句>

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

1-3 视图的查看与删除

查看

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存
在单独显示视图的 SHOW VIEWS 命令。

show tables;          
SHOW CREATE VIEW 视图名 G;      # 查看视图的具体定义

删除（DROP VIEW 视图名）

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

2 存储过程与存储函数

2-1 概述

存储过程与存储函数定义：

• 事先经过编译并存储在数据库中的一段 SQL 语句的集合

存储过程与存储函数优势：

• 简化应用开发人员的很多工作
• 减少数据在数据库和应用服务器之间的传输，提高数据处理的效率

存储过程与存储函数的区分：

• 函数可以理解为带有返回值的存储过程

2-2 创建，调用，查询存储过程

创建

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;

实例

delimiter $
create procedure pro_test1()
    begin
    select 'hello mysql' ;
    end$
delimiter ;

注意：由于MYSQL中 delimiter；+enter 就表示让数据库去执行命令，为了能够执行多条SQL语句，需要临时将 delimiter由；换为其他字符。

调用

call procedure_name() ;

查看创建的存储

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询当前数据库中某个存储过程的定义
show create procedure 数据库名称.存储过程名称 G;

注意：实际上创建的存储过程也是以表的形式存储在数据库中，在MYSQL中存储在mysql数据库中的proc表中。

注意：上面的命令可以查询到数据库创建的所有存储过程。

注意：只要知道存储过程的名称以及所属数据库，就可以通过show命令查看该存储过程所对应的SQL语句集合。

更加快捷的方法：通过GUI界面直接查看mysql.proc表，可以看到上面所有的信息。

删除

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

2-2 存储过程对应的SQL语句语法

2-2-1 变量的赋值（2种方式）

变量定义：可以采用DECLARE声明局部变量，该变量的作用范围只能在 BEGIN…END 块中 。

• 手动的定义常量（SET）

DELIMITER $
CREATE PROCEDURE pro_test3()
    BEGIN
    DECLARE NAME VARCHAR(20);        
    SET NAME = 'MYSQL';
    SELECT NAME ;
END$
DELIMITER 

• 从数据表中获取变量数值（select ... into ）

DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
    declare countnum int;
    select count(*) into countnum from city;
    select countnum;
END$
DELIMITER ;

COUNT(*) 函数返回表中的记录数

总结: 通过into能够将数据表中统计的结果写入变量

2-2-2 if语句的使用

需求

根据定义的身高变量，判定当前身高的所属的身材类型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 标准身材
170 以下 ----------> 一般身材

存储过程创建

delimiter $
create procedure pro_test6()
begin
    declare height int default 175;
    declare description varchar(50);
    if height >= 180 then
        set description = 'good body';
    elseif height >= 170 and height < 180 then
        set description = 'standard body';
    else
        set description = 'common body';
    end if;
    select description ;
end$
delimiter ;

总结：默认身高是175，根据if语句判断为standard body.

2-2-3 存储过程 参数的传递

模板

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

需求

编写一个存储过程，将身高作为参数传递，将判定的结果通过参数返回

代码

delimiter $
create procedure pro_test7(IN height INT,OUT description VARCHAR(100))
begin
 if height >= 180 then
        set description = 'good body';
    elseif height >= 170 and height < 180 then
        set description = 'standard body';
    else
        set description = 'common body';
  end if;
end$
delimiter ;

查看返回的变量

• @description : 变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用
  的，这个类似于全局变量一样。
• @@global.sort_buffer_size : 变量前加上 "@@" 符号, 叫做系统变量

call pro_test7(168, @description);
select @description;

2-2-4 CASE语句的使用

模板

方式一 :
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

方式二 :
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

实例：

delimiter $
create procedure pro_test9(month int)
begin
	declare result varchar(20);
    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;
    select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content;
end$
delimiter ;

执行结果：

2-2-5 WHILE语句的使用

模板

while search_condition do
    statement_list
end while;

实例(计算1加到n的值)

delimiter $
create procedure pro_test8(n int)
begin
    declare total int default 0;
    declare num int default 1;
    while num<=n do
        set total = total + num;
        set num = num + 1;
    end while;
    select total;
end$
delimiter ;

执行结果

2-2-6 REPEAT的使用

模板

REPEAT
    statement_list
    UNTIL search_condition
END REPEAT;

实例：计算1加到n

delimiter $
create procedure pro_test10(n int)
    begin
    declare total int default 0;
    repeat
    set total = total + n;
    set n = n - 1;
    until n=0
    end repeat;
    select total ;
    end$
delimiter ;

2-2-7 LOOP与LEAVE的使用

• LOOP可以实现简单的死循环，如果要退出循环需要配合leave使用

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

实例：

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
    declare total int default 0;
    ins: LOOP
        IF n <= 0 then
            leave ins;
        END IF;
        set total = total + n;
        set n = n - 1;
    END LOOP ins;
    select total;
END$
delimiter ;

注意：上面的ins是循环的开始和结果标记。

2-2-8 游标/光标的使用

定义：

• 用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理
  • 每次提取一条记录

光标的声明、OPEN、FETCH（拿） 和 CLOSE，其语法分别如下：

DECLARE cursor_name CURSOR FOR select_statement ;        
OPEN cursor_name ;
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name ;

光标的使用示例：

数据表创建

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment '姓名',
    age int(11) comment '年龄',
    salary int(11) comment '薪水',
    primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰
王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

需求1：查询emp表中数据, 并逐行获取进行展示

delimiter $
create procedure pro_test15()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
    ',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
    ',e_salary);
close emp_result;
end$
delimiter ;

执行结果：

总结：

declare emp_result cursor for select * from emp;

• 将SQL语句select查询的结果封装到定义的游标中

open emp_result;
close emp_result;

• 游标的使用需要open与closs

 fetch emp_result into e_id,e_name,e_age,e_salary;

• 使用fetch将游标中封装的结果的一条记录提取出来，然后放入到定义的变量中
• 上面的例子中如果fetch的次数 > 数据表中记录的个数，会发生error提示No data

需求2：使用循环结构通过游标查看表中所有记录。

• 下面的例子通过句柄判断记录是否读完
• 也可以通过count()确定表中的记录数

DELIMITER $
create procedure pro_test15()
begin
    DECLARE id int(11);
    DECLARE name varchar(50);
    DECLARE age int(11);
    DECLARE salary int(11);
    DECLARE has_data int default 1;
    
    DECLARE emp_result CURSOR FOR select * from emp;
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
    open emp_result;
        repeat
            fetch emp_result into id , name , age , salary;
            select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ',
            salary);
            until has_data = 0
        end repeat;
    close emp_result;
end$
DELIMITER ;

执行结果：

MYSQL通过提供句柄机制来判断数据表中是否有记录。

DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;  
DECLARE EXIT HANDLER FOR NOT FOUND定义了找不到数据的句柄，当出现找不到数据的情况时，就会触发后面的语句set has_data = 0 执行。

注意点：句柄的声明要紧跟在游标的声明后面，否则会出现报错。

DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

2-2-9 存储函数

存储函数与存储过程有些类似，它们在语法上的区别如下：

• PROCEDURE与FUNCTION，并且存储函数需要RETURN
• 存储函数可以看作“有返回值的存储过程”，实际上存储过程也可以在参数列表中通过OUT关键字返回数据。
• 实际上存储函数能够完成的事情使用存储过程也可以实现。

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;

示例:定义一个函数用于查询特定国家的记录个数

delimiter $
create function count_city(countryId int)
returns int
    begin
        declare cnum int ;
        select count(*) into cnum from city where country_id = countryId;
        return cnum;
    end$
delimiter ;

执行结果：

上图中通过select语句调用存储函数，统计指定id的记录个数。

3 触发器

3-1 概述

定义：在 insert/update/delete 之前或之后，触发并执行的SQL语句集合

应用场景：

• 协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作

分类：

• NEW（新纪录）与OLD(被修改的老记录)是2个行记录变量
  • 存储触发器中发生变化的记录

注意点：MYSQL数据库只支持行级触发器，不支持语句级触发器。Oracle数据则都支持。

3-2 触发器的创建

实例：通过触发器记录表的数据变更日志 , 包含增加, 修改 , 删除 ;

模板

create trigger trigger_name
before/after insert/update/delete   # 触发器类型
on tbl_name                         # 数据表名称
for each row                        # 行级触发器
    begin
        trigger_stmt ;              # SQL语句集合          
    end;

step1:创建日志表来保存操作记录

create table emp_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;

step2:创建INSERT型触发器，记录数据表的插入操作

DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
        age:',new.age,', salary:',new.salary,')'));
    end $
DELIMITER ;

step3:创建update型触发器，记录数据表的插入操作

DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,',
        age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',
        age:',new.age,', salary:',new.salary,')'));
    end $
DELIMITER ;

step4:创建delete 行的触发器 ,,记录删除数据的日志

DELIMITER $

create trigger emp_logs_delete_trigger
after delete
on emp
for each row
    begin
        insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
        values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
        age:',old.age,', salary:',old.salary,')'));
    end $
    
DELIMITER ;

总结：

• 触发器的创建必须要指定作用的数据表以及数据操作
• 触发器执行的SQL语句中可以通过new或者old获得之前的记录。

3-3 触发器的删除与查看

 drop trigger [schema_name.]trigger_name      # shema_name是数据库名称，没有指定则是当前数据库
 show triggers;                               # 查看触发器

• 上图中可以看到之前创建的3个触发器，以及触发之后执行的SQL语句。

参考资料

01 MYSQL的delimiter

02 数据库课程

03 数据库游标的概念和作用

---

20210228