第22章 使用视图
视图本身不包含数据, 包含的是一个SQL查询。
22.1 视图
- 为什么使用视图:
- 重用SQL语句
- 简化复杂的SQL语句
- 使用表的组成部分而不是整个表
- 保护数据
- 更改数据格式和显示
- 性能问题
- 由于视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。 因此当视图的实现细节很复杂并且大量使用视图时,可能会导致性能严重下降。
- 视图的规则和限制
- 视图必须唯一命名(不能去与别的视图或表相同的名字)
- 对于可创建的视图数目没有限制
- 为了创建视图,必须具有足够的访问权限。
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY可以用在视图中,但如果从该视图检索数据的SELECT语句中也含有ORDER BY,则该视图中的ORDER BY将被覆盖。
- 视图不能索引,也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
22.2 使用视图
- 创建视图
- 视图用CREATE VIEW语句来创建
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
- 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。
- 创建可重用的视图
- 创建不受特定数据限制的视图是一种好办法。
- 用视图重新格式化检索出的数据
- 用视图过滤不想要的数据
- 如果从视图检索数据时使用了一条WHERE子句,则两组子句会自动组合。
- 使用视图简化计算字段的使用
- 更新视图
- 通常情况下,视图是可更新的。如INSERT,DELETE,UPDATE。
若视图定义中有以下操作,则不能进行视图的更新:
- 分组(使用GROUP BY 和HAVING)
- 联结
- 子查询
- 并
- 聚集函数(MIN()、MAX()、SUM()等)
- DISTINCT
- 导出(计算)列
- 通常情况下,视图是可更新的。如INSERT,DELETE,UPDATE。
- 一般,视图应用于检索, 而不用与更新。
第23章 使用存储过程
23.1 存储过程: 为以后使用而保存的一条或多条SQL语句的集合。
23.2 为什么要使用存储过程 (简单、安全、高性能)
- 优点
- 通过把处理封装在容易使用的单元中,简化复杂的操作。
- 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。
- 简化对变动的管理。提升安全性。
- 提高性能。
- 缺点
- 编写比一般的SQL语句复杂
- 可能没有创建存储过程的安全访问权限。
23.3 使用存储过程
- 执行存储过程
CALL procudure_name(@param_name1, @param_name2, @param_name3, ...)
- 创建存储过程
CREATE PROCEDURE procedure_name(IN/OUT/INOUT@param_name1, ...)
BEGIN
SELECT 语句;
END;
- 删除存储过程
DROP PROCEDURE procedure_name;
- 删除时只需给出存储过程名称即可,不需要加上()
- 如果指定的过程不存在,则DROP PROCEDURE将产生错误。此时可使用DROP PROCEDURE IF EXISTS。
- 使用参数
- 关键字:
- IN 用于传入参数值
- OUT 用于传出运行结果
- INOUT 用于传入值并且传回运行结果
- 参数的数据类型
- 不能通过一个参数返回多个行和列。 即每个行或列均需要一个参数与其对应。
- 变量名
- 所有MySQL变量都必须以@开始
- 关键字:
- 建立智能存储过程
- 只有在存储过程中包含业务规则和只能处理时,它们的威力才能真正显现出来。
- 定义分隔符
- DELIMITER
- COMMENT关键字
- 非必须, 但如果给出,会在SHOW PROCEDURE STATUS的结果中显示。
- 注释
- 使用两个-(即–)添加注释(单行注释)
- 检查存储过程
SHOW CREATE PROCEDURE
查看创建一个存储过程的语句SHOW PROCEDURE STATUS
查看存储过程的创建时间,创建人等详细信息
第24章 使用游标
24.1 游标
- 游标不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
- 只能用于存储过程
- MySQL的游标只能用于存储过程(和函数)。
24.2 使用游标
- 使用步骤:
- 在能够使用游标前,必须声明(定义)它。(该过程没有检索数据)
- 打开游标以供使用。 (该过程检索数据)
- 对于填有数据的游标, 根据需要检索各行。
- 在结束使用时,必须关闭游标。
- 创建游标
- 游标用DECLARE语句创建。
DECLARE cursor_name CURSOR FOR SELECT ...;
- 打开和关闭游标
OPEN cursor_name;
CLOSE cursor_name;
- 隐含关闭
- 如果不明确关闭游标,MySQL将会在到达END语句时自动关闭它。
- 使用游标数据
FETCH cursor_name INTO param_name1, ...;
- 使用REPEAT 循环检索数据
REPEAT ... UNTIL done END REPEAT
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
- CONTINUE HANDLER 在条件出现时被执行
- SQLSTATE ‘02000’ 表示未找到,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。
- DECLARE语句的次序
- DECLARE语句定义的局部变量必须定在在任何游标或句柄定义之前, 而句柄必须在游标之后定义。 否则将出错。
第25章 使用触发器
25.1 为什么需要使用触发器?
- 有许多事务需要在某个表发生更改时自动处理, 这时需要使用到触发器。
- 触发器是MySQL响应表更改语句(即DELETE、INSERT和UPDATE)而自动执行的一条MySQL语句(或者位于BEGIN和END之间的一组语句)
25.2 创建触发器
- 创建触发器需要的信息
- 唯一的触发器名称
- 触发器关联的表
- 触发器应该响应的活动(即DELETE、INSERT或UPDATE)
- 触发器何时执行(处理之前或之后)
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/DELETE/UPDATE ON table_name FOR EACH ROW sql_sentences;
- 保持每个数据库的触发器名称唯一
- 仅支持表
- 只有表才支持触发器, 视图不支持(临时表也不支持)
- 每个表最多支持6个触发器
- 触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。
- 单一触发器不能与多个时间或多个表关联。即,如果需要一个对INSERT和UPDATE操作执行的触发器,则应该定义两个触发器
- 触发器失败
- 如果BEFORE触发器失败,则MySQL将不执行请求的操作。
- 如果BEFORE触发器失败或语句本身失败,MySQL将不执行AFTER触发器(如果有)。
25.3 删除触发器
DROP TRIGGER trigger_name;
- 触发器不能更新或覆盖。 为了修改一个触发器,必须先删除它,然后再重新创建。
25.4 使用触发器
- INSERT触发器
- INSERT触发器须知:
- 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,通过NEW可以访问被插入的行
- 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
- 对于AUTO_INCREMENT列, NEW在INSERT执行之前包含0,在执行之后包含新的自动生成的值;
- INSERT触发器须知:
- BEFORE或AFTER?
- 通常,将BEFORE用于数据验证和净化。(目的是保证插入表中的数据确实是需要的数据,即正确的数据)
- DELETE触发器
- DELETE触发器须知:
- 在DELETE触发器代码内,可以引用一个名为OLD的虚拟表,以访问被删除的行;
- OLD中的值全都是只读的,不能更新;
- BEFORE DELETE触发器的优点
- 如果由于某种原因,BEFORE操作失败,DELETE操作也会被取消。
- 可以用BEGIN END块在触发器中执行多个SQL语句。
- DELETE触发器须知:
- UPDATE触发器
- UPDATE触发器须知
- 在UPDATE触发器代码中,可引用一个名为OLD的虚拟表访问以前的值,还可引用一个名为NEW的虚拟表访问新更新的值。
- 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更新将要用于UPDATE语句中的值)
- OLD中的值全都是只读的,不能更新
- UPDATE触发器须知
第26章 管理事务处理
26.1 事务处理
- 并非所有引擎都支持事务处理。 如MyISAM不支持明确的事务处理管理,而InnoDB支持。
- 事务处理(transcation processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。
- 相关术语
- 事务 (transaction) 指一组SQL语句
- 回退 (rollback) 指撤销指定SQL语句的过程
- 提交 (commit) 指将未存储的SQL语句结果写入数据库表
- 保留点 (savepoint) 指事务处理中设置的临时占位符,可以对它发布回退。
26.2 控制事务处理
- 使用ROLLBACK
- ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令以后)
- 哪些语句可以回退?
- 不能回退SELECT语句 (没有意义)
- 不能回退CREATE或DROP操作
- 可以回退INSERT、DELETE和UPDATE语句
- 使用COMMIT
- 一般MySQL语句都是隐含提交,但在事务处理块中,隐含提交不会进行。必须使用COMMIT来显式的提交。
- 隐含事务关闭:
- 当COMMIT或ROLLBACK语句执行后,事务会自动关闭(将来的更改会隐含提交)
- 使用保留点
- 复杂的事务处理可能需要部分提交或回退,此时需要用到SAVEPOINT
- 创建SAVEPOINT
SAVEPOINT savepoint_name;
- 使用SAVEPOINT
ROLLBACK TO savepoint_name;
- 保留点越多越好,回退越灵活。
- 释放保留点
- 保留点在事务处理完成后(执行一条ROLLBACK或COMMIT)后自动释放。
- 也可以使用RELEASE SAVEPOINT savepoint_name 明确的释放保留点