# SQL 核心速查

Source: https://codewiki.com/zh/cheatsheets/sql/

## 读取并整理行

- `SELECT order_id, total FROM orders;` — 从每行中只返回指定列
- `SELECT DISTINCT customer_id FROM orders;` — 删除投影结果中的重复行
- `SELECT customer_id, total AS order_total FROM orders;` — 明确命名结果列
- `SELECT * FROM orders ORDER BY total DESC NULLS LAST, order_id;` — 按总额倒序排列，把空值放在末尾并稳定处理并列
- `SELECT * FROM orders ORDER BY created_at DESC, order_id DESC LIMIT 20 OFFSET 40;` — 跳过排序后的前 40 行，再返回至多 20 行
- `SELECT CAST(total AS REAL) FROM orders;` — 把每个值转换为 SQLite 的实数存储类

## 筛选与处理空值

- `SELECT * FROM orders WHERE status = 'paid' AND total >= ?1;` — 用绑定的最低值筛选，避免拼接 SQL
- `SELECT * FROM orders WHERE shipped_at IS NULL;` — 用 `IS NULL` 匹配缺失值
- `SELECT * FROM orders WHERE customer_id IN (1, 3, 5);` — 匹配显式列表中的任意值
- `SELECT * FROM orders WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';` — 用左闭右开范围筛选九月的时间戳
- `SELECT COALESCE(discount, 0) FROM orders;` — 在结果中用零替换空折扣
- `SELECT CASE WHEN total >= 100 THEN 'large' ELSE 'small' END FROM orders;` — 按顺序匹配条件并映射每一行

## 连接与存在性判断

- `SELECT o.order_id, c.name FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id;` — 只保留有匹配客户的订单
- `SELECT c.name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id;` — 保留所有客户，包括没有订单的客户
- `SELECT * FROM colors CROSS JOIN sizes;` — 生成颜色与尺寸的所有组合
- `SELECT * FROM orders JOIN customers USING (customer_id);` — 按同名列连接，并只投影一次该列
- `SELECT * FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);` — 保留至少有一笔订单的客户
- `SELECT * FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);` — 保留没有匹配订单的客户，内层键可为空

## 分组与汇总

- `SELECT COUNT(*) FROM orders;` — 统计行数，不受行内空值影响
- `SELECT COUNT(shipped_at) FROM orders;` — 只统计发货时间非空的行
- `SELECT COUNT(DISTINCT customer_id) FROM orders;` — 统计不同的非空客户标识
- `SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id;` — 返回每位客户的一个总额
- `SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id HAVING SUM(total) >= 1000;` — 聚合完成后筛选分组
- `SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders;` — 统计匹配行，不移除其他聚合的输入

## 合并与嵌套查询

- `SELECT email FROM leads UNION SELECT email FROM customers;` — 合并结果并删除重复行
- `SELECT email FROM leads UNION ALL SELECT email FROM customers;` — 合并结果并保留重复行
- `SELECT email FROM leads INTERSECT SELECT email FROM customers;` — 保留两个结果中都有的行
- `SELECT email FROM leads EXCEPT SELECT email FROM customers;` — 保留左侧有而右侧没有的行
- `SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);` — 把每行与一个标量聚合结果比较
- `SELECT * FROM (SELECT * FROM orders WHERE status = 'paid') AS paid_orders;` — 查询在 `FROM` 中命名的派生表

## 分阶段处理与递归

- `WITH paid_orders AS (SELECT * FROM orders WHERE status = 'paid') SELECT * FROM paid_orders;` — 为紧随其后的语句命名一份查询结果
- `WITH paid AS (SELECT * FROM orders WHERE status = 'paid'), totals AS (SELECT customer_id, SUM(total) AS spent FROM paid GROUP BY customer_id) SELECT * FROM totals;` — 把一个 CTE 的结果传给下一个 CTE
- `WITH order_totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) SELECT c.name, t.spent FROM order_totals AS t JOIN customers AS c USING (customer_id);` — 把汇总后的 CTE 与明细数据连接
- `WITH RECURSIVE sequence(n) AS (VALUES (1) UNION ALL SELECT n + 1 FROM sequence WHERE n < 5) SELECT n FROM sequence;` — 用停止条件生成一到五的整数
- `WITH RECURSIVE tree(id, depth) AS (SELECT category_id, 0 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.category_id, t.depth + 1 FROM categories AS c JOIN tree AS t ON c.parent_id = t.id) SELECT * FROM tree;` — 从根节点开始遍历类别层次结构
- `VALUES (1, 'draft'), (2, 'paid');` — 直接构造一个两行两列的结果

## 窗口计算

- `SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS position FROM orders;` — 在每个客户分区内分配稳定位置
- `SELECT order_id, RANK() OVER (ORDER BY total DESC) AS place FROM orders;` — 相同总额使用相同排名，并在之后留出空位
- `SELECT order_id, DENSE_RANK() OVER (ORDER BY total DESC) AS tier FROM orders;` — 相同总额使用相同排名，之后不留空位
- `SELECT order_id, LAG(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS previous_total FROM orders;` — 读取每个分区内按顺序排列的前一个值
- `SELECT order_id, LEAD(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS next_total FROM orders;` — 读取每个分区内按顺序排列的后一个值
- `SELECT order_id, SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders;` — 用显式行框架计算累计总额

## 插入、更新与删除

- `INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3);` — 用绑定值和显式列列表插入一行
- `INSERT INTO statuses (status_id, name) VALUES (1, 'draft'), (2, 'paid');` — 用一条语句插入多行
- `INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2026-01-01';` — 插入查询返回的行
- `INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3) ON CONFLICT(customer_id) DO UPDATE SET name = excluded.name, email = excluded.email;` — 在指定的唯一性冲突发生时插入或更新
- `UPDATE orders SET status = 'paid' WHERE order_id = ?1 RETURNING order_id, status;` — 更新匹配行并返回新值
- `DELETE FROM orders WHERE order_id = ?1 RETURNING order_id;` — 删除匹配行并返回其标识

## 定义模式与索引

- `CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);` — 创建带标识、非空和唯一性约束的表
- `CREATE TABLE order_items (order_id INTEGER REFERENCES orders(order_id), sku TEXT, quantity INTEGER CHECK (quantity > 0), PRIMARY KEY (order_id, sku));` — 声明复合主键、外键和正数数量
- `ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1;` — 添加带默认值的非空列，以兼容已有行
- `CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);` — 为常见的等值筛选加排序访问路径建立索引
- `CREATE UNIQUE INDEX customers_email_idx ON customers (email) WHERE email IS NOT NULL;` — 只对非空电子邮件地址实施唯一性约束
- `DROP INDEX IF EXISTS orders_customer_created_idx;` — 仅在索引存在时将其删除

## 控制事务

- `BEGIN IMMEDIATE;` — 立即开始一个 SQLite 写事务
- `COMMIT;` — 提交并结束当前事务
- `ROLLBACK;` — 撤销当前事务
- `SAVEPOINT before_import;` — 标记一个嵌套回滚点
- `ROLLBACK TO before_import;` — 撤销保存点之后的工作，同时保留该保存点
- `RELEASE before_import;` — 删除保存点，但不结束外层事务

## 检查与维护

- `EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = ?1;` — 显示高层查询计划，但不运行该查询
- `ANALYZE;` — 刷新所有已附加模式的查询规划器统计信息
- `PRAGMA optimize;` — 针对当前工作负载执行推荐的优化器维护
- `PRAGMA foreign_keys = ON;` — 为当前连接启用外键约束
- `PRAGMA table_info('orders');` — 列出表的普通列及其核心属性
- `PRAGMA integrity_check;` — 扫描数据库中的结构和约束错误