SQL 速查表

SELECT id, name, email FROM users LIMIT 10 ;

查指定字段，最多返回若干行

显式列出字段而不是 `*`：少读用不上的列，也避免表结构变更后按位置读错列

SELECT * FROM users LIMIT 10 ;

看整行内容，排查时最常用

只适合临时排查：`*` 会把大字段（JSON / 长文本）一起读出来，也依赖列顺序

SELECT COUNT(*) FROM users WHERE status = 'active' ;

数一下符合条件的有多少行

COUNT(*) 数行；COUNT(列名) 只数那一列非 NULL 的行，两者结果可能不同

SELECT id, name, email FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

翻页：跳过前 20 行再取若干行

OFFSET 越大越慢（前面那些行仍要读出来再丢掉）；深翻页改用「记住上一页最后一条的排序键」

SELECT id AS user_id, name AS user_name FROM users LIMIT 10 ;

给字段起别名，让结果列名好认

别名只在结果集里生效，不影响表结构；`AS` 在多数库可省，但省掉后一旦拼错就变成两个列名，建议留着

SELECT CAST( id, name, email AS TEXT) FROM users LIMIT 10 ;

临时转换字段类型（这里转成文本）

Postgres 还可写 `列::text`；转换失败会直接报错，不会给个默认值

SELECT name || ' #' || id AS label FROM users LIMIT 10 ;

把多个字段拼成一个显示用的字符串

`||` 是标准写法（MySQL 默认要 `CONCAT()`，除非开了 PIPES_AS_CONCAT）；有 NULL 参与时整段变 NULL，用 COALESCE 兜一下

SELECT id, name, email FROM users UNION ALL SELECT id, name, email FROM orders ;

把两张表的查询结果摞在一起

`UNION ALL` 不去重、不排序，比 `UNION` 快得多；两边列数、类型、顺序必须对得上（列名以前一条为准）

SELECT id, name, email FROM users WHERE status = 'active' ;

按条件筛出需要的行

条件是 `=`、`>` 这类比较式的组合；字符串要加单引号，NULL 不能用 `=` 判（见「判空」那条）

SELECT id, name, email FROM users ORDER BY created_at DESC ;

按某个字段排序

不写 ORDER BY 时行的顺序是**不保证**的，别依赖「看起来是按插入顺序」；NULL 排在最前还是最后各库不同

SELECT id, name, email FROM users WHERE id IN (1, 2, 3);

在一组值里挑，等价于一串 OR

列表元素多到几百个时改写成 JOIN 临时表或 `= ANY(数组)` 更好；`NOT IN` 遇到列表里有 NULL 会一行都不返回

SELECT id, name, email FROM users WHERE name LIKE 'prefix%';

模糊匹配，这里找以某串开头的

`%` 放开头就用不上普通索引，全靠全表扫；`ILIKE` 是 Postgres 写法，MySQL 默认排序规则本来就不区分大小写

SELECT id, name, email FROM users WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

取一个区间（含两端）

BETWEEN 两端都含；写日期区间要小心时间部分 —— `<= 2026-01-31` 会漏掉当天 00:00 之后的记录，常用写法是 `< 下个月一号`

SELECT id, name, email FROM users WHERE deleted_at IS NULL;

判断字段是否为空值

NULL 表示「未知」，`= NULL` 永远不成立（结果是 NULL 而不是 true），只能用 IS NULL

SELECT id, name, email FROM users WHERE status = 'active' OR status = 'pending';

几个条件满足其一即可

AND 比 OR 先算：混着写必须加括号，否则条件会粘错

SELECT id, CASE WHEN status = 'active' THEN 1 ELSE 0 END AS is_active FROM users LIMIT 10 ;

按条件在结果里生成一个新字段

没写 ELSE 且条件都不满足时结果是 NULL（不是 0 也不是空串）；要做「分档统计」用它比写多个子查询省事

SELECT status, COUNT(*) AS cnt FROM users GROUP BY status ORDER BY cnt DESC;

按字段分组数数，看看每类各有多少

SELECT 里没被聚合的列都必须出现在 GROUP BY 里，否则报错（MySQL 宽松模式下会随便取一行，更危险）

SELECT status, COUNT(*) AS cnt FROM users GROUP BY status HAVING COUNT(*) > 10;

分组后按聚合结果再筛一遍

WHERE 在分组**之前**过滤行、不能用聚合函数；HAVING 在分组**之后**。能挪到 WHERE 的条件就挪过去，少算一堆行

SELECT SUM(amount) AS total, AVG(amount) AS avg_amount FROM users WHERE status = 'active' ;

求和与求平均

AVG / SUM 会跳过 NULL，分母是「非 NULL 的行数」；想按总行数算平均要用 `SUM(x) / COUNT(*)` 或 `AVG(COALESCE(x, 0))`

SELECT MIN(created_at) AS first_at, MAX(created_at) AS last_at FROM users ;

找最早与最晚的一条

MIN/MAX 只给值不给那一行的其他字段；要连着其他列一起看，用 `ORDER BY … LIMIT 1` 或窗口函数取序号

SELECT COUNT(DISTINCT user_id) AS users FROM users ;

数不重复的取值有几个（去重统计）

COUNT(DISTINCT) 在大表上很贵（要排序或哈希去重）；只要近似值可用预估行数（Postgres 的 `EXPLAIN`）替代

SELECT id, ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at DESC ) AS rn FROM users ;

组内排名，给每一行编个序号

窗口函数不改行数（不像 GROUP BY 会合并行）；MySQL 8 / Postgres / SQL Server 支持，5.7 与更老的库没有

SELECT * FROM (SELECT id, name, email , ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at DESC ) AS rn FROM users ) t WHERE t.rn = 1;

取每组里最新（或排序最靠前）的那一条

必须套一层子查询再筛 rn —— 窗口函数的结果不能写在同一层的 WHERE 里（那时它还没算出来）

SELECT status, STRING_AGG(name, ', ') AS names FROM users GROUP BY status;

把一组的多个值拼成一个字符串

两者默认都不保证拼接顺序，要固定顺序得在聚合里写 `ORDER BY`（Postgres）；结果串有长度上限

SELECT id, name, email FROM users JOIN orders ON orders.user_id = users.id ;

只保留两张表都能对上的行

`JOIN` 就是 `INNER JOIN`；两张表都有的列名要写全（`用户表.id`），否则报「列名有歧义」

SELECT id, name, email FROM users LEFT JOIN orders ON orders.user_id = users.id ;

左连接：主表全保留，关联表没对上就补空

关联表的条件写在 `ON` 上和写在 `WHERE` 上结果不同：写进 `WHERE` 会把补 NULL 的行滤掉，左连接退化成内连接

SELECT id, name, email FROM users RIGHT JOIN orders ON orders.user_id = users.id ;

右连接：关联表全保留

`A RIGHT JOIN B` 等于 `B LEFT JOIN A`：把两张表调个个儿写 LEFT JOIN，读起来清楚得多，实际很少用 RIGHT

SELECT id, name, email FROM users FULL OUTER JOIN orders ON orders.user_id = users.id ;

两边都保留，对不上的地方补空

Postgres / SQL Server 支持；**MySQL 没有 FULL OUTER JOIN**，要用 LEFT JOIN 与 RIGHT JOIN 的结果 UNION

SELECT id, name, email FROM users JOIN orders USING (id);

同名字段做连接，少写一遍条件

USING 的列在结果里只出现一次（不像 `ON a.id = b.id` 会出现两列）；跨库移植性不如 ON

SELECT id, name, email FROM users CROSS JOIN orders LIMIT 10 ;

笛卡尔积：每行都与另一张表的每行配一次

结果行数是两边行数之积，几张万行表一交叉就是几亿行 —— 写漏连接条件时也会变成这个，看到结果行数暴涨先查 ON

SELECT child.id, parent.id AS parent_id FROM users child JOIN users parent ON child.parent_id = parent.id;

同一张表自己连自己（层级 / 推荐关系）

必须给同一张表起两个不同别名，否则第二处引用报错；层级很深时用递归 CTE

WITH recent AS (SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL '7 days') SELECT * FROM recent;

把一段查询提成带名字的临时结果，后面反复用

CTE 比嵌套子查询好读，也便于分段调试；`INTERVAL` 的写法各库不同（MySQL 用 `DATE_SUB(NOW(), INTERVAL 7 DAY)`）

SELECT id, name, email FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id );

只要另一张表里存在对应行就保留

`EXISTS` 只判断有没有，子查询里写 `SELECT 1` 就够；它找到一行就停，通常比 `IN (子查询)` 更友好

SELECT id, name, email FROM users WHERE id IN (SELECT user_id FROM orders );

把另一张表的查询结果当成条件值列表

子查询结果里若有 NULL，`NOT IN` 会一行都不返回（这是最常踩的一条）；`IN` 与 `EXISTS` 优化器常会改写成同一种计划

SELECT t.id FROM (SELECT id, status FROM users WHERE status = 'active' ) t LIMIT 10 ;

把子查询当成一张临时表再用它查

派生表必须有别名（这里 `t`），否则报语法错；多层嵌套 + UNION 时优先改成 CTE

SELECT id, name, email , (SELECT COUNT(*) FROM orders ) AS total FROM users LIMIT 10 ;

子查询返回单个值时，可以当成一个字段用

子查询必须只返回一行一列，多了直接报错；它对外层每一行都要算一次，放在 SELECT 里对性能不友好

SELECT id, name, email FROM users t, LATERAL (SELECT * FROM orders WHERE orders.user_id = users.id LIMIT 1) x;

让子查询引用外层当前行（每行单独取一条关联记录）

Postgres / MySQL 8.0.14+ 才支持；要用它解决「每组取最新一条」时，窗口函数通常更通用

WITH RECURSIVE tree AS (SELECT id, parent_id FROM users WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id FROM users c JOIN tree ON c.parent_id = tree.id) SELECT * FROM tree;

递归展开层级数据（组织树、分类树）

⚠️ 数据里有环或条件写错会**一直递归下去**，把连接和磁盘吃满 —— 先在只读副本上试，或加深度上限（`WHERE depth < 10`）

CREATE TABLE users (id BIGINT PRIMARY KEY, name TEXT NOT NULL, status TEXT DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

建一张新表，带上主键、非空与默认值

主键别用业务字段（手机号会改、身份证涉隐私）；`BIGINT` 而不是 `INT`，避免自增到 21 亿后翻车

ALTER TABLE users ADD COLUMN remark TEXT DEFAULT '';

给已有表加一列

带默认值加列在部分库里会重写整张表（大表要选低峰期）；新列默认允许 NULL，要非空得补 NOT NULL 与默认值

ALTER TABLE users ALTER COLUMN id TYPE BIGINT;

改字段类型

收窄类型（大变小）或改字符集可能锁表重写数据；MySQL 的 MODIFY 必须把列定义整段重写一遍，漏掉的属性会被丢掉

ALTER TABLE users DROP COLUMN remark;

删掉一列

⚠️ 列里的数据一并删除且**不可回滚**。先确认没有代码在读它：`DROP COLUMN` 在 Postgres 上是立即生效的（MySQL 8 也是），生产上建议先改名、观察一周再删

DROP TABLE users ;

整张表连结构一起删掉

⚠️ 表与数据一起消失，事务里未必能回滚（DDL 在多数库是隐式提交）。Postgres 可加 `IF EXISTS` 避免「表不存在」报错，但不会帮你确认「删对了表」

TRUNCATE TABLE users ;

清空表里的所有行，保留表结构

⚠️ 比 `DELETE FROM` 快得多（不逐行记日志）但也更狠：多数库**不可回滚**，还会重置自增列、连带清掉级联引用。执行前先 `SELECT COUNT(*)` 确认是哪张表

CREATE OR REPLACE VIEW v_ users AS SELECT id, name, email FROM users WHERE status = 'active' ;

把常用查询存成一个视图

视图不存数据、每次查询展开原表；`OR REPLACE` 方便迭代，但列名或类型变化时可能报错，需要先 DROP 再建

COMMENT ON COLUMN users .status IS '订单状态';

给表或字段加注释说明

Postgres / Oracle 用独立的 COMMENT 语句；MySQL 把注释写在列定义里（`status TEXT COMMENT '订单状态'`），改注释要 MODIFY COLUMN

CREATE INDEX idx_users_email ON users ( id, name, email );

给常用筛选 / 排序字段建索引

索引让查询快、写入慢（每次写都要维护索引）；等值条件在前、范围条件在后，顺序反了用不上

CREATE INDEX CONCURRENTLY idx_users_email ON users ( id, name, email );

不锁表地建索引（大表上线的写法）

Postgres 专属，且不能在事务块里执行、失败会留一个「无效索引」需手动删；MySQL 对应写法是 `ALTER TABLE … ADD INDEX …, ALGORITHM=INPLACE, LOCK=NONE`

DROP INDEX idx_users_email ;

删掉一个索引

⚠️ 删掉后依赖它的查询会突然变慢（甚至拖垮库），先看有没有慢查询在用；MySQL 必须写成 `DROP INDEX 索引名 ON 表名`，Postgres 不许加表名

EXPLAIN SELECT id, name, email FROM users WHERE status = 'active' ;

看优化器打算怎么执行这条语句

关注三件事：扫了多少行（rows）、走的是索引还是全表（Seq Scan / ALL）、有没有临时排序（Sort / Using filesort）

EXPLAIN ANALYZE SELECT id, name, email FROM users WHERE status = 'active' ;

真的跑一遍，给每个步骤的实际耗时与行数

⚠️ `ANALYZE` 会**真正执行**这条语句：写操作要先包在事务里再 ROLLBACK，或有条件时加 `BEGIN; … ROLLBACK;` 包起来

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = ' users ';

看一张表上已有哪些索引

`pg_indexes` 是 Postgres 的系统视图；MySQL 用 `SHOW INDEX FROM 表名`。看结构时用 `SHOW CREATE TABLE` 最直观

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = ' users ' ORDER BY idx_scan;

看索引到底有没有被用到（用来找多余索引）

Postgres 专属；`idx_scan` 很小（尤其为 0）的索引用不上却仍在拖慢写入，可考虑删。统计从上次重置起算，进程重启也会清零

SELECT pg_size_pretty(pg_total_relation_size(' users ')) AS total;

看一张表连索引一共占多大

Postgres 的 `pg_*size` 系列函数；MySQL 用 `SELECT data_length, index_length FROM information_schema.tables WHERE table_name = '表名'`

BEGIN;

开始一个事务，之后的语句要么一起成功、要么一起回滚

事务**开着不提交**会一直占着锁与被改的行，排查时优先看有没有卡在 idle in transaction 的会话

ROLLBACK;

撤销当前事务里的所有改动

只能撤销**当前事务**里的改动；事务已提交就回不去了（要靠备份或反向补数据）

COMMIT;

提交事务，让改动生效

提交之后才真正落盘（并开始占用 WAL / binlog）；`COMMIT` 之后再写 `ROLLBACK` 不会有任何效果

SAVEPOINT sp1;

在事务里打一个存档点，可只回滚到这一点

适合「批量写入里某一条错了但其余的想保留」：整段仍在同一个事务里，最后还是要 COMMIT 才生效

SELECT * FROM users WHERE id = 1 FOR UPDATE;

锁住这一行，别的事务改不了（读-改-写的经典写法）

⚠️ 锁在**事务提交前一直有效**：忘提交就把这一行卡死，别的事务会排队等（`lock_timeout` 到了就报错）。锁的粒度看有没有走索引，没走索引会升级成锁全表行

SELECT * FROM users WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

抢一条待处理任务，被别人占着就跳过（任务队列写法）

`SKIP LOCKED` 让并发消费者各拿各的任务而不互相等；MySQL 8 / Postgres 9.5+ 支持。取到的这条在提交前对别人不可见

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置当前事务的隔离级别

Postgres 默认 READ COMMITTED，MySQL InnoDB 默认 REPEATABLE READ；级别越高一致性越强、冲突与重试越多。这个词的**语义在各库并不完全一致**

SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE datname = ' app_db ';

看当前有哪些连接在干什么（谁卡住了）

Postgres 看 `pg_stat_activity`（`wait_event_type = 'Lock'` 就是卡在等锁），MySQL 用 `SHOW FULL PROCESSLIST` 或 `information_schema.innodb_trx`

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = ' public ' ORDER BY table_name;

列出这个库里有哪些表

`information_schema` 是标准视图、各库都有（MySQL 的 `table_schema` 就是库名）；想连大小一起看用各库自己的系统表

SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = ' users ' ORDER BY ordinal_position;

看一张表的字段、类型、是否可空与默认值

`ordinal_position` 是列的物理顺序；同名表出现在多个 schema 时会都列出来，加上 `table_schema` 条件更准

SELECT index_name, column_name, seq_in_index FROM information_schema.statistics WHERE table_name = ' users ' ORDER BY index_name, seq_in_index;

看索引由哪些列按什么顺序组成

`seq_in_index` 就是组合索引里的顺序 —— 判断「这个条件能不能用上索引」要靠它，只看索引名看不出顺序

SELECT count(*) AS conns FROM pg_stat_activity WHERE datname = ' app_db ';

数当前有多少连接，排查连接数打满

连接数满通常不是「人太多」而是**连接没释放**（应用没关连接池 / 卡住的事务占着）；调大上限前先看 `state` 那一列

SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10 ;

按平均耗时排出最慢的语句

Postgres 要先 `CREATE EXTENSION pg_stat_statements` 并重启；统计口径是「同一种语句模板合并」，看 `mean_exec_time` 也要看 `calls`

SELECT pg_terminate_backend(12345);

踢掉一个卡住的连接（进程号从 pg_stat_activity 里拿）

⚠️ 直接掐断连接：**它正在跑的事务会回滚**，客户端会收到连接中断。先确认 `state` / `query` 是不是真该杀；MySQL 对应 `KILL 12345;`

VACUUM ANALYZE users ;

回收垃圾行并更新统计信息，让优化器选对计划

Postgres 的 `VACUUM` 清理失效行、`ANALYZE` 更新统计（两条常一起跑）；MySQL 的 `ANALYZE TABLE` 只更新统计、不回收空间（要 `OPTIMIZE TABLE`，会锁表）

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = ' users ';

看谁对这张表有什么权限

报「权限不足」时先确认 `current_user` 到底是哪个账号（应用连接池里配的常不是你以为的那个）；`information_schema` 只列当前用户看得见的授权