MySQL的高级应用.ppt

2,第14章MySQL的高级应用,MySQL5.0之前的版本并不支持存储过程，随着MySQL技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。曾几何时，虽然拥有高效等优点，MySQL还是因为不支持存储过程、视图等功能而受到很多程序员的抵触，随着MySQL的发展，这些高级功能终于在MySQL5.0以后的版本中得以应用。本章我们将介绍MySQL5的新特性：存储过程、触发器和视图。,3,14.1MySQL与存储过程,存储过程能够允许绝大部分的数据库访问逻辑与程序逻辑分离开来。存储过程的一个优点是，SQL可以被预先编译，由此提高了程序的速度，因为存储过程包含程序逻辑，可以在数据库服务器端执行更多的处理，由于存储在服务器上，所以存储过程是可复用的组件，客户端不管使用什么脚本语言与数据库连接，都可以直接调用它们；另一个优点是存储程序可以提供改良后的性能，因为只有较少的信息需要在服务器和客户端之间传送，程序的代码变得更加小巧和容易理解。代价是增加了数据库服务器系统的负荷，因为更多的工作要在服务器端完成。,4,14.1.1存储过程的创建,一个存储过程包括名字、参数列表，并可以包括很多SQL语句。在MySQL中，创建存储过程的语句是CREATEPROCEDURE。其用法是：CREATEPROCEDUREsp_name(proc_parameter,.)characteristic.routine_bodyMySQL中存储过程的建立以关键字CREATEPROCEDURE开始，后面是存储过程的名称和参数、复合语句和变量，这些将在下面的小节中依次说明。,5,14.1.2存储过程的参数,1.IN输入参数输入参数表示要向存储过程中传入的参数，该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认参数类型。2.OUT输出参数输出参数的值可在存储过程内部被改变，并可返回。3.INOUT输入输出参数输入输出参数在调用时指定，并且可被改变和返回。,6,14.1.3复合语句,CREATEPROCEDURE命令的参数后面，就是存储过程的主体了，在主体中书写常规的SQL语句，每句以分号(;)结束，主体中书写包含INSERT、UPDATE、DELETE、SELECT、DROP、CREATE、REPLACE的语句都是合法的，SET、COMMIT以及ROLLBACK也是合法的。例如：CREATEPROCEDUREsp_name()BEGINSETa=5;SETb=5;INSERTINTOtVALUES(a);SELECTs1*aFROMtWHEREs1=b;END;,7,14.1.4变量,在复合语句中，声明变量的指令是DECLARE，DECLARE仅被用于BEGIN/END复合语句内，并且必须被书写在复合语句的开头，书写顺序依次是DECLARE、变量名、变量类型，见如下DECLARE语句的例子：CREATEPROCEDUREsp_name()BEGINDECLAREaINT;DECLAREbINT;SETa=5;SETb=5;INSERTINTOtVALUES(a);SELECTs1*aFROMtWHEREs1=b;END;还可以在声明变量的同时使用DEFAULT，赋给默认值：CREATEPROCEDUREsp_name()BEGINDECLAREa,bINTDEFAULT5;/变量a、b被赋默认值5INSERTINTOtVALUES(a);SELECTs1*aFROMtWHEREs1=b;END;,8,14.1.5条件语句,在复合语句中可以使用if条件机制，来根据不同的情况选择不同的分支运行，和PHP的if条件语句类似。用法如下：ifsearch_conditionthenstatementelsestatementendif;if实现了一个基本的条件机制，如果search_condition条件求值为真，相应的SQL语句列表被执行。,9,14.1.6循环语句,1.WHILE循环语法形式为：WHILEexpressionDOstatementsENDWHILE;2.LOOP循环语法形式为：LOOPstatementsENDLOOP;3.REPEATUNTIL循环语法形式为：REPEATstatementsUNTILexpressionENDREPEAT;,10,14.1.7存储过程的查看、更改和删除,(1)查看当前数据库中所有已存在的存储过程：showprocedurestatus;(2)查看某一个具体的存储过程：showcreateproceduresp_name;,11,14.1.8PHP5调用存储过程,每次验证示例的存储过程时，我们都是使用CALL关键字后面加存储过程名和参数来调用定义好的存储过程，用法是：CALLsp_name()需要再次重申的是，即使该存储过程没有参数传递，存储过程名称后面也必须加括号。在一个存储过程中是不能删除另一个存储过程的，只能调用另一个存储过程。,12,14.2MySQL与触发器,触发程序是与表有关的命名数据库对象，当表上出现特定的事件时，将激活该对象。触发器通常用于强制业务规则，可以确保数据的完整性和一致性。对数据库中的表而言，创建触发器可以用来在特定事件发生时执行，例如，当表中插入新行时触发某项行为；此外，在论坛或者留言板的设计中，可以通过触发器实现对帖子或者留言的自动回复，还可以通过触发器实现过滤指定的非法关键字，将含有指定非法关键字的帖子或者留言自动删除。触发器在数据库系统开发过程中具有非常重要的作用，例如可以防止有害数据录入数据库，可以改变或取消INSERT、UPDATE和DELETE语句的执行及在一个会话中监听数据库中数据的变化。,13,14.2.1触发器的创建与触发,使用CREATETRIGGER可以创建一个触发器，它的用法是：CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_stmt触发器必须有名字，最多64个字符，可能后面会附有分隔符，它和MySQL中其他对象的命名方式基本相像。,14,14.2.2查看已创建的触发器,要想查看MySQL数据库中已经创建的触发器的详细信息，可以通过SHOWTRIGGERS语句来实现，使用该语句前应先指明要查看的数据库。,15,14.2.3触发器的删除,DROPTRIGGER语句可以删除一个不再需要的触发器，它的用法是：DROPTRIGGERschema_name.trigger_name,16,14.3MySQL与视图,视图类似于多表查询，也类似于一个普通的表，是从一个或多个表或其他视图中导出的表，其结构和数据是建立在对表的查询基础上的。与表一样，视图也是包括几个被定义的数据列和多个数据行，但这些数据均来源于其所引用的表。,17,14.3.1视图的本质,所谓视图，是与特定查询相匹配的表结构的数据快照，通过视图，可以在单个表中表示复杂连接的结果。应用视图主要有两大优点。一方面是视图可以隐藏一些数据。另一方面是可使复杂的查询易于理解和使用，大大简化了用户对数据的操作。此外，由于视图可以有选择地列出表的字段，所以视图能够实现让不同的用户以不同的方式看到不同或相同的数据集，因此，当有许多不同级别的用户共用同一数据库时，这显得极为重要。,18,14.3.2视图的创建,使用CREATEVIEW语句可以创建一个新视图，它的用法是：CREATEORREPLACEALGORITHM=UNDEFINED|MERGE|TEMPTABLEVIEWview_name(column_list)ASselect_statementWITHCASCADED|LOCALCHECKOPTION视图必须具有唯一的字段名，不能有重复，就像基表那样。默认情况下，由SELECT语句检索的字段名将用作视图字段名。,19,14.3.3视图的查看、修改和删除,(1)如果要查看一个视图是如何定义的，我们可以使用SHOW命令：SHOWCREATEVIEWview_name执行该语句时，MySQL将向我们显示定义该视图时的CREATEVIEW语句内容。(2)ALTERVIEW语句可以修改一个已有视图的定义，它的用法是：ALTERALGORITHM=UNDEFINED|MERGE|TEMPTABLEVIEWview_name(column_list)ASselect_statementWITHCASCADED|LOCALCHECKOPTION各参数说明与CREATEVIEW相同。(3)与删除表、存储过程一样，删除视图的语句仍然是DROP：DROPVIEWIFEXISTSview_name,view_name.RESTRICT|CASCADEDROPVIEW可以一次删除一个或多个视图。,20,14.3.4视图的应用,首先，我们需要创建两个表，一个表member存储健身俱乐部会员的注册信息，另一个表fitness存储会员每次健身的记录，这两个表的结构分别如图14-1和图14-2所示。,21,14.3.4视图的应用,两个表的记录依靠会员id建立关联关系，其中存储的数据分别如图14-3和14-4所示。,22,14.3.4视图的应用,下面将创建一个新的视图，从这两个表中获取3个字段：会员编号、姓名和健身记录。从图14-4中红圈圈中的地方，可以发现创建新视图的链接。创建视图的页面如图14-5所示。视图创建后，将会和其他表一起存储在数据库中，注意观看下图划红圈的地方，新视图的类型是View，如图14-6所示。,