MySQL学习笔记-07(视图、触发器)

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;