SQL 命令速查
常用 SQL 命令快速查询，覆盖查询、连接、窗口函数、事务等场景，点击命令可复制
搜索命令
基础查询
SELECT * FROM table_name;查询表中所有数据SELECT col1, col2 FROM table_name;查询指定列SELECT DISTINCT col FROM table_name;去除重复值SELECT col AS alias FROM table_name;列别名SELECT * FROM table_name WHERE condition;条件查询SELECT * FROM table_name WHERE col = value;等于条件SELECT * FROM table_name WHERE col IN (v1, v2);IN 条件（匹配多个值）SELECT * FROM table_name WHERE col BETWEEN a AND b;范围条件（包含边界）SELECT * FROM table_name WHERE col LIKE 'pattern%';模糊匹配（% 代表任意字符）SELECT * FROM table_name WHERE col LIKE '_attern';模糊匹配（_ 代表单个字符）SELECT * FROM table_name WHERE col IS NULL;查询空值SELECT * FROM table_name WHERE col IS NOT NULL;查询非空值SELECT * FROM table_name WHERE col1 AND col2;多条件与SELECT * FROM table_name WHERE col1 OR col2;多条件或SELECT * FROM table_name ORDER BY col ASC;升序排序SELECT * FROM table_name ORDER BY col DESC;降序排序SELECT * FROM table_name ORDER BY col1 DESC, col2 ASC;多列排序SELECT * FROM table_name LIMIT 10;限制返回行数SELECT * FROM table_name LIMIT 10 OFFSET 20;分页查询（跳过20行取10行）SELECT * FROM table_name LIMIT 10 OFFSET (page-1)*10;通用分页公式数据操作 (DML)
INSERT INTO table_name (col1, col2) VALUES (v1, v2);插入单行数据INSERT INTO table_name VALUES (v1, v2, v3);插入完整行（按列顺序）INSERT INTO table_name (col1) VALUES (v1), (v2), (v3);插入多行数据INSERT INTO table1 (col) SELECT col FROM table2;从查询结果插入UPDATE table_name SET col = value WHERE condition;更新数据（务必加WHERE）UPDATE table_name SET col1 = v1, col2 = v2 WHERE id = 1;更新多列UPDATE table_name SET col = col + 1 WHERE condition;基于当前值更新DELETE FROM table_name WHERE condition;删除数据（务必加WHERE）DELETE FROM table_name;删除所有数据（不清空自增ID）TRUNCATE TABLE table_name;清空表（重置自增ID，更快）REPLACE INTO table_name (id, col) VALUES (1, 'new');插入或替换（MySQL）INSERT ... ON DUPLICATE KEY UPDATE col=VALUES(col);插入冲突时更新（MySQL）INSERT INTO ... ON CONFLICT (id) DO UPDATE SET col=EXCLUDED.col;插入冲突时更新（PostgreSQL）连接查询 (JOIN)
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.fk;内连接（只返回匹配的行）SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.fk;左连接（保留左表所有行）SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.fk;右连接（保留右表所有行）SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.fk;全外连接（保留两表所有行）SELECT * FROM t1 CROSS JOIN t2;交叉连接（笛卡尔积）SELECT * FROM t1 JOIN t2 USING (common_col);使用同名列连接SELECT * FROM t1 a JOIN t2 b ON a.id = b.fk;表别名简化查询SELECT * FROM t1 JOIN t2 ON ... JOIN t3 ON ...;多表连接SELECT * FROM t1 LEFT JOIN t2 ON ... WHERE t2.id IS NULL;查找未匹配的行（反连接）SELECT * FROM t1 AS a JOIN t1 AS b ON a.parent = b.id;自连接（同一表连接）聚合与分组
SELECT COUNT(*) FROM table_name;统计行数SELECT COUNT(DISTINCT col) FROM table_name;统计不重复值数量SELECT SUM(col) FROM table_name;求和SELECT AVG(col) FROM table_name;求平均值SELECT MIN(col), MAX(col) FROM table_name;求最小/最大值SELECT col, COUNT(*) FROM t GROUP BY col;按列分组统计SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;分组后过滤（HAVING）SELECT col1, col2, SUM(val) FROM t GROUP BY col1, col2;多列分组SELECT col, GROUP_CONCAT(val) FROM t GROUP BY col;分组拼接字符串（MySQL）SELECT col, STRING_AGG(val, ',') FROM t GROUP BY col;分组拼接字符串（PostgreSQL）SELECT GROUPING SETS ((col1), (col2), ());多分组组合（高级聚合）SELECT col, COUNT(*) OVER() as total FROM t;窗口函数：全表总数子查询
SELECT * FROM t WHERE col = (SELECT MAX(col) FROM t);WHERE 中的标量子查询SELECT * FROM t WHERE col IN (SELECT col FROM t2);WHERE 中的 IN 子查询SELECT * FROM t WHERE col > ANY (SELECT col FROM t2);ANY 子查询（大于任一值）SELECT * FROM t WHERE col > ALL (SELECT col FROM t2);ALL 子查询（大于所有值）SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.fk = t.id);EXISTS 子查询（存在性检查）SELECT * FROM (SELECT col FROM t) AS sub;FROM 中的派生表SELECT col, (SELECT COUNT(*) FROM t2 WHERE t2.fk = t.id) AS cnt FROM t;SELECT 中的标量子查询SELECT * FROM t WHERE (col1, col2) IN (SELECT c1, c2 FROM t2);多列 IN 子查询窗口函数
SELECT col, ROW_NUMBER() OVER (ORDER BY col) AS rn FROM t;行号（1,2,3...不重复）SELECT col, RANK() OVER (ORDER BY col DESC) AS rnk FROM t;排名（并列时跳号：1,1,3）SELECT col, DENSE_RANK() OVER (ORDER BY col DESC) FROM t;密集排名（并列时不跳号：1,1,2）SELECT col, NTILE(4) OVER (ORDER BY col) AS quartile FROM t;分桶（分成4个桶）SELECT col, LAG(col, 1) OVER (ORDER BY date) AS prev FROM t;前一行值（用于计算差值）SELECT col, LEAD(col, 1) OVER (ORDER BY date) AS next FROM t;后一行值SELECT col, SUM(val) OVER (PARTITION BY dept) FROM t;分组窗口（按部门求和）SELECT col, SUM(val) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM t;累计求和SELECT col, AVG(val) OVER (PARTITION BY dept ORDER BY date ROWS 2 PRECEDING) FROM t;移动平均（最近3行）SELECT col, FIRST_VALUE(val) OVER (PARTITION BY dept ORDER BY date) FROM t;分组第一个值SELECT col, LAST_VALUE(val) OVER (PARTITION BY dept ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t;分组最后一个值SELECT col, PERCENT_RANK() OVER (ORDER BY col) FROM t;百分比排名（0-1）SELECT col, CUME_DIST() OVER (ORDER BY col) FROM t;累积分布表结构 (DDL)
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);创建表（基本结构）CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));自增主键（MySQL）CREATE TABLE t (id SERIAL PRIMARY KEY, name VARCHAR(50));自增主键（PostgreSQL）CREATE TABLE t (id INT, fk INT FOREIGN KEY REFERENCES t2(id));外键约束CREATE TABLE t (id INT, email VARCHAR(100) UNIQUE);唯一约束CREATE TABLE t (id INT, age INT CHECK (age > 0));检查约束CREATE TABLE t (id INT, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);默认值（当前时间）ALTER TABLE t ADD COLUMN col VARCHAR(50);添加列ALTER TABLE t ALTER COLUMN col TYPE VARCHAR(100);修改列类型ALTER TABLE t DROP COLUMN col;删除列ALTER TABLE t RENAME COLUMN old TO new;重命名列ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id);添加主键约束ALTER TABLE t ADD INDEX idx (col);添加索引CREATE INDEX idx ON table_name (col);创建单列索引CREATE UNIQUE INDEX idx ON t (col1, col2);创建复合唯一索引DROP INDEX idx;删除索引DROP TABLE IF EXISTS table_name;删除表CREATE TABLE t_new LIKE t_old;复制表结构（MySQL）CREATE TABLE t_new AS SELECT * FROM t_old;复制表结构和数据视图与存储
CREATE VIEW v AS SELECT col1, col2 FROM t WHERE condition;创建视图CREATE OR REPLACE VIEW v AS SELECT ...;创建或替换视图DROP VIEW v;删除视图SELECT * FROM v;查询视图CREATE PROCEDURE proc_name() BEGIN ... END;创建存储过程（MySQL）CALL proc_name();调用存储过程DROP PROCEDURE proc_name;删除存储过程CREATE FUNCTION func_name(param INT) RETURNS INT AS $$ ... $$ LANGUAGE plpgsql;创建函数（PostgreSQL）SELECT func_name(col) FROM t;调用函数CREATE TRIGGER trg_name BEFORE INSERT ON t FOR EACH ROW BEGIN ... END;创建触发器DROP TRIGGER trg_name;删除触发器DELIMITER // CREATE PROCEDURE ... // DELIMITER ;修改分隔符（MySQL存储过程）事务控制
BEGIN; 或 START TRANSACTION;开始事务COMMIT;提交事务ROLLBACK;回滚事务SAVEPOINT sp_name;创建保存点ROLLBACK TO SAVEPOINT sp_name;回滚到保存点RELEASE SAVEPOINT sp_name;释放保存点SET TRANSACTION ISOLATION LEVEL READ COMMITTED;设置隔离级别：读已提交SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;设置隔离级别：可串行化SET autocommit = 0;关闭自动提交（MySQL）SET autocommit = 1;开启自动提交（MySQL）SELECT @@transaction_isolation;查看当前隔离级别（MySQL）SHOW TRANSACTION ISOLATION LEVEL;查看当前隔离级别（PostgreSQL）权限管理
CREATE USER 'username'@'host' IDENTIFIED BY 'password';创建用户DROP USER 'username'@'host';删除用户GRANT ALL PRIVILEGES ON db.* TO 'user'@'host';授予数据库所有权限GRANT SELECT, INSERT, UPDATE ON db.table TO 'user'@'host';授予指定权限GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH GRANT OPTION;授予所有权限（含授权权限）REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'host';撤销所有权限REVOKE SELECT ON db.table FROM 'user'@'host';撤销指定权限FLUSH PRIVILEGES;刷新权限（MySQL）SHOW GRANTS FOR 'user'@'host';查看用户权限ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';修改用户密码SET PASSWORD FOR 'user'@'host' = PASSWORD('new');设置密码（MySQL旧版）高级查询
SELECT CASE WHEN col > 0 THEN 'pos' ELSE 'neg' END AS sign FROM t;CASE WHEN 条件表达式SELECT COALESCE(col, 'default') FROM t;NULL 值替换为默认值SELECT NULLIF(col, '') FROM t;空字符串转 NULLSELECT CAST(col AS INT) FROM t;类型转换SELECT col::INT FROM t;类型转换简写（PostgreSQL）SELECT col1 FROM t1 UNION SELECT col2 FROM t2;合并结果集（去重）SELECT col1 FROM t1 UNION ALL SELECT col2 FROM t2;合并结果集（保留重复）SELECT col FROM t1 INTERSECT SELECT col FROM t2;交集（两结果集共有部分）SELECT col FROM t1 EXCEPT SELECT col FROM t2;差集（t1有但t2没有）WITH cte AS (SELECT ... FROM t) SELECT * FROM cte;CTE（公共表表达式）WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... FROM cte) SELECT * FROM cte;递归 CTE（树形查询）SELECT col FROM t WHERE condition FOR UPDATE;锁定查询行（悲观锁）SELECT col FROM t WHERE condition FOR SHARE;共享锁（PostgreSQL）常用数据库操作
SHOW DATABASES;显示所有数据库（MySQL）SELECT datname FROM pg_database;显示所有数据库（PostgreSQL）CREATE DATABASE db_name;创建数据库CREATE DATABASE db_name CHARACTER SET utf8mb4;创建数据库（指定字符集）DROP DATABASE db_name;删除数据库USE db_name;切换数据库（MySQL）\c db_name切换数据库（PostgreSQL）SHOW TABLES;显示所有表（MySQL）SELECT tablename FROM pg_tables WHERE schemaname = 'public';显示所有表（PostgreSQL）DESCRIBE table_name; 或 DESC table_name;查看表结构（MySQL）\d table_name查看表结构（PostgreSQL）SHOW CREATE TABLE table_name;查看建表语句（MySQL）SELECT version();查看数据库版本SELECT CURRENT_USER;查看当前用户SELECT DATABASE(); 或 SELECT current_database();查看当前数据库mysqldump -u user -p db_name > backup.sql;备份数据库（MySQL命令行）mysql -u user -p db_name < backup.sql;恢复数据库（MySQL命令行）pg_dump -U user db_name > backup.sql;备份数据库（PostgreSQL命令行）pg_restore -U user -d db_name backup.dump;恢复数据库（PostgreSQL命令行）💡 使用提示
• 点击命令 或右侧复制图标可快速复制命令
• <占位符> 需要替换为实际值（如表名、列名等）
• 不同数据库（MySQL/PostgreSQL/SQLite）语法可能有差异，请注意兼容性
• 危险命令（如 DROP、DELETE 不带 WHERE）执行前请确认，避免数据丢失
• 使用 EXPLAIN 可以分析查询执行计划，优化性能