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