《数据库原理及应用教程-MySQL8.0》课件 第8章 MySQL编程

数据库原理与应用教程-MySQL8.0第8章MySQL编程第8章MySQL编程与程序设计语言相比，SQL语言具有高度非过程化的特点，而为了完成较复杂的操作，就需要进行编程，比如使用常量、变量、表达式、关键字等编写程序。MySQL提供称为流程控制的特殊关键字，用于控制SQL语句、语句块和存储过程的执行流。在数据库开发过程中，函数和存储过程起着很重要的作用，函数和存储过程都是由一个或多个SQL语句组成的子程序，可用于封装代码以便重复使用。8.1MySQL编程基础8.1.1标识符标识符是用来标识事物的符号，其作用类似于给事物取的名称。标识符分为合法标识符和引用标识符。1.合法标识符(1)可以由大小写形式的字母a-z、数字0-9、美元符号、下划线、以及范围在U+0080到U+FFFF之间的Unicode扩展字符构成。(2)可以是以上任何字符开头，但不允许全部是数字。(3)不能是MySQL关键字，关键字不区分大小写。8.1.1标识符2.引用标识符不符合合法标识符格式规则的标识符必须使用引用标识符，比如使用空格等特殊符号，使用MySQL关键字以及全部是数字。引用标识符括在反引号``中。8.1.2变量变量可以分为两大类：系统变量和自定义变量。1.系统变量

由系统定义，非用户定义，属于服务器层面。分为全局变量和会话变量。(1)全局变量全局变量由系统提供且预先声明，名称前加两个“@”符号。用户不能定义但可以使用全局变量，具有super权限的用户还可以对它们进行修改。(2)会话变量客户机连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会生成与该会话对应的会话变量。用户可修改，连接断开失效。8.1.2变量2.自定义变量(1)用户变量用户变量与数据库连接有关，如果数据库实例连接断开，变量就会消失。用户变量以”@”开始，形式为”@变量名”，只对当前用户使用的客户端生效。用户变量在定义和使用时要在其名称前加上标志“@”，使用SET或SELECT语句定义并赋值。8.1.2变量【例8-1】定义一个用户变量@CurrentDateTime，赋值为CURDATE()函数的值，最后输出变量@CurrentDateTime的值。SET@CurrentDateTime=CURDATE();SELECT@CurrentDateTimeAS'当前的日期和时间';8.1.2变量(2)局部变量局部变量是根据需要在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程或函数内的局部变量和输入参数局部变量的作用范围是声明它的BEGIN...END语句块内。为了与用户变量进行区分，局部变量不以”@”开头，它采用普通的标识符名称。8.1.2变量(2)局部变量局部变量是根据需要在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程或函数内的局部变量和输入参数局部变量的作用范围是声明它的BEGIN...END语句块内。为了与用户变量进行区分，局部变量不以”@”开头，它采用普通的标识符名称。8.1.3运算符运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。在MySQL系统中，可以使用的运算符可以分为算术运算符、逻辑运算符、赋值运算符、字符串串联运算符、按位运算符、一元运算符及比较运算符等。8.1.4注释两类注释符：(1)注释符“#”用于单行注释；(2)多行注释符“/*……*/”。USEteaching;#查询学生的学号和姓名SELECTsno,snameFROMstudent;/*查询所有男同学的学号、姓名和专业*/SELECTsno,sname,majorFROMstudentWHEREssex=’男’;8.2流程控制语句与所有的计算机编程语言一样，MySQL也提供了用于编写过程性代码的语法结构，可用来进行顺序、分支、循环、函数、存储过程等程序设计，编写结构化的模块代码，从而提高编程语言的处理能力。8.2.1SET语句一个SET语句可以定义并初始化多个变量。SET语句是顺序执行的，将一个表达式赋值给声明的变量，变量的类型就是表达式的数据类型。8.2.2BEGIN…END语句BEGIN…END语句能够将多个SQL语句组合成一个语句块，并将它们视为一个单元处理。语法格式：BEGIN

{sql_statement

|statement_block

}END其中，参数{sql_statement|statement_block}为任何有效的SQL语句或语句块。8.2.3DELIMITER语句在MySQL命令行客户端中，如果有一行命令以分号结束，回车后，MySQL将会执行该命令。但有时候，一段命令可能输入较多的语句，且语句中包含有分号，默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句，因为MySQL一遇到分号，就要自动执行。可以使用DELIMITER语句，把DELIMITER后面换成其它符号，如//、$$、;;等等，限制整段程序遇到其换成的符号时才执行。此语句多用在定义子程序、触发器等MySQL自己内嵌小程序中。8.2.4IF…ELSE语句语法格式：IFBoolean_expression/*条件表达式，可含有SELECT语句*/THEN{sql_statement}/*条件表达式为真时执行的语句*/[ELSE{sql_statement}]/*条件表达式为假时执行的语句*/ENDIF;IF…ELSE语句只能应用于MySQL子程序中，所以在后面的函数、存储过程、触发器等中再进行举例。8.2.5CASE语句使用CASE语句可以进行多个分支的选择。简单CASE格式：将某个表达式与一组简单表达式进行比较（等于），以确定结果。CASEinput_expressionWHENwhen_expression

THENresult_expression[...n

][

ELSEelse_result_expression]

END8.2.5CASE语句【例8-6】以简单CASE格式查询所有学生的专业情况，包括学号、姓名以及专业的英文名。USEteaching;SELECTsno,sname,CASEmajorWHEN'计算机科学与技术'THEN'ComputerScienceandTechnology'WHEN'信息安全'THEN'InformationSecurity'WHEN'通信工程'THEN'CommunicationEngineering'WHEN'软件工程'THEN'SoftwareEngineering'ELSE'ElectronicCommerce'ENDASmajorFROMstudent;8.2.5CASE语句搜索CASE格式：计算一组布尔表达式，以确定结果。CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

END8.2.5CASE语句【例8-7】以搜索CASE格式查询所有学生的考试等级，包括学号、课程号以及成绩级别(a、b、c、d、e)。USEteaching;SELECTsno,cno,

CASEWHENscore>=90then'a'WHENscore>=80then'b'WHENscore>=70then'c'WHENscore>=60then'd'WHENscore<60then'e'ENDASscore_levelFROMsc;8.2.6WHILE语句如果需要重复执行程序中的一部分语句，可使用WHILE循环语句实现。可使用BREAK和CONTINUE关键字在循环内部控制循环中语句的执行。语法格式为：WHILEBoolean_expressionessionDO/*条件表达式*/sql_statement|statement_block/*SQL语句或语句块构成的循环体*/ENDWHILE;8.3函数函数是由一个或多个SQL语句组成的子程序，可用于封装代码以便重复使用。SQL语言提供了丰富的数据操作函数，用以完成各种数据管理工作。MySQL允许用户创建自己的用户定义函数。8.3.1系统内置函数MySQL系统内置函数按函数的功能分类可分为系统信息函数、聚合函数、数学函数、字符串函数、日期和时间函数等类型。8.3.1系统内置函数1.数学函数可以满足数据库维护人员日常的数值计算需要【例8-8】求下列语句的执行结果。SELECTFLOOR(10.9),FLOOR(-10.9);执行结果为：10,-112.日期和时间函数用于进行时间方面的处理工作【例8-9】计算现在是几月。SELECTMONTH(CURDATE());8.3.1系统内置函数3．聚合函数聚合函数在结果集中通过对被选列值的收集处理并返回一个数值型的计算结果。4．字符串函数主要用来处理二进制类型的数据和文本类型的数据。【例8-10】将字符串“Iamastudent”以大写字母显示。SELECTUPPER('Iamastudent');8.3.1系统内置函数5.IF函数用于条件判断的函数，包括IF()和IFNULL()函数。（1）IF()函数IF(condition_expression,result_expression1,result_expression2)【例8-11】查询teaching数据库中的sc表，如果成绩小于60，则输出“不及格”，否则输出“成绩合格或为空”。USEteaching;SELECTsno,cno,score,IF(score<60,'不及格','成绩合格或为空')ASlevelFROMsc;8.3.1系统内置函数（2）IFNULL()函数IFNULL(expression1,experssion2)【例8-12】查询teaching数据库中的sc表，如果成绩不为为空，则显示成绩值，否则显示“缺考”。USEteaching;SELECTsno,cno,IFNULL(score,'缺考')AS成绩FROMsc;8.3.2用户定义函数用户定义函数可以针对特定应用程序问题提供解决方案。用户定义函数都是经过封装的SQL子程序，其他SQL代码可以调用这些子程序来返回单一的值。1.函数的创建CREATEFUNCTIONfunction_name/*函数名部分*/

([{parameter_namepar_data_type}[,...n]])/*形参定义部分*/RETURNSreturn_data_type/*返回参数的类型*/BEGINfunction_body;/*函数体部分*/

RETURNexpression;/*返回语句*/END8.3.2用户定义函数【例8-13】求sc表中某门课的平均成绩。USEteaching;DELIMITER//CREATEFUNCTIONaverage(cnchar(4))RETURNSfloatBEGINDECLAREaverfloat;SETaver=(SELECTavg(score)FROMscWHEREcno=cn);RETURNaver;END//8.3.2用户定义函数2.查看函数可以通过可视化管理工具的菜单界面、也可以通过SQL语句查看用户自定义函数。3.调用函数在其它程序模块中可以调用函数，语法格式：SELECTfunction_name(parameter_expression1…parameter_expressionn);【例8-15】求C001号课的平均成绩。USEteaching;SELECTaverage('C001')AS'C001号课程的平均成绩';8.3.2用户定义函数【例8-16】创建函数，显示两门课中平均成绩高的那门课的课程号。USEteaching;DELIMITER//CREATEFUNCTIONaverage1(cn1char(4),cn2char(4))RETURNSchar(4)BEGINDECLAREaver1,aver2float;SETaver1=(SELECTavg(score)FROMscWHEREcno=cn1);SETaver2=(SELECTavg(score)FROMscWHEREcno=cn2);8.3.2用户定义函数IFaver1>=aver2/*IF…ELSE语句的应用*/THENRETURNcn1;ELSERETURNcn2;ENDIF;END//【例8-17】判断C001和C002号课哪个平均成绩高。USEteaching;SELECTaverage1('C001','C002')AS'平均成绩高的课程';8.3.2用户定义函数4.删除函数可以通过可视化管理工具的菜单界面、也可以通过SQL语句删除用户自定义函数。【例8-18】通过DROPFUNCTION语句删除average函数。USEteaching;DROPFUNCTIONaverage;8.4存储过程存储过程就是一个具有独立功能的子程序，以特定的名称存储在数据库中。8.4.1存储过程概述存储过程是SQL语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。存储过程是一种独立存储在数据库内的对象，可以接受输入参数、输出参数，可以返回单个值或多个结果集，也可以完成数据库中数据更新，由应用程序通过调用执行。8.4.1存储过程概述存储过程的优点：(1)模块化程序设计。只需创建一次，可在程序中调用任意次。可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。

(2)加快SQL语句的执行速度。创建存储过程时对其进行分析和优化并预先编译好放在数据库内，减少编译语句所花的时间。(3)减少网络流量。一条执行过程代码的单独语句。(4)更高地安全性。可以授予用户执行存储过程的权限，而不授予其直接访问数据库对象（表、视图等）的权限。8.4.2创建存储过程MySQL创建存储过程的SQL语句语法格式如下：CREATEPROCEDUREprocedure_name([IN|OUT|INOUT][parameter_namedata_type][,…n])BEGINprocedure_body;END8.4.2创建存储过程【例8-19】在teaching库创建无参存储过程，查询每个学生的平均成绩。USEteaching;DELIMITER//CREATEPROCEDUREstudent_avg()BEGINSELECTsno,avg(score)as'avgscore'FROMscGROUPBYsno;END//8.4.2创建存储过程【例8-20】在teaching库创建带输入参数的存储过程，查询某个学生的基本信息。USEteaching;DELIMITER//CREATEPROCEDUREGetStudent(numberchar(10))BEGINSELECT*FROMstudentWHEREsno=number；END//8.4.2创建存储过程【例8-21】在teaching库创建带输入参数的存储过程，修改某个学生某门课的成绩。USEteaching;DELIMITER//CREATEPROCEDUREUpdate_score(numberchar(10),cnchar(4),sc1int)BEGINUPDATEscSETscore=sc1WHEREsno=numberandcno=cn;END//8.4.2创建存储过程【例8-22】在bankcard数据库中使用流程控制语句创建存储过程。假设今天银行有活动，如果今天某账号交易支出总金额超过3000元，则奖励其10元。USEbankcard;DELIMITER//CREATEPROCEDUREadd_10(ANOchar(20))BEGINIF(SELECTSUM(Expense)FROMTrecordWHERETDate=CURDATE()ANDAccNO=ANO)>=3000THENUPDATEAccountSETBalance=Balance+10WHEREAccNO=ANO;INSERTINTOTrecord(TDate,AccNO,Income,Abstract)VALUES(CURDATE(),ANO,10,'银行活动奖励');ENDIF;END//8.4.2创建存储过程【例8-23】在bankcard数据库创建带OUT参数的存储过程，用于计算指定的储户的总余额，存储过程中使用一个输入参数（身份证号）和两个输出参数（储户姓名和总余额）。USEbankcard;DELIMITER//CREATEPROCEDUREs_balance(INidchar(18),OUTdnvarchar(10),OUTsbalafloat)BEGINSELECTDnameINTOdnFROMdepositorWHEREIDNO=id;SELECTSUM(Balance)INTOsbalaFROMaccount WHEREIDNO=id;END//8.4.2创建存储过程【例8-24】创建存储过程，返回两门课中平均成绩高的那门课的课程号和平均成绩。USEteaching;DELIMITER//CREATEPROCEDUREaverage2(INOUTcn1char(4),INcn2char(4),OUTavgscoredecimal(3,1))BEGINDECLAREaver2decimal(3,1);SETavgscore=(SELECTavg(score)FROMscWHEREcno=cn1);SETaver2=(SELECTavg(score)FROMscWHEREcno=cn2);IFaver2>avgscoreTHENSETcn1=cn2;SETavgscore=aver2;ENDIF;END//8.4.3调用存储过程调用即执行存储过程，可以使用CALL命令。对于存储过程的所有者或任何一名对此过程拥有CALL权限的用户，都可以执行此存储过程。如果被调用的存储过程需要参数输入时，在存储过程名后逐一给定，每一个参数用逗号隔开，参数的排列必须和建立存储过程所定义的次序对应。(1)无参存储过程的调用语句CALL

procedure_name[()]；(2)有参存储过程的调用语句CALL

procedure_name(parameter[,...]);8.4.3调用存储过程【例8-25