MySQL 数据库的结构操作之视图与触发器

[TOC]

## 1. 基础设置
创建校园管理相关的一系列表,创建语句请[点击这里查看](/blog/info-md.html/35A410F82B2B5445?type=195)

## 2. 视图
视图（view）：是一种有结构但没有结果的虚拟表，即有行有列，但是没有真实存储数据的表，它的结构来源不是自己定义的，而是从对应的基表中产生，这里的基表指的是视图的数据来源

> 语句中的 view 后面的 as 必不可少

__视图的意义：__

+ 视图可以节省SQL语句，将一条复杂的查询语句使用视图进行保存，之后只要直接对视图进行查询即可
+ 数据相对安全，视图操作主要是查询，如果对视图结构进行删除，不会影响基表数据
+ 视图一般在大项目中使用，而且是多系统使用，可以对外提供有用的数据，并隐藏关键数据，对数据安全有保护作用
+ 视图可以对外提供友好性，不同的视图提供不同的数据，对外有专门的设计
+ 视图可以更容易的进行权限控制

### 2.1 创建视图
视图创建之后，系统会在视图对应的数据库文件夹下创建一个对应的结构文件 frm 文件

__语法：__create view 视图名字 as select 语句,这里的 select 语句可以是普通查询，也可以是连接查询、联合查询、子查询

替换同名的视图，如果视图已经存在，再创建可能会报错，这是就可以执行替换的语句，只要再创建的视图的语句中create后面加入[or replace]

__语法：__create or replace view 视图名字 as select 语句,这里的 select 语句可以是普通查询，也可以是连接查询、联合查询、子查询

#### 2.1.1 创建单表视图
基表只有一个

```SQL
-- 创建一个以my_student为基表的视图
create view my_student_view as select * from my_student;
```

单表视图select的行数与基表行数一致，至于列则和select 基表的列表有关，示例使用的是 * 符号，表示视图拥有和基表一样的列，如果有指定具体的列，那么视图输出的结果就是指定的列

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |
|  4 | 1990-11-25 | 关羽   | 男     |   1.85 |   52 | 苗     |
|  5 | 1992-09-08 | 张飞   | 男     |   1.82 |   50 | 汉     |
|  6 | 1992-06-12 | 马超   | 男     |   1.77 |   46 | 苗     |
|  7 | 1993-10-22 | 甄宓   | 女     |   1.62 |   38 | 汉     |
|  8 | 1991-11-12 | 小乔   | 女     |   1.56 |   26 | 苗     |
|  9 | 1990-01-25 | 大乔   | 女     |   1.65 |   27 | 汉     |
| 10 | 1988-09-08 | 蔡文姬 | 女     |   1.82 |   30 | 苗     |

#### 2.1.2 创建多表视图
基表来源至少两个

```SQL
-- 创建一个有三个基表的视图
create view my_studentClass_view as
select s.name,c.name className,s.gender from my_student s
left join my_studentclasssrelation r on s.id = r.student
left join my_classs c on c.id = r.classs;
```

多表视图查询的数据与基表关联查询相关，它是以基表查询的结果为结果

| name   | className | gender |
|--------|-----------|--------|
| 刘禅   | 3班       | 男     |
| 关羽   | 1班       | 男     |
| 张飞   | 2班       | 男     |
| 马超   | 3班       | 男     |
| 甄宓   | NULL      | 女     |
| 小乔   | NULL      | 女     |
| 大乔   | NULL      | 女     |
| 蔡文姬 | NULL      | 女     |

#### 2.1.3 创建重名的视图
如果要创建的视图已经存在了，可以使用 `create or replace` 的方式重新创建，它是将原来的视图删除后，再创建新的视图

```SQL
-- 创建一个以my_student为基表的视图
create or replace view my_student_view as select name,gender,height from my_student;
```

| Field  | Type | Null | Key | Default | Extra |
|----|-----|--------|--------|--------|------|
|name|varchar(20)|NO||NULL||
|gender|varchar(2)|YES||NULL||
|height|decimal(18,2)|YES||NULL|&nbsp;|

> __《附加说明》：__创建多表视图时，注意如果作为基表的列有相同的情况，一定要使用别名来区分重名列，例如示例中的 name 列，因为基表中都有 name 这个字段，所以第二个 name 字段用了表名，否则创建就会报ERROR 1060 (42S21): Duplicate column name 'name'的错误

### 2.2 查看视图结构
视图是一张虚拟表，因此表的所有查看方式都适用于视图

__显示表列表中的视图：__

```SQL
show tables like '%_view' ;
```

| Tables_in_study (%\_view) |
|--------------------------|
| my_student_view          |
| my_studentclass_view     |

__显示视图结构：__

```SQL
desc my_student_view;
```

| Field    | Type          | Null | Key | Default | Extra |
|----------|---------------|------|-----|---------|-------|
| id       | int           | NO   |     | 0       |       |
| birthday | date          | YES  |     | NULL    |       |
| name     | varchar(20)   | NO   |     | NULL    |       |
| gender   | varchar(2)    | YES  |     | NULL    |       |
| height   | decimal(18,2) | YES  |     | NULL    |       |
| age      | int           | YES  |     | NULL    |       |
| nation   | varchar(50)   | YES  |     | NULL    |&nbsp; |

__显示视图的创建的SQL语句：__

```SQL
-- 查询视图创建语句时，可以使用table关键字，也可以使用view关键字，这是与表的区别
-- 下面的两条语句查询到SQL语句都是一样的
show create table my_studentclass_view \G
show create view my_studentclass_view \G
-- 创建my_studentclass_view的语句
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `my_studentclass_view` AS select `s`.`name` AS `name`,`c`.`name` AS `className`,`s`.`gender` AS `gender` from ((`my_student` `s` left join `my_studentclasssrelation` `r` on((`s`.`id` = `r`.`student`))) left join `my_classs` `c` on((`c`.`id` = `r`.`classs`)))
character_set_client: gbk
collation_connection: gbk_chinese_ci
```

### 2.3 修改视图
视图本身不可修改，但是视图的来源语句可以修改，因此修改视图就是修改，创建视图时查询基表的 SELECT 语句

__语法：__alter view 视图名字 as 新的 select 语句

```SQL
-- 给my_studentClass_view视图添加新的字段height
alter view my_studentClass_view as
select s.name,c.name className,s.gender,s.height from my_student s
left join my_studentclasssrelation r on s.id = r.student
left join my_classs c on c.id = r.classs;
```

获取修改后的视图结构 `desc my_studentClass_view`

| Field     | Type          | Null | Key | Default | Extra |备注|
|----------|---------------|------|-----|---------|-------|--|
| name      | varchar(20)   | NO   |     | NULL    |       ||
| className | varchar(50)   | YES  |     | NULL    |       ||
| gender    | varchar(2)    | YES  |     | NULL    |       ||
| **`height`**| decimal(18,2) | YES  |     | NULL    |&nbsp;|修改后新增的列|

### 2.4 删除视图

__语法：__drop view 视图名字，删除视图，如果视图不存在，执行删除会报错，因此可以判断一下，drop view if exists 视图名

```SQL
-- 删除 my_student_view 视图
drop view my_student_view;
-- 输出：Query OK, 0 rows affected (0.19 sec)
-- 添加判断再删除视图
-- drop view if exists my_student_view
```

### 2.5 视图新增数据
数据新增是直接对视图进行数据新增，视图是可以进行数据写操作，但写数据有很多的限制

__限制说明：__

+ 多表视图不能新增数据，创建的视图包括 union 或者 join 的关联表，无法使用 insert 和 update

```SQL
-- 给 my_studentClass_view 视图插入数据
insert into my_studentClass_view values('黄忠','2班','男',1.72);
-- 报错了输出错误提示：ERROR 1471 (HY000): The target table my_studentClass_view of the INSERT is not insertable-into
```
***

+ 可以向单表视图插入数据，但视图中包含的字段必须有，基表中所有不能为空或没有默认值的字段

```SQL
-- 创建一个视图，里面缺少基表的必填字段name
create view my_student_view as select birthday,gender,height,age,nation from my_student;
-- 创建完后插入数据
insert into my_student_view (birthday,gender,height,age,nation) values('1989-10-22','男',1.72,58,'汉');
-- 报错了输出错误提示：ERROR 1364 (HY000): Field 'name' doesn't have a default value
```
***

+ 创建的视图中如果有where语句限制，而又没有对where的语句加入编辑限制的话，那么视图引用的基表还是会更新数据，只是视图不会显示出来，这样可能会导致数据不规范，输入要对where语句类的视图加限制，需要在创建视图的语句后面加上with check option

```SQL
-- 重新创建my_student_view视图
create view my_student_view as select * from my_student where name = '刘禅';
-- 创建完后插入数据
insert into my_student_view (birthday,name,gender,height,age,nation) values('1978-11-12','张皇后','男',1.82,68,'汉');
```

查询插入数据后视图【my_student_view】的数据

| id | birthday   | name | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  3 | 2021-11-12 | 刘禅 | 男     |   1.82 |   16 | 汉     |

查询插入数据后基表【my_student】的数据

加入 with check option 限制条件

```SQL
drop view my_student_view;
-- 重新创建my_student_view视图，并加入with check option条件
create view my_student_view as select * from my_student where name = '刘禅' with check option;
-- 创建完后插入数据
insert into my_student_view (birthday,name,gender,height,age,nation) values('1978-11-12','诸葛亮','男',1.82,68,'汉');
-- 报错了输出错误提示：ERROR 1369 (HY000): CHECK OPTION failed 'study.my_student_view'
```

加了 with check option 限制后，就不能往基表`插入和更新`数据了
***

__成功的单表视图插入数据：__

```SQL
-- 重新创建my_student_view视图
create view my_student_view as select * from my_student;
-- 创建完后插入数据
insert into my_student_view (birthday,name,gender,height,age,nation) values('1978-11-12','黄忠','男',1.82,68,'汉');
```
查询插入数据后视图的数据

### 2.5 视图修改数据
单表视图可更新数据操作，多表视图不可以进行视图更新数据操作

__多表视图更新__

```SQL
update my_studentClass_view set className = '3班' where name = '甄宓';
-- 报错提示：ERROR 1288 (HY000): The target table my_studentClass_view of the UPDATE is not updatable
```

__单表视图更新__

```SQL
-- 修改id=13的数据，原数据birthday=1978-11-12
update my_student_view set birthday = '1989-11-12' where id = 13;
```

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
| 13 | `1989-11-12` | 张皇后 | 男     |   1.82 |   68 | 汉     |

### 2.6 视图删除数据
多表视图不能删除数据，单表视图可以删除

__多表视图删除__

```SQL
delete from my_studentClass_view where name = '刘禅';
-- 报错输出：ERROR 1288 (HY000): The target table my_studentClass_view of the DELETE is not updatable
```

__单表视图删除__

```SQL
delete from my_student_view where id = 3;
-- 删除成功
-- 输出：Query OK, 1 row affected (0.14 sec)
```

### 2.7 视图算法
系统对视图以及外部查询视图的 select 语句的一种解析方式，有三种视图算法

__三种视图算法__

+ undefined：未定义（默认的），这不是一种实际使用算法，是一种推卸责任的算法，本身没有定义算法，系统自行处理
+ temptable：临时表算法，系统应该先执行视图的 select 语句，后执行外部查询语句
+ merge：合并算法，系统应该先将视图对应的 select 语句与外部查询视图的 select 语句进行合并，然后执行，这种算法效率高，因为只执行一次查询，undefined时系统经常选择的就是merge算法

__算法指定：__

+ 查看当前视图算法

```SQL
-- 使用 show create view  my_student_view\G 语句查看创建视图的SQL，其中ALGORITHM=UNDEFINED就是当前视图采用的算法
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `my_student_view` AS select `my_student`.`id` AS `id`,`my_student`.`birthday` AS `birthday`,`my_student`.`name` AS `name`,`my_student`.`gender` AS `gender`,`my_student`.`height` AS `height`,`my_student`.`age` AS `age`,`my_student`.`nation` AS `nation` from `my_student`
character_set_client: gbk
collation_connection: gbk_chinese_ci
```
***

+ 创建时指定视图算法

__语法：__create ALGORITHM = 指定算法 view 视图名字 as select 语句;

```SQL
-- 指定视图为 temptable：临时表算法
create or replace algorithm = temptable view  my_studentClass_view as
select s.name,c.name className,s.gender from my_student s
left join my_studentclasssrelation r on s.id = r.student
left join my_classs c on c.id = r.classs;
```

创建完后使用`show create view  my_student_view\G`语句查看视图算法

```SQL
CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `my_studentclass_view` AS select `s`.`name` AS `name`,`c`.`name` AS `className`,`s`.`gender` AS `gender` from ((`my_student` `s` left join `my_studentclasssrelation` `r` on((`s`.`id` = `r`.`student`))) left join `my_classs` `c` on((`c`.`id` = `r`.`classs`)))
character_set_client: gbk
collation_connection: gbk_chinese_ci
```
***

+ 视图算法选择：如果视图的 select 语句中会包含一个查询子句，而且很有可能顺序比外部的查询语句要靠后，一定要使用临时表算法 temptable 来提升视图 select 子句的查询顺序，也就是说需要视图先出结果，在根据视图结果进行查询的情况，一定要使用临时表算法，其它情况可以不用指定算法，默认即可

## 3. 触发器
触发器：事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候，如增删改，系统会自动触发代码执行

触发器存放：所有触发器都会保存在一张系统表中【Information_schema.triggers】，可以使用 select 查询

```SQL
select * from Information_schema.triggers
```

触发器要素：事件类型、触发时间、触发对象

+ 事件类型：增删改，insert、update、delete
 + 触发时间：前后、before 和 after
 + 触发对象：表中的每一行记录

> 一张表中同触发时间的同一种事件类型的触发器只能有一个，一张表最多能有6个触发器

### 3.1. 创建触发器

关键字：`delimiter`：临时修改语句结束符，这里需要注意的是如果当前触发器需要使用自定义结束符，定义完后，触发器最后记得修改回 `;` 分号，`delimiter` 与符号之间有空格

__语法：__
delimiter //
create trigger 触发器名字 触发时间 时间类型 on 触发对象 for each row
begin
insert | update | delete 语句;
end
//
delimiter ;

__语法解释：__`delimiter` 是自定义结束符号，后面代码中只有碰到自定义符号才算结束，这里使用的是 `//` 作为结束符，而不是;分号了，这里的触发对象通常指的就是表，begin类似编程语言的左大括号，end类似编程语言的右大括号，这样包裹着触发器内容，每行内容都必须使用语句结束符

```SQL
delimiter //
create trigger bak_student before delete on my_student for each row
begin
insert into my_student_bak values(old.name,old.birthday,old.age,old.gender,old.height,old.nation);
end
//
delimiter ;
-- 当要删除表my_student数据前回先将要删除的数据添加到my_student_bak中,my_student_bak表是my_student操作的备份表
```

关于这里的old使用查看下面 [3.4. 触发器记录](#h3-3-4-) 章节

### 3.2. 查看触发器
#### 3.2.1 查看触发器信息
查看触发器信息分为查看所有触发器和模糊匹配查询触发器

__语法：__show triggers; 或 show triggers like '%%';

```SQL
show triggers\G
```

查询结果：

```SQL
             Trigger: tri_studentClasssRelation
               Event: INSERT
               Table: my_studentclasssrelation
           Statement: begin
  IF exists (select id from my_studentClasssRelation where student = new.student and classs = new.classs) then
  insert into xxx values (xxx);
  END IF;
end
              Timing: BEFORE
             Created: 2023-05-27 11:41:07.95
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
```

#### 3.2.2 查看触发器创建语句

__语法：__show create trigger 触发器名;

```SQL
show create trigger bak_student\G
```

查看结果：

```SQL
               Trigger: tri_studentClasssRelation
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_studentClasssRelation` BEFORE INSERT ON `my_studentclasssrelation` FOR EACH ROW begin
  IF exists (select id from my_studentClasssRelation where student = new.student and classs = new.classs) then
  insert into xxx values (xxx);
  END IF;
end
  character_set_client: gbk
  collation_connection: gbk_chinese_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2023-05-27 11:41:07.95
```

### 3.3. 删除触发器
触发器不能修改，只能先删除，后新增

__语法：__drop trigger 触发器名字;

```SQL
drop trigger bak_student;
```

### 3.4. 触发器记录
触发器记录：不管触发器是否触发了，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和`即将执行`之后新的状态分别保留下来，供触发器使用，其中，要操作的当前状态保存到 `old` 中，操作之后的可能形态保存给 `new`，old 代表旧记录，new 代表新记录，需要注意的是，删除的时候没有 new 的记录，插入的时候没有 old 的记录，old 和 new 都是代表记录本身，任何一条记录除了有数据，还有字段名字

使用方式：old.字段、new.字段，上面的示例中已经使用old了

> new 代表的是假设发生之后的结果，触发器可能还未执行

```SQL
-- 查看 ACTION_REFERENCE_OLD_ROW : OLD 和 ACTION_REFERENCE_NEW_ROW : NEW
select * from Information_schema.triggers
```

### 3.5. 终止触发器执行
触发器没有提供一个能阻止事件发生的能力，当需要终止`触发内容`和`触发事件`继续执行时，需要采用`暴力报错`的方式进行终止,`触发内容`指的是触发器被触发后执行的变更内容，`触发事件`指的是触发触发器之前执行的变更内容，暴力终止后关于触发器的所有操作都无法生效

示例步骤一：创建一个触发器，给学生进行分班时，如果此学生已经在此班中时，使用触发器终止此次操作
```SQL
delimiter //
create trigger tri_studentClasssRelation before insert on my_studentClasssRelation for each row
begin
  IF exists (select id from my_studentClasssRelation where student = new.student and classs = new.classs) then
  insert into xxx values (xxx);  -- 数据库中并不存在xxx表，这里就是人为的暴力报错
  END IF;
end
//
delimiter ;
--创建成功输出结果：Query OK, 0 rows affected
```

示例步骤二：插入一条已存在的已在班级的学生信息

```SQL
insert into my_studentClasssRelation (student,classs,level) values (2,2,7);
-- 输出：执行了暴力报错语句，ERROR 1146 (42S02): Table 'study.xxx' doesn't exist
select * from my_studentClasssRelation;
-- 输出的结果和插入时一样，说明触发前后都没有执行成功
```