第9章：表连接查询（多表操作，核心）

在实际开发中，数据通常存储在多个相关联的表中。表连接查询是将多个表的数据关联起来进行查询的重要操作，是 MySQL 进阶操作的核心内容。

9.1 表连接的意义

当数据分散在多个表中时，我们需要通过表连接来获取完整的信息：

学生表：存储学生基本信息（ID、姓名、班级ID）
班级表：存储班级信息（班级ID、班级名称）
成绩表：存储学生成绩（学生ID、科目、分数）

通过表连接，我们可以：

查询学生姓名及所属班级
查询学生成绩及对应科目
统计班级平均成绩

9.2 内连接（INNER JOIN，最常用）

内连接是最常用的连接方式，只返回两个表中匹配的数据。

9.2.1 基本语法

sql

SELECT 字段列表
FROM 表1
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

说明：

INNER JOIN 可以简写为 JOIN
ON 子句指定连接条件

9.2.2 示例

创建示例表：

sql

-- 创建班级表
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 插入数据
INSERT INTO classes VALUES (1, '一班'), (2, '二班'), (3, '三班');
INSERT INTO students VALUES 
(1, '张三', 1),
(2, '李四', 1),
(3, '王五', 2),
(4, '赵六', 2),
(5, '钱七', NULL);  -- 无班级的学生

内连接查询：

sql

-- 查询学生姓名及所属班级
SELECT 
    s.id AS 学生ID,
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

查询结果：

学生ID	学生姓名	班级名称
1	张三	一班
2	李四	一班
3	王五	二班
4	赵六	二班

注意

内连接只返回两边都匹配的数据，所以没有班级的学生（钱七）不会被返回。

9.3 左连接（LEFT JOIN）

左连接返回左表的所有数据，右表匹配的数据，无匹配则显示 NULL。

9.3.1 基本语法

sql

SELECT 字段列表
FROM 表1
LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

9.3.2 示例

sql

-- 查询所有学生及其所属班级（包括无班级的学生）
SELECT 
    s.id AS 学生ID,
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

查询结果：

学生ID	学生姓名	班级名称
1	张三	一班
2	李四	一班
3	王五	二班
4	赵六	二班
5	钱七	NULL

9.4 右连接（RIGHT JOIN）

右连接返回右表的所有数据，左表匹配的数据，无匹配则显示 NULL。

9.4.1 基本语法

sql

SELECT 字段列表
FROM 表1
RIGHT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

9.4.2 示例

sql

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

查询结果：

班级ID	班级名称	学生姓名
1	一班	张三
1	一班	李四
2	二班	王五
2	二班	赵六
3	三班	NULL

9.5 多表连接

可以连接3张及以上的表，实现更复杂的关联查询。

9.5.1 示例：学生、班级、成绩三表连接

创建成绩表：

sql

CREATE TABLE scores (
    id INT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 插入数据
INSERT INTO scores VALUES 
(1, 1, '语文', 85),
(2, 1, '数学', 92),
(3, 2, '语文', 78),
(4, 2, '数学', 88),
(5, 3, '语文', 90),
(6, 3, '数学', 95);

三表连接查询：

sql

-- 查询学生姓名、所属班级、科目、成绩
SELECT 
    s.name AS 学生姓名,
    c.name AS 班级名称,
    sc.subject AS 科目,
    sc.score AS 成绩
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
LEFT JOIN scores sc ON s.id = sc.student_id
ORDER BY s.id, sc.subject;

查询结果：

学生姓名	班级名称	科目	成绩
张三	一班	语文	85
张三	一班	数学	92
李四	一班	语文	78
李四	一班	数学	88
王五	二班	语文	90
王五	二班	数学	95
赵六	二班	NULL	NULL
钱七	NULL	NULL	NULL

9.6 表连接的高级用法

9.6.1 使用别名

为表起别名可以简化 SQL 语句：

sql

-- 使用别名简化
SELECT 
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM students AS s
JOIN classes AS c ON s.class_id = c.id;

-- 别名可以省略 AS
SELECT 
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM students s
JOIN classes c ON s.class_id = c.id;

9.6.2 多条件连接

可以在 ON 子句中添加多个连接条件：

sql

-- 多条件连接
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id AND t1.status = t2.status;

9.6.3 结合 WHERE 条件

在连接后可以使用 WHERE 子句进行筛选：

sql

-- 连接后筛选
SELECT 
    s.name AS 学生姓名,
    c.name AS 班级名称
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.name = '一班';  -- 只查询一班的学生

9.7 实操案例

案例1：商品与订单管理

创建表：

sql

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入数据
INSERT INTO products VALUES 
(1, 'iPhone 13', 5999.00),
(2, 'iPad Pro', 6799.00),
(3, 'MacBook Air', 7999.00);

INSERT INTO orders VALUES 
(1, 1, 2, '2023-01-01'),
(2, 1, 1, '2023-01-02'),
(3, 2, 3, '2023-01-03'),
(4, 3, 1, '2023-01-04');

查询：

sql

-- 查询订单详情（包含商品名称和价格）
SELECT 
    o.id AS 订单ID,
    p.name AS 商品名称,
    p.price AS 单价,
    o.quantity AS 数量,
    p.price * o.quantity AS 总价,
    o.order_date AS 订单日期
FROM orders o
JOIN products p ON o.product_id = p.id;

-- 统计每个商品的销售数量
SELECT 
    p.name AS 商品名称,
    SUM(o.quantity) AS 销售数量,
    SUM(p.price * o.quantity) AS 销售总额
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id, p.name;

案例2：员工与部门管理

创建表：

sql

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入数据
INSERT INTO departments VALUES (1, '技术部'), (2, '销售部'), (3, '财务部');
INSERT INTO employees VALUES 
(1, '张三', 1, 10000),
(2, '李四', 1, 12000),
(3, '王五', 2, 8000),
(4, '赵六', 2, 9000),
(5, '钱七', NULL, 7000);

查询：

sql

-- 查询所有员工及其部门（包括无部门的员工）
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称,
    e.salary AS 工资
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 查询每个部门的平均工资
SELECT 
    d.name AS 部门名称,
    COUNT(*) AS 员工人数,
    AVG(e.salary) AS 平均工资
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name;

9.8 新手易错点

❌ 错误1：连接条件错误

sql

-- 错误：连接条件字段类型不匹配
SELECT * FROM students s JOIN classes c ON s.id = c.name;

-- 正确：使用正确的关联字段
SELECT * FROM students s JOIN classes c ON s.class_id = c.id;

❌ 错误2：多表连接语法混乱

sql

-- 错误：连接顺序混乱
SELECT * FROM students s JOIN classes c JOIN scores sc ON s.id = sc.student_id ON s.class_id = c.id;

-- 正确：每个 JOIN 都有对应的 ON 子句
SELECT * FROM students s JOIN classes c ON s.class_id = c.id JOIN scores sc ON s.id = sc.student_id;

❌ 错误3：字段名重复未指定表名

sql

-- 错误：id 字段在多个表中存在，未指定表名
SELECT id, name FROM students s JOIN classes c ON s.class_id = c.id;

-- 正确：指定表名或别名
SELECT s.id, s.name, c.name AS class_name FROM students s JOIN classes c ON s.class_id = c.id;

❌ 错误4：使用错误的连接类型

sql

-- 错误：需要查询所有学生时使用了内连接
SELECT * FROM students s JOIN classes c ON s.class_id = c.id;

-- 正确：使用左连接
SELECT * FROM students s LEFT JOIN classes c ON s.class_id = c.id;

9.9 本章小结

连接类型	语法	特点
内连接	`INNER JOIN`	只返回匹配的数据
左连接	`LEFT JOIN`	返回左表所有数据，右表匹配数据
右连接	`RIGHT JOIN`	返回右表所有数据，左表匹配数据
多表连接	多个 JOIN	连接3张及以上表

表连接的使用场景：

内连接：需要两边都有匹配数据时
左连接：需要左表所有数据，右表可选时
右连接：需要右表所有数据，左表可选时
多表连接：需要多个相关表的数据时

9.10 课后练习

为学生表和班级表创建内连接，查询所有有班级的学生信息
使用左连接查询所有学生及其班级，包括无班级的学生
使用右连接查询所有班级及其学生，包括无学生的班级
创建学生、班级、成绩三表连接，查询学生的姓名、班级、科目和成绩
统计每个班级的平均成绩

参考答案：

sql

-- 练习1
SELECT s.*, c.name AS class_name FROM students s JOIN classes c ON s.class_id = c.id;

-- 练习2
SELECT s.*, c.name AS class_name FROM students s LEFT JOIN classes c ON s.class_id = c.id;

-- 练习3
SELECT c.*, s.name AS student_name FROM students s RIGHT JOIN classes c ON s.class_id = c.id;

-- 练习4
SELECT s.name AS student_name, c.name AS class_name, sc.subject, sc.score 
FROM students s 
LEFT JOIN classes c ON s.class_id = c.id 
LEFT JOIN scores sc ON s.id = sc.student_id;

-- 练习5
SELECT c.name AS class_name, AVG(sc.score) AS avg_score 
FROM students s 
JOIN classes c ON s.class_id = c.id 
JOIN scores sc ON s.id = sc.student_id 
GROUP BY c.id, c.name;

第9章：表连接查询（多表操作，核心） ​

9.1 表连接的意义 ​

9.2 内连接（INNER JOIN，最常用） ​

9.2.1 基本语法 ​

9.2.2 示例 ​

9.3 左连接（LEFT JOIN） ​

9.3.1 基本语法 ​

9.3.2 示例 ​

9.4 右连接（RIGHT JOIN） ​

9.4.1 基本语法 ​

9.4.2 示例 ​

9.5 多表连接 ​

9.5.1 示例：学生、班级、成绩三表连接 ​

9.6 表连接的高级用法 ​

9.6.1 使用别名 ​

9.6.2 多条件连接 ​

9.6.3 结合 WHERE 条件 ​

9.7 实操案例 ​

案例1：商品与订单管理 ​

案例2：员工与部门管理 ​

9.8 新手易错点 ​

❌ 错误1：连接条件错误 ​

❌ 错误2：多表连接语法混乱 ​

❌ 错误3：字段名重复未指定表名 ​

❌ 错误4：使用错误的连接类型 ​

9.9 本章小结 ​

9.10 课后练习 ​

第9章：表连接查询（多表操作，核心）

9.1 表连接的意义

9.2 内连接（INNER JOIN，最常用）

9.2.1 基本语法

9.2.2 示例

9.3 左连接（LEFT JOIN）

9.3.1 基本语法

9.3.2 示例

9.4 右连接（RIGHT JOIN）

9.4.1 基本语法

9.4.2 示例

9.5 多表连接

9.5.1 示例：学生、班级、成绩三表连接

9.6 表连接的高级用法

9.6.1 使用别名

9.6.2 多条件连接

9.6.3 结合 WHERE 条件

9.7 实操案例

案例1：商品与订单管理

案例2：员工与部门管理

9.8 新手易错点

❌ 错误1：连接条件错误

❌ 错误2：多表连接语法混乱

❌ 错误3：字段名重复未指定表名

❌ 错误4：使用错误的连接类型

9.9 本章小结

9.10 课后练习