多表查询基础

一、连接查询基础

1.1 准备数据

1.1.1 创建测试表

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    class_id INT
);

-- 创建课程表
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credit TINYINT UNSIGNED
);

-- 创建成绩表
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5, 2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 创建班级表
CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50)
);

-- 插入测试数据
INSERT INTO classes (class_name, teacher) VALUES
('计算机1班', '张老师'),
('计算机2班', '李老师'),
('计算机3班', '王老师');

INSERT INTO students (name, age, class_id) VALUES
('张三', 20, 1),
('李四', 22, 1),
('王五', 21, 2),
('赵六', 19, 2),
('孙七', 23, 3),
('周八', 20, NULL);

INSERT INTO courses (course_name, credit) VALUES
('数据库原理', 4),
('数据结构', 4),
('操作系统', 3),
('计算机网络', 3);

INSERT INTO scores (student_id, course_id, score) VALUES
(1, 1, 85.5),
(1, 2, 92.0),
(2, 1, 78.5),
(2, 2, 88.0),
(3, 1, 95.5),
(3, 3, 82.0),
(4, 2, 90.0),
(5, 1, 88.0);

1.2 连接类型

1.2.1 连接分类

二、内连接

2.1 INNER JOIN

2.1.1 基本语法

语法格式：

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列;

或

SELECT 列名
FROM 表1, 表2
WHERE 表1.列 = 表2.列;

示例：

-- 查询学生及其班级信息
SELECT s.name, s.age, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- 使用WHERE语法
SELECT s.name, s.age, c.class_name
FROM students s, classes c
WHERE s.class_id = c.id;

输出：

+--------+------+---------------+
| name   | age  | class_name    |
+--------+------+---------------+
| 张三   |   20 | 计算机1班     |
| 李四   |   22 | 计算机1班     |
| 王五   |   21 | 计算机2班     |
| 赵六   |   19 | 计算机2班     |
| 孙七   |   23 | 计算机3班     |
+--------+------+---------------+

2.1.2 多表连接

示例：

-- 查询学生、课程和成绩
SELECT s.name, c.course_name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id;

输出：

+--------+-----------------+-------+
| name   | course_name     | score |
+--------+-----------------+-------+
| 张三   | 数据库原理      | 85.50 |
| 张三   | 数据结构        | 92.00 |
| 李四   | 数据库原理      | 78.50 |
| 李四   | 数据结构        | 88.00 |
| 王五   | 数据库原理      | 95.50 |
| 王五   | 操作系统        | 82.00 |
| 赵六   | 数据结构        | 90.00 |
| 孙七   | 数据库原理      | 88.00 |
+--------+-----------------+-------+

2.2 连接条件

2.2.1 使用ON子句

-- 使用ON指定连接条件
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

2.2.2 使用USING子句

语法格式：

SELECT 列名
FROM 表1
INNER JOIN 表2 USING(列名);

示例：

-- 两表有相同列名时使用USING
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c USING(id);

2.2.3 多条件连接

-- 多个连接条件
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c 
    ON s.class_id = c.id 
    AND c.teacher = '张老师';

三、外连接

3.1 左连接

3.1.1 LEFT JOIN

语法格式：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列 = 表2.列;

特点：

• 返回左表所有行
• 右表无匹配则为NULL

示例：

-- 查询所有学生及其班级（包括没有班级的学生）
SELECT s.name, s.age, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

输出：

+--------+------+---------------+
| name   | age  | class_name    |
+--------+------+---------------+
| 张三   |   20 | 计算机1班     |
| 李四   |   22 | 计算机1班     |
| 王五   |   21 | 计算机2班     |
| 赵六   |   19 | 计算机2班     |
| 孙七   |   23 | 计算机3班     |
| 周八   |   20 | NULL          |
+--------+------+---------------+

3.1.2 过滤左连接结果

-- 查询没有班级的学生
SELECT s.name, s.age
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;

输出：

+--------+------+
| name   | age  |
+--------+------+
| 周八   |   20 |
+--------+------+

3.2 右连接

3.2.1 RIGHT JOIN

语法格式：

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列 = 表2.列;

特点：

• 返回右表所有行
• 左表无匹配则为NULL

示例：

-- 查询所有班级及其学生（包括没有学生的班级）
SELECT s.name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;

输出：

+--------+---------------+
| name   | class_name    |
+--------+---------------+
| 张三   | 计算机1班     |
| 李四   | 计算机1班     |
| 王五   | 计算机2班     |
| 赵六   | 计算机2班     |
| 孙七   | 计算机3班     |
| NULL   | 计算机3班     |
+--------+---------------+

3.3 全连接

3.3.1 FULL OUTER JOIN

MySQL不支持FULL OUTER JOIN，使用UNION模拟：

-- 模拟全连接
SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id

UNION

SELECT s.name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;

四、自连接

4.1 自连接概念

4.1.1 定义

自连接：一个表与自身连接，用于比较同一表中的行。

4.1.2 示例表

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, '张总', NULL),
(2, '李经理', 1),
(3, '王经理', 1),
(4, '赵主管', 2),
(5, '孙主管', 3),
(6, '周员工', 4);

4.2 自连接查询

4.2.1 查询员工及其经理

-- 查询员工及其直接上级
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

输出：

+-----------+-----------+
| employee  | manager   |
+-----------+-----------+
| 张总      | NULL      |
| 李经理    | 张总      |
| 王经理    | 张总      |
| 赵主管    | 李经理    |
| 孙主管    | 王经理    |
| 周员工    | 赵主管    |
+-----------+-----------+

4.2.2 查询同一级别的员工

-- 查询同一经理下的员工
SELECT 
    e1.name AS employee1,
    e2.name AS employee2,
    e1.manager_id
FROM employees e1
INNER JOIN employees e2 
    ON e1.manager_id = e2.manager_id
    AND e1.id < e2.id;

输出：

+-----------+-----------+------------+
| employee1 | employee2 | manager_id |
+-----------+-----------+------------+
| 李经理    | 王经理    |          1 |
+-----------+-----------+------------+

五、子查询

5.1 子查询概念

5.1.1 定义

子查询：嵌套在其他查询中的查询，也称为内部查询。

5.1.2 分类

5.2 标量子查询

5.2.1 返回单个值

示例：

-- 查询成绩高于平均成绩的学生
SELECT name, score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
WHERE sc.score > (
    SELECT AVG(score) FROM scores
);

输出：

+--------+-------+
| name   | score |
+--------+-------+
| 张三   | 92.00 |
| 王五   | 95.50 |
| 赵六   | 90.00 |
+--------+-------+

5.2.2 在SELECT中使用

-- 查询学生成绩与平均分的差值
SELECT 
    s.name,
    sc.score,
    (SELECT AVG(score) FROM scores) AS avg_score,
    sc.score - (SELECT AVG(score) FROM scores) AS diff
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

5.3 列子查询

5.3.1 返回一列

使用IN：

-- 查询有成绩的学生
SELECT * FROM students
WHERE id IN (
    SELECT DISTINCT student_id FROM scores
);

使用NOT IN：

-- 查询没有成绩的学生
SELECT * FROM students
WHERE id NOT IN (
    SELECT DISTINCT student_id FROM scores
);

5.3.2 使用ANY和ALL

ANY：满足任一值

-- 查询成绩大于课程1的最低分的学生
SELECT s.name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
WHERE sc.score > ANY (
    SELECT score FROM scores WHERE course_id = 1
);

ALL：满足所有值

-- 查询成绩大于课程1的所有成绩的学生
SELECT s.name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
WHERE sc.score > ALL (
    SELECT score FROM scores WHERE course_id = 1
);

5.4 行子查询

5.4.1 返回一行

示例：

-- 查询与张三同龄同班的学生
SELECT * FROM students
WHERE (age, class_id) = (
    SELECT age, class_id 
    FROM students 
    WHERE name = '张三'
);

5.5 表子查询

5.5.1 派生表

语法格式：

SELECT 列名
FROM (子查询) AS 别名
[WHERE 条件];

示例：

-- 查询每个班级的平均成绩
SELECT c.class_name, avg_score.avg
FROM classes c
INNER JOIN (
    SELECT s.class_id, AVG(sc.score) AS avg
    FROM students s
    INNER JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.class_id
) avg_score ON c.id = avg_score.class_id;

5.6 EXISTS子查询

5.6.1 EXISTS关键字

语法格式：

WHERE EXISTS (子查询)

特点：

• 只判断子查询是否有结果
• 不返回具体数据
• 性能较好

示例：

-- 查询有成绩的学生
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM scores sc
    WHERE sc.student_id = s.id
);

5.6.2 NOT EXISTS

-- 查询没有成绩的学生
SELECT * FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM scores sc
    WHERE sc.student_id = s.id
);

六、UNION操作

6.1 UNION

6.1.1 基本语法

语法格式：

SELECT 列名 FROM 表1
UNION
SELECT 列名 FROM 表2;

特点：

• 合并多个查询结果
• 自动去重
• 列数必须相同
• 列类型必须兼容

示例：

-- 查询所有教师和学生姓名
SELECT teacher AS name FROM classes
UNION
SELECT name FROM students;

输出：

+-----------+
| name      |
+-----------+
| 张老师    |
| 李老师    |
| 王老师    |
| 张三      |
| 李四      |
| 王五      |
| 赵六      |
| 孙七      |
| 周八      |
+-----------+

6.2 UNION ALL

6.2.1 不去重

语法格式：

SELECT 列名 FROM 表1
UNION ALL
SELECT 列名 FROM 表2;

示例：

-- 查询所有教师和学生姓名（包含重复）
SELECT teacher AS name FROM classes
UNION ALL
SELECT name FROM students;

6.3 UNION与JOIN区别

七、连接查询优化

7.1 索引优化

7.1.1 连接字段建立索引

-- 在连接字段上创建索引
CREATE INDEX idx_class_id ON students(class_id);
CREATE INDEX idx_student_id ON scores(student_id);
CREATE INDEX idx_course_id ON scores(course_id);

7.2 小表驱动大表

7.2.1 连接顺序

优化原则：

• 小表驱动大表
• LEFT JOIN左表小
• INNER JOIN MySQL自动优化

示例：

-- 小表驱动大表
SELECT s.name, c.class_name
FROM classes c  -- 小表
INNER JOIN students s ON s.class_id = c.id;  -- 大表

7.3 避免全表扫描

7.3.1 使用WHERE过滤

-- 先过滤再连接
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE s.age > 20;

7.4 使用EXPLAIN分析

7.4.1 查看执行计划

EXPLAIN SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

输出：

+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL              |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.s.class_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+

八、本章小结

8.1 核心要点

✅ INNER JOIN返回匹配行 ✅ LEFT JOIN返回左表所有行 ✅ 自连接用于比较同一表中的行 ✅ 子查询可以嵌套在SELECT、WHERE、FROM中 ✅ UNION合并多个查询结果

8.2 验证清单

完成本章学习后，请确认您能够：

• 使用INNER JOIN查询匹配数据
• 使用LEFT JOIN查询左表所有数据
• 使用自连接查询层级关系
• 使用子查询嵌套查询
• 使用UNION合并查询结果