数据库触发器、存储过程、视图、约束

【触发器】

MySQL中的触发器（Trigger）是一种数据库对象，它允许在特定的数据库操作（如INSERT、UPDATE或DELETE）发生之前或之后，自动执行预定义的SQL语句或一系列操作。触发器主要用于增强数据库的逻辑控制，确保数据的完整性和一致性，以及执行一些附加的任务，如审计、日志记录或验证数据。

以下是关于MySQL触发器的一些关键点：

1 触发时机：触发器可以在三个基本的DML（数据操纵语言）操作之后或之前触发：

　　BEFORE INSERT: 在插入新行之前执行。
　　AFTER INSERT: 在插入新行之后执行。
　　BEFORE UPDATE: 在更新行之前执行。
　　AFTER UPDATE: 在更新行之后执行。
　　BEFORE DELETE: 在删除行之前执行。
　　AFTER DELETE: 在删除行之后执行。

2 触发器的使用：触发器可以用来执行复杂的业务规则，比如检查新插入的数据是否符合某些条件，或者在数据更改时更新其他相关的表。

3 触发器的语法：创建触发器通常涉及定义触发器的名称、触发的事件（INSERT, UPDATE 或 DELETE）、触发的表、触发的时间（BEFORE 或 AFTER），以及触发时要执行的SQL语句。

4 行级触发：MySQL中的触发器是行级的，这意味着对于每一条受影响的记录，都会执行一次触发器中的代码。如果在一次操作中有多行受到影响，触发器会为每一行分别执行。

5 NEW 和 OLD 形式：在触发器的定义中，NEW 用于引用即将插入或更新的行的新值，而 OLD 用于引用即将被更新或删除的原始行的值。

6 应用示例：一个常见的用途是在员工表中插入新记录时，自动创建一条日志记录，记录下谁何时进行了插入操作，以及新员工的信息。

7 限制：MySQL不支持语句级触发器，即不能基于整个操作的结果来触发，只能基于单个行级别的改变。

触发器是数据库设计的一个强大工具，但应谨慎使用，因为它们可能会增加数据库的复杂性，并可能影响性能。

在MySQL中，使用触发器通常涉及以下步骤：

1 创建触发器：首先，你需要定义一个触发器，指定触发器的名字、触发的事件（INSERT, UPDATE, DELETE）、触发的表、触发时间（BEFORE 或 AFTER），以及当触发事件发生时要执行的SQL语句。例如，创建一个在employees表中INSERT操作后更新employee_log表的触发器：

```sql
DELIMITER //
CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, action, timestamp)
VALUES (NEW.employee_id, 'INSERT', NOW());
END //
DELIMITER ;
```

这个例子中，每当在employees表中插入新员工时，会在employee_log表中记录一条新的日志条目。

2 查看触发器：要查看已经创建的触发器，可以使用SHOW TRIGGERS命令：

```sql
SHOW TRIGGERS;
```

3 修改或删除触发器：如果需要修改触发器，可以使用ALTER TRIGGER语句；若要删除触发器，使用DROP TRIGGER语句：

```sql
ALTER TRIGGER log_employee_insert ...; -- 修改触发器
DROP TRIGGER IF EXISTS log_employee_insert; -- 删除触发器
```

4 测试触发器：在完成创建或修改触发器后，执行相应的DML操作（INSERT, UPDATE, DELETE）来测试触发器是否按预期工作。

请注意，触发器在数据库层面运行，因此在应用程序中不需要直接调用它们。它们会在相应的数据库操作执行时自动触发。

触发器的性能取决于多个因素，包括触发器的复杂性、执行频率以及数据库的总体负载。以下是一些关于触发器性能的要点：

1 开销：每次触发器关联的DML操作发生时，触发器的代码都会被执行。这会增加数据库服务器的处理负担，特别是在高并发环境下，频繁触发的触发器可能导致性能下降。

2 数据完整性：虽然触发器可以增强数据完整性，但过度依赖触发器来维护数据的一致性可能导致额外的计算和磁盘I/O，尤其是当触发器涉及到多表更新时。

3 事务性能：如果触发器导致了额外的事务操作，比如回滚或拒绝事务，这可能会影响事务处理的速度。

4 优化：通过精心设计和优化触发器的逻辑，可以减少不必要的计算。例如，避免在触发器中进行复杂查询或重复计算。

5 替代方案：在某些情况下，应用程序层的逻辑可能比数据库层的触发器更高效。例如，使用存储过程、视图或约束来实现相同功能。

6 监控和分析：定期监控数据库性能，使用性能分析工具来识别触发器是否成为瓶颈，可以帮助确定是否需要调整或替换触发器。

综上所述，触发器在提供额外的功能和保证数据完整性方面是有价值的，但应当谨慎使用，以免对系统性能造成负面影响。在设计数据库架构时，应权衡其优点和潜在的性能成本。

【数据库约束】

数据库约束是数据库管理系统（DBMS）中用于确保数据完整性的一种机制。它们是规则，定义了数据表中允许的值的类型、范围或关系。

约束的主要目的是防止无效数据的插入、更新或保持数据的一致性。以下是几种常见的数据库约束类型：

1 主键约束（Primary Key Constraint）：

确保表中的每一行都有一个唯一的标识符。
主键列的值必须是唯一的且不允许为空。
一个表只能有一个主键。

2 唯一约束（Unique Constraint）：

确保表中某一列或多列的值是唯一的，但不一定是整行的唯一标识。
可以允许空值，但每个非空值都必须是唯一的。

3 非空约束（Not Null Constraint）：

确保表中的某一列的值不能为空。
这有助于避免因意外的空值而导致的数据丢失或错误。

4 外键约束（Foreign Key Constraint）：

确保表之间的参照完整性。
一个表的外键列的值必须是另一个表（主表）的主键列中的有效值，或者为空（如果允许的话）。

5 检查约束（Check Constraint）：

允许你指定列的值必须满足某种条件。
例如，确保年龄列的值大于0且小于150。

6 默认约束（Default Constraint）：

当没有为列提供值时，提供一个默认值。

在MySQL中，所有这些约束类型都是支持的，但在某些数据库系统中，如SQL Server，还支持其他的约束类型，如Computed Constraints（计算约束）或Identity Constraints（身份约束）。不过，提到的MySQL不支持的约束可能是某种特定的变体或在特定上下文下的不支持。例如，SQL Server的Computed Constraints在MySQL中可能需要通过存储过程或触发器来模拟。

【数据库视图】

数据库视图（View）是数据库管理系统（DBMS）中的一个虚拟表，它是由一个或多个基本表（或其他视图）通过SQL查询语句组合而成。视图并不存储实际的数据，而是提供了一个逻辑上的数据视图，其数据来源于底层的表。当你查询视图时，DBMS会动态地执行视图的定义，生成结果集，仿佛视图是一个实际存在的表。

视图的主要特点和作用包括：

1 数据安全性：视图可以用来限制用户对底层表的访问，只允许他们看到和操作特定的数据，而隐藏敏感信息。

2 简化复杂查询：视图可以预先封装复杂的查询逻辑，使得用户只需要简单地查询视图即可得到所需数据。

3 数据抽象：视图提供了一种数据抽象的方式，允许用户以一种更简单、更有意义的方式看待数据，而不必了解底层表的结构和细节。

4 逻辑独立性：即使底层表的结构发生变化，只要不影响视图的定义，视图的使用就不会受到影响。

5 数据模型简化：视图可以帮助简化数据模型，尤其是在大型数据库中，通过视图可以提供用户友好的接口。

6 查询性能：虽然视图不存储数据，但某些情况下，数据库系统可能会对视图进行物化，以提升查询性能。

创建视图的SQL语句通常是CREATE VIEW，你可以指定视图的名称以及生成视图的SELECT语句。例如：

```sql
CREATE VIEW customer_orders AS
SELECT customers.customer_name, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
```

在这个例子中，customer_orders视图展示了客户名和订单日期，而不需要直接访问customers和orders表。
通过这个视图查询到的数据会反映出BASE TABLE的更新，视图并不存储实际的数据，而是根据其定义实时从基础表中获取数据。

列出所有视图：

```sql
SHOW FULL TABLES WHERE Table_Type = 'VIEW';
```

删除视图：

```sql
DROP VIEW IF EXISTS view_name;
```

数据库视图的使用类似于操作普通数据库表，主要通过以下几种方式：

1 查询视图： 你可以像查询任何其他数据库表一样查询视图。使用SELECT语句从视图中检索数据，例如：

```sql
SELECT * FROM view_name;
```

2 过滤和排序数据： 在查询视图时，可以使用WHERE子句来过滤数据，使用ORDER BY子句来排序结果，就像操作基本表一样：

```sql
SELECT * FROM view_name WHERE column_name = 'some_value' ORDER BY another_column;
```

3 联接视图和其他表： 视图可以与其他视图或基本表一起使用在JOIN操作中，以整合来自不同来源的数据：

```sql
SELECT * FROM view_name JOIN another_table ON view_name.id = another_table.view_id;
```

4 更新视图： 在某些情况下，如果视图是可更新的（即视图的定义允许对基础表的数据进行修改），可以直接通过视图执行INSERT、UPDATE或DELETE操作。但要注意，不是所有的视图都是可更新的，特别是那些包含聚合函数、DISTINCT关键字或子查询的视图。

5 在应用程序中使用视图： 应用程序可以配置为直接与视图交互，而不是基础表，这样可以隐藏数据库的复杂性，简化应用程序代码，并提高数据安全性。

6 作为安全措施： 视图可以用来限制用户访问特定的数据列或行，作为一种安全机制，确保用户只能看到他们被授权查看的数据。

7 性能优化： 在复杂查询场景中，预先计算并存储结果的物化视图可以提高查询性能。

记住，在使用视图时，尽管它们提供了数据的逻辑表示，但重要的是要理解它们背后的查询逻辑和对基础表的影响，以避免不当操作导致的性能问题或数据不一致。

【存储过程】

存储过程（Stored Procedure）是数据库管理系统（DBMS）中一组预先编译的SQL语句和控制流语句，用于执行特定的数据库操作。它们是数据库中的一个可重用组件，可以接受输入参数、返回输出参数，甚至可以有返回值。存储过程有以下几个关键特点和优点：

1 性能优化：由于存储过程在首次创建时会被编译，后续的调用不需要再次编译，从而提高了执行速度，尤其是在处理大量数据时。

2 模块化编程：存储过程可以封装复杂的数据库操作，使得代码更易于管理和维护。

3 安全性：可以对存储过程设置访问权限，限制用户对数据库的直接操作，从而提高数据安全性。

4 减少网络流量：相比于多次发送单独的SQL语句，调用一个存储过程可以减少网络间的通信量。

5 增强功能：存储过程可以包含流程控制语句（如IF-THEN-ELSE, WHILE等），能够执行条件判断和循环等复杂逻辑。

6 可移植性：尽管不同数据库系统可能有不同的语法，但存储过程的概念是通用的，可以跨不同的数据库平台使用。

创建存储过程通常使用SQL语句，例如在MySQL中，创建一个简单的存储过程可能如下所示：

```sql
DELIMITER //
CREATE PROCEDURE simple_procedure (IN param1 VARCHAR(50), OUT param2 VARCHAR(50), OUT param3 VARCHAR(50))
BEGIN
SELECT `name`, `en_name` INTO param2, param3 FROM pip_parameter WHERE `name` = param1;
END; //
DELIMITER ;
```

这个存储过程接受一个输入参数param1，查询table1中column2等于param1的column1值，并将结果保存到输出参数param2。

要调用存储过程，可以使用CALL语句：

```sql
CALL simple_procedure("abc", @r1, @r2);
```

在这里，abc是传递给param1的值，@r1是用于接收param2返回值的变量。

调用存储过程后立即显示@r1的值：

```sql
CALL simple_procedure('值', @r1, @r2);
SELECT @r1,@r2;
```

MySQL使用以下命令列出所有存储过程：

```sql
SHOW PROCEDURE STATUS;
```

删除存储过程：

```sql
DROP PROCEDURE IF EXISTS simple_procedure;
```

Tool：SQL格式化、代码差异比较器、HTML查错器、Llama3在线

Link：https://www.cnblogs.com/farwish/p/18196937