SQL 多表查询实战：从入门到精通

一、引言

在数据库开发中，多表查询是一个核心技能。掌握多表连接、子查询等技术，能够让你从复杂的数据关系中高效提取所需信息。本文将通过一个完整的电商数据库案例，深入浅出地讲解 SQL 多表查询的各种用法。

二、数据库设计与表结构

我们首先创建一个电商系统的数据库，包含用户、订单、商品分类、商品以及用户与商品关系的中间表。

1. 用户表 (USER)

存储用户基本信息：

• id：用户 ID，主键
• NAME：用户姓名
• age：用户年龄

2. 订单表 (orderlist)

记录用户订单信息：

• id：订单 ID，主键
• number：订单编号
• uid：用户 ID，外键关联 USER 表

3. 商品分类表 (category)

管理商品分类：

• id：分类 ID，主键
• NAME：分类名称

4. 商品表 (product)

存储商品详情：

• id：商品 ID，主键
• NAME：商品名称
• cid：分类 ID，外键关联 category 表

5. 中间表 (us_pro)

建立用户与商品的多对多关系：

• upid：中间表 ID，主键
• uid：用户 ID，外键关联 USER 表
• pid：商品 ID，外键关联 product 表

三、多表查询实战

1. 查询有订单的用户信息

需求：查询用户的编号、姓名、年龄以及订单编号，只返回有订单的用户。

SELECT u.id, u.NAME, u.age, o.number
FROM USER u
INNER JOIN orderlist o ON u.id = o.uid;

关键点：

• 使用 INNER JOIN，只返回两个表中匹配的记录
• 通过用户 ID 关联 USER 表和 orderlist 表

2. 查询所有用户信息 (包括无订单用户)

需求：查询所有用户的编号、姓名、年龄以及订单编号，没有订单的用户订单编号显示为 NULL。

SELECT u.id, u.NAME, u.age, o.number
FROM USER u
LEFT JOIN orderlist o ON u.id = o.uid;

关键点：

• 使用 LEFT JOIN，确保返回左表 (USER) 的所有记录
• 没有匹配订单的用户，订单信息会显示为 NULL

3. 查询所有订单及关联用户信息

需求：查询所有订单的编号，以及对应的用户编号、姓名和年龄。

SQL：

SELECT u.id, u.NAME, u.age, o.number
FROM orderlist o
LEFT JOIN USER u ON o.uid = u.id;

关键点：

• 以订单表为基准进行 LEFT JOIN
• 处理可能存在的无用户关联订单 (uid 为 NULL)

4. 条件查询：年龄大于 23 岁的用户及其订单

需求：查询年龄大于 23 岁的用户编号、姓名、年龄以及订单编号。

SQL：

SELECT u.id, u.NAME, u.age, o.number
FROM USER u
LEFT JOIN orderlist o ON u.id = o.uid
WHERE u.age > 23;

 

关键点：

• 在连接查询后使用 WHERE 子句过滤年龄条件
• 使用 LEFT JOIN 保留无订单用户

5. 特定用户查询：张三和李四的订单信息

需求：查询张三和李四的用户编号、姓名、年龄以及订单编号。

SQL：

SELECT u.id, u.NAME, u.age, o.number
FROM USER u
LEFT JOIN orderlist o ON u.id = o.uid
WHERE u.NAME IN ('张三', '李四');

 

关键点：

• 使用 IN 操作符指定多个匹配值
• 同样使用 LEFT JOIN 保留无订单用户

6. 商品分类与商品信息查询

需求：查询商品分类的编号、名称以及该分类下的商品名称。

SQL：

SELECT c.id, c.NAME AS category_name, p.NAME AS product_name
FROM category c
INNER JOIN product p ON c.id = p.cid;

关键点：

• 使用 INNER JOIN 只返回有商品的分类
• 使用 AS 关键字为列设置别名，提高可读性

7. 查询所有商品分类及商品 (包括无商品分类)

需求：查询所有商品分类的编号、名称以及对应的商品名称，没有商品的分类也要返回。

SQL：

SELECT c.id, c.NAME AS category_name, p.NAME AS product_name
FROM category c
LEFT JOIN product p ON c.id = p.cid;

 

关键点：

• 使用 LEFT JOIN 确保所有分类都被返回
• 无商品的分类，商品名称显示为 NULL

8. 查询所有商品及其分类信息

需求：查询所有商品的编号、名称以及所属分类的编号和名称。

SQL：

SELECT p.id, p.NAME AS product_name, c.id AS category_id, c.NAME AS category_name
FROM product p
LEFT JOIN category c ON p.cid = c.id;

 

关键点：

• 以商品表为基准进行 LEFT JOIN
• 处理可能存在的无分类商品 (cid 为 NULL)

9. 查询用户及其可查看的商品

需求：查询所有用户的编号、姓名、年龄以及该用户能查看的商品名称。

SQL：

SELECT u.id, u.NAME, u.age, p.NAME AS product_name
FROM USER u
LEFT JOIN us_pro up ON u.id = up.uid
LEFT JOIN product p ON up.pid = p.id;

关键点：

• 使用两次 LEFT JOIN 连接三个表
• 通过中间表 us_pro 建立用户与商品的关联

10. 特定用户可查看商品查询

需求：查询张三和李四可以查看的商品，显示用户编号、姓名、年龄以及商品名称。

SQL：

SELECT u.id, u.NAME, u.age, p.NAME AS product_name
FROM USER u
LEFT JOIN us_pro up ON u.id = up.uid
LEFT JOIN product p ON up.pid = p.id
WHERE u.NAME IN ('张三', '李四');

关键点：

• 在多表连接后使用 WHERE 子句过滤特定用户
• 保留用户无可见商品的情况

四、总结与技巧

1. 连接类型选择

• INNER JOIN：只返回匹配的记录
• LEFT JOIN：返回左表所有记录，右表无匹配时显示 NULL
• RIGHT JOIN：返回右表所有记录，左表无匹配时显示 NULL
• FULL OUTER JOIN：返回左右表所有记录，无匹配时显示 NULL (某些数据库不支持)

2. 性能优化建议

• 合理使用索引：在外键和连接字段上创建索引
• 避免 SELECT *：明确指定需要的字段
• 利用 EXPLAIN 分析查询执行计划

3. 复杂查询构建技巧

• 分步构建：先实现简单连接，再添加过滤条件
• 使用别名：提高 SQL 可读性
• 注意 NULL 值处理：使用 COALESCE 等函数处理可能的 NULL 值

掌握这些 SQL 多表查询技术，你将能够轻松应对各种复杂的数据查询需求。关注我，获取更多数据库开发和优化的实用技巧！