[沫忘录]MySQL储存对象

视图

视图本质是对原表(基表)显示上的裁剪，可以当作表进行操作，其操作的结果会直接反馈到原表上，即对视图的操作实质上是对原表的操作。
MySQL不仅支持为基表创建视图，同时也支持为视图创建视图。

基本语法

视图创建
CREATE [OR REPLACE] VIEW 视图名称 AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

视图查询
#查看创建视图语句
SHOW CREATE VIEW 视图名称;
#查看视图数据(把视图当表操作)
SELECT * FROM 视图名称...;

视图修改
#方式一
#同视图创建，使用关键字OR REPLACE
#方式二
ALTER VIEW 视图名称 AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

视图删除
DROP VIEW [IF EXISTS] 视图名称

检查选项

当我们创建视图时，添加了限制条件，例如只查询id>10的数据，那么创建的数据只会引用id>10的数据。如果我们通过视图添加了id<10的数据，虽然数据会添加到基表，但视图仍只会显示id>10的数据。这再次表明视图是对基表显示上的裁剪。

但很多时候我们希望对基表的操作限制在视图范围内，避免在视图范围内"不可视"的操作，而增加检查选项可以有效解决这一问题。

检查选项可以有效检查插入、更新和删除等操作，以使其符合视图的定义。

WITH CASCADED CHECK OPTION
#向上检查所有视图及父视图是否满足视图范围
WITH LOCAL CHECK OPTION
#只检查当前视图是否满足视图范围

#当我们在视图中插入数据时，会向上检查所有父视图是否有检查选项。

更新及作用

视图的更新

如果视图中某一待修改元组不是基表上对应元组的子集，则该视图无法被更新。

怎样会造成元组无法对应的情况呢?

1. 使用聚合函数(COUNT, MAX, SUM)、窗口函数或GROUP BY等会造成多对一字段、一对多字段或字段对应函数计算值。
2. 使用DISTINCT、HAVING 或UNION(UNION ALL)等关键字时，会导致元组的索引条件不明确，无法定位到对应基表上的元组，故无法更新。

视图作用

• 便捷性

  常用的SQL查询可被定义为视图，减少了数据范围和查询条件，既简化用户对数据的理解，也简化对数据的操作。

• 安全性

  数据库只能数据库和表进行权限操作。而通过视图则将权限操作限定到行和列。这样通过视图用户只能查询和修改他们所能见到的数据。

• 数据独立性

  当表的某字段名发生变化时，往往需要修改和此字段相关的所有SQL语句。而创建视图时为修改字段起固定别名能否屏蔽这种变化，使基于此视图的SQL语句都不会受到影响。

存储过程

储存过程是事先经过编译并储存在数据库中的一段SQL语句的集合(类似于函数对整块执行逻辑的封装)。这种封装能够有效简化开发人员的很多工作，减少数据在数据库和应用服务器之间的传输(调用存储过程可减少对SQL语句的调用次数)，对于提高数据处理过程的效率是有好处的。

• 特点

  1. 封装性和复用性。
  2. 能够接受传出数据。
  3. 减少网络交互，效率提升。

存储过程语法

#创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	SQL语句;
END;

#调用
CALL 名称([参数]);

#查看
#查看指定数据库的所有存储过程的属性信息
SELECT * FROM information_schema.ROUTINES WHERE = '数据库名';
#查看储存过程的创建语句
SHOW CREATE PROCEDURE 名称;

#删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;

特别注意：

在储存过程中SQL语句以分号结尾，储存过程结束也是以分号结尾，而在命令行中分号是执行结束的标志，因此我们需要重新设置结束符。

#手动设置结束符
delimiter $$(可设置为其他符号)

变量

系统变量

​ MySQL服务器提供， 属服务层。分为全局变量(GLOBAL)、会话变量(SESSION)。

#查看系统变量
SHOW [GLOBAL|SESSION] VARIABLES [LIKE ''];
SELECT @@[GLOBAL|SESSION] 指定系统变量名;

#设置系统变量
SET [SESSION | GLOBAL] KEY = VALUE;
SET @@[SESSION | GLOBAL] KEY = VALUE;

用户定义变量

​ 用户变量不需提前声明，使用时直接用"@变量名"使用即可。作用域为当前连接。

#赋值
SET @VAR =[| :=] VALUE[,@VAR2=VALUE2]...;
SELECT @VAR =[| :=]VALUE[...];
SELECT 字段值 INTO @VAR FROM 表名;#将查询结果储存进用户变量。

#查询
SELECT @VAR;

局部变量

​ 局部变量在局部生效。访问前需要declare声明。可作为储存过程内的局部变量和输入参数。

#声明
DECLARE 变量名 变量类型[default 默认值];

#赋值操作同用户变量

if

IF 条件1 THEN
...
ELSEIF 条件2 THEN 
...
ELSE
...
END IF;

参数IO

• IN: 参数输入，调用时的传入值
• OUT: 参数输出，参数能做返回值
• INOUT: 输入输出参数

CREATE PROCEDURE 储存过程名([IN|OUT|INOUT 参数名 参数类型])
...
#示例
CALL p1(input, @outout);

case

CASE 判断值
	WHEN 值1 THEN ...
	[WHEN 值2 THEN ...]
	[ELSE ...]
END CASE;
#-----------------------
CASE 
	WHEN 条件1 THEN ...
	[WHEN 条件2 THEN ...]
	[ELSE ...]
END CASE;

while

WHILE 条件 DO
	SQL逻辑...
END WHILE;

repeat

REPEAT
	SQL逻辑
	UNTIL 条件
END REPEAT

loop

​ loop循环没有退出条件，需配合中断语句来使用。

• LEAVE: 在循环中退出循环(break)
• ITERATE: 在循环中跳过本轮(continue)

[label:]LOOP
	SQL 逻辑...
END LOOP [label];
#----------------
LEAVE label;
ITERATE label;

游标cursor

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

#声明游标, 游标使用的局部变量的声明应先于游标
DECLARE 游标名 CURSOR FOR 查询语句
#打开游标
OPEN 游标名;
#获取游标内数据
FETCH 游标名 INTO 变量[,变量2]...;#变量数等同于游标行字段数
#关闭游标
CLOSE 游标名;

当游标内有多行数据，就需要通过循环进行取值，但我们需要通过一些手段使游标内数据读完时能够退出循环——条件处理程序。

条件处理程序(handler)

​ 条件处理程序能够处理在流程控制结构执行过程中遇到问题时相应的处理步骤。

DECLARE handler_action HANDLER FOR contition_variable [,...] statement;
handler_action:
	CONTINUE: 继续执行
	EXIT: 终止退出
condition_value:
	SQLSTATE sqlstate_value(状态码, 如02000)
	SQLWARNING(所有以01开头的SQLSTATE代码简写)
	NOT FOUND(所有以02开头的SQLSTATE代码简写)
	SQLEXCEPTION(所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码简写)
statement:
	当handler满足时执行的SQL语句

存储函数

存储函数适用场景被储存过程覆盖，故存储函数较少用。

触发器

触发器是与表有关的数据库对象，能够在insert/update/delete前后执行定义的SQL语句集合。这种特性可以辅助完成数据完整性确保，日志记录和数据校验等操作。

使用别名OLD和NEW可以引用发生变化前后的数据。同时触发器只支持行级触发，不支持语句级触发。
tip: 行级触发指SQL语句对多行数据(元组)造成修改时(例如update更新多行)，会触发对于次数的触发器操作。而语句级触发无论对多少行数据造成修改，都只触发一次。

基本语法

#创建
CREATE TRIGGER 触发器名字 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW
BEGIN 
	SQL语句
END;

#查看
SHOW TRIGGERS;

#删除
DROP TRIGGER [schema_name.]trigger_name; 
如果没有指定schema_name数据库名，则默认当前所在数据库

ate更新多行)，会触发对于次数的触发器操作。而语句级触发无论对多少行数据造成修改，都只触发一次。

基本语法

#创建
CREATE TRIGGER 触发器名字 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW
BEGIN 
	SQL语句
END;

#查看
SHOW TRIGGERS;

#删除
DROP TRIGGER [schema_name.]trigger_name; 
如果没有指定schema_name数据库名，则默认当前所在数据库