MySQL数据库技术与项目应用教程电子教案 项目六-1 数据库编程(基础)

备课纸适用班级：_______P.课题项目六编程操作网上商城系统数据（一）课时2+2课时教学目的掌握MySQL中变量和常量的定义掌握MySQL中流程控制语句的使用掌握MySQL常用的内置函数重点MySQL中流程控制语句的使用掌握MySQL常用的内置函数难点MySQL中流程控制语句的使用教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入提出问题数据库编程1.数据库中如何进行业务逻辑处理？的意义2.对于集合数据，数据库中如何单独操纵？任务1数据库编程基础知识描述：任何一种语言都是为了解决实际应用中的问题而存在的。SQL程序的流程控制和游标的使用能够有效解决数据库程序设计中的复杂逻辑问题。本任务在SQL程序语言基础上，详细讨论了SQL的流程控制和游标的使用。备课纸适用班级：_P.SQL程序语言基础带着问题开本任务讨论如下内容：展本章学习1.MySQL中如何定义变量？激发学生兴2.MySQL中怎样控制处理逻辑？趣3.在集合数据中如何对记录进行逐行处理？变量略讲变量是指程序运行过程中会变化的量。用户变量系统变量和服务器变量局部变量略讲（1）用户变量用户变量即用户定义的变量。用户变量可以被赋值。以@字符作为前缀。用户变量名不区分大小写，在MySQL会话末端结束其定义。用户变量使用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;（2）系统变量系统变量是MySQL的一些特定的参数。当MySQL服务启动时，这些参数将被读取并配置MySQL的运行环境。系统变量使用“@@”作为前缀标识符。MySQL提供了专门查看系统变量的语句：备课纸适用班级：_P.SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配模式'|WHERE条件表达式];GLOBAL用于显示全局系统变量，当变量没有全局值时，则不会显示。SESSION为表示会话变量，是默认值，可以省略，用于显示当前连接中有效的系统可变值。【例6.2】使用SHOWVARIABLES查看所有系统变量。SHOWVARIABLES;【例6.3】设置和查看系统变量。SET@@profiling=0;#设置系统变量，关闭profiles功能SET@@event_scheduler=0;00;#设置系统变量，开启事件调度器SELECT@@global.version;#查看全局变量version,获知当前mysql的版本号SHOWVARIABLESLIKE'ver%'#查看以ver开头的系统变量（3）局部变量局部变量一般用在SQL语句块（如存储过程的BEGIN和END）中。其作用域仅限于语句块，当语句块执行完毕后，局部变量就消失了。局部变量用DECLARE来声明，可以使用DEFAULT来设置初始值。【例6.3】定义名称为proc_add的存储过程，该存储过程有两个int类型的参数，分别为a和b。DELIMITER//#修改默认提交符为“//”CREATEPROCEDUREproc_add(aint,bint)BEGINDECLAREcintDEFAULT0;#定义局部变量变c,初始值为0SETc=a+b;SELECTcAS'Result';END//#提交学习提示MySQL默认代码提交符为分号“;”，由于程序体中有多个语句，这时需要通过DELIMITER语句修改默认的代码提交符号。笔者这里修改为“//”符号，读者可以根据个人习惯设置。备课纸适用班级：_P.2、常量常量是指在程序运行过程中，值不会改变的量。（1）字符串常量字符串是指用单引号或双引号括起来的字符序列（2）数值常量数值常量可以分为整数常量和浮点数常量（3）日期时间常量用单引号将表示日期时间的字符串括起来就是日期时间常量例如，'2008-05-1214:26：24:00'就是一个合法的日期时间常量。（4）布尔值常量布尔值只包含TRUE和FALSE两个值，其中TRUE表示真，数字值为1,FALSE表示假，数字值为0。（5）NULL值常量NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义3、运算符运算符是执行数学运算、字符串连接以及列、常量和变量之间进行比较的符号。同其他高级语言相同SQL的流程控制语句使用SQL语言也像其他程序设计语言一样有顺序结构、分支结构和循环结构等流程控制语句。1.条件分支语句条件分支语句是通过对特定条件的判断，选择一个分支的语句执行。SQL中可以实现条件分支的语句的方法（1）IF…ELSE语句备课纸适用班级：_P.IF条件表达式1THEN 语句块1;[ELSEIF条件表达式2THEN语句块2;]……[ELSE 语句块n+1;]ENDIF;【例6.5】查询uid为3的用户是否购买过商品（判断其是否有订单）。实例演示DELIMITER//CREATEPROCEDUREproc_orders()#定义存储过程，用于包含IF语句BEGINDECLAREnumint;#定义局部变量#计算订单数并存储num中SELECTcount(*)INTOnumFROMordersWHEREuid=3;IFnum>0THEN#IF语句判断num值SELECT'有订单';ELSESELECT'无订单';ENDIF;#结束IF语句END//（2）CASE语句=1\*GB3①CASE简单结构。语法知识CASE表达式WHEN数值1THEN语句块1;[WHEN数值2THEN语句块2;]……[ELSE 语句块n+1;]ENDCASE;=2\*GB3②CASE搜索结构语法知识CASEWHEN条件表达式1THEN语句块1;[WHEN条件表达式2THEN语句块2;]……[ELSE 语句块n+1;]ENDCASE;备课纸适用班级：_P.【例6.6】判断参数grade，当值为A时返回“优秀”，值为B时返回“良好”，其他值返回“一般”。精讲+演示DELIMITER//CREATEPROCEDUREproc_grade1(gradechar)#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEgradeWHEN'A'THENSETresult='优秀';WHEN'B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//【例6.7】使用CASE搜索结构实现【例6.6】精讲+演示DELIMITER//CREATEPROCEDUREproc_grade2(gradechar(1))#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEWHENgrade='A'THENSETresult='优秀';WHENgrade='B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//2.循环语句（1）WHILE语句MySQL中循环语句可以在函数、存储过程或者触发器等内容中使用。MySQL中提供WHILE、REPEAT和LOOP三种。多种方法实（1）WHILE语句现流程控制[开始标签:]WHILE条件表达式DO 语句块;ENDWHILE[结束标签];备课纸适用班级：_P.【例6.8】使用WHILE语句，求1到100的和。实例演示DELIMITER//CREATEPROCEDUREproc_doWhile()BEGINDECLAREiintdefault1;#定义局部变量iDECLAREsintdefault0;#定义局部变量s WHILEi<=100DO#开始循环 SETs=s+i; SETi=i+1; ENDWHILE;#结束循环 SELECTs;END//（2）REPEAT语句[开始标签:]REPEAT 语句块; UNTIL条件表达式ENDREPEAT[结束标签];学习提示REPEAT语句是在执行循环体里的语句块后再执行“条件表达式”的比较，不管条件是否满足，循环体至少执行一次；而WHILE语句则是先执行“条件表达式”的比较，当结果为TRUE时再执行循环体中的语句块。（3）LOOP语句[开始标签:]LOOP 语句块ENDLOOP[结束标签];【例6.9】LOOP语句示例。add_num:LOOP SETi=i+1;ENDLOOPadd_num;备课纸适用班级：_P.（4）LEAVE语句LEAVE语句主要用于跳出循环控制，与高级语言中的BREAK语句相似LEAVE标签名;【例6.10】修改【例6.9】，使用LEAVE语句跳出循环。add_num:LOOP SETi=i+1; IFi=100THENLEAVEadd_num;ENDLOOPadd_num;（4）ITERATE语句ITERATE语句也可用于跳出循环，与高级语言中的CONTINUE语句相似。ITERATE标签名;学习提示LEAVE语句和ITERATE语句都是用来跳出循环语句，但两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环外的程序语句；ITERATE语句是跳出本次循环，进入下一次循环。MySQL常用内置函数1.数学函数数学函数主要用于处理数字，包括整数、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数和随机函数等【例6.11】以1为基数，每天进步0.01，计算一年后的进步有多大?实例演示mysql>SELECTpower((1+0.01),365)asprogress;+-------------------+|progress|+-------------------+|37.78343433288728|+-------------------+1rowinset(0.00sec)2.字符串函数备课纸适用班级：_P.字符串函数主要用于处理字符串。字符串函数包括字符串长度、合并字符串、在字符串中插入子串和大小字母之间切换等函数【例6.12】输出合并的两个字符串，并在两个子串之间插入1个空格。实例演示mysql>SELECTCONCAT('Hunan',SPACE(1),'Changsha')asstr;+----------------+|str|+----------------+|HunanChangsha|+----------------+1rowinset(0.00sec)【例6.13】从字符串“mysql”取出“sql”的子串。实例演示SELECTmid('mysql',3,3);3.日期时间函数日期时间函数主要用于处理日期和时间数据。日期时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等【例6.14】获取系统当前日期时间的年份、月份、日期、小时和分钟。实例演示SET@mydate=CURDATE();SET@mytime=CURTIME();SELECTYEAR(@mydate),MONTH(@mydate),DAYOFMONTH(@mydate),HOUR(@mytime),MINUTE(@mytime);【例6.15】计算100天后的日期。实例演示SELECTdate_add(current_date(),interval100day);4.数据类型转换函数知识点介绍【例6.16】数据类型转换函数示例。备课纸适用班级：_P.实例演示SELECTCAST('2021-10-0116:50:21'asdate); #输出2021-10-01SELECTconvert('132str',SIGNED); #输出123SELECTconvert('大'USINGutf8mb4),convert('大'USINGascii);#输出大,？5.条件控制函数知识点介绍【例6.17】条件控制函数示例。实例演示SELECTif(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，显示“热销”，否则显示为“一般”。实例演示mysql>SELECTgname,if(gishot=1,'热销','一般')ASsale_status->FROMgoodsJOINcategoryUSING(cid)->WHEREcname='图书';+------------------------------------+-------------+|gname|sale_status|+------------------------------------+-------------+|林清玄启悟人生系列：愿你，归来仍是少年|一般||平凡的世界：全三册（激励青年的不朽经典）|热销||曾国藩全集（全六卷绸面精装插盒珍藏版）|一般||中外文化文学经典系列红岩导读与赏析|一般|+------------------------------------+-------------+4rowsinset(0.00sec)6.加密和散列函数备课纸适用班级：_P.知识点介绍加密和散列函数主要用于对存储的数据进行加密，相对于明文存储，加密后的字符串不会被管理员直接看到，以保证数据的安全性，实际应用中对于敏感数据的存储都要进行加密处理。学习提示md5()和sha()经常用于敏感数据的非明文存储，加密过程不可逆，数据验证时需要将验证文本按同样的计算后再比较是否一致。【例6.19】加密和散列函数示例。实例演示mysql>SELECTmd5('abc'),sha1('abc'),->convert(aes_decrypt(aes_encrypt('abc','z'),'z')usingascii)str\G;***************************1.row***************************md5('abc'):900150983cd24fb0d6963f7d28e17f72sha1('abc'):a9993e364706816aba3e25717850c26c9cd0d89dstr:abc1rowinset(0.00sec)7.JSON函数知识点介绍备课纸适用班级：_P.【例6.20】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对象。实例演示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)【例6.22】将category表中cid小于4的记录，生成成一个JSON数组。实例演示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":"蔬菜水果"}]1rowinse