多表查询

在实际应用中，数据通常分散在多个表中。本章将介绍如何使用连接查询从多个表中获取数据。

准备数据

sql

-- 创建示例数据库
CREATE DATABASE IF NOT EXISTS company;
USE company;

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    manager_id INT,
    hire_date DATE
);

-- 项目表
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    budget DECIMAL(12, 2)
);

-- 员工项目关联表
CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50),
    PRIMARY KEY (employee_id, project_id)
);

-- 插入测试数据
INSERT INTO departments (name, location) VALUES
('技术部', '北京'),
('销售部', '上海'),
('人事部', '广州'),
('财务部', '深圳');

INSERT INTO employees (name, department_id, salary, manager_id, hire_date) VALUES
('张三', 1, 15000, NULL, '2020-01-15'),
('李四', 1, 25000, 1, '2018-06-01'),
('王五', 2, 20000, NULL, '2019-03-20'),
('赵六', 2, 12000, 3, '2021-07-10'),
('钱七', 3, 10000, NULL, '2022-01-05'),
('孙八', 1, 16000, 1, '2020-08-15'),
('周九', 4, 14000, NULL, '2019-11-01'),
('吴十', NULL, 18000, NULL, '2021-03-01');  -- 无部门的员工

INSERT INTO projects (name, department_id, budget) VALUES
('电商平台', 1, 500000),
('CRM系统', 1, 300000),
('市场推广', 2, 200000);

INSERT INTO employee_projects (employee_id, project_id, role) VALUES
(1, 1, '项目经理'),
(2, 1, '技术负责人'),
(6, 1, '开发工程师'),
(2, 2, '架构师'),
(6, 2, '开发工程师'),
(3, 3, '负责人'),
(4, 3, '执行人员');

内连接

sql

/*
 * 内连接（INNER JOIN）：
 * 只返回两个表中匹配的记录
 * 如果没有匹配，则不返回该行
 */

-- 基本内连接
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 使用WHERE子句的等价写法
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e, departments d
WHERE e.department_id = d.id;

-- 多表内连接
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称,
    p.name AS 项目名称,
    ep.role AS 角色
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN employee_projects ep ON e.id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.id;

-- 自连接（员工与经理）
SELECT 
    e.name AS 员工,
    m.name AS 经理
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

-- 使用USING简化（当连接列名相同时）
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
INNER JOIN departments d USING (id);  -- 注意：这里id不匹配，仅作语法示例

外连接

左外连接（LEFT JOIN）

sql

/*
 * 左外连接（LEFT JOIN）：
 * 返回左表所有记录，右表没有匹配则显示NULL
 */

-- 左连接：显示所有员工及其部门
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- 吴十没有部门，部门名称显示NULL

-- 只显示没有部门的员工
SELECT 
    e.name AS 员工姓名
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

-- 统计各部门人数（包括0人的部门）
SELECT 
    d.name AS 部门名称,
    COUNT(e.id) AS 人数
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

右外连接（RIGHT JOIN）

sql

/*
 * 右外连接（RIGHT JOIN）：
 * 返回右表所有记录，左表没有匹配则显示NULL
 */

-- 右连接：显示所有部门及其员工
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- 左连接和右连接可以互换
-- 以下两个查询等价：
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

SELECT e.name, d.name
FROM departments d
RIGHT JOIN employees e ON e.department_id = d.id;

全外连接

sql

/*
 * MySQL不支持FULL OUTER JOIN
 * 可以使用UNION模拟
 */

-- 模拟全外连接
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION

SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- 使用UNION ALL（不去重，性能更好）
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION ALL

SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

交叉连接

sql

/*
 * 交叉连接（CROSS JOIN）：
 * 返回两个表的笛卡尔积
 * 左表每行与右表每行组合
 */

-- 交叉连接
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e
CROSS JOIN departments d;

-- 等价于
SELECT 
    e.name AS 员工,
    d.name AS 部门
FROM employees e, departments d;

-- 结果数量 = 左表行数 × 右表行数
SELECT 
    COUNT(*) AS 结果数量,
    (SELECT COUNT(*) FROM employees) AS 员工数,
    (SELECT COUNT(*) FROM departments) AS 部门数
FROM employees e
CROSS JOIN departments d;

自然连接

sql

/*
 * 自然连接（NATURAL JOIN）：
 * 自动基于同名列进行连接
 * 不推荐使用，可读性差
 */

-- 自然连接
SELECT * FROM employees
NATURAL JOIN departments;
-- 自动使用department_id连接

-- 自然左连接
SELECT * FROM employees
NATURAL LEFT JOIN departments;

复杂连接查询

多表连接

sql

-- 查询员工、部门、项目信息
SELECT 
    e.name AS 员工,
    d.name AS 部门,
    p.name AS 项目,
    ep.role AS 角色
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN employee_projects ep ON e.id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.id
ORDER BY e.name, p.name;

-- 查询每个部门的员工数和项目数
SELECT 
    d.name AS 部门,
    COUNT(DISTINCT e.id) AS 员工数,
    COUNT(DISTINCT p.id) AS 项目数
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN projects p ON d.id = p.department_id
GROUP BY d.id, d.name;

连接与聚合

sql

-- 查询各部门的平均工资
SELECT 
    d.name AS 部门,
    COUNT(e.id) AS 人数,
    AVG(e.salary) AS 平均工资,
    MAX(e.salary) AS 最高工资,
    MIN(e.salary) AS 最低工资
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 0;

-- 查询参与多个项目的员工
SELECT 
    e.name AS 员工,
    COUNT(ep.project_id) AS 项目数
FROM employees e
INNER JOIN employee_projects ep ON e.id = ep.employee_id
GROUP BY e.id, e.name
HAVING COUNT(ep.project_id) > 1;

子查询与连接

sql

-- 子查询与连接结合
-- 查询工资高于部门平均工资的员工
SELECT 
    e.name AS 员工,
    e.salary AS 工资,
    d.name AS 部门,
    dept_avg.avg_salary AS 部门平均工资
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

-- 使用EXISTS检查关联
SELECT 
    e.name AS 员工
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employee_projects ep
    WHERE ep.employee_id = e.id
);
-- 查询有项目的员工

连接查询优化

sql

/*
 * 连接查询优化建议：
 * 
 * 1. 使用索引
 *    - 连接列建立索引
 *    - 外键自动创建索引
 * 
 * 2. 选择合适的连接类型
 *    - 确定需要哪些数据
 *    - 避免不必要的连接
 * 
 * 3. 小表驱动大表
 *    - LEFT JOIN时左表尽量小
 * 
 * 4. 减少返回列
 *    - 只查询需要的列
 *    - 避免SELECT *
 * 
 * 5. 使用EXPLAIN分析
 */

-- 使用EXPLAIN分析查询
EXPLAIN SELECT 
    e.name,
    d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 查看执行计划
EXPLAIN FORMAT=JSON
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

本章小结

本章学习了：

内连接：INNER JOIN，只返回匹配记录
左连接：LEFT JOIN，返回左表所有记录
右连接：RIGHT JOIN，返回右表所有记录
交叉连接：CROSS JOIN，笛卡尔积
自连接：表与自身连接
多表连接：连接多个表查询
连接优化：索引、选择合适连接类型

下一章，我们将学习索引，了解如何提高查询效率。

多表查询 ​

准备数据 ​

内连接 ​

外连接 ​

左外连接（LEFT JOIN） ​

右外连接（RIGHT JOIN） ​

全外连接 ​

交叉连接 ​

自然连接 ​

复杂连接查询 ​

多表连接 ​

连接与聚合 ​

子查询与连接 ​

连接查询优化 ​

本章小结 ​

多表查询

准备数据

内连接

外连接

左外连接（LEFT JOIN）

右外连接（RIGHT JOIN）

全外连接

交叉连接

自然连接

复杂连接查询

多表连接

连接与聚合

子查询与连接

连接查询优化

本章小结