当前位置： 首页 > ds >正文

SQL详细语法教程（五）事务和视图

ds 2025/8/19 20:01:29

以下是 MySQL 事务 + 视图 完整知识体系拆解，从基础概念到核心逻辑、实操演示，帮你彻底吃透这两块内容：

一、MySQL 事务核心知识点

1. 事务 - 简介
  • 本质：一组 SQL 操作的「原子性执行单元」，要么全成功（提交），要么全失败（回滚），保障数据一致性。
  • 典型场景：转账（A 扣钱、B 加钱必须同时成功 / 失败）、订单创建（减库存 + 生成订单需原子性）。
2. 事务 - 操作演示（以转账为例）
-- 1. 开启事务（2选1）
START TRANSACTION;  
BEGIN;  -- 2. 执行操作（模拟 A 给 B 转账 100）
UPDATE account SET balance = balance - 100 WHERE name = 'A';  
UPDATE account SET balance = balance + 100 WHERE name = 'B';  -- 3. 提交/回滚（2选1）
COMMIT;  -- 提交：操作永久生效  
ROLLBACK; -- 回滚：撤销所有操作，数据回退
  • 关键逻辑：事务内操作需「整体成功」，若中间报错（如余额不足），ROLLBACK 会撤销所有修改，避免数据混乱。
3. 事务 - 四大特性（ACID）
特性作用实现逻辑
原子性事务内操作「要么全成，要么全败」，像原子一样不可拆分基于 undo 日志（回滚时恢复数据）
一致性事务执行前后，数据状态「符合业务规则」（如转账后总金额不变）依赖原子性 + 业务逻辑校验
隔离性多事务并发时，相互「隔离不干扰」，避免脏读、幻读等问题靠锁机制 + MVCC 实现
持久性事务提交后，修改「永久保存到数据库」，即使崩溃也不丢失基于 redo 日志（崩溃后恢复）
4. 并发事务问题（隔离性不足导致）
问题现象描述示例（A、B 同时操作数据）
脏读读了「其他事务未提交」的脏数据（后可能回滚）A 改数据未提交，B 读到；A 回滚，B 数据无效
不可重复读同一事务内，「多次读同一数据结果不同」（被其他事务修改提交）A 读数据，B 改数据提交；A 再读结果不同
幻读同一事务内，「读范围数据时，结果行数变化」（被其他事务插入 / 删除提交）A 查符合条件的行，B 插入新行；A 再查行数变
丢失更新两事务同时改数据，「后提交的覆盖先提交的」（未做并发控制）A、B 同时读数据，A 先提交，B 后提交覆盖
5. 隔离级别（解决并发问题）

MySQL 提供 4 种隔离级别，隔离性越强，并发性能越低，需根据业务权衡：

隔离级别脏读不可重复读幻读并发性能语法（设置会话级别）
读未提交（Read Uncommitted）✔️✔️✔️最高SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
读已提交（Read Committed）❌✔️✔️较高SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
可重复读（Repeatable Read）❌❌弱幻读 ¹中等SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
串行化（Serializable）❌❌❌最低SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

¹ MySQL 中，可重复读通过 MVCC + 间隙锁 解决「大部分幻读」，极端场景仍可能出现（需串行化解决）。

二、MySQL 视图核心知识点

1. 视图 - 介绍及基本语法

  • 本质：一条 SELECT 语句封装成的「虚拟表」，不存数据，只存查询逻辑，用于简化复杂查询、做权限控制。

  • 作用：

    • 简化查询：把多表关联、复杂条件查询封装成视图，直接用 SELECT * FROM 视图。
    • 权限控制：给用户开放视图访问，而非直接暴露底层表，保障数据安全。

  • 语法：

    -- 创建视图（简化多表查询）
CREATE VIEW view_name AS  
SELECT column1, column2...  
FROM table1  
JOIN table2 ON table1.id = table2.id  
WHERE condition;  -- 查询视图（像查普通表一样）
SELECT * FROM view_name;  -- 修改视图
CREATE OR REPLACE VIEW view_name AS ...;  -- 覆盖原视图  
ALTER VIEW view_name AS ...;              -- 另一种修改方式  -- 删除视图
DROP VIEW view_name;
2. 检查选项（Cascaded、Local）

创建视图时，若加 WITH CHECK OPTION，可限制「通过视图修改数据」必须满足视图的 WHERE 条件，避免修改后数据「跳出视图规则」。

  • Cascaded（级联检查）：修改数据时，递归检查所有关联视图 的 WHERE 条件。

    CREATE VIEW view_a AS  
SELECT * FROM table WHERE col > 10  
WITH CASCADED CHECK OPTION;  CREATE VIEW view_b AS  
SELECT * FROM view_a WHERE col < 20  
WITH CASCADED CHECK OPTION;  
-- 修改 view_b 时，需同时满足 view_a（col>10）和 view_b（col<20）条件

  • Local（本地检查）：修改数据时，仅检查当前视图 的 WHERE 条件，不递归检查关联视图。

    CREATE VIEW view_b AS  
SELECT * FROM view_a WHERE col < 20  
WITH LOCAL CHECK OPTION;  
-- 修改 view_b 时，仅检查自身 col<20，不管 view_a 的 col>10
3. 视图 - 更新及作用

  • 可更新场景：视图满足以下条件时，可通过视图 UPDATE/DELETE/INSERT 数据（底层表会同步修改）：

    • 基于 单表 创建，且包含表的「主键 / 唯一键」。
    • 未使用 DISTINCT、GROUP BY、HAVING 等复杂逻辑。

  • 不可更新场景：若视图含以下逻辑，修改会报错（需直接操作底层表）：

    CREATE VIEW view_name AS  
SELECT DISTINCT col FROM table;  -- DISTINCT 导致不可更新  
CREATE VIEW view_name AS  
SELECT col1, col2 FROM table1 JOIN table2;  -- 多表关联，默认不可更新

  • 作用总结：

    • 简化开发：复杂查询封装成视图，业务代码直接用简单查询。
    • 数据安全：隐藏敏感字段（如用户密码），只暴露必要数据。
    • 逻辑复用：多个业务模块复用同一视图查询逻辑，避免重复写 SQL。
4. 视图 - 案例（电商订单简化查询）

需求：封装「订单 + 用户信息」的视图，方便业务查询。

-- 1. 创建视图（关联订单表、用户表）
CREATE VIEW order_user_view AS  
SELECT o.order_id,o.order_amount,u.user_name,u.user_phone  
FROM orders o  
JOIN users u ON o.user_id = u.user_id  
WHERE o.order_status = '已支付';  -- 只查已支付订单  -- 2. 查询视图（像查普通表一样简单）
SELECT * FROM order_user_view WHERE user_name = '张三';  -- 3. 尝试更新（若满足条件可同步改底层表）
UPDATE order_user_view 
SET order_amount = 100 
WHERE order_id = 123;  -- 底层 orders 表的 order_amount 会同步修改

三、知识关联 & 学习建议

  1. 事务 + 视图 协同场景：

    • 视图查询时，若涉及「多表复杂逻辑」，可结合事务保障原子性（如视图统计数据时，需同时改多张表，用事务包裹）。
    • 高并发场景下，视图 + 事务 + 隔离级别 需配合使用（如串行化隔离级别保障视图数据一致性，但会降低性能）。

  2. 学习路径：

    • 先实操 事务流程（开启、提交、回滚），理解 ACID 特性。
    • 再测试 并发问题（用两个客户端模拟脏读、幻读），体会隔离级别的作用。
    • 最后练 视图封装（从简单单表到多表关联），掌握更新规则和业务场景。

四、案例展示

以下是一个结合 MySQL 事务和视图的综合案例，模拟电商平台的订单处理流程，包含完整的业务场景、表结构设计、事务操作和视图应用：

-- 1. 创建数据库和表结构
CREATE DATABASE IF NOT EXISTS ecommerce;
USE ecommerce;-- 用户表
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,balance DECIMAL(10,2) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 商品表
CREATE TABLE products (product_id INT PRIMARY KEY AUTO_INCREMENT,product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL,stock INT NOT NULL DEFAULT 0,status ENUM('在售', '下架') DEFAULT '在售'
);-- 订单表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,total_amount DECIMAL(10,2) NOT NULL,order_status ENUM('待支付', '已支付', '已取消', '已发货', '已完成') DEFAULT '待支付',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,paid_at TIMESTAMP NULL,FOREIGN KEY (user_id) REFERENCES users(user_id)
);-- 订单详情表
CREATE TABLE order_items (item_id INT PRIMARY KEY AUTO_INCREMENT,order_id INT NOT NULL,product_id INT NOT NULL,quantity INT NOT NULL,unit_price DECIMAL(10,2) NOT NULL,FOREIGN KEY (order_id) REFERENCES orders(order_id),FOREIGN KEY (product_id) REFERENCES products(product_id)
);-- 2. 初始化测试数据
INSERT INTO users (username, balance) VALUES 
('张三', 5000.00),
('李四', 3000.00);INSERT INTO products (product_name, price, stock) VALUES 
('iPhone 15', 6999.00, 10),
('华为Mate 60', 5999.00, 20),
('小米14', 3999.00, 30);-- 3. 创建视图（用于订单管理和数据分析）
-- 3.1 订单详情视图（关联订单和订单详情）
CREATE OR REPLACE VIEW order_details_view AS
SELECT o.order_id,o.user_id,u.username,o.total_amount,o.order_status,o.created_at,i.product_id,p.product_name,i.quantity,i.unit_price,(i.quantity * i.unit_price) AS item_total
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items i ON o.order_id = i.order_id
JOIN products p ON i.product_id = p.product_id
ORDER BY o.created_at DESC;-- 3.2 商品销售统计视图（带检查选项）
CREATE OR REPLACE VIEW product_sales_view AS
SELECT p.product_id,p.product_name,p.price,SUM(i.quantity) AS total_sold,SUM(i.quantity * i.unit_price) AS total_revenue
FROM products p
LEFT JOIN order_items i ON p.product_id = i.product_id
LEFT JOIN orders o ON i.order_id = o.order_id
WHERE o.order_status = '已支付' AND p.status = '在售'
GROUP BY p.product_id, p.product_name, p.price
WITH CASCADED CHECK OPTION;-- 4. 订单处理事务（完整流程：创建订单→支付订单→扣减库存→更新余额）
DELIMITER //-- 4.1 创建订单存储过程（包含事务）
CREATE PROCEDURE create_order(IN p_user_id INT,IN p_product_id INT,IN p_quantity INT,OUT p_order_id INT
)
BEGINDECLARE v_product_price DECIMAL(10,2);DECLARE v_product_stock INT;DECLARE v_total_amount DECIMAL(10,2);DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 发生异常时回滚事务ROLLBACK;SELECT '创建订单失败，已回滚' AS result;END;START TRANSACTION;-- 检查商品是否存在及库存是否充足SELECT price, stock INTO v_product_price, v_product_stock FROM products WHERE product_id = p_product_id AND status = '在售';IF v_product_stock < p_quantity THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';END IF;-- 计算总金额SET v_total_amount = v_product_price * p_quantity;-- 创建订单INSERT INTO orders (user_id, total_amount) VALUES (p_user_id, v_total_amount);SET p_order_id = LAST_INSERT_ID();-- 创建订单详情INSERT INTO order_items (order_id, product_id, quantity, unit_price)VALUES (p_order_id, p_product_id, p_quantity, v_product_price);-- 扣减库存（预留）UPDATE products SET stock = stock - p_quantity WHERE product_id = p_product_id;COMMIT;SELECT CONCAT('订单创建成功，订单ID：', p_order_id) AS result;
END //-- 4.2 支付订单存储过程（包含事务）
CREATE PROCEDURE pay_order(IN p_order_id INT
)
BEGINDECLARE v_user_id INT;DECLARE v_total_amount DECIMAL(10,2);DECLARE v_user_balance DECIMAL(10,2);DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SELECT '支付失败，已回滚' AS result;END;START TRANSACTION;-- 检查订单状态SELECT user_id, total_amount, order_status INTO v_user_id, v_total_amount, @order_statusFROM orders WHERE order_id = p_order_id;IF @order_status != '待支付' THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单状态错误，无法支付';END IF;-- 检查用户余额SELECT balance INTO v_user_balance FROM users WHERE user_id = v_user_id;IF v_user_balance < v_total_amount THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足，无法支付';END IF;-- 更新订单状态UPDATE orders SET order_status = '已支付', paid_at = CURRENT_TIMESTAMP WHERE order_id = p_order_id;-- 扣减用户余额UPDATE users SET balance = balance - v_total_amount WHERE user_id = v_user_id;COMMIT;SELECT '订单支付成功' AS result;
END //DELIMITER ;-- 5. 测试订单处理流程
-- 5.1 张三购买2台小米14（product_id=3）
CALL create_order(1, 3, 2, @order_id); -- 创建订单
SELECT @order_id; -- 查看生成的订单ID-- 5.2 支付订单（使用上一步生成的订单ID）
CALL pay_order(@order_id);-- 5.3 通过视图查询订单详情
SELECT * FROM order_details_view WHERE order_id = @order_id;-- 5.4 通过视图查询商品销售统计
SELECT * FROM product_sales_view;-- 5.5 测试异常场景（库存不足）
CALL create_order(1, 1, 20, @order_id2); -- iPhone 15只有10台库存，会触发回滚-- 5.6 测试异常场景（余额不足）
CALL create_order(2, 1, 1, @order_id3); -- 李四创建订单购买iPhone 15
CALL pay_order(@order_id3); -- 李四余额3000 < 6999，会触发回滚

案例解析

这个综合案例模拟了电商平台的核心订单处理流程，融合了事务和视图的关键知识点：

1. 业务流程设计
  • 完整链路：创建订单 → 库存检查 → 订单支付 → 余额扣减 → 数据统计
  • 核心保障：通过事务确保每个环节的原子性，要么全成功，要么全回滚
2. 事务应用亮点
  • 异常处理：使用EXIT HANDLER FOR SQLEXCEPTION捕获异常并自动回滚
  • 多表操作：在一个事务中完成订单创建、库存扣减、余额更新等跨表操作
  • 业务校验：包含库存检查、余额检查、订单状态检查等完整校验逻辑
  • 保存点思想：通过存储过程的分步执行实现类似保存点的效果，提高事务灵活性
3. 视图应用场景
  • 订单详情视图：关联 4 张表（orders、users、order_items、products），简化复杂查询
  • 销售统计视图：自带WITH CASCADED CHECK OPTION，确保统计数据符合 "已支付 + 在售" 条件
  • 数据安全：通过视图展示必要信息，隐藏敏感字段（如用户余额明细）
4. 测试场景覆盖
  • 正常订单流程（创建→支付→查询）
  • 异常场景（库存不足、余额不足、状态错误）
  • 视图查询验证（通过视图快速获取统计数据）

通过这个案例可以清晰看到：事务保障了业务操作的数据一致性，视图则简化了数据查询和统计分析，两者结合能有效提升数据库操作的安全性和开发效率。

http://www.xdnf.cn/news/18326.html

相关文章：

  • ubuntu 下载安装tomcat简单配置（傻瓜式教程）
  • 如何生成和安全保存私钥？
  • 信号上升时间Tr不为0的信号反射情况
  • scikit-learn/sklearn学习|弹性网络ElasticNet解读
  • linux系统查看ip命令
  • 深度学习与线性模型在扰动预测上的比较
  • kafka 冲突解决 kafka安装
  • 如何在VS Code中使用Copilot与MCP服务器增强开发体验
  • 【Linux操作系统】简学深悟启示录：进程状态优先级
  • Android RxBinding 使用指南：响应式UI编程利器
  • 数据转换细节揭秘：ETL如何精准映射复杂业务逻辑
  • 27.Linux 使用yum安装lamp，部署wordpress
  • 【自动化测试】Selenium详解-WebUI自动化测试
  • Linux: RAID（磁盘冗余阵列）配置全指南
  • 作业标准化：制造企业的效率基石与品质保障
  • 可编辑150页PPT | 某制造集团产业数字化转型规划方案
  • idea部署到docker
  • 【MyBatis-Plus】一、快速入门
  • kafka 发送消息有哪些模式？各自的应用场景是什么？
  • 秋招笔记-8.17
  • Java 学习笔记（基础篇5）
  • 【OpenAI】 GPT-4o-realtime-preview 多模态、实时交互模型介绍+API的使用教程！
  • 宋红康 JVM 笔记 Day05｜运行时数据区内部结构、JVM中的线程说明、程序计数器
  • RAID服务器
  • PDF处理控件Aspose.PDF教程：将 PNG 合并为 PDF
  • HTTP协议-4-浏览器是怎么抉择HTTP版本的？
  • 【Java基础】反射，注解，异常，Java8新特性，object类-详细介绍
  • 01.Linux小技巧
  • 「Flink」业务搭建方法总结
  • Chromium base 库中的 Observer 模式实现：ObserverList 与 ObserverListThreadSafe 深度解析