MySQL学习笔记-07(视图、触发器)
视图
视图介绍
概念
-
视图(View)是一种虚拟存在的表,视图中的数据并不在数据库中实际存在,来自定义视图查询中使用的基本表,并且是在使用视图时动态创建;
-
视图只保存了查询SQL逻辑,不保存查询结果。在创建视图时,主要的工作是在创建这条SQL查询语句上;
-
通过视图对数据进行修改,会被更新定义视图查询中使用的基本表中;
-
定义视图查询中使用的基本表中的数据发生变化,会自动反应到视图的查询结果中。
作用
-
简化操作:视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,使得用户不必为以后的操作每次指定全部的条件;
-
数据安全性: 数据库无法控制数据库特定行和特定的列的权限,通过视图用户只能查询和修改他们所能见到的数据,从而提供更高的数据安全性和隐私保护;
-
数据抽象和封装:可以将多个表的数据抽象为一个虚拟表,简化数据模型和应用程序开发;
-
数据独立:视图可帮助用户屏蔽真实表结构变化带来的影响;
-
性能优化:视图可以预先计算和存储结果集,以提高查询性能,并避免重复执行复杂查询。
语法
- 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT ... [WITH [CASCADED | LOCAL] CHECK OPTION]
- 查看视图
# 查看创建视图语句
SHOW CREATE VIEW 视图名称;
#查看视图数据:
SELECT * FROM 视图名称 ...;
- 修改视图
#方式-
CREATE [OR REPLACE]VIEW 视图名称 AS SELECT ... [WITH [CASCADED | LOCAL] CHECK OPTION]
#方式二
ALTER VIEW 视图名称 AS SELECT语句 ... [WITH [CASCADED | LOCAL] CHECK OPTION]
- 删除视图
DROP VIEW [IF EXISTS] 视图名称[,视图名称] ...
视图的检查选项
-
当使用"WITH CHECK OPTION"创建视图时,MySQL会通过视图检查正在更改的每个行,例如 插入,更新,删除,以使其符合视图的定义。MYSQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项CASCADED和LOCAL,默认值为CASCADED。
-
CASCADED:例如有两个视图v1与v2,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为CASCADED,v1视图创建时未指定检查选项。 在执行检查时,不仅会检查v2,还会级联检查v2的关联视图v1。
- LOCAL:例如有两个视图v1与v2,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为 local ,但是v1视图建时未指定检查选项。 则在执行检查时,只会检查v2,不会检查v2的关联视图v1。
触发器
触发器介绍
-
在操作(insert/update/delete)与表有关的数据库对象之前或之后,触发并执行触发器中定义的SOL语句集合;
-
触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作;
-
OLD 和 NEW 来引用触发器中发生变化的记录内容;
-
现在触发器还只支持行级触发,不支持语句级触发。
触发器类型
触发器类型 | NEW 和 OLD |
---|---|
INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
UPDATE 型触发器 | OLD 表示修改之前的数据,NEW 表示将要或已经修改后的数据 |
DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
INSERT型触发器示例
# 创建日志表
create table user_logs(
id int(11) not null auto_increment primary key,
operation varchar(20) not null comment '操作类型',
operate_time datetime not null comment '操作时间',
operate_id int(11)not null comment '操作的ID',
operate_params varchar(500)comment '操作参数'
) engine=innodb default charset=utf8;
# 创建插入数据触发器
create trigger emp_insert_trigger after insert on emp for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null, 'insert', now(), new.id, concat('插入数据:id=', new.id, ',name=',new.name));
end;
insert into emp values (1, '老大', 23, 1,'18960406002');
# 查询触发器
show triggers;
# 删除触发器
DROP trigger emp_insert_trigger;
# 查询日志表
select * from user_logs;
UPDATE型触发器示例
# 创建删除数据触发器
create trigger emp_update_trigger after update on emp for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null, 'insert', now(), new.id, concat('更新前数据:id=', old.id, ',name=',old.name,
'; 更新后的数据:id=', new.id, ',name=',new.name));
end;
# 查询触发器
show triggers;
# 更新
update emp set name = '老二' where id=1;
# 查询日志表
select * from user_logs;
DELETE型触发器示例
# 创建数据触发器
create trigger emp_delete_trigger after delete on emp for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null, 'delete', now(), old.id, concat('删除的数据:id=', old.id, ',name=',old.name));
end;
# 查询触发器
show triggers;
# 更新
delete from emp where id=1;
# 查询日志表
select * from user_logs;