花了几天时间，整理了MySQL的基础内容，看完后你就是合格的增删改查程序员了。通过 itcast课程进行整理。

本章节主要介绍mysql的视图/存储过程/触发器

视图

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

简而言之：视图只保存了查询的SQL逻辑，不保存查询的结果，所以我们在创建视图的时候，主要工作就落在创建这条sql查询语句上。

操作相关

创建视图

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

-- 从查询中，创建或者替换一个视图，视图名字叫做stu_v_1
create or replace view stu_v_1 as select id,name from tb_user where id <=10;  


-- 查询视图创建信息 
show create view stu_v_1;

-- 查询视图数据，此时可以理解为视图就是表
select * from stu_v_1 where id=3;

修改

• 方式一： create [or replace] view 视图名称[(列名列表)] as select 语句 [with [cascaded|local] check option ] ，眼熟不眼熟，是不是和创建一样？
• 方式二：alter view 视图名称[(列名)] as select 语句 [with[cascaded|local] check option ]]

create or replace view stu_v_1 as select id,name,age from tb_user where id <=10;  

alter view stu_v_1 as select id,name,age from tb_user where id <=12;  

删除

DROP view [IF EXISTS] 视图名称 [视图名称] ...

drop view if exists stu_v_1;

增加视图数据

insert into 视图名称 values(列值) 和表操作一样 insert into stu_v_1 values(13,"hello",12); 但此时要注意了：

• 视图是虚拟的表，不存在数据，所以插入的话相当于在实际表中插入数据。
• 如果实际表中有id为13，且id为主键的话，则不能插入数据。
• 如果成功插入id为13的，但是我们在视图中还是找不到，原因是我们创建视图的条件是 id<=10。

选项以及解释

视图检查选项_cascaded

当使用with cascaded|local check option时，mysql会通过视图检查正在更改的每个行，例如插入、更新、删除以使其符合视图的定义。
如上增加了13的数据，但是在视图中查不到，因为我们创建视图的时候并未指定任何选项，且创建视图的条件是id<=10,但视图给我们提供了选项避免此情况的发生。

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

mysql> insert into stu_v_1 values(13,"hello",12);
ERROR 1369 (HY000): CHECK OPTION failed 'study.stu_v_1'

则会阻止你的插入，因为你插入的和你查询的，是不一致的。

视图的更新

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

• 聚合函数或窗口函数(sum()、min()、max()、count()等等)
• distinct 去重
• group by
• having
• union 或 union all

如下：开始通过count创建了视图，后续就不能插入或者更新了，因为视图中的行和表中的行不一一对应。

mysql> create view stu_v_2 as select count(*) FROM tb_user;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stu_v_2;
+----------+
| count(*) |
+----------+
| 12099605 |
+----------+
1 row in set (9.43 sec)
mysql> insert into stu_v_2 values(19);
ERROR 1471 (HY000): The target table stu_v_2 of the INSERT is not insertable-into

视图的作用

• 简单，视图不仅可以简化用户对数据的理解，也可以简化他们的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全，数据库可授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。比如可以把用户的姓名和号码给隐藏起来，其他的字段可以通过视图查看，保障了安全性。最大的作用。
• 数据独立，视图可以帮助用户屏蔽真实表结构变化带来的影响。 这个没太大的作用。

以下为安全操作流程

• root用户创建milo用户。
• 授予study库的stu_v_1给milo用户，只授予select权限。GRANT SELECT ON study.stu_v_1 TO 'milo'@'localhost';
• milo用户登录上来后，只能看当前授予的视图。

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| stu_v_1         |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from stu_v_1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | e      |   12 |
|  2 | 用户2  |   35 |
|  3 | 用户3  |   20 |
+----+--------+------+
12 rows in set (0.00 sec)
mysql> update stu_v_1 set name='d' where id=1;
ERROR 1142 (42000): UPDATE command denied to user 'milo'@'localhost' for table 'stu_v_1'

存储过程

我们先代入一下逻辑：在操作数据库的时候，可能会有很多操作，比如我先查询数据，在更新第一张表的数据，再更新第二张表的数据等等，这样操作多次的逻辑，就会有多次连接，此时我们能否优化呢？可以的，可以使用 存储过程。 存储过程：是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的，简单来说，存储过程就是数据库SQL语言层面的代码封装和重用。

特点

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

基本语法

创建

 create procedure 存储过程名称([参数列表])
 
 begin
     --sql语句，可以多个
 end;
 
 call 名称([参数]) --调用

如一个最简单的，统计。
但是不能在命令行中创建，因为出现分号则mysql会认为你已经结束了。但是可以通过设置delimiter 指定结束符号。

-- delimiter $$ --指定结束符号
create procedure p1()
begin
select count(*) from tb_user;
end;
-- end $$ --表示结束

call p1(); -- 调用

• 创建成功后，我们也可以通过如下命令查看：

 select * from information_schema.routines where ROUTINE_SCHEMA='study'; -- 指定数据库的存储过程以及状态信息
 
 show create procedure p1; -- 查询某个存储过程的定义

• 删除

drop procedure if exists p1;

变量

变量分为三种，一种是系统变量，一种是用户定义变量,还有一个是局部变量

系统变量

mysql服务提供，不是用户定义的，属于服务器层面，分为全局变量(global)和会话变量(session)。

• 全局变量，可以理解为全局有效，比如设置了mysql的系统变量。
• 会话变量，仅仅在当前的会话窗口内有效。

查看系统变量：

show [session|global] variables;  -- 查看所有系统变量,可以不指定，默认session
show global variables;

show [session|global] variables like '...'; -- 可以通过like模糊匹配方式查找变量,可以不指定，默认session
show global variables like 'auto%'; 

select @@[session|global] 系统变量名; -- 查看指定变量的值,可以不指定，默认session
select @@session.autocommit;

设置系统变量

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

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

注意： 1：如果没有指定session|global,默认为session，会话变量。
2：mysql服务重新启动之后，所设置的全局参数也会失效，如想永久有效，在etc/my.cnf中配置后重启。

用户定义变量

用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接使用'@变量名'使用就可以了。其作用域为当前连接。

赋值，用户自定义变量无序提前声明或初始化，直接赋值即可。假如说我们使用了这个变量，但是还未赋值，则该变量为null,如 select @aaa：

set @var_name=expr[,@var_name=expr]...;
set @var_name:=expr[,@var_name:=expr]...;

set @myname = 'miloyang';
set @myage := 33,@mysex:='男'; -- 可以赋值多个，推荐使用:=为赋值，因为=也有相等的意思。

-------
select @var_name:=expr [,@var_name:=expr]...;
select 字段名 into @var_name from 表名;

select @myname:='milo',@mysex:='女'; -- 复制完成后直接查询了
select count(*) into @mycount from tb_user; -- 把tb_user表查询的count(*)的结果，赋值给@mycount的变量

查看

select @myname;
select @myname,@myage;
select @mycount;

局部变量

根据需要定义在局部生效的变量，访问之前需要declare声明，可用做存储过程内部的局部变量和输入参数，局部变量的范围在内部声明的 begin...end 之间,超出则不可用。 声明：
declare 变量名 变量类型 [default ...]; 变量类型就是有：int,bigint,char,varchar,time等等数据库的字段类型。
赋值：

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

create PROCEDURE p2()
BEGIN
DECLARE stu_count int DEFAULT 0;
set stu_count = 100;

# set sut_count:=100;
select count(*) into stu_count from tb_user;
select stu_count;
END;

if判断

语法：

if 条件1 then
    ...
elseif 条件2 then 
    ...
else
    ...
end if;

来个例子，根据年龄，年龄小于10为少年，小于10-20为青少年，大于20-30为青年，大于30为中年。

create PROCEDURE p3()
BEGIN
    declare re_age int default 0;
    declare re_str varchar(10); 
    
    set re_age:=30;
    
    if re_age <=10 then
        set re_str:='少年';
    elseif re_age <=20 then
        set re_str:='青少年';
    elseif re_age <=30 then
        set re_str:='青年';
    else 
        set re_str:='中年';
    end if;
        
    select re_age,re_str;
    
END;

call p3();

参数

我们上述的例子很死板，年龄是写死的，如果可以通过参数传递，这样p3根据传入的年龄，返回对于的年龄段，这样才属于封装。
传参来了，有以下类型：

• in，该参数作为输入，也就是需要调用时传入值。默认的。
• out，该类参数作为输出，也就是该参数可以作为返回值。
• inout，该类型参数可以作为入参，也可以作为返回的出参。 语法：

create PROCEDURE 名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
...
END;

改进上面的例子，随机id的age，根据传入的age返回对应的年龄段的值。

create PROCEDURE p4(in re_age int, out re_str varchar(10))
BEGIN

    if re_age <=10 then
        set re_str:='少年';
    elseif re_age <=20 then
        set re_str:='青少年';
    elseif re_age <=30 then
        set re_str:='青年';
    else 
        set re_str:='中年';
    end if;
    
END;


create PROCEDURE p5()
BEGIN
    declare rand_id int default 0;
    declare re_age int default 0;

    select floor(rand()*100) into @rand_id;     #随机id
    select age into @re_age from tb_user where id = @rand_id; #通过id得到age
    
    call p4(@re_age, @result_age); #传入age，以及接收的参数

    select @re_age,@rand_id, @result_age; # 输出
END;

call p5();

case

有两种写法： 直接来例子吧， 改写上面的p4，通过case来返回。
语法一：
这种语法适合里面为确定的值

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

create PROCEDURE p7(in re_age int, out re_str varchar(10))
BEGIN
    case re_age
            when 10 then
                set re_str:='少年';
            when 20 then
                set re_str:='青少年';
            when 39 then
                set re_str:='青年';
            else
                set re_str:='中年';
        end case;
END;

语法二：
这种语法适合里面为区间的值

case 
        when search_condition1 then  statement_list1
        when search_condition2 then  statement_list2
        else search_condition1
end case;

create PROCEDURE p6(in re_age int, out re_str varchar(10))
BEGIN
    case
            when re_age<=10 then
                set re_str:='少年';
            when re_age<=20 then
                set re_str:='青少年';
            when re_age<=39 then
                set re_str:='青年';
            else
                set re_str:='中年';
        end case;
END;

call p6(20,@re_str)
select @re_str;

循环语法

循环结构中，一般用到的有while，repeat，loop三种循环语法。

while

while循环式有条件的循环控制语句，满足条件后，再执行循环体中的sql语句，具体语法为：

while 条件 do
    sql...
end while;

来个例子，计算从1加到n的值。

create PROCEDURE p8(in num int,out total int)

BEGIN
    DECLARE idx int default 0;
    DECLARE add_num int default 0;
        
    while idx<num do
        set add_num=add_num+idx;
        set idx=idx+1;
    end while;
    set total:=add_num;
END;

call p8(5,@total)
select @total;

repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环，类似于do while，上来先去repeat中间去循环一次，然后再去判断until中的条件是否满足，满足就走下一次。具体语法为：

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

继续上个例子，计算1到n的值。

create PROCEDURE p9(in num int)
begin
    DECLARE total int default 0;
    
    repeat
            set total:=total+num;
            set num:=num-1;
        until num<=0
    end repeat;
end;

call p9(5)

loop

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

• leave：配合循环使用，退出循环，类似break。 退出指定标记的循环体为:leave label;
• iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。直接进入下一次循环体为:iterate label;

结构为：

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

来个例子，继续计算从1加到n的值。

create PROCEDURE p10(in num int)
begin
    DECLARE total int default 0;
    sum:loop
        if num<=0 then
            leave sum; #退出sum循环
        end if;

        set total:=total+num;
        set num:=num-1;
    
    end loop sum;
    
    select total;
end;

再来一个，计算1到n的偶数值的相加。

create PROCEDURE p11(in num int)
begin
    DECLARE total int default 0;
    
    sum:loop
        if num<=0 then
            leave sum; #退出sum循环
        end if;
        
        if num%2 = 1 then
              set num:=num-1;
                iterate sum; #继续下一个 sum循环
        end if;
        
        set total:=total+num;
      set num:=num-1;
    
    end loop sum;
    
    select total;
end;

游标(cursor)

我们在begin和end之间定义了局部变量，只能接收单行单列的具体某一个值比如 select age from tb_user where id=1的值，如果要把全部数据接收，如select * from tb_user where id>10这些数据接收怎么办呢？ 游标来了。
游标：用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明，open/fetch/close等等，如下介绍。

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

来吧，例子：根据传入的用户年龄uage，查询用户表中所有的用户年龄等于uage的用户姓名和电话号码，并将用户的姓名和号码插入到锁创建的一张新表中。

create procedure p12(in u_age int)
begin
    declare uname varchar(10);
    declare uphone varchar(10);
    declare uage int;

    declare u_cursor cursor for select name, phone, age from tb_user where age = u_age; # 声明游标，通过查询获取
    # declare exit handler for sqlstate '02000' close u_cursor; # 条件处理程序，稍后解释
     declare exit handler for not found close u_cursor; # 条件处理程序，稍后解释

    drop table if exists tb_user_phone;
    create table if not exists tb_user_phone
    (
        id    int primary key auto_increment,
        name  varchar(100),
        phone varchar(20),
        age   int
    );

    open u_cursor;
    #开启游标

    #循环遍历
    while true
        do
            fetch u_cursor into uname,uphone,uage; #此时的顺序，要和上面select的顺序一样。
            insert into tb_user_phone values (null, uname, uphone, uage); #插入到新表中
        end while;
    close u_cursor;
end;

条件处理程序

条件处理程序(handle),可以用来定义在流程控制执行过程中遇到问题时相对应的处理步骤，具体语法为：

declare handler_action HANDLER FOR condition_vale [,condition_value] ... statement;

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

具体的可以查看： mysql错误信息

存储函数

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

create function 存储函数名称 ([参数列表]) #这个参数列表只能是in类型
returns type [characteristion ...] # 返回值的类型
begin
    --sql语句
    return ...;
end;

charcteristion说明：

• deterministic：相同的输入参数总数产生相同的结果
• no sql：不包含sql语句
• reads sql data：包含读取数据的语句，但不包含写入数据的语句

来个例子，哈哈，还是1加到n。

create function fun1(n int)
    returns int
    #no sql
    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(100);

存储函数用的相对较少，因为存储函数必须要指定返回值，且存储函数都可以使用存储过程来替代。

触发器

触发器是与表有关的数据库对象，在insert/update/delete之前或之后，触发并执行触发器中定义sql语句集合，理解为钩子。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等等操作。触发器只支持行级触发，比如执行update的时候，影响了5行数，则触发器会触发五次。不支持语句级触发。使用old和new来引用触发器中发生变化的记录内容。如果对canal有了解的话，这个与其差不多。

• 创建触发器

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.]grigger_name; 如果没有指定schema_name，默认为当前数据库

来个例子(没办法使用加到n了...)，通过触发器记录tb_user表的数据变更日志，将变更日志插入到log表中，包含增、删、改。

• 日志表结构：

  create table user_logs
  (
    id               int primary key auto_increment,
    operation        varchar(20) not null comment '操作类型',
    operation_time   datetime    not null comment '操作时间',
    operation_id     int(11)     not null comment '操作的id',
    operation_params varchar(50) comment '操作的参数'
  )
  

• 定义触发器 增加的触发器

create trigger tb_user_insert_trigger
    after insert
    on tb_user
    for each row
begin
    insert into user_logs(id, operation, operation_time, operation_id, operation_params)
    values (null, 'insert', now(),
            new.id,
            concat('插入的数据内容为:id=', new.id, 'name=', new.name, 'age=', new.age));
end;

#查看触发器

show triggers ;

# 删除触发器
drop trigger tb_user_insert_trigger;

此时如果在tb_user表中插入一条数据后，则user_logs表中会自动插入一条数据。

更新、删除的例子就不来了，修改上述的，把after修改为 update/detele，然后更新之前的数据使用old.id,old.name...，更新之前的，还是new.id,new.name...。