SQL 速查手册
全部
SQL | 说明 |
|---|
SELECT * FROM users; | 查询表中所有列所有行 |
SELECT id, name FROM users; | 只查询指定列 |
SELECT name AS user_name FROM users; | 列别名（AS 可省略） |
SELECT DISTINCT city FROM users; | 去重查询 |
SELECT * FROM users WHERE name = 'Tom'; | 等值条件查询 |
SELECT * FROM users LIMIT 10 OFFSET 20; | 分页：跳过 20 条取 10 条 |
SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id = u.id; | 表别名 + 连接查询 |
SELECT '固定值' AS label, NOW() AS ts; | SELECT 可直接输出常量与函数结果 |
SELECT COUNT(*) FROM users; | 统计行数 |
SELECT VERSION(); | 查看数据库版本 |
SELECT * FROM users WHERE age > 18 AND city = '北京'; | AND 组合条件 |
SELECT * FROM users WHERE city IN ('北京', '上海'); | IN 匹配多个值 |
SELECT * FROM users WHERE age BETWEEN 18 AND 30; | BETWEEN 闭区间范围 |
SELECT * FROM users WHERE name LIKE '张%'; | LIKE 模糊匹配，% 任意多字符 |
SELECT * FROM users WHERE name LIKE '_三'; | _ 匹配单个字符 |
SELECT * FROM users WHERE email IS NULL; | 判空必须用 IS NULL（= NULL 恒为假） |
SELECT * FROM users WHERE NOT (age < 18); | NOT 取反 |
SELECT * FROM users ORDER BY age DESC, id ASC; | 多列排序 |
SELECT * FROM users ORDER BY created_at DESC LIMIT 5; | 取最新 5 条 |
SELECT city, COUNT(*) AS cnt FROM users GROUP BY city; | 按城市分组计数 |
SELECT AVG(age), MIN(age), MAX(age) FROM users; | 平均值/最小值/最大值 |
SELECT SUM(amount) FROM orders WHERE user_id = 1; | 求和 |
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10; | HAVING 过滤分组结果（WHERE 不能用聚合） |
SELECT COUNT(DISTINCT city) FROM users; | 去重计数 |
SELECT COUNT(*), COUNT(email) FROM users; | COUNT(*) 算所有行，COUNT(列) 忽略 NULL |
SELECT city, GROUP_CONCAT(name) FROM users GROUP BY city; | MySQL：组内拼接字符串（PG 用 STRING_AGG） |
SELECT STRING_AGG(name, ",") FROM users; | PostgreSQL：组内拼接字符串 |
SELECT CASE WHEN age < 18 THEN '未成年' ELSE '成年' END AS stage FROM users; | CASE WHEN 条件表达式 |
SELECT * FROM a INNER JOIN b ON a.id = b.a_id; | 内连接：只返回两表都匹配的行 |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id; | 左连接：左表全保留，右表无匹配填 NULL |
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id; | 右连接：右表全保留 |
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id; | 全外连接（MySQL 不支持，可用 UNION 模拟） |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL; | 左反连接：左表有、右表没有的行 |
SELECT * FROM a CROSS JOIN b; | 笛卡尔积：所有行两两组合 |
SELECT * FROM users u JOIN orders o USING (user_id); | USING：同名列连接，结果列不重复 |
SELECT * FROM emp e JOIN emp m ON e.manager_id = m.id; | 自连接：同一张表按不同角色连接 |
SELECT * FROM a NATURAL JOIN b; | 自然连接：按所有同名列自动连接（慎用） |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id AND b.status = 1; | 连接条件放 ON：左表行仍全部保留 |
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); | IN 子查询 |
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); | EXISTS 相关子查询（大表常比 IN 快） |
SELECT * FROM (SELECT city, COUNT(*) c FROM users GROUP BY city) t WHERE c > 10; | 派生表：子查询作临时表（必须有别名） |
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt FROM users u; | 标量子查询作一列 |
WITH t AS (SELECT * FROM users WHERE age > 18) SELECT * FROM t; | WITH 定义 CTE，提升可读性 |
WITH a AS (SELECT ...), b AS (SELECT ... FROM a) SELECT * FROM b; | 多个 CTE 可相互引用 |
WITH RECURSIVE dept_tree AS (SELECT id, parent_id, name FROM dept WHERE id = 1 UNION ALL SELECT d.id, d.parent_id, d.name FROM dept d JOIN dept_tree t ON d.parent_id = t.id) SELECT * FROM dept_tree; | 递归 CTE 查组织架构树 |
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users); | 与整体平均值比较 |
SELECT name, city, ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS rn FROM users; | 组内编号：分区排序后给序号（1,2,3 不重复） |
SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM exam; | 排名：并列同号且跳号（1,1,3） |
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM exam; | 密集排名：并列同号不跳号（1,1,2） |
SELECT name, score, LAG(score) OVER (ORDER BY day) FROM daily; | 取上一行值（同比环比常用） |
SELECT name, score, LEAD(score, 1, 0) OVER (ORDER BY day) FROM daily; | 取下一行值，可指定默认值 |
SELECT name, SUM(amount) OVER (PARTITION BY city) AS city_total FROM orders; | 分组聚合但不折叠行 |
SELECT name, SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum FROM orders; | 累计求和（滑动/累计窗口） |
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY created_at DESC) rn FROM users) t WHERE rn = 1; | 经典：每组取最新一条 |
SELECT name, AVG(score) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM exam; | 移动平均（3 行窗口） |
INSERT INTO users (name, age) VALUES ('Tom', 20); | 插入单行 |
INSERT INTO users (name, age) VALUES ('Tom', 20), ('Jerry', 22); | 批量插入多行 |
INSERT INTO users (name, age) VALUES ('Tom', 20) RETURNING id; | PostgreSQL：插入后返回生成的 id |
INSERT INTO users (id, name) VALUES (1, 'Tom') ON DUPLICATE KEY UPDATE name = VALUES(name); | MySQL UPSERT：存在则更新 |
INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; | PostgreSQL UPSERT：冲突则更新 |
INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT (id) DO NOTHING; | PostgreSQL：冲突则忽略 |
UPDATE users SET age = age + 1 WHERE city = "北京"; | 条件更新（不带 WHERE 会全表更新！） |
DELETE FROM users WHERE created_at < "2020-01-01"; | 条件删除（不带 WHERE 会清空表！） |
TRUNCATE TABLE users; | 快速清空表并重置自增（不可回滚部分场景） |
BEGIN; UPDATE ...; COMMIT; | 事务：BEGIN 后执行，COMMIT 提交 |
BEGIN; UPDATE ...; ROLLBACK; | 回滚事务，撤销所有未提交修改 |
CREATE TABLE users (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP); | 建表：主键自增/非空/唯一/默认值 |
CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id)); | 外键约束 |
CREATE TABLE users (id BINARY(16) PRIMARY KEY); | MySQL 8 也可用 UUID 作主键（存 BINARY(16)） |
ALTER TABLE users ADD COLUMN phone VARCHAR(20); | 加列 |
ALTER TABLE users DROP COLUMN phone; | 删列 |
ALTER TABLE users MODIFY COLUMN age INT NOT NULL DEFAULT 0; | MySQL：修改列定义 |
ALTER TABLE users RENAME TO members; | 重命名表 |
DROP TABLE IF EXISTS users; | 删表（IF EXISTS 防报错） |
CREATE TABLE t (status VARCHAR(10) CHECK (status IN ('active', 'disabled'))); | CHECK 约束限制取值 |
CREATE INDEX idx_users_city ON users (city); | 为 WHERE 高频列建索引 |
CREATE UNIQUE INDEX idx_users_email ON users (email); | 唯一索引：兼做约束与加速 |
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at); | 联合索引：遵循最左前缀原则 |
CREATE INDEX idx_users_name_lower ON users (LOWER(name)); | 函数索引（PG/MySQL 8+） |
DROP INDEX idx_users_city ON users; | 删除索引 |
EXPLAIN SELECT * FROM users WHERE city = "北京"; | 查看执行计划，关注 type/rows/key |
EXPLAIN ANALYZE SELECT ...; | 真实执行并返回耗时（MySQL 8/PG 均支持） |
SELECT * FROM orders WHERE YEAR(created_at) = 2024; | 反例：列上用函数导致索引失效 |
SELECT * FROM users WHERE phone LIKE "%8888"; | 反例：前置 % 模糊查询无法走索引 |
SELECT COALESCE(nickname, name, "匿名") FROM users; | 返回第一个非 NULL 的值 |
SELECT IFNULL(NULL, 0); | MySQL：NULL 时返回默认值（PG 用 COALESCE） |
SELECT NULLIF(a, b); | a=b 时返回 NULL（常用于防除零：a/NULLIF(b,0)） |
SELECT ROUND(price, 2), CEIL(x), FLOOR(x), ABS(x) FROM t; | 四舍五入/向上/向下取整/绝对值 |
SELECT CONCAT(first_name, " ", last_name) FROM users; | 拼接字符串 |
SELECT SUBSTRING(name, 1, 3), LENGTH(name), UPPER(name) FROM users; | 截取/长度/大写 |
SELECT REPLACE(url, "http://", "https://") FROM sites; | 字符串替换 |
SELECT TRIM(" abc "); | 去除首尾空格 |
SELECT DATE_FORMAT(created_at, "%Y-%m-%d %H:%i:%s") FROM users; | MySQL：日期格式化（PG 用 TO_CHAR） |
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); | MySQL：当前时间加 7 天 |
SELECT DATEDIFF("2024-06-01", "2024-05-01"); | MySQL：两个日期相差天数 |
SELECT EXTRACT(YEAR FROM created_at) FROM users; | 提取年/月/日（标准 SQL，PG 通用） |
SELECT NOW()::DATE; | PostgreSQL：类型转换简写（等价 CAST(NOW() AS DATE)） |
SELECT `name` FROM `user`; -- MySQL | MySQL 用反引号包裹保留字；PG 用双引号 "name" |
SELECT * FROM t LIMIT 10 OFFSET 5; -- MySQL/PG | SQL Server 写法：OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY |
SELECT * FROM t WHERE name ILIKE '%abc%'; -- PG | PG 专有：不区分大小写的 LIKE；MySQL 默认排序规则已不区分 |
SELECT * FROM t WHERE name ~ '^A'; -- PG | PG 支持正则匹配操作符 ~ |
SELECT id SERIAL PRIMARY KEY; -- PG | PG 自增列用 SERIAL/IDENTITY；MySQL 用 AUTO_INCREMENT |
SELECT TO_CHAR(now(), 'YYYY-MM-DD'); -- PG | PG 日期格式化用 TO_CHAR；MySQL 用 DATE_FORMAT |
SELECT * FROM t LIMIT 10; -- PG 分页取前10也可写 FETCH FIRST 10 ROWS ONLY | 标准 SQL 分页写法与 LIMIT 等价 |
SHOW TABLES; -- MySQL | PG 等价：\dt 或查 information_schema.tables |
点击任意行可复制 SQL；示例以 MySQL / PostgreSQL 为主，个别方言差异见「方言差异」分类