MySQL数据库应用项目教程 课件 项目5 数据库编程

MySQL数据库应用

项目教程项目五数据库编程5.1SQL编程基础

5.2存储过程和存储函数5.3触发器、事务和锁

5.1SQL编程基础

5.1.1SQL语言基础SQL语言是一系列操作数据库及数据库对象的命令语句，因此必须了解基本语法和流程语句的构成，主要包括常量和变量、表达式、运算符、控制语句等。1.常量和变量MariaDB的体系结构封装了SQL接口、查询解析器、查询优化器、查询执行引擎、缓存/缓冲机制以及一个插件式存储引擎。(1)常量。常量也称为文字值或标量值,是指程序运行中值始终不变的量。在SQL程序设计过程中,常量的格式取决于它所表示的值的数据类型。十进制整型常量、十六进制整型常量、实型常量、字符串常量、日期常量、布尔常量、位字段值、NULL值5.1SQL编程基础5.1.1SQL语言基础1.常量和变量(2)变量。变量就是在程序执行过程中,值是可以改变的量①全局变量和会话变量。显示系统变量可以使用SHOWVARIABLES语句,语法格式:SHOW[GLOBAL|SESSION]VARIABLES[LIKE‘字符串’];【例1】查看系统变量。查询所连接的MariaDB数据库的所有全局变量。SHOWGLOBALVARIABLES;查询所连接的MariaDB数据库中以a开头的会话变量。SHOWVARIABLESLIKE‘a%’;5.1SQL编程基础5.1.1SQL语言基础1.常量和变量【例2】修改系统变量的操作。查询全局变量sort_buffer_size的值,然后将其值增加5000并再次查看。SELECT@@GLOBAL.sort_buffer_size;SET@@GLOBAL.sort_buffer_size=@@GLOBAL.sort_buffer_size+5000;SELECT@@GLOBAL.sort_buffer_size;当前会话变量sql_select_limit决定了SELECT语句的结果集中的最大行数。查看当前会话变量sql_select_limit的值,并将其值修改为500。SELECT@@SESSION.sql_select_limit;SET@@SESSION.sql_select_limit=500;SELECT@@SESSION.sql_select_limit;SELECT@@GLOBAL.sql_select_limit;5.1SQL编程基础5.1.1SQL语言基础1.常量和变量②用户变量。用户变量指的是用户自己定义的变量。定义和初始化一个用户变量使用SET

语句或SELECT语句。语法格式:SET<@用户变量名>[∶]=<表达式>[,<@用户变量名>[∶]=<表达式>,...];SELECT<@用户变量名>∶=<表达式>[,<@用户变量名>∶=<表达式>,...];5.1SQL编程基础5.1.1SQL语言基础1.常量和变量③局部变量。局部变量的作用范围是在BEGIN...END语句块中,可以使用DECLARE语句进行定义,然后为变量进行赋值。在Mar-iaDB中,定义局部变量的语法格式:DECLARE<局部变量名>[,...]<数据类型>[DEFAULTvalue];【例3】局部变量的操作。定义局部变量myvar,数据类型为INT,默认值为5。DECLAREmyvarINTDEFAULT5;为局部变量赋值50。SETmyvar=50;5.1SQL编程基础5.1.1SQL语言基础2.运算符MariaDB支持使用运算符。通过运算符,不仅可以使数据库的功能更加强大,而且可以更加灵活地使用数据表中的数据。MariaDB运算符包括四类,分别是算术运算符、比较运算符、逻辑运算符和位运算符。(1)算术运算符。算术运算符是SQL语言中最常用的运算符,主要是对数值运算使用的。算术运算符主要包括加、减、乘、除和取余五种。(2)比较运算符。比较运算符主要用于比较两个表达式的值,其运算结果为逻辑值,有1(真)、0(假)及NULL(不能确定)三种情况。运算符<>和!=用来判断数字值、字符串、表达式等是否不相等。如果不相等,结果返回1;如果相等,结果返回0。5.1SQL编程基础5.1.1SQL语言基础2.运算符【例4】算术运算符和比较运算符的操作。使用算术运算符对教学管理数据库teaching_management中的学生选修课程信息表student_course的学生成绩score字段值进行加、减、乘、除和求余运算。SELECTscore,score+10,score-10,score∗10,score/10,score%10FROMteaching_management.student_course;运用运算符<>和!=判断教学管理数据库teaching_management中的学生选修课程信息表student_course的学生成绩score字段值是否等于80。SELECTstudent_no,score<>80,score!=80FROMteaching_management.student_course;5.1SQL编程基础5.1.1SQL语言基础2.运算符(3)逻辑运算符。逻辑运算符又称布尔运算符,用来判断表达式的真假。逻辑运算符的返回结果只有1和0。如果表达式是真,结果将返回1;如果表达式是假,结果将返回0。MariaDB中支持与、或、非和异或四种逻辑运算符。(4)位运算符。位运算符是使用二进制数进行计算的运算符。位运算会先将操作数变成二进制数,然后进行位运算,最后再将计算结果从二进制数转换为十进制数。在MariaDB中有六种位运算符,分别是按位与、按位或、按位取反、按位异或、按位左移和按位右移。(5)运算符的优先级。运算符的优先级决定了不同的运算符在表达式中计算的先后顺序。5.1SQL编程基础5.1.1SQL语言基础3.SQL流程控制语句在SQL语言中,流程控制语句就是用来控制程序执行流程的语句,也称流控制语句或控制流语句。在MariaDB中,这些流程控制语句和局部变量只能在存储过程或函数、触发器或事务的定义中出现。(1)BEGIN...END语句块。语法格式:[begin_label:]BEGIN[statement_list]END[end_label];5.1SQL编程基础5.1.1SQL语言基础3.SQL流程控制语句【例5】编写一个简单代码块。

DELIMITERCREATEPROCEDUREexample_begin()BEGINDECLARExTINYINTUNSIGNEDDEFAULT1;BEGIN DECLARExTINYINTUNSIGNEDDEFAULT2;DECLAREyTINYINTUNSIGNEDDEFAULT10;SELECTx,y;/∗查询局部变量x,y的值∗/END;SELECTx;END;DELIMITER;CALLexample_begin();5.1SQL编程基础(2)IF...ELSE条件语句。它可以指定SQL语句的执行条件。如果条件为真,则执行条件表达式后面的SQL语句;如果条件为假,则可以用ELSE关键字指定要执行的SQL语句。语法格式:IF<表达式>THENstatement_list[ELSEIF<表达式>THENstatement_list]...[ELSEstatement_list]ENDIF;5.1.1SQL语言基础3.SQL流程控制语句【例6】查询康养学院的办公室位置。如果查询结果为空,则显示“办公地点不详”,否152则显示其办公地点。DELIMITER//CREATEPROCEDUREexample_if()BEGINUSEteaching_management;IF(SELECTofficeFROMdepartment_infoWHEREname=‘康养学院’)ISNULLTHEN SELECT‘办公地点不详’AS办公地点;ELSE SELECTofficeFROMteaching_management.department_infoWHEREname=‘康养学院’;ENDIF; END; DELIMITER;CALLexample_if();5.1SQL编程基础5.1.1SQL语言基础3.SQL流程控制语句(3)CASE分支语句。CASE关键字可根据表达式的真假来确定是否返回某个值,允许表达式的任何位置使用这一关键字。使用CASE语句可以进行多个分支的选择,CASE语句具有如下两种格式。

①简单格式。将某个表达式与一组简单表达式进行比较以确定结果。CASEcase_value WHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE;②搜索格式。计算一组布尔表达式以确定结果。CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE;5.1SQL编程基础5.1.1SQL语言基础3.SQL流程控制语句(4)循环语句。在SQL语言中主要有三个标准的循环方式:LOOP、REPEAT和WHILE。①LOOP循环。语法格式:begin_label:LOOP [statement_list]; LEAVEbegin_label; [statement_list]; [ITERATEbegin_label;][statement_list];ENDLOOP[end_label];5.1SQL编程基础5.1.1SQL语言基础3.SQL流程控制语句【例7】计算1+2+3+…+n。通过IF语句判断退出循环的条件,达到退出条件时使用LEAVE语句退出循环。DELIMITERCREATEPROCEDUREexample_loop(nint)BEGIN DECLAREsum,iINTDEFAULT0; loop_label:LOOP SETsum=sum+i; IFi=nTHEN LEAVEloop_label; ENDIF;SETi=i+1;5.1SQL编程基础ENDLOOPloop_label;SELECTsum;END;DELIMITER;CALLexample_loop(100);5.1.1SQL语言基础3.SQL流程控制语句②REPEAT循环。语法格式:begin_label:REPEAT[statement_list];LEAVEbegin_label; [statement_list];[ITERATEbegin_label;][statement_list];UNTILsearch_conditionENDREPEAT[end_label];5.1SQL编程基础③WHILE循环。语法格式:begin_label:WHILEsearch_conditionDO [statement_list]; LEAVEbegin_label; [statement_list]; [ITERATEbegin_label;] [statement_list];ENDWHILE[end_label];5.1.1SQL语言基础3.SQL流程控制语句【例8】使用WHILE循环计算1到n之间奇数的和。

DELIMITER//CREATEPROCEDUREexample_while(nint)BEGIN DECLAREsum,iINTDEFAULT0;while_label: whilei<nDO SETi=i+1; IFmod(i,2)=1THEN SETsum=sum+i; ENDIF; ENDwhile_label; SELECTsum;END; DELIMITER;CALLexample_while(10);5.1SQL编程基础5.1.1SQL语言基础4.条件和处理程序的定义(1)定义条件。可以使用DECLARE语句定义条件。语法格式:DECLAREcondition_nameCONDITIONFORcondition_value(2)定义处理程序。语法格式:DECLAREhandler_typeHANDLERFORcondition_value[,condition_value]statement5.注释(1)单行注释。①在行前使用#。②在行前使用--(2)多行注释。使用/∗∗/注释。5.1SQL编程基础5.1.2常用函数1.字符串函数字符串函数用于控制字符串返回满足用户需求的位置,这些功能仅用于字符型数据。ASCII(char)、LENGTH(s)、CHAR_LENGTH(s)、CONCAT(s1,s2,...)、UPPER(s)、CONCAT_WS(sep,s1,s2,...)、INSERT(s1,x,len,s2)、LOWER(s)、LEFT(s,n)、TRIM(s)、RIGHT(s,n)等2.数学函数

常用的数学函数有：ABS(x)函数、FLOOR(x)函数、CEILING(x)函数、GREATEST()函数、LEAST()函数、ROUND(x)函数、TRUNCATE(x,y)函数、RAND()函数、RAND(x)函数、SQRT(x)函数、MOD(x,y)函数5.1SQL编程基础5.1.2常用函数3.日期和时间函数

常用日期和时间函数的操作有：CURDATE()函数、CURTIME()函数、DATE_ADD()函数、DATE_SUB()函数、DAYOFWEEK(d)函数、DAYOFMONTH(d)函数、DAYOFYEAR(d)函数、HOUR(t)函数、MINUTE(t)函数、SECOND(t)函数、⑤NOW()函数、DATEDIFF()函数、4.系统函数

常用的系统函数：DATABASE()函数、USER()函数、VERSION()函数、CHARSET(str)函数COLLATION(str)函数5.1SQL编程基础5.1.3游标1.声明游标在MariaDB中,游标主要包括游标结果集合和游标位置两部分。语法格式:DECLAREcursor_nameCURSORFORselect_statement;2.游标操作(1)打开游标。语法格式:OPENcursor_name;例如，打开先前定义的游标course_cursor。OPENcourse_cursor;(2)检索游标。语法格式:FETCHcursor_nameINTOvar_name[,var_name,...];(3)关闭游标关闭游标的语法格式:CLOSEcursor_name;5.1SQL编程基础5.2存储过程和存储函数

5.2.1熟悉存储过程和存储函数1.声明游标存储过程是一组可以完成特定功能的SQL语句的集合,经编译后以一个名称存储在数据库中。存储函数是一个存储的程序,用于接受参数并获取该程序执行后的返回值。存储过程和存储函数的优缺点存储过程和存储函数的缺点。5.2存储过程和存储函数5.2.1熟悉存储过程和存储函数2.存储过程和存储函数参数存储过程和存储函数能接受输入参数,并把参数返回给调用方。参数传递信息的关键字5.2存储过程和存储函数5.2.2创建存储过程和存储函数1.创建存储过程(1)使用CREATEPROCEDURE语句创建存储过程,语法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}]PROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_body使用ORREPLACE子句，判断将要创建的存储过程是否存在。DEFINER子句指定存储过程执行时,检查权限所使用的上下文。sp_name参数表示创建的存储过程的名称proc_parameter允许使用空参数列表,如果给定了参数,则参数名不区分大小写。routine_body部分包含有效的SQL语句。5.2存储过程和存储函数5.2.2创建存储过程和存储函数1.创建存储过程【例9】创建存储过程。创建统计教学管理数据库中学生信息表的学生数的简单存储过程,并使用OUT参数输出学生人数。DELIMITER//CREATEPROCEDUREsp_student_num(OUTs_numINT)BEGIN

SELECTCOUNT(∗)INTOs_numFROMteaching_management.student_in-fo;END;//DELIMITER;

CALLsp_student_num(@student_num);SELECT@student_num;5.2存储过程和存储函数5.2.2创建存储过程和存储函数2.创建存储函数(1)使用CREATEFUNCTION语句创建存储函数。使用CREATEFUNCTION语句,必须要具备CREATEROUTINE权限。语法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}][AGGREGATE]FUNCTION[IFNOTEXISTS]func_name([func_parameter[,...]])RETURNStype[characteristic...]RETURNfunc_body

5.2存储过程和存储函数5.2.2创建存储过程和存储函数2.创建存储函数【例10】通过一个参数传入值,执行一个SQL内置函数CONCAT(),返回一个新的字符串。DELIMITER//CREATEFUNCTIONteaching_management.func_string(sCHAR(20))

RETURNSCHAR(50)DETERMINISTIC

RETURNCONCAT(‘Hello,’,s,‘!’);//DELIMITER;SELECTfunc_string(‘world’);5.2存储过程和存储函数5.2.2创建存储过程和存储函数2.创建存储函数【例11】创建一个计数器函数,使用一个临时表来存储当前的值。可以在函数内部使用一个语句块来操作数据(即使用DML),如INSERT语句和UPDATE语句。CREATETEMPORARYTABLEcounter(cINT);INSERTINTOcounterVALUES(0);DELIMITER//CREATEFUNCTIONfunc_counter()RETURNSINT

BEGIN

UPDATEcounterSETc=c+1;

RETURN(SELECTcFROMcounterLIMIT1);

END;//DELIMITER;SELECTfunc_counter();5.2存储过程和存储函数5.2.2创建存储过程和存储函数2.创建存储函数(2)利用HeidiSQL图形界面创建存储函数。利用HeidiSQL图形界面创建存储函数的步骤与创建存储过程相似。5.2存储过程和存储函数3.调用存储过程和存储函数存储函数的调用与MariaDB内部函数的调用方式相同。(1)在HeidiSQL图形界面调用存储过程和存储函数。利用HeidiSQL图形界面调用存储过程和存储函数的方法非常简单。(2)使用SQL语句调用存储过程和存储函数。①调用存储过程。语法格式:CALLsp_name([parameter[,...]]);②调用存储函数。如：调用存储函数func_teacher。SELECTfunc_teacher(‘T01’);5.2.3管理存储过程和存储函数1.查看存储过程和存储函数(1)利用SHOWSTATUS语句查看。语法格式:SHOW[PROCEDURE|FUNCTION]STATUS[LIKE‘pattern’];(2)利用SHOWCREATE语句查看。语法格式:SHOWCREATE[PROCEDURE|FUNCTION]<sp_name|func_name>;(3)从information_schema.ROUTINES数据表查看存储过程或存储函数的信息。SELECT∗FROMinformation_schema.ROUTINES[WHEREROUTINE_NAME{=‘sp_name|func_name’|LIKE‘pattern’}];5.2存储过程和存储函数5.2.3管理存储过程和存储函数1.查看存储过程和存储函数【例12】查看存储过程或存储函数的操作。查看存储过程sp_count_test的基本信息。SHOWPROCEDURESTATUSsp_count_test;查看存储函数func_teacher_department的基本信息。SHOWCREATEFUNCTIONfunc_teacher_department;从information_schema.ROUTINES数据表查看存储函数func_counter的信息。SELECT∗FROMinformation_schema.ROUTINESWHEREROUTINE_NAME=‘func_counter’;5.2存储过程和存储函数5.2.3管理存储过程和存储函数2.修改存储过程和存储函数语法格式:ALTER[PROCEDURE|FUNCTION]<sp_name|func_name>[characteristic...];【例13】修改存储过程或存储函数的操作。查询information_schema.ROUTINES数据表中存储过程sp_count_test的信息。SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPEFROMinfor-mation_schema.ROUTINESWHEREROUTINE_NAME=‘sp_count_test’;sp_count_test(‘S01’);修改存储过程sp_count_test。ALTERPROCEDUREsp_count_testModifiesSQLdataSQLSECURITYIN-VOKER;5.2存储过程和存储函数5.2.3管理存储过程和存储函数3.删除存储过程和存储函数使用DROP语句可以删除存储过程或存储函数，语法格式:DROP[PROCEDURE|FUNCTION][IFEXISTS]<sp_name|func_name>;例如,删除存储过程sp_count_test的语法格式:DROPPROCEDUREIFEXISTSsp_count_test;5.2存储过程和存储函数5.3触发器、事务和锁

5.3.1触发器1.创建触发器触发器(Trigger)是特殊的存储过程,它基于一个数据表创建。(1)利用SQL语句创建触发器。语法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}]TRIGGER[IFNOTEXISTS]trigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_stmt5.3触发器、事务和锁5.3.1触发器1.创建触发器【例14】触发器的创建操作。在teaching_management数据库中创建一个INSERT触发器tr_class_num。USEteaching_management;DELIMITER//CREATEDEFINER=`root`@`localhost`TRIGGERtr_class_num

AFTERINSERTONstudent_infoFOREACHROW

BEGIN

UPDATEclass_infoSETnumber=number+1WHEREno=NEW.class_no;

END;//DELIMITER;5.3触发器、事务和锁5.3.1触发器1.创建触发器(2)利用HeidiSQL图形界面创建触发器。【例15】创建一个触发器tr_update_sno,当student_info数据表中学生的学号发生变更时,同时更新student_course数据表中相应的学生的学号。5.3触发器、事务和锁5.3.1触发器2.管理触发器(1)查看触发器。①用SHOWTRIGGERS语句查看触发器信息。语法格式:SHOWTRIGGERS;②在information_schema.TRIGGERS数据表中查看触发器的信息。(2)删除触发器。语法格式:DROPTRIGGER[db_name.]trigger_name;如：删除触发器tr_update_sno。DROPTRIGGERteaching_management.tr_update_sno;(3)利用HeidiSQL图形界面管理触发器。5.3触发器、事务和锁5.3.2事务1.事务的特性事务是由有限的数据库操作序列组成的,但并不是任意的数据库操作序列都能成为事务,作为一个逻辑工作单元,必须有4个属性,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)及持久性(Durability),简称事务的ACID特性。2.事务分类

扁平事务、带有保存点的扁平事务、链式事务、嵌套事务、分布式事务。3.事务控制事务控制包括开始事务、提交事务、回滚事务等操作。5.3触发器、事务和锁5.3.2事务【例16】修改教学管理系统中学生所在班级的班级号,成功则提交事务,否则回滚事务。DELIMITER//CREATEPROCEDUREsp_student_class()BEGIN

DECLAREl_noCHAR(3);

STARTTRANSACTION;UPDATEteaching_management.student_infoSETclass_no=‘L23’WHEREno=‘T01’;SELECTclass_noINTOl_noFROMteaching_management.student_infoWHEREno=‘T01’;IFl_no=‘L23’THEN

BEGIN5.3触发器、事务和锁5.3.2事务

SELECT‘修改成功,提交事务!’;

COMMIT;

END;

ELSE

BEGIN

SELECT‘修改失败,回滚事务!’;

ROLLBACK;

END;

ENDIF;

END;//DELIMITER;callsp_student_class;5.3触发器、事务和锁5.3.3锁使用事务可以解决用户存取数据的问题,从而保证数据库的完整性和一致性,为防止其他用户修改另一个还没有完成的事务中的数据,就必须在事务中使用锁。1.并发操作引起的问题(1)丢失更新；(2)脏读；

(3)不可重复读；(4)幻象读2.事务隔离级别 四个隔离级别的特性5.3触发器、事务和锁5.3.3锁3.锁机制(1)表级锁定。①读锁

MariaDB中用于READ(读)的表级锁定的实现机制。②写锁MariaDB中用于WRITE(写)的表级锁定的实现机制。LOCKTABLES语句可实现表级锁的锁定,锁数据表的时候可以使用不同的锁一次性锁定多张数据表,而解锁的时候只能一次性解锁当前客户端会话的所有数据表。语法格式:LOCKTABLEStb_name[[AS]alias]lock_type[,tb_name[[AS]alias]lock_type]...;5.3触发器、事务和锁5.3.3锁3.锁机制【例17】加锁操作。在test数据库中创建练习用的t1、t2数据表,并插入测试数据。USEtest;DROPTABLEIFEXISTSt1,t2;CREATETABLEt1(aINT,bCHAR(5))ENGINE=MYISAM;CREATETAB