MySQL技能完整学习列表8、触发器、视图和事务——1、触发器（Triggers）的使用——2、视图（Views）的创建和使用——3、事务（Transactions）的管理

发布于

触发器（Triggers）的使用

MySQL的触发器（Triggers）是一种特殊类型的存储过程，它会在数据表上的特定操作（插入、更新或删除）发生时自动执行。触发器可以帮助我们实现数据的自动处理、验证和维护等任务。下面将详细说明MySQL触发器的使用方法，并提供具体的示例。

创建触发器

触发器可以使用CREATE TRIGGER语句创建。CREATE TRIGGER语句的基本语法如下：

代码语言：javascript
复制
CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    BEGIN
        -- 触发器的执行语句
    END;
  • trigger_name是触发器的名称。
  • trigger_time指定触发器的时间，可以是BEFORE或AFTER。
  • trigger_event指定触发的事件，可以是INSERT、UPDATE或DELETE。
  • table_name是触发器所关联的数据表名称。
  • FOR EACH ROW表示触发器将为每一行执行。
  • BEGIN ... END之间是触发器的主体，包含了一组SQL语句。
示例：创建一个简单的触发器

下面的示例演示了如何创建一个简单的触发器，该触发器在向users表插入新记录之前，自动为新记录的created_at字段设置当前时间。

代码语言：javascript
复制
DELIMITER //
CREATE TRIGGER SetCreatedAtBeforeInsert
    BEFORE INSERT ON users FOR EACH ROW
    BEGIN
        SET NEW.created_at = NOW();
    END //
DELIMITER ;

在这个示例中，我们创建了一个名为SetCreatedAtBeforeInsert的触发器，它在向users表插入新记录之前执行。触发器的主体是一个SET语句，将新记录的created_at字段设置为当前时间（使用NOW()函数获取）。由于我们使用了BEFORE INSERT，所以这个设置将在实际插入数据之前生效。

测试触发器

要测试触发器是否按预期工作，可以向关联的数据表中插入、更新或删除数据，并观察触发器的执行效果。以下是一个测试上面创建的触发器的示例：

代码语言：javascript
复制
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
SELECT * FROM users; -- 查看插入的记录，created_at字段应该被自动设置了当前时间

视图（Views）的创建和使用

MySQL的视图（Views）是一种虚拟的表，它是根据SELECT语句的结果集创建的。视图本身不存储数据，它只是保存了一条用于生成结果集的SELECT语句。当查询视图时，MySQL会执行该SELECT语句并返回结果集。视图可以帮助我们简化复杂的查询操作、隐藏部分数据、实现数据抽象等。下面将详细说明MySQL视图的创建和使用方法，并提供具体的示例。

创建视图

视图可以使用CREATE VIEW语句创建。CREATE VIEW语句的基本语法如下：

代码语言：javascript
复制
CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
  • view_name是视图的名称。
  • column1, column2, ...是视图中要包含的列名。
  • table_name是要从中选择数据的表名。
  • WHERE condition是可选的筛选条件，用于限制视图中的数据。
示例：创建一个简单的视图

下面的示例演示了如何创建一个简单的视图，该视图从users表中选择姓名和电子邮件列：

代码语言：javascript
复制
CREATE VIEW UserNamesAndEmails AS
    SELECT name, email
    FROM users;

在这个示例中，我们创建了一个名为UserNamesAndEmails的视图，它包含了users表中的name和email列。当查询该视图时，将返回这两列的数据。

使用视图

使用视图就像使用普通的表一样，可以在SELECT语句中引用视图名称来查询数据。以下是一个使用上面创建的UserNamesAndEmails视图的示例：

代码语言：javascript
复制
SELECT * FROM UserNamesAndEmails; -- 查询视图中的所有数据

还可以对视图应用筛选条件、排序等操作，就像对普通表一样：

代码语言：javascript
复制
SELECT * FROM UserNamesAndEmails WHERE email LIKE '%@example.com'; 
-- 筛选特定域名的用户

事务（Transactions）的管理

MySQL的事务（Transactions）是一组数据库操作语句的逻辑单元，可以保证这组操作要么全部执行成功，要么全部回滚（撤销）。事务管理对于保证数据的完整性、一致性和并发控制非常重要。下面将详细说明MySQL事务的管理方法，并提供具体的示例。

事务的基本要素

事务具有以下四个基本要素，通常简称为ACID特性：

  1. 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
  3. 隔离性（Isolation）：事务的执行不被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不会互相干扰。
  4. 持久性（Durability）：一旦事务提交，则其结果就是永久性的。
管理事务的SQL语句

MySQL中使用以下SQL语句来管理事务：

  1. START TRANSACTION或BEGIN：开始一个新的事务。
  2. COMMIT：提交当前事务，使所有已经执行的变更成为永久性的。
  3. ROLLBACK：回滚当前事务，撤销所有未提交的变更。
  4. SAVEPOINT：在事务内部设置一个保存点，用于后续的回滚到该点。
  5. ROLLBACK TO SAVEPOINT：回滚到指定的保存点。
  6. RELEASE SAVEPOINT：删除指定的保存点。
  7. SET TRANSACTION：设置事务的隔离级别。
示例：使用事务进行转账操作

假设我们有一个银行数据库，其中包含两个表：accounts（账户信息）和transactions（交易记录）。现在我们要执行一个转账操作，将A账户的资金转到B账户。这个操作需要同时更新两个账户的余额，并且要保证这两个更新操作的原子性，即要么都成功，要么都失败。我们可以使用事务来实现这个需求。

首先，我们创建测试表和插入测试数据：

代码语言：javascript
复制
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

CREATE TABLE transactions (

id INT PRIMARY KEY AUTO_INCREMENT,

from_account_id INT,

to_account_id INT,

amount DECIMAL(10, 2),

transaction_date DATE,

FOREIGN KEY (from_account_id) REFERENCES accounts(id),

FOREIGN KEY (to_account_id) REFERENCES accounts(id)

);


INSERT INTO accounts (id, name, balance) VALUES (1, 'A', 1000);

INSERT INTO accounts (id, name, balance) VALUES (2, 'B', 500);

接下来，我们执行转账操作的事务：

代码语言：javascript
复制
START TRANSACTION; -- 开始事务


-- 从A账户扣款

UPDATE accounts SET balance = balance - 200 WHERE id = 1;

-- 向B账户存款

UPDATE accounts SET balance = balance + 200 WHERE id = 2;

-- 记录交易信息

INSERT INTO transactions (from_account_id, to_account_id, amount, transaction_date) VALUES (1, 2, 200, CURDATE());


-- 如果上述操作都成功执行，则提交事务，否则回滚事务

COMMIT; -- 或者使用 ROLLBACK 回滚事务

#mysql#transactions#triggers#管理#事务