MySQL 数据库操作命令大全
一、基础入门命令
1.1 连接到 MySQL
| 操作 |
命令 |
说明 |
| 本地登录 |
mysql -u 用户名 -p |
默认用户名 root |
| 远程登录 |
mysql -h 主机地址 -P 端口 -u 用户名 -p |
指定主机和端口 |
| 直接指定数据库 |
mysql -D 数据库名 -h 主机名 -u 用户名 -p |
登录后自动切换到指定数据库 |
| 指定密码登录 |
mysql -u 用户名 -p密码 |
注意 -p 和密码之间没有空格 |
1.2 查看系统信息
| 操作 |
命令 |
| 查看 MySQL 版本 |
SELECT VERSION(); |
| 查看当前用户 |
SELECT USER(); |
| 查看当前数据库 |
SELECT DATABASE(); |
| 查看 MySQL 状态 |
STATUS; |
| 查看 MySQL 端口号 |
SHOW GLOBAL VARIABLES LIKE 'port'; |
| 列出所有进程 |
SHOW PROCESSLIST; |
| 终止指定进程 |
KILL pid; |
| 查看数据库状态信息 |
SHOW STATUS; |
| 查看错误信息 |
SHOW ERRORS; |
| 查看警告信息 |
SHOW WARNINGS; |
1.3 退出 MySQL
| 操作 |
命令 |
| 退出会话 |
EXIT; 或 QUIT; 或 \q |
二、数据库管理命令
| 操作 |
命令 |
示例 |
| 查看所有数据库 |
SHOW DATABASES; |
|
| 创建数据库 |
CREATE DATABASE 数据库名; |
CREATE DATABASE blog_system; |
| 创建数据库（指定字符集） |
CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则; |
CREATE DATABASE blog_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 删除数据库 |
DROP DATABASE 数据库名; |
DROP DATABASE blog_system; |
| 选择/切换数据库 |
USE 数据库名; |
USE blog_system; |
| 查看数据库创建语句 |
SHOW CREATE DATABASE 数据库名; |
查看数据库的完整定义信息 |
| 修改数据库编码格式 |
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则; |
|
三、数据表管理命令
3.1 查看表信息
| 操作 |
命令 |
| 查看所有表 |
SHOW TABLES; |
| 查看表结构 |
DESC 表名; 或 DESCRIBE 表名; 或 SHOW COLUMNS FROM 表名; |
| 查看建表 SQL |
SHOW CREATE TABLE 表名; |
| 查看表索引 |
SHOW INDEX FROM 表名; |
| 查看表统计信息 |
SHOW TABLE STATUS LIKE '表名'; |
| 查看表的字段列表 |
SHOW FIELDS FROM 表名; |
3.2 创建表
CREATE TABLE user (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(100),
status TINYINT DEFAULT 1,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.3 修改表结构
| 操作 |
命令 |
| 添加列 |
ALTER TABLE 表名 ADD 列名 数据类型 [约束]; |
| 删除列 |
ALTER TABLE 表名 DROP COLUMN 列名; |
| 修改列定义 |
ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]; |
| 重命名列 |
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束]; |
| 重命名表 |
RENAME TABLE 旧表名 TO 新表名; |
| 添加约束 |
ALTER TABLE 表名 ADD constraint; |
| 删除约束 |
ALTER TABLE 表名 DROP constraint; |
| 删除表 |
DROP TABLE 表名; |
| 清空表数据 |
TRUNCATE TABLE 表名; |
四、数据操作命令（DML）
4.1 插入数据
| 操作 |
命令 |
| 插入单条 |
INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2, …); |
| 插入多条 |
INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2), (值3, 值4), …; |
| 从查询结果插入 |
INSERT INTO 目标表 (列1, 列2) SELECT 列1, 列2 FROM 源表 WHERE 条件; |
4.2 查询数据
| 操作 |
命令 |
| 基本查询 |
SELECT 列1, 列2 FROM 表名 WHERE 条件; |
| 查询所有列 |
SELECT * FROM 表名; |
| 条件查询 |
SELECT * FROM 表名 WHERE 条件1 AND/OR 条件2; |
| 排序查询 |
SELECT * FROM 表名 ORDER BY 列名 ASC/DESC; |
| 分组查询 |
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名; |
| 分组后过滤 |
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING COUNT(*) > 值; |
| 限制结果数量 |
SELECT * FROM 表名 LIMIT 偏移量, 数量; |
| 去重查询 |
SELECT DISTINCT 列名 FROM 表名; |
4.3 更新数据
| 操作 |
命令 |
| 更新单表 |
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件; |
| 多表更新 |
UPDATE 表1 JOIN 表2 ON 条件 SET 表1.列 = 值 WHERE 条件; |
4.4 删除数据
| 操作 |
命令 |
| 条件删除 |
DELETE FROM 表名 WHERE 条件; |
| 删除所有数据 |
DELETE FROM 表名;（逐行删除，可回滚） |
| 多表删除 |
DELETE 表1, 表2 FROM 表1 JOIN 表2 ON 条件 WHERE 条件; |
五、索引管理命令
5.1 索引类型
| 索引类型 |
关键字 |
说明 |
| 普通索引 |
INDEX |
最基本的索引，无限制 |
| 唯一索引 |
UNIQUE INDEX |
索引列的值必须唯一，允许 NULL |
| 主键索引 |
PRIMARY KEY |
特殊的唯一索引，不允许 NULL |
| 全文索引 |
FULLTEXT INDEX |
用于全文搜索 |
| 复合索引 |
INDEX (列1, 列2, …) |
多列组合的索引 |
5.2 索引操作命令
| 操作 |
命令 |
| 创建普通索引 |
CREATE INDEX 索引名 ON 表名 (列名); |
| 创建唯一索引 |
CREATE UNIQUE INDEX 索引名 ON 表名 (列名); |
| 创建复合索引 |
CREATE INDEX 索引名 ON 表名 (列1, 列2, …); |
| 建表时创建索引 |
CREATE TABLE 表名 (…, INDEX 索引名 (列名)); |
| 使用 ALTER 添加索引 |
ALTER TABLE 表名 ADD INDEX 索引名 (列名); |
| 删除索引 |
DROP INDEX 索引名 ON 表名; 或 ALTER TABLE 表名 DROP INDEX 索引名; |
| 查看表索引 |
SHOW INDEX FROM 表名; |
六、用户与权限管理命令
6.1 用户管理
| 操作 |
命令 |
| 创建用户 |
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; |
| 删除用户 |
DROP USER '用户名'@'主机'; |
| 修改密码 |
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码'; |
| 查看所有用户 |
SELECT user, host FROM mysql.user; |
| 重命名用户 |
RENAME USER '旧用户名'@'主机' TO '新用户名'@'主机'; |
6.2 权限管理
| 操作 |
命令 |
| 授予权限 |
GRANT 权限 ON 数据库.表 TO '用户名'@'主机'; |
| 授予所有权限 |
GRANT ALL PRIVILEGES ON 数据库.* TO '用户名'@'主机'; |
| 撤销权限 |
REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机'; |
| 查看用户权限 |
SHOW GRANTS FOR '用户名'@'主机'; |
| 刷新权限 |
FLUSH PRIVILEGES; |
6.3 常用权限类型
| 权限 |
说明 |
ALL PRIVILEGES |
所有权限 |
SELECT |
查询权限 |
INSERT |
插入权限 |
UPDATE |
更新权限 |
DELETE |
删除权限 |
CREATE |
创建数据库/表权限 |
DROP |
删除数据库/表权限 |
ALTER |
修改表结构权限 |
INDEX |
创建/删除索引权限 |
REFERENCES |
外键权限 |
七、事务控制命令
| 操作 |
命令 |
说明 |
| 开始事务 |
START TRANSACTION; 或 BEGIN; |
开启一个新事务 |
| 提交事务 |
COMMIT; |
提交当前事务，永久保存修改 |
| 回滚事务 |
ROLLBACK; |
回滚当前事务，撤销所有修改 |
| 设置保存点 |
SAVEPOINT 保存点名; |
在事务中创建回滚点 |
| 回滚到保存点 |
ROLLBACK TO SAVEPOINT 保存点名; |
回滚到指定保存点 |
| 释放保存点 |
RELEASE SAVEPOINT 保存点名; |
删除保存点 |
| 查看事务状态 |
SHOW ENGINE INNODB STATUS; |
查看 InnoDB 引擎事务状态 |
| 锁定表（写锁） |
LOCK TABLES 表名 WRITE; |
锁定表用于写入 |
| 锁定表（读锁） |
LOCK TABLES 表名 READ; |
锁定表用于读取 |
| 释放表锁 |
UNLOCK TABLES; |
释放所有表锁 |
事务隔离级别设置
| 隔离级别 |
命令 |
| 读未提交 |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| 读已提交 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 可重复读（默认） |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| 可串行化 |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
八、存储过程与函数
8.1 存储过程
| 操作 |
命令 |
| 创建存储过程 |
CREATE PROCEDURE 过程名([参数]) BEGIN … END; |
| 调用存储过程 |
CALL 过程名([参数]); |
| 删除存储过程 |
DROP PROCEDURE [IF EXISTS] 过程名; |
| 查看存储过程 |
SHOW PROCEDURE STATUS; |
| 查看创建语句 |
SHOW CREATE PROCEDURE 过程名; |
存储过程示例
-- 修改分隔符
DELIMITER //
-- 创建带参数的存储过程
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
SELECT * FROM user WHERE id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL GetUserById(1);
8.2 存储函数
| 操作 |
命令 |
| 创建函数 |
CREATE FUNCTION 函数名([参数]) RETURNS 返回类型 BEGIN … RETURN 值; END; |
| 调用函数 |
SELECT 函数名([参数]); |
| 删除函数 |
DROP FUNCTION [IF EXISTS] 函数名; |
九、视图与触发器
9.1 视图操作
| 操作 |
命令 |
| 创建视图 |
CREATE VIEW 视图名 AS SELECT …; |
| 创建或替换视图 |
CREATE OR REPLACE VIEW 视图名 AS SELECT …; |
| 删除视图 |
DROP VIEW [IF EXISTS] 视图名; |
| 查看视图定义 |
SHOW CREATE VIEW 视图名; |
视图示例
-- 创建视图
CREATE VIEW user_summary AS
SELECT id, username, email, status, create_time
FROM user
WHERE status = 1;
9.2 触发器操作
| 操作 |
命令 |
| 创建触发器 |
CREATE TRIGGER 触发器名 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW BEGIN … END; |
| 删除触发器 |
DROP TRIGGER [IF EXISTS] 触发器名; |
| 查看触发器 |
SHOW TRIGGERS; |
触发器示例
-- 创建触发器
CREATE TRIGGER before_user_insert
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
SET NEW.create_time = NOW();
END;
十、性能分析与调优
10.1 慢查询日志
| 操作 |
命令 |
| 查看慢查询日志状态 |
SHOW VARIABLES LIKE 'slow_query_log'; |
| 开启慢查询日志 |
SET GLOBAL slow_query_log = ON; |
| 设置慢查询阈值（秒） |
SET GLOBAL long_query_time = 2; |
| 查看慢查询日志文件 |
SHOW VARIABLES LIKE 'slow_query_log_file'; |
10.2 执行计划分析（EXPLAIN）
| 操作 |
命令 |
| 分析查询计划 |
EXPLAIN SELECT …; |
| 分析实际执行 |
EXPLAIN ANALYZE SELECT …;（MySQL 8.0+） |
10.3 查询性能分析
| 操作 |
命令 |
| 开启性能分析 |
SET profiling = 1; |
| 查看所有分析结果 |
SHOW PROFILES; |
| 查看指定查询详情 |
SHOW PROFILE FOR QUERY query_id; |
| 查看 CPU 使用 |
SHOW PROFILE CPU FOR QUERY query_id; |
10.4 其他性能命令
| 操作 |
命令 |
| 分析表（更新统计信息） |
ANALYZE TABLE 表名; |
| 检查表 |
CHECK TABLE 表名; |
| 优化表 |
OPTIMIZE TABLE 表名; |
| 修复表 |
REPAIR TABLE 表名; |
十一、备份与恢复
11.1 mysqldump 常用命令
| 操作 |
命令 |
| 备份单个数据库 |
mysqldump -u 用户名 -p 数据库名 > backup.sql |
| 备份多个数据库 |
mysqldump -u 用户名 -p --databases db1 db2 > backup.sql |
| 备份所有数据库 |
mysqldump -u 用户名 -p --all-databases > backup.sql |
| 备份单张表 |
mysqldump -u 用户名 -p 数据库名 表名 > backup.sql |
| 仅备份结构（无数据） |
mysqldump -u 用户名 -p --no-data 数据库名 > structure.sql |
| 仅备份数据（无结构） |
mysqldump -u 用户名 -p --no-create-info 数据库名 > data.sql |
| 恢复备份 |
mysql -u 用户名 -p 数据库名 < backup.sql |
| 压缩备份 |
mysqldump -u 用户名 -p 数据库名 | gzip > backup.sql.gz |
11.2 常用备份参数
| 参数 |
说明 |
--no-data |
不导出数据，只导出表结构 |
--no-create-info |
不导出建表语句 |
--add-drop-table |
在创建表前先删除同名表 |
--single-transaction |
使用事务保证备份一致性（InnoDB） |
--routines |
导出存储过程和函数 |
--triggers |
导出触发器 |
--events |
导出事件 |
--where='条件' |
仅备份满足条件的行 |
--ignore-table=db.table |
忽略指定表 |
十二、数据类型速查
12.1 数值类型
| 类型 |
说明 |
范围 |
TINYINT |
极小整数 |
-128 到 127（无符号：0 到 255） |
SMALLINT |
小整数 |
-32768 到 32767 |
MEDIUMINT |
中等整数 |
-8388608 到 8388607 |
INT / INTEGER |
标准整数 |
-2³¹ 到 2³¹-1 |
BIGINT |
大整数 |
-2⁶³ 到 2⁶³-1 |
FLOAT |
单精度浮点数 |
|
DOUBLE |
双精度浮点数 |
|
DECIMAL(M,D) |
定点数 |
M 位总长，D 位小数 |
12.2 字符串类型
| 类型 |
说明 |
CHAR(n) |
定长字符串，最多 255 字符 |
VARCHAR(n) |
变长字符串，最多 65535 字符 |
TINYTEXT |
短文本，最多 255 字符 |
TEXT |
长文本，最多 65535 字符 |
MEDIUMTEXT |
中等文本，最多 16MB |
LONGTEXT |
极大文本，最多 4GB |
ENUM |
枚举类型，从预定义值中选择 |
SET |
集合类型，可选择多个预定义值 |
12.3 日期时间类型
| 类型 |
格式 |
范围 |
DATE |
YYYY-MM-DD |
1000-01-01 到 9999-12-31 |
TIME |
HH:MM:SS |
-838:59:59 到 838:59:59 |
DATETIME |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 到 9999-12-31 23:59:59 |
TIMESTAMP |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC |
YEAR |
YYYY |
1901 到 2155 |
十三、字符集与排序规则
13.1 常用命令
| 操作 |
命令 |
| 查看可用字符集 |
SHOW CHARACTER SET; |
| 查看可用排序规则 |
SHOW COLLATION; |
| 查看当前字符集 |
SHOW VARIABLES LIKE 'character_set%'; |
| 查看当前排序规则 |
SHOW VARIABLES LIKE 'collation%'; |
| 设置数据库字符集 |
ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则; |
| 设置表字符集 |
ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集 COLLATE 排序规则; |
13.2 常用字符集
| 字符集 |
说明 |
utf8 |
UTF-8 编码（最多 3 字节） |
utf8mb4 |
完整 UTF-8 编码（最多 4 字节，支持 Emoji） |
latin1 |
西欧字符编码 |
gbk |
简体中文编码 |
gb2312 |
国标简体中文编码 |
十四、MySQL 8.0 新特性命令
14.1 窗口函数
| 函数 |
说明 |
ROW_NUMBER() |
行号 |
RANK() |
排名（有间隔） |
DENSE_RANK() |
排名（无间隔） |
NTILE(n) |
分桶 |
LAG() / LEAD() |
前/后一行值 |
FIRST_VALUE() / LAST_VALUE() |
首/尾值 |
窗口函数示例：
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employee;
14.2 公用表表达式（CTE）
| 操作 |
命令 |
| 普通 CTE |
WITH cte_name AS (SELECT …) SELECT * FROM cte_name; |
| 递归 CTE |
WITH RECURSIVE cte_name AS (初始查询 UNION ALL 递归查询) SELECT * FROM cte_name; |
递归 CTE 示例（查询树形结构）：
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 1 AS level
FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM category c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
14.3 JSON 函数
| 函数 |
说明 |
JSON_EXTRACT() |
提取 JSON 值 |
JSON_TABLE() |
将 JSON 转换为关系表 |
JSON_ARRAY() |
创建 JSON 数组 |
JSON_OBJECT() |
创建 JSON 对象 |
JSON_SET() |
设置 JSON 值 |
JSON_REMOVE() |
删除 JSON 字段 |
14.4 其他 8.0 新特性
| 特性 |
说明 |
| 隐藏索引 |
ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE/VISIBLE; |
| 函数索引 |
CREATE INDEX 索引名 ON 表名 ((函数(列名))); |
| 原子 DDL |
CREATE/DROP/ALTER 操作原子性保证 |
十五、常用约束
| 约束类型 |
关键字 |
说明 |
| 主键约束 |
PRIMARY KEY |
唯一标识每行，不允许 NULL |
| 外键约束 |
FOREIGN KEY … REFERENCES |
保证参照完整性 |
| 唯一约束 |
UNIQUE |
列值必须唯一，允许 NULL |
| 非空约束 |
NOT NULL |
列不能为 NULL |
| 默认值 |
DEFAULT 值 |
插入时的默认值 |
| 检查约束 |
CHECK (条件) |
限制列值范围（MySQL 8.0+） |
| 自增 |
AUTO_INCREMENT |
自动递增数值 |
十六、实用速查表
16.1 常用 SQL 函数速查
| 类别 |
函数 |
说明 |
| 聚合函数 |
COUNT(), SUM(), AVG(), MAX(), MIN() |
统计计算 |
| 字符串函数 |
CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM(), REPLACE() |
字符串处理 |
| 日期函数 |
NOW(), CURDATE(), DATE_FORMAT(), DATEDIFF(), DATE_ADD() |
日期处理 |
| 数学函数 |
ROUND(), FLOOR(), CEIL(), ABS(), RAND() |
数学计算 |
| 条件函数 |
IF(), CASE WHEN … THEN … END, IFNULL(), COALESCE() |
条件判断 |
16.2 常用运算符
| 运算符 |
说明 |
=, <>, !=, >, <, >=, <= |
比较运算符 |
AND, OR, NOT |
逻辑运算符 |
IN, NOT IN |
集合匹配 |
BETWEEN … AND … |
范围匹配 |
LIKE, NOT LIKE |
模糊匹配（% 任意多字符，_ 单个字符） |
IS NULL, IS NOT NULL |
NULL 判断 |
REGEXP |
正则表达式匹配 |
