MySQL高级_触发器_视图

触发器

触发器定义：在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。

行记录变量：OLD 和 NEW
INSERT 型触发器 ：NEW 表示将要或者已经新增的数据
UPDATE 型触发器：OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器 ：OLD 表示将要或者已经删除的数据

1）创建触发器

create trigger trigger_name 
before/after insert/update/delete
on tbl_name 

[ for each row ]  -- 行级触发器

begin
	trigger_stmt ;
end;

-- 语法说明
-- trigger_name ：触发器名称
-- before或者after：之前或之后
-- insert/update/delete：选择一种语句
-- tbl_name ：表名
-- trigger_stmt ：触发器内容

举例：通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

-- 1、创建 insert 型触发器，完成插入数据时的日志记录 : 
create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
	INSERT INTO emp_logs ( id, operation, operate_time, operate_id, operate_params )
	VALUES
		(
			NULL,
			'insert',
			now( ),
			new.id,
			-- 通过new 获取新增的数据
			concat( '插入后(id:', new.id, ', name:', new.NAME, ', age:', new.age, ', salary:', new.salary, ')' ) 
		);
end;

-- 创建 update 型触发器，完成更新数据时的日志记录 : 
create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values
	( NULL,
		'update',
		now( ),
		new.id,
		-- OLD 表示修改之前的数据, NEW 表示将要或已经修改后的数据
		concat( '修改前(id:',old.id, ', name:',old.NAME,', age:',old.age,', salary:',old.salary,') , 修改后(id', new.id,'name:',new.NAME,', age:',new.age,', salary:',new.salary,')' 
		) 
	);
end;

-- 创建delete 行的触发器 , 完成删除数据时的日志记录 : 
create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values
  -- OLD 表示将要或者已经删除的数据     
	(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end;

2)删除触发器

drop trigger trigger_name

3）查看触发器

show triggers ；

视图

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

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

二：视图语法
1）创建视图

-- 语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

--选项 : 
--	WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
--	LOCAL ： 只要满足本视图的条件就可以更新。
--	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 这个是默认值.

-- 语法说明
-- CREATE：创建视图
-- CREATE OR REPLACE：创建并替换
-- view_name：视图名称
-- column_list：一个或多个字段名
-- select_statement：select语句

-- 举例（创建名为view_city_country 的视图，AS 后为普通的select 语句）
CREATE VIEW view_city_country AS 
SELECT
c.*,
t.country_name 
FROM city c
	JOIN country t ON c.country_id = t.country_id

2）更新视图（视图可以更新，但是不建议更新视图）

-- 注意：如果更新视图，更新的是基础表的数据
-- 方法一：更新视图举例（和普通sql语句一样）
update view_city_country set city_name ='西安市' where city_id = 1

--方法二：
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

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

4）删除视图

-- 语法：
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]	

-- 举例
drop view view_city_country