温馨提示建议在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,namefromstudentwhereid20;# 往v2插入数据时会检查id是否满足10id20只有符合条件的数据才允许插入createviewv2asselectid,namefromv1whereid10withcascadedcheckoption;# 往v3插入数据时会检查id是否满足10id20只有符合条件的数据才允许插入# 由于创建v3视图时没有添加检查选项所以不会检查id是否满足id15createviewv3asselectid,namefromv2whereid15;LOCAL# 往v1插入数据时不会检查idcreateviewv1asselectid,namefromstudentwhereid15;# 往v2插入数据时会检查id是否满足id10只有符合条件的数据才允许插入# 由于v1在创建时没有添加检查选项所以不会检查id是否满足id15createviewv2asselectid,namefromv1whereid10withlocalcheckoption;# 往v3插入数据时会检查id是否满足id10只有符合条件的数据才允许插入# 由于创建v1和v3视图时没有添加检查选项所以不会检查id是否满足id15和id20createviewv3asselectid,namefromv2whereid20;视图的更新要使视图可更新视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项则该视图不可更新1、聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等2、DISTINCT3、GROUP BY4、HAVING5、UNION 或者 UNION ALL作用简单视图不仅可以简化用户对数据的理解也可以简化他们的操作。那些被经常使用的查询可以被定义为视图从而使得用户不必为以后的操作每次指定全部的条件。安全数据库可以授权但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。存储过程介绍存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。存储过程思想上很简单就是数据库SQL语言层面的代码封装与重用。特点封装复用可以接收参数也可以返回数据减少网络交互效率提升创建CREATEPROCEDURE存储过程名称([参数列表])BEGIN-- SQL语句END;调用CALL名称([参数]);查看# 查询指定数据库的存储过程及状态信息SELECT*FROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_SCHEMAxxx;# 查询某个存储过程的定义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中配置。用户自定义变量用户定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用“变量名”使用就可以。其作用域为当前连接。赋值SETvar_nameexpr[,var_nameexpr,......];SETvar_name:expr[,var_name:expr,......];SELECTvar_name:expr[,var_name:expr,......];SELECT字段名INTOvar_nameFROM表名;补充赋值既可以使用也可以使用:但是推荐使用:因为在MySQL服务器中也用作等值比较赋值操作使用:可以与等值比较操作进行区分。使用SELECTvar_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;whilewhile循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。具体语法为#先判定条件如果条件为true则执行逻辑否则不执行逻辑WHILE条件DOSQL逻辑...ENDWHILE;repeatrepeat是有条件的循环控制语句当满足条件的时候退出循环。具体语法为#先执行一次逻辑然后判定逻辑是否满足如果满足则退出。如果不满足则继续下一次循环REPEATSQL逻辑...UNTIL 条件ENDREPEAT;loopLOOP实现简单的循环如果不在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代码的简写-- 示例declareexithandlerforSQLSTATE02000closeU_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语句创建的触发器为例
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,namefromstudentwhereid20;# 往v2插入数据时会检查id是否满足10id20只有符合条件的数据才允许插入createviewv2asselectid,namefromv1whereid10withcascadedcheckoption;# 往v3插入数据时会检查id是否满足10id20只有符合条件的数据才允许插入# 由于创建v3视图时没有添加检查选项所以不会检查id是否满足id15createviewv3asselectid,namefromv2whereid15;LOCAL# 往v1插入数据时不会检查idcreateviewv1asselectid,namefromstudentwhereid15;# 往v2插入数据时会检查id是否满足id10只有符合条件的数据才允许插入# 由于v1在创建时没有添加检查选项所以不会检查id是否满足id15createviewv2asselectid,namefromv1whereid10withlocalcheckoption;# 往v3插入数据时会检查id是否满足id10只有符合条件的数据才允许插入# 由于创建v1和v3视图时没有添加检查选项所以不会检查id是否满足id15和id20createviewv3asselectid,namefromv2whereid20;视图的更新要使视图可更新视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项则该视图不可更新1、聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等2、DISTINCT3、GROUP BY4、HAVING5、UNION 或者 UNION ALL作用简单视图不仅可以简化用户对数据的理解也可以简化他们的操作。那些被经常使用的查询可以被定义为视图从而使得用户不必为以后的操作每次指定全部的条件。安全数据库可以授权但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。存储过程介绍存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。存储过程思想上很简单就是数据库SQL语言层面的代码封装与重用。特点封装复用可以接收参数也可以返回数据减少网络交互效率提升创建CREATEPROCEDURE存储过程名称([参数列表])BEGIN-- SQL语句END;调用CALL名称([参数]);查看# 查询指定数据库的存储过程及状态信息SELECT*FROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_SCHEMAxxx;# 查询某个存储过程的定义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中配置。用户自定义变量用户定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用“变量名”使用就可以。其作用域为当前连接。赋值SETvar_nameexpr[,var_nameexpr,......];SETvar_name:expr[,var_name:expr,......];SELECTvar_name:expr[,var_name:expr,......];SELECT字段名INTOvar_nameFROM表名;补充赋值既可以使用也可以使用:但是推荐使用:因为在MySQL服务器中也用作等值比较赋值操作使用:可以与等值比较操作进行区分。使用SELECTvar_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;whilewhile循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。具体语法为#先判定条件如果条件为true则执行逻辑否则不执行逻辑WHILE条件DOSQL逻辑...ENDWHILE;repeatrepeat是有条件的循环控制语句当满足条件的时候退出循环。具体语法为#先执行一次逻辑然后判定逻辑是否满足如果满足则退出。如果不满足则继续下一次循环REPEATSQL逻辑...UNTIL 条件ENDREPEAT;loopLOOP实现简单的循环如果不在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代码的简写-- 示例declareexithandlerforSQLSTATE02000closeU_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语句创建的触发器为例
