数据库原理与应用 课件 张千帆 第8-10章 视图；存储过程、存储函数和触发器；数据库备份与恢复VIP

第8章视图本章介绍视图的基本概念和作用，以及与视图有关的基本操作数据库系统原理及应用主要内容8.1视图的概念8.2定义视图8.3基于视图的数据操纵8.4基于视图的数据查询8.1视图的概念视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。基本表中的数据发生变化，从视图中查询出的数据也随之改变。8.2定义视图CREATEVIEWview_name[(column_list)]ASselect_statement[WITHCHECKOPTION]SQL中，CREATEVIEW语句用于创建视图，其基本的语法格式为：参数：column_list：组成视图的属性列名：全部省略或全部指定。全部省略:

由子查询中SELECT目标列中的诸字段组成

明确指定视图的所有列名:

目标列是聚集函数、列表达式

多表中的同名

希望在在视图中为某个列定义一个更合适的名字时select_statement：SELECT语句，但不能包含ORDERBY子句和DISTINCT短语。WITHCHECKOPTION：使用该子句，系统自动检查视图的更新操作是否满足视图定义中查询语句的条件表达式；如果违反条件表达式，则拒绝相应的操作。8.2.1创建单源表视图单源表视图是指视图的数据取自一个基本表的部分行、列，它的行、列与基本表的行、列相对应。[例8-1]:创建视图view_card，显示所有挂失的校园卡的卡号和余额。CREATEVIEWview_cardASSELECTCID卡号,balance余额FROMcardWHEREstate='1';8.2.2创建多源表视图多源视图是指视图的数据来自多个基本表。CREATEVIEWview_salebill20200706ASSELECT*FROMview_salebillWHEREsaledate='2020-7-6';[例8-2]:在视图view_salebill上创建视图view_salebill20200706，查询2020年7月6日单笔消费金额在10-20元之间的消费信息。8.2.3在视图上创建视图视图不仅可以建立在基本表上，也可以建立在已有视图上。[例8-3]:在视图view_salebill上创建视图view_salebill20200706，查询2020年7月6日单笔消费金额在10-20元之间的消费信息。CREATEVIEWview_salebill20200706ASSELECT*FROMview_salebillWHEREsaledate='2020-7-6';8.2.4修改视图SQL中，ALTERVIEW语句用来修改视图的定义，其基本的语法格式为：ALTERVIEWview_name[(column_list)]ASselect_statement[WITHCHECKOPTION]ALTERVIEWview_salebill20200706ASSELECTCID,payamountFROMview_salebillWHEREsaledate='2020-7-6';[例8-4]:将视图view_salebill20200706改为仅查询CID和payamount列，其他条件不变。8.2.5删除视图SQL中，DROPVIEW语句用于删除视图，其基本的语法格式为：DROPVIEW[IFEXISTS]view_name[,view_name]...参数：IFEXISTS：不使用IFEXISTS，在视图列表中命名的视图不存在时系统会提示错误。使用IFEXISTS可以避免删除不存在的视图时发生错误。view_name：可以一次删除一个或多个视图，多个视图之间用逗号分开，且必须在每个视图上拥有DROP权限。8.2.5删除视图DROPVIEW语句从数据字典中删除指定的视图定义。视图删除后，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须使用DROPVIEW语句删除。删除基表后，由该基表导出的所有视图定义都必须使用DROPVIEW语句删除。[例8-5]:删除视图view_sname和视图view_college。DROPVIEWview_sname,view_college;8.3基于视图的数据操纵视图的数据操纵包括INSERT操作、UPDATE操作、DELETE操作。由于视图是不存储数据的虚表，视图是否允许更新要看对视图的更新能否转化为对基本表的更新。排除以下情况后，视图的更新受到严格的限制。定义在多个基本表或其他视图之上的视图，不允许更新。定义视图的SELECT语句含有GROUPBY、DISTINCT、表达式或聚集函数等，这类视图可执行删除操作，但不允许进行插入或修改操作。对于可更新的视图，如果定义视图时使用了WITHCHECKOPTION子句，对视图的更新操作要保证插入、修改和删除的行满足视图定义中查询语句的条件表达式。8.4基于视图的数据查询对于已经定义好的视图，通过视图查询数据与通过基本表查询数据一样，适用SELECT语句做查询操作。[例8-6]:通过视图view_totalamount查询第一食堂的营业额。SELECT商户,营业额

FROMview_totalamountWHERE商户='第一食堂';第9章存储过程、存储函数和触发器本章介绍存储过程、存储函数和触发器等数据库对象的基本概念以及创建、调用、修改和删除方法数据库系统原理及应用9.1存储过程9.2存储过程中的复合语句9.3存储函数9.4触发器主要内容存储过程9.19.1.1存储过程的基本概念存储过程(StoredProcedure)是一种把重复的任务操作封装起来的方法，是一组为了完成特定功能的SQL语句和可选控制流语句的预编译集合。存储过程创建后只需编译一次，以后即可多次执行。执行效率高灵活性强保证安全性和完整性执行效率高降低网络通信量存储过程的参数不仅可以向存储过程传入值，也可以由存储过程向外输出参数值，存储过程和调用存储过程的对象之间的可以进行双向的数据交换。按照传递数据的方向不同，存储过程中的参数类型有三种：

IN参数

OUT参数

INOUT参数。9.1.2创建存储过程参数：（1）sp_name：存储过程名。一个数据库中存储过程的名字必须唯一。（2）[proc_parameter[,...]]：存储过程的参数。存储过程的参数是任意的，可以有一个或多个参数，也可以没有参数。（3）[IN|OUT|INOUT]param_nametype：定义参数类型、参数名和参数的数据类型。IN为默认参数类型。（4）routine_body：存储过程中的SQL语句。CREATEPROCEDUREsp_name([proc_parameter[,...]])routine_bodyproc_parameter:[IN|OUT|INOUT]param_nametypeCREATEPROCEDURE语句用于创建存储过程，其基本的语法格式为：9.1.2创建存储过程[例9-1]：建立存储过程p_increase，使用OUT参数total_payamount输出消费清单的总金额，使用INOUT参数incr_number输入当前消费清单中的最大流水号并输出下一个流水号。CREATEPROCEDUREp_increase(OUTtotal_payamountVARCHAR(25),INOUTincr_numberINT)BEGINSELECTSUM(payamount)INTOtotal_payamountFROMsalebill;SETincr_number=incr_number+1;END;9.1.3调用存储过程CALLsp_name([proc_parameter[,...]])CALL语句用于调用CREATEPROCEDURE定义好的存储过程，

其基本的语法格式为：对于有OUT或INOUT参数的存储过程，调用时需要先声明参数对应的变量，用来保存参数的返回值。9.1.3调用存储过程[例9-2]：调用存储过程p_increase。SET@incr_number=18;CALLp_increase(@total_payamount,@incr_number);调用语句执行后，查看OUT参数和INOUT参数的值：

SELECT@total_payamount,@incr_number;结果如图所示：9.1.4删除存储过程DROPPROCEDURE语句用于删除存储过程，

其基本的语法格式为：DROPPROCEDURE[IFEXISTS]sp_name;参数：IFEXISTS：用于防止因误删除不存在的存储过程而引发错误。[例9-3]：删除存储过程p_increase。DROPPROCEDUREp_increase;9.1.5修改存储过程ALTERPROCEDURE语句用于修改存储过程，其基本语法为：ALTER{PROCEDURE|FUNCTION}proc_or_func[characterustic...]characteristics：指定存储过程特性，包括：{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指定子程序使用存储过程的限制。CONTAINSSQL：说明子程序包含SQL语句，但是不包含写数据语句；NOSQL：说明子程序不包含SQL语句；READSSQLDATA：说明子程序包含读数据读数据语句；MODIFIESSQLDATA：说明子程序包含写数据语句。9.1.5修改存储过程ALTERPROCEDURE语句作用有限，不能更改存储过程的参数或主体。如果需要进行此类更改，必须使用DROPPROCEDURE语句删除旧的存储过程，再用CREATEPROCEDURE新建一个存储过程。SQLSECURITY{DEFINER|INVOKER}：指明谁有权限执行，默认值：DEFINER；DEFINER：只有定义者才能执行INVOKER：拥有权限的调用者才可以执行COMMNET：注释信息注意：存储过程中的复合语句9.29.2.1变量DECLARE命令用于在存储过程中声明局部变量，语法格式如下：DECLAREvar_name[,var_name]...type[DEFAULTvalue]参数：1）var_name：变量名。2）Type：变量的数据类型。3）DEFAULTvalue：为变量提供默认值，该值可以是常数，也可以是表达式。如果没有DEFAULT子句，则变量的初始值为NULL。9.2.1变量[例9-4]：在存储过程p_business中声明两个变量new_id和new_name，通过调用存储过程为变量赋值，然后把变量值插入business表。CREATEPROCEDUREp_business(xCHAR(4)，yVARCHAR(20))BEGINDECLAREnew_idCHAR(4)DEFAULTx;DECLAREnew_nameVARCHAR(20)DEFAULTy;INSERTINTObusinessvalues(x,y);SELECT*FROMbusinessWHEREBID=new_id;END;CALLp_business('B008','东三食堂');9.2.2BEGIN…ENDBEGIN…END用来将一个或多个语句设定为一个程序块，其基本的语法格式为：[begin_label:]BEGIN

[statement_list]END[end_label]参数：

1）label：标签，相当于给程序块命名。

2）BEGIN…END：经常在条件语句、循环语句等中使用。一个BEGIN…END程

序块中还可以嵌套另外的BEGIN…END程序块。3）statement_list：语句列表是一个包含一个或多个语句的程序块，并用关键字BEGIN和END括起来。语句列表可以出现在存储过程、存储函数、触发器和事务中。9.2.3条件语句IF语句的基本语法格式为：IF语句IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF参数：search_condition：条件表达式。条件为真执行IF后的程序块，否则执行ELSE后面的程序块。IF语句可以嵌套使用。9.2.3条件语句[例9-5]：创建存储过程p_scale，查询商户的经营规模。如果该商户过去一年的营业额少于10万元则为“小商户”；10万到50万之间为“中等商户”；超过50万元则为“大商户”。CREATEPROCEDUREp_scale(INidCHAR(4)，OUTmessageVARCHAR(20))BEGINDECLAREsumamoutDECIMAL(10,2);SELECTSUM(payamount)INTOsumamoutFROMsalebillWHEREBID=id;IFsumamout<100000THENSETmessage='小商户';ELSEIFsumamout<500000THENSETmessage='中等商户';ELSESETmessage='大商户';ENDIF;ENDIF;SELECTid,message;END;9.2.3条件语句CASE语句可以嵌套在SQL语句中使用，CASE语句的基本语法格式为：：CASE语句CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE参数：1）search_condition：条件表达式。2）WHENsearch_conditionTHENstatement_list：当WHEN子句的条件表达式为逻辑真值时执行THEN后程序块，然后跳出CASE语句。3）ELSEstatement_list：如果CASE语句中包含ELSE子句，则当所有WHEN子句的条件表达式都为逻辑假时，就执行ELSE子句中的程序块。如果CASE语句中不包含ELSE子句且所有WHEN子句的条件表达式都为逻辑假时，CASE语句返回NULL。9.2.3条件语句[例9-6]：统计各商户当年的营业额，如果营业额超过50万元，则输出“大商户”；

少于10万元则输出“小商户”；10万到50万之间输出“中等商户”。SELECTBID商户编号,CASEWHENSUM(payamount)>500000THEN'大商户'WHENSUM(payamount)<100000THEN'小商户'ELSE'中等商户'END商户规模FROMsalebillWHEREYEAR(saledate)=YEAR(CURRENT_DATE())GROUPBYBID;9.2.4ITERATE语句ITERATE语句用在LOOP、REPEAT、WHILE等循环结构内，表示再次循环，语法格式为：ITERATElabel参数：

label：循环语句的标签。9.2.5LEAVE语句LEAVE语句可以用在BEGIN...END语句内或者LOOP、REPEAT、WHILE等循环结构内，作用是退出给定标签的BEGIN...END流程控制语句或终止循环，语法格式为：LEAVElabel参数：

label：复合语句的标签。9.2.6循环语句[begin_label:]LOOPstatement_listENDLOOP[end_label]参数：statement_list：循环体中允许重复执行的语句列表，如果该语句列表由多个语句组成，每个语句以分号分开。重复循环循环体中的语句，直到循环终止。通常，循环体中通过ITERATE子句开始下一次循环，通过LEAVE子句来终止循环。1）LOOP语句LOOP语句可以构造一个循环结构，其基本的语法格式为：9.2.6循环语句[例9-7]：创建存储过程p_doloop，求解n！。CREATEPROCEDUREp_doloop(INin_countINT)BEGINDECLARECOUNTINTDEFAULT1;DECLAREfactorialINTDEFAULT1;label1:LOOPSETfactorial=factorial*COUNT;SETCOUNT=COUNT+1; IFCOUNT<=in_countTHENITERATElabel1;ENDIF;LEAVElabel1;ENDLOOPlabel1;SELECTfactorial; END;调用存储过程p_doloop，求解5！

CALLp_doloop(5);执行结果如下表所示。9.2.6循环语句[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]参数：1）search_condition：条件表达式。2）statement_list：允许重复执行的语句序列。REPEAT至少进行一次循环，直到search_condition表达式为真时终止循环。statement_list由一个或多个语句组成，每个语句以分号分开。2）REPEAT语句REPEAT语句的基本语法格式为：9.2.6循环语句[例9-8]：创建存储过程p_dorepeat，求解n！。CREATEPROCEDUREp_dorepeat(INin_countINT)BEGINDECLARECOUNTINTDEFAULT1;DECLAREfactorialINTDEFAULT1;REPEATSETfactorial=factorial*COUNT;SETCOUNT=COUNT+1;UNTILCOUNT>in_countENDREPEAT; SELECTfactorial;END;调用存储过程p_dorepeat，求解5！CALLp_dorepeat(5);执行结果如下表所示。9.2.6循环语句[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]参数同REPEAT。当WHILE的条件表达式为真，循环体中的语句列表就会重复执行。如果条件表达式在第一次循环开始之时就为假，WHILE的循环体就一次也没有执行，这是和REPEAT不同之处。3）WHILE语句WHILE语句的基本语法格式为：9.2.6循环语句[例9-9]：创建存储过程p_dowhile，求解n！。CREATEPROCEDUREp_dowhile(INin_countINT)BEGINDECLARECOUNTINTDEFAULT1;DECLAREfactorialINTDEFAULT1;WHILECOUNT<=in_countDOSETfactorial=factorial*COUNT;SETCOUNT=COUNT+1;ENDWHILE; SELECTfactorial;END;调用存储过程p_dowhile，求解5！CALLp_dowhile(5);执行结果如下表所示。9.2.7游标存储过程中的条件语句、循环语句操作的对象是单行数据，但是SQL语言是基于集合运算的，查询结果是一般是由多行组成的结果集。这两类数据操作的处理对象和处理结果是不匹配的，需要把对集合的操作转换成对单行的处理。游标是一种从包含多个行的结果集中每次提取一行的机制。游标一次指向一行，并通过游标的顺序推进可以依次指向查询结果集的所有行，实现对查询结果集的遍历。MySQL支持存储过程内部的游标和服务器端的游标，二者实现方式相同。MySQL中游标是只读的，不可更新。使用游标之前需要先声明游标、打开游标，然后才能滚动游标查找数据，游标不使用时要关闭游标。9.2.7游标DECLAREcursor_nameCURSORFORselect_statement参数：（1）cursor_name：游标名。存储过程可能包含多个游标，但是在给定程序块中每个游标必须具有唯一的名称。（2）CURSORFORselect_statement：将游标与SELECT语句的查询结果集建立关联。1）声明游标游标的声明语句必须出现在变量和条件的声明语句之后、处理程序的声明语句之前。DECLARE语句用于声明游标，其基本的语法格式为：9.2.7游标OPENcursor_name该语句是执行游标声明中的SELECT语句，把查询结果取到缓冲区，然后使游标处于活动状态并指向某个地址，从该地址可以检索到游标所关联的结果集的第一行。可见，游标充当的是指针的作用。2）打开游标OPEN语句用于打开已声明的游标，其语法格式为：9.2.7游标FETCH[[NEXT]FROM]cursor_nameINTOvar_name[,var_name]...参数：var_name[,var_name]...：输出变量名，变量的数据类型和数量必须与SELECT语句的目标列一一对应。3）推进游标FETCH语句可以推进游标，获取游标所指向的数据行，将提取的列值存储在输出变量中，并将游标推进到下一行。其基本的语法格式为：当游标已经指向最后一行时继续执行FETCH语句会造成游标溢出，可以在系统引发NOTFOUND错误时定义一个CONTINUE的事件，指定这个事件发生时修改done变量的值。如果done=1，就结束循环。语法格式为：DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;9.2.7游标CLOSEcursor_name关闭的游标如果需要再次使用，可以用另一个OPEN语句打开，重新与查询结果集产生关联。4）关闭游标CLOSE语句用于关闭先前打开的游标，释放结果集占用的缓冲区及其他资源，其语法格式为：9.2.7游标[例9-10]：创建存储过程p_bname_list，使用游标输出所有商户的名称。CREATEPROCEDUREp_bname_list(INOUTbname_listVARCHAR(100))BEGINDECLAREdoneINTDEFAULT0;DECLAREv_bnameVARCHAR(20)DEFAULT'';DECLAREbname_cursorCURSORFORSELECTbnameFROMbusiness;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENbname_cursor;get_bname:LOOPFETCHbname_cursorINTOv_bname;IFdone=1THENLEAVEget_bname;ENDIF;SETbname_list=CONCAT(v_bname,';',bname_list);ENDLOOPget_bname;CLOSEbname_cursor;END;存储函数9.3存储函数和存储过程的区别

存储函数的限制比较多，例如不能用临时表,只能用表变量，而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out，inout)。调用时的不同。存储函数嵌入在SQL中使用，可以在select存储函数名(变量值)；存储过程通过call语句调用call存储过程名。参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数；存储过程的参数类型有三种，in、out和inout：9.3.1创建存储函数CREATEFUNCTION语句用于创建存储函数，其基本的语法格式为：CREATEFUNCTIONsp_name([func_parameter[,...]])RETURNStype[NOT]DETERMINISTICroutine_bodyfunc_parameter: param_nametype参数：（1）sp_name：存储函数的名称。（2）param_nametype：参数的名称和数据类型。存储函数只有输入参数，参数不能IN，OUT，INOUT修饰。（3）RETURNStype：指定存储函数返回值的数据类型。（4）[NOT]DETERMINISTIC：对于相同的输入参数，如果函数返回相同的结果，则被认为是确定性的，否则不是确定性的。必须决定一个存储函数是否是确定性的。（5）routine_body：可以是单个语句也可以是复合语句，存储过程中适用的SQL语句也适用于存储函数。9.3.1创建存储函数[例9-11]：创建函数NameById，根据输入的学号输出该学生的姓名。CREATEFUNCTIONNameById(search_idCHAR(12))RETURNSVARCHAR(20)DETERMINISTICBEGINRETURN(SELECTsnameFROMstudentWHERESID=search_id);END;9.3.2调用存储函数成功创建的存储函数可以使用SELECT进行调用，语法格式为：SELECTsp_name([param_name[,...]]);[例9-12]：调用存储函数NameByID查看学号为202003010004的学生姓名。SELECTNameByID('202003010004');9.3.3修改存储函数ALTERFUNCTION语句用于修改存储函数的某些相关特征。若要修改存储函数的内容，则需要先删除该存储函数，然后重新创建。其语法格式为：ALTERFUNCTION[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]parameter_data_type[=default]}[,...n]])RETURNSreturn_data_type[WITH<function_option>[,...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND[;]9.3.4删除存储函数DROPFUNCTION语句用于删除存储函数，其基本的语法格式为：DROPFUNCTION[IFEXISTS]sp_name;参数：（1）sp_name：要删除的存储函数的名称。（2）IFEXISTS：使用IFEXISTS可防止因删除不存在的存储函数而引发错误。[例9-13]：删除存储函数NameById。DROPFUNCTIONNameById;触发器9.49.4.1触发器的基本概念触发器（Trigger）是一种特殊类型的存储过程，采用事件驱动机制。当某个触发事件发生时，定义在触发器中的功能将被DBMS自动执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。当一个触发器建立后，它作为一个数据库对象被存储。常用的触发器:INSERT触发器、UPDATE触发器、DELETE触发器9.4.1触发器的基本概念触发器由三个部分组成∶事件、条件、动作。或称为触发事件，当某个事件发生的时候就运行触发器判断是否达到指定的条件，如果没有达到条件将不执行动作，可以留空，表示直接执行动作。条件通过后所要做的事情。9.4.2创建触发器CREATETRIGGER语句用于创建触发器。该语句指定了触发表、触发时机、触发事件和触发器的所有指令。其基本的语法格式为：CREATETRIGGERtrigger_name{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ONtbl_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_body参数：1）{BEFORE|AFTER}：触发时机，其中BEFORE为前触发型触发器，AFTER为后触发型触发器。前和后是指触发器在触发表中引发触发事件的数据操作语句之前还是之后执行。2）{INSERT|UPDATE|DELETE}：触发事件，即在表上执行哪些数据操纵语句时将激活触发器。必须至少指定一个选项，多个选项之间用逗号分开。3）ONtbl_nameFOREACHROW：MySQL只支持行级触发器，影响多少行触发器就会执行多少次。4）{FOLLOWS|PRECEDES}other_trigger_name：触发顺序。5）trigger_body：触发器主体。9.4.2创建触发器1）NEW表和OLD表MySQL触发器中定义了NEW和OLD两个虚表，用来临时存储触发表中使触发事件发生的那一行数据。NEW表和OLD表的使用如下表所示。触发器类型NEW表和OLD表的使用INSERT型触发器NEW表存储将要(BEFORE)或已经(AFTER)插入的数据行。DELETE型触发器OLD表存储将要或已经被删除的数据行。UPDATE型触发器OLD表存储将要或已经被更新的原始数据行，又需要使用NEW表存储将要或已经插入的数据行。9.4.2创建触发器2）前触发型触发器前触发型触发器成功执行后，再执行触发表中的触发语句。如果触发失败，则不执行后继的触发表中的触发语句。[例9-14]：创建前触发型触发器operation，保证只有状态正常且卡内余额大于消费金额的校园卡才能进行消费。CREATETRIGGERoperationBEFOREINSERTONsalebillFOREACHROWUPDATEcardSETbalance=balance-NEW.payamountWHERECID=NEW.CID;9.4.2创建触发器3）后触发型触发器后触发型触发器在触发表中触发语句成功执行并且所有的约束检查也成功完成后才执行。触发器触发失败时，需要回滚已经执行的语句，以保持数据一致性。[例9-15]：删除salebill表的前触发型触发器operation后，建立后触发型触发器operation2，生成消费记录后自动修改校园卡的余额。CREATETRIGGERoperation2AFTERINSERTONsalebillFOREACHROWUPDATEcardSETbalance=balance-NEW.payamountWHERECID=NEW.CID;9.4.2创建触发器4）多个触发器的执行一个表中可以同时存在多个触发器，对于相同事件触发的多个触发器，MSQL按照触发器创建的顺序进行调度。要改变触发器的执行顺序，可以在FOREACHROW子句后使用FOLLOWS或者PRECEDES。FOLLOWS表示新创建的触发器后执行，PRECEDES表示新创建的触发器先执行。相同事件触发的多个触发器中，无论触发器的执行顺序是怎么样的，只要有一个触发器的条件不满足，之前成功执行的所有触发器已经执行的操作都会回滚，之后的触发器不在执行，以此保持数据一致性。9.4.2创建触发器CREATETRIGGERoperationBEFOREINSERTONsalebillFOREACHROW UPDATEcardSETbalance=balance-NEW.payamount WHERECID=NEW.CID;

CREATETRIGGERoperation2BEFOREINSERTONsalebillFOREACHROWFOLLOWSoperationBEGIN DECLAREsCHAR(1); SELECTstateINTOsFROMcardwhereCID=NEW.CID; IFs='1'ORs='2'THENUPDATEcardSETs='3'WHERECID=NEW.CID; ENDIF;END;[例9-16]：删除salebill表之前建立的触发器，新建两个前触型触发器operation和operation2，operation的效果如例[9-14]，operation2保证只有正常状态，即state为0的校园卡可以消费。9.4.2创建触发器操作之前先查询一下即将进行操作的C00005校园卡的信息，如表右表所示。执行操作：C00005校园卡消费30元。INSERTINTOsalebill(CID,BID,payamount,saledate)VALUES('C00005','B003',30,'2020-8-10');SELECTCID,balanceFROMcardWHERECID='C00005';这个INSERT操作触发了触发器，满足卡余额大于消费数且此卡的state=0，两个触发器的条件都满足，都可以成功执行，C00005的余额被修改，结果如右表所示。SELECT*FROMcardWHERECID='C00005';执行操作：C00005校园卡继续消费50元。INSERTINTOsalebill(CID,BID,payamount,saledate)VALUES('C00005','B003',50,'2020-8-10');执行第一个触发器时，消费金额大于卡余额，违反了card表的约束条件CHECKbalance>=0。触发器执行失败。系统给出报错信息“3819-Checkconstraint'card_chk_1'isviolated”。9.4.3修改和删除触发器修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。使用DROP语句删除触发器。基本的语法格式为：DROPTRIGGER[IFEXISTS]trigger_name第10章数据库备份与恢复本章介绍数据库备份与恢复的基本概念以及MySQL提供的数据备份与数据恢复方法。数据库系统原理及应用10.1基本概念10.2逻辑备份与恢复10.4NavicatPremium操作主要内容10.3表的导出与导入基本概念10.110.1基本概念数据库故障的种类：1）操作系统崩溃；2）电源故障；3）文件系统崩溃；4）存储介质故障；5）服务器瘫痪；6）用户误操作；7）病毒破坏；8）自然灾害备份是指定期或不定期地对数据库数据进行复制。可以复制到本地机器上，也可以复制到其它介质上。是保证系统安全的一项重要措施。备份就是为了最大限度地降低灾难性数据丢失的风险，从数据库中定期保存用户对数据库所做的修改，用以将数据库从错误状态下恢复到某一正确状态的副本。使用数据管理管理软件，比如NavicatPremium就提供了一套功能强大的、安全的数据备份和恢复工具，数据库的备份和恢复可以在系统发生错误的时候，抢救恢复以前的数据。10.1.1备份类型按数据库备份的方法划分：逻辑备份逻辑备份是指将数据库中的数据按照预定义的逻辑格式，生成一组CREATEDATABASE、CREATETABLE等定义数据库结构的语句和INSERT、定界文本文件等定义数据库内容的语句。物理备份：物理备份是指备份时直接复制数据库的数据文件。根据备份时数据服务是否在线可以把物理备份分为冷备份、热备份和温备份。冷备份：指在关闭MySQL服务器、停用数据库的读写操作下所做的备份。热备份：指在停用数据库的写入操作，但不停用读操作的情况下进行备份。温备份：指在不停用数据库所提供的数据服务的读写操作下所做的备份。10.1.1备份类型根据备份的数据集的范围可以划分全量备份：又称为完全备份、完整备份、全备，指对数据库中的全部信息进行备份，包括数据库的数据文件、日志文件、数据库对象以及其它相关信息。特点：消耗时间和资源增量备份：备份从最近的一次备份之后对数据所作的更新。优点：速度快，使备份一个数据库需要的时间最小，因为它要备份的数据量比完全备份小得多。通过增加差异备份的备份次数，可以降低丢失数据的风险。在MySQL中，第一次增量备份是基于全量备份的，之后的增量备份是基于最近一次的备份（可能是全量备份，也可能是增量备份）10.1.1备份类型事务日志备份：事务日志备份是对数据库发生的事务进行备份，它可以在相应的数据库备份的基础上，尽可能的恢复最新的数据库记录。由于它仅对数据库事务日志进行备份，所以其需要的磁盘空间和备份时间都比数据库备份少得多。执行事务日志备份主要有两个原因：1）是要在一个安全的介质上存储自上次事务日志备份或数据库备份以来修改的数据；2）是要合适地关闭事务日志到它的活动部分的开始。10.1.2备份和恢复策略完整备份包括对数据和日志的备份特点:适合数据库数据量不是很大,数据更改不频繁比如在每天0:00进行一次完整备份的策略，如图所示10.1.2备份和恢复策略完整备份加日志备份用户不允许丢失太多数据，而且又不希望经常进行完整备份，这时可以在完整备份中间加入若干次日志备份。

比如如图所示，每天0:00进行一次完整备份，再间隔几小时进行一次日志备份10.1.2备份和恢复策略完整备份加增量备份再加日志备份当用户希望将进行完整备份的时间间隔再加大，如果还采用完整备份加日志备份的方法恢复起来比较耗时。在此种情况下可取完整备份加增量备份再加日志备份的策略比如每周的周日进行一次完整备份。这种策略的优势是备份和恢复的速度都比较快，而且当系统出现故障时，丢失的数据也相对较少。10.1.2备份和恢复策略恢复数据库时的顺序：一般先恢复最近的完全备份然后按增量备份的先后顺序恢复从最近的完全备份之后的所有增量备份。最后恢复日志备份逻辑备份与恢复10.210.2逻辑备份与恢复MySQL自带的mysqldump工具、多线程备份工具mydumper，以及SELECT...INTOOUTFILE语句都可以实现逻辑备份。要恢复逻辑备份，可以使用MySQL客户端处理SQL格式的备份文件、与mydumper配套的myloader工具，或者使用LOADDATAINFILE语句加载带分隔符的文本格式的备份文件。10.2.1用mysqldump工具进行逻辑备份mysqldump是MySQL自带的数据库逻辑备份工具,适用于所有的存储引擎,支持温备份，对于InnoDB存储引擎支持热备份。使用mysqldump可以备份一个数据库，也可以备份多个数据库或者备份所有数据库。生成的备份文件可以是SQL格式的文件，也可以是带分隔符的定界文本格式的文件，还可以是XML格式的文件。通常情况下，mysqldump是备份成后缀名为.sql的SQL格式的文件。mysqldump需要使用shell脚本。根据要备份的数据库对象的不同，mysqldump语句有以下三种格式：（1）备份一个数据库（2）备份多个数据库（3）备份所有数据库10.2.1用mysqldump工具进行逻辑备份（1）备份一个数据库语句的基本语法格式为：shell>mysqldump-u[uname]-p[pass]dbtb1tb2>dump.sql参数：（1）-u[uname]-p[pass]：连接MySQL的用户名和密码。在shell脚本中输入密码时屏幕上不显示任何信息，不会出现“*”或其他符号，只要密码输入正确就可以了。（2）db：需要备份的表所在的数据库的名称。（3）tb1tb2：需要备份的表的名称。没有该参数时将备份整个数据库。（4）dump.sql：SQL格式的备份文件的名称。文件名前可加上绝对路径。10.2.1用mysqldump工具进行逻辑备份[例10-1]：以root用户身份备份校园卡管理数据库cardmanagement中的salebill表，备份文件名保存在指定目录(此处以笔者的电脑桌面文件夹示例，读者根据实际情况设定目录)，备份文件名称为salebill.sql。打开终端(Terminal)，输入如下语句：mysqldump-uroot-pcardmanagementsalebill>'/Users/zqf/Desktop/salebill.sql'10.2.1用mysqldump工具进行逻辑备份2）备份多个数据库mysqldump语句的--databases选项用于备份多个数据库，其基本的语法格式为：shell>mysqldump--databasesdb1db2db3>dump.sql参数：--databasesdb1db2db3：--databases后的所有名称都被视为数据库名称。只备份一个数据库的情况下，可以省略--databases选项。省略--databases的情况下，备份文件中不包含CREATEDATABASE和USE语句。使用--databases选项时，mysqldump在每个数据库的备份文件之前写入CREATEDATABASE和USE语句，以确保在重新加载备份文件时，如果不存在数据库，将先创建数据库并将其设置为默认数据库，以便将数据库内容加载到备份时的同名数据库中。如果使用--add-drop-database选项，mysqldump还在每个CREATEDATABASE语句之前写入DROPDATABASE语句，在加载备份文件重新创建数据库之前强制删除数据库。10.2.1用mysqldump工具进行逻辑备份[例10-2]：使用root用户备份cardmanagement数据库和mysql数据库，备份文件名为backup.sql。打开终端，输入如下mysqldump语句：mysqldump-uroot-p--databasescardmanagementmysql>'/Users/zqf/Desktop/backup.sql'10.2.1用mysqldump工具进行逻辑备份3）备份所有数据库mysqldump语句的--all-databases选项用于备份所有数据库，其基本的语法格式为：shell>mysqldump[--all-databases]>dump.sql参数：[--all-databases]：省略该选项的情况下，备份文件中不包含CREATEDATABASE和USE语句。例10-3：使用root用户备份所有数据库，备份文件名为all.sql。mysqldump-u-root-p--all-databases>'/Users/zqf/Desktop/all.sql'10.2.2加载SQL格式的备份文件恢复数据库1）用mysql语句恢复数据库使用mysql语句直接在终端加载备份文件，其基本的语法格式为：shell>mysql-u[uname]-p[pass]<dump.sql[例10-4]：恢复cardmanagement数据库中的salebill表。在终端输入命令：mysql-uroot-pcardmanagement</Users/zqf/Desktop/salebill.sql按照提示输入连接数据库的密码，输入密码后将完成数据恢复。10.2.2加载SQL格式的备份文件恢复数据库2）用source语句恢复数据库在MySQL内部使用source语句加载备份文件，其基本的操作步骤是：（1）打开终端后，先连接MySQL。基本的语法格式为：mysql-u[uname]-p[pass]（2）如果要恢复的数据库不存在，需要先创建同名数据库，并切换为当前数据库。基本的语法格式为：CREATEDATABASEdb;USEdb;（3）使用source语句加载备份文件。基本的语法格式为：sourcedump.sql10.2.2加载SQL格式的备份文件恢复数据库[例10-5]：恢复cardmanagement数据库中的salebill表。（1）打开终端，按照提示输入连接数据库的密码，进入MySQL环境:

mysql-uroot-p（2）在MySQL环境下，把cardmanagement数据库切换为当前数据库：

usecardmanagement（3）恢复salebill表：

source/Users/zqf/Desktop/salebill.sql[例10-6]：重新加载backup.sql，恢复cardmanagement数据库和mysql数据库。shell>mysql-uroot-p<backup.sql或者在MySQL环境下，执行sourcebackup.sql表的导出与导入10.310.3.1用SELECT......INTOOUTFILE语句导出文本文件SELECT......INTOOUTFILE语句可用于将表的内容导出为一个文本文件转储到服务器上，并且导出文件不能已存在。其基本的语法格式为：SELECTselect_expr[,select_expr]...INTOOUTFILE'file_name'[FIELDSTERMINATEDBY'string'][FIELDSENCLOSEDBY'char'][FIELDSESCAPEDBY'char'][LINESSTARTINGBY'string'][LINESTERMINATEDBY'string']10.3.1用SELECT......INTOOUTFILE语句导出文本文件参数：（1）SELECT子句：查询需要备份的数据。（2）file_name：存放输出数据的文件名。（3）FIELDSTERMINATEDBY'string'：设置字段之间的分隔符，可以为单个或多个字符。默认值是“\t”。（4）FIELDSENCLOSEDBY'char'：设置字符来括住字段的值，只能为单个字符。默认情况下不使用任何符号。（5）FIELDSESCAPEDBY'char'：设置转义字符，只能为单个字符。默认值为“\”。（6）LINESSTARTINGBY'string'：设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符。（7）LINESTERMINATEDBY'string'：设置每行数据结尾的字符，可以为单个或多个字符。默认值是“\n”。（8）FIELDS和LINES两个子句都是自选的，但是如果两个子句都被指定了，FIELDS必须位于LINES的前面。10.3.1用SELECT......INTOOUTFILE语句导出文本文件[例10-7]：把student表的数据备份到桌面的studen