这份笔记按 SQL之母第 1~22 关 的顺序记录。复习时先看每节第一句话,再看 SQL,尽量能自己说出“查什么、从哪查、怎么筛选”。
例子统一使用学生表和班级表,方便看清相邻语法的区别。时间和字符串函数沿用练习环境的 SQLite 写法。
先记住两张表
student:一行代表一名学生的一次考试记录,本例中每名学生只出现一次。
| id | name | age | class_id | exam_num | score |
|---|---|---|---|---|---|
| 1 | 林舟 | 18 | 101 | 1 | 92 |
| 2 | 许宁 | 20 | 101 | 1 | 85 |
| 3 | 陈语 | 19 | 102 | 1 | 76 |
| 4 | 周野 | 18 | 102 | 2 | NULL |
| 5 | 林溪 | 22 | 999 | 1 | 92 |
| 6 | 唐可 | 20 | NULL | 2 | 60 |
class:班级编号 id 唯一。
| id | name | level |
|---|---|---|
| 101 | 一班 | 基础 |
| 102 | 二班 | 基础 |
| 103 | 三班 | 进阶 |
这里特意保留了空成绩、不存在的班级编号、没有班级的学生,以及没有学生的班级,后面用来观察 NULL 和关联查询。
想实际运行:在空的 SQLite 练习库中初始化这组数据
CREATE TABLE student (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
class_id INTEGER,
exam_num INTEGER,
score INTEGER
);
CREATE TABLE class (
id INTEGER PRIMARY KEY,
name TEXT,
level TEXT
);
INSERT INTO student (id, name, age, class_id, exam_num, score) VALUES
(1, '林舟', 18, 101, 1, 92),
(2, '许宁', 20, 101, 1, 85),
(3, '陈语', 19, 102, 1, 76),
(4, '周野', 18, 102, 2, NULL),
(5, '林溪', 22, 999, 1, 92),
(6, '唐可', 20, NULL, 2, 60);
INSERT INTO class (id, name, level) VALUES
(101, '一班', '基础'),
(102, '二班', '基础'),
(103, '三班', '进阶');
第 1~4 关:决定查哪些列
1. 全表查询:SELECT *
SELECT 决定返回哪些列,FROM 决定从哪张表查。
SELECT *
FROM student;
* 表示所有列。这里没有筛选条件,因此返回所有学生记录;没有指定排序时,不能依赖结果的先后顺序。
2. 选择查询:写出需要的列
SELECT name, score
FROM student;
只返回姓名、成绩两列。选列不会减少记录数,本例仍有 6 行;要筛选行,需要后面的 WHERE。
多个字段用逗号分隔,最后一个字段后面不加逗号。
3. 别名:AS
给结果列起一个更清楚的名字,不会修改表中的字段名。
SELECT name AS student_name, score AS student_score
FROM student;
返回列名变成 student_name、student_score。题目要求什么列名,就用 AS 对齐。
表也能起别名,后面的多表查询会用到:
SELECT s.name, s.score
FROM student AS s;
s.name 表示学生表的 name。字符串值用单引号,例如 '林舟';中文列别名可以写成 AS "姓名"。
4. 常量和运算:查询时计算一列
SELECT name, score, score * 2 AS double_score, '分' AS unit
FROM student;
score * 2 根据每行的成绩计算,'分' 是每行都相同的常量。这里只生成查询结果,不会把原成绩乘二后写回数据库。
常用运算符是 +、-、*、/。遇到空成绩时,score * 2 仍是 NULL。
第 5~9 关:决定留下哪些行
5. WHERE:按条件筛选
SELECT name, score
FROM student
WHERE score >= 85;
只保留成绩不低于 85 的记录:林舟、许宁、林溪。
记忆方式:SELECT 选列,WHERE 筛行。
6. 运算符:比较和范围
| 写法 | 含义 |
|---|---|
= | 等于 |
<> 或 != | 不等于 |
>、< | 大于、小于 |
>=、<= | 大于等于、小于等于 |
BETWEEN a AND b | 在 a 到 b 之间,包含两端 |
-- 姓名不是林舟
SELECT name, age
FROM student
WHERE name <> '林舟';
-- 年龄在 18~20 岁之间,18 和 20 也算
SELECT name, age
FROM student
WHERE age BETWEEN 18 AND 20;
SQL 判断相等用 =,不要写成 JavaScript 里的 == 或 ===。
7. 空值:IS NULL / IS NOT NULL
NULL 表示缺失或未知,和 0、空字符串 '' 都不同。
-- 没有成绩的学生:周野
SELECT name, score
FROM student
WHERE score IS NULL;
-- 已有成绩的学生
SELECT name, score
FROM student
WHERE score IS NOT NULL;
不要用 score = NULL 判断空值。普通比较遇到 NULL 通常得到“未知”,WHERE 只保留条件为真的记录,所以 score <> 60 也不会选出空成绩。
8. 模糊查询:LIKE / NOT LIKE
LIKE 按文本模式匹配,% 匹配零个或多个字符,_ 匹配一个字符。
| 条件 | 含义 |
|---|---|
name LIKE '林%' | 以“林”开头 |
name LIKE '%宁' | 以“宁”结尾 |
name LIKE '%语%' | 包含“语” |
name LIKE '林_' | “林”后面恰好一个字符 |
name NOT LIKE '%林%' | 不包含“林” |
SELECT name, score
FROM student
WHERE name LIKE '林%';
返回林舟、林溪。不要把 % 当成普通文本,也不要把 _ 当成任意长度。
9. 逻辑运算:AND / OR / NOT
AND 要求条件同时成立,OR 要求至少一个成立,NOT 对条件取反。
-- 姓名以“林”开头,并且成绩至少 90
SELECT name, score
FROM student
WHERE name LIKE '林%' AND score >= 90;
-- 18 岁或 20 岁,并且成绩至少 80
SELECT name, age, score
FROM student
WHERE (age = 18 OR age = 20) AND score >= 80;
优先级是 NOT → AND → OR。混用时把业务上属于一组的条件加上括号,避免把“年龄满足且成绩满足”写成另一层意思。
第 10~13 关:整理返回结果
10. 去重:DISTINCT
对所选列组成的结果去重。
SELECT DISTINCT age
FROM student;
年龄 18、19、20、22 各出现一次,顺序未指定。
SELECT DISTINCT class_id, exam_num
FROM student;
多个字段时,按 字段组合 去重:同一个班级的第 1 场和第 2 场考试是不同组合。
11. 排序:ORDER BY
SELECT id, name, score
FROM student
WHERE score IS NOT NULL
ORDER BY score DESC, id ASC;
ASC 升序,DESC 降序,默认是升序。多个排序字段从左到右比较:先看成绩,成绩相同时再看学号。
本例前两名是同为 92 分的林舟、林溪,因为学号 1 排在 5 前面。需要稳定顺序时,最后补一个唯一字段。
12. 截断和偏移:LIMIT / OFFSET
LIMIT 表示最多取多少行,OFFSET 表示先跳过多少行。
SELECT name, age
FROM student
ORDER BY age ASC, id ASC
LIMIT 3 OFFSET 1;
先排序,跳过第 1 行,再取 3 行,结果是周野、陈语、许宁。
练习中也会遇到下面的等价写法,逗号前是偏移量,后是条数:
SELECT name, age
FROM student
ORDER BY age ASC, id ASC
LIMIT 1, 3;
偏移量从 0 开始,第 1 页跳过 0 行,第 2 页跳过一页的行数。分页前要先明确排序。SQLite 的 LIMIT 说明
13. 条件分支:CASE WHEN
根据条件返回不同的值,相当于查询中的 if / else if / else。
SELECT
name,
score,
CASE
WHEN score IS NULL THEN '未出分'
WHEN score >= 90 THEN '优秀'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS score_level
FROM student;
从上往下判断,遇到第一个成立的 WHEN 就采用对应结果。因此 >= 90 要放在 >= 60 前面,否则 92 分也会被判为“及格”。
结尾别漏掉 END;不写 ELSE 且没有条件命中时,结果是 NULL。
第 14~16 关:函数
14. 时间函数:DATE / DATETIME / TIME
在 SQLite 中,这三个函数分别返回日期、日期时间、时间文本。
SELECT
DATE() AS today,
DATETIME() AS current_datetime,
TIME() AS current_time;
-- 给每个学生的查询结果带上当前日期
SELECT name, DATE() AS "当前日期"
FROM student;
| 函数 | 返回格式 |
|---|---|
DATE() | YYYY-MM-DD |
DATETIME() | YYYY-MM-DD HH:MM:SS |
TIME() | HH:MM:SS |
不传参数时默认取当前时间,按 UTC 处理;需要运行环境的本地时间,可以写 DATETIME('now', 'localtime')。这不是 MySQL 时间函数的通用写法。SQLite 时间函数说明
15. 字符串处理:UPPER / LOWER / LENGTH
SELECT
UPPER('SqlNote') AS upper_name,
LOWER('SqlNote') AS lower_name,
LENGTH('学习SQL') AS name_length;
返回 SQLNOTE、sqlnote、5。
SELECT name, UPPER(name) AS upper_name, LENGTH(name) AS name_length
FROM student;
UPPER 转大写,LOWER 转小写;中文本身没有大小写之分。SQLite 的 LENGTH 对普通文本按字符计数,例如“林舟”是 2;内置大小写转换主要处理 ASCII 字母。SQLite 字符串函数说明
16. 聚合函数:把多行汇总成一个结果
| 函数 | 含义 | 对 NULL 的处理 |
|---|---|---|
COUNT(*) | 统计行数 | 包含字段为空的行 |
COUNT(score) | 统计成绩非空的行数 | 忽略空成绩 |
COUNT(DISTINCT class_id) | 统计不同的非空班级编号数 | 忽略空班级编号 |
SUM(score) | 总分 | 忽略空成绩 |
AVG(score) | 平均分 | 忽略空成绩 |
MAX(score) / MIN(score) | 最高分 / 最低分 | 忽略空成绩 |
SELECT
COUNT(*) AS total_num,
COUNT(score) AS scored_num,
SUM(score) AS total_score,
AVG(score) AS avg_score,
MAX(score) AS max_score,
MIN(score) AS min_score
FROM student;
本例得到:6 条记录、5 条非空成绩、总分 405、平均分 81、最高分 92、最低分 60。
最容易忘的是:AVG(score) 的分母是 5 个非空成绩,不会把周野的空成绩当成 0。没有输入行时,COUNT 返回 0,上表其余聚合返回 NULL;如果有记录但成绩全为空,COUNT(*) 仍会统计这些记录。SQLite 聚合函数说明
第 17~19 关:按组统计
17. 单字段分组:GROUP BY
先按一个字段把记录分组,再对每组分别计算。
SELECT class_id, AVG(score) AS avg_score
FROM student
GROUP BY class_id
ORDER BY class_id;
101 班平均分是 88.5,102 班是 76。空班级编号也形成一组;999 虽然在班级表里不存在,在学生表中仍是一个分组值。
GROUP BY 的目标是得到每组的结果,单独排序用 ORDER BY。写分组查询时,所选字段应当是 分组字段或聚合结果,避免顺手加一个无法代表整组的 name。
18. 多字段分组:按组合分组
SELECT class_id, exam_num, COUNT(*) AS total_num
FROM student
GROUP BY class_id, exam_num
ORDER BY class_id, exam_num;
这里的“一组”是 同一个班级 + 同一场考试。
101 班第 1 场有 2 条记录;102 班第 1 场、第 2 场各有 1 条。周野虽然成绩为空,仍然是一条考试记录,所以统计记录数用 COUNT(*),不能用 COUNT(score) 替代。
19. HAVING:筛选分组后的结果
SELECT class_id, SUM(score) AS total_score
FROM student
GROUP BY class_id
HAVING SUM(score) > 150
ORDER BY class_id;
只保留总分超过 150 的班级,本例是 101 班,总分 177。
| 对比 | WHERE | HAVING |
|---|---|---|
| 筛选对象 | 分组前的原始行 | 分组后的结果 |
| 典型条件 | score >= 60 | AVG(score) >= 80 |
| 位置 | GROUP BY 前 | GROUP BY 后 |
同时使用时,先筛选有效记录,再分组统计,最后筛选组:
SELECT class_id, COUNT(*) AS total_num, AVG(score) AS avg_score
FROM student
WHERE score IS NOT NULL
GROUP BY class_id
HAVING COUNT(*) >= 2
ORDER BY avg_score DESC, class_id ASC;
本例只有 101 班留下。把 COUNT(*) >= 2 写在 WHERE 中不成立,因为这时还没有形成组。
第 20~22 关:把两张表关联起来
20. CROSS JOIN:所有行两两组合
SELECT s.name AS student_name, c.name AS class_name
FROM student AS s
CROSS JOIN class AS c;
不按班级编号匹配,而是每个学生都与每个班级组合。本例有 6 名学生、3 个班级,返回 6 × 3 = 18 行。
关键词是 所有组合。要查询学生实际所属班级,应使用下一节的匹配条件。
21. INNER JOIN:只保留匹配上的记录
SELECT
s.name AS student_name,
s.age AS student_age,
s.class_id,
c.name AS class_name,
c.level AS class_level
FROM student AS s
INNER JOIN class AS c ON s.class_id = c.id
ORDER BY s.id;
ON 写关联条件:学生的 class_id 对应班级的 id。普通 JOIN 也可以表示 INNER JOIN。
结果只包含林舟、许宁、陈语、周野。林溪的 999 匹配不到班级,唐可没有班级编号,因此不返回;没有学生的三班也不会出现在结果中。
两张表都有 name,需要写 s.name、c.name 区分。关联后也不一定一人一行:若一条左表记录匹配多条右表记录,就会展开成多行。本例班级 id 唯一,所以每名学生最多匹配一个班级。
22. OUTER JOIN:保留没有匹配上的记录
LEFT JOIN 保留左表的所有记录,右表匹配不到的字段补 NULL。
SELECT s.name AS student_name, s.class_id, c.name AS class_name
FROM student AS s
LEFT JOIN class AS c ON s.class_id = c.id
ORDER BY s.id;
| student_name | class_id | class_name |
|---|---|---|
| 林舟 | 101 | 一班 |
| 许宁 | 101 | 一班 |
| 陈语 | 102 | 二班 |
| 周野 | 102 | 二班 |
| 林溪 | 999 | NULL |
| 唐可 | NULL | NULL |
“所有学生都要返回,即使没有对应班级”,就是把 student 放左边,用 LEFT JOIN。
RIGHT JOIN 则保留右表所有记录。为了便于记忆和兼容练习环境,可以交换两张表的位置,改用 LEFT JOIN:
-- 所有班级都返回,包括没有学生的三班
SELECT c.name AS class_name, s.name AS student_name
FROM class AS c
LEFT JOIN student AS s ON s.class_id = c.id
ORDER BY c.id, s.id;
外连接中,右表的筛选条件放在哪里,会改变保留的结果。
-- 保留所有学生,只关联级别为“基础”的班级
SELECT s.name AS student_name, c.name AS class_name
FROM student AS s
LEFT JOIN class AS c
ON s.class_id = c.id AND c.level = '基础';
-- 只保留匹配到“基础”班级的学生
SELECT s.name AS student_name, c.name AS class_name
FROM student AS s
LEFT JOIN class AS c ON s.class_id = c.id
WHERE c.level = '基础';
第一条仍有 6 名学生;第二条只剩 4 名,因为补出的 NULL 无法通过 WHERE c.level = '基础'。SQLite 的关联与 WHERE 处理说明
| 关联方式 | 复习时先问自己 | 本例行数 |
|---|---|---|
CROSS JOIN | 是不是要所有组合? | 18 |
INNER JOIN | 是不是只要双方能匹配的? | 4 |
student LEFT JOIN class | 是不是所有学生都要保留? | 6 |
class LEFT JOIN student | 是不是所有班级都要保留? | 5 |
刷题时的回忆顺序
先把题目拆成几个问题,再选语法:
| 题目在问什么 | 想到的语法 |
|---|---|
| 返回哪些字段,叫什么名字? | SELECT、AS |
| 从哪张表取数据? | FROM |
| 哪些记录符合条件? | WHERE、比较运算、LIKE、逻辑运算 |
| 空值算不算? | IS NULL、IS NOT NULL |
| 要不要按条件生成一列? | CASE WHEN |
| 要不要把结果去重? | DISTINCT |
| 是整体统计,还是每个班分别统计? | 聚合函数、GROUP BY |
| 统计后还要筛掉哪些组? | HAVING |
| 要补充另一张表的信息吗?所有记录都要保留吗? | INNER JOIN、LEFT JOIN |
| 怎么排序、取多少条、跳过多少条? | ORDER BY、LIMIT、OFFSET |
SQL 的书写顺序可以记为:
SELECT [DISTINCT] 列或表达式
FROM 表
[JOIN 另一张表 ON 关联条件]
[WHERE 行条件]
[GROUP BY 分组字段]
[HAVING 组条件]
[ORDER BY 排序字段]
[LIMIT 条数 OFFSET 偏移量]
方括号表示可选部分,不是实际 SQL。理解查询时,可以先沿着“取数据和关联 → 筛行 → 分组统计 → 筛组 → 输出列与去重 → 排序 → 截取”的顺序推一遍;这是帮助理解结果的顺序,数据库实际如何执行由优化器决定。