SQLite 教学工作区 · 速查卡 · 配合课程 0003

SELECT 与 JOIN 速查

来源:官方 SELECT、Query Planning、EXPLAIN QUERY PLAN、NULL 语义。

查询骨架(写之前先在心里填一遍)

SELECT   要什么(列 / 聚合 / 表达式)
FROM     从哪来(表 / 子查询 / JOIN)
WHERE    过滤行(聚合之前)
GROUP BY 按什么分组
HAVING   过滤组(聚合之后)
ORDER BY 怎么排
LIMIT    取几行 [OFFSET 跳过几行];

书写顺序 = 执行顺序(除 SELECT 实际最后算),所以 WHERE 里不能用聚合别名,HAVING 里可以。

JOIN 四种(默认是 INNER)

写法	留下什么	典型用途
`JOIN … ON`(= INNER JOIN)	两边都能配上的行	补信息(如给 tag 补项目名)
`LEFT JOIN`	左表全部 + 右表配上的部分	"有哪些没配上的"(右表列为 NULL 即未匹配)
`CROSS JOIN`	左右笛卡尔积	生成组合(如日期 × 项目)
逗号 + WHERE	等价于 INNER JOIN	老写法,可读性差,不建议

JOIN 前必做的一次检查

SELECT count(*) AS rows, count(DISTINCT 连接键) AS uniq FROM 右表;
-- 相等 → 安全;不等 → 你的 sum()/count() 会被放大,且不报错

聚合与 NULL(最容易静默出错的地方)

写法	含义
`count(*)`	行数(含全 NULL 的行)
`count(列)`	该列非空的行数
`count(DISTINCT 列)`	该列非空的去重个数
`sum(列)` / `avg(列)`	忽略 NULL;若全是 NULL,`sum` 返回 NULL、`avg` 返回 NULL
`total(列)`	同 `sum` 但全 NULL 时返回 `0.0`(避免 NULL 传播)
`group_concat(列, '分隔')`	把一组值拼成一个字符串(排查/展示很好用)

判断空值必须用 IS NULL / IS NOT NULL;写成 = NULL 永远是"不成立",也不会报错。

结果整形

ORDER BY 列 DESC, 另一列 ASC          -- 多级排序
LIMIT 10 OFFSET 20                    -- 分页(第 3 页、每页 10 行)
SELECT 列 AS 别名                     -- 列别名;中文别名在 Windows 控制台易乱码,先 chcp 65001
SELECT DISTINCT 列                    -- 去重
SELECT CASE WHEN 条件 THEN 'A' ELSE 'B' END AS 标签 FROM t;   -- 行内分支
WITH x AS (SELECT …) SELECT * FROM x; -- CTE:把复杂查询拆成可读的几步

看数据库怎么读你的查询

EXPLAIN QUERY PLAN SELECT …;
-- SEARCH 表 USING INDEX 索引名 (列=?)   → 用索引定位(好)
-- SEARCH 表 USING COVERING INDEX …      → 索引里就有全部所需列,连表都不用回(更好)
-- SCAN 表                               → 逐行扫全表(数据量大时慢)
-- USE TEMP B-TREE FOR ORDER BY          → 排序没法借索引,临时建树(值得关注)

它说的是执行计划,不是结果对错。全表扫也能出正确结果 —— 差别在代价。

五个常见坑

count(列) 当 count(*) 用 → 有 NULL 时少算。
JOIN 右表键不唯一 → 行数放大,sum() 静默偏大。
该用 HAVING 的地方写成 WHERE(对聚合结果过滤)→ 报错或语义错。
忘了 GROUP BY 却混用聚合与普通列 → SQLite 容忍,结果"看起来对",但含义不确定。
= NULL 代替 IS NULL → 永远不成立,不报错。

SELECT 与 JOIN 速查

查询骨架(写之前先在心里填一遍)

JOIN 四种(默认是 INNER)

JOIN 前必做的一次检查

聚合与 NULL(最容易静默出错的地方)

结果整形

看数据库怎么读你的查询

五个常见坑

相关