SQL(结构化查询语言)

SQL声明查询和数据操作的目标,数据库根据语义与优化规则选择执行方式;具体语法、约束和事务行为依方言而异。

二稿核验说明(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:

图 1
  • 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)。

延伸阅读

二稿边界与自测

逻辑处理顺序不是物理执行计划。普通等值或大小比较遇NULL常得到UNKNOWN,但IS NULL等运算有不同语义;CHECK也不自动排除NULL。参数化解决值的转义,表名等标识符与任意模型生成SQL仍需单独约束。

自测:CHECK(amount>0)能拒绝NULL金额吗?

参考答案:通常不能仅靠这个CHECK,UNKNOWN可通过检查;需要NOT NULL等约束,并核对目标数据库规则。

本次修订与核验

  • 启用SQLite外键检查,说明REFERENCES与PRAGMA的关系
  • 纠正对索引列套函数必失效的绝对断言
  • 窗口最新订单须定义并列时的稳定排序

本次在本地CPU环境执行修订脚本,退出码为0;运行日志。数字是示例结果,不是产品性能保证。

官方资料核验(2026-10-15)

未覆盖与待补查

  • 只在SQLite运行,不把输出作为PostgreSQL/MySQL逐项兼容性证明
  • Text-to-SQL只读权限也不能替代资源限制与数据授权

本次示例运行范围

2026-10-15:通过。SQLite内存SQL、外键、NULL和参数绑定示例。

查看本次日志。

“一稿留存输出”保留原字,不是二稿代码新生成的结果;其原结论须结合二稿修订与边界阅读。

配套代码与核验资料

继续浏览基础 返回知识 →返回顶部 ↑
本篇目录
教程图解100%
教程图解