二稿核验说明(2026-10-15):本篇按官方资料和示例代码修订。旧日志保留为“一稿留存输出”,不是本次复跑证据;本次结果见文末链接。版本与服务端配置需以实际环境为准。星河笔记、用户、订单均为虚构。
标注说明:【有据可查】 附官方文档 / 原始论文链接;【社区共识】 为业界普遍做法,没有唯一权威出处;【待考察/推测】 为未能核实或作者推断,请勿当作定论。
一句话定义
SQL声明查询和数据操作的目标,数据库根据语义与优化规则选择执行方式;具体语法、约束和事务行为依方言而异。
为什么重要
- 几乎所有业务数据最终都在关系库或仓库里;ETL、分析、评测报表都绕不开 SQL。
- 声明式意味着同一条 SQL 在不同数据量下由优化器选择不同计划(索引见 k5.data.db-index),这与命令式循环的思维不同(见 k1.prog.declarative)。
- 常见事故不是语法错,而是结果悄悄不对(
NULL三值逻辑)和安全漏洞(拼接导致注入)。下文实测这两种。
原理 / 图解
逻辑执行顺序(与书写顺序不同)【有据可查】PostgreSQL SELECT 文档:https://www.postgresql.org/docs/current/sql-select.html:
- WHERE vs HAVING:WHERE 在分组前过滤行;HAVING 在分组后过滤组。
- 窗口函数:
OVER (PARTITION BY … ORDER BY …)在不折叠行的情况下计算排名、累计和。 - NULL 与三值逻辑:普通等值或大小比较遇 NULL 常得到 UNKNOWN;IS NULL 等运算另有语义;
x NOT IN (…, NULL)永远不会为真。【有据可查】SQLite NULL 处理说明:https://www.sqlite.org/nulls.html - 参数化查询:值与 SQL 文本分开传给驱动,避免注入。【有据可查】OWASP SQL 注入防护:https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
可运行示例
SQLite 内存库,5 个用户、7 笔订单(虚构),依次演示约束、聚合、LEFT JOIN、CTE + 窗口函数和三个陷阱。(一稿运行记录,Python 内置 sqlite3)
"""SQL(实跑,SQLite 内存库):建表与约束 → JOIN / GROUP BY / HAVING → CTE 与窗口函数 → 三个经典陷阱:
NULL 与 NOT IN、COUNT(*) 与 COUNT(列)、字符串拼接 SQL 导致注入(对比参数化查询)。数据为虚构。"""
import sqlite3
con = sqlite3.connect(":memory:"); cur = con.cursor()
con.execute("PRAGMA foreign_keys = ON")
print("SQLite", sqlite3.sqlite_version)
cur.executescript("""
CREATE TABLE users (user_id TEXT PRIMARY KEY, name TEXT NOT NULL, plan TEXT CHECK (plan IN ('free','pro','team')), referrer TEXT);
CREATE TABLE orders (order_id TEXT PRIMARY KEY, user_id TEXT REFERENCES users(user_id), amount REAL CHECK (amount > 0), status TEXT, created_at TEXT);
INSERT INTO users VALUES ('U1','林小星','pro',NULL),('U2','周河','free','U1'),('U3','吴笔','team','U1'),('U4','郑记','free',NULL),('U5','王云','pro','U3');
INSERT INTO orders VALUES
('O1','U1',365,'paid','2026-10-01'),('O2','U1',60,'paid','2026-10-03'),('O3','U2',30,'refunded','2026-10-02'),
('O4','U3',1299,'paid','2026-10-02'),('O5','U3',199,'paid','2026-10-05'),('O6','U5',365,'paid','2026-10-06'),('O7','U5',12,'pending','2026-10-07');
""")
def q(sql, *a):
cur.execute(sql, a); cols = [d[0] for d in cur.description]; rows = cur.fetchall()
print(" " + " | ".join(cols)); [print(" " + " | ".join(str(x) for x in r)) for r in rows]; return rows
try: cur.execute("INSERT INTO orders VALUES ('O8','U1',-5,'paid','2026-10-08')")
except sqlite3.IntegrityError as e: print("约束拦截负金额:", e)
print("\n① 每个套餐的已付款收入(JOIN + GROUP BY + HAVING)")
q("""SELECT u.plan, COUNT(*) AS n_orders, SUM(o.amount) AS revenue FROM orders o JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'paid' GROUP BY u.plan HAVING SUM(o.amount) > 100 ORDER BY revenue DESC""")
print("\n② LEFT JOIN:包括没下过单的用户")
q("""SELECT u.name, COUNT(o.order_id) AS n_orders, COALESCE(SUM(o.amount),0) AS total FROM users u LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id ORDER BY total DESC""")
print("\n③ CTE + 窗口函数:每个用户最近一笔订单,以及按日累计收入")
q("""WITH ranked AS (SELECT user_id, order_id, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders)
SELECT user_id, order_id, created_at FROM ranked WHERE rn = 1 ORDER BY user_id""")
q("""SELECT created_at, SUM(amount) AS day_rev, SUM(SUM(amount)) OVER (ORDER BY created_at) AS cum_rev
FROM orders WHERE status='paid' GROUP BY created_at""")
print("\n④ 陷阱 1:NOT IN 遇到 NULL —— 找出“没有推荐过任何人”的用户")
r1 = q("SELECT name FROM users WHERE user_id NOT IN (SELECT referrer FROM users)")
r2 = q("SELECT name FROM users u WHERE NOT EXISTS (SELECT 1 FROM users x WHERE x.referrer = u.user_id)")
print(f" NOT IN 返回 {len(r1)} 行(子查询含 NULL,条件变成 UNKNOWN);NOT EXISTS 返回 {len(r2)} 行")
print("\n⑤ 陷阱 2:COUNT(*) 与 COUNT(列)")
q("SELECT COUNT(*) AS all_rows, COUNT(referrer) AS non_null_referrer FROM users")
print("\n⑥ 陷阱 3:SQL 注入")
evil = "x' OR '1'='1"
cur.execute(f"SELECT COUNT(*) FROM orders WHERE user_id = '{evil}'"); print(" 字符串拼接:返回订单数", cur.fetchone()[0], "(攻击者看到了全部订单)")
cur.execute("SELECT COUNT(*) FROM orders WHERE user_id = ?", (evil,)); print(" 参数化查询:返回订单数", cur.fetchone()[0])
一稿留存输出(未随二稿代码重生成):
SQLite 3.53.1
约束拦截负金额: CHECK constraint failed: amount > 0
① 每个套餐的已付款收入(JOIN + GROUP BY + HAVING)
plan | n_orders | revenue
team | 2 | 1498.0
pro | 3 | 790.0
② LEFT JOIN:包括没下过单的用户
name | n_orders | total
吴笔 | 2 | 1498.0
林小星 | 2 | 425.0
王云 | 2 | 377.0
周河 | 1 | 30.0
郑记 | 0 | 0
③ CTE + 窗口函数:每个用户最近一笔订单,以及按日累计收入
user_id | order_id | created_at
U1 | O2 | 2026-10-03
U2 | O3 | 2026-10-02
U3 | O5 | 2026-10-05
U5 | O7 | 2026-10-07
created_at | day_rev | cum_rev
2026-10-01 | 365.0 | 365.0
2026-10-02 | 1299.0 | 1664.0
2026-10-03 | 60.0 | 1724.0
2026-10-05 | 199.0 | 1923.0
2026-10-06 | 365.0 | 2288.0
④ 陷阱 1:NOT IN 遇到 NULL —— 找出“没有推荐过任何人”的用户
name
name
周河
郑记
王云
NOT IN 返回 0 行(子查询含 NULL,条件变成 UNKNOWN);NOT EXISTS 返回 3 行
⑤ 陷阱 2:COUNT(*) 与 COUNT(列)
all_rows | non_null_referrer
5 | 3
⑥ 陷阱 3:SQL 注入
字符串拼接:返回订单数 7 (攻击者看到了全部订单)
参数化查询:返回订单数 0
- 约束:
CHECK (amount > 0)在写入时拦下负金额;它单独不排除 NULL,需另加 NOT NULL。本例二稿显式 PRAGMA foreign_keys=ON 以启用 SQLite 外键检查。 - ① team 套餐 2 单 1498.0,pro 3 单 790.0;free 只有一笔退款,被 WHERE 先过滤,组不存在。
- ② LEFT JOIN 让没下过单的“郑记”也出现(0 单、0 元);用
COUNT(o.order_id)而非COUNT(*),否则会被算成 1 单。 - ③
ROW_NUMBER()在本例时间无并列时取每人最近一单;真实数据需用稳定唯一键打破并列;SUM(SUM(amount)) OVER (ORDER BY created_at)得到累计收入 2288.0。 - ④ NOT IN 陷阱:
referrer列含 NULL,NOT IN返回 0 行;NOT EXISTS正确返回 3 行(周河、郑记、王云)。查询不报错,只是结果错。 - ⑤
COUNT(*)= 5,COUNT(referrer)= 3。 - ⑥ 注入:拼接
x' OR '1'='1后返回全部 7 笔订单;参数化查询返回 0。
常见误区
- 用 NOT IN 处理可能含 NULL 的子查询:改用 NOT EXISTS 或先过滤 NULL。【社区共识】
- 字符串拼接 SQL:哪怕是“内部工具”,也要参数化。LLM 生成 SQL 时尤其要注意(参数占位符主要绑定值,不绑定任意表名、排序表达式或整段生成SQL;Text-to-SQL还需语句、标识符和权限约束)。
- SELECT * 上生产:列变更会破坏下游,也读了多余数据。
- 在 WHERE 里对索引列套函数:可能不能直接使用普通列索引;表达式索引、优化器改写等需另查计划(见 k5.data.db-index)。
- 以为方言都一样:upsert、日期函数、字符串拼接在 SQLite / PostgreSQL / MySQL 间写法不同(见 k5.data.postgresql、k5.data.mysql)。
与 AI 开发的关系
- Text-to-SQL / 数据分析 Agent:模型写 SQL,应用要做只读权限、超时、行数限制,并对结果做校验;本篇的 NULL 与注入陷阱是最常见的失败点。
- RAG 的元数据过滤、评测结果分析、成本报表,背后都是 SQL(仓库侧见 k5.data.warehouse)。
延伸阅读
- SQLite 窗口函数:https://www.sqlite.org/windowfunctions.html
- PostgreSQL 教程:https://www.postgresql.org/docs/current/tutorial.html
- Python sqlite3 文档(参数占位符):https://docs.python.org/3/library/sqlite3.html
二稿边界与自测
逻辑处理顺序不是物理执行计划。普通等值或大小比较遇NULL常得到UNKNOWN,但IS NULL等运算有不同语义;CHECK也不自动排除NULL。参数化解决值的转义,表名等标识符与任意模型生成SQL仍需单独约束。
自测:CHECK(amount>0)能拒绝NULL金额吗?
参考答案:通常不能仅靠这个CHECK,UNKNOWN可通过检查;需要NOT NULL等约束,并核对目标数据库规则。
本次修订与核验
- 启用SQLite外键检查,说明REFERENCES与PRAGMA的关系
- 纠正对索引列套函数必失效的绝对断言
- 窗口最新订单须定义并列时的稳定排序
本次在本地CPU环境执行修订脚本,退出码为0;运行日志。数字是示例结果,不是产品性能保证。
官方资料核验(2026-10-15)
-
sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.15.0 documentation
-
SQLite CREATE TABLE constraints:CHECK表达式结果NULL不构成违反,拒绝NULL需NOT NULL;外键检查需显式配置。
未覆盖与待补查
- 只在SQLite运行,不把输出作为PostgreSQL/MySQL逐项兼容性证明
- Text-to-SQL只读权限也不能替代资源限制与数据授权
本次示例运行范围
2026-10-15:通过。SQLite内存SQL、外键、NULL和参数绑定示例。
“一稿留存输出”保留原字,不是二稿代码新生成的结果;其原结论须结合二稿修订与边界阅读。
配套代码与核验资料