全栈进阶指南

皮肤切换

第 5 章：多表查询与连接 ​

在实际的数据库应用中，数据通常分散在多个相关的表中。为了获取完整的信息，我们需要学习如何在多个表之间进行查询和连接操作。这是关系型数据库的核心特性之一。

本章内容概要 ​

  • 表之间的关系（一对一、一对多、多对多）
  • 内连接（INNER JOIN）
  • 左连接（LEFT JOIN）
  • 右连接（RIGHT JOIN）
  • 全外连接（FULL OUTER JOIN）
  • 交叉连接（CROSS JOIN）
  • 自连接
  • 子查询（标量子查询、列子查询、行子查询、表子查询）
  • 实战练习：多表关联查询

表之间的关系 ​

在关系型数据库中，表之间存在不同的关系类型：

一对一关系（1:1） ​

一个表中的一条记录只对应另一个表中的一条记录。例如：

  • 学生表与学生档案表
  • 用户表与用户详情表

一对多关系（1:N） ​

一个表中的一条记录可以对应另一个表中的多条记录。例如：

  • 专业表与学生表（一个专业有多个学生）
  • 课程表与成绩表（一门课程有多个成绩记录）

多对多关系（M:N） ​

一个表中的多条记录可以对应另一个表中的多条记录。例如：

  • 学生表与课程表（一个学生可以选多门课程，一门课程可以被多个学生选择）

多对多关系通常通过中间表来实现。

内连接（INNER JOIN） ​

内连接返回两个表中满足连接条件的记录。

基本语法 ​

sql
SELECT 列名
FROM 表1
INNER JOIN 表2 ON 连接条件;

实际示例 ​

sql
USE student_management;

-- 查询学生姓名及其成绩
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
INNER JOIN grades g ON s.id = g.student_id
INNER JOIN courses c ON g.course_id = c.id
ORDER BY s.name, c.course_name;

内连接的特点 ​

内连接只返回两个表中都存在匹配记录的数据，如果某个学生没有成绩记录，或者某门课程没有成绩记录，这些记录不会出现在结果中。

左连接（LEFT JOIN） ​

左连接返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，则对应字段为 NULL。

基本语法 ​

sql
SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 连接条件;

实际示例 ​

sql
-- 查询所有学生及其成绩（包括没有成绩的学生）
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
LEFT JOIN courses c ON g.course_id = c.id
ORDER BY s.name, c.course_name;

这个查询会显示所有学生，即使某些学生还没有成绩记录。

右连接（RIGHT JOIN） ​

右连接与左连接相反，返回右表中的所有记录，以及左表中匹配的记录。

基本语法 ​

sql
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 连接条件;

实际示例 ​

sql
-- 查询所有课程及其成绩（包括没有成绩的课程）
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
RIGHT JOIN grades g ON s.id = g.student_id
RIGHT JOIN courses c ON g.course_id = c.id
ORDER BY c.course_name, s.name;

全外连接（FULL OUTER JOIN） ​

全外连接返回左表和右表中的所有记录。MySQL 不直接支持 FULL OUTER JOIN，但可以通过 UNION 模拟实现。

模拟实现 ​

sql
-- 查询所有学生和所有课程的组合
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
LEFT JOIN courses c ON g.course_id = c.id
UNION
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
RIGHT JOIN grades g ON s.id = g.student_id
RIGHT JOIN courses c ON g.course_id = c.id
WHERE s.id IS NULL
ORDER BY 学生姓名, 课程名称;

交叉连接（CROSS JOIN） ​

交叉连接返回两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。

基本语法 ​

sql
SELECT 列名
FROM 表1
CROSS JOIN 表2;
-- 或者
SELECT 列名
FROM 表1, 表2;

实际示例 ​

sql
-- 查询所有学生和所有课程的组合（用于选课系统初始化）
SELECT 
    s.id AS 学生ID,
    s.name AS 学生姓名,
    c.id AS 课程ID,
    c.course_name AS 课程名称
FROM students s
CROSS JOIN courses c
ORDER BY s.name, c.course_name;

自连接 ​

自连接是指表与自身进行连接，通常用于处理层次结构数据。

实际示例 ​

假设我们有一个员工表，其中包含员工和其上级的信息：

sql
-- 创建员工表示例
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT,
    department VARCHAR(50)
);

-- 插入示例数据
INSERT INTO employees (name, manager_id, department) VALUES
('张总', NULL, '管理层'),
('李经理', 1, '技术部'),
('王经理', 1, '销售部'),
('张三', 2, '技术部'),
('李四', 2, '技术部'),
('王五', 3, '销售部'),
('赵六', 3, '销售部');

-- 查询员工及其直接上级
SELECT 
    e.name AS 员工姓名,
    m.name AS 上级姓名,
    e.department AS 部门
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.department, e.name;

子查询 ​

子查询是嵌套在其他查询中的查询语句。

标量子查询 ​

返回单个值的子查询：

sql
-- 查询成绩高于平均分的学生
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
WHERE g.grade > (SELECT AVG(grade) FROM grades)
ORDER BY g.grade DESC;

列子查询 ​

返回一列值的子查询：

sql
-- 查询选修了"数据库原理"课程的学生
SELECT name AS 学生姓名
FROM students
WHERE id IN (
    SELECT student_id 
    FROM grades 
    WHERE course_id = (
        SELECT id 
        FROM courses 
        WHERE course_name = '数据库原理'
    )
);

行子查询 ​

返回一行数据的子查询：

sql
-- 查询与张三在相同课程获得相同成绩的学生
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
WHERE (g.course_id, g.grade) = (
    SELECT g2.course_id, g2.grade
    FROM students s2
    JOIN grades g2 ON s2.id = g2.student_id
    WHERE s2.name = '张三'
    LIMIT 1
)
AND s.name != '张三';

表子查询 ​

返回多行多列数据的子查询：

sql
-- 查询各科成绩最高的学生
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
WHERE (g.course_id, g.grade) IN (
    SELECT course_id, MAX(grade)
    FROM grades
    GROUP BY course_id
)
ORDER BY c.course_name;

EXISTS 子查询 ​

用于检查子查询是否返回结果：

sql
-- 查询有成绩记录的学生
SELECT name AS 学生姓名
FROM students s
WHERE EXISTS (
    SELECT 1 
    FROM grades g 
    WHERE g.student_id = s.id
);

-- 查询没有成绩记录的学生
SELECT name AS 学生姓名
FROM students s
WHERE NOT EXISTS (
    SELECT 1 
    FROM grades g 
    WHERE g.student_id = s.id
);

实战练习：多表关联查询 ​

让我们通过一个综合的例子来练习多表查询的各种技巧。

1. 复杂成绩分析 ​

sql
-- 查询每个学生的详细成绩报告
SELECT 
    s.student_number AS 学号,
    s.name AS 学生姓名,
    s.major AS 专业,
    COUNT(g.id) AS 选课数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分,
    SUM(c.credits) AS 总学分
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
LEFT JOIN courses c ON g.course_id = c.id
GROUP BY s.id, s.student_number, s.name, s.major
ORDER BY 平均分 DESC;

2. 专业成绩对比 ​

sql
-- 各专业平均成绩对比
SELECT 
    s.major AS 专业,
    COUNT(DISTINCT s.id) AS 学生数,
    COUNT(g.id) AS 成绩记录数,
    ROUND(AVG(g.grade), 2) AS 专业平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
GROUP BY s.major
ORDER BY 专业平均分 DESC;

3. 课程难度分析 ​

sql
-- 各课程的难度分析（平均分越低表示越难）
SELECT 
    c.course_name AS 课程名称,
    c.credits AS 学分,
    COUNT(g.id) AS 参考人数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分,
    ROUND(STDDEV(g.grade), 2) AS 成绩标准差
FROM courses c
LEFT JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name, c.credits
ORDER BY 平均分 ASC;

4. 学期趋势分析 ​

sql
-- 不同学期的成绩趋势分析
SELECT 
    g.semester AS 学期,
    COUNT(g.id) AS 成绩记录数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    COUNT(CASE WHEN g.grade >= 90 THEN 1 END) AS 优秀人数,
    COUNT(CASE WHEN g.grade >= 80 AND g.grade < 90 THEN 1 END) AS 良好人数,
    COUNT(CASE WHEN g.grade >= 60 AND g.grade < 80 THEN 1 END) AS 及格人数,
    COUNT(CASE WHEN g.grade < 60 THEN 1 END) AS 不及格人数
FROM grades g
GROUP BY g.semester
ORDER BY g.semester;

5. 综合排名系统 ​

sql
-- 学生综合排名（考虑学分绩点）
SELECT 
    s.name AS 学生姓名,
    s.major AS 专业,
    COUNT(g.id) AS 选课数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    ROUND(SUM(g.grade * c.credits) / SUM(c.credits), 2) AS 加权平均分,
    RANK() OVER (ORDER BY AVG(g.grade) DESC) AS 平均分排名,
    RANK() OVER (ORDER BY SUM(g.grade * c.credits) / SUM(c.credits) DESC) AS 加权排名
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id
GROUP BY s.id, s.name, s.major
ORDER BY 加权平均分 DESC;

连接查询优化建议 ​

1. 使用适当的连接类型 ​

根据业务需求选择合适的连接类型，避免不必要的全表扫描。

2. 创建合适的索引 ​

为连接条件中的列创建索引：

sql
-- 为外键创建索引
CREATE INDEX idx_grades_student_id ON grades(student_id);
CREATE INDEX idx_grades_course_id ON grades(course_id);

3. 优化 WHERE 条件 ​

将过滤条件尽可能放在 ON 子句中，减少连接的数据量：

sql
-- 优化前
SELECT * FROM students s
LEFT JOIN grades g ON s.id = g.student_id
WHERE g.grade > 80;

-- 优化后
SELECT * FROM students s
LEFT JOIN grades g ON s.id = g.student_id AND g.grade > 80;

小结 ​

在本章中，我们学习了多表查询的各种连接方式：

  1. 内连接（INNER JOIN） - 返回两个表中都匹配的记录
  2. 左连接（LEFT JOIN） - 返回左表所有记录和右表匹配的记录
  3. 右连接（RIGHT JOIN） - 返回右表所有记录和左表匹配的记录
  4. 全外连接（FULL OUTER JOIN） - 返回两个表中的所有记录
  5. 交叉连接（CROSS JOIN） - 返回两个表的笛卡尔积
  6. 自连接 - 表与自身连接处理层次结构数据
  7. 子查询 - 标量子查询、列子查询、行子查询、表子查询

掌握这些多表查询技巧对于处理复杂的关系型数据至关重要。在下一章中，我们将学习索引与性能优化，帮助你提升查询效率。

Copyright © 2025 梁欢的前后端技术笔记 鲁ICP备2025190626号