GeekIt

SQL命令速查

查询、连接、索引、事务、权限与备份排查等 136 条常用 SQL 语句

共 136 条语句 · 10 类场景 · 点击右侧图标可复制

SQL / 命令说明复制
mysql -h 10.0.0.5 -u root -p连接 MySQL 并交互式输入密码
mysql -h 10.0.0.5 -P 3306 -u app -p db_name指定端口并直接进入某个库
mysql -e "SHOW DATABASES" -u root -p不进入交互界面直接执行一条 SQL
SHOW DATABASES;列出所有数据库
USE app_db;切换当前数据库
SELECT DATABASE();查看当前所在数据库
SELECT VERSION();查看数据库版本
SHOW TABLES;列出当前库的所有表
SHOW TABLE STATUS LIKE "user%";查看表的行数、引擎与大小等信息
DESC user;查看表结构(等价 SHOW COLUMNS FROM user)
SHOW CREATE TABLE user;查看建表语句,含索引与字符集
CREATE DATABASE app_db DEFAULT CHARSET utf8mb4;创建数据库并指定字符集
DROP DATABASE app_db;删除数据库(高危,先备份)
psql -h 10.0.0.5 -U postgres -d app_dbPostgreSQL 客户端连接
\l 与 \dt 与 \c app_dbpsql 内查看库列表、表列表与切换库
\d userpsql 内查看表结构
SQL / 命令说明复制
CREATE TABLE user (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);创建带主键、自增与默认时间的表
CREATE TABLE IF NOT EXISTS user (...);表不存在时才创建,脚本可重复执行
CREATE TABLE t LIKE user;复制表结构(不含数据)
CREATE TABLE t AS SELECT * FROM user WHERE 1=0;按查询结果建表,仅结构
ALTER TABLE user ADD COLUMN email VARCHAR(128) DEFAULT NULL;新增列
ALTER TABLE user MODIFY COLUMN name VARCHAR(128) NOT NULL;修改列类型与约束
ALTER TABLE user CHANGE COLUMN name username VARCHAR(128);改列名同时改类型
ALTER TABLE user DROP COLUMN email;删除列(数据不可恢复)
ALTER TABLE user RENAME TO users;重命名表
ALTER TABLE user ADD INDEX idx_name (name);新增普通索引
ALTER TABLE user ADD UNIQUE INDEX uk_email (email);新增唯一索引
ALTER TABLE user DROP INDEX idx_name;删除索引
CREATE INDEX idx_created ON user (created_at);单独创建索引
TRUNCATE TABLE log_tmp;清空表数据(比 DELETE 快,不可回滚)
DROP TABLE IF EXISTS log_tmp;删除表结构与数据
ALTER TABLE user COMMENT = "用户表";设置表注释,便于协作理解
SHOW INDEX FROM user;查看表的索引结构(含区分度)
SQL / 命令说明复制
SELECT * FROM user WHERE status = 1;基础条件查询
SELECT id, name FROM user;只取需要的列,避免 SELECT *
SELECT DISTINCT city FROM user;去重查询
SELECT id, name AS username FROM user;为列取别名
SELECT * FROM user ORDER BY created_at DESC;按时间倒序
SELECT * FROM user ORDER BY status DESC, id ASC;多字段排序
SELECT * FROM user LIMIT 20;取前 20 条
SELECT * FROM user LIMIT 20 OFFSET 40;分页取第 3 页(深分页建议用游标)
SELECT * FROM user WHERE id > 1000 ORDER BY id LIMIT 20;游标分页,避免 OFFSET 越翻越慢
SELECT * FROM user WHERE age BETWEEN 18 AND 30;范围查询
SELECT * FROM user WHERE city IN ("北京", "上海");枚举匹配
SELECT * FROM user WHERE name LIKE "张%";前缀匹配可走索引,前缀模糊不行
SELECT * FROM user WHERE email IS NULL;判断空值(不能用 = NULL)
SELECT * FROM user WHERE status != 0 AND level >= 2;多条件组合
SELECT * FROM user WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);查询近 7 天数据
SQL / 命令说明复制
SELECT COUNT(*) FROM user;统计总行数
SELECT COUNT(DISTINCT city) FROM user;统计去重后的数量
SELECT SUM(amount), AVG(amount), MAX(amount), MIN(amount) FROM orders;常用聚合函数一次取全
SELECT city, COUNT(*) AS cnt FROM user GROUP BY city;按城市分组统计
SELECT city, COUNT(*) cnt FROM user GROUP BY city HAVING cnt > 100;分组后再过滤用 HAVING
SELECT city, COUNT(*) cnt FROM user GROUP BY city WITH ROLLUP;附带汇总行
SELECT CASE WHEN score >= 90 THEN "优秀" WHEN score >= 60 THEN "及格" ELSE "不及格" END AS level, COUNT(*) FROM exam GROUP BY level;条件分组统计(报表常用)
SELECT DATE(created_at) d, COUNT(*) FROM orders GROUP BY d ORDER BY d DESC LIMIT 7;按天统计最近 7 天订单量
SELECT status, SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS paid FROM orders GROUP BY status;行列转换式统计
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ",") FROM user WHERE city = "北京";MySQL 把多行拼成一个字符串
SQL / 命令说明复制
SELECT u.name, o.amount FROM user u INNER JOIN orders o ON o.user_id = u.id;内连接,只保留两边都匹配的行
SELECT u.name, o.amount FROM user u LEFT JOIN orders o ON o.user_id = u.id;左连接,保留左表全部数据
SELECT u.name, o.amount FROM user u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;找出没有下单的用户
SELECT * FROM orders o RIGHT JOIN user u ON o.user_id = u.id;右连接(可改写成左连接)
SELECT * FROM a JOIN b USING (user_id);同名字段用 USING 简化写法
SELECT * FROM user u JOIN orders o ON o.referrer_id = u.id AND o.status = 1;连接条件附加业务过滤
SELECT id FROM user WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);IN 子查询
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);EXISTS 子查询,存在即返回
SELECT a.id FROM a UNION SELECT id FROM b;合并结果并去重
SELECT id FROM a UNION ALL SELECT id FROM b;合并结果保留重复,性能更好
SELECT e.name, m.name AS manager FROM employee e LEFT JOIN employee m ON e.manager_id = m.id;自连接查询上下级关系
SELECT * FROM a CROSS JOIN b LIMIT 10;笛卡尔积(慎用,注意数据量)
SQL / 命令说明复制
INSERT INTO user (name, age) VALUES ("tom", 20);插入一行
INSERT INTO user (name, age) VALUES ("a", 1), ("b", 2), ("c", 3);批量插入,一条语句多行更快
INSERT IGNORE INTO user (id, name) VALUES (1, "tom");主键或唯一键冲突时跳过不报错
INSERT INTO user (id, name) VALUES (1, "tom") ON DUPLICATE KEY UPDATE name = VALUES(name);存在则更新,实现 upsert
REPLACE INTO user (id, name) VALUES (1, "tom");先删后插(会丢其他字段值,慎用)
UPDATE user SET status = 0 WHERE id = 100;按主键更新单行
UPDATE user SET level = level + 1 WHERE city = "北京";批量更新并做自增计算
DELETE FROM log WHERE created_at < "2026-01-01";按条件删除历史数据
DELETE FROM log WHERE created_at < "2026-01-01" LIMIT 1000;分批删除,避免长事务与锁表
SELECT * FROM user WHERE id = 100 FOR UPDATE;加行锁查询,事务内防并发修改
SELECT ... INTO OUTFILE "/tmp/user.csv" FIELDS TERMINATED BY ",";导出查询结果到文件(需权限)
LOAD DATA INFILE "/tmp/user.csv" INTO TABLE user FIELDS TERMINATED BY ",";从 CSV 批量导入数据
SQL / 命令说明复制
EXPLAIN SELECT * FROM user WHERE name = "tom";查看执行计划:type、key、rows 是关键
EXPLAIN ANALYZE SELECT * FROM user WHERE name = "tom";MySQL 8.0.18+ / PG 实际执行并返回耗时
EXPLAIN FORMAT=JSON SELECT ...;输出更详细的 JSON 计划
SHOW INDEX FROM user;查看索引与列的先后顺序
ANALYZE TABLE user;更新统计信息,修复执行计划异常
OPTIMIZE TABLE user;整理表空间与碎片(大表注意锁与耗时)
SHOW STATUS LIKE "Handler_read%";查看索引使用情况
SHOW VARIABLES LIKE "slow_query_log%";查看慢查询日志是否开启及路径
SHOW VARIABLES LIKE "long_query_time";查看慢查询阈值
SET GLOBAL long_query_time = 1;临时把慢查询阈值调为 1 秒
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;日志写入表时可直接查询慢 SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log按总耗时排序统计 Top10 慢 SQL
SQL / 命令说明复制
START TRANSACTION;开启事务(等价 BEGIN)
COMMIT;提交事务
ROLLBACK;回滚事务
SAVEPOINT sp1;设置保存点
ROLLBACK TO sp1;回滚到指定保存点,不必整单回滚
SET autocommit = 0;关闭自动提交,后续操作需手动 COMMIT
SELECT @@transaction_isolation;查看当前事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;当前会话改为读已提交
SHOW ENGINE INNODB STATUS\G查看最近一次死锁详情与事务信息
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;查看运行中的长事务
SELECT * FROM performance_schema.data_locks;MySQL 8 查看当前锁等待关系
SELECT * FROM performance_schema.data_lock_waits;查看谁在等谁的锁
KILL 12345;终止卡住的会话(先确认再执行)
SQL / 命令说明复制
CREATE USER "app"@"10.0.0.%" IDENTIFIED BY "密码";创建限定来源网段的用户
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO "app"@"10.0.0.%";授予业务所需的库级权限
GRANT ALL PRIVILEGES ON app_db.* TO "app"@"10.0.0.%";授予库内全部权限(谨慎)
GRANT SELECT ON app_db.* TO "readonly"@"%";创建只读账号,供报表与查询使用
FLUSH PRIVILEGES;直接改权限表后刷新生效
SHOW GRANTS FOR "app"@"10.0.0.%";查看某个账号拥有的权限
REVOKE DELETE ON app_db.* FROM "app"@"10.0.0.%";回收指定权限
ALTER USER "app"@"%" IDENTIFIED BY "新密码";修改密码
RENAME USER "old"@"%" TO "new"@"%";重命名账号
DROP USER "app"@"%";删除账号
SELECT user, host, account_locked FROM mysql.user;查看账号列表与锁定状态
SHOW VARIABLES LIKE "require_secure_transport";检查是否强制 TLS 连接
SQL / 命令说明复制
mysqldump -h 10.0.0.5 -u root -p --single-transaction app_db > app_db.sqlInnoDB 一致性备份(不锁表)
mysqldump -u root -p --all-databases --single-transaction --routines --triggers > all.sql备份全部库含存储过程与触发器
mysqldump -u root -p app_db user orders > part.sql只备份指定两张表
mysql -u root -p app_db < app_db.sql恢复备份到指定库
source /data/backup/app_db.sql;登录后在 mysql 内导入 SQL 文件
SHOW PROCESSLIST;查看当前连接与正在执行的 SQL
SELECT * FROM information_schema.processlist WHERE time > 60;筛出执行超过 60 秒的语句
SHOW FULL PROCESSLIST;查看完整 SQL(不截断)
SHOW STATUS LIKE "Threads_connected";查看当前连接数
SHOW VARIABLES LIKE "max_connections";查看最大连接数上限
SHOW STATUS LIKE "Innodb_buffer_pool_read_requests";评估缓冲池命中率的关键指标
SHOW BINARY LOGS;查看 binlog 文件列表与大小
SHOW MASTER STATUS;查看当前 binlog 位点,用于恢复与同步
SHOW REPLICA STATUS\G查看主从同步状态与延迟(旧版 SHOW SLAVE STATUS)
pg_dump -h 10.0.0.5 -U postgres -Fc app_db > app_db.dumpPostgreSQL 自定义格式备份
pg_restore -h 10.0.0.5 -U postgres -d app_db app_db.dumpPostgreSQL 恢复备份
SELECT pid, query, now() - query_start AS duration FROM pg_stat_activity WHERE state = "active" ORDER BY duration DESC;PostgreSQL 查看长时间运行查询

运维常用组合

慢 SQL 定位SHOW FULL PROCESSLIST 与 EXPLAIN <sql> 与 mysqldumpslow -s t -t 10 slow.log
安全备份与恢复mysqldump --single-transaction -u root -p app_db > app_db.sql 与 mysql -u root -p app_db < app_db.sql
只读账号开通CREATE USER "ro"@"10.0.0.%" IDENTIFIED BY "密码"; GRANT SELECT ON app_db.* TO "ro"@"10.0.0.%"
排查锁等待SELECT * FROM information_schema.innodb_trx 与 SELECT * FROM performance_schema.data_lock_waits
无下单用户查询SELECT u.id FROM user u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL

常见问题排查

1. Deadlock found when trying to get lock

死锁:SHOW ENGINE INNODB STATUS 看详情,统一更新顺序并把事务写短

2. Too many connections

连接打满:PROCESSLIST 查来源,KILL 空闲连接,同时检查应用连接池与 max_connections

3. 查询突然变慢

先 EXPLAIN 看是否走错索引或全表扫描,再 ANALYZE TABLE 更新统计信息

4. 锁等待超时

查 innodb_trx 与 data_lock_waits 找出阻塞源,确认后 KILL 掉长事务会话

5. 中文乱码

统一 utf8mb4:库、表、连接字符集都要一致,SET NAMES utf8mb4 仅影响当前会话

6. 磁盘空间告警

多为 binlog 或大表膨胀:清理过期 binlog、归档历史表,OPTIMIZE 回收碎片

共 136 条 SQL 语句与数据库运维命令 · 生产执行 UPDATE/DELETE 前务必先 SELECT 验证条件

关于SQL命令速查

在线 SQL 命令速查手册,按连接与库操作、建表与改表、查询基础、聚合与分组、多表连接、写入与更新、索引与执行计划、事务与锁、用户与权限、备份恢复与运维排查等 10 类场景整理 140+ 条常用 SQL 语句与数据库运维命令,每条附中文说明与典型写法,支持关键词搜索、分类折叠和一键复制,打开网页即可查阅。涵盖 SELECT / WHERE / ORDER BY / LIMIT 分页、GROUP BY 与 HAVING、INNER/LEFT JOIN、UNION、子查询、INSERT 与 ON DUPLICATE KEY UPDATE、UPDATE / DELETE、CREATE TABLE 与 ALTER TABLE、CREATE INDEX 与 EXPLAIN 执行计划、联合索引最左前缀、事务 BEGIN/COMMIT/ROLLBACK 与隔离级别、锁等待与死锁查看、GRANT 权限与只读账号、mysqldump 备份与 source 恢复、SHOW PROCESSLIST 与 KILL 会话、SHOW STATUS/VARIABLES 巡检、慢查询日志定位,并附 PostgreSQL 的 psql、pg_dump 常用写法。适用于 MySQL / PostgreSQL 日常运维、慢查询与锁问题排查、数据库备份与迁移、SQL 编写与面试复习。

核心能力

  • 涵盖 SELECT / WHERE / ORDER BY / LIMIT 分页、GROUP BY 与 HAVING、INNER/LEFT JOIN、UNION、子查询、INSERT 与 ON DUPLICATE KEY UPDATE、UPDATE / DELETE、CREATE TABLE 与 ALTER TABLE、CREATE INDEX 与 EXPLAIN 执行计划、联合索引最左前缀、事务 BEGIN/COMMIT/ROLLBACK 与隔离级别、锁等待与死锁查看、GRANT 权限与只读账号、mysqldump 备份与 source 恢复、SHOW PROCESSLIST 与 KILL 会话、SHOW STATUS/VARIABLES 巡检、慢查询日志定位,并附 PostgreSQL 的 psql、pg_dump 常用写法

适用场景

适用于 MySQL / PostgreSQL 日常运维、慢查询与锁问题排查、数据库备份与迁移、SQL 编写与面试复习。SQL命令速查全程在浏览器中完成,无需安装插件,可放心用于日常办公与排障。

常见问题

SQL命令速查是免费的吗?需要注册或安装吗?
SQL命令速查完全免费,无需注册、无需下载安装任何软件,用浏览器打开网页即可直接使用,也不限制使用次数。
SQL命令速查支持哪些功能?
涵盖 SELECT / WHERE / ORDER BY / LIMIT 分页、GROUP BY 与 HAVING、INNER/LEFT JOIN、UNION、子查询、INSERT 与 ON DUPLICATE KEY UPDATE、UPDATE / DELETE、CREATE TABLE 与 ALTER TABLE、CREATE INDEX 与 EXPLAIN 执行计划、联合索引最左前缀、事务 BEGIN/COMMIT/ROLLBACK 与隔离级别、锁等待与死锁查看、GRANT 权限与只读账号、mysqldump 备份与 source 恢复、SHOW PROCESSLIST 与 KILL 会话、SHOW STATUS/VARIABLES 巡检、慢查询日志定位,并附 PostgreSQL 的 psql、pg_dump 常用写法。
SQL命令速查适合哪些场景使用?
适用于 MySQL / PostgreSQL 日常运维、慢查询与锁问题排查、数据库备份与迁移、SQL 编写与面试复习。