MySQL数据库项目实战 课件 项目5、6“天意书屋”数据库中程序的设计、用户权限与运用备份

项目五“天意书屋”数据库中程序的设计走进程序设计1探索触发器2Contents目录处理事务3任务一走进程序设计看情景，明目标温旧知，做准备

刘老师，如何实现在“天意书屋”数据库中编写SQL程序代码呢？在MySQL数据库中编写程序代码就是存储过程，一个存储过程里边可以包含多条SQL语句，SQL语句之间可以使用流程控制语句进行控制，用来实现较为复杂的业务逻辑。遇见视图品味索引

要求：在“天意书屋”数据库中，创建一个名称为proc_mybook的存储过程，查询图书信息表tb_book中的所有图书信息。

任务描述任务一走进程序设计

任务实施操作步骤如下：

步骤1：在Navicat主窗口中依次单击“查询”—>“新建查询”按钮，创建一个查询窗口。任务一走进程序设计

任务实施步骤2：在该查询窗口中输入：createprocedureproc_mybook()beginselect*fromtb_book;end;任务一走进程序设计步骤3：单击“运行”按钮，运行结果如图所示。

任务实施步骤4：继续在该查询窗口中输入命令，调用存储过程proc_mybook：callproc_mybook();任务一走进程序设计步骤5：单击“运行”按钮，运行结果如图所示。任务一走进程序设计一、存储过程概述任务一走进程序设计

存储过程是一组经过编译并保存在数据库中的SQL语句集，可以随时被调用。

存储过程具有如下5个优点：

01执行速度快02系统性能高03允许标准组件式编程04灵活性强05安全任务一走进程序设计二、存储过程的创建及调用1.创建存储过程任务一走进程序设计语法格式：

CREATEPROCEDURE存储过程名称([参数列表[,...]])

SQL语句集;

语句说明：CREATEPROCEDURE：表示创建存储过程。存储过程名称：必须符合标识符命名规则，且对于数据库及其所有者必须唯一的。参数列表：是可选项，不省略为有参数存储过程，省略为无参数存储过程。SQL语句集：使用BEGIN表示开始，使用END表示结束。提示：在创建存储过程时，设置的存储过程参数名不要与数据表中的字段名重复，否则系统会报错。【例5-1】在“天意书屋”数据库中，要求创建存储过程proc_book，查询图书信息表tb_book中图书名称含有“mysql”的图书信息。

代码如下：

任务实施createprocedureproc_book()beginselect*fromtb_bookwherebooknamelike'%mysql%';end;任务一走进程序设计2.调用存储过程任务一走进程序设计语法格式：

CALL存储过程名称([参数列表[,...]]);语句说明：CALL：关键字，表示调用存储过程，后面加要调用的存储过程名称。参数列表：可选项，调用带有参数的存储过程，给出参数的具体的值。【例5-2】要求调用存储过程proc_book，查看图书信息表tb_book中含有mysql的图书信息。

代码如下：

任务实施callproc_book();任务一走进程序设计任务一走进程序设计语法格式：

CREATEPROCEDURE存储过程名称([IN|OUT|INOUT]

参数名称

参数类型)

SQL语句集;语句说明：IN：表示输入参数，可把外界的数据传递到存储过程当中。OUT：表示输出参数，可把存储过程的运算结果传递到外界。INOUT：表示输入输出参数，既可以把外界的数据传递给存储过程当中，又可以把存储过程的运算结果传递到外界。在没有指定参数方向的情况下，系统默认是输入参数IN。3.有参数存储过程任务一走进程序设计(1)创建和调用带输入参数的存储过程3.有参数存储过程

任务实施【例5-3】在“天意书屋”数据库中，创建存储过程proc_getbookbyId，要求在图书信息表tb_book中根据图书编号bookid查询指定的图书信息，显示图书编号、图书名称、图书作者和图书价格。

代码如下：任务一走进程序设计

任务实施【例5-3】在“天意书屋”数据库中，创建存储过程proc_getbookbyId，要求在图书信息表tb_book中根据图书编号bookid查询指定的图书信息，显示图书编号、图书名称、图书作者和图书价格。

代码如下：任务一走进程序设计createprocedureproc_getbookbyId(inidint)beginselectbookid,bookname,author,bookpricefromtb_bookwherebookid=id;end;

任务实施【例5-4】要求调用存储过程proc_getbookbyId，查询bookid的值为3的图书信息。

代码如下：callproc_getbookbyId(3);任务一走进程序设计任务一走进程序设计(2)创建和调用带输出参数的存储过程3.有参数存储过程

任务实施【例5-5】在“天意书屋”数据库中，创建存储过程proc_total，要求查询图书信息表tb_book中图书的数量。

任务一走进程序设计

任务实施【例5-5】在“天意书屋”数据库中，创建存储过程proc_total，要求查询图书信息表tb_book中图书的数量。

代码如下：任务一走进程序设计createprocedureproc_total(outnumint)beginselectcount(*)intonumfromtb_book;end;

任务实施【例5-6】要求调用存储过程proc_total，查看图书信息表tb_book中的图书数量。

代码如下：callproc_total(@num);select@num;任务一走进程序设计任务一走进程序设计(3)创建和调用带输入输出参数的存储过程3.有参数存储过程

任务实施【例5-7】在“天意书屋”数据库中，创建存储过程proc_getuserbyname，要求在客户信息表tb_user中根据客户姓名username查询该客户的客户编号。

任务一走进程序设计

任务实施【例5-7】在“天意书屋”数据库中，创建存储过程proc_getuserbyname，要求在客户信息表tb_user中根据客户姓名username查询该客户的客户编号。

代码如下：任务一走进程序设计createprocedureproc_getuserbyname(innamevarchar(30),outidint)beginselectuseridintoidfromtb_userwhereusername=name;end;

任务实施【例5-8】要求调用存储过程proc_getuserbyname，查询用户名为“王伟”的客户编号。

代码如下：callproc_getuserbyname("王伟",@id);select@id;任务一走进程序设计任务一走进程序设计三、查看存储过程1.查看存储过程的状态任务一走进程序设计语法格式：

SHOWPROCEDURESTATUS[LIKE'存储过程名称'];

语句说明：SHOWPROCEDURESTATUS：表示查看存储过程的状态。LIKE'存储过程名称'：可选项，用来匹配存储过程的名称，LIKE不能省略。

任务实施【例5-9】在“天意书屋”数据库中，要求查看存储过程名称中以proc开头的所有存储过程的状态信息。

代码如下：showprocedurestatuslike'proc%';任务一走进程序设计2.查看存储过程的定义任务一走进程序设计语法格式：

SHOWCREATE

PROCEDURE

存储过程名称;

语句说明：SHOWCREATEPROCEDURE：显示创建的存储过程。存储过程名称：要查看的存储过程名称。

任务实施【例5-10】在“天意书屋”数据库中，要求查看存储过程proc_book的定义语句。

代码如下：showcreateprocedureproc_book;任务一走进程序设计任务一走进程序设计四、修改存储过程任务一走进程序设计语法格式：

ALTERPROCEDURE存储过程名称

[

MODIFIESSQLDATA

|SQLSECURITY{DEFINER|INVOKER}];

语句说明：ALTERPROCEDURE：关键字，表示修改存储过程。MODIFIESSQLDATA：表示子程序中包含写数据的语句。DEFINER：表示只有定义者自己才能执行。INVOKER：表示调用者可以执行。

任务实施【例5-11】在“天意书屋”数据库中，要求修改存储过程proc_book的读写权限为modifiessqldata，安全类型为sqlsecurityinvoker。

代码如下：alterprocedureproc_bookmodifiessqldatasqlsecurityinvoker;任务一走进程序设计任务一走进程序设计五、删除存储过程任务一走进程序设计语法格式：

DROPPROCEDURE[IFEXISTS]存储过程名称;

语句说明：DROPPROCEDURE：表示删除存储过程。IFEXISTS：可选项，用于防止因删除不存在的存储过程而引发的错误。使用IFEXISTS在执行删除操作时，首先判断存储过程是否存在，如果存在，则直接删除；如果不存在，则删除不会报错；避免了系统错误。存储过程名称：表示要删除的存储过程。

任务实施【例5-12】在“天意书屋”数据库中，要求删除存储过程proc_book。

代码如下：dropprocedureproc_book;任务一走进程序设计任务一走进程序设计六、变量1.变量的分类任务一走进程序设计（1）用户变量（User-DefinedVariables）：带有前缀@，只能被定义它的用户使用。（2）局部变量（LocalVariables）：没有前缀，一般用于SQL语句块的BEGIN...END中。（3）系统变量（ServerSystemVariables）：带有前缀@@，MySQL有许多已经设置默认值的系统变量。2.局部变量的定义任务一走进程序设计语法格式：

DECLARE变量名数据类型

[DEFAULT默认值];

语句说明：DECLARE：关键字，表示定义局部变量，后面加变量名和数据类型。DEFAULT：关键字，表示默认，后面加默认值。

任务实施【例如】定义一个int类型的局部变量，名称为var1。

代码如下：declarevar1int;任务一走进程序设计提示：变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，declare语句在存储过程中使用时，必须出现在begin...end语句块的最前面。变量名不区分大小写，可以一次声明多个相同类型的变量。3.使用SET关键字为变量赋值任务一走进程序设计语法格式：

SET变量名=变量值;

语句说明：SET：关键字，表示设置。变量值：可以是常量或者表达式。

任务实施【例如】给已经定义的局部变量var1赋值。

代码如下：setvar1=3;任务一走进程序设计【例如】给用户变量var2赋值。代码如下：set@var2=3;#给用户变量var2赋值3select@var2;#查看用户变量var2的值3.使用SELECT...INTO语句为变量赋值任务一走进程序设计语法格式：

SELECT列名INTO变量名FROM表名;

语句说明：SELECT…FROM：查询语句，变量的数量必须与列或表达式的数量相同。INTO：连接列名和变量名之间的关键字。

任务实施【例5-13】在“天意书屋”数据库中，创建存储过程proc1，定义4个整型局部变量var1、var2、var3和var4，要求实现var3等于var1和var2相加，var4等于在订单信息表tb_book中查询图书编号为1的客户编号，最后将var3和var4的结果赋值给用户变量uvar1和uvar2。任务一走进程序设计

任务实施createprocedureproc1()begindeclarevar1,var2,var3,var4int;#声明4个局部变量setvar1=1;#局部变量var1赋值为1setvar2=2;#局部变量var2赋值为2setvar3=var1+var2;#局部变量var3赋值为var1与var2相加#局部变量var4赋值为从订单信息表中查询bookid=1的userid的值selectuseridintovar4fromtb_orderwherebookid=1;set@uvar1=var3;#用户变量uvar1赋值为var3set@uvar2=var4;#用户变量uvar2赋值为var4end;任务一走进程序设计代码如下：

任务实施任务一走进程序设计运行结果：

任务实施【例5-14】要求调用存储过程proc1，查询uvar1和uvar2的结果。

代码如下：任务一走进程序设计callproc1();select@uvar1;select@uvar2;任务一走进程序设计提示：单行注释可以使用#注释符，#注释符后直接加注释内容。单行注释也可以使用--注释符，--注释符后需要加一个空格，注释才能生效。多行注释使用/**/注释符，/*用于注释内容的开头，*/用于注释内容的结尾。任务一走进程序设计七、流程控制语句1.条件分支语句任务一走进程序设计

条件分支语句是通过对特定条件的判断，选择一个分支的语句执行。在MySQL中可以实现条件分支的语句有IF语句、IFNULL语句、IF...ELSE语句和CASE语句共4种。（1）IF语句任务一走进程序设计语法格式：

IF(条件表达式，结果1，结果2);

语句说明：当“条件表达式”的值为TRUE时，返回“结果1”，否则返回“结果2”。

任务实施【例5-15】在“天意书屋”数据库中，要求查询客户信息表tb_user中的前5条记录，输出username字段和telephone字段的值。当telephone字段的值为null时，输出字符串“nothing”，否则显示当前字段的值。

代码如下：任务一走进程序设计selectusername,if(telephoneisnull,'nothing',telephone)astelephonefromtb_userlimit5;（2）IFNULL语句任务一走进程序设计语法格式：

IFNULL(结果1，结果2);

语句说明：若结果1的值不为空，则返回结果1，否则返回结果2。

任务实施【例5-16】在“天意书屋”数据库中，要求查询图书信息表tb_book中从第7条数据开始，总条数为6的数据记录，输出bookname字段和author字段的值。当author字段不为空时，输出author字段的值，否则输出“noauthor”。

代码如下：任务一走进程序设计selectbookname,ifnull(author,'noauthor')asauthorfromtb_booklimit6,6;（3）IF...ELSE语句任务一走进程序设计语法格式：

IF条件表达式THEN

语句块1;

ELSE

语句块2;

ENDIF;

语句说明：当“条件表达式”的值为TRUE时，“语句块1”将被执行。如果“条件表达式”的值为FALSE，则执行“语句块2”。每个语句块都可以包含一个或多个语句。

任务实施【例5-17】在“天意书屋”数据库中，要求创建存储过程proc_myorder，查询订单信息表tb_order中客户编号为142258847的用户是否有订单。

代码如下：任务一走进程序设计createprocedureproc_myorder()begindeclarenumint;selectcount(*)intonumfromtb_orderwhereuserid=142258847;ifnum>0thenselect'有订单';elseselect'无订单';endif;end;

任务实施任务一走进程序设计运行结果：

任务实施【例5-18】要求调用存储过程proc_myorder，查询订单信息表tb_order中客户编号为142258847的用户是否有订单。

代码如下：任务一走进程序设计callproc_myorder();（4）CASE语句任务一走进程序设计

CASE语句在MySQL中用于实现分支处理，能够根据表达式的不同取值，转向不同的计算或处理，类似高级程序语言中的SWITCH...CASE语句。当判断条件的范围较大时，使用CASE会使得程序的结构更为简洁。适用于需要根据同一个表达式的不同取值来决定将执行哪一个分支的场合。

CASE语句具有简单结构和搜索结构两种语法。

简单CASE结构任务一走进程序设计语法格式：

CASE表达式

WHEN数值1THEN

语句1;

[WHEN数值2THEN

语句2;]

......

[ELSE

语句n+1;]

ENDCASE;

语句说明：“表达式”的值与WHEN子句后的“数值”比较，找到完全相同的项时，则执行对应的“语句”，若未找到匹配项，则执行ELSE后的“语句”。

任务实施【例5-19】在“天意书屋”数据库中，要求查询图书信息表tb_book中的图书名称bookname、图书作者author、图书价格bookprice和pricevalue。其中pricevalue的取值为：若bookprice=30为1，否则为0。

代码如下：任务一走进程序设计selectbookname,author,bookprice,casebookpricewhen30then1else0endaspricevaluefromtb_book;

CASE搜索结构任务一走进程序设计语法格式：

CASE

WHEN条件表达式1THEN

语句1;

[WHEN条件表达式2THEN

语句2;]

......

[ELSE

语句n+1;]

ENDCASE;

语句说明：该结构判断WHEN子句后的“条件表达式”的值是否为TRUE，若为TRUE，则执行对应的“语句”。若所有的“条件表达式”的值均为FALSE，则执行ELSE后的“语句”。若无ELSE子句，则返回NULL。

任务实施【例5-20】在“天意书屋”数据库中，要求查询订单信息表tb_order中的订单编号orderid、图书编号bookid、数量total和remark。其中remark的取值为若total大于等于4，则为“畅销书”，若大于等于2，则为“潜质畅销书”，其余为“一般畅销书”。

代码如下：任务一走进程序设计selectorderid,bookid,total, case whentotal>=4then'畅销书' whentotal>=2then'潜质畅销书' else'一般畅销书' endasremarkfromtb_order;2.循环语句任务一走进程序设计

除了条件语句之外，在MySQL中还经常会用到循环语句，循环语句可以在存储过程或者触发器等内容中使用。每一种循环都是重复执行的一个语句块，该语句块可包括一条或多条语句。循环语句在其他程序设计语言中有多种形式，MySQL中只有WHILE语句、LOOP语句和REPEAT语句三种。（1）WHILE语句任务一走进程序设计语法格式：

[开始标签:]WHILE条件表达式DO

语句块;

ENDWHILE[结束标签];

语句说明：WHILE语句是先判断“条件表达式”的值是否为TRUE，当“条件表达式”的值为TRUE时，语句块被重复执行，直至“条件表达式”的值为FALSE，才会结束循环。只要“开始标签”语句存在，则“结束标签”语句才能被使用；若两者都存在，它们的名称必须相同。

任务实施【例5-21】创建存储过程proc_doWhile，要求使用while语句循环输出1到100的累加和。

代码如下：任务一走进程序设计createprocedureproc_doWhile()begin set@count=1; set@sum=0; while@count<=100do set@sum=@sum+@count; set@count=@count+1;endwhile;select@sum;end;

任务实施【例5-22】要求调用存储过程proc_doWhile，查看1-100的和。

代码如下：任务一走进程序设计callproc_doWhile();（2）LOOP语句任务一走进程序设计语法格式：

[开始标签:]LOOP

语句块;

ENDLOOP[结束标签];

语句说明：“开始标签”和“结束标签”分别表示循环开始和结束的标识，这两个标识必须相同，可以省略。“语句块”表示需要循环执行的语句。（3）LEAVE语句任务一走进程序设计语法格式：

LEAVE标签名;语句说明：LEAVE语句主要用于跳出循环控制，与高级语言中的BREAK语句相似。“标签名”用于标识跳出循环的标识符。（4）ITERATE语句任务一走进程序设计语法格式：

ITERATE

标签名;语句说明：ITERATE语句只跳出当次循环，然后直接进入下一次循环，与高级语言中的CONTINUE语句相似。“标签名”表示用来跳出的本次循环的标识符。

任务实施【例5-23】创建存储过程proc_loop，要求使用loop语句循环输出1到100的奇数和。

代码如下：任务一走进程序设计createprocedureproc_loop()beginset@count=0;set@sum=0;add_num:loopset@count=@count+1;if@count=100then leaveadd_num;elseifmod(@count,2)=0theniterateadd_num;endif;endif; set@sum=@sum+@count;endloopadd_num;select@sum;end;

任务实施任务一走进程序设计运行结果：

任务实施【例5-24】要求调用存储过程proc_loop，查看1-100的奇数和。

代码如下：任务一走进程序设计callproc_loop();任务一走进程序设计提示：leave语句和iterate语句都是用来跳出循环语句，但两者的功能是不一样的。leave语句是跳出整个循环，然后执行循环外的程序语句。iterate语句是跳出本次循环，进入下一次循环。任务一走进程序设计八、游标1.声明游标任务一走进程序设计语法格式：

DECLARE游标名称CURSORFORSELECT语句;语句说明：DECLARE：关键字，表示声明游标，后面加游标名称。CURSOR：关键字，表示游标。FOR：关键字，后接查询语句。

任务实施【例5-25】在“天意书屋”数据库中，要求声明一个游标cur_user，在客户信息表tb_user中查询客户姓名。

代码如下：任务一走进程序设计declarecur_usercursorforselectusernamefromtb_user;2.打开游标任务一走进程序设计语法格式：

OPEN游标名称;语句说明：OPEN：关键字，表示打开游标。游标名称：已经声明的游标名。

任务实施【例5-26】要求打开声明的游标cur_user。

代码如下：任务一走进程序设计opencur_user;提示：在打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。3.使用游标任务一走进程序设计语法格式：

FETCH游标名称

INTO变量1[,变量2,.....];语句说明：FETCH：关键字，表示提取数据，后面加游标名称。INTO：关键字，后面加变量。提示：变量的个数必须和游标返回字段的数量相同，否则游标提取数据失败。

任务实施【例5-27】要求将cur_user游标查询出来的数据存入uname变量中。

代码如下：任务一走进程序设计fetchcur_userintouname;提示：MySQL中游标是仅向前的且只读的，也就是说，游标只能顺序的从前往后一条条读取结果集。4.关闭游标任务一走进程序设计语法格式：

CLOSE游标名称;语句说明：CLOSE：关键字，表示关闭游标。

任务实施【例5-28】要求将打开的游标cur_user关闭。

代码如下：任务一走进程序设计closecur_user;提示：游标关闭之后，若要继续使用游标，则需要重新打开游标。

任务实施【例5-29】要求将上述游标使用的4个步骤放到一个存储过程pro_cursor中，然后通过调用存储过程，查看游标的使用方法。

步骤1：创建存储过程。任务一走进程序设计createprocedurepro_cursor()begindeclareunamevarchar(30);declarecur_user cursorforselectusernamefromtb_user;opencur_user;fetchcur_userintouname;selectuname;closecur_user;end;

任务实施步骤2：调用存储过程。任务一走进程序设计callpro_cursor();1、在“天意书屋”数据库中，要求创建存储过程spUser，在客户信息表tb_user中查询客户总数。请根据题意将代码补充完整。

动手实践任务一走进程序设计

#创建存储过程spUserbegin

#查询客户信息表tb_user中的客户总数end;2、要求调用存储过程spUser。请根据运行结果将代码补充完整。

动手实践任务一走进程序设计

#调用存储过程spUser3、要求删除存储过程spUser。请根据题意将代码补充完整。

#删除存储过程spUser4、要求创建存储过程spGetInteger，输出100以内能够同时被3和5整除的整数和。请根据题意将代码补充完整。

动手实践任务一走进程序设计createprocedurespGetInteger()begin

;#设置用户变量count，初始值为0

;#设置用户变量sum，初始值为0add_num:loop

;#设置用户变量count加1if@count=101then

;#跳出循环elseif

then#用户变量count对3和5整除#用户变量sum等于用户变量sum加上用户变量count

;

动手实践任务一走进程序设计endif;endif;

;#结束loop循环select@sum;end;5、要求调用存储过程spGetInteger，输出100以内同时被3和5整除的整数和。请根据运行结果将代码补充完整。

#调用存储过程spGetInteger

1+X知识巩固

1、关于MySQL存储过程,说法错误的是（）。A、调用存储过程使用关键字CALLB、存储过程的参数在定义时，有两种参数约束，即IN、OUTC、创建存储过程的语法是CREATEPROCEDURED、存储过程是一种在数据库中存储复杂程序，以便由外部程序调用的数据库对象2、创建存储过程的命令是（）。A、createprocedureB、createruleC、createdureD、createfile任务一走进程序设计

1+X知识巩固

3、MySQL中使用（）来调用存储过程。A、execB、callC、executeD、create4、下面的哪个语句用来声明游标？（）A、createcursorB、altercursorC、setcursorD、declarecursor5、流程控制语句中的选择语句有（）。A、ifB、whileC、selectD、switch任务一走进程序设计任务二探索触发器看情景，明目标温旧知，做准备

刘老师，如何实现当插入一条新数据时，这个新数据的某些字段内容可以同步到其他表中呢？在MySQL数据库中触发器能实现这个功能，触发器与存储过程类似，它也是一段程序代码。不同的是，触发器是由事件激发某个操作，当表中出现特定事件时，就会激发该对象。使用触发器可以对表实施复杂的完整性约束，保持数据的一致性。走进程序设计

要求：在“天意书屋”数据库中，购买了2本图书后，订单表中将生成一条记录，这时要求图书信息表（tb_book）表中的数量做相应变化。

任务描述任务二探索触发器

任务实施

提前在tb_book表中添加quantity字段，插入该字段的值为100。

操作步骤如下：

步骤1：在Navicat主窗口中依次单击“查询”—>“新建查询”按钮，创建一个查询窗口。任务二探索触发器

任务实施步骤2：在该查询窗口中输入：createtriggerup_inafterinsertontb_orderforeachrowbeginupdatetb_booksetquantity=quantity-new.totalwherebookid=new.bookid;end;任务二探索触发器步骤3：单击“运行”按钮，运行结果如图所示。

任务实施步骤4：继续在该查询窗口中输入insert语句，触发up_in触发器：insertintotb_ordervalues(198564666,673833825,3,"2022-08-25",2);select*fromtb_book;任务二探索触发器步骤5：单击“运行”按钮，运行结果如图所示。任务二探索触发器一、触发器概述任务二探索触发器触发器是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，以保证关联数据的完整性。一般激活触发器的事件包括INSERT、UPDATE和DELTE事件。在MySQL中，定义在触发器中的SQL语句可以关联表中的任意列，但不能直接使用列的名称标识，那会使系统混淆，所以MySQL提供了两个逻辑表NEW和OLD。NEW和OLD的表结构与触发器所在数据表的结构完全一致，当触发器的执行完成之后，这两个表也会被自动删除。NEW表用来存放更新后的记录。对于INSERT语句，NEW表中存放的是要插入的记录；对于UPDATE语句，该表中存放的是要更新的记录。任务二探索触发器OLD表用来存放更新前的记录。对于UPDATE语句，OLD表中存放的是更新前的记录（更新完后即被删除）；对于DELETE语句，该表中存放的是被删除的记录。任务二探索触发器二、创建触发器任务二探索触发器语法格式：

CREATETRIGGER触发器名称触发时机

触发事件

ON表名FOREACHROW触发器激活后执行的SQL语句;语句说明：CREATETRIGGER：表示创建触发器。触发时机：可以取值BEFORE或AFTER。触发事件：可以是INSERT、UPDATE和DELETE。FOREACHROW：表示数据表中任意一条记录满足触发事件都会激活触发器。提示：BEFORE是指触发器在激活它的语句之前触发。AFTER是指触发器在激活它的语句之后触发。任务二探索触发器1.创建AFTER触发器

AFTER触发器是指触发器监视的触发事件执行之后，再激活触发器，激活后所执行的操作无法影响触发器所监视的事件。

AFTER触发器也可以根据所监视的事件分为三种，分别是INSERT型触发器、UPDATE型触发器和DELETE型触发器。【例5-30】在“天意书屋”数据库中，要求为图书信息表tb_book创建insert型触发器trig_insertbook，同时向图书信息表tb_book中插入数据，将typeid插入到类型信息表tb_type中的typeid中，最后显示类型信息表tb_type的信息。操作步骤如下：

步骤1：创建触发器。

代码如下：

任务实施createtriggertrig_insertbookafterinsertontb_bookforeachrowbegininsertintotb_typevalues(new.typeid,'开发类');end;任务二探索触发器（1）INSERT型触发器步骤2：插入一条记录实现触发触发器。

代码如下：

任务实施insertintotb_bookvalues(100,10,'Java程序设计','谭浩强',69,100);任务二探索触发器步骤3：查看插入记录后tb_type表信息。代码如下：select*fromtb_type;【例5-31】在“天意书屋”数据库中，要求为图书信息表tb_book创建update型触发器trig_updatebook，当向图书信息表tb_book中更新数据时，同时更新类型信息表tb_type中的typeid数据，最后显示类型信息表tb_type的信息。

任务实施任务二探索触发器（2）UPDATE型触发器【例5-31】在“天意书屋”数据库中，要求为图书信息表tb_book创建update型触发器trig_updatebook，当向图书信息表tb_book中更新数据时，同时更新类型信息表tb_type中的typeid数据，最后显示类型信息表tb_type的信息。

操作步骤如下：

步骤1：创建触发器。

代码如下：

任务实施createtriggertrig_updatebookafterupdateontb_bookforeachrowbeginupdatetb_typesettypeid=new.typeidwheretypename='开发类';end;任务二探索触发器（2）UPDATE型触发器步骤2：更新一条记录实现触发触发器。

代码如下：

任务实施updatetb_booksettypeid=11wherebookid=100;任务二探索触发器步骤3：查看更新记录后的tb_type表信息。代码如下：select*fromtb_type;【例5-32】在“天意书屋”数据库中，要求为图书信息表tb_book创建delete型触发器trig_deletebook，当向图书信息表tb_book中删除数据时，同时删除类型信息表tb_type中对应数据，最后显示类型信息表tb_type的信息。

任务实施任务二探索触发器（3）DELETE型触发器【例5-32】在“天意书屋”数据库中，要求为图书信息表tb_book创建delete型触发器trig_deletebook，当向图书信息表tb_book中删除数据时，同时删除类型信息表tb_type中对应数据，最后显示类型信息表tb_type的信息。

操作步骤如下：

步骤1：创建触发器。

代码如下：

任务实施createtriggertrig_deletebookafterdeleteontb_bookforeachrowbegindeletefromtb_typewheretypeid=old.typeid;end;任务二探索触发器（3）DELETE型触发器步骤2：删除一条记录实现触发触发器。

代码如下：

任务实施deletefromtb_bookwherebookid=100;任务二探索触发器步骤3：查看删除记录后tb_type表信息。代码如下：select*fromtb_type;任务二探索触发器2.创建BEFORE触发器

BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断或修改即将发生的操作。BEFORE触发器可以根据所监视的事件分为三种，分别是INSERT型触发器、UPDATE型触发器和DELETE型触发器。【例5-33】在“天意书屋”数据库中，要求为订单信息表tb_order创建insert型触发器tg1，当tb_order表中图书数量大于tb_book表中所对应图书的总量，tb_book表中的图书库会出现负数，为避免这类问题，可以创建before触发器，系统会先判断订单中图书的购买数量，如果大于库存，则抛出异常，终止操作。

操作步骤如下：

步骤1：创建触发器。

运行结果如下：

代码如下：

任务实施任务二探索触发器任务二探索触发器提示：row_count()：用于记录更新操作影响的行数，如果其值不等于1，就说明订单中图书数量大于库存的数量，tb_book表不能进行更新，此时就会把“商品名称+库存不足”赋值给变量msg。signal：用于在存储过程中向调用者返回错误或警告条件。此外，它还提供对错误特征（错误编号、sqlstate值、消息）的控制。

任务实施createtriggertg1beforeinsertontb_orderforeachrowbegindeclaremsgvarchar(200);updatetb_booksetquantity=quantity-new.totalwherebookid=new.bookidandquantity>=new.total;ifrow_count()<>1thenselectconcat(bookname,'库存不足')intomsgfromtb_bookwherebookid=new.bookid; signalsqlstate'TX000'setmessage_text=msg;endif;end;任务二探索触发器步骤2：插入一条记录实现触发触发器。

代码如下：

任务实施insertintotb_ordervalues(184514955,100101255,13,'2022-8-20',200);任务二探索触发器步骤3：查看tb_book表信息。代码如下：

select*fromtb_book;任务二探索触发器三、查看触发器任务二探索触发器语法格式：

SHOWTRIGGERS;语句说明：SHOWTRIGGERS：查看触发器的基本信息。【例5-34】要求在“天意书屋”数据库中，查看所有触发器。

代码如下：

任务实施showtriggers;任务二探索触发器任务二探索触发器四、删除触发器任务二探索触发器语法格式：

DROPTRIGGER[数据库名].触发器名称;语句说明：DROPTRIGGER：删除已经存在的触发器。数据库名：可选项，如果删除不是当前数据库中的触发器，则不可以省略。触发器名称：表示要删除的触发器名称。【例5-35】在“天意书屋”数据库中，要求删除trig_insertbook触发器。

代码如下：

任务实施droptriggertrig_insertbook;任务二探索触发器1、在“天意书屋”数据库中，要求创建insert型触发器trigInsertOrder，触发时机是before，当向订单信息表tb_order中插入数据时，同时也在图书信息表tb_book中插入对应的数据，最后显示图书信息表tb_book的信息。请根据运行结果将代码补充完整。

操作步骤如下：

步骤1：创建触发器。

代码如下：

动手实践

#创建触发器trigInsertOrder

#设置触发器时机、触发器事件和触发器作用的表foreachrowbegininsertintotb_book(bookid,typeid,bookprice)values(

.bookid,1,120);end;任务二探索触发器步骤2：插入一条记录实现触发触发器。

代码如下：

insertintotb_ordervalues(111111112,100000001,99,'2021-08-19',10);任务二探索触发器步骤3：查看插入记录后tb_book表信息。代码如下：select*fromtb_bookwherebookid=99;动手实践2、在“天意书屋”数据库中，要求创建update型触发器trigUpdatebook，触发时机是after，当向图书信息表tb_book中更新数据时，同时也在订单信息表tb_order中更新对应的数据，最后显示订单信息表tb_order的信息。请根据运行结果将代码补充完整。

操作步骤如下：

步骤1：创建触发器。

代码如下：

动手实践

#创建触发器trigUpdatebook

#设置触发器时机、触发器事件和触发器作用的表

#对表中每一行都生效beginupdatetb_ordersetbookid=new.bookidwhereorderid=111111112;end;任务二探索触发器步骤2：更新一条记录实现触发触发器。

代码如下：

updatetb_booksetbookid=9999wherebookprice=120;任务二探索触发器步骤3：查看更新记录后tb_order表信息。代码如下：select*fromtb_orderwhereorderid=111111112;动手实践3、在“天意书屋”数据库中，要求查看触发器信息。请根据题意将代码补充完整。

动手实践

#查看触发器信息任务二探索触发器4、在“天意书屋”数据库中，要求删除触发器trigUpdatebook。请根据题意将代码补充完整。

#删除触发器trigUpdatebook

1+X知识巩固

1、创建触发器的关键语句是（）。A、createfunctionB、createprocedureC、createtriggerD、createevent2、一般激活触发器的事件包括insert、update和（）事件。A、createB、alterC、dropD、delete3、下列说法中错误的是（）。A、常用的触发器有insert、update、delete三种B、对于同一张数据表，可以同时有两个beforeupdate触发器C、new表在insert触发器中用来访问被插入的行D、old表中值只读不能被更新任务二探索触发器任务三处理事务看情景，明目标温旧知，做准备

刘老师，我想试验一种极端情况，比如买家购买图书信息后结账的瞬间，断电了或者其他意外情况，买家的钱已经扣除，显示余额变少，但是卖家的钱没有增加，怎么办？为了预防这种情况发生，数据库设计了事务机制。事务是一组有着内在逻辑联系的SQL语句，这些SQL语句执行的数据结果存在一定的关联，要么都执行成功，要么都不执行，且事务必须同时满足ACID特征。通过使用事务可以大大提高数据安全性和执行效率。走进程序设计探索触发器

要求：在“天意书屋”数据库中，采用事务提交的方法，设置订单信息表tb_order中的下单日期orderdate为当前系统时间。

任务描述任务三处理事务

任务实施操作步骤如下：

步骤1：在Navicat主窗口中依次单击“查询”—>“新建查询”按钮，创建一个查询窗口。任务三处理事务

任务实施步骤2：在该查询窗口中输入：starttransaction;updatetb_ordersetorderdate=date_format(now(),'%Y-%m-%d');commit;任务三处理事务步骤3：单击“运行”按钮,运行结果如图所示。

任务实施步骤4：继续在该查询窗口中输入查询语句，查看orderdate的时间。select*fromtb_order;任务三处理事务步骤5：单击“运行”按钮,运行结果如图所示。任务三处理事务一、事务概述任务三处理事务事务是一组有着内在逻辑联系的SQL语句，可以是一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。支持事务的数据库系统要么正确执行事务里的所有SQL语句，要么把它们当做整体全部放弃，也就是说事务永远不会只完成一部分。在事务中的操作，要么都执行，要么都不执行，这就是事务的目的，也是事务的重要特征之一。使用事务可以大大提高数据安全性和执行效率。1.事务的定义任务三处理事务1324原子性是指数据库事务是不可分割的操作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。原子性（Atomicity）

一致性是指事务将数据库从一种状态变成另一种状态。在事务开始之前和事务结束之后，数据的完整性约束没有被破坏。一致性（Consistency）隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。隔离性（Isolation）事务一旦提交，其结果就是永久性的，即使发生死机等故障，数据库也能将数据恢复。持久性（Durability）2.事务的特征任务三处理事务二、事务的基本操作任务三处理事务语法格式：

第1步：START

TRANSACTION;

第2步：SQL语句集;

第3步：COMMIT/

ROLLBACK;语句说明：STARTTRANSACTION：表示开始事务。COMMIT：关键字，表示提交事务。ROLLBACK：关键字，表示回滚事务。提示：ROLLBACK只能针对未提交的事务回滚，已提交的事务无法回滚。【例5-36】在“天意书屋”数据库中，要求采用事务提交的方法，设置图书信息表tb_book中“mysql数据库程序设计”的图书作者为空，同时价格在原来价格的基础上增加20元。

操作步骤如下：

步骤1：查看“mysql数据库程序设计”图书信息。

代码如下：

任务实施select*fromtb_bookwherebookname='mysql数据库程序设计';任务三处理事务步骤2：事务提交设置图书作者为空，同时图书价格在原来基础上增加20元。

代码如下：

任务实施starttransaction;updatetb_booksetauthor=''wherebookname='mysql数据库程序设计';updatetb_booksetbookprice=bookprice+20wherebookname='mysql数据库程序设计';commit;任务三处理事务步骤3：再次查看“mysql数据库程序设计”图书信息。

代码如下：

任务实施select*fromtb_bookwherebookname='mysql数据库程序设计';任务三处理事务【例5-37】在“天意书屋”数据库中，要求采用事务回滚的方法，设置图书信息表tb_book中“mysql数据库程序设计”的图书编号为33，同时价格在原来价格的基础上减少20元。

操作步骤如下：

步骤1：查看“mysql数据库程序设计”图书信息。

代码如下：

任务实施select*fromtb_bookwherebookname='mysql数据库程序设计';任务三处理事务步骤2：事务回滚设置图书编号为33，同时图书价格在原来基础上减少20元。

代码如下：

任务实施starttransaction;updatetb_booksetbookid=33wherebookname='mysql数据库程序设计';updatetb_booksetbookprice=bookprice-20wherebookname='mysql数据库程序设计';rollback;任务三处理事务步骤3：再次查看“mysql数据库程序设计”图书信息。

代码如下：

任务实施select*fromtb_bookwherebookname='mysql数据库程序设计';任务三处理事务任务三处理事务提示：MySQL中的事务不允许嵌套。若在执行starttransaction语句前，上一个事务还未提交，会隐式地执行提交操作。事务处理主要是针对数据表中数据的处理，不包括创建或删除数据库、数据表，修改表结构等操作。1、在“天意书屋”数据库中，要求采用事务提交的方法，设置订单信息表tb_order中的数量total在原来的基础上增加5。请根据运行结果将代码补充完整。

动手实践

#开启事务

#设置数量在原来的基础上增加5

#提交事务

#再次查询订单信息表中的数量信息任务三处理事务2、在“天意书屋”数据库中，要求采用事务回滚的方法，设置订单信息表tb_order中的数量total在原来的基础上减少5。请根据运行结果将代码补充完整。

动手实践

#开启事务

#设置数量在原来的基础上减少5

#回滚事务

#再次查询订单信息表中的数量信息任务三处理事务

1+X知识巩固

1、用于将事务处理写到数据库的命令是（）。A、insertB、rollbackC、commitD、savepoint2、用于将事务处理回滚操作的命令是（）。A、insertB、rollbackC、commitD、savepoint3、每个事务都是完整不可分割的最小单元，是事务哪个特性（）。A、原子性B、一致性C、隔离性D、持久性任务三处理事务

1+X知识巩固

4、下列关于MySQL中事务的说法，错误的是（）。A、事务就是针对数据库的一组操作B、事务中的语句要么都执行，要么都不执行C、事务提交后其中的操作才会生效D、提交事务的语句为submit5、（多选）事务的特性都有（）。A、原子性B、一致性C、隔离性D、持久性任务三处理事务

一、走进程序设计

1、在“学生选课系统”数据库中，创建并调用存储过程proStu，查询学生信息表Student中专业名称含有“软件技术”的学生信息。2、在“学生选课系统”数据库中，创建并调用带输入参数的存储过程proSco，查询学生成绩表Score中学号为20205127的成绩总和。3、在“学生选课系统”数据库中，创建并调用带输出参数的存储过程proCou，查询课程信息表Course中课程数量。4、在“学生选课系统”数据库中，删除存储过程proStu。综

合

实

训

1、在“学生选课系统”数据库中，创建insert型触发器trigInsertCourse，触发时机是before，当向课程信息表Course中插入数据时，同时也在学生成绩表Score中插入对应的数据。2、在“学生选课系统”数据库中，创建update型触发器trigUpdateStudent，触发时机是after，当向学生信息表Student中更新数据时，同时也在学生成绩表Score中更新对应的数据。3、在“学生选课系统”数据库中，查看触发器的信息。4、在“学生选课系统”数据库中，删除触发器trigUpdateStudent。综

合

实

训二、探索触发器

三、处理事务

1、在“学生选课系统”数据库中，采用事务提交的方法，设置学生成绩表Score中的学生成绩在原来的基础上增加5分。2、在“学生选课系统”数据库中，采用事务回滚的方法，设置学生成绩表Score中的学生成绩在原来的基础上减少5分。综

合

实

训谢谢大家观看！主讲老师：史倩倩项目六

用户权限与运用备份思考权限1运用备份2Contents目录任务一思考权限看情景，明目标温旧知，做准备

刘老师，王同学想查看该数据库数据，我可以将root账号密码告诉他吗？最好不要哦。你应该创建一个新的账户，然后赋予该账户查看数据库的权限，让王同学使用这个账户。数据维护数据查询要求使用Navicat查看数据库系统中有哪些用户

任务描述任务一思考权限

任务实施任务一思考权限步骤：打开Navicat，使用root用户与服务器建立连接，双击展开“mysql”数据库，双击打开“user”表，即可查看数据库系统中的用户。1、用户任务一思考权限MySQL服务器中名为mysql的数据库，是在安装MySQL时系统自动创建的，用于维护数据库的用户以及权限的控制和管理。所有用户信息都保存在mysql.user数据表中。根据mysql.user表字段的功能可将其分为6类：账号字段身份验证字段资源限制字段账号锁定字段权限字段安全连接字段1、用户任务一思考权限类型字段含义账号字段User用于代表用户的名称。Host表示允许访问的主机地址。当host的值为“%”时，表示任何主机，值为localhost时，表示本地主机身份验证字段plugin用于指定用户的验证插件名称。authentication_string是账户明文密码（如123456）加密后的字符串。安全连接字段ssl_type用于保存安全连接的类型资源限制字段max_questions：保存每小时允许用户执行查询操作的最多次数。max_updates：保存每小时允许用户执行更新操作的最多次数。max_connections：保存每小时允许用户建立连接的最多次数。权限字段以“_priv”结尾的字段一共有29个，这些字段保存了用户的全局权限账号锁定字段account_locked字段用于保存当前用户是锁定、还是解锁状态。1、用户：查看用户任务一思考权限方法一查看用户使用Navicat图形化界面方式方法二使用SQL语句

任务实施任务一思考权限select*frommysql.user;【例6-1】要求使用SQL语句方式查看数据库系统中所有用户。2、用户：创建用户任务一思考权限方法一创建用户使用Navicat图形化界面方式方法二使用SQL语句

任务实施任务一思考权限步骤一：打开Navicat，使用root用户与服务器建立连接，单击工具栏中的“用户”按钮，即可看到当前的所有用户【例6-2】使用Navicat创建一个普通用户，用户名为ybb，主机地址为localhost，密码为123456。

任务实施任务一思考权限步骤二：点击工具栏中的“新建用户”按钮，出现一个用户编辑窗口，输入用户名“ybb”，主机“localhost”，插件使用“caching_sha2_password”，密码“123456”，确认密码“123456”，点击“保存”按钮，即可完成创建新用户ybb。

任务实施任务一思考权限步骤三：再次点击工具栏的“用户”按钮，即可看到新创建的“ybb”用户已经出现在列表中。1、用户：创建用户任务一思考权限使用SQL语句创建用户，语法格式：参数说明：CREATEUSER：可以一次创建多个用户，多个用户之间使用逗号分隔；账户名由用户名和主机地址组成。用户名不能超过32个字符，且区分大小写，主机地址不区分大小写；

主机地址指的是允许创建的用户从哪个地址去连接MySQL服务器，主机地址的值为“%”时，表示任何主机，值为localhost时，表本地主机若不指定主机地址、密码以及相关的用户选项，则表示此用户在