MySQL的高级应用.ppt

第14章 MySQL的高级应用MySQL 5.0之前的版本并不支持存储过程，随之前的版本并不支持存储过程，随着着MySQL技术的日趋完善，存储过程将在以后技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。曾几何时，虽然拥的项目中得到广泛的应用。曾几何时，虽然拥有高效等优点，有高效等优点，MySQL还是因为不支持存储过还是因为不支持存储过程、视图等功能而受到很多程序员的抵触，随程、视图等功能而受到很多程序员的抵触，随着着MySQL的发展，这些高级功能终于在的发展，这些高级功能终于在MySQL 5.0以后的版本中得以应用。以后的版本中得以应用。本章我们将介绍本章我们将介绍MySQL5的新特性：存储过程、的新特性：存储过程、触发器和视图。触发器和视图。214.1 MySQL与存储过程存储过程能够允许绝大部分的数据库访问逻辑与程序存储过程能够允许绝大部分的数据库访问逻辑与程序逻辑分离开来。逻辑分离开来。存储过程的一个优点是，SQL可以被预先编译，由此提高了程序的速度，因为存储过程包含程序逻辑，可以在数据库服务器端执行更多的处理，由于存储在服务器上，所以存储过程是可复用的组件，客户端不管使用什么脚本语言与数据库连接，都可以直接调用它们；另一个优点是存储程序可以提供改良后的性能，因为只有较少的信息需要在服务器和客户端之间传送，程序的代码变得更加小巧和容易理解。代价是增加了数据库服务器系统的负荷，因为更多的代价是增加了数据库服务器系统的负荷，因为更多的工作要在服务器端完成。工作要在服务器端完成。314.1.1 存储过程的创建一个存储过程包括名字、参数列表，并可以包一个存储过程包括名字、参数列表，并可以包括很多括很多SQL语句。在语句。在MySQL中，创建存储过程中，创建存储过程的语句是的语句是CREATE PROCEDURE。其用法是：。其用法是：CREATE PROCEDURE sp_name(proc_parameter,.)characteristic.routine_bodyMySQL中存储过程的建立以关键字中存储过程的建立以关键字CREATE PROCEDURE开始，后面是存储过程的名称和开始，后面是存储过程的名称和参数、复合语句和变量，这些将在下面的小节参数、复合语句和变量，这些将在下面的小节中依次说明。中依次说明。414.1.2 存储过程的参数1.IN输入参数输入参数表示要向存储过程中传入的参数，该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认参数类型。2.OUT输出参数输出参数的值可在存储过程内部被改变，并可返回。3.INOUT输入输出参数输入输出参数在调用时指定，并且可被改变和返回。514.1.3 复合语句CREATE PROCEDURE命令的参数后面，就是存储命令的参数后面，就是存储过程的主体了，在主体中书写常规的过程的主体了，在主体中书写常规的SQL语句，每句语句，每句以分号以分号(;)结束，主体中书写包含结束，主体中书写包含INSERT、UPDATE、DELETE、SELECT、DROP、CREATE、REPLACE的语句都是合法的，的语句都是合法的，SET、COMMIT以及以及ROLLBACK也是合法的。也是合法的。例如：例如：CREATE PROCEDURE sp_name()BEGINSET a=5;SET b=5;INSERT INTO t VALUES(a);SELECT s1*a FROM t WHERE s1=b;END;614.1.4 变量在复合语句中，声明变量的指令是在复合语句中，声明变量的指令是DECLARE，DECLARE仅被用于仅被用于BEGIN/END复合语句内，并且必须被书写在复合语句的开头，书写顺序依复合语句内，并且必须被书写在复合语句的开头，书写顺序依次是次是DECLARE、变量名、变量类型，见如下、变量名、变量类型，见如下DECLARE语句的例子：语句的例子：CREATE PROCEDURE sp_name()BEGINDECLARE a INT;DECLARE b INT;SET a=5;SET b=5;INSERT INTO t VALUES(a);SELECT s1*a FROM t WHERE s1=b;END;还可以在声明变量的同时使用还可以在声明变量的同时使用DEFAULT，赋给默认值：，赋给默认值：CREATE PROCEDURE sp_name()BEGINDECLARE a,b INT DEFAULT 5;/变量a、b被赋默认值5INSERT INTO t VALUES(a);SELECT s1*a FROM t WHERE s1=b;END;714.1.5 条件语句在复合语句中可以使用在复合语句中可以使用if条件机制，来根据不同条件机制，来根据不同的情况选择不同的分支运行，和的情况选择不同的分支运行，和PHP的的if条件语条件语句类似。用法如下：句类似。用法如下：if search_condition thenstatementelsestatementend if;if实现了一个基本的条件机制，如果实现了一个基本的条件机制，如果search_condition条件求值为真，相应的条件求值为真，相应的SQL语句列表被执行。语句列表被执行。814.1.6 循环语句1.WHILE循环语法形式为：WHILE expression DOstatementsEND WHILE;2.LOOP循环语法形式为：LOOPstatementsEND LOOP;3.REPEAT UNTIL循环语法形式为：REPEATstatementsUNTIL expressionEND REPEAT;914.1.7 存储过程的查看、更改和删除(1)查看当前数据库中所有已存查看当前数据库中所有已存在的存储过程：在的存储过程：show procedure status;(2)查看某一个具体的存储过程：查看某一个具体的存储过程：show create procedure sp_name;1014.1.8 PHP5调用存储过程每次验证示例的存储过程时，我们都是使用每次验证示例的存储过程时，我们都是使用CALL关键字后面加存储过程名和参数来调用定关键字后面加存储过程名和参数来调用定义好的存储过程，用法是：义好的存储过程，用法是：CALL sp_name()需要再次重申的是，即使该存储过程没有参数需要再次重申的是，即使该存储过程没有参数传递，存储过程名称后面也必须加括号。在一传递，存储过程名称后面也必须加括号。在一个存储过程中是不能删除另一个存储过程的，个存储过程中是不能删除另一个存储过程的，只能调用另一个存储过程。只能调用另一个存储过程。1114.2 MySQL与触发器触发程序是与表有关的命名数据库对象，当表上出现触发程序是与表有关的命名数据库对象，当表上出现特定的事件时，将激活该对象。触发器通常用于强制特定的事件时，将激活该对象。触发器通常用于强制业务规则，可以确保数据的完整性和一致性。业务规则，可以确保数据的完整性和一致性。对数据库中的表而言，创建触发器可以用来在特定事对数据库中的表而言，创建触发器可以用来在特定事件发生时执行，例如，当表中插入新行时触发某项行件发生时执行，例如，当表中插入新行时触发某项行为；此外，在论坛或者留言板的设计中，可以通过触为；此外，在论坛或者留言板的设计中，可以通过触发器实现对帖子或者留言的自动回复，还可以通过触发器实现对帖子或者留言的自动回复，还可以通过触发器实现过滤指定的非法关键字，将含有指定非法关发器实现过滤指定的非法关键字，将含有指定非法关键字的帖子或者留言自动删除。键字的帖子或者留言自动删除。触发器在数据库系统开发过程中具有非常重要的作用，触发器在数据库系统开发过程中具有非常重要的作用，例如可以防止有害数据录入数据库，可以改变或取消例如可以防止有害数据录入数据库，可以改变或取消INSERT、UPDATE和和DELETE语句的执行及在一个语句的执行及在一个会话中监听数据库中数据的变化。会话中监听数据库中数据的变化。1214.2.1 触发器的创建与触发使用使用CREATE TRIGGER可以创建一个触发器，可以创建一个触发器，它的用法是：它的用法是：CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_nameFOR EACH ROWtrigger_stmt触发器必须有名字，最多触发器必须有名字，最多64个字符，可能后面个字符，可能后面会附有分隔符，它和会附有分隔符，它和MySQL中其他对象的命名中其他对象的命名方式基本相像。方式基本相像。1314.2.2 查看已创建的触发器要想查看要想查看MySQL数据库中已经创数据库中已经创建的触发器的详细信息，可以通建的触发器的详细信息，可以通过过SHOW TRIGGERS语句来实现，语句来实现，使用该语句前应先指明要查看的使用该语句前应先指明要查看的数据库。数据库。1414.2.3 触发器的删除DROP TRIGGER语句可以删语句可以删除一个不再需要的触发器，除一个不再需要的触发器，它的用法是：它的用法是：DROP TRIGGER schema_name.trigger_name1514.3 MySQL与视图视图类似于多表查询，也类似于一个普通的表，视图类似于多表查询，也类似于一个普通的表，是从一个或多个表或其他视图中导出的表，其是从一个或多个表或其他视图中导出的表，其结构和数据是建立在对表的查询基础上的。结构和数据是建立在对表的查询基础上的。与表一样，视图也是包括几个被定义的数据列与表一样，视图也是包括几个被定义的数据列和多个数据行，但这些数据均来源于其所引用和多个数据行，但这些数据均来源于其所引用的表。的表。1614.3.1 视图的本质所谓视图，是与特定查询相匹配的表结构的数据快照，所谓视图，是与特定查询相匹配的表结构的数据快照，通过视图，可以在单个表中表示复杂连接的结果。通过视图，可以在单个表中表示复杂连接的结果。应用视图主要有两大优点。应用视图主要有两大优点。一方面是视图可以隐藏一些数据。另一方面是可使复杂的查询易于理解和使用，大大简化了用户对数据的操作。此外，由于视图可以有选择地列出表的字段，所以视此外，由于视图可以有选择地列出表的字段，所以视图能够实现让不同的用户以不同的方式看到不同或相图能够实现让不同的用户以不同的方式看到不同或相同的数据集，因此，当有许多不同级别的用户共用同同的数据集，因此，当有许多不同级别的用户共用同一数据库时，这显得极为重要。一数据库时，这显得极为重要。1714.3.2 视图的创建使用使用CREATE VIEW语句可以创建一个新视图，语句可以创建一个新视图，它的用法是：它的用法是：CREATE OR REPLACE ALGORITHM=UNDEFINED|MERGE|TEMPTABLEVIEW view_name(column_list)AS select_statementWITH CASCADED|LOCAL CHECK OPTION视图必须具有唯一的字段名，不能有重复，就视图必须具有唯一的字段名，不能有重复，就像基表那样。默认情况下，由像基表那样。默认情况下，由SELECT语句检语句检索的字段名将用作视图字段名。索的字段名将用作视图字段名。1814.3.3 视图的查看、修改和删除(1)如果要查看一个视图是如何定义的，我们可以使用如果要查看一个视图是如何定义的，我们可以使用SHOW命命令：令：SHOW CREATE VIEW view_name执行该语句时，执行该语句时，MySQL将向我们显示定义该视图时的将向我们显示定义该视图时的CREATE VIEW语句内容。语句内容。(2)ALTER VIEW语句可以修改一个已有视图的定义，它的用法语句可以修改一个已有视图的定义，它的用法是：是：ALTER ALGORITHM=UNDEFINED|MERGE|TEMPTABLE VIEW view_name(column_list)AS select_statement WITH CASCADED|LOCAL CHECK OPTION各参数说明与各参数说明与CREATE VIEW相同。相同。(3)与删除表、存储过程一样，删除视图的语句仍然是与删除表、存储过程一样，删除视图的语句仍然是DROP：DROP VIEW IF EXISTS view_name,view_name.RESTRICT|CASCADEDROP VIEW可以一次删除一个或多个视图。可以一次删除一个或多个视图。1914.3.4 视图的应用首先，我们需要创建两个表，一个表首先，我们需要创建两个表，一个表member存储健身俱乐部会员的注册信息，另一个表存储健身俱乐部会员的注册信息，另一个表fitness存储会员每次健身的记录，这两个表的存储会员每次健身的记录，这两个表的结构分别如图结构分别如图14-1和图和图14-2所示。所示。2014.3.4 视图的应用两个表的记录依靠会员两个表的记录依靠会员id建立关联关系，其中建立关联关系，其中存储的数据分别如图存储的数据分别如图14-3和和14-4所示。所示。2114.3.4 视图的应用下面将创建一个新的视图，从这两个表中获取下面将创建一个新的视图，从这两个表中获取3个字段：会员编个字段：会员编号、姓名和健身记录。从图号、姓名和健身记录。从图14-4中红圈圈中的地方，可以发现中红圈圈中的地方，可以发现创建新视图的链接。创建视图的页面如图创建新视图的链接。创建视图的页面如图14-5所示。所示。视图创建后，将会和其他表一起存储在数据库中，注意观看下视图创建后，将会和其他表一起存储在数据库中，注意观看下图划红圈的地方，新视图的类型是图划红圈的地方，新视图的类型是View，如图，如图14-6所示。所示。2214.3.4 视图的应用新视图的结构如图新视图的结构如图14-7所示。所示。新视图列出的记录内容如图新视图列出的记录内容如图14-8所示。所示。2314.4 本 章 小 结随着随着MySQL 5.0以上版本对存储过程、触发器以上版本对存储过程、触发器和视图这些高级功能的提供和日益完善，和视图这些高级功能的提供和日益完善，MySQL正在愈发的健壮和成熟，在数据库领域正在愈发的健壮和成熟，在数据库领域必将占据越来越重要的地位，应用也必将越来必将占据越来越重要的地位，应用也必将越来越广泛。越广泛。这些高级功能的出现，增强了数据库服务端的这些高级功能的出现，增强了数据库服务端的服务能力，减少了网络通信流量，客户端的代服务能力，减少了网络通信流量，客户端的代码就可以更多地集中到程序功能的实现上。码就可以更多地集中到程序功能的实现上。24