SQL 核心速查
SQLite 3.45.1 常用语句速查，涵盖筛选、连接、分组、窗口、数据写入、模式变更、事务和查询计划检查。
SQLite 3.45.1 打印为 1 页
读取并整理行
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; 扫描数据库中的结构和约束错误