MySQL数据库教程课件

第8章MySQL编程技术命令语句，采用的是联机交互的方式，执行的方式是每次一条。为了提高工作效率，可以将多条命令组合在一起，形成一个程序一次性执行。因为程序是可以重复使用的，能减少数据库开发人员的工作量，也能通过设定程序的权限来限制用户对程序的定义和使用，从而提高系统的安全性，MySQL中这样的程序称为过程式对象。【学习目标】理解常量和变量的概念。掌握系统内置的常用函数和流程控制语句。能够编写简单的存储过程。能够编写简单的存储函数。能够编写简单的触发器代码。8.1编程基础知识8.1编程基础知识8.1.1常量和变量1．常量常量是指在程序中可以直接引用的量，其值在程序运行期间保持不变，它的表示形式决定了其数据类型。常量可分为数值常量、字符串常量、日期时间常量、布尔常量和NULL值常量等。（1）数值常量数值常量由数字组成，可以分成整数常量和实数常量。①

整数常量是不带小数点的十进制整数。如156、-100。②

实数常量是包含小数点的数值常量。如3.14、-100.23、2.67E5（2）字符串常量字符串常量是用单引号或双引号括起来的字符序列，如″HELLO″、″数据库系统″。（3）日期和时间常量日期常量是用单引号或双引号括起来的，包括年、月、日，并按照“年-月-日”的顺序表示日期，中间的分隔符也可以使用“/”“@”“%”等特殊符号。如“2022-1-1”“2022/1/1”“2022@1@1”“2022%1%1”。时间常量包括时、分、秒、微秒，并按照“时:分:秒:微秒”的顺序构成，如“10:20:35:45”。（4）布尔常量布尔常量只有TURE和FALSE两个值。TURE对应的数值为“1”；FALSE对应的数值为“0”。（5）NULL值NULL值适用于各种类型，表示没有值或无数据，不等价于空字符串或数据0值。2.变量变量是指在程序运行期间取值可以变化的量，用于临时存储数据，变量中的数据随着程序的运行而变化。一个变量有2个基本要素：变量名和变量的数据类型。每个变量都用唯一的变量名来标识，用户可以通过变量名来访问内存中的数据，变量的数据类型决定了变量的值和对应的运算。MySQL变量可分为用户变量、系统变量和局部变量。（1）用户变量用户使用变量定义语句定义的变量称为用户变量。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有用户变量将自动释放。①SET语句用户变量在使用前必须定义和初始化，使用SET语句定义和初始化用户变量的语法格式：SET@用户变量1=表达式1[,@用户变量2=表达式2

];说明：在用户变量前添加@符号，便于区分变量名和字段名称。用户变量名可以包含字母、数字、“.”“_”和“$”。定义多个用户变量时，每个用户变量之间用逗号分隔。表达式是赋给用户变量的值，可以是常量、变量或表达式。【例8-1】使用用户变量查询“李华”同学所在的院系名称。第一步：在学生表student中查询到“李华”同学所在的院系代码deptno字段，并存储在用户变量@deptno中。mysql>SET@deptno=(SELECTdeptnoFROMstudentWHEREsname="李华");显示用户变量@deptno的值。mysql>SELECT@deptno;+---------+|@deptno|+---------+|104|+---------+第二步：在院系表department中查询等于用户变量@deptno的院系代码deptno字段和院系名称deptname字段。最终查询到“李华”院系名称是“电气与电子工程学院”。mysql>SELECTdeptno,deptnameFROMdepartmentWHEREdeptno=@deptno;+--------+--------------------+|deptno|deptname|+--------+--------------------+|104|电气与电子工程学院|+--------+--------------------+②SELECT

INTO语句SELECT

INTO语句可以将查询得到的一行结果中的字段值赋值给对应的用户变量语法格式：SELECT字段1[,字段2

]INTO@用户变量1[,@用户变量2

]FROM表名WHERE条件;【例8-2】将学号sno为“120211040101”的姓名sname和入学总分enterscore存入用户变量@name和@score中。定义用户变量。mysql>SET@name=NULL,@score=NULL;将查询结果赋值给用户变量。mysql>SELECTsname,enterscoreINTO@name,@score->FROMstudent->WHEREsno="120211040101";显示用户变量@name和@score的值。mysql>SELECT@name,@score;+--------+--------+|@name|@score|+--------+--------+|王晓红|630|+--------+--------+（2）系统变量系统变量是MySQL的一些特殊设置，当MySQL数据库服务器启动时，初始化这些变量为默认值。大多数系统变量名称前都需要加两个@，某些特定的系统变量不加这两个@，如CURRENT_DATE（当前系统日期）、CURRENT_TIME（当前系统时间）、CURRENT_USER（当前用户名称）等。【例8-3】查看当前系统日期和使用的MySQL的版本信息。mysql>SELECTCURRENT_DATEAS当前日期,@@versionAS当前版本;+------------+----------+|当前日期|当前版本|+------------+----------+|2022-05-30|8.0.30|+------------+----------+（3）局部变量局部变量的作用范围是BEGIN

END语句块中，用来存放存储过程体中的临时结果。局部变量只能定义在存储过程、存储函数和触发器中。局部变量和用户变量的主要区别：作用范围不同，用户变量存在于整个会话中，局部变量只存在于BEGIN

END语句块中。用户变量前有@符号，局部变量前没有@符号。例如，在存储过程中，声明局部变量vscore和n，数据类型为INT；声明局部变量vstr和vname，数据类型为CHAR。DECLAREvscore,nINT;DECLAREvstr,vnameCHAR;②

局部变量赋值可以使用SET语句或SELECT

INTO语句为局部变量赋值。例如，使用SET语句为局部变量n和vstr赋值。SETn=86,vstr=”good”；例如，使用SELECT

INTO语句为局部变量vname和vscore赋值。SELECTsname,enterscoreINTOvname,vscoreFROMstudentWHEREsno="120211040101";8.1.2系统内置函数在MySQL中提供了许多内置的标准函数，每个标准函数可以实现某个特定的功能，方便用户使用。函数的调用格式如下。函数名([参数1[,参数2

]])说明：（1）参数可以是常量、变量或表达式。（2）函数可以没有参数，也可以有一个或多个参数，多个参数之间用逗号进行分隔。

（3）调用函数后，得到一个函数的返回值。函数名和括号之间不能有空格；没有参数的函数也不能省略括号()。1．数学函数数学函数完成数学计算功能函数函数功能示例返回结果ABS(n)返回数值表达式n的绝对值ABS(-2.5)2.5ROUND(n,m)返回按照指定的小数位数m对n值四舍五入的结果ROUND(12.38,1)12.4TRUNCATE(n,m)返回按照指定的小数位数m对n值截取的结果TRUNCATE(12.38,1)12.3SQRT(n)返回数值表达式的平方根SQRT(9)3【例8-4】统计学生表student中男女生入学总分的平均值（保留2位小数）。mysql>SELECTsexAS性别,ROUND(AVG(enterscore),2)AS平均值

->FROMstudent

->GROUPBYsex;+------+--------+|性别|平均值|+------+--------+|男|640.38||女|613.43|+------+--------+2．字符串函数字符串函数用来处理字符串型变量或字符串表达式。函数函数功能示例返回结果ASCII(c)返回字符串c最左边字符的ASCII码ASCII("A")65CHAR(n)将数值n转换成字符CHAR(65)ACONCAT(c1,c2

)将多个字符串进行连接CONCAT("AB","XYZ")"ABXYZ"LENGTH(c)求字符串c的长度LENGTH("ABCD")4LEFT(c,n)取字符串c左边的n个字符LEFT("ABCD",3)"ABC"RIGHT(c,n)取字符串c右边的n个字符RIGHT("ABCD",3)"BCD"SUBSTRING(c,n1[,n2])取子字符串，在c串中从n1位置开始取n2个字符。缺省n2时，从n1位置开始取到串尾SUBSTRING(“ABCDE”,2,3)"BCD"REPLACE(c1,c2,c3)用字符串c3替换c1中出现的所有字符串c2，返回替换后的字符串。REPLACE("ABCDABE","AB","12")"12CD12E"LTRIM(c)去掉字符串c中左边的空格LTRIM("ABCD")"ABCD"RTRIM(c)去掉字符串c中右边的空格RTRIM("ABCD")"ABCD"TRIM(c)去掉字符串c中左右两边的空格TRIM("ABCD")"ABCD"LOWER(c)将字符串c转换为小写字符LOWER(("AB")“ab”UPPER(c)将字符串c转换为大写字符UPPER(“ab”)“AB”【例8-5】查询学生表student中姓“李”的学号sno、姓名sname、性别sex和入学总分enterscore。mysql>SELECTsno,sname,sex,enterscoreFROMstudent

->WHERELEFT(sname,1)="李";+--------------+--------+------+------------+|sno|sname|sex|enterscore|+--------------+--------+------+------------+|120211010230|李媛媛|女|596||120211040108|李明|男|650||120211041102|李华|女|648||120211070101|李淑子|女|589|+--------------+--------+------+------------+【例8-6】将教师表teacher中姓名tname字段分成姓和名字两列显示。mysql>SELECTLEFT(tname,1)AS姓,SUBSTRING(tname,2,LENGTH(tname)-1)AS名字->FROMteacher;+------+------+|姓|名字|+------+------+|周|家罗||孟|凯彦||宋|江科||朱|军||李|亚明||杨|丽||林|达||王|平||赵|晓丽||马|丽|+------+------+3．日期和时间函数日期和时间函数用于处理日期和时间型表达式或变量函数函数功能示例返回结果CURDATE()返回系统当前日期CURDATE()2022-8-12CURTIME()返回系统当前时间CURTIME()11:23:58NOW()返回系统当前日期和时间NOW()2022-8-1211:23:58YEAR(d)返回日期表达式d的年份YEAR("2022-8-12")2022MONTH(d）返回日期表达式d的月份MONTH("2022-8-12")8DAY(d)返回日期表达式d的天数DAY("2022-8-12")12DATEDIFF(d1,d2)返回两个日期之间的天数DATEDIFF("2022-8-16","2022-8-10")6【例8-7】查询学生表student中学生的年龄。“年龄”通过计算表达式“YEAR(NOW())-YEAR(birthdate)”获得，使用AS指定列标题为“年龄”。mysql>SELECTsnoAS学号,snameAS姓名,YEAR(NOW())-YEAR(birthdate)AS年龄->FROMstudent;+--------------+--------+------+|学号|姓名|年龄|+--------------+--------+------+|120211010103|宋洪博|19||120211010105|刘向志|20||120211010230|李媛媛|19||120211030110|王琦|19||120211030409|张虎|19||120211040101|王晓红|20||120211040108|李明|20||120211041102|李华|19||120211041129|侯明斌|20||120211050101|张函|19||120211050102|唐明卿|20||120211060104|王刚|18||120211060206|赵壮|19||120211070101|李淑子|19||120211070106|刘丽|20|+--------------+--------+------+TRUNCATE(DATEDIFF(NOW(),birthdate)/365,0)4．其他函数函

数函

数

功

能示

例返回结果IF(expr,v1,v2)判断条件表达式expr的值，如果为真则返回v1的值；否则返回v2的值IF(5>0,’是’,’否’)是IFNULL(v1,v2)如果v1的值不为空则返回v1的值；否则返回v2的值IFNULL(5,2)5VERSION()返回当前数据库的版本号VERSION()8.0.30【例8-8】判断5*2是否大于3*3，如果是，则返回“是”，否则返回“否”。mysql>SELECTIF(5*2>3*3,'是','否');+-----------------------+|IF(5*2>3*3,'是','否')|+-----------------------+|是|+-----------------------+8.1.3流程控制语句程序是按照一定的结构来控制整个流程的，流程控制语句是用来控制程序执行顺序的语句MySQL的流程控制语句和局部变量一样只能放在存储过程、存储函数或触发器中来控制程序的执行流程，不能单独执行流程控制语句有：顺序语句分支语句循环语句1.顺序语句（1）BEGIN

END语句块。使用BEGIN

END定义语句块，语句块中可以包含一组语句，语句可以嵌套。关键字BEGIN定义语句的起始位置，END定义同一语句块的结束位置。语法结构：BEGIN语句序列；END例如：BEGINSELECTsno,sname,sexFROMstudent;END;（2）DELIMITER命令在BEGIN

END语句块中可能包含多个SQL语句，SQL语句是以分号为结束标志。MySQL服务器处理程序时，分号是默认的结束标志。系统处理到第一个分号时就认为程序结束了，导致后序的SQL语句不能执行，此时需要使用DELIMITER命令将MySQL语句的结束标志修改为其他符号，这样就可以连续执行多个SQL语句了语法结构：DELIMITER结束符用户可以使用“$$”“##”等特殊的符号为结束符，注意避免使用MySQL中的转义字符“\”。例如，将MySQL语句的结束标志修改为“$$”。可以使用如下语句。DELIMITER$$这条语句执行后，结束符就变成了“$$”，接下来的语句必须使用“$$”结束。例如，SELECTsno,sname,sexFROMstudentWHEREsex=”男”$$DELIMITER$$BEGINSELECTsno,sname,sexFROMstudentWHEREsex=”男”;SELECTsno,sname,sexFROMstudentWHEREsex=”女”;END$$DELIMITER;如果想恢复使用分号“;”作为结束符，则需要执行如下的语句。DELIMITER;2．分支语句（1）IF语句IF-THEN-ELSE语句可以根据不同的条件执行不同的操作语法格式：IF条件1THEN语句序列1[ELSEIF条件2THEN语句序列2]

[ELSE语句序列n]ENDIF;IF语句执行流程先计算条件的值。当某个条件值为真（TRUE）时，则执行相应的语句序列；如果没有一个条件值为真，则执行ELSE中的语句序列n。【例8-9】查询课程表course中“数据库应用”课程的学时数hours，如果查询结果为空，则显示“无学时数信息”，否则显示学时数。DECLAREvhoursINT;SELECThoursINTOvhoursFROMcourseWHEREcname=”数据库应用”IFvhoursISNULLTHENSELECT“无学时数信息”AS学时数;ELSESELECTvhoursAS学时数;ENDIF;（2）CASE语句CASE是另一种分支语句，有两种语法格式。CASE语句格式一：CASE表达式WHEN值1THEN语句序列1[WHEN值2THEN语句序列2]

[ELSE语句序列n]ENDCASE;CASE语句格式一执行流程如下。先计算出表达式的值然后与WHEN

THEN语句块中的值进行比较如果某个值比较的结果为真，则执行对应语句序列中的语句如果每一个语句块中的值都不匹配，则执行ELSE语句序列n中的语句CASE语句格式二如下。CASEWHEN条件1THEN语句序列1[WHEN条件2THEN语句序列2]

[ELSE语句序列n]ENDCASE;CASE语句格式二执行流程如下。CASE关键字后没有参数。在WHEN

THEN语句块中指定一个条件如果条件的结果为真时，则执行对应语句序列中的语句如果每一个语句块中的条件表达式均不为真，则执行ELSE语句序列n中的语句【例8-10】查询学生表student中“刘向志”所属的级存入变量中。班级classno字段中的第3、4位表示是级的信息。例如，“电气2101班”对应的级为“2021级”。DECLAREvclassCHAR(2);DECLAREvclassyearCHAR(5);SELECTSUBSTRING(classno,3,2)INTOvclassFROMstudentWHEREsname=”刘向志”;CASEvclassWHEN“21”THENSETvclassyear=”2021级”WHEN“20”THENSETvclassyear=”2020级”WHEN“19”THENSETvclassyear=”2019级”WHEN“18”THENSETvclassyear=”2018级”ENDCASE;3.循环语句循环语句有三种形式，分别为：

WHILE语句REPEAT语句LOOP语句（1）WHILE语句语法格式：WHILE条件DO

语句序列ENDWHILE;WHILE语句执行流程如下。首先判断条件是否成立如果条件成立，则执行语句序列然后再次判断条件是否成立，如果条件成立则继续循环，否则结束循环。【例8-11】计算1+2+3+

+100的和。DECLAREnINTDEFAULT1;DECLAREsumINTDEFAULT0;WHILEn<=100DOSETsum=sum+n;SETn=n+1;ENDWHILE;

变量

sum必须使用DEFAULT0

赋初值为0，否则其初值默认为NULL，最终得到的结果为NULL，不能得到正确的结果。（2）REPEAT语句语法格式如下。REPEAT语句序列UNTIL条件ENDREPEAT;REPEAT语句执行流程如下。首先执行语句序列，然后判断条件是否成立，如果条件不成立则继续循环，否则结束循环。REPEAT语句是“先执行，后判断”，循环体至少被执行一次；而WHILE语句是“先判断，后执行”，循环体可能一次也不被执行。【例8-12】计算2+4+6+

+100的和。DECLAREnINTDEFAULT2;DECLAREsumINTDEFAULT0;REPEATSETsum=sum+n;SETn=n+2;UNTILn>100ENDREPEAT;（3）LOOP语句语法格式为：[语句标号:]LOOP语句序列ENDLOOP[语句标号];LOOP语句执行流程如下。重复执行语句序列，语句序列中通常存在一个LEAVE语句，执行到该语句时退出循环。其中的语句标号是用户自定义的名称。①

退出循环语句LEAVE的语法格式如下。LEAVE语句标号;其中，语句标号是LOOP语句中自定义的名称。执行到该语句时结束循环。②

再次循环语句ITERATE的语法格式如下。ITERATE语句标号;ITERATE语句只能出现在WHILE、REPEAT和LOOP语句中，结束本次的循环，然后开始下一次的循环。【例8-13】计算5的阶乘。DECLAREn,fINTDEFAULT1;label1:LOOPSETf=f*n;SETn=n+1;IFn>5THENLEAVElabel1;ENDIF;ENDLOOPlabel1;8.2存储过程存储过程是在数据库中定义的SQL语句的集合，经过编译后存储在数据库中，用户通过指定存储过程的名称并给出需要的参数来调用执行存储过程中的语句。用户可以将经常需要执行的特定操作写成存储过程，每次需要时调用该存储过程，可以实现一个存储过程的多次调用，实现了程序的模块化设计。因为存储过程是预编译的，可以加快执行速度。存储过程由声明式SQL语句（如SELECT、INSERT、UPDATE等语句）和过程式SQL语句（如IF-THEN-ELSE等流程控制语句）组成，完成对较为复杂问题的处理。8.2.1创建存储过程使用CREATEPROCEDURE语句可以创建存储过程语法格式：CREATEPROCEDURE存储过程名（[参数1[,参数2

]]）[特征]存储过程体

说明

（1）存储过程名：用户自定义的存储过程名称。（2）参数：存储过程中的参数是形式参数，简称形参

调用存储过程使用的参数是实际参数，简称实参。

形式参数有输入参数IN、输出参数OUT、输入输出参数INOUT共3种参数。

形式为：[IN

OUT

INOUT]参数名类型。

①

IN：将实参的值传递给形参，作为存储过程的输入值。

②

OUT：是存储过程的输出值，结束时将形参的结果值传递给实参。

③

INOUT：既是输入值也是输出值，调用存储过程时将实参传递给形参，存储过程结束后将形参传递给实参。存储过程可以有0个或多个参数，没有参数时存储过程名后的括号()必须保留；当有多个参数时，各个参数之间用逗号分隔。（3）存储过程体：调用存储过程时将要执行的SQL语句，这部分总是以BEGIN开始，END为结束。当存储过程体中只有一条SQL语句时，可以省略BEGIN

END语句。（4）特征的格式如下。

LANGUAGESQL

[NOT]DETERMINISTIC

{CONTAINSSQL

NOSQL

READSSQLDATA

MODIFIESSQLDATA}

SQLSECURITY{DEFINER

INVOKER}

COMMENT‘STRING’①LANGUAGESQL：说明存储过程体部分由SQL语句组成。②[NOT]DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的，不同用户输入相同的数据会得到相同的输出结果。NOTDETERMINISTIC表示结果是非确定的，不同用户输入相同的数据可能会得到不同的输出结果。③{CONTAINSSQL

NOSQL

READSSQLDATA

MODIFIESSQLDATA}：指明子程序使用SQL语句的限制。CONTAINSSQL表示子程序中包含SQL语句，但不包含读或写数据的语句；NOSQL表示子程序中不包含SQL语句；READSSQLDATA表示子程序中包含读数据的语句；MODIFIESSQLDATA表示子程序中包含写数据的语句；CONTAINSSQL是默认值。④SQLSECURITY{DEFINER

INVOKER}：指明执行的权限。DEFINER表示只有定义者才能执行，是默认值；INVOKER表示调用者可以执行。⑤COMMENT‘STRING’：注释信息，用来描述存储过程的功能。8.2.2调用存储过程创建存储过程后，可以在程序、触发器、其他存储过程中使用CALL语句调用。其语法格式如下。CALL存储过程名([参数1[,参数2

]]);说明：（1）参数是调用存储过程使用的实在参数，简称实参。（2）实参的个数必须与存储过程定义的形参个数相同。【例8-14】创建无参数的存储过程p_count，实现统计学生表student中的学生人数。创建存储过程。CREATEPROCEDUREp_count()SELECTCOUNT(sno)AS学生人数FROMstudent;调用这个存储过程。mysql>CALLp_count();+----------+|学生人数|+----------+|15|+----------+【例8-15】创建带输入参数的存储过程p_countsex，实现统计学生表student中指定性别的学生人数。创建存储过程，该存储过程包含了一个输入参数。CREATEPROCEDUREp_countsex(INvsexCHAR(1))SELECTCount(sno)AS人数FROMstudentWHEREsex=vsex;调用这个存储过程，将实参“女”传递给形参vsex。CALLp_countsex(“女”);+----------+|人数|+----------+|7|+----------+【例8-16】创建带输入和输出参数的存储过程p_avggrade，实现统计某门课程的平均成绩是否达到预期效果，如果大于等于80，则显示“该课程成绩达到预期效果”，否则显示“该课程成绩没有达到预期效果”。DELIMITER$$CREATEPROCEDUREp_avggrade(INvcnameCHAR(50),OUTevaluateCHAR(50))BEGINDECLAREvavgFLOAT;SELECTAVG(grade)INTOvavgFROMcourseJOINscoreONo=oJOINstudentONstudent.sno=score.snoWHEREcname=vcname;IFvavg>=80THENSETevaluate="该课程成绩达到预期效果";ELSESETevaluate="该课程成绩没有达到预期效果";ENDIF;END$$DELIMITER;因为其中有多条语句，需要先用DELIMITER语句将语句的结束标识转换为“$$”，然后在BEGIN和END之间编写存储过程体，最后用DELIMITER语句将语句的结束标识恢复为分号“;”。在MySQL命令行创建存储过程。调用这个存储过程时使用用户变量@eval作为实参得到输出的结果，实参必须是用户变量才能查询到结果，如果定义为局部变量，存储过程执行后，不能查询到该局部变量的结果。调用该存储过程统计“数据库应用”的平均成绩，在MySQL命令行输入如下命令语句，执行结果如下。mysql>CALLp_avggrade(“数据库应用”,@eval);该存储过程的输出结果保存在用户变量@eval中，通过SELECT语句查看结果。mysql>SELECT@eval;显示用户变量@eval的结果如下。+------------------------+|@eval|+------------------------+|该课程成绩达到预期效果|+------------------------+8.2.3删除存储过程当存储过程不再需要时，为了释放其所占用的存储空间，可以使用DROPPROCEDURE语句删除。语法格式：DROPPROCEDURE[IFEXISTS]存储过程名;说明：（1）存储过程名：需要删除的存储过程名称。（2）IFEXISTS：可选项，检测指定的存储过程名是否存在，存在时才执行删除。可以避免由于存储过程名不存在而引起的错误。【例8-17】删除存储过程p-count。mysql>DROPPROCEDUREp_count;8.2.4使用游标游标能从结果集中每次提取一条记录进行处理，游标类似指针，一次指向一条记录行，能够遍历结果集的全部记录。游标不能单独在查询中使用，一定要在存储过程或存储函数中使用。1．声明游标使用游标之前首先要声明游标，定义SELECT语句的结果集语法格式：DECLARE游标名CURSORFORSELECT语句;

说明

（1）使用SELECT语句查询出来的结果构成结果集。（2）游标声明时，定义的SELECT语句还没有执行，没有结果集。因此声明游标之后必须打开游标。2．打开游标打开游标即执行与之对应的SELECT语句，得到结果集语法格式：OPEN游标名;3．提取数据提取数据可以获取游标所指向的结果集中的当前记录，并将各个字段值传送给一组对应的变量。变量的个数必须与SELECT语句返回的字段个数一致语法格式：FETCH游标名INTO变量列表;

说明

（1）FETCH语句每次执行只能从结果集中提取一条记录。（2）如果需要逐条提取全部结果集中的记录，必须将FETCH语句放置在循环语句中。4．关闭游标游标使用结束后需要及时关闭，这样可以释放游标所占用的内存空间语法格式：CLOSE游标名;5．游标错误处理程序使用FETCH语句提取结果集中的最后一条记录后，再执行FETCH语句会产生错误，其提示信息为“ERROR1329(02000):nodatatoFETCH”，所以需要游标错误处理程序语法格式：DECLARE错误处理类型HANDLEFORNOTFOUND错误处理程序;

说明

（1）错误处理程序：表示发生错误后，MySQL会立即执行错误处理程序中的SQL语句。（2）错误处理类型：类型值为CONTINUE和EXIT。CONTINUE表示错误发生后，MySQL会立即执行错误处理程序，然后忽略该错误继续执行其他MySQL语句。EXIT表示错误发生后，MySQL会立即执行错误处理程序，并且立刻停止执行其他MySQL语句。声明游标打开游标提取数据是最后一条记录吗？关闭游标是否计算数据开始结束游标的流程示意图【例8-18】创建一个包含游标的存储过程p_award，根据入学总分显示奖励的等级，680分及以上显示“特等奖”，660~679显示“优秀奖”，其他分数段不显示，最后显示“特等奖”和“优秀奖”的人数。

分析：可以用游标取得包含学号sno,、姓名sname,、入学总分enterscore这3个字段的结果集，然后逐一从结果集中取出当前记录的3个字段值存入对应的3个变量中，用条件判断语句按照设定的条件执行不同的操作，并分别累加获奖学生人数，结果集中的记录全部取出后，显示最终的“特等奖”和“优秀奖”的人数。DELIMITER$$CREATEPROCEDUREp_award()BEGINDECLAREdoneINTDEFAULT0;DECLAREvnoCHAR(12);DECLAREvname,vawardCHAR(50);DECLAREvscore,n1,n2INT;DECLAREscorecursorCURSORFORSELECTsno,sname,enterscoreFROMstudent;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;SETn1=0,n2=0;OPENscorecursor;REPEATFETCHscorecursorINTOvno,vname,vscore;IFdone=0THENIFvscore>=680THENSETvaward="特等奖";SETn1=n1+1;SELECTvnoAS学号,vnameAS姓名,vawardAS奖励等级;ELSEIFvscore>=660THENSETvaward="优秀奖";SETn2=n2+1;SELECTvnoAS学号,vnameAS姓名,vawardAS奖励等级;ELSESETvaward=NULL;ENDIF;ENDIF;UNTILdone=1ENDREPEAT;CLOSEscorecursor;SELECTn1AS特等奖人数,n2AS优秀奖人数;END$$DELIMITER;调用这个存储过程。mysql>CALLp_award();+--------------+--------+----------+|学号|姓名|奖励等级|+--------------+--------+----------+|120211010103|宋洪博|特等奖|+--------------+--------+----------+1rowinset(0.00sec)+--------------+------+----------+|学号|姓名|奖励等级|+--------------+------+----------+|120211050101|张函|优秀奖|+--------------+------+----------+1rowinset(0.01sec)+--------------+------+----------+|学号|姓名|奖励等级|+--------------+------+----------+|120211060104|王刚|优秀奖|+--------------+------+----------+1rowinset(0.01sec)+------------+------------+|特等奖人数|优秀奖人数|+------------+------------+|1|2|+------------+------------+【例8-19】创建一个包含游标的存储过程p_avgage，计算学生表student中学生的平均年龄。

分析：学生表student中只有出生日期birthdate字段，可以用游标取得包含全部学生的出生日期birthdate字段结果集，然后逐一从结果集中取出记录，分别用日期函数计算出每个学生的年龄进行累加、并统计学生人数，结果集中的记录全部取出后，用年龄的累加和除以学生人数即可得到平均年龄。DELIMITER$$CREATEPROCEDUREp_avgage()BEGINDECLAREdoneINTdefault0;DECLAREVAGEdate;DECLAREvavgageFLOATDEFAULT0;DECLAREnINTDEFAULT0;DECLAREagecursorCURSORFORSELECTbirthdateFROMstudent;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENagecursor;REPEATFETCHagecursorINTOvage;IFdone=0THENSETvavgage=vavgage+Year(NOW())-Year(vage);SETn=n+1;ENDIF;UNTILdone=1ENDREPEAT;SETvavgage=vavgage/n;SELECTvavgageAS学生平均年龄;CLOSEagecursor;END$$DELIMITER;调用这个存储过程。mysql>CALLp_avgage();+--------------+|学生平均年龄|+--------------+|19.3333|+--------------+8.3存储函数MySQL的存储函数与存储过程的作用和格式有许多类似之处，都是由声明式SQL语句和过程式SQL语句组成的主要区别：（1）存储函数有返回值，所以没有输出参数。（2）存储函数不能使用CALL语句来调用，它的调用类似于系统的内置函数，放在表达式、赋值语句中实现调用。（3）存储函数必须包含一条RETURN语句。8.3.1创建存储函数使用CREATEFUNCTION语句可以创建存储函数语法格式：CREATEFUNCTION存储函数名([参数1[，参数2

]])RETURNS类型DETERMINISTIC存储函数体

说明

（1）存储函数名：用户自定义的存储函数名称。（2）参数：用于指定存储函数的参数，参数只有参数名和参数类型。不能为参数指定IN、OUT或INOUT的形式。（3）RETURNS子句：用于声明存储函数返回值的数据类型。（4）DETERMINISTIC：表示存储函数的结果是确定的，不同用户输入相同的数据会返回相同的结果。（5）存储过程体：存储函数体中必须包含一条RETURNvalue语句，value用于指定存储函数的返回值；在存储过程中使用的SQL语句在存储函数中也适用，包括流程控制语句、游标等，多条语句需要存放在以BEGIN开始，END为结束的结构中。8.3.2调用存储函数存储函数的调用类似于系统函数的调用，可以使用SELECT关键字。语法格式：SELECT存储函数名([参数1[，参数2

]]);【例8-20】创建无参数的存储函数f_count，实现统计学生表student中的学生人数。创建存储函数，RETURN语句将返回学生人数。DELIMITER$$CREATEFUNCTIONf_count()RETURNSINTDETERMINISTICBEGINRETURN(SELECTCOUNT(sno)FROMstudent);END$$DELIMITER;调用存储函数f_count，虽然该存储函数没有参数，但是调用时必须包含括号()。mysql>SELECTf_count();+-----------+|f_count()|+-----------+|15|+-----------+【例8-21】创建带参数的存储函数f_deptname，实现输入学生姓名sname，返回该学生所在院系名称deptname。创建存储函数，RETURN语句将返回院系名称deptname。DELIMITER$$CREATEFUNCTIONf_deptname(vsnameCHAR(50))RETURNSCHAR(50)DETERMINISTICBEGINRETURN(SELECTdeptnameFROMdepartmentJOINstudentONdepartment.deptno=student.deptnoWHEREsname=vsname);END$$DELIMITER;使用姓名为“李华”作为参数，调用存储函数f_deptname。mysql>SELECTf_deptname("李华");运行结果如下。+--------------------+|f_deptname("李华")|+--------------------+|电气与电子工程学院|+--------------------+再次使用姓名为“刘向志”作为参数，调用存储函数f_deptname。mysql>SELECTf_deptname("刘向志");运行结果如下。+----------------------+|f_deptname("刘向志")|+----------------------+|外国语学院|+----------------------+8.3.3删除存储函数当存储函数不再需要时，可以使用DROPFUNCTION语句删除语法格式DROPFUNCTION[IFEXISTS]存储函数名;说明：（1）存储函数名：需要删除的存储函数名称。（2）IFEXISTS：可选项，检测指定的存储函数名是否存在，存在时才执行删除。可以避免由于存储函数不存在而引起的错误。【例8-22】删除存储函数f_count。mysql>DROPFUNCTIONIFEXISTSf_count;8.4触发器触发器是一种特殊的存储过程，用于保护表中数据来实现数据库的数据完整性。它不需要使用CALL语句调用，也不需要直接写出触发器名来调用。当有操作会影响到触发器保护的数据时，触发器会自动激活执行，保证了数据库的数据完整性。例如，当要修改学生表student中某个学生的学号sno字段时，为了保持数据的一致性，该学生在选修成绩表score中的所有学号sno字段也要同时更新；在学生表student中删除一个学生的记录时，该学生在选修成绩表score中的所有记录也要同时删除，否则将会出现成绩没有隶属学生的数据不一致的情况。8.4.1创建触发器使用CREATETRIGGER语句可以创建触发器语法格式：CREATETRIGGER触发器名触发时间触发事件ON表名FOREACHROW触发器动作;

说明

（1）触发器名：用户自定义的触发器名称。（2）触发时间：触发器触发的时刻，有AFTER和BEFORE两个选项，分别表示触发动作是在触发事件之前执行，还是在触发事件之后执行。如果想在触发事件之后执行更多的改变操作，通常使用AFTER；如果想在触发事件之前验证新数据是否满足使用的限制，则使用BEFORE。（3）触发事件：激活触发器程序的语句包括INSERT、UPDATE和DELETE，即当在表中插入、修改或删除记录时都会激活触发器。（4）表名：与触发器相关的表名，在该表上发生触发事件才会激活触发器。（5）FOREACHROW：用于指定每一行都可以激活触发器。（6）触发器动作：触发器的主体，包含触发器激活时将要执行的语句。如果要执行多条语句，则需要放置在以BEGIN开始，END为结束的结构中。【例8-23】在课程表course中创建一个触发器t_courseadd，每当插入一条记录后，给用户变量@str赋值为“添加一门课程”。①

创建触发器t_courseadd。mysql>CREATETRIGGERt_courseaddAFTERINSERT->ONcourseFOREACHROW->SET@str="添加一门课程";②

在课程表course中插入一条新记录。mysql>INSERTINTOcourseVALUES("12345678","操作系统",64);③

查询用户变量@str，验证触发器的执行结果。mysql>SELECT@str;用户变量@str的显示结果如下。+--------------+|@str|+--------------+|添加一门课程|+--------------+8.4.2使用触发器在触发器的SQL语句中，可以使用表中的任何字段，为了避免引起系统混淆，不可以直接使用字段名称，需要使用“OLD.字段名称”或“NEW.字段名称”来标识。“OLD.字段名称”引用被修改或删除前的值“NEW.字段名称”引用新插入或修改的值。对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD是合法的；对于UPDATE语句，可以同时使用NEW和OLD。1．INSERT触发器【例8-24】在学生表student中创建一个触发器t_studentadd，每当插入一条记录后，显示所插入记录的学生的姓名。①

创建触发器t_studentadd，将新插入记录的sname值赋值给用户变量@str。mysql>CREATETRIGGERt_studentaddAFTERINSERT->ONstudentFOREACHROW->SET@str=NEW.sname;②

在学生表student中插入一条新记录。mysql>INSERTINTOstudentVALUES("120211010123","赵红","女","2002-12-23",NULL,NULL,"101",670,NULL);③

查询用户变量@str，验证触发器的执行结果。mysql>SELECT@str;用户变量@str的执行结果如下。+------+|@str|+------+|赵红|+------+2．UPDATE触发器【例8-25】

在学生表student中创建一个触发器t_sname，每当修改一条学生记录前，要保证外籍学生姓名sname字段一定是大写。①

创建触发器t_sname，修改学生表student的一条记录前，将sname字段转换为大写。mysql>CREATETRIGGERt_snameBEFOREUPDATE->ONstudentFOREACHROW->SETNEW.sname=UPPER(NEW.sname);②

将学生表student中姓名sname为“赵红”的记录的姓名修改为“mary”。mysql>UPDATEstudentSETsname="mary"WHEREsname="赵红";③

查询学生表student中该记录的修改情况，以验证触发器。mysql>SELECTsno,sname,sexFROMstudent;+--------------+--------+------+|sno|sname|sex|+--------------+--------+------+|120211010103|宋洪博|男||120211010105|刘向志|男||120211010123|MARY|女||120211010230|李媛媛|女||120211030110|王琦|男||120211030409|张虎|男||120211040101|王晓红|女||120211040108|李明|男||120211041102|李华|女||120211041129|侯明斌|男||120211050101|张函|女||120211050102|唐明卿|女||120211060104|王刚|男||120211060206|赵壮|男||120211070101|李淑子|女||120211070106|刘丽|女|+--------------+--------+------+3.DELETE触发器【例8-26】在学生表student中创建一个触发器t_studentdel，在删除一条学生记录前，将所删除的记录保存到student_archive表中。student_archive表结构与student基本相同，但增加了具有自增属性的字段id，且该字段是主键。①

创建student_archive表。mysql>CREATETABLEstudent_archive->(->idINTPRIMARYKEYAUTO_INCREMENT,->snoCHAR(12),->snameVARCHAR(50),->sexCHAR(1),->birthdateDATE,->partyVARCHAR(50),->classnoVARCHAR(20),->deptnoCHAR(3),->enterscoreINT,->awardsTEXT->);②

创建触发器t_studentdel，在删除一条学生记录前，将该记录插入到student_archive表中。mysql>CREATETRIGGERt_studentdelBEFOREDELETE->ONstudentFOREACHROW->INSERTINTOstudent_archive(sno,sname,sex,birthdate,party,classno,deptno,enterscore,awards)->VALUES(OLD.sno,OLD.sname,OLD.sex,OLD.birthdate,OLD.party,OLD.classno,OLD.deptno,OLD.enterscore,OLD.awards);③

删除student表中学号sno为“120211010123”的学生。mysql>DELETEFROMstudentWHEREsno="120211010123";④

查询student_archive表的记录，以验证触发器。mysql>SELECT*FROMstudent_archive;查询结果如下。+---+------------+-------+-----+-----------+-----+--------+------+-----------+-------+|id|sno|sname|sex|birthdate|party|classno|deptno|enterscore|awards|+---+------------+-------+-----+-----------+-----+--------+------+-----------+-------+|1|120211010123|MARY|女|2002-12-23|NULL|NULL|101|670|NULL|+---+------------+-------+-----+-----------+-----+--------+------+-----------+-------+8.4.3查看触发器（1）使用SHOWTRIGGER语句查看触发器SHOWTRIGGER语句可以查看当前数据库中的所有触发器，其语法格式如下。SHOWTRIGGERS;（2）在TRIGGERS表中查看触发器在MySQL中，所有触发器的定义都保存在information_schema数据库的triggers表中，可以使用SELECT语句查看触发器的详细信息。查看所有触发器的详细信息。SELECT*FROMinformation_schema.triggers;也可以查看指定触发器的详细信息。8.4.4删除触发器可以使用DROP语句删除触发器语法格式DROPTRIGGER[IFEXISTS][数据库名.]触发器名;如果没有指定数据库的名称，则默认为当前数据库。当删除一个表时，该表中的触发器被同时自动删除。【例8-27】删除触发器t_studentdel。mysql>DROPTRIGGERt_studentdel;8.5事件事件（event）是在指定时刻才被执行的过程式数据库对象。一个事件可以在事件调度器的管理下周期性的启动。事件和触发器类似，都是在某些事情发生时启动的，所以事件也被称为临时性触发器。它们的区别在于触发器是基于每张表所产生的某个操作（插入、修改、删除等）来触发的，而事件是基于特定的时间周期来触发的。事件调度器必须是开启状态才可以使用。创建事件前可以用“SELECT@@EVENT_SCHEDULER;”命令查看事件调度器的状态，ON表示开启；OFF表示关闭。如果事件调度器处于关闭状态，可以使用“SETGLOBALEVENT_SCHEDULER=1;”命令进行开启。8.5.1创建事件使用CREATEEVENT语句可以创建事件语法格式：CREATEEVENT事件名ONSCHEDULE时间调度DO触发事件;

说明

（1）事件名：用户自定义的事件名称。（2）时间调度：指定事件何时发生或每隔多久发生一次，可以有以下2个取值。①AT时间点[+INTERVAL时间间隔]：表示在指定的时间点事件发生，如果有时间间隔，则表示在这个时间间隔后事件发生。②EVERY时间间隔[STARTS时间点[+INTERVAL时间间隔]][END时间点[+INTERVAL时间间隔]]：表示事件在指定的时间区间内，每间隔多长时间发生一次。其中，STARTS指定开始时间，END指定结束时间。（3）触发事件：包含事件激活时将要执行的语句。可以是一条SQL语句，也可以是BEGIN

END语句块或者是一个存储过程。【例8-28】创建立即执行的事件e_createtb，完成创建一张表timetb。mysql>CREATEEVENTe_createtb->ONSCHEDULEATNOW()->DO->CREATETABLEtimetb->(->noINTPRIMARYKEYAUTO_INCREMENT,->timefdTIME->);mysql>SELECT*FROMtimetb;Emptyset(0.02sec)【例8-29】创建事件e_instb，每10秒向表timetb中插入一条记录。mysql>CREATEEVENTe_instb->ONSCHEDULEEVERY10SECOND->DO->INSERTINTOtimetb(tim