- users (用户表):
user_id(主键),username,email,created_at - products (商品表):
product_id(主键),product_name,price,stock,category_id - orders (订单表):
order_id(主键),user_id(外键),product_id(外键),quantity,order_date,status
以下是针对五种常见功能场景的详细说明与SQL语句。
基础数据查询与过滤
功能描述：
查询所有状态为“已完成”且总价（数量乘以单价）超过100元的订单详情,并按下单时间倒序排列。
SQL语句：
SELECT
o.order_id,
u.username,
p.product_name,
o.quantity,
p.price,
(o.quantity p.price) AS total_amount,
o.order_date
FROM
orders o
JOIN
users u ON o.user_id = u.user_id
JOIN
products p ON o.product_id = p.product_id
WHERE
o.status = 'completed'
AND (o.quantity p.price) > 100
ORDER BY
o.order_date DESC;
逻辑解析：
- 使用
JOIN关联三张表以获取用户、商品和订单的完整信息。 WHERE子句过滤出状态为 ‘completed’ 的记录。- 在
WHERE中直接计算总价进行过滤,确保只保留高价值订单。 ORDER BY确保结果按时间最新优先显示。
聚合统计与分组
功能描述：
统计每个类别（category_id）下的商品平均价格,并筛选出平均价格高于50元的类别。
SQL语句：
SELECT
category_id,
COUNT() AS product_count,
AVG(price) AS avg_price
FROM
products
GROUP BY
category_id
HAVING
AVG(price) > 50;
逻辑解析：
GROUP BY将数据按category_id分组。AVG(price)计算每组的平均价格，COUNT()统计商品数量。HAVING子句用于对聚合后的结果进行过滤，这里筛选出平均价格大于50的组，注意：不能使用WHERE来过滤聚合函数。
数据插入与事务处理
功能描述：
模拟用户下单过程，需要在一个事务中完成：1. 插入订单记录；2. 扣减商品库存；3. 如果库存不足,则回滚整个操作。
程序段（以Python + SQLAlchemy伪代码为例）：
from sqlalchemy import create_engine, text
from sqlalchemy.exc import IntegrityError
# 假设 engine 已配置好
def create_order(user_id, product_id, quantity):
session = create_session()
try:
# 1. 检查库存
product = session.query(Product).filter_by(product_id=product_id).with_for_update().first()
if product.stock < quantity:
raise Exception("库存不足")
# 2. 计算总价
total_price = product.price quantity
# 3. 插入订单
new_order = Order(
user_id=user_id,
product_id=product_id,
quantity=quantity,
status='pending',
total_amount=total_price
)
session.add(new_order)
# 4. 扣减库存
product.stock -= quantity
# 5. 提交事务
session.commit()
print("订单创建成功")
except Exception as e:
# 发生异常时回滚
session.rollback()
print(f"订单创建失败，已回滚: {str(e)}")
finally:
session.close()
逻辑解析：
- 使用
with_for_update()锁定行,防止并发修改导致超卖。 - 将数据库操作包裹在
try...except块中。 - 成功时调用
commit()，失败时调用rollback()保证数据一致性。
复杂更新与条件逻辑
功能描述：
将所有“待支付”状态且创建时间超过24小时的订单状态更新为“已取消”,并将对应商品的库存恢复。
SQL语句：
-第一步：更新订单状态
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
AND order_date < NOW() INTERVAL '24 hours';
-第二步：恢复库存（通过子查询关联）
UPDATE products p
SET stock = stock + sub.qty
FROM (
SELECT product_id, SUM(quantity) AS qty
FROM orders
WHERE status = 'cancelled'
AND order_date < NOW() INTERVAL '24 hours'
GROUP BY product_id
) sub
WHERE p.product_id = sub.product_id;
逻辑解析：
- 第一个
UPDATE直接修改订单状态。 - 第二个
UPDATE使用子查询找出刚被取消的订单所对应的商品及总数量,然后执行加法操作恢复库存。 - 注意：不同数据库（如MySQL, PostgreSQL, SQL Server）的语法略有差异,此处以通用逻辑为主。
视图创建与权限控制
功能描述：
创建一个视图 v_user_order_summary，仅展示用户的ID、用户名和总消费金额，用于前端报表展示,隐藏敏感信息如邮箱和具体订单明细。
SQL语句：
CREATE VIEW v_user_order_summary AS
SELECT
u.user_id,
u.username,
SUM(o.quantity p.price) AS total_spent
FROM
users u
JOIN
orders o ON u.user_id = o.user_id
JOIN
products p ON o.product_id = p.product_id
GROUP BY
u.user_id, u.username;
-授予只读权限
GRANT SELECT ON v_user_order_summary TO readonly_role;
逻辑解析：
CREATE VIEW将复杂的查询逻辑封装成一个虚拟表。- 视图中只包含必要的聚合字段，隐藏了
email等敏感字段。 GRANT语句限制特定角色只能读取该视图,增强安全性。
相关问题与解答
问题 1：在上述“数据插入与事务处理”场景中，如果两个用户同时购买同一件仅剩1件的商品，如何防止超卖？
解答：
防止超卖的核心在于并发控制，在上述代码中，使用了 with_for_update()（即 SELECT FOR UPDATE）对商品行进行排他锁锁定。
- 当用户A查询商品时，数据库会锁定该行，其他事务无法读取或修改该行数据（直到A的事务结束）。
- 用户A检查库存并扣减,提交事务后锁释放。
- 用户B随后查询时，会发现库存已为0，从而抛出“库存不足”异常。
还可以使用数据库的原子性操作，如UPDATE products SET stock = stock 1 WHERE product_id = ? AND stock >= 1，利用数据库引擎保证更新操作的原子性,即使不加行锁也能有效防止超卖。
问题 2：在“聚合统计与分组”功能中，为什么不能使用 WHERE 子句来过滤 AVG(price) > 50 的结果？
解答：
因为 SQL 的执行顺序决定了过滤时机，SQL 的逻辑执行顺序大致为：FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY。
WHERE子句在分组（GROUP BY）之前执行，它作用于原始的行数据，聚合函数（如AVG,SUM,COUNT）尚未计算出来，WHERE无法识别聚合结果。HAVING子句在分组之后执行，专门用于对聚合后的结果集进行过滤，必须使用HAVING AVG(price) > 50而不是WHERE。
原创文章，发布者：酷盾叔，转转请注明出处：https://www.kd.cn/ask/478195.html
