《数据库技术及应用（MySQL）》课件 第6章 MySQL编程基础

MySQL数据库技术及应用第6章MySQL编程基础MySQL不仅可以实现数据的增、删、改和查功能，还可以使用它自己提供的系统函数、用户定义函数、变量和流程控制语句来编写程序。本章将围绕MySQL编程技术进行详细讲解。前言学习要点掌握常用的系统函数掌握用户自定义函数掌握变量的定义和赋值了解变量的作用域掌握流程控制语句第6章MySQL编程基础学习内容6.1函数6.2变量6.3流程控制语句第6章MySQL编程基础函数是由函数名、参数、返回值和函数体组成，来实现某一特定的功能。MySQL数据库提供了大量的、功能丰富的系统函数以及用户自定义函数供开发者使用。用户在进行数据库管理以及操作时，使用这些函数，可以使数据库功能更加强大、使用更加灵活、管理更加高效，以满足不同用户的需求。MySQL系统函数包括数学函数、字符串函数、日期和时间函数和系统信息函数等类型。第6章MySQL编程基础6.1函数6.1.1数学函数数学函数是用来处理数值运算。数学函数包括绝对值函数、三角函数(包含正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机函数等。在使用数学函数的过程中如果有错误产生，该函数将会返回空值NULL。第6章MySQL编程基础6.1函数6.1.1数学函数1.绝对值函数ABS()函数用来求绝对值。【例6-1】求-1.1，1.1及3的绝对值。SELECTABS(-1.1),ABS(1.1),ABS(3);2.圆周率函数PI()函数返回圆周率的值。【例6-2】返回圆周率的值。SELECTPI();第6章MySQL编程基础6.1函数6.1.1数学函数3.获取最小和最大整数函数CEIL(x)函数返回不小于x的最小整数，FLOOR(x)函数返回不大于x的最大整数。【例6-3】求不小于8.6的最小整数，不大于-7.9的最大整数。SELECTCEIL(8.6),FLOOR(-7.9);4.平方根函数SQRT(x)函数返回x的平方根值，要求x大于等于0。【例6-4】求5.4平方根。SELECTSQRT(5.4);第6章MySQL编程基础6.1函数6.1.1数学函数5.四舍五入函数ROUND(x,y)函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。【例6-5】将9.685保留后两位小数。SELECTROUND(9.68,1);6.幂运算函数POW(x,y)和POWER(x,y)函数功能相同，都是用于计算x的y次方。【例6-6】求-3的3次方，2.56的平方，90的立方根。SELECTPOW(-3,3),POW(2.56,2),POW(90,1/3);第6章MySQL编程基础6.1函数6.1.2字符串函数字符串函数主要用来处理字符串数据。字符串函数包括计算字符长度函数、字符串合并函数、字符串比较函数、查找指定字符串位置函数等。第6章MySQL编程基础6.1函数6.1.2字符串函数1.计算字符数和字符串长度的函数CHAR_LENGTH(str)函数的返回值为字符串附中所包含字符的个数。LENGTH(str)函数的返回值为字符串长度。【例6-7】求“hello”的长度，“MySQL8.0数据库”字符个数和长度。SELECTLENGTH('hello'),CHAR_LENGTH('MySQL8.0数据库'),LENGTH('MySQL8.0数据库');第6章MySQL编程基础6.1函数6.1.2字符串函数2.连接字符串函数CONCAT(sl,s2,···)函数的返回结果为连接参数产生的字符串。【例6-8】将“MongoDB,”与“MySQL数据库”两个字符串进行连接。SELECTCONCAT('MongoDB,','MySQL数据库');第6章MySQL编程基础6.1函数6.1.2字符串函数3.替换字符串函数INSERT(sl,x,len,s2)函数的返回结果sl，将字符串sl中起始于x位置、长度为len的子字符串替换为s2。如果x超过字符串长度，则返回值为原始字符串；如果len的长度大于x位置后字符串的总长度，则从位置x开始将s1内容全部替换为s2。【例6-9】将“DATABASEjxxs”字符串的后四位替换为“usersm”。SELECTINSERT('DATABASEjxxs,',9,6,'usersm');第6章MySQL编程基础6.1函数6.1.2字符串函数4.大小写字母转换函数LOWER(str)函数的返回结果是将str中含有大写字母的字符转化为小写字母；UPPER(str)函数的返回结果是将str中含有小写字母的字符转化为大写字母。【例6-10】将“CreateTable”字符串大写字母转化为小写字母，将“AlterEvent”字符串小写字母转化为大写字母。SELECTLOWER('CreateTable'),UPPER('AlterEvent');第6章MySQL编程基础6.1函数6.1.2字符串函数5.删除空格函数LTRIM(s)函数返回将s左侧的空格字符被删除；RTRIM(s)函数返回将s右侧的空格字符被删除的字符串；TRIM(s)函数返回将s两端都删除的字符串。【例6-11】分别将“firstsecond”的右侧，左侧和两端的空格删除。SELECTLTRIM('firstsecond'),RTRIM('firstsecond'),TRIM('firstsecond');第6章MySQL编程基础6.1函数6.1.2字符串函数6.比较两个字符串大小函数STRCMP(sl,s2)函数是比较s1和s2的大小。当s1大于s2时，返回1；当s1等于s2时，返回0；当s1小于s2时，返回-1；返回结果。【例6-12】比较“Compute”和“compute”；比较“Compute”和“Compute123”。SQL语句如下：SELECTSTRCMP('Compute','compute'),STRCMP('Compute','Compute123');第6章MySQL编程基础6.1函数6.1.2字符串函数7.截取字符串函数LEFT(str,len)函数返回从左侧截取str，且长度为len的子串；RIGHT(str,len)函数返回从右侧截取str，长度是len的字串。SUBSTRING(str,pos,len)函数返回从pos位置开始，截取长度为len的子串。【例6-13】分别截取“MongoDBRedisMySQL”右端的7个字符，左端的5个字符和从第8个位置开始的5个字符。SELECTLEFT('MongoDBRedisMySQL',7),RIGHT('MongoDBRedisMySQL',5),SUBSTRING('MongoDBRedisMySQL',8,5);第6章MySQL编程基础6.1函数6.1.2字符串函数8.获取子串第一次出现位置函数LOCATE(s1,str)函数返回s1第一次出现在str的位置。若str中不含有s1，则结果为0。【例6-14】分别显示“cu”和“数据库”在“cucumber”及“关系数据库，非关系型数据库”第一次出现的位置。SELECTLOCATE('cu','cucumber'),LOCATE('数据库','关系数据库，非关系型数据库');第6章MySQL编程基础6.1函数6.1.3日期时间函数MySQL中的日期和时间类型对于数据库管理至关重要，日期类型的值又会因不同场景而存储的格式不一致，在查询的时候，就需要经过转换到指定格式来查询想要的数据，这时候，日期函数就能带来极大的便捷。第6章MySQL编程基础6.1函数6.1.3日期时间函数1.获取系统当前日期和时间函数CURDATE()函数返回系统当前日期；CURTIME()函数返回系统当前时间；NOW()函数返回系统当前日期和时间。【例6-15】显示系统当前日期、时间和日期时间。SELECTCURDATE(),CURTIME(),NOW();第6章MySQL编程基础6.1函数6.1.3日期时间函数2.获取月份函数MONTH(date)函数返回date对应的月份；MONTHNAME(date)函数返回date对应的月份的英文名称。【例6-16】显示系统日期的月份及它的英文名称。SELECTMONTH(CURDATE()),MONTHNAME(CURDATE());第6章MySQL编程基础6.1函数6.1.3日期时间函数3.获取星期函数DAYOFWEEK(date)函数返回date对应的一周中的索引位置；DAYNAME(date)函数返回date对应星期的英文名称。【例6-17】显示“2024-02-09”是一周中的第几天及它的英文名称。SELECTDAYOFWEEK('2024-02-09'),DAYNAME('2024-02-09');第6章MySQL编程基础6.1函数6.1.3日期时间函数4.获取星期数函数WEEK(date)函数返回date对应的一年中的第几个星期。【例6-18】显示“2024-01-01”和“2024-12-31”是2024年的第几个星期。SELECTWEEK('2024-01-01'),WEEK('2024-12-31');第6章MySQL编程基础6.1函数6.1.3日期时间函数5.增加和减少日期函数DATE_ADD(date,INTERVALexprtype)函数返回date加上expr和type的时间，其中type可以是YEAR、DAY、HOUR、MINTUE和DAY_SECOND等。DATE_SUB(date,INTERVALexprtype)函数返回date减去expr和type的时间。【例6-19】显示系统当前时间加3天10分钟，显示“2024-05-0103:10:10”减20分钟30秒。SELECTDATE_ADD(CURDATE(),INTERVAL'3:02'DAY_HOUR)ASt1,DATE_SUB('2024-05-0103:10:10',INTERVAL'20:30'MINUTE_SECOND)ASt2;第6章MySQL编程基础6.1函数6.1.3日期时间函数6.计算两个日期间隔天数DATEDIFF(datel,date2)函数返回date1减去date2所得的相差天数。【例-20】计算“2024-06-30”与“2024-01-01”相差的天数。SELECTDATEDIFF('2024-06-30','2024-01-01');第6章MySQL编程基础6.1函数6.1.3日期时间函数7.日期时间格式化函数DATE_FORMAT(date,format)函数根据format指定的格式显示date值。format格式字符：%Y使用4位表示年份；%y使用2位表示年份。%M表示英文月份；%m使用2位表示月份。%d和%e表示一个月的第几天，%d使用前导0表示，%e无前导0表示。格式字符还有%W、%w和%H等。【例6-21】将“2024-12-01:12:20:30”显示格式设为年份2位，月份用英文，日无前导0。SELECTDATE_FORMAT('2024-12-01:12:20:30','%e,%M,%y')ASd1,DATE_FORMAT('2024-01-01:14:20:30','%y,%M,%e,%h,%w')ASd2;第6章MySQL编程基础6.1函数6.1.4系统信息函数MySQL的系统信息包含数据库的版本号、当前用户名和连接数、系统字符集和最后一个自动生成的值等。第6章MySQL编程基础6.1函数6.1.4系统信息函数1.获取字符串的字符集函数CHARSET(str)函数返回str字符串使用的字符集。【例6-22】显示“course”使用的字符集。SELECTCHARSET('course');第6章MySQL编程基础6.1函数6.1.4系统信息函数2.获取最后一个自动递增列的值函数LAST_INSERT_ID()函数用于获取自动递增列的值。【例6-23】创建member表，并将no字段设置为自动递增列，再添加三条记录，显示最后一条记录的Sno值。--创建表语句CREATETABLEmember(NOINTAUTO_INCREMENTNOTNULLPRIMARYKEY,NAMEVARCHAR(20));--添加记录语句INSERTINTOmemberVALUES(NULL,'王小');INSERTINTOmemberVALUES(NULL,'李惠');INSERTINTOmemberVALUES(NULL,'马茜');--执行上述语句后，查询最后一次执行INSERT语句增加记录时的自动自增列的值SELECTLAST_INSERT_ID();第6章MySQL编程基础6.1函数6.1.5自定义函数MySQL本身提供了内置函数，比如面提到过的聚合函数SUM()、AVG()以及日期时间函数等等，可是实际应用过程中会出现其他的需求是MySQL函数系统不能提供的。在MySQL中，可以使用CREATEFUNCTION语句来创建自定义函数。自定义函数是SQL的一个扩展，可以用来封装复杂的逻辑，简化查询。MySQL默认语句结束符为“;”，通常自定义函数使用多条语句组成来实现特定功能，每个语句之后都用分号，这就会导致错误，此时需要使用DELIMITER命令来设置一个新的结束符，语句结构如下。DELIMITER新结束符号--自定义函数--新结束符号DELIMITER;第6章MySQL编程基础6.1函数6.1.5自定义函数1.创建函数创建函数的基本语句结构如下。CREATEFUNCTIONfunc_name([func_parameter[,…]])RETURNSTYPE[CHARACTERISTICS][BEGIN]--func_bodyRETURNVALUE;[END]第6章MySQL编程基础6.1函数说明：·func_name：命名必须符合MySQL的标识符命名规则。·func_parameter：可选，由一个参数名称和数据类型组成，多个参数之间用逗号隔开。·RETURNSTYPE：指定返回值的数据类型。·[CHARACTERISTICS]：是一个可选部分，用于指定函数的一些额外属性或行为特性。CHARACTERISTICS这个词并不直接作为一个单独的关键字出现在创建函数的语法中，可以通过指定DETERMINISTIC、NOSQL、READSSQLDATA、MODIFIESSQLDATA、CONTAINSSQL等属性来定义函数的特性。·BEGIN…END：可选项，当在函数体中如果含有多条语句，需要把多条语句放到BEGIN...END语句块中。·value：返回值数据。·单行注释：使用#或者--。·多行注释：使用/*……*/。第6章MySQL编程基础6.1函数6.1.5自定义函数1.创建函数【例6-24】在jxxx数据库中，创建一个findname函数，通过参数id值在student表中查找学生姓名，然后输出Sname字符串的值。DELIMITER$$CREATEFUNCTIONfindname(idVARCHAR(255))RETURNSVARCHAR(255)DETERMINISTICBEGINRETURN(SELECTSnameFROMstudentWHERESno=id);END$$DELIMITER;第6章MySQL编程基础6.1函数6.1.5自定义函数2.调用函数调用函数基本语句结构如下。SELECTfunction_name(([expr[,…]]));【例6-25】调用findname函数。SELECTfindname(‘20231042');第6章MySQL编程基础6.1函数6.1.5自定义函数3.删除函数删除函数基本语句结构如下。DROPFUNTION[IFEXISTS]function_name;【例6-26】删除findname函数。DROPFUNCTIONIFEXISTSfindname;第6章MySQL编程基础6.1函数学习内容6.1函数6.2变量6.3流程控制语句第6章MySQL编程基础在MySQL8中，变量是存储数据的容器，它们可以是系统定义的，也可以是用户自定义的。这些变量用于控制数据库的行为、存储临时数据或作为查询的一部分。第6章MySQL编程基础6.2变量6.2.1变量定义变量定义的基本语句结构如下。DECLAREvar_name1,[var_name2,…]TYPE[DEFAULTVALUE]]说明：·var_name1：变量名，一次可以定义多个变量。·TYPE：变量数据类型，省略时变量的初值为NULL。·DEFAULTVALUE：设置变量的默认值，value可以是常数或表达式，省略时变量的初值为NULL。【例6-27】定义变量score和grade，类型为int，默认值为0。DECLAREscore,gradeINTDEFAULT0;第6章MySQL编程基础6.2变量6.2.2变量赋值变量在定义时若没有初始化，则需要SET语句给已定义变量赋值，其的基本语句结构如下。SETvar_name=expr[,var_name2=exp];说明：·expr：表达式，表达式值的类型必须和变量的类型一致。第6章MySQL编程基础6.2变量6.2.2变量赋值【例6-28】定义变量var1，var2，类型分别为int和char，将其初始化为“1”和“数据库”。DECLAREvar1INT;DECLAREvar2CHAR(30);SETvar1=1;SETvar2='数据库';第6章MySQL编程基础6.2变量6.2.2变量赋值还可以使用SELECT···INTO语句为变量赋值，其基本语句结构如下。SELECTcol_name[,…]INTOvar_name[,…]FROMtable_name[WEHREcondition];说明：·col_name：列名。·condition：指定查询的条件。【例6-29】定义变量var_cname，将课程号为“18130320”的课程名赋值给该变量。DECLAREvar_cnameCHAR(30);SELECTCnameINTOvar_CnameFROMcourseWHERECno='18130320';第6章MySQL编程基础6.2变量6.2.3系统变量系统变量是MySQL服务器内部使用的变量，它们控制服务器的操作特性。系统变量分为全局变量和会话变量。系统变量（无论是全局的还是会话的）都是由MySQL服务器管理和维护的，用于控制服务器的行为。全局变量影响整个MySQL服务器实例，而会话变量仅影响当前数据库会话。系统变量是MySQL系统提供并赋值的变量。全局变量以@@符号开头。用户不能建立或修改系统全局变量。影响整个MySQL服务器实例，通过配置文件或SETGLOBAL语句设置。第6章MySQL编程基础6.2变量6.2.3系统变量1.查看系统变量查看系统变量基本语句结构如下。SHOW[GLOBAL|SESSION|VARIABLES][LIKE'匹配模式'|WHILEexpr];说明：·GLOBAL：显示全局变量。·SESSION：默认修饰符，显示当前连接中有效的系统可变值，如果没有会话值，则显示全局变量值。第6章MySQL编程基础6.2变量6.2.3系统变量1.查看系统变量【例6-30】查看以admin_ssl_c开头的系统变量。SHOWVARIABLESLIKE'admin_ssl_c%’;使用SELECT语句查看系统变量是一种常见且直接的方法。通过SELECT语句，可以轻松地查询变量的值。查看全局系统变量的值，可以使用@@global.前缀，但通常对于全局变量，直接使用@@前缀而不指定global.也是可行的，因为MySQL会默认先查找全局变量。【例6-31】查看当前使用的MySQL的版本信息。SELECT@@version;第6章MySQL编程基础6.2变量6.2.3系统变量2.修改系统变量对于大多数全局变量，可以使用SETGLOBAL语句在运行时动态地更改它们的值，而无需重启MySQL服务。但是，请注意，并非所有全局变量都可以动态更改；有些变量需要重启MySQL服务才能更改其值。【例6-32】将全局的max_connections变量设置为1000。SETGLOBALmax_connections=1000;对于需要在MySQL服务启动时设置的变量，或者那些不能动态更改的变量，可以在MySQL的配置文件中进行设置。然后，重启MySQL服务以使更改生效。方法是找到MySQL的配置文件(f、my.ini或其他名称，具体取决于操作系统和MySQL安装方式)。在配置文件的[mysqld]部分下添加或修改变量设置。[mysqld]max_connections=1000第6章MySQL编程基础6.2变量6.2.4会话变量会话变量(SessionVariables)是仅在当前数据库会话中有效的变量。例如，想要更改当前会话的字符集为utf8mb4，可以这样做：SETNAMES'utf8mb4'COLLATE'utf8mb4_unicode_ci’;或者，分别设置字符集和校对规则：SETSESSIONcharacter_set_client=utf8mb4;SETSESSIONcollation_connection=utf8mb4_unicode_ci;SETSESSIONcharacter_set_results=utf8mb4;第6章MySQL编程基础6.2变量6.2.4会话变量用户自定义的变量，称为用户变量，仅对当前用户使用的客户端有效，以@符号开头，在定义时需要为该变量赋值。语句结构如下。SET@var_name=expr;或SELECT@var_name：=exprFROMtable_name[WHILE…];或SELECT…INTO@var_name;第6章MySQL编程基础6.2变量6.2.4会话变量【例6-33】分别使用SET、SELECT和SELECT…INTO给变量name，grade和specialtyname变量赋值，并显示以上变量的值。SET@name='张华';SELECT@grade:=GradeFROMscWHERESno='20031042'ANDCno='18032370';SELECTZnameFROMspecialtyLIMIT1INTO@specialtyname;SELECT@name,@grade,@specialtyname;第6章MySQL编程基础6.2变量6.2.5局部变量局部变量的作用范围仅在复合语句BEGIN和END语句之间，其在BEGIN和END之外无效。局部变量的定义和赋值与变量的定义和赋值语法完全相同。第6章MySQL编程基础6.2变量6.2.5局部变量【例6-34】创建函数func_mul，计算5*2的积，其中定义两个局部变量num1和num2，其值分别为5和2。并显示结果。DELIMITER$$CREATEFUNCTIONfunc_mul()RETURNSINTDETERMINISTICBEGINDECLAREnum1INT;DECLAREnum2INT;DECLAREmnumINT;SETnum1=5;SETnum2=2;SETmnum=num1*num2;RETURNmnum;END$$DELIMITER;SELECTfunc_mul();第6章MySQL编程基础6.2变量学习内容6.1函数6.2变量6.3流程控制语句第6章MySQL编程基础在MySQL中除了函数外，还可以使用流程控制语句根据特定的条件执行指定的语句。MySQL控制流程语句包括判断语句、循环语句和跳转语句等。第6章MySQL编程基础6.3流程控制语句6.3.1判断语句1.IF语句IF语句是根据不同的条件执行不同的语句，其语句结构如下。IFsearch_condition1THENstatement_list1[ELSEIFsearch_condition2THENstatement_list2]…[ELSEIFsearch_conditionnTHENstatement_listn][ELSEstatement_listn+1]ENDIF;第6章MySQL编程基础6.3流程控制语句6.3.1判断语句1.IF语句说明：·search_condition：条件表达式。·statement_list：由一个或多个SQL语句组成，且不为空。在上述语句结构中，当条件表达式1为真时，执行THEN后的语句列表1；当条件表达式1为假时，则判断条件表达式2是否为真，若为真，执行其对应的THEN后的语句列表2；依次类推，若所有的条件表达式都为假，则执行ELSE后的语句列表。第6章MySQL编程基础6.3流程控制语句6.3.1判断语句1.IF语句【例6-35】在jxxx数据库中，创建函数func_isPosOrNeg，判断变量num是否大于0，若大于输出1，反之，输出-1。DELIMITER$$CREATEFUNCTIONfunc_isPosOrNeg(numINT)RETURNSINTDETERMINISTICBEGINDECLAREXINTDEFAULT0;IFnum>0THENSETX=1;ELSESETX=-1;ENDIF;RETURNX;END$$DELIMITER;SELECTfunc_isPosOrNeg(15);第6章MySQL编程基础6.3流程控制语句6.3.1判断语句2.CASE语句CASE语句也是用来进行条件判断的，它提供了多个条件进行选择，可以实现比IF语句更复杂的条件判断，与Java中的SWITCH语句类似，关于CASE的语句结构如下。CASEsearch_conditionWHENsearch_condition1THENstatement_list1[WHENsearch_condition2THENstatement_list2]…[ELSEstatement_list]ENDCASE;第6章MySQL编程基础6.3流程控制语句6.3.1判断语句2.CASE语句【例6-36】在jxxx数据库中，创建函数func_exchange，依据变量值输出相应的结果。当变量值为1，输出“spring”，当变量值为2，输出“summer”，当变量值为3，输出“autumn”，当变量值为4，输出“winter”，若变量值以上都不是，则输出“error”。DELIMITER$$CREATEFUNCTIONfunc_exchange(numINT)RETURNSVARCHAR(20)DETERMINISTICBEGINDECLAREseasonVARCHAR(20)DEFAULT'';CASEnumWHEN1THENSETseason='spring';WHEN2THENSETseason='summer';WHEN3THENSETseason='autumn';WHEN4THENSETseason='winter';ELSESETseason='error';ENDCASE;RETURNseason;END$$DELIMITER;SELECTfunc_exchange(4);第6章MySQL编程基础6.3流程控制语句6.3.1判断语句2.CASE语句CASE语句除了上述的语句结构以外，还有另外一种格式如下。CASEWHENsearch_condition1THENstatement_list1[WHENsearch_condition2THENstatement_list2]…[ELSEstatement_list]ENDCASE;第6章MySQL编程基础6.3流程控制语句6.3.1判断语句2.CASE语句【例6-37】在jxxx数据库中，创建函数func_islevel，根据变量score值，判断属于哪一个等级，并将结果输出。当score值大于89，输出“优秀”；当score值大于79，输出“良好”；当score值大于69，输出“中等”；当score值大于59，输出“及格”；若score值以上都不是，则输出“不及格”。DELIMITER$$CREATEFUNCTIONfunc_islevel(scoreINT)RETURNSVARCHAR(30)DETERMINISTICBEGINDECLAREgradeVARCHAR(30)DEFAULT'';CASEWHENscore>89THENSETgrade='优秀';WHENscore>79THENSETgrade='良好';WHENscore>69THENSETgrade='中等';WHENscore>59THENSETgrade='及格';ELSESETgrade='不及格';ENDCASE;RETURNgrade;END$$DELIMITER;SELECTfunc_islevel(89);第6章MySQL编程基础6.3流程控制语句6.3.2循环语句1.LOOP语句LOOP语句只实现了一个简单的循环，并不进行条件判断。它本身没有停止循环的语句，必须使用LEAVE语句等才能停止循环，跳出循环过程。关于LOOP的语句结构如下。[begin_label:]LOOP--需要循环执行的语句

EXITWHEN条件;--当条件满足时退出循环ENDLOOP;第6章MySQL编程基础6.3流程控制语句6.3.2循环语句1.LOOP语句【例6-38】在jxxx数据库中，创建函数func_sum，输出1～100之间数字之和。