本文更新于 2026-04-02

SQL 进阶 - 多表查询

多表关系

在真实业务中，合理的表结构设计是高并发、高性能查询的基石。

一对多 (One-to-Many)
- 案例：一个部门有多个员工；一个用户有多个收货地址。
- 实现：在“多”的一方（从表）建立外键，指向“一”的一方（主表）的主键。
- 实战细节：在分布式或高并发系统中，为了性能，通常不建立物理外键约束，而是由应用层代码来保证逻辑外键的完整性。
多对多 (Many-to-Many)
- 案例：学生选课；电商系统中商品与订单。
- 实现：必须通过第三张中间表（关联表）来实现。中间表通常包含两个外键，分别对应两张原表的主键。
- 实战细节：中间表除了存储双方的 ID，往往还会挂载业务字段，比如“选课时间”、“购买数量”等。
一对一 (One-to-One)
- 案例：用户基础表与用户隐私详情表。
- 实现：在任意一方加入外键，并设置唯一约束 (UNIQUE)。
- 实战细节：主要用于冷热数据分离（垂直拆分）。将不常用的、大体积的字段（如用户简介）拆分出来，提升主表的查询效率。

多表查询概述与性能陷阱

多表查询的本质是将多张表按照某种逻辑横向“拼接”起来。

笛卡尔积 (Cartesian Product)
- 原理：不加任何条件的 SELECT * FROM A, B;。如果 A 表有 1 万条数据，B 表有 1 万条数据，结果将产生 1 亿条垃圾数据。
- 避坑：在编写任何多表查询时，永远先写连接条件（ON 或 WHERE），防止产生笛卡尔积导致数据库瞬间卡死。

内连接 (Inner Join)

最基础的连接，查询的是两张表完全匹配的交集。

隐式内连接（传统的 SQL92 标准，可读性较差）：

    SELECT e.name, d.name 
    FROM emp e, dept d 
    WHERE e.dept_id = d.id AND e.salary > 5000;

显式内连接（SQL99 标准，强烈推荐，逻辑清晰）：

    SELECT e.name, d.name 
    FROM emp e 
    [INNER] JOIN dept d ON e.dept_id = d.id -- INNER 可省略
    WHERE e.salary > 5000;

原理：MySQL 会用小表作为驱动表，去大表中进行匹配（通常配合索引提高速度）。

外连接 (Outer Join)

当你想保留某张表的全部数据，即使另一张表没有对应的数据时，使用外连接。

左外连接 (LEFT JOIN)：
- 特点：包含左表的全部行。如果右表没有匹配，右表的列显示为 NULL。
- 经典案例：查询所有员工及其对应的部门（包括刚入职、还没分配部门的新员工）。

	SELECT e.name, d.name 
	FROM emp e 
	LEFT JOIN dept d ON e.dept_id = d.id;

右外连接 (RIGHT JOIN)：
- 特点：包含右表的全部行。
- 实战建议：在实际开发中，我们 95% 的情况下都使用 LEFT JOIN。因为只需要调整表的先后顺序，LEFT JOIN 完全可以替代 RIGHT JOIN，代码风格更统一。

自连接 (Self Join)

这是一道极为经典的高频面试题。当表中的某列关联了本表的另一列时使用。

典型场景：
- 员工表（包含 emp_id 和 manager_id，领导也是员工）。
- 地区表（包含 area_id 和 parent_id，省、市、区都在一张表里）。
核心技巧：必须把一张表当成两张完全独立的表来看待，通过别名加以区分。
案例：查询员工姓名及其直属领导的姓名。

	-- 这里 a 代表员工表，b 代表领导表
	SELECT a.name AS '员工', b.name AS '领导'
	FROM emp a 
	LEFT JOIN emp b ON a.manager_id = b.id;

联合查询 (Union)

用于把多次 SELECT 的结果集纵向拼接在一起，变成一个大结果集。

UNION：自动去除重复的行，性能稍低，因为包含去重排序的操作。
UNION ALL：直接拼接，不进行去重。
性能军规：除非业务明确要求去重，否则在实际开发中一律优先使用 UNION ALL，性能远高于 UNION。
限制条件：
1. 两次查询的列数必须相同。
2. 对应列的字段类型或语义必须一致。

子查询 (Subquery) 全景解析

子查询是多表查询中极具技巧性的部分。根据子查询返回结果的不同，分为以下四类：

标量子查询

特征：子查询只返回一个孤零零的值。
案例：查询薪资比“张三”高的所有员工

	SELECT * FROM emp 
	WHERE salary > (SELECT salary FROM emp WHERE name = '张三');

列子查询

特征：子查询返回的是一列数据（多行单列）。
核心操作符：
- IN：在指定的集合范围内。
- NOT IN：不在范围内。
- ANY / SOME：满足其中任意一个即可。
- ALL：必须满足所有的条件。
案例：查询研发部和销售部所有员工的工资。

	SELECT name, salary FROM emp 
	WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('研发部', '销售部'));

行子查询

特征：子查询返回的是一行数据（单行多列）。
案例：查询与“李四”的薪资且直属领导完全相同的员工。

	SELECT * FROM emp 
	WHERE (salary, manager_id) = (SELECT salary, manager_id FROM emp WHERE name = '李四');

表子查询

特征：子查询返回的是一个二维表格（多行多列）。
经典用法：作为 FROM 后面的临时表使用，或者用 IN 配合多列。
案例：查询入职日期在“2025-01-01”之后的员工，并关联他们的部门信息。

	-- 将子查询的结果作为一张临时表 temp
	SELECT t.name, d.dept_name 
	FROM (
		SELECT * FROM emp 
		WHERE entry_date > '2025-01-01'
	)t
	LEFT JOIN dept d ON t.dept_id = d.id;

外键约束

一对多

父表（Parent Table / Referenced Table）：被引用的表，通常主键（Primary Key）所在的表
子表（Child Table / Foreign Key Table）：引用父表的表，外键所在的表
外键列：子表中存储父表主键值的列

-- 父表
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL
);

-- 子表
CREATE TABLE Emp(
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    -- 定义外键约束
    CONSTRAINT fk_dept 
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    ON DELETE CASCADE -- 部门删除时，员工也自动删除
);

如果表已经创建好了，可以使用 ALTER TABLE：

-- 添加外键
ALTER TABLE Emp ADD CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    
-- 删除外键
ALTER TABLE Emp DROP FOREIGN KEY fk_dept

一对一

一个用户只能有一个身份证号，一个身份证号也只能对应一个用户

-- 用户表（主表）
CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

-- 身份证表（从表）
CREATE TABLE IDCards (
    card_id INT PRIMARY KEY AUTO_INCREMENT,
    card_number CHAR(18) NOT NULL UNIQUE,
    user_id INT UNIQUE, -- 关键点：UNIQUE 约束确保一对一
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

多对多

-- 学生表
CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 课程表
CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50)
);

-- 中间表（维护多对多关系）
CREATE TABLE Student_Courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id), -- 联合主键防止重复选课
    CONSTRAINT fk_student 
	    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    CONSTRAINT fk_course 
	    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

联合主键（Composite Primary Key）：中间表通常建议使用 (ID_A, ID_B) 作为联合主键，这样可以从物理层面防止数据重复（例如：防止同一个学生对同一门课选两次）
冗余字段：中间表不一定只能有两个 ID。它还可以存储“关系本身”的属性。例如：在 Student_Courses 中增加 score（成绩）或 enroll_date（选课时间）字段
查询方式：查询多对多数据通常需要 两次 JOIN。
- 查询张三选了哪些课： Students JOIN Student_Courses JOIN Courses

物理外键的缺点

性能瓶颈：在高并发写入时，外键校验会导致频繁的行锁竞争，降低吞吐量
分库分表困难：当数据量大到需要拆分到不同物理数据库时，跨库的物理外键是无法实现的
死锁风险：复杂的级联更新容易导致数据库产生死锁，排查极其困难
测试麻烦：手动构造测试数据时，必须先造父表再造子表，流程繁琐

个人常犯的错误

题目要求：查询拥有员工的部门

内连接！
INNER JOIN = 只查两边都匹配的数据（最适合本题）
RIGHT JOIN 会把没有部门的员工也带进来，产生 NULL 部门
题目要有员工的部门 → 必须用 INNER JOIN 或加 IS NOT NULL

	SELECT DISTINCT d.dept_name FROM department d  
	INNER JOIN employee e  
	ON e.dept_id = d.dept_id;

JOIN的使用

	SELECT e.emp_name FROM employee e  
	JOIN(  
	    SELECT dept_id,AVG(salary) AS avg_salary  
	    FROM employee  
	    GROUP BY dept_id  
	)t ON e.dept_id = t.dept_id  
	WHERE e.salary < t.avg_salary;

此SQL语句等价于

	SELECT e.emp_name FROM employee e 
	WHERE e.salary < (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id);

`COUNT(*)`是什么？

写法	意思
`COUNT(*)`	数总行数，包括 NULL
`COUNT(字段)`	数这个字段不为 NULL 的行数
`COUNT(1)`	效果基本同 `COUNT(*)`，也是数行数
`COUNT()`：统计当前组里一共有多少条记录*

	-- 查询每个部门的人数
	SELECT d.dept_name,COUNT(*)   
	FROM employee e,department d  
	WHERE e.dept_id = d.dept_id GROUP BY d.dept_name;