MySQL 15个经典练习与深度解析

发布于 2025-09-23 分类: 数据库

引言

掌握SQL（结构化查询语言）的唯一途径就是不断练习。理论知识固然重要，但只有在实际操作中，你才能真正理解查询的细微差别、性能的影响以及如何规避常见的错误。

本教程我们将一起深入探讨每个查询背后的逻辑、SQL的执行顺序、如何优化查询性能，以及初学者最容易掉入的陷阱。无论你是数据库新手，还是希望巩固基础的开发者，这15个练习都将是你的宝贵财富。

我们将分三个核心部分进行：

单表查询：数据操作的基石。
数据排序与分组：从数据中提炼商业洞见。
多表连接：构建数据的关系网络。

让我们开始吧！

第一部分：单表查询——数据筛选的基础

单表查询是所有复杂查询的起点。它教会我们如何从一个数据表中精确地提取所需信息。核心语法结构是 SELECT ... FROM ... WHERE ...。

数据集介绍: `cat` 表

我们将使用一个名为 cat 的表，它记录了猫咪的信息。

id: 猫的唯一ID
name: 名字
breed: 品种 (如 Siamese, Ragdoll)
coloration: 毛色
age: 年龄
sex: 性别
fav_toy: 最喜欢的玩具

练习 1: 列出所有猫咪信息

需求： 从 cat 表中查询所有数据。

解决方案：

SELECT * FROM cat;

深度解析：

SELECT *: 这里的 * 是一个通配符，代表“所有列”。它会按照表中定义的列顺序返回所有列的数据。
FROM cat: 指定了数据来源是 cat 这张表。

性能优化提示：
在生产环境中，强烈建议避免使用 SELECT *。原因如下：

资源浪费：你可能只需要2个列，但查询会返回所有10个列，这增加了网络传输的负担和数据库的I/O开销。
可读性与维护性差：代码无法清晰地表明需要哪些数据。如果未来表中增加了新的列，查询结果也会意外地改变，可能导致应用程序出错。
无法利用覆盖索引：当查询所需的所有列都包含在某个索引中时，数据库可以直接从索引中获取数据，无需回表查询，这被称为“覆盖索引”（Covering Index）。使用 SELECT * 会使这种优化几乎不可能实现。

最佳实践： 明确列出你需要的列名。例如：SELECT id, name, breed FROM cat;

练习 2: 筛选年轻的猫

需求： 查询年龄小于5岁的猫的名字、品种和毛色。

解决方案：

SELECT name, breed, coloration
FROM cat
WHERE age < 5;

深度解析：

WHERE age < 5: WHERE子句用于过滤行。这里，它只保留 age 列的值小于5的记录。

性能优化提示：
WHERE 子句是性能优化的关键。如果 age 列上有一个索引，数据库可以非常高效地定位到符合条件的行，而无需扫描整个表（全表扫描）。对于数值、日期等范围查询（<、>、BETWEEN），索引效果显著。

练习 3: 按品种精确查找

需求： 查询品种为 'Ragdoll' (布偶猫) 的猫的ID和名字。

解决方案：

SELECT id, name
FROM cat
WHERE breed = 'Ragdoll';

深度解析：

WHERE breed = 'Ragdoll': 这里使用了等值比较。注意，在SQL中，字符串常量需要用单引号 ' 括起来。

常见陷阱：

大小写敏感性：在MySQL中，字符串比较是否区分大小写取决于列的“校对规则”（Collation）。默认的校对规则（如 utf8mb4_general_ci）是不区分大小写的（ci 代表 case-insensitive）。这意味着 WHERE breed = 'ragdoll' 也能得到相同结果。但在其他数据库（如PostgreSQL）或某些特定校对规则下，比较是区分大小写的。请务必了解你的数据库设置。
数值与字符串：WHERE id = 5 和 WHERE id = '5' 在某些情况下可能因为隐式类型转换而得到相同结果，但这依赖于数据库的行为，是不良实践。请始终确保比较的值类型与列的数据类型匹配。

练习 4: 多条件模糊查询

需求： 查询符合以下所有条件的猫的全部信息：

品种（breed）以 'R' 开头。
最喜欢的玩具（fav_toy）以 'ball' 开头。
毛色（coloration）以 'm' 结尾。

解决方案：

SELECT *
FROM cat
WHERE
    breed LIKE 'R%'
    AND fav_toy LIKE 'ball%'
    AND coloration LIKE '%m';

深度解析：

LIKE: 用于字符串的模式匹配。
%: LIKE 操作符中的通配符，代表零个、一个或多个任意字符。
'R%': 匹配以 'R' 开头的任何字符串。
'%m': 匹配以 'm' 结尾的任何字符串。
AND: 逻辑运算符，用于连接多个条件，表示所有条件都必须为真。

性能优化提示：
LIKE 的性能与通配符的位置密切相关。

LIKE 'R%' (前缀匹配)：这种模式是 SARGable (Search ARGument Able)，意味着它可以有效利用 breed 列上的B-Tree索引。数据库可以快速定位到索引中以 'R' 开头的条目。
LIKE '%m' (后缀匹配)：这种模式是 non-SARGable。标准的B-Tree索引无法用于后缀或中缀匹配，因为索引是按从左到右的顺序组织的。这将导致全表扫描，在大数据量下性能极差。如果需要频繁进行这类查询，可以考虑使用全文索引（Full-Text Index）或反向索引等技术。

练习 5: 复杂的组合条件查询

需求： 查询符合以下所有条件的猫的全部信息：

年龄大于10岁。
品种是 'Ragdoll' 或 'Abyssinian'。
有明确记录最喜欢的玩具（即该字段不为NULL）。

解决方案：

SELECT *
FROM cat
WHERE
    age > 10
    AND (breed = 'Ragdoll' OR breed = 'Abyssinian')
    AND fav_toy IS NOT NULL;

深度解析：

OR: 逻辑运算符，表示只需满足其中一个条件。
括号 () 的重要性：AND 的优先级高于 OR。如果不加括号，查询会被解释为 (age > 10 AND breed = 'Ragdoll') OR breed = 'Abyssinian'，这显然不符合我们的需求。使用括号可以明确指定逻辑运算的顺序。
IS NOT NULL: 这是判断一个列的值是否为 NULL 的标准方式。

常见陷阱：
绝对不要使用 = NULL 或 <> NULL。NULL 代表“未知”或“不存在”，它不是一个具体的值。任何与 NULL 进行的常规比较（=、!=、> 等）结果都是 NULL（可以理解为“未知”），WHERE 子句会将其视为 FALSE。正确的语法是 IS NULL 和 IS NOT NULL。

第二部分：数据排序与分组——从数据中提炼洞见

筛选出数据后，我们往往需要对其进行排序、汇总和分组，以便进行统计分析。

数据集介绍: `games` 表

id: 游戏ID
title: 标题
company: 公司
type: 类型 (RPG, Action, etc.)
production_year: 发行年份
production_cost: 制作成本
revenue: 收入
rating: 评分

练习 6: 多重条件排序

需求： 查询所有游戏信息。首先按制作成本从低到高排序；如果成本相同，则按评分从高到低排序。

解决方案：

SELECT *
FROM games
ORDER BY production_cost ASC, rating DESC;

深度解析：

ORDER BY: 用于对结果集进行排序。
ASC: 升序（Ascending），是默认值，可以省略。
DESC: 降序（Descending）。
production_cost ASC, rating DESC: 定义了两个排序规则。数据库会首先按 production_cost 升序排列所有行。当遇到 production_cost 相同的行时，再在这些行内部按 rating 降序排列。

性能优化提示：
为了优化排序，可以在排序列上创建索引。对于多列排序，创建一个复合索引 (production_cost, rating) 会非常高效。如果索引的排序方向（ASC/DESC）与查询完全匹配，数据库可以直接按索引顺序读取数据，避免昂贵的“文件排序”（filesort）操作。

练习 7: 筛选后排序

需求： 查询在2010年至2015年间制作、且评分高于7的游戏的制作成本。结果按成本从低到高排序。

解决方案：

SELECT production_cost
FROM games
WHERE
    production_year BETWEEN 2010 AND 2015
    AND rating > 7
ORDER BY production_cost ASC;

深度解析：

BETWEEN ... AND ...: 一个方便的范围操作符，它等价于 production_year >= 2010 AND production_year <= 2015。它是包含边界值的。
SQL查询的逻辑执行顺序：理解这个顺序至关重要。
1. FROM: 确定操作的表。
2. WHERE: 过滤行。
3. SELECT: 选择要返回的列。
4. ORDER BY: 对最终结果进行排序。
  这意味着排序操作是在所有过滤完成之后才进行的。

练习 8: 按年份进行分组统计

需求： 按年份统计：

该年份发行的游戏总数（列名为 count）。
该年份游戏的平均制作成本（列名为 avg_cost）。
该年份游戏的平均收入（列名为 avg_revenue）。

解决方案：

SELECT
    production_year,
    COUNT(*) AS count,
    AVG(production_cost) AS avg_cost,
    AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

深度解析：

聚合函数：COUNT(), AVG(), SUM(), MAX(), MIN() 等函数会作用于一组行并返回单个值。
GROUP BY production_year: 这个子句是核心。它将所有具有相同 production_year 值的行合并成一个“组”，然后聚合函数会对每个组进行计算。
AS: 用于给列或表达式起一个别名（Alias），使结果更具可读性。

常见陷阱：
SELECT 列表规则：当使用 GROUP BY 时，SELECT 列表中只能包含：

GROUP BY 子句中出现的列（如 production_year）。
聚合函数（如 COUNT(*)）。
常量。
如果你尝试 SELECT title, COUNT(*) FROM games GROUP BY production_year;，就会报错（在标准的SQL模式下）。因为对于每个年份分组，存在多个 title，数据库不知道该显示哪一个。

练习 9: 多列分组

需求： 统计每个公司制作的每种类型的游戏中，有多少是盈利的（收入 > 制作成本）。显示公司、类型和盈利游戏数量。

解决方案：

SELECT
    company,
    type,
    COUNT(*) AS number_of_games
FROM games
WHERE revenue > production_cost
GROUP BY company, type;

深度解析：

GROUP BY company, type: 这里按两个列进行分组。数据库会为 (company, type) 的每个唯一组合创建一个分组。例如，("EA", "RPG") 是一个组，("EA", "Action") 是另一个组。

练习 10: 分组后筛选

需求： 统计每个公司的游戏总数和平均制作成本，但只显示那些制作了超过一款游戏的公司。

解决方案：

SELECT
    company,
    COUNT(company) AS number_of_games,
    AVG(production_cost) AS avg_cost
FROM games
GROUP BY company
HAVING COUNT(company) > 1;

深度解析：

HAVING: 这个子句专门用于在 GROUP BY 之后对分组结果进行过滤。
WHERE vs HAVING：
- WHERE 在分组前作用于单行数据。
- HAVING 在分组后作用于聚合结果。
  你可以这样记：WHERE 是“入场券”，HAVING 是“分组后的VIP筛选”。在本例中，我们必须先计算出 COUNT(company)，然后才能判断它是否大于1，所以必须用 HAVING。

COUNT(*) vs COUNT(column):

COUNT(*): 统计所有行的数量，不管任何列的值是什么。这是最常用、通常也是最优化的方式。
COUNT(column): 只统计指定列中值不为 NULL 的行的数量。在本例中，因为 company 列不可能为 NULL，所以 COUNT(company) 和 COUNT(*) 结果相同。但如果是在一个可能为 NULL 的列上计数，两者就有本质区别。

第三部分：多表连接——构建数据的关系网络

现实世界的数据库很少只有一个表。连接（JOIN）是将多个表中的数据根据相关联的列组合在一起的关键操作。

数据集介绍: `employees`

employee 表:
- id: 员工ID
- first_name, last_name: 姓名
- salary: 薪水
- manager_id: 该员工上级的ID (关联回 employee 表的 id)
salgrade 表:
- grade: 薪资等级
- lower_limit, upper_limit: 该等级的薪资上下限
benefits 表:
- salary_req: 获得该福利所需的最低薪水
- benefit_name: 福利名称

可视化JOIN类型

在开始之前，我们用Mermaid图来理解两种最核心的JOIN：

graph TD
    subgraph "INNER JOIN (交集)"
        A((Table A))
        B((Table B))
        A -- ON A.key = B.key --> C{Matched Rows}
        B -- ON A.key = B.key --> C
    end

    subgraph "LEFT JOIN (左连接)"
        D((Table A - Left))
        E((Table B - Right))
        D -- ON D.key = E.key --> F{All Rows from A}
        E -- Matched --> F
        style D fill:#cde,stroke:#333
    end

INNER JOIN: 只返回两个表中连接键能匹配上的行。
LEFT JOIN: 返回左表的所有行，以及右表中能匹配上的行。如果右表没有匹配行，则右表的列显示为 NULL。

练习 11: 非等值连接

需求： 查询每位员工的姓名、薪水及其对应的薪资等级。

解决方案：

SELECT
    e.first_name,
    e.last_name,
    e.salary,
    sg.grade
FROM
    employee AS e
JOIN
    salgrade AS sg ON e.salary BETWEEN sg.lower_limit AND sg.upper_limit;

深度解析：

表别名 AS: employee AS e 为 employee 表起了个简称 e。这在多表连接中非常重要，可以使代码更简洁，并避免列名冲突。AS 关键字可以省略，写成 employee e。
JOIN ... ON ...: 这是现代SQL推荐的连接语法。JOIN 指定要连接的表，ON 指定连接条件。它比旧的 FROM table1, table2 WHERE table1.id = table2.id 语法更清晰，能将“连接条件”和“过滤条件”分离开。
非等值连接: 这里的连接条件 ON e.salary BETWEEN sg.lower_limit AND sg.upper_limit 不是简单的相等比较，而是一个范围判断。

性能优化提示：
非等值连接的优化比等值连接复杂。尽管如此，在 salgrade 表的 lower_limit 和 upper_limit 上建立索引仍然可能带来性能提升。

练习 12: 自连接 (Self-Join)

需求： 显示每位员工的姓名、薪水，以及其直属上级的姓名。只列出有上级的员工。

解决方案：

SELECT
    e.first_name   AS employee_first_name,
    e.last_name    AS employee_last_name,
    e.salary       AS employee_salary,
    m.first_name   AS manager_first_name,
    m.last_name    AS manager_last_name
FROM
    employee AS e
JOIN
    employee AS m ON e.manager_id = m.id;

深度解析：

自连接的核心思想: 我们需要将 employee 表“一分为二”，在概念上把它看作两张独立的表：一张是“员工表”（别名为 e），另一张是“经理表”（别名为 m）。
ON e.manager_id = m.id: 这就是连接的桥梁。它表示“员工表”中的 manager_id 必须等于“经理表”中的 id。通过这个条件，我们就把员工和他们的经理关联在了一起。
这是一个 INNER JOIN，所以它只会返回 e.manager_id 不为 NULL 且能在 m 表中找到对应 id 的员工，这正好满足了“只列出有上级的员工”的需求。

练习 13: 多表连接与过滤

需求： 查询ID为5的员工可以获得的所有福利。显示该员工的姓名和福利名称。

解决方案：

SELECT
    e.first_name,
    e.last_name,
    b.benefit_name
FROM
    employee AS e
JOIN
    benefits AS b ON e.salary >= b.salary_req
WHERE
    e.id = 5;

深度解析：

ON e.salary >= b.salary_req: 这是另一个非等值连接。一个员工可以获得所有薪水要求低于或等于其当前薪水的福利，所以这是一个一对多的关系。
WHERE e.id = 5: WHERE 子句在 JOIN 操作之后执行，用于从连接后的巨大结果集中筛选出我们感兴趣的特定员工的记录。

性能优化提示：

先过滤后连接：虽然逻辑上 WHERE 在 JOIN 后执行，但现代数据库的查询优化器非常智能。它可能会重写查询，先对 employee 表应用 WHERE e.id = 5 （如果 id 是主键，这会极快地返回一行），然后再用这一行数据去连接 benefits 表。这比先连接两个大表再过滤要高效得多。
索引：employee.id（主键，自带索引）、employee.salary 和 benefits.salary_req 上的索引对性能至关重要。

练习 14: 连接与分组聚合

需求： 统计每项福利分别有多少名员工可以获得。不显示无人获得的福利。

解决方案：

SELECT
    b.benefit_name,
    COUNT(e.id) AS employee_count
FROM
    benefits AS b
JOIN
    employee AS e ON b.salary_req <= e.salary
GROUP BY
    b.benefit_name;

深度解析：
这个查询完美地结合了JOIN和GROUP BY。

FROM ... JOIN ... ON ...: 首先，通过薪水条件将 benefits 和 employee 表连接起来，生成一个临时的、巨大的结果集，其中每一行代表一个员工获得的一项福利。
GROUP BY b.benefit_name: 然后，根据福利名称对这个结果集进行分组。
SELECT b.benefit_name, COUNT(e.id): 最后，对每个福利分组，计算该组内有多少个员工ID（即有多少员工），从而得出结果。

练习 15: 外连接 (Outer Join) 的妙用

需求： 统计每项福利分别有多少名员工可以获得。这次，即使没有任何员工可以获得某项福利，也要显示出来，其员工计数为0。

解决方案：

SELECT
    b.benefit_name,
    COUNT(e.id) AS employee_count
FROM
    benefits AS b
LEFT JOIN
    employee AS e ON b.salary_req <= e.salary
GROUP BY
    b.benefit_name
HAVING COUNT(e.id) = 0; -- 只显示0的

为了完整性，我们先讨论不带HAVING的情况，因为它更能体现LEFT JOIN的用途。

深度解析：

LEFT JOIN 的关键作用: 与 INNER JOIN 不同，LEFT JOIN 会保证左表 (benefits) 中的所有行都出现在最终结果里。
当连接不成功时：对于某项福利（例如一项薪水要求极高的福利），如果在 employee 表中找不到任何一个员工的薪水满足 b.salary_req <= e.salary，那么在连接结果中，该福利所对应的所有 employee 表的列（如 e.id, e.first_name等）都会被填充为 NULL。
COUNT(e.id) 的行为: COUNT(column) 函数会忽略 NULL 值。因此，对于那些没有任何员工能获得的福利，COUNT(e.id) 的结果自然就是 0。这就是我们能够统计到0的方法。

引言

第一部分：单表查询——数据筛选的基础

数据集介绍: cat 表

练习 1: 列出所有猫咪信息

练习 2: 筛选年轻的猫

练习 3: 按品种精确查找

练习 4: 多条件模糊查询

练习 5: 复杂的组合条件查询

第二部分：数据排序与分组——从数据中提炼洞见

数据集介绍: games 表

练习 6: 多重条件排序

练习 7: 筛选后排序

练习 8: 按年份进行分组统计

练习 9: 多列分组

练习 10: 分组后筛选

第三部分：多表连接——构建数据的关系网络

数据集介绍: employees

可视化JOIN类型

练习 11: 非等值连接

练习 12: 自连接 (Self-Join)

练习 13: 多表连接与过滤

练习 14: 连接与分组聚合

练习 15: 外连接 (Outer Join) 的妙用

数据集介绍: `cat` 表

数据集介绍: `games` 表

数据集介绍: `employees`