MySQL学习（六）——视图和触发器

1. 视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

1.1 视图语法

• 创建

  CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
  1

• 查询

  查看创建视图语句：SHOW CREATE VIEW 视图名称;
  
  查看视图数据：SELECT * FROM 视图名称 ...... ;
  1
  2
  3

• 修改

  方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
  
  方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
  1
  2
  3

• 删除

  DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
  1

代码演示：

-- 创建视图
create or replace view user_v_1 as select id,name,phone,age from tb_user where age <= 30;
-- 查询视图
show create view user_v_1;
select * from user_v_1;
select * from user_v_1 where age<27;
-- 修改视图
create or replace view user_v_1 as select id,name,phone from tb_user where age <= 40;
alter view user_v_1 as select id,name,phone from tb_user where id <= 40;
-- 删除视图
drop view if exists user_v_1;
1
2
3
4
5
6
7
8
9
10
11

创建了上述视图后，我们向视图中插入数据，如下：

insert into user_v_1 values(25,'莱希奥', '12345678910', 29);
insert into user_v_1 values(26,'坤小满', '12345678911', 31);
1
2

然后我们再执行查询语句，查询视图，可以发现只有 id 为25的数据在视图中，但是，这两条数据都已经插入到了表格中。

那么，如果我们定义视图时，如果指定了条件，然后我们在插入、修改、删除数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？ 答案是可以的，这就需要借助于视图的检查选项了。

1.2 检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。

1. CASCADED

   CASCADED 是级联。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

   

2. LOCAL

   LOCAL 是本地。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

   

1.3 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

代码演示：

create view stu_v_count as select count(*) from student;
1

上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入的，将会报错。

1.4 视图的作用

1. 简单

   视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2. 安全

   数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3. 数据独立

   视图可帮助用户屏蔽真实表结构变化带来的影响。

2. 触发器

2.1 介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

2.2 语法介绍

• 创建

  CREATE TRIGGER trigger_name
  BEFORE/AFTER INSERT/UPDATE/DELETE
  ON tbl_name  FOR EACH ROW -- 行级触发器
  BEGIN
  	trigger_stmt ;
  END;
  1
  2
  3
  4
  5
  6

• 查看

  SHOW TRIGGERS ;
  1

• 删除

  DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 
  1

2.3 触发器示例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到定义的日志表user_logs中, 包含增加，修改，删除 ;

表结构准备:

-- 准备工作 : 日志表 user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;
1
2
3
4
5
6
7
8
9

2.3.1 插入数据触发器

create trigger tb_user_insert_trigger
	after insert on tb_user 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, ', phone=', NEW.phone, ', email=', NEW.email, ',
    profession=', NEW.profession));
end;
1
2
3
4
5
6
7
8
9

测试如下：

-- 查看
show triggers ;
-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES 
(26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());
1
2
3
4
5
6

测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

2.3.2 修改数据触发器

create trigger tb_user_update_trigger
after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES
    (null, 'update', now(), new.id,
    concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession, ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;
1
2
3
4
5
6
7
8

测试如下：

-- 查看
show triggers ;

-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;
1
2
3
4
5
6

2.3.3 删除数据触发器

create trigger tb_user_delete_trigger
after delete on tb_user 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, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;
1
2
3
4
5
6
7

测试如下：

-- 查看
show triggers ;

-- 删除数据
delete from tb_user where id = 26;
1
2
3
4
5