MySQL学习12-＜进阶篇＞-视图/存储过程

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

# 创建视图
create or replace view stu_v_1 as select id,username,password,email from user where id <= 10;
# 查询视图
show create view  stu_v_1;
select * from stu_v_1;
# 修改视图
create or replace view stu_v_1 as select id,username,password from user where id <= 10;
alter view  stu_v_1 as select  id,username from user where id <= 10;
# 删除视图
drop view if exists stu_v_1;

检查选项

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

存储过程

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

特点：

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

基本语法

# 创建
create procedure tz()
begin
    select count(*) from user;
end;
# 调用
call tz();
# 查看
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'test';
show create procedure tz;
# 删除
drop procedure if exists tz;

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

变量

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

# 系统变量
# 查看
show session variables;
show global variables;
show global variables like '%dir%';
select  @@global.autocommit;
# 设置
set global autocommit  = 0;
set @@session .autocommit  = 0

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

# 用户变量
# 赋值
set @myname = '桃子';
set @myage := '18';
set @mygender:= '男',@myhobby := 'js';
select @myColor := 'pink';
select count(*) into @myCount from user;
# 使用
select @myname,@myage,@myhobby;
select @myColor,@myCount;

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

# 局部变量
create procedure p2()
begin
    declare user_count int default 0;
    select count(*) into user_count from user;
    select user_count;
end;

条件语句

if 判断

# if语句
create procedure  p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result := '优秀';
    elseif score >= 75 then
        set result := '良好';
    else
        set result := '需要努力';
    end if;
    select result;
end;
call p3();

参数

create procedure p3(in score int,out result varchar(10))
begin
    if score>=85 then
        set result := '优秀';
    elseif score >= 75 then
        set result := '良好';
    else
        set result := '需要努力';
    end if;
end;
call p3(76,@result);
select @result;

create procedure p4(inout score double)
begin
    set score := score *0.5;
end;
set @score = 99;
call p4(@score);
select @score;

case语句

 

# case语句
create procedure  p5(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) data;
end;
call p5(4);

while 循环

# while循环 计算1-n的和
create procedure p6(in n int)
begin
    declare total int default 0;
    while n > 0
        do
            set total := total + n;
            set n := n - 1;
        end while;
    select  total;
end;
call p6(10);

repeat循环

# repeat 循环
create procedure p7(in 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;
call p7(20);

loop 循环

LOOP实现简单的循环，如果不在saL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LooP可以配合一下两个语句使用
心
- LEAVE:配合循环使用，退出循环。
. ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

# loop 循环
create procedure p8(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 sum;
    select total;
end;
call p8(20);

create procedure p9(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 sum;
    select total;
end;
call p9(10);

游标-cursor

游标(CURSOR)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

# 游标
create  procedure  p10(in pwd varchar(255))
begin
    declare uName varchar(100);
    declare uEmail varchar(100);
    declare u_cursor cursor for select username,email from user where password = pwd;
    declare exit handler for SQLSTATE '02000' close u_cursor;

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key  auto_increment,
        username varchar(255),
        email varchar(255)
    );
    open u_cursor;
    while true
        do
            fetch u_cursor into uName,uEmail;
            insert into tb_user_pro values (null, uName, uEmail);
        end while;
    close u_cursor;
end;
call p10(123);

条件处理程序

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