「玩《三国志》学MySQL」7.视图触发器

女爷i 2022-11-07 13:30 303阅读 0赞

**「玩《三国志》学MySQL」** 内容汇总

[「玩《三国志》学MySQL」带你玩游戏学MySQL][MySQL_MySQL]

### 文章目录 ###

*  内容简介
 *  视图
 *   *  创建视图
     *  查看视图的定义
     *  视图的作用
     *  删除视图
     *  检查视图
 *  触发器
 *   *  准备工作
     *  使用触发器实现检查约束
     *  使用触发器维护冗余数据
     *  使用触发器模拟外键级联选项
     *  查看触发器的定义
     *  删除触发器
 *  临时表
 *   *  临时表的创建、查看与删除
 *  派生表（Derived Table）
 *  子查询、视图、临时表、派生表

# 内容简介 #

这部分做为数据分析师，了解即可，一般情况下用不到。

视图以及触发器的管理及使用，然后结合《三国志》分别介绍视图以及触发器在该系统中的应用。

视图与表有很多相似的地方，视图也是由若干个字段以及若干条记录构成，视图也可以作为select语句的数据源。甚至在某些特定条件下，可以通过视图对表进行更新操作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwMjg4MzI3_size_16_color_FFFFFF_t_70]

# 视图 #

视图中保存的仅仅是一条select语句，视图中的源数据都来自于数据库表，数据库表称为基本表或者基表，视图称为虚表。

## 创建视图 ##

Createview 视图名[(视图字段列表)]
    as
    Select 语句

CREATE VIEW view_name AS
    Select san_wujiang.姓名 from san_wujiang where san_wujiang.姓名 like '%曹%'

## 查看视图的定义 ##

可以使用下面四种方法查看视图的定义。

*  在某数据库中成功地创建了视图available\_course\_view后，该视图的定义默认保存在数据库目录（例如某目录）下，文件名为available\_course\_view.frm。使用记事本打开该文件，即可查看该视图的定义。
 *  视图是一个虚表，也可以使用查看表结构的方式查看视图的定义。
 *  不仅显示当前数据库中所有的基表，也会将所有的视图罗列出来。

showtables;

*  MySQL系统数据库information\_schema的views表存储了所有视图的定义，使用下面的select语句查询该表的所有记录，也可以查看所有视图的详细信息。

select * from in formation_schema.views

## 视图的作用 ##

1.  使操作变得简单
2.  避免数据冗余
3.  增强数据安全性
4.  提高数据的逻辑独立性

## 删除视图 ##

dropview 视图名

## 检查视图 ##

视图分为普通视图与检查视图。

通过检查视图更新基表数据时，只有满足检查条件的更新语句才能成功执行。

Createview 视图名[(视图字段列表)]
    AS 
    Select 语句
    With check option

Create view view_name AS 
    Select san_wujiang.姓名 from san_wujiang where san_wujiang.姓名 like '%曹%'
    With check option

# 触发器 #

触发器主要用于监视某个表的insert、update以及delete等更新操作，这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行，从而实现数据的自动维护。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwMjg4MzI3_size_16_color_FFFFFF_t_70 1]

## 准备工作 ##

使用create trigger语句可以创建一个触发器。

create trigger 触发器名 触发时间 触发事件 on 表名 for each row
    begin
    触发程序
    end

**MySQL的触发事件有三种**

*  **insert**：将新记录插入表时激活触发程序，例如通过insert、load data和replace语句，可以激活触发程序运行。  
    MySQL的触发事件有三种：
 *  **update**：更改某一行记录时激活触发程序，例如通过update语句，可以激活触发程序运行。
 *  **delete**：从表中删除某一行记录时激活触发程序，例如通过delete和replace语句，可以激活触发程序运行。

**触发器的触发时间有两种**

*  **before** 表示在触发事件发生之前执行触发程序。
 *  **after** 表示在触发事件发生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置六种类型的触发器。

## 使用触发器实现检查约束 ##

MySQL可以使用复合数据类型set或者enum对字段的取值范围进行检查约束，使用复合数据类型可以实现离散的字符串数据的检查约束，对于数值型的数不建议使用set或者enum实现检查约束，可以使用触发器实现。

## 使用触发器维护冗余数据 ##

冗余的数据需要额外的维护，维护冗余数据时，为了避免数据不一致问题的发生（例如：剩余的武将人数+已选武将人数≠该势力人数上限），冗余的数据应该尽量避免交由人工维护，建议冗余的数据交由应用系统（例如触发器）自动维护。

## 使用触发器模拟外键级联选项 ##

对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、set null或者no action（restrict），外键约束关系可以交由InnoDB存储引擎自动维护。

## 查看触发器的定义 ##

*  使用show triggers命令查看触发器的定义
 *  查询information\_schema数据库中的triggers表，可以查看触发器的定义  
    MySQL中所有触发器的定义都存放在information\_schema数据库下的triggers表中，查询triggers表，可以查看所有数据库中所有触发器的详细信息，查询语句如下：

select * from information_schema.triggers

*  使用“show create trigger”命令可以查看某一个触发器的定义。  
    例如使用“show create trigger organization\_delete\_before\_trigger”命令可以查看触发器organization\_delete\_before\_trigger的定义。
 *  成功创建触发器后，MySQL自动在数据库目录下创建TRN以及TRG触发器文件，以记事本方式打开这些文件，可以查看触发器的定义。

## 删除触发器 ##

可以使用drop trigger语句将该触发器删除。

drop trigger 触发器名

# 临时表 #

按照MySQL临时表的存储位置可以将其分为内存临时表（in-memory）以及外存临时表（on-disk）。  
按照MySQL临时表的创建时机可以将其分为自动创建的临时表以及手动创建的临时表。

## 临时表的创建、查看与删除 ##

*  手动创建临时表，给正常的create table语句加上temporary关键字即可。
 *  查看临时表的定义可以使用MySQL语句“show create table 临时表名;”。
 *  断开MySQL服务器的连接，临时表frm表结构定义文件以及表记录将被清除。使用drop命令也可以删除临时表，语法格式如下。  
    drop temporary table 临时表表名

# 派生表（Derived Table） #

派生表与视图一样，一般在from子句中使用，其语法格式如下（粗体字代码为派生表代码）。

…. from (select子句) 派生表名 ….

派生表必须是一个有效的表，因此它必须遵守以下规则：  
每个派生表必须有自己的别名。  
派生表中的所有字段必须要有名称，字段名必须唯一。

# 子查询、视图、临时表、派生表 #

子查询一般在主查询语句中的where子句或者having子句中使用。  
视图通常在主查询语句中的from子句中使用。

视图本质是一条select语句，执行的是某一个数据源某个字段的查询操作，如果视图的“主查询”语句是update语句、delete语句或者insert语句，且“主查询”语句执行了该数据源该字段的更新操作，那么主查询语句将出错。

对某个表的某个字段操作时，查询操作（select语句）不能与更新操作（update语句、delete语句或者insert语句）同时进行。

与视图相似，临时表一般在from子句中使用。临时表与视图的区别在于：  
临时表本质也是一条select语句，执行的是某一个数据源某个字段的查询操作，但由于临时表会先执行完毕，并且将查询结果集提前置放到服务器内存。因此 **临时表** 的 **主查询** 语句（例如update、delete或者insert语句）执行字段的更新操作时，不会产生“ERROR 1443 (HY000)”错误。

派生表与临时表的功能基本相同，它们之间的最大区别在于生命周期不同。

临时表如果是手工创建，那么临时表的生命周期在MySQL服务器连接过程中有效；

而派生表的生命周期仅在本次select语句执行的过程中有效，本次select语句执行结束，派生表立即清除。因此，如果希望延长查询结果集的生命周期，可以选用临时表；反之亦然。

另外，通过视图虽然可以更新基表的数据，但本书并不建议这样做。

原因在于：通过视图更新基表数据，并不会触发触发器的运行。

[MySQL_MySQL]: https://blog.csdn.net/qq_20288327/article/details/116432124
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwMjg4MzI3_size_16_color_FFFFFF_t_70]: /images/20221023/c3c5d123981943d498c6c4d814dbea55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwMjg4MzI3_size_16_color_FFFFFF_t_70 1]: /images/20221023/0bd29a20937e4ce0983bcbaf3e938d84.png