视图-存储过程
一、视图
1.1介绍
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
1.2基本语法
- 创建
1create [or replace] view 视图名称[(列表名称)] as select 语句 [with [cascaded | local] check option]
- 查询
1查询创建视图语句：show create view 视图名称；
2查看视图数据： select * from 视图名称 ....;
- 修改
1方式一：create or replace view 视图名称[(列名列表)]] as select 语句[with [cascaded | local] check option]
- 删除
1drop view [if exists] 视图名称 [,视图名称] ...
1.3检测选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED和 LOCAL，默认值为 CASCADED 。
- cascaded 级联
当使用该属性时在进行视图检查时，上一级的检查条件会向下一级传递，并对每一级进行判断。
- local 本地
只会检查当前级别的判定条件。
1.4视图更新规则
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：A.聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION或者 UNION ALL
1.5视图的好处
- 简单
视图不仅可以简化数据库使用者对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得使用者不必为以后的操作每次指定全部的条件。
- 安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
- 数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
案例
二、存储过程
2.1介绍
存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL语言层面的代码封装与重用，即SQL的函数。
特点：
- 封装，复用—》可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
- 可以接收参数，也可以返回数据
- 减少网络交互，提升效率
2.2基本语法
- 创建
1create procedure 存储过程名称 ([参数列表])
2begin
3 -- SQL语句
4end;
- 调用
1call 名称([参数]);
- 查看
1
2SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指
3定数据库的存储过程及状态信息
4
5show create procedure 存储过程名称; -- 查询某个存储过程的定义
- 删除
1drop procedure [if exists] 存储过程名称;
2.3变量
在MySQL中变量分为三种类型：系统变量，用户定义变量，局部变量。
系统变量
系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。
- 查看系统变量
1show [session | global] variables; -- 查看所有系统变量
2SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
3SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
- 设置系统变量
1set [ SESSION | GLOBAL ] 系统变量名 = 值 ;
2SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
如果没有指定session/global,默认是session。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
- 用户定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量
名” 使用就可以。其作用域为当前连接。
赋值
方式一：
1set @var_name=expr [,@var_name=expr]...;
2set @var_name := expr [, @var_name := expr] ... ;
赋值时，可以使用 = ，也可以使用 := 。
方式二:
1select @var_name :=expr [,@var_name :=expr]...;
2select 字段名 into @var_name from 表名；
使用
1select @var_name;
- 局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的
局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块
声明
1declare 变量名 变量类型 [default ...];
变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
赋值
1set 变量名 =值；
2set 变量名 :=值；
3select 字段名 into 变量名 from 表名 ...;
2.4流程控制语句
参数类型
| 参数 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
if
用于做条件判断，具体的语法结构为：
1IF 条件1 THEN
2.....
3ELSEIF 条件2 THEN -- 可选
4.....
5ELSE -- 可选
6.....
7END IF;
case
1-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
2执行statement_list2， 否则就执行 statement_list
3CASE case_value
4
5 WHEN when_value1 THEN statement_list1
6 [ WHEN when_value2 THEN statement_list2] ...
7 [ ELSE statement_list ]
8END CASE;
1-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
2立时，执行statement_list2， 否则就执行 statement_list
3CASE
4 WHEN search_condition1 THEN statement_list1
5 [WHEN search_condition2 THEN statement_list2] ...
6 [ELSE statement_list]
7END CASE;
while
while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
1-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
2WHILE 条件 DO
3 SQL逻辑...
4END WHILE;
repeat
repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。具体语法为：
1-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
2REPEAT
3 SQL逻辑...
4 UNTIL 条件
5END REPEAT;
loop
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
LOOP可以配合一下两个语句使用：
- leave:配合循环使用，退出循环。类似break。
- iterate:必须用在循环中，作用是跳过当前循环剩余的语句，直接进去下一循环。类似countie。
语法：
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
游标
游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进
行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下
- 语法
A.声明游标
1declare 游标名称 cursor for 查询语句;
B.打开游标
1open 游标名称;
C.获取游标记录
1fetch 游标名称 into 变量 [.变量];
D.关闭游标
1close 游标名称;
案例
1-- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
2-- （name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
3-- (id,name,profession)中。
4create procedure p11(in uage int)
5begin
6 declare uname varchar(100);
7 declare pro varchar(100);
8 declare u_cursor cursor for select name ,profession from tb_user where age<=uage;
9 drop table if exists tb_user_pro;
10 create table if not exists tb_user_pro(
11 id int primary key auto_increment,
12 name varchar(100),
13 profession varchar(100)
14 );
15 open u_cursor ;
16 while true do
17 fetch u_cursor into uname,pro;
18 insert into tb_user_pro values(null,uname,pro);
19 end while;
20 close u_cursor;
21end;
2.5条件处理程序
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体
语法为：
1DECLARE handler_action HANDLER FOR condition_value [, condition_value]
2... statement ;
3handler_action 的取值：
4 CONTINUE: 继续执行当前程序
5 EXIT: 终止执行当前程序
6condition_value 的取值：
7 SQLSTATE sqlstate_value: 状态码，如 02000
8 SQLWARNING: 所有以01开头的SQLSTATE代码的简写
9 NOT FOUND: 所有以02开头的SQLSTATE代码的简写
10 SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
案例
1create procedure p11(in uage int)
2begin
3 declare uname varchar(100);
4 declare pro varchar(100);
5 declare u_cursor cursor for select name ,profession from tb_user where age<=uage;
6 -- 声明条件处理程序： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor,并退出
7 declare exit handler for SQLSTATE '02000' close u_cursor;
8 drop table if exists tb_user_pro;
9 create table if not exists tb_user_pro(
10 id int primary key auto_increment,
11 name varchar(100),
12 profession varchar(100)
13 );
14 open u_cursor ;
15 while true do
16 fetch u_cursor into uname,pro;
17 insert into tb_user_pro values(null,uname,pro);
18 end while;
19 close u_cursor;
20end;