MySQL进阶教程（一）—— 储存过程与触发器（上）

储存过程与触发器前言一、准备工作一SQL语句分类型二Navicat工具二、存储过程一定义与特性二相关语法1、创建2、调用3、查看4、删除三、变量一系统变量1、查看环境变量2、设置环境变量二用户自定义变量1、赋值三局部变量1、声明2、赋值结语前言在完成了我们的MySQL的基础教程后对数据库与表结构的基础操作有了基础概念后我们继续对MySQL的特性进行学习。本次教程将聚焦与SQL语句来进行介绍本期要介绍的是储存过程与触发器。一、准备工作一SQL语句分类型在正式开始内容前我们需要回顾一下SQL语句的分类我们才能具体知道存储过程和触发器到底是一个怎么样的概念。其中SQL语句可分为以下几类。DDL【data definition language】 数据定义语言用来维护存储数据的结构代表指令: create, drop, alterDML【data manipulation language】 数据操纵语言用来对数据进行操作代表指令 insertdeleteupdateDML中又单独分了一个DQL数据查询语言代表指令 selectDCL【Data Control Language】 数据控制语言主要负责权限管理和事务代表指令 grantrevokecommit二Navicat工具本次课程SQL语句的编写将借助Navicat这个工具有兴趣的读者可以了解一下该工具的使用。Navicat 是一款专业的图形化数据库管理和开发工具。它的核心价值在于用一个直观的图形界面替代了复杂的命令行操作让我们更方便的连接与管理各类数据库。在这里我们给出Navicat免费版本的Navicat Premium Lite的官网下载链接有需要的友友可用。点此处去下载二、存储过程一定义与特性存储过程是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字和参数来执行并获取相应的结果。其具有封装性、可维护性、可重复性的特点。与用应用程序直接操作数据库的不同点可以用下图进行解释。可见其相当与一个经编译后存储在数据库里的媒介替代应用程序去完成操作数据库的任务。除了以上特性外存储过程还有着以下优缺点。优点性能优化存储过程在创建时编译并存储在数据库中执行速度比单个SQL语句快。代码重用存储过程可以重复调用减少重复代码提高代码的可维护性。安全性可以限制用户直接访问数据库通过存储过程间接访问从而保证系统安全性。事务管理可以在存储过程中实现复杂的事务逻辑。降低耦合当表结构发⽣变化时只需要修改相应的存储过程应用程序的改动较小。缺点可移植性差存储过程不能跨数据库移植更换数据库时需要重新编写。调试困难只有少数数据库管理系统支持存储过程的调试开发和维护困难。不适合高并发场景在高并发场景下存储过程可能会增加数据库的压力难以维护。二相关语法1、创建-- 修改SQL语句结束标识符为 //DELIMITER//-- 创建存储过程CREATEPROCEDURE存储过程名(参数列表)BEGIN-- SQL 语句END//-- 修改SQL语句结束标识符为 ;DELIMITER;2、调用-- 调⽤存储过程CALL存储过程名(参数列表);3、查看-- 查看指定数据库中创建的存储过程SELECT*FROMinformation_schema.ROUTINESWHEREROUTINE_SCHEMA数据库名;-- 查看存储过程的定义SHOWCREATEPROCEDURE存储过程名;4、删除DROPPROCEDURE[IFEXISTS]存储过程名;下面给出使用的例子-- 计算学生们总分-- 创建存储过程DELIMITER//DROPPROCEDUREIFEXISTSp1;CREATEPROCEDUREIFNOTEXISTSp1()BEGINselectname,chinesemathenglishastotalfromexam;END//DELIMITER;-- 调用存储过程callp1();其中的 DELIMITER // 是用来告诉客户端接下来遇到 // 才认为一条完整语句结束。存储过程内部的 ; 不会被客户端当作语句结束符。其只在使用 mysql 命令行或支持该命令的客户端时需要例如 mysql CLI、Navicat 的查询窗口。三、变量在MySQL中变量主要分为系统变量、用户自定义变量和局部变量。一系统变量系统变量是MySQL服务器的配置变量控制着服务器的行为和性能。分为全局变量(GLOBAL)和会话变量(SESSION) 。1、查看环境变量-- 查看所有系统变量SHOW[GLOBAL|SESSION]VARIABLES;-- 查看指定的系统变量SHOW[GLOBAL|SESSION]VARIABLESLIKExxx;-- 查看指定的系统变量可以通过LKIE进⾏模糊查询SHOW[GLOBAL|SESSION]VARIABLESlike%xxx%;-- 使⽤SELECT查看指定系统变量SELECT[GLOBAL|SESSION].系统变量名;2、设置环境变量SET[GLOBAL|SESSION]系统变量名值;SETSESSION.系统变量名值;值得注意的是如果在设定时没有指定GLOBAL|SESSION 则默认设置为会话SESSION。当会话关闭后所设置的会话变量会失效新建会话时读取全局系统变量来作为初始值。当MySQL重启后设置的全局变量就会失效想要自己设置的全局变量永久生效需要去修改对应的配置文件。二用户自定义变量用户自定义变量是在SQL会话中定义的变量不用提前声明其作用域为当前会话。1、赋值-- ⽅式⼀SETvar_nameexpr[,var_name]...;-- ⽅式⼆ 【推荐】SETvar_name:expr[,var_name]...;-- ⽅式三在SELECT语句中SELECTvar_name:expr[,var_name]...;-- ⽅式四查询结果赋值给⾃定义变量SELECT列名INTOvar_nameFROM表名WHERE...;因为由于SQL中比较比较相等也是使用等号 ()所以在为变量赋值的时候推荐使用 ( : ) 。举一个例子-- ⽰例定义⼀个age变量并赋值为18并查看SETage:18;SELECTage;-- ⽰例从学⽣表中查询编号为1的学⽣学号并赋值给sno变量SELECTsnoINTOsnofromstudentwhereid1;SELECTsno;-- ⽰例查询学⽣表中的总记录数并赋值给conut变量SELECTcount(*)INTOcountfromstudent;SELECTcount;-- ⽰例访问⼀个未赋值的变量返回NULLSELECTvar;三局部变量局部变量只在存储过程、函数或触发器的范围内有效。需要使用 DECLARE 声明使用的作用域的范围在声明的 BEGIN … END 块内。1、声明DECLARE 变量名 变量类型 [DEFAULT 默认值] …;2、赋值-- ⽅式⼀SETvar_name值;-- ⽅式⼆ 【推荐】SETvar_name :值;-- ⽅式三查询结果赋值给⾃定义变量SELECT列名INTOvar_nameFROM表名WHERE...;具体使用的例子-- ⽰例在存储过程中定义局部变量记录学⽣表的总记录数delimiter//dropprocedureifexistsp2;createprocedureifnotexistsp2()begindeclarestu_cntintdefault0;selectcount(*)intostu_cntfromstudent;selectstu_cnt;end//delimiter;callp2();要注意的是变量名不区分大小写在存储过程和函数中局部变量必须在使用前声明。用户自定义变量在会话结束时失效而局部变量在存储过程或函数结束时失效。避免使用保留字作为变量名。结语以上便是本期关于MySQL储存过程与触发器章节的上部分内容了。如果喜欢我的内容请点赞、收藏加关注多多支持我以及欢迎各位在评论区讨论交流并指出我的不足谢谢大家。