MySQL - 存储对象

视图

视图（View）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时自动生成的。

• 创建视图：create [or replace] view 视图名称[(列名列表)] as select查询语句;

• 查询视图：
  1、查看创建视图的语句：show create view 视图名称;
  2、查看视图数据：select * from 视图名称;

• 修改视图：
  1、create [or replace] view 视图名称[(列名列表)] as select查询语句;
  2、alter view 视图名称[(列名列表)] as select查询语句;

• 删除视图：drop view [if exist] 视图名称 [,视图名称...];

• 视图检查选项：当使用with [cascaded / local] check option字句创建视图时，在对视图进行插入、更新、删除这些操作时，MySQL 会通过视图检查来判断要操作的数据是否符合创建视图时select查询语句中的条件，不符合则无法插入。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则来保持一致性，cascaded（默认）、local 是 MySQL 用来确定检查范围的两个选项：
  1、cascaded：如果创建视图指定了该选项，那么在操作当前视图时会检查当前视图的条件、以及递归检查当前视图依赖的所有视图的条件，如果依赖的视图没有添加检查选项，则会给强制添加上，保证所有视图的检查条件生效。
  2、local：会检查当前视图的条件、以及尝试递归检查当前视图依赖的所有视图的条件，但是如果依赖的视图没有添加检查选项则不检查它的视图条件，也就是不会给其强制添加视图检查选项，只保证有检查选项的视图条件生效。

• 视图更新：要让视图可以更新，视图中的行与基础表中的行之间必须存在一对一关系，如果视图包含如下内容，则不能更新：
  1、聚合函数
  2、distinct
  3、group by
  4、having
  5、union、union all

• 视图的作用：
  1、视图不仅可以简化用户对数据的理解，还可以简化操作。那些经常使用的查询可以被定义为视图，这样不用为以后相同的操作每次指定全部查询条件，而是直接从视图查询。
  2、数据库可以通过权限管理来指定用户访问那些库那些表，但不能授权用户只访问特定的行数据以及列，这些数据可以通过视图来提供给用户。
  3、视图可以帮助用户屏蔽真实的表结构，用户只关心需要的数据即可。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以传递参数、以及返回结果数据，简化了开发、减少数据在数据库和应用服务器之间的传输，进而提高数据的处理效率。核心思想就是数据库 SQL 语言层面的代码封装与重用。

基本操作

• 创建存储过程

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

上边语句在命令行无法成功执行，因为 end 之前还有;，导致错误的识别 SQL 结束，可以用如下方式：

-- 指定结束限定符，这样遇到 $$ 才认为 SQL 结束
delimiter $$
create procedure 存储过程名称([in/out/inout 参数名 参数类型, ...])
begin
    -- sql 语句;
    -- sql 语句;
end$$
-- 指定 SQL 结束符为默认的分号
delimiter ; 

• 调用存储过程

call 存储过程名称([参数列表]);

• 查看存储过程

-- 查询指定数据库的存储过程以及状态信息
select * from information_schema.routines where routine_schema = '数据库名称';

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

• 删除存储过程

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

变量

• 系统变量

-- 查看所有系统变量
show [global | session] variables;
-- 模糊查找系统变量
show [global | session] variables like '';
-- 查看指定系统变量，select @@session.autocommit; select @@autocommit;
select @@[global | session].变量名;

-- 设置系统变量
set  [global | session] 变量名 = 值;
-- set @@session.autocommit = 0; set @@autocommit = 0;
set  @@[global | session].变量名 = 值;

如果不指定 global、session，默认是 session；设置系统变量指定为 global，MySQL 服务重启后也会失效。

• 用户自定义变量
  用户自定义变量不用提前声明，直接用@变量名使用即可，作用域为当前连接。

-- 给变量赋值，推荐使用 :=
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 表名;

-- 使用变量，没赋值的变量到的值是 null
select @var_name [, @var_name]...;

• 局部变量
  局部变量在访问之前，需要使用declare声明，可以作为存储过程内的局部变量和输入参数，在begin end块之内有效。

-- 声明局部变量，类型就是数据库支持的类型
declare 变量名 变量类型 [default 默认值];

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

-- 取值
select 变量名;

条件判断

• if

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

• 参数
  1、in，默认的类型，标记参数为输入类型的，可以作为输入参数
  2、out，标记参数为输出类型的，可以作为返回值
  3、inout，可以作为输入参数也可以作为返回值

create procedure p(in score int, out result varchar(10))
begin
    if score >= 80 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

-- 调用存储过程，使用用户自定义变量接收结果
call p(70, @result);
select @result;

• case

create procedure p(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 && month <= 3 then set result = '第一季度';
        when month >= 4 && month <= 6 then set result = '第二季度';
        else set result = '其它';
    end case;
end;

循环

• while

while 条件 do
    sql 逻辑...
end while;

• repeat

repeat
    sql 逻辑...
    until 条件
end repeat;

• loop

[label:]loop
    sql 逻辑...
    [leave [label]; 退出循环 | iterate [lable]; 直接进入下一次循环]
end loop [label];

条件处理程序

用来定义在流程控制结构执行过程中遇到问题时的处理办法。
declare handler_action handler for condition_value [, condition_value...] statement;

• handler_action
  1、continue：继续执行当前程序
  2、exit：终止执行当前程序
• condition_value
  1、sqlstate 状态码
  2、sqlwarning：所有以01开头的 sqlstate 代码的简写
  3、not found：所有以02开头的 sqlstate 代码的简写
  4、sqlexception：所有没被 sqlwarning、not found 捕获的 sqlstate 代码的简写

游标

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

• 声明游标：declare 游标名称 cursor for 查询语句;
• 打开游标：open 游标名称;
• 获取游标记录：fetch 游标名称 into 变量[, 变量...];
• 关闭游标：close 游标名称;

create procedure p(in user_age int)
begin
    declare _name varchar(100);
    declare _profession varchar(100);
    -- 声明游标，存储查询的结果集
    declare u_cursor cursor for select name, profession from user where age >= user_age;
    -- 声明一个条件处理程序，当状态码为0200时执行退出操作、关闭游标
    declare exit handler for sqlstate '0200' close u_cursor;
    -- 创建新表
    drop table if exists user2;
    create table user2(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100));
    -- 打开游标
    open u_cursor;
    while true do
        -- 获取游标记录
        fetch u_cursor into _name, _profession;
        -- 插入记录到新表
        insert into user2 (name, profession) values (_name, _profession);
    end while;
    -- 关闭游标
    close u_cursor;
end;

存储函数

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

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

• type：返回值类型
• characteristic：
  1、deterministic：相同的输入参数总是产生相同的结果
  2、no sql：不包含 sql 语句
  3、reads sql data：包含读取数据的语句，但不包含写入数据的语句

create function fun_sum(n int)
returns int no sql
begin
    declare total int default 0;
    while n > 0 do
        set total := total + n;
        set n := n - 1;
    end while;
    return total;
end;

select fum_sum(100);

触发器

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

使用别名 old、new 来引用触发器中发生变化的记录内容，目前只支持行级触发器（修改多少行记录就触发几次）不支持语句级触发器。

触发器类型	new、old
insert	new 表示将要或已经新增的数据
update	old 表示修改之前的数据，new 表示将要或修改后的数据
delete	old 表示将要或已经删除的数据

• 创建

create trigger trigger_name
before/after insert/update/delete
on table_name for each row
begin
    trigger_statement;
end;

• 查看

show triggers;

• 删除

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

• 实例

-- 给 tb_user 表添加数据后，自动给日志表添加日志：
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('insert', '张三', now(), new.content);
end;

-- 更新 tb_user 表数据后，自动给日志表添加日志：
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('update', '张三', now(), concat('更新前的数据', old.content, '；', '更新后的数据', new.content));
end;

-- 从 tb_user 表删除数据后，自动给日志表添加日志：
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('delete', '张三', now(), old.content);
end;