MySQL数据库技术与项目应用课件 项目6 编程操作网上商城系统数据

模块概览编程操作网上商城系统数据MySQL数据库技术与项目应用06引言/FOREWORD计算机应用有科学计算、数据处理与过程控制三大主要领域。随着信息时代对数据处理的要求不断增多，数据处理在计算机应用领域中占有越来越大的比重，包括现在最流行的客户端/服务器模式（C/S）、Web模式（B/S）应用等。在网上商城系统中，为了有效地提高数据访问效率和数据安全性，网上商城系统的开发过程更加专注于业务逻辑的处理，数据库负担为系统提供数据支持的任务，把复杂逻辑的数据处理放在数据库中，即数据库编程。MySQL提供了函数、存储过程、触发器、事件等数据对象来实现复杂的数据处理逻辑。本项目在数据库编程基础上，详细介绍了MySQL中函数、存储过程、触发器、事件在数据库应用系统开发中的作用，并通过实例阐明它们的使用方法。目录/Contents0103数据库编程基础使用存储过程实现数据访问02使用存储函数实现数据访问05使用事件实现自动任务04使用触发器实现自动任务任务1数据库编程基础任何一种语言都是为了解决实际应用问题而存在的。SQL程序的流程控制及提供的系统函数能够有效解决数据库程序设计中的复杂逻辑问题。本任务在SQL程序语言基础上，详细讨论了SQL的流程控制和MySQL中常用函数的使用。任务场景SQL程序语言基础变量变量是指程序运行过程中会变化的量，MySQL支持的变量类型有3种。用户变量：这种变量用一个@字符作为前缀，在MySQL会话末端结束其定义。系统变量和服务器变量：这种变量包含了MySQL服务器的状态或属性。它们以@@字符作为前导符（例如：@@profiling）。局部变量：只用于存储过程中的变量，而且只在存储过程中有效。没有前导标识，因此定义局部变量命名时必须与数据表和数据列的名字有所区别。在MySQL8.0中变量不区分大小写，@name、@Name或@NAME都表示同一变量。SQL程序语言基础用户变量用户变量即用户定义的变量。用户变量可以被赋值，也可以在后面的其他语句中引用其值。用户变量的名称由“@”字符作为前缀标识符。用户变量使用SET命令和SELECT命令给其赋值。SET命令使用的赋值操作符是“=”或“:=”SELECT命令使用的赋值操作符只能是“:=”。【例6.1】MySQL中用户变量赋值。#语句1SET@id=10;#语句2SELECT@x1:=1,@x2:=@x1+1,@x3:=@x2+1;#语句3SELECTcnameFROMcategoryWHEREcid=1INTO@name;SELECT@name;#语句4SELECT@id:=@id+1,cnameFROMcategory;SQL程序语言基础系统变量系统变量是MySQL的一些特定的参数。当MySQL服务启动时，这些参数将被读取并配置MySQL的运行环境。系统变量使用“@@”作为前缀标识符。【例6.2】使用SHOWVARIABLES查看所有系统变量。【例6.3】设置和查看系统变量。

SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配模式'|WHERE条件表达式];SHOWVARIABLES; #查看MySQL的所有系统变量SET@@profiling=0;

#设置系统变量，关闭profiles功能SET@@event_scheduler=0;00;

#设置系统变量，开启事件调度器SELECT@@global.version;

#查看全局变量version,获知当前mysql的版本号SQL程序语言基础局部变量局部变量一般用在SQL语句块（如存储过程的BEGIN和END）中。其作用域仅限于语句块，当语句块执行完毕后，局部变量就消失了。局部变量用DECLARE来声明，可以使用DEFAULT来设置初始值。【例6.4】定义名称为proc_add的存储过程，计算参数a，b之和。DELIMITER//#修改默认提交符为“//”CREATEPROCEDUREproc_add(aint,bint)BEGINDECLAREcintDEFAULT0;#定义局部变量变c,初始值为0SETc=a+b;SELECTcAS'Result';END//#提交SQL程序语言基础常量常量是指在程序运行过程中，值不会改变的量。一个数字，一个字母或一个字符串等都可以是一个常量。字符串常量指用单引号或双引号括起来的字符序列。数值常量数值常量可以分为整数常量和浮点数常量。整数常量即不带小数点的十进制数，例如+1453等。浮点数常量是使用小数点的数值常量，例如，-5.43、1.5E6等。日期时间常量用单引号将表示日期时间的字符串括起来就是日期时间常量。例如，'2008-05-1214:26:24:00'就是一个合法的日期时间常量。布尔值常量布尔值只包含TRUE和FALSE两个值。NULL值常量NULL值适用于各种类型，它通常用来表示“没有值”、“无数据”等意义。SQL程序语言基础运算符运算符是执行数学运算、字符串连接以及列、常量和变量之间进行比较的符号算术运算符：+、-、*、／、%赋值运算符：=、:=逻辑运算符：!（NOT）、&&（AND）、||（OR）、XOR位运算符：&、^、<<、>>、～|比较运算符：=、<>（!=）、<=>、<、<=、>、>=、ISNULLSQL的流程控制语句SQL语言中同其他语言一样有顺序结构、分支结构和循环结构等流程控制语句。通过流程控制语句来控制存储函数、存储过程等语句块的执行过程，实现数据库中较为复杂的程序逻辑。常用分支语句IF…ELSE语句CASE语句WHILE语句LOOP语句REPEAT语句条件分支语句IF…ELSE语句IF…ELSE语句只能使用在存储过程中，实现了非此即彼的逻辑IF条件表达式1THEN

语句块1;

[ELSEIF条件表达式2THEN

语句块2;]

……

[ELSE

语句块n+1;]ENDIF;其中，当“条件表达式1”的值为TRUE时，“语句块1”将被执行；若没有“条件表达式”的值为TRUE，则执行“语句块n+1”，每个语句块都可以包含一个或多个语句。条件分支语句【例6.5】查询uid为3的用户是否购买过商品（判断其是否有订单）。DELIMITER//CREATEPROCEDUREproc_orders()#定义存储过程，用于包含IF语句BEGIN DECLAREnumint;#定义局部变量 #计算订单数并存储num中 SELECTcount(*)INTOnumFROMordersWHEREuid=3; IFnum>0THEN#IF语句判断num值 SELECT'有订单'; ELSE SELECT'无订单'; ENDIF;#结束IF语句END//条件分支语句CASECASE作为分支语句放在程序体中，类似Java或C语言中的switch…case语句。CASE语句语法有两种格式：简单CASE结构和CASE搜索结构简单CASE结构CASE表达式 WHEN数值1THEN语句块1; [WHEN数值2THEN语句块2;] …… [ELSE语句块n+1;]ENDCASE;将“表达式”的值与WHEN子句后的“数值”比较，找到完全相同的项时，则执行对应的“语句块”，若未找到匹配项，则执行ELSE后的“语句块n+1”

条件分支语句CASE搜索结构CASE WHEN条件表达式1THEN语句块1; [WHEN条件表达式2THEN语句块2;] …… [ELSE语句块n+1;]ENDCASE;判断WHEN子句后的“条件表达式”的值是否为TRUE，若为TRUE，则执行对应的“语句块”，若所有的“条件表达式”的值均为FALSE，则执行ELSE后的“语句块n+1”。

条件分支语句【例6.6】判断参数grade，当值为A时返回“优秀”，值为B时返回“良好”，其他值返回“一般”。DELIMITER//CREATEPROCEDUREproc_grade1(gradechar)#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEgradeWHEN'A'THENSETresult='优秀';WHEN'B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//SQL的流程控制【例6.7】使用CASE搜索结构实现【例6.6】DELIMITER//CREATEPROCEDUREproc_grade2(gradechar(1))#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEWHENgrade='A'THENSETresult='优秀';WHENgrade='B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//循环语句

WHILE语句【例6.8】使用WHILE语句，求1到100的和。[开始标签:]WHILE条件表达式DO

语句块;ENDWHILE[结束标签];DELIMITER//CREATEPROCEDUREproc_doWhile()BEGIN DECLAREiintdefault1;#定义局部变量i DECLAREsintdefault0;#定义局部变量s WHILEi<=100DO#开始循环 SETs=s+i; SETi=i+1; ENDWHILE;#结束循环 SELECTs;END//循环语句REPEAT语句[开始标签:]REPEAT

语句块; UNTIL条件表达式;ENDREPEAT[结束标签];其中，UNTIL关键字表示直到满足条件表达式时结束循环，其它参数释意同WHILE语句。学习提示：REPEAT语句是在执行循环体里的语句块后再执行“条件表达式”的比较，不管条件是否满足，循环体至少执行一次；而WHILE语句则是先执行“条件表达式”的比较，当结果为TRUE时再执行循环体中的语句块。循环语句LOOP语句【例6.9】LOOP语句示例。[开始标签:]LOOP

语句块ENDLOOP[结束标签];add_num:LOOP SETi=i+1;ENDLOOPadd_num;学习提示：（1）循环里没有跳出循环的语句，这个循环是死循环。（2）LOOP语句的循环体中没有中止循环的语句，它必须和LEAVE语句结合使用。

循环语句LEAVE语句用于跳出循环控制，与高级语言中的BREAK语句相似。【例6.10】修改【例6.9】，使用LEAVE语句跳出循环。LEAVE标签名;add_num:LOOP SETi=i+1; IFi=100THENLEAVEadd_num;ENDLOOPadd_num;循环语句ITERATE语句用于跳出循环，与Java或C语言中的CONTINUE语句相似。ITERATE语句只跳出当次循环，然后直接进入下一次循环。ITERATE标签名;学习提示：LEAVE语句和ITERATE语句都是用来跳出循环语句，但两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环外的程序语句；ITERATE语句是跳出本次循环，进入下一次循环。MySQL常用内置函数MySQL内置函数是MySQL数据库提供的内部函数。SQL语句和表达式中都可以使用这些函数。主要包括：数学函数字符串函数日期时间函数数据类型转换函数条件控制函数加密和散列函数系统信息函数JSON函数数学函数数学函数主要用于处理数字，包括整数、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数和随机函数。函数名称作用abs(x)返回x的绝对值ceil(x),ceiling(x)返回大于或等于x的最小整数floor(x)返回小于或等于x的最大整数rand()返回0～1的随机数rand(x)返回0～1的随机数，x值相同时返回的随机数相同sign(x)返回x的符号，x是负数、0、正数时分别返回-1、0和1pi()返回圆周率（3.141593）truncate(x,y)返回数值x保留到小数点后y位的值round(x)返回离x最近的整数round(x,y)返回x小数点后y位的值，但截断时要进行四舍五入pow(x,y),power(x,y)返回x的y次方sqrt(x)返回x的平方根exp(x)返回e的x次方mod(x,y)返回x除以y以后的余数【例6.11】以1为基数，每天进步0.01，计算一年后的进步有多大?数学函数函数名称作用log(x)返回自然对数（以e为底的对数）log10(x)返回以10为底的对数radians(x)将角度转换为弧度degrees(x)将弧度转换为角度sin(x)求正弦值cos(x)求余弦值tan(x)求正切值cot(x)求余切值mysql>SELECTpower((1+0.01),365)asprogress;++|progress|++|37.78343433288728|++1rowinset(0.00sec)字符串函数字符串函数主要用于处理字符串。字符串函数包括字符串长度、合并字符串、在字符串中插入子串和大小字母之间切换等函数。函数名称作用char_length(s)返回字符串s的字符数length(s)返回字符串s的长度concat(s1,s2,…)将字符串s1、s2等多个字符串合并为一个字符串concat_ws(x,s1,s2,…)同CONCAT(s1,s2,…)函数，但是每个字符串要直接加上xinsert(s1,x,len,s2)将字符串s2替换成s1的x位置开始长度为len的字符串upper(s),ucase(s)将字符串s的所有字母都变成大写字母lower(s),lcase(s)将字符串s的所有字母都变成小写字母left(s,n)返回字符串s的前n个字符right(s,n)返回字符串s的后n个字符lpad(s1,len,s2)字符串s2来填充s1的开始处，使字符串长度达到lenrpad(s1,len,s2)字符串s2来填充s1的结尾处，使字符串长度达到lenltrim(s)去掉字符串s开始处的空格rtrim(s)去掉字符串s结尾处的空格trim(s)去掉字符串s开始处和结尾处的空格【例6.12】输出合并的两个字符串，并在两个子串之间插入1个空格。数学函数函数名称作用trim(s1froms)去掉字符串s中开始处到结尾处的字符串s1repeat(s,n)将字符串s重复n次space(n)返回n个空格replace(s,s1,s2)用字符串s2替代字符串s中的字符串s1strcmp(s1,s2)比较两个字符串，若s1>s2，返回1，反之返回-1，若相等返回0substring(s,n,len)获取从字符串s中的第n个位置开始长度为len的子字符串mid(s,n,len)同SUBSTRING(s,n,len)locate(s1,s),position(s1ins)返回字符串s1在字符串s中的起始位置mysql>SELECTCONCAT('Hunan',SPACE(1),'Changsha')asstr;++|str|++|HunanChangsha|++1rowinset(0.00sec)日期时间函数日期时间函数主要用于处理日期和时间数据。日期时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等函数名称作用curdate(),current_date()返回当前日期curtime(),current_time()返回当前时间now(),current_timestamp()返回当前日期和时间utc_date()返回UTC（国际协调时间）日期utc_time()返回UTC（国际协调时间）时间month(d)返回日期d中的月份值，范围1～12monthname(d)返回日期d中的月份名称，如January、February等dayname(d)返回日期d是星期几，如Monday、Tuesday等dayofweek(d)返回日期d是星期几，1表示星期日，2表示星期一等weekday(d)返回日期d是星期几，0表示星期一，1表示星期二等week(d)计算日期d是本年的第几个星期，范围是0～53dayofyear(d)计算日期d是本年的第几天dayofmonth(d)计算日期d是本月的第几天year(d)返回日期d中的年份值【例6.14】获取系统当前日期时间的年份值、月份值、日期值、小时值和分钟值。日期时间函数函数名称作用hour(t)返回时间t中的小时值minute(t)返回时间t中的分钟值second(t)返回时间t中的秒钟值date_format(d,f)按表达式f的格式显示d,f定义了日期和日间的格式，以%开头date_add(d,intervaleunit)返回指定日期d指定间隔的日期，e为间隔数，unit为日期部分SET@mydate=CURDATE();SET@mytime=CURTIME();SELECTYEAR(@mydate),MONTH(@mydate),DAYOFMONTH(@mydate),HOUR(@mytime),MINUTE(@mytime);数据类型转换函数【例6.16】数据类型转换函数示例。函数名称作用cast(xAStype)将x的值按type类型返回convert(x,type)将x的值按type类型返回convert(xUSING字符集)将经的值按指定的字符集返回SELECTCAST('2021-10-0116:50:21'asdate); #输出2021-10-01SELECTconvert('132str',SIGNED); #输出123SELECTconvert('大'USINGutf8mb4),convert('大'USINGascii);#输出大,？条件控制函数主要处理简单的逻辑判断。【例6.17】条件控制函数示例。条件控制函数函数名称作用if(expr,v1,v2)判断expr的值，为TRUE时返回v1，否则返回v2ifnull(v1,v2)判断v1的值，若不为null返回v1，否则返回v2nullif(v1,v2)比较v1与v2的值，若相等返回null，否则返回v1isnull(expr)判断expr的值，为null时返回1，否则返回0SELECTif(TRUE,'A','B'),if(FALSE,'A','B'); #输出ABSELECTifnull('A','B'),ifnull(null,'B'); #输出ABSELECTnullif('A','B'),nullif('A','A'); #输出AnullSELECTisnull(null),isnull('A'); #输出10【例6.18】查询图书类商品的名称和销售状态(sale_status)，若gishot为1，显示“热销”，否则显示为“一般”。条件控制函数Smysql>SELECTgname,if(gishot=1,'热销','一般')ASsale_status

->FROMgoodsJOINcategoryUSING(cid)

->WHEREcname='图书';+++|gname

|sale_status|+++|林清玄启悟人生系列：愿你，归来仍是少年

|一般

||平凡的世界：全三册（激励青年的不朽经典）

|热销

||曾国藩全集（全六卷绸面精装插盒珍藏版）

|一般

||中外文化文学经典系列红岩导读与赏析

|一般

|+++4rowsinset(0.00sec)加密和散列函数主要用于对存储的数据进行加密，相对于明文存储，加密后的字符串不会被管理员直接看到，以保证数据的安全性，实际应用中对于敏感数据的存储都要进行加密处理。【例6.19】加密和散列函数示例。加密和散列函数函数名称作用md5(str)使用MD5算法对str计算，返回32位的散列字符串aes_encrypt(str,key)使用密钥key对str进行加密，返回128位的二进制串aes_decrypt(str,key)使用密钥key对加密文本str进行解密sha1(str),sha(str)使用安全散列算法SHA1计算str，返回40位十六进制数字组成的字符串->convert(aes_decrypt(aes_encrypt('abc','z'),'z')usingascii)str\G;***************************1.row***************************md5('abc'):900150983cd24fb0d6963f7d28e17f72

sha1('abc'):a9993e364706816aba3e25717850c26c9cd0d89d

str:abc1rowinset(0.00sec)【例6.19】加密和散列函数示例。JSON函数函数名称作用json_array([val[,val]…])生成一个包括指定元素的JSON数组json_object([key,val[,key,val]…])生成一个包括指定key-value键值对的JSON对象json_keys(json_doc[,path])获取JSON文档指定path下的所有键json_value(json_doc,path)获取JSON文档指定path下的所有键值json_contains(json_doc,val[,path])若JSON文档在path中包含指定数据，则返回1json_extract(json_doc,one_or_all,str,[path])从JSON文档中抽取指定path的值，也可以使用->运算符json_search(json_doc,one_or_all,str,[path])返回符合查询条件的str对应的的JSON路径所组成的数组json_arrayagg(expr)将结果集expr聚合成单个JSON数组json_objectagg(k,v)将结果集的分键值对聚合成单个JSON对象json_table(json_doc,pathCOLUMNS(列定义列表)[AS]列别名)MySQL8.0.4+支持，用于解析JSON文档，按指定的path,按列定义列表将JSON对象转换成关系表【例6.20】JSON函数示例。JSON函数#创建一个json数组SELECTjson_array('id',12,'name','李明');#输出["id",12,"name","李明"]#创建一个json对象SELECTjson_object('id',12,'name','李明');#输出{"id":12,"name":"李明"}#定义用户变量@infoSET@info='{"name":[{"id":12,"name":"李明"},{"id":13,"name":"刘立"}]}';#取json对象的所有键SELECTjson_keys(@info,"$.name[0]");#输出["id","name"]#取json对象的键值SELECTjson_value(@info,"$.name[2]");#输出{"id":12,"name":"李明"}【例6.21】将category表中cid小于4的每一行记录，生成成一个JSON对象。【例6.22】将category表中cid小于4的记录，生成成一个JSON数组。JSON函数mysql>SELECTjson_object('cid',cid,'cname',cname)->FROMcategory->WHEREcid<4;++|json_object('cid',cid,'cname',cname)|++|{"cid":1,"cname":"图书"}||{"cid":2,"cname":"乐器"}||{"cid":3,"cname":"蔬菜水果"}|++3rowsinset(0.00sec)mysql>SELECTjson_objectagg(json_object('cid',cid,'cname',cname))cjson->FROMcategory->WHEREcid<4\G;***************************1.row***************************cjson:[{"cid":1,"cname":"图书"},{"cid":2,"cname":"乐器"},{"cid":3,"cname":"蔬菜水果"}]1rowinset(0.00sec)【例6.23】将【例6.22】生成的@category_json变量值还原成关系表。。JSON函数SELECT*FROMjson_table(@category_json, '$.category[*]'COLUMNS(cidintpath'$.cid’, cnamevarchar(30)path'$.cname'))ast+++|cid|cname

|+++|1|图书

||2|乐器

||3|蔬菜水果|+++3rowsinset(0.00sec)系统信息函数用来查询MySQL数据库的系统信息。例如，查询数据库版本、数据库当前用户等。【例6.24】获取当前登录用户、连接id和数据库名。系统信息函数函数名称作用version()返回数据库的版本号，也系统变量@@version的值相同connection_id()返回当前服务器的连接iddatabase(),schema()返回当前数据库名user(),system_user(),session_user()返回当前登录MySQL服务器的用户current_user(),current_user返回当前帐户允许哪些主机可以登录MySQL服务器last_insert_id返回当前会话中最后一个插入的auto_increment的值mysql>SELECTuser(),connection_id(),database();++++|user()|connection_id()|database()|++++|root@localhost|12

|onlinedb|++++1rowinset(0.00sec)【例6.25】IP地址转换函数示例。其他常用函数函数名称作用last_insert_id()返回当前会话中最后一个插入的auto_increment的值inet_aton(IP)将IP地址转换成数值存储inet_aton(value)将value值转换成IP地址sleep(value)延迟value秒执行语句uuid()在同一时间和同一空间实现创建唯一标识符SELECTinet_aton(''); #输出3232235521SELECTinet_ntoa(3232235521);

#输出任务2

使用存储函数实现数据访问实际开发中，为了让应用程序专注业务处理，数据库层常定义存储函数和存储过程来封装数据处理逻辑，以提高代码的重用性及数据访问效率。本任务主要介绍MySQL中存储函数的创建、调用和管理的方法，有效实现数据库中模块化数据访问。任务场景创建存储函数MySQL中，创建存储函数的SQL语句的语法格式如下。CREATEFUNCTION函数名([参数列表]) RETURNS数据类型 {

DETERMINISTIC|NOSQL|READSSQLDATA

}

函数体;

函数名：存储函数的名称。不能与数据库中其他对象名相同。

参数列表：存储函数的输入参数，每个参数由参数名称和参数类型组成。

RETURNS数据类型：指定函数返回值的数据类型。

函数体：存储函数的主体，可以是单个SELECT语句，若包含多条语句时，必须使用BEGIN...END来标识SQL代码的开始和结束。函数体中必须包含RETURN关键字将结果返回给调用者，且返回的结果值必须为标量值。

DETERMINISTIC|NOSQL|READSSQLDATA：为函数特征值，至少选择三者之一。DETERMINISTIC：指明函数的确定性，当设置为确定性函数时，表示每次执行函数时，相同的输入会得到相同的输出，且不会修改数据；NOSQL表示函数体中不包含SQL语句；READSSQLDATA说明函数体中只包含读语句。若不设置任何特征，就需要设置系统全局变量@@GLOBAL.log_bin_trust_function_creators的值为ON。创建存储函数【例6.26】创建函数func_count，返回商品类别的数量。【例6.27】创建函数func_getName，根据指定的商品id，查询商品名称。CREATEFUNCTIONfunc_count()RETURNSintegerDETERMINISTICRETURN(SELECTCOUNT(*)FROMcategory);CREATEFUNCTIONfunc_getName(in_idint)#定义参数in_id用于接受商品id值RETURNSvarchar(50)DETERMINISTICRETURN(SELECTgnameFROMgoodsWHEREgid=in_id);创建存储函数【例6.28】创建函数func_getRandStr，返回指定长度的字母数字随机串。DELIMITER//CREATEFUNCTIONfunc_getRandStr(nint)#定义参数nRETURNSvarchar(255)NOSQL#表示函数体中不包括查询语句BEGIN--定义字符库，由字母和数据组成DECLAREchars_strvarchar(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';DECLAREreturn_strvarchar(255)DEFAULT'';#定义局部变量return_strDECLAREiintDEFAULT0;#定义局部变量i,用于循环计数WHILEi<nDOSET@len=length(chars_str);#计算chars_str字符串长度SET@pos=ceiling(rand()*@len);#随机生成0-@len范围内的数#取字符串chars_str中的@pos位的字母，并使用concat函数连接成串SETreturn_str=concat(return_str,mid(chars_str,@pos,1));SETi=i+1;#循环变量加1ENDWHILE;RETURNreturn_str;#返回字符串return_strEND//调用存储函数存储函数的使用与MySQL内置函数的使用方法是一样。【例6.29】调用函数func_count。SELECT函数名([参数列表]);mysql>SELECTfunc_count();++|count(*)|++|6|++1rowinset(0.01sec)调用存储函数存储函数的使用与MySQL内置函数的使用方法是一样。【例6.29】调用函数func_count。SELECT函数名([参数列表]);mysql>SELECTfunc_count();++|count(*)|++|6|++1rowinset(0.01sec)调用存储函数存储函数的使用与MySQL内置函数的使用方法是一样。【例6.29】调用函数func_count。SELECT函数名([参数列表]);mysql>SELECTfunc_count();++|count(*)|++|6|++1rowinset(0.01sec)调用存储函数【例6.30】调用函数func_getName，查询商品ID为1的商品名称。【例6.31】调用函数func_getRandStr，输出产生长度为3、5、10的随机字符串。mysql>SELECTfunc_getName(2);++|gname

|++|平凡的世界：全三册（激励青年的不朽经典）|++1rowinset(0.01sec)mysql>SELECTfunc_getRandStr(3),func_getRandStr(5),func_getRandStr(10);++++|func_getRandStr(3)|func_getRandStr(5)|func_getRandStr(10)|++++|UJL

|rNuRJ

|RZaK52Jnxo

|++++1rowinset,2warnings(0.01sec)查看存储函数的状态和定义MySQL中可以通过SHOWSTATUS语句来查看存储函数的状态。

【例6.32】查看存储函数func_count的状态信息。SHOWFUNCTIONSTATUS[LIKE匹配模式];mysql>SHOWCREATEFUNCTIONlike'func_count'\G;***************************1.row***************************Db:onlinedbName:func_countType:FUNCTIONDefiner:root@localhostModified:2021-09-0511:25:49Created:2021-09-0511:25:49Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)查看存储函数的状态和定义MySQL也可以通过SHOWCREATE语句来查看函数的定义。

【例6.33】查看函数func_count的定义。SHOWCREATEFUNCTION存储函数名;mysql>SHOWCREATEFUNCTIONfunc_count\G;***************************1.row***************************Function:func_countsql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreateFunction:CREATEDEFINER=`root`@`localhost`FUNCTION`func_count`()RETURNSintDETERMINISTICRETURN(SELECTCOUNT(*)FROMcategory)character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)删除函数删除函数指删除数据库中已经存在的函数。MySQL中使用DROPFUNCTION语句来删除函数。【例6.34】删除函数func_count。DROPFUNCTION[IFEXISTS]存储函数名;mysql>DROPFUNCTIONIFEXISTSfunc_count;QueryOK,0rowsaffected(0.05sec)任务3

使用存储过程实现数据访问存储过程与存储函数一样，也可以封装具有一定功能的语句块，存储过程也可以将预编译并保存在数据库中，供用户重复调用。本任务从存储过程的优点着手，详细介绍创建、执行、修改和删除存储过程的方法，有效实现数据库中模块化数据访问。任务场景存储过程概述存储过程是数据库中的重要对象，它将特定的SQL语句集进行封装，完成数据库中复杂的数据处理逻辑，以提高程序的复用性。存储过程采用预编译方式，也就是说存储过程执行一次，其执行规划就驻留在高速缓存中，再次调用时直接使用已编译好的二进制代码即可，因而其执行效率比较高。存储过程的优点存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码即可，提高了系统性能。存储函数必须使用RETURN返回数据，且只能返回标量数据；而存储过程没有RETURN子句，其数据返回方式可以通过SELECT语句和输出参数实现。存储过程可以嵌套在触发器或事件中，运用灵活。数据库管理员能够对存储过程进行单独的权限控制，避免非授权用户对数据的访问。创建存储过程创建存储过程的基本语法如下。CREATEPROCEDURE存储过程名([参数列表]) [存储过程特性]

程序体

参数定义:[IN|OUT|INOUT]参数名称参数类型

存储过程名：定义的存储过程的名称。

存储过程特性：与存储函数定义里该参数的说明相同。

程序体：封装的SQL代码集合，用BEGIN...END来标识SQL代码的开始和结束。

IN|OUT|INOUT：表示参数方向，其中IN表示输入参数；OUT表示输出参数；INOUT表示输入输出参数。创建存储过程【例6.35】创建存储过程，查询goods表中前3条商品的gid,gname和gprice。DELIMITER//CREATEPROCEDUREproc_getgoods()READSSQLDATABEGIN SELECTgid,gname,gpriceFROMgoodsLIMIT3;END//调用存储过程MySQL中使用CALL语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句，将执行结果返回给输出。【例6.36】调用名为proc_getgoods的存储过程，输出相应商品的名称和价格。CALL存储过程名([参数列表]);mysql>CALLproc_getgoods();++++|gid|gname

|gprice|++++|1|林清玄启悟人生系列：愿你，归来仍是少年|29.00||2|平凡的世界：全三册（激励青年的不朽经典）|94.00||3|曾国藩全集（全六卷绸面精装插盒珍藏版）|255.00|++++3rowsinset(0.00sec)QueryOK,0rowsaffected(0.01sec)参数化存储过程存储过程可以指定一个或多个参数参数的声明由参数方向、参数名和参数类型3部分构成。一般至少提供参数名和参数类型。参数方向是指数据传输方向，在没有指定的情况下默认为输入参数。创建和调用带输入参数的存储过程【例6.37】创建存储过程proc_getGoodsPage，根据指定的页码(假定每页3件商品)，显示该页中商品的gid,gname和gprice。DELIMITER//CREATEPROCEDUREproc_getGoodsPage(pageint)READSSQLDATABEGINDECLAREstartposint;#定义局部变量startposSETstartpos=(1)*5;#计算查询记录的开始位置SELECTgid,gname,gpriceFROMgoodsLIMITstartpos,5;END//参数化存储过程【例6.38】调用存储过程proc_getGoodsPage，查询第2页商品的信息。mysql>CALLproc_getGoodsPage(2);++++|gid|gname

|

gprice|++

++|4|中外文化文学经典系列红岩导读与赏析|

29.00||5|古琴老杉木乐器伏羲式_七弦琴

|3299.00||6|专业演奏级乐器洞箫_8孔正手G调

|

549.00|++++3rowsinset(0.04sec)QueryOK,0rowsaffected(0.05sec)参数化存储过程创建和调用带输入输出参数的存储过程【例6.39】创建存储过程proc_getGoodsPages，在返回【例6.38】结果的基础上，返回总页数。DELIMITER//CREATEPROCEDUREproc_getGoodsPages(pageint,outtotal_pagesint)READSSQLDATABEGINDECLAREstartposint;#定义局部变量startposSETstartpos=(1)*3;#计算查询记录的开始位置SELECTgid,gname,gpriceFROMgoodsLIMITstartpos,3;#计算总页数等于商品总数除以总页数向上取整SETtotal_pages=ceiling((SELECTcount(*)FROMgoods)/3);END//参数化存储过程【例6.40】调用存储过程proc_getGoodsPages，结果如下。mysql>CALLproc_getGoodsPages(2,@pages);++++|gid|gname

|

gprice|++++|4|中外文化文学经典系列红岩导读与赏析|

29.00||5|古琴老杉木乐器伏羲式_七弦琴

|3299.00||6|专业演奏级乐器洞箫_8孔正手G调

|

549.00|++++3rowsinset(0.00sec)QueryOK,0rowsaffected(0.04sec)

mysql>SELECT@pages;#查询返回的输出参数@page++|@pages|++|

4|++1rowinset(0.00sec)管理存储过程查看存储过程的定义MySQL中可以通过SHOWSTATUS语句来查看存储过程的状态。MySQL也可以通过SHOWCREATE语句来查看存储过程的定义。【例6.41】查看存储过程proc_getGoodsPages的定义。SHOWPROCEDURESTATUS[LIKE匹配模式];SHOWCREATEPROCEDURE存储过程名;SHOWCREATEPROCEDUREproc_getGoodsPages;管理存储过程删除存储过程【例6.42】删除名为proc_getGoodsPage的存储过程。DROPPROCEDURE[IFEXISTS]存储过程名;DROPPROCEDUREIFEXISTSproc_getGoodsPage;错误处理MySQL提供的DECLARE…HANDLERFOR语句可以指定的错误名称或代码定义相应的处理程序。DECLARE错误处理方式HANDLERFOR错误类型处理程序;错误处理方式取值为CONTINUE（遇到错误时不处理，继续执行）和EXIT（遇到错误时马上中断执行并退出）；错误类型主要有如下4种。MySQL的错误代码，比如1305或服务器内部错误42000。SQLWARNING：表示所有以01开头的SQLSTATE错误代码。NOTFOUND：表示所有以02开头的SQLSTATE错误代码。SQLEXCEPTION：表示除01和02开头外的所有SQLSTATE错误代码。处理程序，表示当遇到时，需要执行的存储过程的代码段。错误处理【例6.43】错误处理的应用示例。【例6.44】调用存储过程proc_errorHandler，查看用户变量@flag的值。DELIMITER//CREATEPROCEDUREproc_errorHandler()#创建存储过程proc_errorHandlerBEGIN#当遇到状态码为23000的错误时，设置@flag值为10，程序继续执行 DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@flag=10; INSERTINTOcategoryvalues(7,'鲜花'); SET@flag=1; INSERTINTOcategoryvalues(7,'鲜花');#重复插入商品类别 SET@flag=@flag+1;END//mysql>CALLproc_errorHandler();QueryOK,0rowsaffected(0.00sec)mysql>SELECT@flag;#查询用户变量@flag++|@flag|++|11|++1rowinset(0.00sec)任务4

使用触发器实现自动任务触发器是数据库中的独立对象，为了确保数据完整性，设计人员可以用触发器实现复杂的业务逻辑。例如，当用户选购好商品之后，并完成了订单，那么用户所选购的商品的库存量应该根据用户订单中商品的数量进行减少。本任务详细介绍触发器的应用，以保持数据的完整性和一致性。任务场景触发器概述触发器是一种特殊的存储过程可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所关联的数据改变时，触发器会自动被激活。MySQL中激活触发器的操作包含INSERT、UPDATE和DELETE。MySQL提供了两个逻辑表new和old。new和old的表结构与触发器所在数据表的结构完全一致，当触发器的执行完成之后，这两个表也会被自动删除。old表用来存放更新前的记录。对于UPDATE语句，old表中存放的是更新前的记录（更新完后即被删除）；对于DELETE语句，该表中存放的是被删除的记录。NEW表用来存放更新后的记录。对于INSERT语句，new表中存放的是要插入的记录；对于UPDATE语句，new表中存放的是要更新的记录。创建触发器在MySQL中，创建触发器的语法格式如下CREATETRIGGER触发器名称触发时间触发事件ON表名FOREACHROW程序体触发器名称：指要创建的触发器的名称；触发时间：指触发器执行的时间，它可以是BEFORE或AFTER，以指明触发器是在激活它的语句之前或之后触发；触发事件：指激活触发程序的语句类型，包括INSERT、UPDATE和DELETE表名：是指触发事件操作的表名称；FOREACHROW：表示任何一条记录上的操作满足触发事件都会触发该触发器；程序体：指触发器被触发后执行的语句集。创建触发器【例6.45】创建触发器trig_ins_addnum，当uid为1的用户向购物车中添加商品时，自动记录该用户添加商品的总数量。CREATETRIGGERtrig_ins_addnum#建议命名以trig_开头，其中ins表示是insertAFTERINSERT

#数据插入完成后执行ONcart

#触发器建立的表为cartFOREACHROW SET@total=@total+NEW.cnum;#累加新增记录的购买数量创建触发器【例6.46】向users表中添加新用户，判断输入的密码长度，若长度小于6，则拒绝插入新用户，并提示“密码长度小于6，请重新输入“。若长度大于等于6，则按md5算法对密码加密处理。DELIMITER//CREATETRIGGERtrig_ins_checkpwdBEFOREINSERT#向users表插入数据前执行ONusersFOREACHROWBEGIN IFlength(new.upwd)>=6THEN#执行判断密码长度 SETnew.upwd=md5(upwd);#符合条件时的处理逻辑

ELSE #自定义错误提示，数据插入失败 SIGNALSQLSTATE'45000’ SETmessage_text='密码长度小于6，请重新输入'; ENDIF;END//管理触发器查看触发器的定义MySQL中可以通过SHOWTRIGGERS语句来查看触发器的基本信息。【例6.47】查看cart表上定义的触发器。SHOWTRIGGERS[{FROM|IN}数据库名][LIKE匹配模式|WHERE条件表达式]mysql>SHOWTRIGGERSlike'cart%'\G;***************************1.row************************Trigger:trig_ins_addnumEvent:INSERTTable:cartStatement:SET@total=@total+NEW.cnumTiming:AFTERCreated:2021-09-0710:35:34.06sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)管理触发器删除触发器删除触发器指删除数据库中已经存在的触发器。MySQL使用DROPTRIGGER语句来删除触发器。【例6.48】删除名称为trig_ins_addnum的触发器。DROPTRIGGER[IFEXISTS]

trigger_name;mysql>DROPTRIGGERtrig_ins_addnum;QueryOK,0rowsaffected(0.02sec)任务5

使用事件实现自动任务数据库管理是一项重要且烦琐的工作，许多日常管理任务往往会频繁地、周期性地执行，例如定时刷新数据、定期维护索引、定时关闭账户、定义打开或关闭数据库等操作，实际应用中，数据库管理员会定义事件对象以自动化完成这些任务。本任务将详细介绍MySQL中事件的创建、维护和管理等。任务场景事件概述事件是在特定时刻调用的数据库对象。一个事件可调用一次，也可周期性的被调用，由MySQL中的“事件调度器”来调度和管理。事件取代了原先只能由操作系统的计划任务来执行的工作。MySQL的事件调度器可以精确到每秒钟执行一个任务。操作系统的计划任务只能精确到每分钟执行一次。开启事件调度器事件调度器是MySQL数据库服务器的一部分，负责事件的调度，它监视数据库中哪些事件需要被调用，默认为关闭状态。若要事件能按时被调用，服务器必须先开启事件调度器。MySQL中的全局变量@@GLOBAL.event_scheduler用于监控事件调度器启停状态。【例6.49】查看MySQL服务器事件调度器的状态。mysql>SHOWVARIABLESLIKE'event_scheduler';+++|Variable_name|Value|+++|event_scheduler|OFF|+++1rowinset,1warning(0.01sec)开启事件调度器【例6.50】打开MySQL服务器事件调度器。mysql>SET@@GLOBAL.event_scheduler=ON;QueryOK,0rowsaffected(0.00sec)

mysql>SHOWVARIABLESLIKE'event_scheduler';+++|Variable_name|Value|+++|event_scheduler|ON|+++创建事件MySQL中，要完成自动化作业就需要创建事件。每个事件由事件调度（eventschedule）和事件动作（eventaction）两个部分组成。事件调度表示事件何时启动以及按什么频率启动。事件动作表示事件启动时执行的代码。CREATEEVENT[IFNOTEXISTS]事件名称

ONSCHEDULE时间与频率

[ONCOMPLETI