【开发篇】MySQL中的视图、触发器

一、MySQL中的视图

1、什么是视图

  视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
  视图主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

  视图相对于普通的表的优势主要包括以下几项。

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2、视图操作

  视图的操作包括创建或者修改视图、删除视图，以及查看视图定义。

1. 视图的创建和修改

  创建视图需要有 CREATE VIEW 的权限，并且对于查询涉及的列有 SELECT 权限。如果使用CREATE OR REPLACE 或者 ALTER 修改视图，那么还需要该视图的 DROP 权限。

-- 创建（替换）视图的语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]
	
-- 修改视图语句：
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

-- 示例：
 CREATE OR REPLACE VIEW staff_list_view AS 
	SELECT s.staff_id,s.first_name,s.last_name,a.address
	FROM staff AS s,address AS a 
	where s.address_id = a.address_id ;

 参数说明：

• OR REPLACE：可选项，若视图存在，则替换。
• ALGORITHM：可选项，表示视图选择的算法。
• VIEW：语法关键字，表明创建的东西是视图
• view_name [(column_list)]：视图名称，以及视图显示的内容，默认和后面的select语句一致。
• AS：后面跟一个完整的查询语句，将查询记录导入视图中。
• [WITH [CASCADED | LOCAL] CHECK OPTION]：可选项，表示更新视图时要保证在该视图的权限范围之内。其中LOCAL 是只要满足本视图的条件就可以更新； 而 CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新，默认是 CASCADED。

  MySQL 视图的定义有一些限制，例如，在 FROM 关键字后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。

  视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

• 包含以下关键字的 SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL。
• 常量视图。
• SELECT 中包含子查询。
• JION。
• FROM 一个不能更新的视图。
• WHERE 字句的子查询引用了 FROM 字句中的表。

关于更新参数的示例：

-- 构建视图
create or replace view view_pay as 
	select payid,payname from payment
	where mount < 10;

-- 构建LOCAL类型视图
create or replace view view_pay1 as 
	select payid,payname from view_pay 
	where mount > 5
	with local check option;

-- 构建 CASCADED视图
create or replace view view_pay2 as 
	select payid,payname from view_pay 
	where mount > 5 
	with cascaded check option;

-- 更新两个视图，观察效果
update view_pay1 set mount =10 where payid= 3; 
Query OK, 1 row affected (0.03 sec)

update view_pay2 set mount =10 where payid= 3;
ERROR 1369 (HY000): CHECK OPTION failed 'sakila.view_pay2 '

  从测试结果可以看出，view_pay1 是 WITH LOCAL CHECK OPTION 的，所以只要满足本视图的条件（mount>5）就可以更新，但是 view_pay2 是 WITH CASCADED CHECK OPTION 的，必须满足针对该视图的所有视图的条件（mount>5 and mount<10）才可以更新，因为更新后记录不再满足 view_pay 的条件，所以更新操作提示错误退出。

2. 删除视图：

  用户可以一次删除一个或者多个视图，前提是必须有该视图的 DROP 权限。

DROP VIEW [IF EXISTS] view_name [, view_name] …[RESTRICT | CASCADE]

例如，删除 staff_list 视图：
drop view staff_list;
Query OK, 0 rows affected (0.00 sec)

3. 查看视图：

  从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

  同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

  如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看。

二、触发器

  MySQL 从 5.0.2 版本开始支持触发器的功能。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

  MySQL触发器和存储过程（后面介绍）一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE。如果定义了触发器，当数据库执行这些语句的时候就会激活触发器执行相应的操作。
  触发器是一个特殊的存储过程，不同的是，执行存储过程要使用call语句来调用，而触发器的执行不需要用call来调用，也不需要手工启动，只要当一个预定义的事件发生，触发器就会被MySQL自动调用。触发器可以查询其他表，而且可以包含复杂的SQL语句。

1. 创建触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
参数说明：

TRIGGER：触发器标志
trigger_name：触发器名称
trigger_time：触发器的出发时间。可以是BEFORE或者AFTER，BEFORE是指在检查约束前触发，而 AFTER是在检查约束后触发。
trigger_event：触发器的触发事件。可以是 INSERT、UPDATE 或者 DELETE。
for each row ：是指每行受影响，触发器都执行，叫行级触发器。oracle触发器中分行级触发器和语句级触发器，可不写for each row，无论影响多少行都只执行一次。mysql不支持语句触发器，所以必须写for each row。
trigger_stmt：触发器执行语句，可以是单条，也可以是多条。

示例：

-- 创建 BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 触发器：

-- 创建一个接收触发器的表
create table tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));
 
 DELIMITER //
 CREATE TRIGGER ins_film_bef before INSERT ON film FOR EACH ROW 
	BEGIN
	INSERT INTO tri_demo (note) VALUES ('before insert');
	END
	//

create trigger ins_film_after after insert on film for each row
	begin
	INSERT INTO tri_demo (note) VALUES ('after insert');
	END
	//

CREATE TRIGGER upd_film_bef BEFORE update ON film FOR EACH ROW 
	BEGIN
	INSERT INTO tri_demo (note) VALUES ('before update');
	END
	//

CREATE TRIGGER upd_film_after after update ON film FOR EACH ROW 
	BEGIN
	INSERT INTO tri_demo (note) VALUES ('after update');
	END
	//
delimiter ;

-- 插入一条已经存在的记录
INSERT INTO film VALUES 
(1001,'Only test','Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',
'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42') 
ON DUPLICATE KEY 
UPDATE title='update record';

-- 查看触发器结果
select * from tri_demo;
+----+---------------+
| id | note |
+----+---------------+
| 1 | before insert |
| 2 | before update |
| 3 | after update |
+----+---------------+
3 rows in set (0.00 sec)

-- 插入一条新数据
INSERT INTO film VALUES 
(1002,'Only test','Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',
'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42') 
ON DUPLICATE KEY 
UPDATE title='update record';

-- 查看结果
select * from tri_demo;
+----+---------------+
| id | note |
+----+---------------+
| 4 | before insert |
| 5 | after insert |
+----+---------------+
2 rows in set (0.00 sec)

  从上面的例子可以知道，对于有重复记录，需要进行 UPDATE 操作的 INSERT，触发器触发的顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE；对于没有重复记录的 INSERT，就是简单的执行 INSERT 操作，触发器触发的顺序是 BEFORE INSERT、AFTER INSERT。对于那些实际执行 UPDATE 操作的记录，仍然会执行 BEFORE INSERT 触发器的内容，在设计触发器的时候一定要考虑这种情况，避免错误地触发了触发器。

  另外，关于ON DUPLICATE KEY ，这个东西是MySQL特有的，语句的作用，当insert已经存在的记录时，执行Update。

  
注意：

• 触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。
• 当涉及触发器的表删除时，触发器也同时删除。
• 触发器不能修改和更新，只能删除重建。

2. 删除触发器

  一次可以删除一个触发程序，如果没有指定 schema_name (数据库名称)，默认为当前数据库,具体语法如下：

DROP TRIGGER [schema_name] trigger_name;
例如：删除触发器before_trigger
drop trigger before_trigger;

3. 查看触发器

  可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用起来不是很方便，具体语法如下：

SHOW TRIGGERS;

  另外一个查看方式是查询系统表的 information_schema.triggers 表，这个方式可以查询指定触发器的指定信息，操作起来明显方便很多：

select * from triggers where trigger_name = ‘ins_film_bef’;

4. 触发器的注意事项

  触发器执行的语句有以下两个限制。

• 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用 CALL 语句的动态 SQL语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
• 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、COMMIT 或ROLLBACK。

  MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。