查询的艺术 —— 单表查询与连接查询

学会了数据怎么存、索引怎么建，现在我们来学最实用的技能——怎么高效地把数据取出来。查询是你和MySQL打交道最频繁的操作，也是最能体现功力的地方。

📋 开篇自测：你已经知道多少？

1. SELECT语句的各个子句（WHERE、GROUP BY、HAVING、ORDER BY、LIMIT）的执行顺序是什么？
2. INNER JOIN和LEFT JOIN在结果集上有什么区别？
3. 子查询和JOIN哪个性能更好？为什么？

---

一、SELECT的执行顺序——和书写顺序不一样

我们写SQL时的顺序是：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

但MySQL内部执行的顺序是：

1. FROM        → 确定数据来源（包括JOIN）
2. WHERE       → 对行进行过滤
3. GROUP BY    → 对过滤后的行分组
4. HAVING      → 对分组后的结果过滤
5. SELECT      → 选择要返回的列（计算表达式）
6. DISTINCT    → 去重
7. ORDER BY    → 排序
8. LIMIT       → 限制返回行数

FROM最先执行，SELECT几乎最后才执行。 这个执行顺序决定了很多语法规则。

比如，为什么不能在WHERE中使用列别名？

-- 报错！WHERE在SELECT之前执行，此时还不知道total_price是什么
SELECT price * quantity AS total_price
FROM order_items
WHERE total_price > 100;

-- 正确写法：在WHERE中用原始表达式
SELECT price * quantity AS total_price
FROM order_items
WHERE price * quantity > 100;

-- 或者用HAVING（MySQL扩展行为：允许HAVING引用SELECT别名）
-- 注意：不带GROUP BY的HAVING是MySQL的扩展语法，不推荐在实际项目中使用。
-- 这里仅用于演示执行顺序的概念，PostgreSQL等数据库不支持此写法。
SELECT price * quantity AS total_price
FROM order_items
HAVING total_price > 100;

但ORDER BY可以使用列别名，因为它在SELECT之后执行：

-- 没问题：ORDER BY在SELECT之后
SELECT price * quantity AS total_price
FROM order_items
ORDER BY total_price DESC;

打个比方：SELECT的执行过程就像做菜——先去市场买食材（FROM），挑出新鲜的（WHERE），按类别分堆（GROUP BY），扔掉不合格的堆（HAVING），把食材切好装盘（SELECT），去掉重复的（DISTINCT），按大小排列（ORDER BY），最后只上前三盘（LIMIT）。

🤔 想一想 WHERE和HAVING都是用来过滤的，它们有什么区别？什么情况下必须用HAVING而不能用WHERE？

---

二、单表查询的常用技巧

条件查询进阶

-- 准备示例数据
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(30),
    salary DECIMAL(10,2),
    hire_date DATE,
    manager_id INT
);

INSERT INTO employees (name, department, salary, hire_date, manager_id) VALUES
('张伟', '技术部', 18000.00, '2020-03-15', NULL),
('李娜', '技术部', 15000.00, '2021-06-20', 1),
('王芳', '市场部', 12000.00, '2019-08-10', NULL),
('赵强', '市场部', 13500.00, '2022-01-05', 3),
('孙丽', '技术部', 22000.00, '2018-11-20', 1),
('周杰', '人事部', 11000.00, '2023-02-14', NULL),
('吴敏', '技术部', 16500.00, '2021-09-01', 1),
('郑浩', '市场部', 14000.00, '2020-07-18', 3),
('陈静', '人事部', 10500.00, '2022-05-30', 6),
('刘洋', '技术部', 25000.00, '2017-04-22', NULL);

-- IN：等于多个值中的任意一个
SELECT * FROM employees WHERE department IN ('技术部', '市场部');

-- BETWEEN：范围查询（包含两端）
SELECT * FROM employees WHERE salary BETWEEN 15000 AND 20000;

-- IS NULL / IS NOT NULL：判断空值
SELECT * FROM employees WHERE manager_id IS NULL;

-- LIKE：模糊匹配
SELECT * FROM employees WHERE name LIKE '张%';    -- 以"张"开头
SELECT * FROM employees WHERE name LIKE '%伟';    -- 以"伟"结尾
SELECT * FROM employees WHERE name LIKE '_伟';    -- 名字正好两个字，第二个是"伟"

-- 多条件组合
SELECT * FROM employees
WHERE department = '技术部'
  AND salary > 15000
  AND hire_date >= '2020-01-01';

聚合函数

聚合函数对一组值进行计算，返回单个值：

-- COUNT：计数
SELECT COUNT(*) AS total_employees FROM employees;
SELECT COUNT(manager_id) AS has_manager FROM employees;  -- NULL不计入

-- SUM：求和
SELECT SUM(salary) AS total_salary FROM employees;

-- AVG：平均值
SELECT AVG(salary) AS avg_salary FROM employees;

-- MAX / MIN：最大值 / 最小值
SELECT MAX(salary) AS highest, MIN(salary) AS lowest FROM employees;

-- 配合GROUP BY使用
SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary
FROM employees
GROUP BY department;

GROUP BY + HAVING

-- 找出平均薪资超过15000的部门
SELECT department,
       AVG(salary) AS avg_salary,
       COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING avg_salary > 15000;

-- GROUP BY多列
SELECT department,
       YEAR(hire_date) AS hire_year,
       COUNT(*) AS count
FROM employees
GROUP BY department, YEAR(hire_date)
ORDER BY department, hire_year;

ORDER BY + LIMIT

-- 按薪资降序排列
SELECT name, salary FROM employees ORDER BY salary DESC;

-- 多列排序：先按部门升序，同部门内按薪资降序
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- 分页查询：第2页，每页3条
SELECT * FROM employees ORDER BY id LIMIT 3 OFFSET 3;
-- 等价于 LIMIT 3, 3（第一个参数是offset，第二个是count）

⚠️ 常见误区 误区：LIMIT大偏移量性能没问题。 LIMIT 100000, 10看起来只取10条，但MySQL实际上要先扫描100010条记录再丢掉前100000条。偏移量越大越慢。优化方案是使用”延迟关联”或”游标分页”：

-- 慢：大偏移量
SELECT * FROM employees ORDER BY id LIMIT 100000, 10;

-- 快：利用主键范围
SELECT * FROM employees WHERE id > 100000 ORDER BY id LIMIT 10;

---

三、连接查询——多张表的联姻

现实中的数据往往分散在多张表中。比如订单信息在orders表，用户信息在users表。要同时获取订单和对应的用户信息，就需要连接查询。

-- 准备示例表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    dept_name VARCHAR(30),
    location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, '技术部', '3楼'),
(2, '市场部', '5楼'),
(3, '人事部', '2楼'),
(4, '财务部', '4楼');  -- 注意：没有员工属于财务部

连接的本质——笛卡尔积

两张表连接的最原始形式是笛卡尔积（Cross Join）：把表A的每一行和表B的每一行两两组合。

-- 笛卡尔积：10名员工 × 4个部门 = 40行结果
SELECT e.name, d.dept_name
FROM employees e CROSS JOIN departments d;
-- 结果有40行——每个员工和每个部门都组合了一次

笛卡尔积结果中大部分组合是无意义的。我们需要连接条件来过滤出有意义的组合。

INNER JOIN（内连接）

只返回两张表中匹配上的行。

SELECT e.name, e.salary, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;

结果：
张伟 | 18000 | 技术部 | 3楼
李娜 | 15000 | 技术部 | 3楼
王芳 | 12000 | 市场部 | 5楼
...（所有有对应部门的员工）

注意：财务部没有员工，所以财务部不出现在结果中。

把内连接想象成两个集合的交集——只有在两张表中都能找到匹配的行才会出现在结果中。

LEFT JOIN（左外连接）

返回左表的所有行，即使右表中没有匹配的行。无匹配时右表的列填NULL。

SELECT d.dept_name, d.location, e.name, e.salary
FROM departments d
LEFT JOIN employees e ON d.dept_name = e.department;

结果：
技术部 | 3楼 | 张伟 | 18000
技术部 | 3楼 | 李娜 | 15000
市场部 | 5楼 | 王芳 | 12000
人事部 | 2楼 | 周杰 | 11000
财务部 | 4楼 | NULL | NULL     ← 财务部没有员工，但仍然出现

LEFT JOIN保证了左表（departments）的每一行都至少出现一次。

RIGHT JOIN（右外连接）

和LEFT JOIN相反，保证右表的所有行都出现。实际开发中用得比较少，因为可以通过调换表的顺序用LEFT JOIN替代。

-- 这两个查询效果一样
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;
SELECT * FROM B RIGHT JOIN A ON A.id = B.a_id;

自连接

一张表和自己连接，用来处理层级关系（如员工和经理的关系）。

-- 查询每个员工和他的经理名字
SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

结果：
张伟 | NULL       ← 张伟没有经理
李娜 | 张伟
王芳 | NULL
赵强 | 王芳
孙丽 | 张伟
...

🤔 想一想 如果employees表有1万行，departments表有10行，做INNER JOIN时MySQL是怎样执行的？是先把两张表的所有行组合（10万行笛卡尔积）再过滤，还是有更聪明的做法？

---

四、连接的执行原理——嵌套循环

理解连接查询的性能，需要了解MySQL底层是怎么执行连接的。

简单嵌套循环（Simple Nested-Loop Join）

最朴素的方式：对于驱动表的每一行，都扫描被驱动表的全部数据来找匹配。

for each row in 驱动表 {
    for each row in 被驱动表 {
        if 满足连接条件 {
            输出这对行
        }
    }
}

如果驱动表有M行，被驱动表有N行，这个算法需要M×N次比较。如果M=10000，N=10000，就是1亿次比较——太慢了。

索引嵌套循环（Index Nested-Loop Join）

如果被驱动表的连接列上有索引，就不需要全表扫描了：

for each row in 驱动表 {
    通过索引在被驱动表中查找匹配行  // O(log N)
}

M × log(N)次比较，快了很多。这就是为什么连接列上的索引非常重要。

-- 确保连接列上有索引
CREATE INDEX idx_dept ON employees(department);

-- 这个JOIN就能利用索引了
SELECT e.name, d.dept_name
FROM departments d
INNER JOIN employees e ON d.dept_name = e.department;

块嵌套循环（Block Nested-Loop Join）

如果没有可用的索引，MySQL会使用”块”优化：不是逐行地去扫描被驱动表，而是把驱动表的多行数据一起放入连接缓冲区（Join Buffer），然后扫描一次被驱动表时同时和缓冲区中的多行比较。

-- 查看Join Buffer大小
SHOW VARIABLES LIKE 'join_buffer_size';
-- 默认值通常是256KB

MySQL 8.0.18之后引入了Hash Join，对于没有索引的等值连接，性能更好。

驱动表的选择

优化器通常会选择较小的表作为驱动表。因为驱动表需要全表扫描，被驱动表可以利用索引。小表驱动大表的循环次数更少。

-- 优化器通常会选择departments（4行）作为驱动表
-- 而不是employees（10行）
SELECT *
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;

你可以用EXPLAIN查看MySQL的连接执行计划：

EXPLAIN SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;

输出中第一行就是驱动表。

⚠️ 常见误区 误区一：写在FROM后面的表就是驱动表。 MySQL优化器会自动选择最优的连接顺序，不一定按你SQL中写的顺序。你可以用STRAIGHT_JOIN来强制指定连接顺序，但通常不建议这么做，让优化器自己决定。

误区二：子查询一定比JOIN慢。 在MySQL 5.6以后，优化器对子查询做了很多优化（比如将某些子查询转换为半连接），性能差距已经不像以前那么大了。但总体而言，对于大部分场景，JOIN的可读性和性能表现更稳定。

---

五、子查询——查询中的查询

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

标量子查询（返回单个值）

-- 查询薪资高于平均值的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

列子查询（返回一列多行）

-- 查询技术部和市场部的所有员工
SELECT * FROM employees
WHERE department IN (
    SELECT dept_name FROM departments
    WHERE location IN ('3楼', '5楼')
);

行子查询（返回一行多列）

-- 查找和"张伟"同部门同年入职的员工
SELECT * FROM employees
WHERE (department, YEAR(hire_date)) = (
    SELECT department, YEAR(hire_date)
    FROM employees
    WHERE name = '张伟'
);

表子查询（返回多行多列）

-- 查询每个部门薪资最高的员工
SELECT e.*
FROM employees e
INNER JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
) AS dept_max ON e.department = dept_max.department
                AND e.salary = dept_max.max_salary;

EXISTS子查询

-- 查询有员工的部门
SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department = d.dept_name
);

EXISTS只关心子查询是否能返回结果，不关心返回什么。只要有行返回就是TRUE。

🤔 想一想 IN和EXISTS在语义上可以互换，但在性能上有区别吗？当外表很大、内表很小时，应该用哪个？反过来呢？

---

六、实用查询模式

排名查询（窗口函数，MySQL 8.0+）

-- 给每个部门的员工按薪资排名
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_in_dept,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

三种排名函数的区别：

• RANK()：并列排名后跳号（1, 2, 2, 4）
• DENSE_RANK()：并列排名不跳号（1, 2, 2, 3）
• ROW_NUMBER()：不并列（1, 2, 3, 4）

前后行对比（LAG / LEAD）

LAG()和LEAD()可以访问当前行的前一行或后一行数据，非常适合做环比分析、趋势对比等场景。

-- 对比每位员工和入职时间相邻的前一位员工的薪资差异
SELECT name, hire_date, salary,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
       salary - LAG(salary, 1) OVER (ORDER BY hire_date) AS salary_diff
FROM employees
ORDER BY hire_date;

结果示例：
刘洋 | 2017-04-22 | 25000 | NULL  | NULL     ← 第一行没有前一行
孙丽 | 2018-11-20 | 22000 | 25000 | -3000
王芳 | 2019-08-10 | 12000 | 22000 | -10000
张伟 | 2020-03-15 | 18000 | 12000 | 6000
...

• LAG(column, N)：取当前行往前第N行的值（默认N=1）
• LEAD(column, N)：取当前行往后第N行的值（默认N=1）

-- 查看每位员工和同部门中下一位入职员工的薪资对比
SELECT name, department, hire_date, salary,
       LEAD(name, 1) OVER (PARTITION BY department ORDER BY hire_date) AS next_hire,
       LEAD(salary, 1) OVER (PARTITION BY department ORDER BY hire_date) AS next_salary
FROM employees;

行转列

-- 将每个部门的人数做成一行展示
SELECT
    SUM(CASE WHEN department = '技术部' THEN 1 ELSE 0 END) AS tech_count,
    SUM(CASE WHEN department = '市场部' THEN 1 ELSE 0 END) AS market_count,
    SUM(CASE WHEN department = '人事部' THEN 1 ELSE 0 END) AS hr_count
FROM employees;

累计聚合（SUM/AVG/COUNT OVER）

窗口函数不仅可以做排名，还可以做累计聚合——在不折叠行的前提下，计算到当前行为止的聚合值。

-- 按入职日期计算薪资的累计值
SELECT name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary
FROM employees
ORDER BY hire_date;

结果示例：
刘洋 | 2017-04-22 | 25000 | 25000      ← 第一人
孙丽 | 2018-11-20 | 22000 | 47000      ← 25000+22000
王芳 | 2019-08-10 | 12000 | 59000      ← 47000+12000
张伟 | 2020-03-15 | 18000 | 77000
...

你还可以在分组内做累计聚合，以及用AVG计算移动平均：

-- 每个部门内部按入职顺序累计人数和累计薪资
SELECT name, department, hire_date, salary,
       COUNT(*) OVER (PARTITION BY department ORDER BY hire_date) AS dept_cum_count,
       SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS dept_cum_salary
FROM employees
ORDER BY department, hire_date;

-- 计算截至当前行的整体平均薪资（滚动平均）
SELECT name, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date) AS running_avg
FROM employees
ORDER BY hire_date;

---

📝 掌握度自测

1. SELECT语句中各子句的执行顺序是什么？为什么WHERE中不能使用列别名？
2. INNER JOIN、LEFT JOIN的区别是什么？请用集合的概念解释。
3. MySQL执行连接查询时，底层使用了什么算法？为什么连接列上的索引很重要？
4. 什么时候用EXISTS？什么时候用IN？它们在性能上的差异是什么？
5. LIMIT 100000, 10为什么慢？如何优化深度分页？

💡 自我评估

• 答对5题：查询功力扎实，可以应对大部分业务查询需求
• 答对3-4题：基础到位，建议在真实数据上多练习连接查询和子查询
• 答对0-2题：查询是MySQL最核心的技能，建议用本章的示例数据动手练习每一个SQL