数据基础及应用 3

第7章SQL程序设计7.4程序流程控制语句7.3游标应用运算符与表达式7.2变量应用7.5函数CONTENTS目录

7.1触发器

7.6存储过程

7.7事件

7.87.1运算符与表达式7.1运算符与表达式SQL运算符共有5类：算术运算符、位运算符、逻辑运算符、比较运算符、连接运算符1.算术运算符：+（加）、-（减）、*（乘）、/（除）、%（取模），优先级从高到低：*、/、%→+、-2.位运算符：&（与）、|（或）、^（异或）、~（取反）例7-2：171&73=9；15|12=15；15^12=3；~1=-23.比较运算符：>、<、>=、<=、=、<>（不等于）运算符与表达式（续）4.逻辑运算符：AND（与）、OR（或）、NOT（非）AND：两个操作数都为TRUE结果才为TRUEOR：两个操作数都为FALSE结果才为FALSENOT：单目运算，对操作数取反5.字符串连接：使用CONCAT函数例：CONCAT('MySQL','数据库管理系统')='MySQL数据库管理系统'运算符优先级优先级由高到低：①括号（）②正、负、取反：+、-、~③乘除求模：*、/、%④加减：+、-⑤比较运算符：=、>、<、>=、<=、<>⑥逻辑：NOT→AND→OR（优先级依次降低）例7-1算术运算符应用使用'+'将课程表中低于2的课程学分增加1：UPDATEcourseSETmark=mark+1WHEREmark<2;7.2变量应用7.2.1局部变量局部变量：作用范围在BEGIN…END之间定义语法：DECLAREvar_namedata_type[DEFAULTvalue];赋值语法：SETvar_name=value;DECLARExintDEFAULT0;SETx=100;从表查询赋值：SELECTAVG(score)INTOavg_scoreFROMstudent_course;7.2.2会话变量与全局变量会话变量：仅在当前连接有效，连接关闭时消失SET@session_variable_name=value;例：SET@my_session_var='Hello,MySQL!';全局变量：对所有连接有效SETGLOBALmax_connections=300;（重启后失效）SETPERSISTmax_connections=300;（永久有效，MySQL8.0新增）7.3游标应用7.3游标应用游标用于保存一次查询返回的多条记录，逐条进行处理分析游标使用流程：声明→打开→读取→关闭1.声明游标：DECLAREcursor_nameCURSORFORselect_statement;2.打开游标：OPENcursor_name;3.读取数据：FETCHcursor_nameINTOvar_1,var_2…4.关闭游标：CLOSEcursor_name;游标应用示例例7-3定义游标，返回student表中女生的id、name、gender：DECLAREcursor_studentCURSORFORSELECTid,name,genderFROMstudentWHEREgender='女';例7-4读取游标数据：FETCHcursor_studentINTOid,name,gender;注：游标只能在函数和存储过程中使用7.4程序流程控制语句7.4.1条件执行语句IF…ELSEIFboolean_expressionTHEN{sql_statement|statement_block}--条件为真时执行[ELSE{sql_statement|statement_block}]--条件为假时执行ENDIF;IF…ELSE语句可以嵌套使用例7-5IF条件执行示例判断选课表中是否存在平均成绩高于85分的课程：DELIMITER//CREATEPROCEDUREexample_if()BEGINIFEXISTS(SELECTcourse_idFROMstudent_courseGROUPBYcourse_idHAVINGAVG(mark)>85)例7-5IF条件执行示例（续）THENSELECT'有课程平均成绩高于85分';ELSESELECT'没有课程平均成绩高于85分';ENDIF;END//调用：CALLexample_if();注：DELIMITER//将结束符从分号改为//，避免与存储过程内部分号冲突7.4.1CASE函数CASEinput_expressionWHENwhen_expressionTHENresult_expression[…n][ELSEelse_result_expression]ENDCASE;执行过程：计算CASE后表达式的值，依次与WHEN中的值比较，匹配则返回THEN后的值例7-6CASE函数示例根据输入值决定输出内容：DELIMITER//CREATEPROCEDUREexample_case(INxint)BEGINCASExWHEN1THENSELECT1;WHEN2THENSELECT2;ELSESELECT3;RETURN语句RETURN语句用于结束程序执行，并返回值给调用程序语法：RETURN[expression]注意：RETURN只能用于函数中在存储过程、触发器和事件中，使用LEAVE语句例7-7RETURN语句示例：返回变量x和y中的最大值7.4.2WHILE循环WHILEcondition_expressionDO…ENDWHILE;当condition_expression为TRUE时，执行循环体；为FALSE时退出例7-8用WHILE计算1+3+5+7+…，直到和≥100：结果sum=100（可使用LEAVE语句跳出循环）例7-9嵌套WHILE循环计算sum=1!+2!+…+10!DELIMITER//CREATEFUNCTIONexample_fac()RETURNSintBEGINDECLAREsum,a,b,facint;SETsum=0;SETa=1;WHILEa<=10DO--外层：累加各阶乘例7-9嵌套WHILE循环（续）SETfac=1;SETb=1;WHILEb<=aDO--内层：计算a的阶乘SETfac=fac*b;SETb=b+1;ENDWHILE;SETsum=sum+fac;SETa=a+1;ENDWHILE;RETURNsum;END//REPEAT循环与LOOP循环REPEAT循环（先执行后判断）：REPEAT…UNTILconditionENDREPEAT例7-10：sum=1+2+…+100，结果5050LOOP循环（无条件，用LEAVE退出）：LOOP…ENDLOOP;例7-11：计算100以内奇数和，结果2500ITERATE：跳转到循环开始（与LEAVE的区别）7.5函数7.5.1数学函数常用数学函数：ABS(x)：绝对值CEIL(x)：向上取整FLOOR(x)：向下取整ROUND(x,y)：四舍五入到y位（y为负数时在小数点左边取整）SQRT(x)：平方根RAND()：0~1的随机数MOD(x,y)：取模例7-12：SELECTround(62.45613,3)→62.456例7-13：SELECTsqrt(121)→117.5.1字符串函数常用字符串函数：LENGTH(s)：字符串长度SUBSTRING(s,start,len)：取子串UPPER(s)/LOWER(s)：大小写转换TRIM(s)：去首尾空格CONCAT(s1,s2)：连接字符串CHAR(n)：返回ASCII码对应字符例7-14：SELECTsubstring('MySQL',3,3)→'SQL'；SELECTchar(65)→'A'7.5.1日期函数与系统函数常用日期函数：CURDATE()：当前日期NOW()：当前日期时间DAYNAME(d)：星期几ADDDATE(d,n)：加n天ADDTIME(t,n)：加时间例7-15：SELECTcurdate()→2024-12-26例7-17：SELECTadddate('2023-03-01',60)→2023-04-307.5.1加密函数与系统信息函数系统信息函数：DATABASE()：当前数据库名SYSTEM_USER()：当前连接用户VERSION()：MySQL版本加密函数：SHA1(str)：SHA1加密MD5(str)：MD5加密例7-21：SELECTsha1('p123456'),md5('p123456');7.5.2用户自定义函数——创建CREATEFUNCTIONname([func_parameter[,...]])RETURNStype[CHARACTERISTIC...]routine_body在MySQL8.0中需先设置：SETGLOBALlog_bin_trust_function_creators=TRUE;例7-22创建函数course_avg，根据课程代号返回该课程的平均成绩：CREATEFUNCTIONcourse_avg(course_idvarchar(10))RETURNSfloat...7.5.2用户自定义函数——调用与管理调用：SELECTfunction_name([parameter[,…]]);例7-23：SELECTcourse_avg('c001');→83.3091查看：SHOWCREATE{PROCEDURE|FUNCTION}name;修改：ALTERFUNCTIONname[CHARACTERISTIC...];删除：DROPFUNCTION[IFEXISTS]name;Workbench：右键Functions→Create/DropFunction7.6触发器7.6触发器概述触发器由INSERT、UPDATE、DELETE命令事件触发特定操作满足触发条件时，数据库系统自动执行触发器触发器的主要作用：（1）强化约束：比CHECK语句更复杂的约束（2）跟踪变化：侦测操作，禁止未经许可的更新（3）级联运行：自动影响其他表的数据7.6.1创建触发器CREATETRIGGER触发器名称BEFORE|AFTER触发事件ON表名FOREACHROWBEGIN执行语句列表ENDBEFORE/AFTER：触发时机（事件前/后）；FOREACHROW：每行触发7.6.2查看与删除触发器查看触发器：SHOWTRIGGERS;SHOWCREATETRIGGERtrigger_name;删除触发器：DROPTRIGGER[IFEXISTS]trigger_name;NEW和OLD：触发器的两个临时表，分别表示新数据和旧数据7.7存储过程7.7存储过程概述存储过程是包含程序流、逻辑及对数据库查询的一组SQL语句经过预编译后存储在数据库内，通过调用在服务器上执行存储过程的优点：（1）减少网络流量：减少客户端与服务器之间的数据传输（2）执行速度快：预编译，减少执行时的分析工作（3）安全性高：通过授权控制对存储过程的访问7.7.1创建存储过程CREATEPROCEDUREsp_name([parameter[,...]])[characteristics...]routine_body参数类型：IN（输入）、OUT（输出）、INOUT（输入输出）例7-30创建存储过程：CREATEPROCEDUREget_student_info(INsidchar(10))BEGINSELECT*FROMstudentWHEREid=sid;END//7.7.2调用与管理存储过程调用：CALLsp_name([parameter[,…]]);查看：SHOWCREATEPROCEDUREsp_name;修改：ALTERPROCEDUREsp_name[CHARACTERISTIC...];删除：DROPPROCEDURE[IFEXISTS]sp_name;存储过程与函数的区别：函数有RETURN返回值，存储过程用OUT参数输出结果7.8事件7.8事件概述事件（EventScheduler）：在指定时间执行SQL语句取代操作系统计划任务，在数据库内部实现定时任务事件与触发器的区别：触发器由数据操作事件触发（INSERT/UPDATE/DELETE）事件由时间调度触发（定时执行）7.8.2创建事件CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE]DOevent_body;例7-35每隔1分钟向student_count表中插入一条数据：CREATEEVENTe_student_countONSCHEDULEEVERY1MINUTE...7.8.3修改与删除事件修改事件：ALTEREVENTevent_name[ONSCHEDULE...][DOevent_body];临时关闭：ALTEREVENTe_student_countDISABLE;重新开启：ALTEREVENTe_student_countENABLE;删除事件：DROPEVENT[IFEXISTS]event_name;例7-37：DROP