死磕数据库系列（十九）：MySQL 视图、触发器的原理与实战

2023-03-16 00:00:00 视图 数据 语句 删除 触发器


今天我将详细的为大家介绍 MySQL 视图、触发器的原理与实战相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

MySQL 视图原理与实战

本文主要讲解视图（view）的基本概念，什么是视图以及视图的使用场景，视图增删改查的实战（CREATE、DESCRIBE、SHOW CREATE、DORP和ALTER），视图的更新操作包括增加（INSERT）、删除（DELETE）和更新（UPDATE）的视图操作基表数据实战。

什么是视图（view）

视图（view）是一个虚拟的逻辑表，视图本身不包含数据，仅作为select语句保存在数据字典中。

视图所依赖的表称为基表（创建视图的数据库表），视图的创建和删除只影响视图本身，而不影响相应的基表，在视图中添加、删除和修改数据时，数据表中的数据将相应地更改，同样的数据库基表中添加、删除和修改视图也会更改。

通常小型项目的数据库一般不使用视图，在大型项目中，当数据表很复杂时，视图可以帮助我们将频繁查询的结果集放到虚拟表中，以提高使用效率。

视图（view）的使用场景

使用视图的不需要关心相应表的结构、关联条件和过滤条件，视图已过滤复合条件的结果集。

使用视图的只能访问允许查询的结果集，基表权限管理不能局限于行或列，可以通过视图简单地实现。

使用视图结构，可以掩盖表结构更改对用户的影响，向基表中添加列对视图没有影响，如果修改了基表的列名，可以通过修改视图来解决，而不会影响访问者。

视图的大部分使用是为了确保数据安全和提高查询效率。

视图增删改查的实战

MySQL创建视图（CREATE VIEW）

CREATE VIEW 语句来创建视图，<SELECT语句>指定创建视图的SELECT语句，该语句可用于查询多个基表或源视图，SELECT不能引用系统或用户变量，不能在FROM中包含子查询，也不能引用预处理的语句参数。视图定义中引用的表或视图必须存在，创建视图后，可以删除定义引用的表或视图。视图定义中允许ORDER BY 语句，视图无法创建TEMPORARY 表（临时表）视图也无法定义临时表。

-- CREATE VIEW 语句来创建视图
-- <SELECT语句>指定创建视图的SELECT语句，该语句可用于查询多个基表或源视图
-- SELECT不能引用系统或用户变量，不能在FROM中包含子查询，也不能引用预处理的语句参数
CREATE VIEW <视图名> AS <SELECT语句>
 
-- 例如
CREATE VIEW view_name AS SELECT * FROM order_info;
MySQL查看视图（DESCRIBE、SHOW CREATE VIEW）

与数据表信息一样，使用DESCRIBE关键字查看视图字段信息，使用 SHOW CREATE VIEW 语句查看视图的详细信息。

-- 查看视图
DESCRIBE 视图名;
DESC 视图名;
 
-- SHOW CREATE VIEW 语句查看视图的详细信息
SHOW CREATE VIEW 视图名;
MySQL删除视图（DORP VIEW）

删除视图是指删除MySQL数据库中的现有视图，删除视图时，只能删除视图的定义，而不能删除数据。

-- 使用 DROP VIEW 语句来删除视图。
DROP VIEW <视图名1> [ , <视图名2> …]
MySQL修改视图（ALTER VIEW）

修改视图（ ALTER VIEW ）是指修改MySQL数据库中现有的视图。当基本表的某些字段发生更改时，可以修改视图以保持与基本表的一致性。

要修改视图的定义，可以使用 ALTER VIEW或 DROP VIEW 语句先删除视图，然后使用 CREATE VIEW 语句再次创建，使用 WITH CHECK OPTION 意味着在修改视图时，检查插入的数据是否满足WHERE设置的条件。

可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于以下结构中的任何一种，它就是不可更新的：聚合函数 SUM()、MIN()、MAX()、COUNT() 等、DISTINCT 关键字、HAVING 、UNION 或 UNION ALL 运算符、FROM 子查询、FROM 包含多个表。

-- 使用 ALTER VIEW 语句来对已有的视图进行修改
ALTER VIEW <视图名> AS <SELECT语句>

视图操作基表数据实战（视图对表的增删改）

视图的更新操作还包括增加（INSERT）、删除（DELETE）和更新（UPDATE）数据，更新操作的实际对象是基表。

-- INSERT语句通过视图添加数据
INSERT [into] 视图名 [(字段名1, 字段名2, …)] values | value (值1, 值2, …);
-- 例如
INSERT into view_name values (***,***,***);
 
-- DELETE语句通过视图删除数据
DELETE from 视图名 [where 条件语句]
-- 例如
DELETE from view_name where name='***';
 
-- UPDATE语句通过视图更新数据
UPDATE 视图名 set 字段名=值 [where 更新条件]
-- 例如
UPDATE view_name set name='****' where name='***'

MySQL触发器原理与实战

本文讲解触发器基本概念与原理，包括什么是触发器、触发器与存储过程异同、触发器的特性、优缺点和实际应用。第二节讲解MySQL触发器实战，包括创建、查看、MySQL修改和删除触发器，后讲解MySQL支持的触发器类型触发器类型NEW和OLD的使用。

触发器基本概念与原理

什么是触发器

触发器是一种特殊的存储过程（主要用于保护表中的数据），当SQL试图更改受触发器保护的数据时，触发器会自动执行。特别是，当存在多个彼此相关的表时，触发器可以使不同的表保持数据一致性。

在MySQL中，触发器只能在执行 INSERT、DELETE 和 UPDATE 操作时激活。其他SQL语句不会激活触发器。

触发器与存储过程异同

MySQL触发器与存储过程（触发器是特殊的存储过程）一样，是完成特定功能并存储在数据库服务器上的SQL片段，它们都是在MySQL中管理数据的工具。

不同之处在于，存储过程的执行需要使用CALL语句来调用SQL段，而触发器的执行不需要使用CALL语句来CALL，也不需要手动启动。相反，当对数据库表中的数据执行DML操作时，SQL段的执行将自动触发，而无需手动调用。例如当学生表被操作（INSERT、DELETE或UPDATE）时，它将被激活以执行。

别名OLD和NEW用于引用触发器中已更改的记录内容，触发器只支持行级触发，不支持语句级触发。

触发器的特性
  • 触发条件？INSERT、UPDATE和DELETE
  • 何时触发？添加、删除或修改之前或之后
  • 触发频率？每行执行一次
  • 触发器都是定义并将其附加到表上
触发器的优缺点

  • 优点：

    • 触发器可以自动执行。它们在触发器相关表中的数据被相应修改后立即执行。
    • 触发器可以实现比 FOREIGN KEY 约束、CHECK 约束更复杂的检查和操作。
    • 触发器可以实现表数据的级联更改，并保证数据的完整性。

  • 缺点：

    • 当SQL出现问题时，使用触发器实现的业务逻辑很难定位，特别是当涉及多个触发器。
    • 大量使用触发器导致代码结构被破坏。
    • 如果要更改的数据量很大，则触发器的执行效率非常低。

触发器的实际应用

触发器的这一特性有助于确保数据库端的数据完整性、日志记录、数据验证等操作。

例如，在开发项目时，数据表更改时，都需要自动处理（统计总数），此时可以使用触发器来处理 。

MySQL触发器实战
创建MySQL触发器

MySQL使用 CREATE TRIGGER 语句创建触发器，BEFORE和AFTER，表示触发触发器的时间是在激活它的语句之前或之后触发的。

如果要验证新数据是否满足条件，使用 BEFORE 选项；如果要在激活触发器的语句执行后完成几个或多个更改，通常使用 AFTER 选项。

CREATE TRIGGER  触发器名 before|after 
触发事件(<INSERT | UPDATE | DELETE >)
ON <表名> FOR EACH Row 执行语句(<触发器主体>);
 
-- 例如：当***表执行了insert 之后，触发器则会自动在user_log添加日志记录
create trigger trigger_name after 
insert 
on user for each row
insert into user_logs values(NULL,now(),'new');
查看MySQL触发器

查看触发器是指查看数据库中已经存在的触发器的定义、状态和语法信息。MySQL中查看触发器的方法包括 SHOW TRIGGERS 语句和查询 information_schema 数据库的查询 triggers 数据表。

-- SHOW TRIGGERS语句查看触发器信息
SHOW TRIGGERS;
 
-- 在triggers表中查看触发器信息
-- 所有触发器的信息都存在 information_schema 数据库的 triggers 表中
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
修改和删除MySQL触发器

使用 DROP 语句将触发器从数据库中删除，DROP TRIGGER 语句需要 SUPER 权限，要修改触发器，必须删除原始触发器并创建一个同名的新触发器。

-- 使用 DROP 语句将触发器从数据库中删除
-- DROP TRIGGER 语句需要 SUPER 权限
-- IF EXISTS：避免在没有触发器的情况下删除触发器
DROP TRIGGER [ IF EXISTS ] [数据库名] <要删除的触发器名称>
 
-- 删除上一节中创建的触发器
DROP TRIGGER trigger_name;
MySQL支持的触发器类型
触发器类型NEW和OLD的使用

MySQL定义了NEW和OLD，用于表示触发器所在表中触发了触发器的哪一行数据，并引用触发器中更改的记录内容

INSERT 类型触发器：NEW表示要添加或已添加的数据

UPDATE 类型触发器：OLD表示修改前的数据，NEW表示要修改或已经修改的数据

DELETE 类型触发器：OLD表示要删除或已删除的数据

INSERT 触发器

在INSERT语句执行之前或之后响应的触发器。

使用INSERT触发器时应注意，在INSERT触发器代码中，可以引用名为NEW（不区分大小写）的虚拟表来访问插入的行。

在 BEFORE INSERT 触发器中，也可以更新NEW中的值，即允许更改插入的值。

在AUTO_INCREMENT 列中，NEW在执行INSERT之前包含 0值，在执行INSERT之后将包含一个新的自动生成的值。

UPDATE 触发器

在执行UPDATE语句之前或之后响应的触发器。

使用UPDATE触发器时应注意，在UPDATE触发器代码中，可以引用名为NEW（不区分大小写）的虚拟表来访问更新的值。

在UPDATE触发器代码中，可以在执行UPDATE语句之前引用名为OLD（不区分大小写）的虚拟表来访问值，OLD中的所有值都是只读的，无法更新。

在BEFORE UPDATE触发器中，也可以更新NEW中的值，也就是说，允许更改UPDATE语句中使用的值（只要您具有相应的操作权限）。

UPDATE触发器，只能使用BEFORE类型的触发器，不允许使用AFTER类型的触发器。

DELETE 触发器

在执行DELETE语句之前或之后响应的触发器。

使用DELETE触发器时应注意，在DELETE触发器代码中，可以引用名为OLD（不区分大小写）的虚拟表来访问删除的行，同样与UPDATE触发器一样，OLD中的所有值都是只读的，无法更新。

来源：https://xiaoming.blog.csdn.net/
article/details/128540827 
https://xiaoming.blog.csdn.net/article
/details/128539290




相关文章