MySQL 常用命令速查手册：从建表到查询优化

"SELECT 后面接什么来着？"、"加个外键怎么写？"、"怎么查某张表有没有某个字段？"——每次写 SQL 都要掏手机查，查着查着 5 分钟过去了。这份速查手册整理了 MySQL 最常用的命令，从建库建表到查询优化，需要的时候直接 Ctrl+F。

一、库操作

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE mydb;

-- 查看当前所在数据库
SELECT DATABASE();

-- 删除数据库（慎用！）
DROP DATABASE mydb;

为什么用 utf8mb4 而不是 utf8？因为 MySQL 的 utf8 是个阉割版，最多 3 字节，存不了 emoji（😀）。utf8mb4 才是真正的 UTF-8。

二、表操作

-- 创建表
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 0,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看当前库所有表
SHOW TABLES;

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;  -- 完整建表语句

-- 查看表的所有索引
SHOW INDEX FROM users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO customers;

-- 删除表（慎用！）
DROP TABLE users;

三、常用字段类型速查

选型口诀：钱用 DECIMAL，不长不短用 VARCHAR，要查的就加索引。

四、增删改（CRUD）

-- 插入
INSERT INTO users (username, email, age) VALUES ('john', 'john@example.com', 25);
INSERT INTO users (username, email, age) VALUES
    ('alice', 'alice@example.com', 30),
    ('bob', 'bob@example.com', 28);

-- 查询
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 'active';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT DISTINCT status FROM users;

-- 聚合查询
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status HAVING cnt > 5;

-- 排序和分页
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 20;    -- 第 3 页，每页 10 条

-- 更新
UPDATE users SET status = 'inactive' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE status = 'active';    -- 活跃用户年龄 +1

-- 删除
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2025-01-01';    -- 删掉去年的旧数据

-- 软删除（推荐）
UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE id = 1;

生产环境不要用 DELETE 做硬删除，加个 is_deleted 字段做软删除，后悔药好买。

五、多表查询

-- 内连接（只返回两表都匹配的行）
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（返回左表全部 + 右表匹配的）
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 子查询
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

-- 联合查询（两表结构一样时合并结果）
SELECT username, email FROM users
UNION ALL
SELECT username, email FROM archived_users;

UNION 和 UNION ALL 的区别：UNION 会去重（慢），UNION ALL 不去重（快）。确定不会重复就用 UNION ALL。

六、索引优化（重点）

索引是数据库提速的第一手段，加对索引，查询从扫全表变成直接定位。

-- 创建索引
CREATE INDEX idx_username ON users(username);                        -- 普通索引
CREATE UNIQUE INDEX idx_email ON users(email);                       -- 唯一索引
CREATE INDEX idx_status_created ON users(status, created_at);        -- 联合索引

-- 查看查询是否用到索引
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

EXPLAIN 关键字段解读

索引最佳实践

• 最左前缀原则：联合索引 (A, B, C) 能匹配 WHERE A、WHERE A AND B，但不能单独匹配 WHERE B
• 不要在索引列上做计算：WHERE YEAR(created_at) = 2026 不走索引，应改为 WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'
• 区分度低的字段别单独建索引：gender（男女）这种只有两三个值的字段建索引效率极低
• 避免 SELECT *：只查需要的字段，配合覆盖索引效果更好

七、慢查询定位

-- 开启慢查询日志（临时）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;     -- 超过 1 秒就算慢查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 查看当前正在执行的查询
SHOW FULL PROCESSLIST;

-- 杀掉某个卡死的查询
KILL 123;   -- 123 是 processlist 里的 Id

慢查询日志是找到性能瓶颈的最直接手段。建议线上环境 long_query_time 设 0.5 秒。

八、备份与恢复

-- 导出单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

-- 导出所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

-- 只导出表结构（不要数据）
mysqldump -u root -p --no-data mydb > schema.sql

-- 恢复
mysql -u root -p mydb < mydb_backup.sql

生产环境一定要定期自动备份，配合 cron 每天凌晨跑一次：

0 3 * * * mysqldump -u root -p'密码' mydb | gzip > /backup/mydb_$(date +\%Y\%m\%d).sql.gz

九、常用运维命令

-- 查看 MySQL 版本
SELECT VERSION();

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看数据库大小
SELECT table_schema AS '数据库',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- 查看某张表的大小
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'users';

-- 查看表行数（InnoDB 下是近似值）
SELECT TABLE_ROWS FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'users';

-- 分析表（更新索引统计信息）
ANALYZE TABLE users;

十、防坑指南

这份手册常用的东西都覆盖了，遇到没列出来的，mysql --help 或者 \h 进 MySQL 敲 help contents 也能查。把这些命令敲熟了，日常开发 95% 的 SQL 场景都够用了。