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 BEGINSET a 5 SET b 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语句可以删除一个不再需要的触发器 它的用法是 DROPTRIGGER schema name trigger name 16 14 3MySQL与视图 视图类似于多表查询 也类似于一个普通的表 是从一个或多个表或其他视图中导出的表 其结构和数据是建立在对表的查询基础上的 与表一样 视图也是包括几个被定义的数据列和多个数据行 但这些数据均来源于其所引用的表 17 14 3 1视图的本质 所谓视图 是与特定查询相匹配的表结构的数据快照 通过视图 可以在单个表中表示复杂连接的结果 应用视图主要有两大优点 一方面是视图可以隐藏一些数据 另一方面是可使复杂的查询易于理解和使用 大大简化了用户对数据的操作 此外 由于视图可以有选择地列出表的字段 所以视图能够实现让不同的用户以不同的方式看到不同或相同的数据集 因此 当有许多不同级别的用户共用同一数据库时 这显得极为重要 18 14 3 2视图的创建 使用CREATEVIEW语句可以创建一个新视图 它的用法是 CREATE ORREPLACE ALGORITHM UNDEFINED MERGE TEMPTABLE VIEWview name column list ASselect statement WITH CASCADED LOCAL CHECKOPTION 视图必须具有唯一的字段名 不能有重复 就像基表那样 默认情况下 由SELECT语句检索的字段名将用作视图字段名 19 14 3 3视图的查看 修改和删除 1 如果要查看一个视图是如何定义的 我们可以使用SHOW命令 SHOWCREATEVIEWview name执行该语句时 MySQL将向我们显示定义该视图时的CREATEVIEW语句内容 2 ALTERVIEW语句可以修改一个已有视图的定义 它的用法是 ALTER ALGORITHM UNDEFINED MERGE TEMPTABLE VIEWview name column list ASselect statement WITH CASCADED LOCAL CHECKOPTION 各参数说明与CREATEVIEW相同 3 与删除表 存储过程一样 删除视图的语句仍然是DROP DROPVIEW IFEXISTS view name view name RESTRICT CASCADE DROPVIEW可以一次删除一个或多个视图 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所