第11章 MySQL编程基础

第11章MySQL编程基础学习目标：理解标识符的基本概念，掌握常量、变量、运算符与表达式、系统函数、注释、语句块及DELIMITER语句的使用方法，掌握自定义函数的基本操作方法，掌握控制流程语句的使用方法。11.1标识符标识符（Identifier）是用户编程时使用的名字，用于给数据库、表、列、变量、常量、函数、过程等对象命名，以便在其他地方引用。标识符必须符合标识符格式规则：以ASCII字符、Unicode字符、汉字、下画线_或@开头，后跟一个或多个ASCII字符、Unicode字符、汉字、下画线_、@或$，但不能都是下画线_、@或$。标识符的长度不能超过128个字符。11.2常量1．字符串常量【例11-1】使用SELECT语句输出3个字符串。SELECT"I'lltrymybest."ASaa,'That\'saterrificidea!'bb,'MySQL数据库管理系统';2．数值常量（1）整型常量（2）实型常量【例11-2】使用SELECT语句输出数据常量。SELECT30,+123,-51397,1.356,2.51,-0.071,+102.357,0.32E-5,+523E+3,-0.5638E-15;11.2常量3．日期和时间常量4．布尔值常量【例11-3】使用SELECT语句输出True和False。SELECTTrue,NOTFalse,10>20,10>20OR3<=5,1=0,NULL;

5．NULLNULL适用于各种列类型，通常表示“未知”“值不确定”“没有值”“无数据”等意义。11.2常量11.3变量11.3.1变量的三要素和变量的类型变量具有名字、数据类型和值三个属性。11.3变量11.3.2全局变量1．查看所有全局变量的值SHOWGLOBALVARIABLES;【例11-4】查看当前连接所有的全局变量。SHOWGLOBALVARIABLES;11.3变量2．指定显示某个全局变量的值。SELECT@@GLOBAL.var_name1[,@@GLOBAL.var_name2,…];【例11-5】显示指定的全局变量值。SELECT@@GLOBAL.character_set_client,@@GLOBAL.default_storage_engine,@@GLOBAL.version;11.3变量【例11-6】显示当前用户、当前日期、时间、时间戳的全局变量值。SQL语句和运行结果如下：SELECTCURRENT_USER,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP;11.3变量3．使用LIKE查看全局变量的值SHOWGLOBALVARIABLESLIKE'匹配模式字符串';【例11-7】查看全局变量名中包含有字符“slow”的全局变量名及其值。SQL语句和运行结果如下：SHOWGLOBALVARIABLESLIKE'%slow%';11.3变量4．设置全局变量SETGLOBALvar_name=value;SET@@GLOBAL.var_name=value;【例11-8】将全局变量connect_timeout的值设为3。SHOWGLOBALVARIABLESLIKE'connect_timeout';

SETGLOBALconnect_timeout=3;SHOWGLOBALVARIABLESLIKE'connect_timeout';11.3变量11.3.3会话变量1．查看所有会话变量的值SHOWSESSIONVARIABLES;2．指定显示某个会话变量的值SELECT@@[SESSION.]var_name1[,@@[SESSION.]var_name2,…];3．使用LIKE查看会话变量的值SHOWSESSIONVARIABLESLIKE'匹配模式字符串';11.3变量4．设置会话变量SET[SESSION]var_name=value;SET@@[SESSION.]var_name=value;【例11-9】查看指定的变量character_set_client，该变量既是全局变量，又是会话变量。SHOWSESSIONVARIABLESLIKE'character_set_client';11.3变量11.3.4用户会话变量1．定义用户会话变量（1）使用SET语句定义用户会话变量SET@user_variable1[:]=expression1[,@user_variable2[:]=expression2,…];SELECT@user_variable1[,@user_variable2,…];11.3变量【例11-10】使用SET语句定义和初始化用户会话变量@id、@name和@age，然后用SELECT语句输出该变量的值。SET@id='2023510103',@name='zhangsan',@age=19;SELECT@id,@name,@age;

SET@age=@age+3;SELECT@id,@name,@age;11.3变量（2）使用SELECT语句定义用户会话变量SELECT@user_variable1:=expression1[,@user_variable2:=expression2,…];11.3变量【例11-11】使用SELECT语句定义和初始化用户会话变量@x和@y。SELECT@x:=10; #这里的:=是赋值运算符

SELECT@x,@y;

SELECT@x=20,@y=@x; #这里的=是比较运算符

11.3变量（3）使用SELECT…INTO语句定义用户会话变量SELECTexpression1[,expression2,…]INTO@user_variable1[,@user_variable2,…];【例11-12】使用SELECTINTO语句定义用户会话变量@id、@name和@age并初始化。SELECT'2023510103','zhangsan',19INTO@id,@name,@age;SELECT@id,@name,@age;11.3变量2．用户会话变量在SQL语句中的使用3．用户会话变量的特点11.4运算符与表达式11.4.1运算符1．算术运算符11.4运算符与表达式2．比较运算符（1）等于（=）运算符【例11-13】使用等于（=）运算符判断两个值是否相等。SELECT3=3,'3'=3,3.0=3,'AbC'='aBc',(5/3)=1.666667,(5/2)=2.5,NULL=Null,NULL=0;11.4运算符与表达式（2）安全等于（<=>）运算符【例11-14】使用安全等于“<=>”运算符进行两个值是否相等的判断。SELECT3<=>3,'3'<=>3,'AbC'<=>'aBc',(5/2)<=>2.5,NULL<=>Null,NULL<=>0,100<=>NULL;11.4运算符与表达式（3）不等于（<>或!=）运算符（4）小于或等于（<=）运算符（5）小于（<）运算符（6）大于（>）运算符（7）大于或等于（>=）运算符（8）BETWEENAND11.4运算符与表达式【例11-15】使用BETWEENAND判定指定的数值是否在18～30之间。SELECT21BETWEEN18AND30,16BETWEEN18AND30;11.4运算符与表达式（9）IN（10）ISNULL11.4运算符与表达式3．逻辑运算符（1）“NOT”或“！”运算符【例11-16】使用“NOT”和“!”进行逻辑判断。SELECTNOT2+3,!2+3,!(2+3),!2-3,!2*3,!(2-3),NOTNULL,!NULL;11.4运算符与表达式（2）“AND”或“&&”运算符【例11-17】使用与运算符“AND”和“&&”进行逻辑判断。SELECT2AND-3,2&&-3,2AND0,1&&0,NOT1ANDNULL,1ANDNULL;11.4运算符与表达式（3）“OR”或“||”运算符【例11-18】使用或运算符“OR”和“||”进行逻辑判断。SELECT2OR-3,2||-3,2OR0OR-3,1||0,NOT1ORNULL,3ORNULL,NULLORNULL;11.4运算符与表达式（4）“ХOR”运算符【例11-19】使用异或运算符XOR进行逻辑判断。SELECT2XORNULL,2XOR3,0XOR0,0XOR3,10XOR20XOR30;11.4运算符与表达式11.4.2运算符的优先级11.4.3表达式1．按表达式值的类型分类2．按表达式的形式分类11.4运算符与表达式3．按值的形式分类（1）标量表达式（2）行表达式（3）表表达式4．按运算符的类型分类11.5系统函数11.5.1数值型函数【例11-20】数值型函数的使用实例。SELECTCEIL(-3.58),FLOOR(-3.58),ROUND(35.136,2),ROUND(35.136),RAND(),SIGN(-3.58);11.5系统函数【例11-21】二进制函数、十六进制函数的调用。SELECTBIN(127),OCT(127),HEX(127),BIN(2010),OCT(2010),HEX(2010);

11.5系统函数【例11-22】使用随机函数RAND()对查询结果集随机排序。SELECT*FROMstudent_db.studentORDERBYRAND();11.5系统函数11.5.2字符串函数1．获取字符串的长度和字节数【例11-23】分别使用CHAR_LENGTH()和LENGTH()计算字符串的长度。SELECTCHAR_LENGTH('SQL语句'),LENGTH('SQL语句');

11.5系统函数2．连接字符串【例11-24】把3个字符串连接成为一个字符串。SELECTCONCAT('MySQL','数据库','Serverversion8.0'),CONCAT('Good','Better','Best',NULL);

SELECTCONCAT_WS('--','MySQL','数据库','ver8.0'),CONCAT_WS('*','Good','Better','Best',NULL);11.5系统函数3．替换字符串【例11-25】把“I'maboy.”替换成“I'magirl.”。SELECTINSERT("I'maboy.",7,3,'girl'),REPLACE("I'maboy.",'boy','girl');11.5系统函数4．比较两个字符串的大小【例11-26】比较两个字符串。SELECTSTRCMP('ABC','ABB'),STRCMP('ABC','abc'),STRCMP('ABC','ABD');11.5系统函数11.5.3日期和时间函数日期和时间函数主要用于对日期和时间的值进行处理，日期和时间函数包括获取MySQL服务器当前日期或时间函数，获取日期或时间的某一部分的函数，时间和秒数之间的转换函数，日期间隔、时间间隔函数，日期和时间格式化函数等。11.5系统函数11.5.4延时函数SLEEP(duration)【例11-27】SLEEP()能够放在SELECT子句中，让每行记录都能执行指定的休眠时间。SELECTSLEEP(2),StudentID,StudentNameFROMstudent;DECLAREdelay;SETdelay=0;SELECTSLEEP(3)INTOdelay;11.5系统函数11.5.5流程控制函数11.5系统函数1.5.6加密函数加密函数主要用于对字符串进行加密和解密的处理。11.5系统函数11.5.7系统信息函数系统信息函数主要用于获取MySQL服务器的系统信息，包括MySQL版本号、登录服务器的用户名、主机地址、当前用户名和连接数、系统字符集等。11.5系统函数11.5.8数据类型转换函数1．CAST()CAST(valueASdatatype)【例11-28】把2023-10-5转换为DATETIME，把130、32.58转换为CHAR，把'23.596'转换为FLOAT，把-3*6转换为SIGNED，把6.3转换为UNSIGNED，将6.3转换为DECIMAL。SELECTCAST('2023-10-5'ASDATETIME),CAST(130ASCHAR),CAST(32.58ASCHAR(10)),CAST('23.596'ASFLOAT),CAST(-3*6ASSIGNED),CAST(6.3ASUNSIGNED),CAST(6.3ASDECIMAL(10,3));11.5系统函数2．CONVERT()CONVERT(value,type)【例11-29】把数值130、23.596转换为CHAR，把'12.893'转换为DECIMAL，把'2023-10-5'转换为日期，把10:56转换为时间。SELECTCONVERT(130,CHAR),CONVERT(23.569,CHAR(10)),CONVERT('12.893',DECIMAL(10,3)),CONVERT('2023-10-5',DATE),CONVERT('10:56',TIME);11.6注释、语句块及DELIMITER语句11.6.1注释1．单行注释#注释内容--注释内容2．多行注释/*注释内容注释内容*/11.6注释、语句块及DELIMITER语句11.6.2BEGIN…END语句块[label]BEGIN[statement1;][statement2;][…]END[label];11.6注释、语句块及DELIMITER语句11.6.3DELIMITER语句DELIMITER的语法格式：DELIMITER[{结束符|;}]11.7自定义函数11.7.1创建自定义函数CREATEFUNCTIONfunction_name([parameter1type1[,parameter2type2,…]])RETURNStype[characteristic…]BEGINfunction_body_statements;RETURNvalues;END;11.7自定义函数characteristic用于指定函数的特性参数，该参数的取值由一种或几种选项组合而成，语法格式：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'11.7自定义函数【例11-30】在数据库student_db中，创建计算长方体体积的函数fu_volume()，给定3条边的长度，返回体积。CREATEFUNCTIONfu_volume(aFLOAT,bFLOAT,cFLOAT)RETURNSFLOAT #指定返回值的数据类型DETERMINISTIC #表示结果是确定的BEGINRETURNa*b*c; #函数的返回值END;11.7自定义函数11.7自定义函数1.7.2调用自定义函数function_name([parameter1[,parameter2,…]])【例11-31】调用fu_volume()计算长、宽、高分别为20厘米、10厘米、30厘米的长方形体积。SELECTfu_volume(20,10,30);11.7自定义函数11.7.3查看函数1．查看所有函数SHOWFUNCTIONSTATUS;SHOWFUNCTIONSTATUSLIKE'匹配模式字符串';【例11-32】显示开头是fu的所有函数和函数名。SHOWFUNCTIONSTATUS;SHOWFUNCTIONSTATUSLIKE'fu%';11.7自定义函数2．查看指定函数（1）使用SHOWCREATEFUNCTION查看指定的函数SHOWCREATEFUNCTIONfunction_name;【例11-33】查看fu_volume()的详细信息。SHOWCREATEFUNCTIONfu_volume;11.7自定义函数（2）使用系统数据库information_schema的routines表查询指定的函数SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='function_name';【例11-34】查看fu_volume()的信息。SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='fu_volume';11.7自定义函数11.7.4修改自定义函数ALTERFUNCTIONfunction_name[characteristic…]【例11-35】修改自定义函数fu_volume()的特性，将DETERMINISTIC改为CONTAINSSQL。ALTERFUNCTIONfu_volumeCONTAINSSQL;11.7自定义函数1.7.5删除自定义函数DROPFUNCTION[IFEXISTS]function_name;【例11-36】删除fu_volume()。DROPFUNCTIONIFEXISTSfu_volume;11.8控制流程语句11.8.1局部变量1．局部变量的定义DECLAREvar_name1[,var_name2,…]data_type[DEFAULTvalue];11.8控制流程语句【例11-37】定义无参自定义函数fu_id()，在函数体中定义局部变量id、name、age。CREATEFUNCTIONfu_id() #创建无参自定义函数RETURNSCHAR(10) #指定返回值的数据类型DETERMINISTIC #返回确定值，本关键字不能省略BEGINDECLAREid,nameCHAR(10);DECLAREageINTDEFAULT18; RETURNid; END;调用自定义函数fu_id()，SQL语句和运行结果如下：SELECTfu_id();11.8控制流程语句2．局部变量的赋值（1）使用SET语句SETvar_name1=expr1[,var_name2=expr2,…];11.8控制流程语句【例11-38】新建fu_message()，通过参数传递id、name、age给该函数，返回id、name、age连接成的字符串。CREATEFUNCTIONfu_message(idCHAR(10),nameCHAR(10),ageINT)RETURNSCHAR(25) DETERMINISTIC BEGINDECLAREmessageCHAR(25);

SETmessage=CONCAT_WS(SPACE(5),id,name,CAST(ageASCHAR));RETURNmessage;END;SELECTfu_message('2023510101','刘一',19);11.8控制流程语句（2）使用SELECT…INTO语句SEL.ECTcol_name1[,col_name2,…]INTOvar_name1[,var_name2,…][FROMtable_name[WHEREcondition]];11.8控制流程语句【例11-39】在course表中，输入课程名，输出该课程编号、学分、课时。DROPFUNCTIONIFEXISTSfu_course_name;CREATEFUNCTIONfu_course_name(vCourseNameVARCHAR(20))RETURNSVARCHAR(255)DETERMINISTICBEGINDECLAREvCourseIDCHAR(10);

DECLAREvCreditINT;

DECLAREvCourseHourINT;

DECLAREmessageVARCHAR(255); SELECTCourseID,Credit,CourseHourINTOvCourseID,vCredit,vCourseHourFROMcourseWHERECourseName=vCourseName;

SETmessage=CONCAT_WS(SPACE(3),vCourseID,vCourseName,'学分：',CAST(vCreditASCHAR),'课时：',CAST(vCourseHourASCHAR));

RETURNmessage;END;11.8控制流程语句SET@cname='数据库原理'; SELECTfu_course_name(@cname)AS查到的课程信息;

SET@cname='数据库原理及应用'; SELECTfu_course_name(@cname)AS查到的课程信息; 11.8控制流程语句3．局部变量的应用场合使用DECLARE语句定义局部变量及对应的数据类型。局部变量必须定义在存储程序（函数、触发器、存储过程、事件）中，并且局部变量的作用范围仅局限于存储程序中，脱离存储程序，局部变量没有意义。11.8控制流程语句11.8.2条件控制语句1．IF语句IFsearch_condition1THENstatement_list1;[ELSEIFsearch_condition2THENstatement_list2;]…[ELSEstatement_list3;]ENDIF;11.8控制流程语句【例11-40】创建函数fu_getmax()，要求给定两个整数，输出较大的数。创建fu_getmax()，SQL语句如下：DROPFUNCTIONIFEXISTSfu_getmax;CREATEFUNCTIONfu_getmax(aINT,bINT)RETURNSINTDETERMINISTICBEGINDECLAREmaxINT;IFa>bTHENSETmax=a;ELSESETmax=b;ENDIF;RETURNmax;END;11.8控制流程语句SELECTfu_getmax(2,3),fu_getmax(30,20),fu_getmax(3,3);11.8控制流程语句【例11-41】DROPFUNCTIONIFEXISTSfu_getGrade;CREATEFUNCTIONfu_getGrade(vStudentIDCHAR(10),vCourseIDCHAR(6))RETURNSVARCHAR(255)DETERMINISTICBEGINDECLAREvScoreFLOAT; #成绩

DECLAREvGradeCHAR(1); #等级

DECLAREmessageVARCHAR(255); #返回的信息

SELECTScoreINTOvScoreFROMscoreWHEREStudentID=vStudentIDANDCourseID=vCourseID;IFvScore>=90THENSETvGrade='A';ELSEIFvScore>=80THENSETvGrade='B';ELSEIFvScore>=70THENSETvGrade='C';ELSEIFvScore>=60THENSETvGrade='D';ELSESETvGrade='E';ENDIF;SETmessage=CONCAT(vStudentID,SPACE(3),vCourseID,SPACE(3),CAST(vScoreASCHAR),'分',SPACE(3),vGrade,'级');RETURNmessage;END;11.8控制流程语句SET@stID='2023510103',@coID='300103';SELECTfu_getGrade(@stID,@coID);

SET@stID='2023510102',@coID='300103';SELECTfu_getGrade(@stID,@coID);11.8控制流程语句2．CASE语句（1）第1种形式的CASE语句CASEcase_exprWHENwhen_value1THENstatement_list1;[WHENwhen_value2THENstatement_list2;]…[ELSEstatement_list3;]ENDCASE;11.8控制流程语句【例11-42】DROPFUNCTIONIFEXISTSfu_getGrade1;CREATEFUNCTIONfu_getGrade1(scoreFLOAT)RETURNSCHAR(3)NOSQLBEGINDECLAREgradeCHAR(3); #等级

SETscore=ROUND(score/10); #把输入的成绩除以10按四舍五入取整，以判断分数范围

CASEscoreWHEN10THENSETgrade='优秀';WHEN9THENSETgrade='优秀';WHEN8THENSETgrade='良好';WHEN7THENSETgrade='中等';WHEN6THENSETgrade='及格';ELSESETgrade='不及格';ENDCASE;RETURNgrade;END;11.8控制流程语句SELECTfu_getGrade1(30),fu_getGrade1(59.5),fu_getGrade1(79.5),fu_getGrade1(80),fu_getGrade1(89.5),fu_getGrade1(100);11.8控制流程语句（2）第2种形式的CASE语句CASEWHENsearch_condition1THENstatement_list1;[WHENsearch_condition2THENstatementlist2;]…[ELSEstatement_list3;]ENDCASE;11.8控制流程语句【例11-43】DROPFUNCTIONIFEXISTSfu_getGrade2;CREATEFUNCTIONfu_getGrade2(vStudentIDCHAR(10),vCourseIDCHAR(6))RETURNSVARCHAR(255)DETERMINISTICBEGINDECLAREvScoreFLOAT; #成绩

DECLAREvGradeCHAR(1); #等级

DECLAREmessageVARCHAR(255); #返回的信息

DECLAREvSt1,vCo1CHAR(10);SELECTStudentIDINTOvSt1FROMscoreWHEREStudentID=vStudentIDLIMIT1;SELECTCourseIDINTOvCo1FROMScoreWHERECourseID=vCourseIDLIMIT1;CASEWHENvSt1ISNULLTHENSETmessage='没有该学生';WHENvCo1ISNULLTHENSETmessage='没有该课程的成绩';ELSESELECTScoreINTOvScoreFROMscoreWHEREStudentID=vStudentIDANDCourseID=vCourseID;CASEWHENvScore>=90THENSETvGrade='A';WHENvScore>=80THENSETvGrade='B';WHENvScore>=70THENSETvGrade='C';WHENvScore>=60THENSETvGrade='D';ELSE