MySQL-进阶篇-视图/存储过程/触发器
温馨提示：建议在PC端浏览~
视图/存储过程/触发器（MySQL数据库中的存储对象）
视图
介绍
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视
图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
创建
CREATE[ORREPLACE]VIEW视图名称[(列名列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];
查询
# 查看创建视图语句SHOWCREATEVIEW视图名称;# 查看视图数据（视图就是一张虚拟的表，可以像操作表一样操作视图）SELECT*FROM视图名称......;
修改
# 方式一CREATEORREPLACEVIEW视图名称[(列名列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];# 方式二ALTERVIEW视图名称[(列名列表)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];
删除
DROPVIEW[IFEXISTS]视图名称;
视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。
CASCADED（级联）
# 往v1插入数据时不会检查idcreateviewv1asselectid,namefromstudentwhereid<=20;# 往v2插入数据时会检查id是否满足10<=id<=20，只有符合条件的数据才允许插入createviewv2asselectid,namefromv1whereid>=10withcascadedcheckoption;# 往v3插入数据时会检查id是否满足10<=id<=20，只有符合条件的数据才允许插入# 由于创建v3视图时没有添加检查选项，所以不会检查id是否满足id<=15createviewv3asselectid,namefromv2whereid<=15;
LOCAL
# 往v1插入数据时不会检查idcreateviewv1asselectid,namefromstudentwhereid<=15;# 往v2插入数据时会检查id是否满足id>=10，只有符合条件的数据才允许插入# 由于v1在创建时没有添加检查选项，所以不会检查id是否满足id<=15createviewv2asselectid,namefromv1whereid>=10withlocalcheckoption;# 往v3插入数据时会检查id是否满足id>=10，只有符合条件的数据才允许插入# 由于创建v1和v3视图时没有添加检查选项，所以不会检查id是否满足id<=15和id<20createviewv3asselectid,namefromv2whereid<20;
视图的更新
- 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
- 1、聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
- 2、DISTINCT
- 3、GROUP BY
- 4、HAVING
- 5、UNION 或者 UNION ALL
- 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
作用
- 简单
- 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
- 安全
- 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
- 数据独立
- 视图可帮助用户屏蔽真实表结构变化带来的影响。
- 简单
存储过程
介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
特点
- 封装，复用
- 可以接收参数，也可以返回数据
- 减少网络交互，效率提升
创建
CREATEPROCEDURE存储过程名称([参数列表])BEGIN-- SQL语句END;
调用
CALL名称([参数]);
查看
# 查询指定数据库的存储过程及状态信息SELECT*FROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_SCHEMA='xxx';# 查询某个存储过程的定义DROPPROCEDURE[IFEXISTS]存储过程名称;
删除
DROPPROCEDURE[IFEXISTS]存储过程名称;
注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。例如：
delimiter$$
变量
系统变量
系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL)、会话变量（SESSION)。
查看系统变量
# 查看所有系统变量SHOW[SESSION|GLOBAL]VARIABLES;# 可以通过LIKE模糊匹配方式查找变量SHOW[SESSION|GLOBAL]VARIABLESLIKE'......';# 查看指定变量的值SELECT@@[SESSION|GLOBAL].系统变量名;
设置系统变量
SET[SESSION|GLOBAL]系统变量名=值;SET@@[SESSION|GLOBAL].系统变量名=值;
注意
- 如果没有指定SESSION / GLOBAL，默认是SESSION，会话变量。
- mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。
用户自定义变量
用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。
赋值
SET@var_name=expr[,@var_name=expr,......];SET@var_name:=expr[,@var_name:=expr,......];SELECT@var_name:=expr[,@var_name:=expr,......];SELECT字段名INTO@var_nameFROM表名;- 补充：赋值既可以使用=，也可以使用:=，但是推荐使用:=，因为在MySQL服务器中，=也用作等值比较，赋值操作使用:=可以与等值比较操作进行区分。
使用
SELECT@var_name[,@var_name2,@var_name3,......];
注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
局部变量
局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN…END块。
声明
DECLARE变量名 变量类型[DEFAULT...];- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
赋值
SET变量名=值;SET变量名 :=值;SELECT字段名INTO变量名FROM表名...;
if条件判断
语法
IF条件1THEN......ELSEIF条件2THEN-- 可选......ELSE-- 可选......ENDIF;
参数
类型
用法
CREATEPROCEDURE存储过程名称([IN/OUT/INOUT参数名 参数类型])BEGIN-- SQL语句END;
case
语法一
CASEcase_valueWHENwhen_valuelTHENstatement_listl[WHENwhen_value2THENstatement_list2]...[ELSEstatement_list]ENDCASE;
语法二
CASEWHENsearch_condition1THENstatement_listl[WHENsearch_condition2THENstatement_list2]...[ELSEstatement_list]ENDCASE;
while
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑WHILE条件DOSQL逻辑...ENDWHILE;
repeat
repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：
#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环REPEATSQL逻辑...UNTIL 条件ENDREPEAT;
loop
LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：
LEAVE：配合循环使用，退出循环。
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:]LOOPSQL逻辑...ENDLOOP[end_label];LEAVElabel;-- 退出指定标记的循环体ITERATElabel;-- 直接进入下一次循环
游标
游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标
的声明、OPEN、FETCH和CLOSE，其语法分别如下。声明游标
DECLARE游标名称CURSORFOR查询语句;
打开游标
OPEN游标名称;
获取游标记录
FETCH游标名称INTO变量[,变量];
关闭游标
CLOSE游标名称;
条件处理程序
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：
-- 语法：DECLAREhandler_actionHANDLERFORcondition_value[,condition_value]...statement;handler_actionCONTINUE：继续执行当前程序EXIT：终止执行当前程序 condition_value SQLSTATE sqlstate_value：状态码，如02000SQLWARNING：所有以01开头的SQLSTATE代码的简写NOTFOUND：所有以02开头的SQLSTATE代码的简写 SQLEXCEPTION:所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE代码的简写-- 示例：declareexithandlerforSQLSTATE'02000'closeU_cursor;declareexithandlerfornotfoundcloseU_cursor;- 提示：更多详细信息请参考MySQL官网https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html。
存储函数（很少使用，一般使用存储函数的场景都可以使用存储过程替代）
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：
CREATEFUNCTION存储函数名称([参数列表])RETURNStype[characteristic...]BEGIN-- SQL语句RETURN...;END;characteristic说明：DETERMINISTIC：相同的输入参数总是产生相同的结果NOSQL：不包含SQL语句。READSSQLDATA：包含读取数据的语句，但不包含写入数据的语句。
触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
- 行级触发器：SQL语句影响了多少条数据，触发器就被触发几次。例如一条update语句影响了5条数据，那么针对update语句创建的触发器就会触发5次。
触发器类型
语法
创建
CREATETRIGGERtrigger_name BEFORE/AFTERINSERT/UPDATE/DELETEONtable_nameFOR EACH ROW-- 行级触发器BEGINtrigger_statement;END;
查看
SHOWTRIGGERS;
删除
DROPTRIGGER[schema_name.]trigger_name;-- 如果没有指定schema_name，默认为当前数据库。
示例（以针对update语句创建的触发器为例）