MySQL / 系统课时
编写业务增删改查
编写业务增删改查
这一课把用户、商品和订单三类数据串成一套可执行的 CRUD 操作。你会建立最小业务表,完成新增、查询、修改和删除,并通过事务与外键约束避免把订单改成不一致的数据。
学习目标
- 能为用户、商品和订单编写常见的新增、查询、修改与删除 SQL
- 能用事务保证订单写入与库存扣减要么一起成功、要么一起回滚
- 能识别删除顺序、主键条件和受影响行数等关键边界
准备业务数据表
示例使用 users、products、orders 三张表。订单通过外键引用用户和商品,并记录购买数量与下单时的商品单价。这样即使商品以后调价,历史订单金额仍能按订单中的 unit_price 计算。
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'CREATED',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id)
);预期结果
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected不同 MySQL 版本显示的执行时间可能不同,但三张表都应成功创建。
完成新增与查询
先写入一名用户和一个商品,再生成订单。实际业务中不应依赖固定自增 ID,因此这里使用 LAST_INSERT_ID() 保存刚刚生成的主键。
INSERT INTO users (username) VALUES ('alice');
SET @user_id = LAST_INSERT_ID();
INSERT INTO products (name, price, stock)
VALUES ('机械键盘', 399.00, 20);
SET @product_id = LAST_INSERT_ID();
INSERT INTO orders (user_id, product_id, quantity, unit_price)
VALUES (@user_id, @product_id, 2, 399.00);
SELECT o.id,
u.username,
p.name AS product_name,
o.quantity,
o.unit_price,
o.quantity * o.unit_price AS order_amount,
o.status
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
JOIN products AS p ON p.id = o.product_id
WHERE o.id = LAST_INSERT_ID();预期结果
+----+----------+--------------+----------+------------+--------------+---------+
| id | username | product_name | quantity | unit_price | order_amount | status |
+----+----------+--------------+----------+------------+--------------+---------+
| <ID> | alice | 机械键盘 | 2 | 399.00 | 798.00 | CREATED |
+----+----------+--------------+----------+------------+--------------+---------+<ID> 是数据库生成的订单主键,实际值会随当前数据变化。业务查询通常应明确列名,不建议长期使用 SELECT *,这样可以减少无关字段传输,也能避免表结构变化影响调用方。
用事务完成订单修改
订单提交后,库存扣减和订单状态变化属于同一个业务动作。若只成功执行其中一条 SQL,就会出现订单已确认但库存没减少,或者库存减少却没有有效订单的问题。
下面的事务先锁定商品行并检查库存,再扣减库存、更新订单状态。示例假设当前订单购买数量为 2,商品库存至少为 2。
START TRANSACTION;
SELECT stock
FROM products
WHERE id = @product_id
FOR UPDATE;
UPDATE products
SET stock = stock - 2
WHERE id = @product_id
AND stock >= 2;
UPDATE orders
SET status = 'PAID'
WHERE user_id = @user_id
AND product_id = @product_id
AND status = 'CREATED';
COMMIT;
SELECT stock
FROM products
WHERE id = @product_id;
SELECT status
FROM orders
WHERE user_id = @user_id
AND product_id = @product_id;预期结果
+-------+
| stock |
+-------+
| 18 |
+-------+
+--------+
| status |
+--------+
| PAID |
+--------+生产代码还应检查每条 UPDATE 的受影响行数。如果库存更新影响 0 行,说明库存不足或商品不存在,此时应执行 ROLLBACK,而不是继续把订单改成已支付。
安全地修改和删除数据
修改用户状态时,应使用主键或其他明确条件限制目标行。删除数据时则要考虑外键依赖:当前结构下,存在订单的用户或商品不能直接删除。
UPDATE users
SET status = 'DISABLED'
WHERE id = @user_id;
DELETE FROM orders
WHERE user_id = @user_id
AND product_id = @product_id;
DELETE FROM users
WHERE id = @user_id;
DELETE FROM products
WHERE id = @product_id;预期结果
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected真实系统通常不会随意物理删除订单,而会通过状态字段进行取消、关闭或归档。无论采用物理删除还是逻辑删除,核心原则都一样:写操作必须带明确条件,删除前先处理依赖关系,并核对受影响行数是否符合预期。
CRUD 编写时的关键边界
新增时要依赖数据库约束阻止重复用户名、无效外键等错误;查询时只返回业务需要的字段;修改和删除时优先使用主键或唯一键定位;涉及多张表的一致性操作则放入同一事务。把这几条原则落实到 SQL 中,基础 CRUD 才能从“能执行”提升到“可以安全用于业务”。