sql基本语法笔记（2026-10-16）
SQL 学习笔记
复习与查阅版 · 2026-10-16 · MySQL 8.0 / Navicat
刷题时查这一份。 完整原表、题目和参考答案放在练习附录，掌握情况和待办放在学习进度。
| 想查什么 | 去哪里 |
|---|---|
| 查询顺序、别名、筛选 | 1. 查询与筛选 |
| 计数、去重、分组、排序 | 2. 汇总与排序 |
| 两表怎么配、连接后为什么多行 | 3. JOIN 与每行的含义 |
| 拆步骤、子查询、WITH | 4. 分步查询 |
| 条件计数、条件金额、比例 | 5. CASE 与条件统计 |
| 字符串、日期、时间差、NULL | 6. 常用函数 |
| 排名、COUNT OVER、累计、前后行 | 7. 窗口函数 |
| 订单、复购、购买比例、留存 | 8. 常见指标 |
| 建表、增删改、视图、索引 | 9. 数据与数据库对象 |
| 报错和结果不对时怎么查 | 10. 检查方法 |
也可搜索带方括号的知识点标记；每个标记只出现一次。代码示例中的表、字段须与自己的数据对应。
1. 查询与筛选
一条查询怎么组成
【SELECT】决定显示哪些列；FROM 指定来源；WHERE 筛选参与查询的行。
SELECT product_name, sale_price
FROM product
WHERE product_type = '衣服';
【CALCULATED_COLUMN】表达式也能作为结果列，例如 sale_price - purchase_price AS profit。它只计算查询结果，不给原表新增一列。
【SQL_ORDER】书写顺序：
SELECT → FROM / JOIN / ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
理解结果时，先确定来源和连接，再筛行、分组汇总、筛组、形成输出、去重、排序。窗口计算发生在同层筛行和分组之后。这是理解模型，数据库实际执行方案可能不同。
别名与引号
【ALIAS】列别名是结果的名字，表别名是来源的简称。
| 写法 | 含义 |
|---|---|
sale_price AS price |
结果列叫 price |
FROM product AS p |
用 p 代表 product |
p.product_name |
读取 p 的 product_name 列 |
FROM (...) AS t |
用 t 代表整份子查询结果 |
【ALIAS_SCOPE】MySQL 中，同层 SELECT 刚定义的列别名：
| 可以引用 | 不能直接引用 |
|---|---|
| ORDER BY；适当的 GROUP BY、HAVING | WHERE；SELECT 中的另一个表达式 |
要筛选新算出的 profit 或排名：重写短表达式，或先放进子查询 / WITH，再由外层使用。
【SQL_LITERAL】列名读取数据；字符串和日期加英文单引号；数字通常不加引号。
| 示例 | 表示什么 |
|---|---|
product_name |
列 |
'product_name' |
固定文字 |
'衣服'、'2026-10-16' |
文字、日期值 |
3000 |
数字 |
`product_name` |
用反引号包住的列名 |
常用筛选写法
| 知识点 | 写法与含义 | 记住这一点 |
|---|---|---|
| 【WHERE】筛行 | WHERE sale_price >= 1000 |
只保留条件为 TRUE 的行 |
| 【AND_OR】条件组合 | A AND (B OR C) |
AND 优先于 OR；混用时用括号表达意图 |
| 【LIKE】模糊匹配 | LIKE 'abc%' 开头；'%abc' 结尾；'%abc%' 包含 |
% 可匹配零个或多个字符；LIKE 不是函数 |
| 【BETWEEN】闭区间 | BETWEEN 500 AND 1000 |
包含 500 和 1000 |
| 【IS_NULL】判断缺失 | IS NULL / IS NOT NULL |
不用 = NULL |
| 【IN】名单筛选 | IN ('衣服', '办公用品') |
只有一项也合法 |
1000 <= 售价 < 3000 要写成两个条件：sale_price >= 1000 AND sale_price < 3000。
【NULL】NULL 表示未知或缺失；0、''、' '、'NULL' 都不是 NULL。NOT NULL 也不等于禁止空字符串或空格。
2. 汇总与排序
聚合与计数
【AGGREGATE】不分组时对筛选后的全部行汇总；分组时各组独立汇总。
| 知识点 | 写法 | 作用 |
|---|---|---|
| 【SUM】合计 | SUM(sale_price) |
非 NULL 数值相加 |
| 【AVG】平均 | AVG(purchase_price) |
除以非 NULL 值的个数 |
| 【MAX】最大 | MAX(sale_price) |
不会自动带出对应的商品名 |
| 【MIN】最小 | MIN(regist_date) |
日期最小表示最早 |
| 【COUNT】计数 | COUNT(*) / COUNT(score) |
全部行数 / score 非 NULL 的行数 |
| 【COUNT_DISTINCT】去重计数 | COUNT(DISTINCT user_id) |
不同非 NULL 用户编号的个数 |
成绩是 90、NULL、0、60 时：
| COUNT(*) | COUNT(score) | SUM(score) | AVG(score) |
|---|---|---|---|
| 4 | 3 | 150 | 50 |
零参与统计，NULL 被上述数值聚合跳过。 没有可计算的非 NULL 值时，SUM / AVG / MAX / MIN 返回 NULL；COUNT 返回 0。
去重与分组
【DISTINCT】SELECT DISTINCT user_id, score 按这两列的组合去重。它只改变查询结果，不删除原表记录。
按用户去重时应保留用户编号；两个不同用户评分相同，不能因此合成一个用户。
【GROUP_BY】决定每组由什么识别；例如按种类汇总，结果每个种类一行。
SELECT product_type, COUNT(*) AS product_count
FROM product
GROUP BY product_type;
【GROUP_COLUMNS】分组后优先显示分组键和聚合结果。按种类分组，却直接显示某件商品名，会出现“一组里该选哪个名字”的问题。
MySQL 能识别部分函数依赖：主键可确定同表其他列。但不能据此随意输出任意未分组列。多列 GROUP BY 按列值的组合分组。
【HAVING】WHERE 筛原始行，HAVING 筛汇总后的组：
SELECT product_type, COUNT(*) AS product_count
FROM product
WHERE sale_price >= 500
GROUP BY product_type
HAVING COUNT(*) >= 2;
排序与取行
【ORDER_BY】多个排序键从左到右比较，前一项相同才比后一项。
ORDER BY sale_price DESC, purchase_price ASC, product_id ASC
ASC 升序，DESC 降序；每个排序字段分别写方向。SELECT 中列的先后不决定排序。
【NULL_ORDER】MySQL 默认 NULL 升序在前、降序在后。需要“已知值升序，NULL 最后”时：
ORDER BY purchase_price IS NULL ASC, purchase_price ASC
【LIMIT】LIMIT 3 最多取 3 行；LIMIT 1, 4 跳过 1 行，再取最多 4 行。要取最高的几条，应先明确 ORDER BY。
3. JOIN 与每行的含义
怎么连接
【JOIN】INNER JOIN 只保留满足连接条件的配对。
SELECT p.product_name, sp.shop_name
FROM product AS p
JOIN shopproduct AS sp ON p.product_id = sp.product_id;
【JOIN_CONDITION】ON 要比较能对应的两边字段。误写 p.product_id = p.product_id，会让每件商品配上右表所有记录。
【LEFT_JOIN】左表没配上也保留，右表字段填 NULL；配上多条就产生多行。
连接后，一行代表什么
【JOIN_GRAIN】用附录中的 users 与 orders 左连接，得到以下结果：
| 用户编号 | 订单编号 | 付款金额 |
|---|---|---|
| U01 | 1001 | 100 |
| U01 | 1002 | 100 |
| U02 | 1003 | 100 |
| U03 | NULL | NULL |
| U04 | NULL | NULL |
这里有 5 行、4 位用户、3 笔订单、2 位买家。一位用户可以有多笔订单，不能把连接后的行数直接当人数。
【JOIN_GROUP】数不同对象，要数对应的编号：
| 想数什么 | 在上述结果中怎么数 |
|---|---|
| 全部用户 | COUNT(DISTINCT u.user_id) → 4 |
| 购买用户 | COUNT(DISTINCT o.user_id) → 2 |
| 订单 | COUNT(o.order_id) → 3，本例每笔订单只出现一次 |
LEFT JOIN 补出的空行也被 COUNT(*) 计入，因此不能用它直接数右表匹配记录。
ON 与 WHERE
【JOIN_ON_WHERE】想保留全部商品，只补东京店的信息：
FROM product AS p
LEFT JOIN shopproduct AS sp
ON p.product_id = sp.product_id
AND sp.shop_name = '东京'
若把店名条件移到 WHERE，只会留下店名为东京的结果行；没配上的商品会被筛掉。WHERE 筛掉后，不会重新补一行 NULL。
【JOIN_UNMATCHED】找没匹配上的左表行，可在 LEFT JOIN 后用 WHERE sp.product_id IS NULL。判断列应选右表匹配成功时保证非空的标识列。
一对多连接后的金额
【JOIN_PREAGG】订单 1001 金额 100，有两条明细，直接连接会出现：
| 订单编号 | 订单金额 | 明细商品 |
|---|---|---|
| 1001 | 100 | A |
| 1001 | 100 | B |
此时 SUM(订单金额) 会得到 200。处理步骤：明细先按订单汇总 → 每笔订单一行 → 连接订单 → 按用户汇总。 完整代码见附录 E06。
不能用 SUM(DISTINCT paid_amount) 通用修复：两笔真实的 100 元订单也会被合成一个金额。
【JOIN_THREE】三表连接时逐段找对应编号：订单通过 user_id 配用户，通过 order_id 配订单件数。每连接一次，都检查行数和一行的含义。见附录 E12。
4. 分步查询
先看内层结果的形状
【SUBQUERY】子查询是查询中再包含查询。先问外层需要什么：
| 知识点 | 用在哪里 | 内层提供什么 |
|---|---|---|
| 【SCALAR_SUBQUERY】标量子查询 | 比较或输出一个值 | 一列，最多一行；没返回行时为 NULL |
| 【SELECT_SUBQUERY】SELECT 子查询 | 给当前行增加一个值 | 必须满足标量要求 |
| 【FROM_SUBQUERY】FROM / JOIN 子查询 | 当作一张表继续查 | 可以多行多列，MySQL 要求表别名 |
| 【IN_SUBQUERY】名单子查询 | WHERE product_id IN (...) |
与左边相匹配的一列，可多行 |
| 【EXISTS】存在判断 | WHERE EXISTS (...) |
找到至少一行就成立 |
| 【NOT_EXISTS】不存在判断 | WHERE NOT EXISTS (...) |
一行也找不到才成立 |
例如，查售价高于全表均价的商品：
SELECT product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
EXISTS 常写 SELECT 1，关注有没有行。不要改成不分组的 SELECT COUNT(*)：即使计数为 0，它也返回一行。
找“没有东京经营记录”，是在内层寻找东京记录，再对存在性取反；不能简单把内层条件改成店名不等于东京。见附录 E23。
内层结果作为表
SELECT product_type, total_price
FROM (
SELECT product_type, SUM(sale_price) AS total_price
FROM product
GROUP BY product_type
) AS product_total
WHERE total_price > 4000;
内层每种商品一行；外层能筛 total_price，因为它已经是来源中的列。
【SUBQUERY_SCOPE】外层只能使用内层输出的列和整份结果的别名。内层叫 ord，整份结果叫 totaltable，外层应读 totaltable.order_id。
WITH：给中间结果起名
【WITH】只在当前语句内有效，不创建永久表。
WITH product_profit AS (
SELECT product_name, sale_price - purchase_price AS profit
FROM product
)
SELECT product_name, profit
FROM product_profit
WHERE profit > 1000;
多个中间结果放在一个 WITH 后，用逗号分隔多个 名称 AS (...)；分号放在整条语句末尾。子查询和 WITH 都可以用，按步骤是否清楚来选。
关联子查询
【CORRELATED_SUBQUERY】内层使用外层当前行的信息。
SELECT p1.product_name,
(SELECT AVG(p2.sale_price)
FROM product AS p2
WHERE p2.product_type = p1.product_type) AS type_avg_price
FROM product AS p1;
外层走到运动T恤 → 当前种类是衣服 → 内层取两件衣服的均价 2500 → 显示在这一行旁边。
p1、p2 是同一张表在不同查询层中的别名。按种类关联才能算同类均价；按唯一商品编号关联只会留下自己。
5. CASE 与条件统计
根据条件生成值
【CASE】给当前行生成标签或数值：
CASE
WHEN sale_price >= 3000 THEN '高价'
WHEN sale_price >= 1000 THEN '中价'
ELSE '低价'
END AS price_label
【CASE_PRIORITY】同一个 CASE 采用第一个成立的分支。先判断高价，再判断中价；多个独立 CASE 要各自写清条件。CASE 生成值，WHERE 筛行。
数数量，还是加金额
| 知识点 | 表达式 | 条件成立时取什么 |
|---|---|---|
| 【CONDITIONAL_COUNT】条件计数 | SUM(CASE WHEN sale_price >= 3000 THEN 1 ELSE 0 END) |
取 1，用于数符合条件的行 |
| 【CONDITIONAL_AMOUNT】条件金额 | SUM(CASE WHEN repeat_flag = 1 THEN user_amount ELSE 0 END) |
取金额，用于算符合条件的金额合计 |
COUNT 会数 0。 COUNT(CASE WHEN ... THEN 1 ELSE 0 END) 会同时数到 1 和 0；需要数符合条件的行时，用上面的 0/1 求和，或让未满足分支返回 NULL。
【RATIO】比例先写清分子、分母，再决定在哪一步筛选。分母若要全部商品，就不能先 WHERE 只留下高价商品。
ROUND(
SUM(CASE WHEN sale_price >= 3000 THEN 1 ELSE 0 END) / COUNT(*),
2
)
这是高价数量占全部数量的比例。0.50 表示 50%；分母可能为零时见下一章的 NULLIF。
6. 常用函数
普通函数处理当前行；聚合函数汇总多行；窗口函数在保留明细的同时跨行计算。函数可以嵌套，先算内层。
数值与字符串速查
| 知识点 | 写法 | 例子或提醒 |
|---|---|---|
| 【ABS】绝对值 | ABS(数值) |
ABS(-5) → 5 |
| 【MOD】余数 | MOD(被除数, 除数) |
MOD(880, 100) → 80；不整除判断用 <> 0 |
| 【ROUND】四舍五入 | ROUND(表达式, 位数) |
ROUND(8.2567, 3) → 8.257；0 位表示取整 |
| 【CONCAT】拼接 | CONCAT(product_name, '-', product_type) |
任意参数为 NULL，结果为 NULL |
| 【CHAR_LENGTH】字符数 | CHAR_LENGTH('打孔器') |
3；问几个字时用它 |
| 【LENGTH】字节数 | LENGTH(_utf8mb4'打孔器') |
9；取决于编码 |
| 【LOWER】小写 | LOWER('SQL学习') |
sql学习 |
| 【UPPER】大写 | UPPER('sql学习') |
SQL学习 |
| 【SUBSTRING】按位置截取 | SUBSTRING('abcdef', 2, 3) |
bcd；从 1 开始，第三项是取几个字符 |
| 【SUBSTRING_INDEX】按分隔符截取 | SUBSTRING_INDEX('a@b.com', '@', -1) |
b.com；1 取左边，-1 取右边 |
| 【TRIM】去两端普通空格 | TRIM(' data analysis ') |
中间空格保留 |
SUBSTRING 也支持 SUBSTRING(文字 FROM 起点 FOR 个数)。SUBSTRING_INDEX 遇到多个分隔符时，正数从左数，负数从右数。
替换文字
【REPLACE】REPLACE(原文字, 旧片段, 新片段)，替换所有匹配片段，匹配区分大小写。
SELECT REPLACE('mysql_sql', 'sql', 'SQL') AS new_name;
结果为 mySQL_SQL。找不到旧片段就返回原文字。SELECT 只显示结果；要写回原表，用 UPDATE，见第 9 章。
提取与推算日期
| 知识点 | 写法 | 作用 |
|---|---|---|
| 【YEAR】年份 | YEAR(regist_date) |
返回年份数字 |
| 【MONTH】月份 | MONTH(regist_date) |
返回月份，不包含年份 |
| 【DAY】日号 | DAY(regist_date) |
当月第几天 |
| 【DATE_FORMAT】格式化 | DATE_FORMAT(regist_date, '%Y-%m') |
返回年月文字 |
| 【DATE_ADD】往后推 | DATE_ADD(regist_date, INTERVAL 7 DAY) |
7 天后的日期 |
| 【DATE_SUB】往前推 | DATE_SUB('2009-09-20', INTERVAL 6 DAY) |
2009-09-14 |
日期格式符：%Y 年、%m 月、%d 日、%H 时、%i 分、%s 秒。
跨年统计不能只按 MONTH 分组；可按年月分组。往前 6 天加当天，共 7 个自然日。
日期差与实际时长
【DATEDIFF】只计算日期部分：DATEDIFF(结束日期, 开始日期)，忽略时分秒。
【TIMESTAMPDIFF】按指定单位计算时间差：TIMESTAMPDIFF(单位, 开始时间, 结束时间)，结果为整数。
| 单位 | 含义 |
|---|---|
| SECOND | 秒 |
| MINUTE | 完整分钟 |
| HOUR | 完整小时 |
| DAY | 完整的 24 小时 |
单位不加引号。想保留小数小时，先算秒，再除以 3600。
SELECT TIMESTAMPDIFF(
SECOND, '2022-03-22 08:45:00', '2022-03-22 17:00:00'
) / 3600 AS work_hours;
结果：29700 秒 ÷ 3600 = 8.25 小时。直接按 HOUR 算得到 8，之后 ROUND 不能找回丢掉的分钟。
从 3 月 22 日 23:00 到 3 月 23 日 01:00：
| 算法 | 结果 |
|---|---|
| DATEDIFF(结束, 开始) | 1 天，日期变了 |
| TIMESTAMPDIFF(DAY, 开始, 结束) | 0，未满 24 小时 |
| TIMESTAMPDIFF(SECOND, 开始, 结束) / 3600 | 2 小时 |
两种函数用途、参数顺序都不同，保留各自用法。平均工时通常先算准确时长，再求平均，最后按题意 ROUND。你的完整提交见附录 C01。
日期范围
【DATE_RANGE】DATE 只含日期，BETWEEN '2009-09-01' AND '2009-09-30' 可以包含整个 9 月。
【DATETIME_RANGE】DATETIME 含时间，完整自然月优先写“本月月初及之后、下月月初之前”：
WHERE regist_time >= '2009-09-01 00:00:00'
AND regist_time < '2009-10-01 00:00:00'
上界若写 9 月 30 日，通常只到当天零点，会漏掉白天和晚上。边界样例见附录 C02。
类型与缺失值
| 知识点 | 写法 | 记住这一点 |
|---|---|---|
| 【CAST】转换类型 | CAST('0001' AS SIGNED INTEGER) → 1 |
转数字会丢前导零；转换结果不修改原列类型 |
| 【COALESCE】缺失时取备用值 | COALESCE(purchase_price, 0) |
从左到右取第一个非 NULL 值；0 和空串不触发替换 |
| 【NULLIF】相等时变 NULL | NULLIF(previous_amount, 0) |
等于 0 返回 NULL，否则返回原值 |
是否把缺失金额补 0，要按业务定义决定。仅显示“未登记”时可以用文字备用值，后续金额计算仍应保留数值列。
【NULL_ARITHMETIC】普通加减乘除遇到 NULL，结果为 NULL，包括 0 * NULL。聚合通常跳过 NULL，不能把两种规则混用。
7. 窗口函数
OVER 与分区
【OVER】函数决定算什么；OVER 内规定在哪些行中、按什么顺序计算。结构示意：
函数(参数) OVER (
PARTITION BY 分区列
ORDER BY 排序列
ROWS 计算范围
)
按需求选择各项。SUM、AVG、COUNT 可以配 OVER；ROUND 等普通函数用来包住计算结果，不能随意加 OVER。
【PARTITION_BY】按分区独立计算，保留明细行：
| 目标 | 写法 | 结果每行代表什么 |
|---|---|---|
| 每类商品一个均价 | GROUP BY product_type 后 AVG | 一种商品类别 |
| 每件商品旁边显示同类均价 | AVG(...) OVER(PARTITION BY product_type) | 一件商品 |
COUNT 后面可以加 OVER
【WINDOW_COUNT】保留每位员工，同时显示所在岗位的人数：
SELECT staff_id, post,
COUNT(*) OVER(PARTITION BY post) AS post_count
FROM staff_tb;
岗位依次为产品、产品、运营时，人数列是 2、2、1。COUNT(*) OVER() 不分区，三行都显示 3；COUNT(列) 仍忽略该列的 NULL。
三种排名放在一起记
成绩按降序为 4、4、2：
| 知识点 | 结果 | 用途 |
|---|---|---|
| 【ROW_NUMBER】连续序号 | 1、2、3 | 每行一个不同序号 |
| 【RANK】并列跳号 | 1、1、3 | 同分并列，后面跳名次 |
| 【DENSE_RANK】并列不跳号 | 1、1、2 | 同分并列，下个分数接下一名 |
ROW_NUMBER() OVER(ORDER BY score DESC, student_id ASC)
同分按唯一学号确定先后。若 RANK 需要同分并列，窗口里只按成绩等排名依据排序，唯一编号放到最终展示排序中。
【WINDOW_ORDER】窗口内 ORDER BY 决定怎样计算；查询末尾 ORDER BY 决定怎样展示。两者不能相互替代。
每类前几名
【TOP_N】先算排名，再由外层筛选：
WITH ranked_products AS (
SELECT product_type, product_id, sale_price,
RANK() OVER(
PARTITION BY product_type ORDER BY sale_price DESC
) AS rk
FROM product
)
SELECT product_type, product_id, sale_price, rk
FROM ranked_products
WHERE rk <= 2
ORDER BY product_type, sale_price DESC, product_id;
RANK 取前两名可能超过两行；DENSE_RANK 取前两个不同分值；ROW_NUMBER 每类最多两行。同层 WHERE 不能直接筛窗口函数或其新别名。
均值、累计与移动平均
| 知识点 | 表达式 |
|---|---|
| 【WINDOW_AVG】同类均价 | AVG(sale_price) OVER(PARTITION BY product_type) |
| 【RUNNING_SUM】从头累计到当前行 | SUM(sale_amount) OVER(ORDER BY sale_date ROWS UNBOUNDED PRECEDING) |
| 【MOVING_AVG】当前行加前两行的均值 | AVG(sale_amount) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) |
销售额按日期依次为 100、200、150、250：
| 第几行 | 当天金额 | 累计金额 | 最近三行均值 |
|---|---|---|---|
| 1 | 100 | 100 | 100 |
| 2 | 200 | 300 | 150 |
| 3 | 150 | 450 | 150 |
| 4 | 250 | 700 | 200 |
不足三行用实际存在的行；AVG 还会忽略范围中的 NULL。最近三行是否等于最近三天，取决于日期是否连续且每天一行。
ROWS：相对当前行的范围
【ROWS】只规定取哪些行；用 SUM 还是 AVG，才决定怎样计算。
| 边界或范围 | 含义 |
|---|---|
| CURRENT ROW | 当前行 |
| 2 PRECEDING / 3 FOLLOWING | 前第 2 行 / 后第 3 行 |
| UNBOUNDED PRECEDING / FOLLOWING | 分区第一行 / 最后一行 |
| ROWS UNBOUNDED PRECEDING | 从分区第一行到当前行 |
| ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | 前两行到当前行，最多三行 |
逐行累计时保留明确的 ROWS 范围；省略范围后的默认规则可能把排序值相同的行一起纳入。同一日期多行时，按题意补充能确定先后的排序列。
先 WHERE 留下第 2～4 天再累计，得到 200、350、600；全部算完再用外层筛第 2～4 天，显示 300、450、700。筛选放在哪一层，会改变参与窗口计算的数据。
上一行、下一行与环比
| 知识点 | 表达式 | 四行金额 100、200、150、250 的结果 |
|---|---|---|
| 【LAG】上一行 | LAG(sale_amount) OVER(ORDER BY sale_date) |
NULL、100、200、150 |
| 【LEAD】下一行 | LEAD(sale_amount) OVER(ORDER BY sale_date) |
200、150、250、NULL |
默认偏移一行，缺少对应行时返回 NULL。前后行不自动等于昨天、明天，也不由 ROWS 范围控制偏移。
【CHANGE_RATE】环比 =（本期 − 上期）÷ 上期。先用 LAG 输出 previous_amount，再由外层计算：
ROUND((sale_amount - previous_amount) / NULLIF(previous_amount, 0), 4)
四行增长率为 NULL、1.0000、-0.2500、0.6667。上期为 0 或 NULL 时，本写法返回 NULL，不表示增长 0%。完整分层代码见附录 E20。
8. 常见指标
先说清：统计什么范围、每行代表谁、分子分母分别是什么。下面使用附录固定样本，不额外添加月份或注册后的限制。
订单与复购
【ORDER_METRICS】订单表每笔订单一行时：
| 指标 | 计算 | 原样本结果 |
|---|---|---|
| 订单数 | COUNT(*) | 3 |
| 买家数 | COUNT(DISTINCT user_id) | 2 |
| 付款总额 | SUM(paid_amount) | 300 |
| 每笔订单平均金额 | 总额 ÷ 订单数 | 100 |
| 每位买家平均付款 | 总额 ÷ 买家数 | 150 |
题目写“客单价”时，确认它以订单还是人数为分母。连接明细后，先检查订单金额有没有重复。
【REPEAT_BUYER】本练习把观察范围内至少两笔已付款订单的用户定义为复购买家。先按用户汇总：
| 用户 | 订单数 | 用户付款合计 | 复购标记 |
|---|---|---|---|
| U01 | 2 | 200 | 1 |
| U02 | 1 | 100 | 0 |
【REPEAT_RATE】复购买家数 ÷ 全部买家数，本例 1 ÷ 2 = 0.5000。每位买家一行时，用标记求和数复购买家；不先筛掉标记为 0 的人。
【REPEAT_PAID】复购买家的全部付款 ÷ 全部付款，本例 200 ÷ 300 = 0.6667。此口径包含复购买家的首笔订单，不等于仅第二笔及以后订单金额。
完整练习：E08～E11。
购买比例与次日留存
【PURCHASE_RATE】购买用户人数 ÷ 全部用户人数。
从 users LEFT JOIN orders；分母数不同 u.user_id，分子数不同 o.user_id。原样本 2 ÷ 4 = 0.5000；不能把连接后的 5 行当作 5 位用户。见附录 E13、E14。
【RETENTION】注册次日留存率 = 某注册日次日回访人数 ÷ 该注册日全部注册人数。
处理顺序：用户编号与注册次日同时匹配 → 每人一个 0/1 标记 → 按注册日汇总。 不提前筛掉未回访者；无人回访的注册日也要保留。
原样本 10 月 1 日注册的 2 人中回访 1 人，留存 0.5000；10 月 2 日注册的 2 人中回访 0 人，留存 0.0000。
一天多条活动先确保每人不被重复计数；尚未观察完次日，不能直接判成未留存。见附录 E15、E16。
9. 数据与数据库对象
数据库、类型与约束
| 知识点 | 记住的内容 |
|---|---|
| 【DATABASE】库、表、行、列 | 库放表；一行一条记录；一列一种属性；USE shop 选择库 |
| 【QUERY_SAVE】保存查询 | 保存 SQL 文字与改变数据库数据分别处理；仅编辑未执行不会改变数据 |
| 【DATA_TYPE】常见类型 | INT 整数；DECIMAL(10,2) 共最多 10 位、其中小数 2 位；CHAR 定长文字；VARCHAR 变长文字；DATE 日期；DATETIME 日期时间 |
| 【CONSTRAINT】约束 | NOT NULL 禁止 NULL；主键唯一且非空；联合主键要求组合唯一 |
编号有前导零时可用文字类型保存。关闭查询页不会删除已经成功执行并提交的数据；未提交事务另按提交或回滚处理。
建表与表结构
【CREATE_TABLE】创建表；列定义之间用逗号，最后一项后不加多余逗号。
CREATE TABLE addressbook_demo (
regist_no INT PRIMARY KEY,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address VARCHAR(100)
);
【ALTER_TABLE】增加列，例如 ALTER TABLE addressbook_demo ADD COLUMN zip_code CHAR(8) DEFAULT NULL;。DEFAULT 指默认值；给已有数据增加非空列时，要考虑旧行的取值。
【CREATE_TABLE_AS】保存查询结果为新表：
CREATE TABLE clothes_copy AS
SELECT product_id, product_name, sale_price
FROM product
WHERE product_type = '衣服';
它保存当时的数据，之后不自动同步，也不等于完整复制原表的主键和索引。
【DROP_TABLE】DROP TABLE addressbook_demo; 删除整张表及数据。
INSERT：新增行
【INSERT_VALUES】列和值按位置对应；每组括号是一行，多行用逗号隔开。
INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
【INSERT_IGNORE】在 INSERT 后加 IGNORE，可跳过主键或唯一键冲突的待插入行，保留原有行。冲突判断依靠约束；它还可能把部分其他数据错误转成警告，不能当作只处理重复。详例见附录 M01。
【INSERT_SELECT】从查询结果插入已有表；目标列与 SELECT 输出列按位置对应：
INSERT INTO clothes_copy (product_id, product_name, sale_price)
SELECT product_id, product_name, sale_price
FROM product
WHERE product_type = '衣服';
UPDATE：修改已有行
【UPDATE】SET 列出新值，WHERE 确定修改范围；多列赋值用逗号。
UPDATE titles_test
SET to_date = NULL,
from_date = '2001-01-01'
WHERE to_date = '9999-01-01';
先按原数据找符合条件的行，再修改这些行的两列。NULL 不加引号；SET to_date = NULL 是赋值，WHERE to_date IS NULL 是判断缺失。没有 WHERE 会更新所有行。
UPDATE 与 REPLACE 组合时：
| SET 中的写法 | 含义 |
|---|---|
raw_name = REPLACE(raw_name, 'sql', 'SQL') |
读取原值，替换匹配片段，再写回；无匹配就保留原值 |
raw_name = 'SQL' |
符合 WHERE 的行都设成固定文字 SQL |
REPLACE 按字符串片段处理；数字整体改值通常直接赋值更清楚。原数字例子见附录 M02。
DELETE 与查询去重
【DELETE】DELETE FROM 表名 WHERE 条件; 删除满足条件的原表行；没有 WHERE 会删除所有行。这里是语法提示，独立删除练习仍待完成。
SELECT DISTINCT、SELECT 筛选排名保留行，只改变显示结果。SQL241 查询到 rk = 1，不等于已经删除其他行。
视图与上下合并
| 知识点 | 作用与写法 |
|---|---|
| 【VIEW】视图 | CREATE VIEW v_clothes AS SELECT ...；保存查询定义，读取底表当时的数据 |
| 【ALTER_VIEW】改定义 | ALTER VIEW v_clothes AS SELECT ...；重写完整查询定义 |
| 【VIEW_UPDATE】改数据 | 可更新视图上的 UPDATE 改变底表数据；分组聚合视图通常不可更新，连接视图须看定义 |
| 【VIEW_INSERT】经视图插入 | 仍需满足底表必填列和约束；是否检查视图筛选条件取决于相关选项 |
| 【VIEW_LOOKUP】查看 | SELECT * FROM v_clothes 查内容；information_schema.VIEWS 可列出视图名称 |
| 【UNION】上下合并去重 | 两边列数相同，对应类型兼容，按全部输出列判断重复 |
| 【UNION_ALL】上下合并保留重复 | 输出列名取第一条 SELECT；整体 ORDER BY 写在最后 |
JOIN 按条件配对行；UNION 把结果上下拼接。列名和顺序来自第一条 SELECT 的输出定义，数据按对应位置合并。
索引与触发器
【INDEX】索引用于辅助查找；普通索引不限制重复，唯一索引限制非 NULL 键值重复，主键唯一且非空。
ALTER TABLE product ADD INDEX idx_product_type (product_type);
一张表可有多个普通或唯一索引，只有一个主键；联合主键可以多列。MySQL 唯一索引允许多个 NULL；创建约束前已有数据须符合要求。索引不替代 ORDER BY。
【FORCE_INDEX】FROM product AS p FORCE INDEX (idx_product_type)，括号中写已有索引名；主键名为 PRIMARY。它影响索引选择，但不保证更快，也不保证所有情况都避免全表扫描。
【TRIGGER】表发生指定变化时自动执行动作。BEFORE / AFTER 表示操作前后；INSERT / UPDATE / DELETE 表示事件；FOR EACH ROW 表示每处理一行触发一次。
NEW 代表新行，OLD 代表旧行：INSERT 用 NEW，DELETE 用 OLD，UPDATE 可用两者。正式学习和独立练习仍待进行，完整已讲示例放在附录 M03。
10. 检查方法
【SQL_REVIEW】动笔前写四句话：
- 哪些数据参与？
- 最终每行代表什么？
- 输出哪些字段或指标？
- 按什么排序，如何处理并列？
【SQL_SYNTAX_CHECK】报错时依次看：英文逗号与引号 → 括号 → 关键字结构 → 表字段拼写 → 别名作用域 → 子查询别名 → 分号位置。
报错 near 指向数据库无法继续解析的位置，错误也可能出在它前面。仅凭报错片段不能确定唯一原因，需要完整 SQL，尤其是 JOIN 条件和 WITH 定义。
【SQL_RESULT_CHECK】能运行但结果不对时：
| 现象 | 优先检查 |
|---|---|
| 每个商品统计值都一样 | ON 是否把字段与自身比较 |
| 订单金额变大 | 连接明细后是否重复累计订单金额 |
| 没购买的人消失 | INNER JOIN 或 WHERE 是否筛掉未匹配行 |
| 比例全部 100% | 是否提前删掉分母需要的其他行 |
| 同类均价等于自己价格 | 是否按唯一编号关联 |
| 排名或累计不符合预期 | 排名规则、窗口顺序、范围和筛选层次 |
把问题分成语法、统计逻辑、输出名称与排序三类。先查看中间结果，每一步说清一行代表什么；文本检查通过不等于已在数据库执行。
维护方式：同一知识点继续修改原条目；完整题目进附录，学习记录进进度。此次整理前的三份原文件已保存到 archive 中的备份压缩包。
浙公网安备 33010602011771号