MySql中的视图和触发器

MySql中的视图

1、什么是视图
视图就是一条SELECT语句执行后返回的结果集,
视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表。
2、视图作用
（1）简化用户的操作
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，
简化操作；

（2）对机密数据提供保护作用
不希望用户访问表中某些含敏感信息的列，比如salary…
3、视图的特性
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；
4、操作视图
（1）创建视图:

Create view  视图名称 as DQL语句(select ...);

（2）删除视图:

Drop view 视图名称

练习：

创建一个员工基本信息的视图。
– 查看表格中的所有数据

select * from t_employee;

– 查看表格中员工的基本信息。

select name,gender,age,home,marry,hobby from t_employee;

– 创建视图。

create view v_employee as select name,gender,age,home,marry,hobby from t_employee;

– 查看视图中的内容

select * from v_employee;

练习2：将员工所有的信息做成一个视图。
– 查询出所有员工信息

select * from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;

– 去掉 重复的id

select t1.*,t2.pos,t2.experence from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;

– 创建视图

create view v_employee_detail as select t1.*,t2.pos,t2.experence from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;

– 显示所有数据

select * from v_employee_detail;
select name,gender,age from v_employee_detail;

5、更新视图
在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。
更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。
注意: 更新视图的时候对应 的真实表上的数据也发生改变 了

不可更新的视图：
某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：
• 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
• DISTINCT
• GROUP BY
• HAVING
• UNION
• 位于选择列表中的子查询
• Join
• FROM子句中的不可更新视图
• WHERE子句中的子查询，引用FROM子句中的表。

注意

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

触发器trigger

1、什么是触发器
触发器（trigger）：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
简单来说就设置一个事件和触发条件 当对表的某种操作触发了这个条件的时候就执行这个事件。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

2、触发器基本语法
创建触发器：

Create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt;

trigger_time ：触发器的触发时间，可以为before（在检查约束前触发）
或after（在检查约束后触发）；

trigger_event：是触发器的触发事件，包括insert、update和delete；

tb_name： 表示建立触发器的表名，就是在哪张表上建立触发器

trigger_stmt：

触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句；

FOR EACH ROW：

表示任何一条记录上的操作满足触发事件都会触发该触发器。

删除触发器：
Drop trigger 触发器名称

案例:
有两张表格分别为 商品表 和订单表
– 创建商品表

create table g(
	id int primary key auto_increment,
	name varchar(20),
	num int
);

– 创建订单表

create table o(
	oid int primary key auto_increment,
	gid int,
	much int
);

– 插入商品

insert into g(name,num) values('苹果',10),('香蕉',10),('桔子',10);

练习1: 卖了3个苹果，请更新商品表和订单表中的数据。
– 1.往订单表插入一条记录

-- insert into o(gid,much) values(1,3);

– 2.更新商品表商品1的剩余数量

-- update g set num=num-3 where id=1;

练习2：使用触发器来完成练习1的效果。
– 创建触发器
– 修改结束符

delimiter $ 

create TRIGGER tg1 after insert on o for each row 
begin 
update g set num = num-3 where id = 1;
end $

delimiter ;

insert into o(gid,much) VALUES(1,3);

注: 会发现苹果的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

产生的问题:
不管我们买哪个商品，最终更新的都是商品1的数量。

insert INTO o(gid,much) VALUES(2,2);

new 和old 的使用

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据

扩展: before 和after 的区别：
after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；也就是说先插入订单记录，再更新商品的数量；
before是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；