MySQL常用命令汇总

MySQL 常用命令汇总

MySQL 常用命令，覆盖账号管理、库表管理、增删改查、事务与锁、索引视图、性能优化、备份恢复与安全等十个模块，适合作为学习与工作的速查手册。

一、连接与账号管理

连接本地 MySQL：mysql -u root -p
连接远程 MySQL：mysql -h 192.168.1.10 -u root -p
指定端口连接：mysql -h 127.0.0.1 -P 3307 -u root -p
查看当前用户：SELECT USER();
查看当前数据库：SELECT DATABASE();
查看所有用户：SELECT user,host FROM mysql.user;
创建用户：CREATE USER 'test'@'%' IDENTIFIED BY '123456';
修改密码：ALTER USER 'test'@'%' IDENTIFIED BY 'newpass';
设置密码（老方法）：SET PASSWORD FOR 'test'@'%' = PASSWORD('pwd');
删除用户：DROP USER 'test'@'%';
授权用户：GRANT ALL PRIVILEGES ON db1.* TO 'test'@'%';
授权只读：GRANT SELECT ON db1.* TO 'test'@'%';
查看用户权限：SHOW GRANTS FOR 'test'@'%';
撤销权限：REVOKE INSERT ON db1.* FROM 'test'@'%';
刷新权限：FLUSH PRIVILEGES;
修改 root 密码：ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
创建超级用户：GRANT ALL ON . TO 'admin'@'%' WITH GRANT OPTION;
锁定用户：ALTER USER 'test'@'%' ACCOUNT LOCK;
解锁用户：ALTER USER 'test'@'%' ACCOUNT UNLOCK;
删除匿名用户：DROP USER ''@'localhost';

二、数据库操作

查看所有数据库：SHOW DATABASES;
创建数据库：CREATE DATABASE db1;
创建带字符集数据库：CREATE DATABASE db1 CHARACTER SET utf8mb4;
删除数据库：DROP DATABASE db1;
切换数据库：USE db1;
查看当前数据库：SELECT DATABASE();
修改数据库字符集：ALTER DATABASE db1 CHARACTER SET utf8;
查看建库语句：SHOW CREATE DATABASE db1;
数据库版本：SELECT VERSION();
查看状态：SHOW STATUS;
查看当前连接数：SHOW PROCESSLIST;
显示完整连接：SHOW FULL PROCESSLIST;
查看线程数：SHOW STATUS LIKE 'Threads_connected';
查看最大连接数：SHOW VARIABLES LIKE 'max_connections';
修改最大连接数：SET GLOBAL max_connections=500;
查看字符集：SHOW VARIABLES LIKE 'character_set_%';
查看排序规则：SHOW VARIABLES LIKE 'collation_%';
设置默认字符集：SET NAMES utf8mb4;
查看数据库大小：SELECT table_schema, SUM(data_length+index_length)/1024/1024 AS MB FROM information_schema.tables GROUP BY table_schema;
查看表数量：SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='db1';

三、数据表操作

查看所有表：SHOW TABLES;
查看表结构：DESC users;
查看建表语句：SHOW CREATE TABLE users;
创建表：CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50));
创建带约束表：CREATE TABLE orders(id INT, uid INT, FOREIGN KEY(uid) REFERENCES users(id));
删除表：DROP TABLE users;
清空表：TRUNCATE TABLE users;
修改表名：RENAME TABLE users TO members;
添加列：ALTER TABLE users ADD email VARCHAR(100);
修改列名：ALTER TABLE users CHANGE name username VARCHAR(50);
修改列类型：ALTER TABLE users MODIFY age SMALLINT;
删除列：ALTER TABLE users DROP COLUMN email;
添加主键：ALTER TABLE users ADD PRIMARY KEY(id);
删除主键：ALTER TABLE users DROP PRIMARY KEY;
添加唯一约束：ALTER TABLE users ADD UNIQUE(email);
删除唯一约束：ALTER TABLE users DROP INDEX email;
添加外键：ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY(uid) REFERENCES users(id);
删除外键：ALTER TABLE orders DROP FOREIGN KEY fk_user;
添加默认值：ALTER TABLE users ALTER age SET DEFAULT 18;
删除默认值：ALTER TABLE users ALTER age DROP DEFAULT;
查看索引：SHOW INDEX FROM users;
添加普通索引：CREATE INDEX idx_age ON users(age);
删除索引：DROP INDEX idx_age ON users;
创建唯一索引：CREATE UNIQUE INDEX idx_name ON users(name);
复制表结构：CREATE TABLE new_users LIKE users;
复制表数据：INSERT INTO new_users SELECT * FROM users;
查看表状态：SHOW TABLE STATUS LIKE 'users';
修改表存储引擎：ALTER TABLE users ENGINE=InnoDB;
查看表大小：SELECT table_name, data_length+index_length FROM information_schema.tables WHERE table_schema='db1';
查看自增值：SHOW TABLE STATUS LIKE 'users'\G;

四、数据操作

插入数据：INSERT INTO users(name,age)VALUES('Tom',20);
批量插入：INSERT INTO users(name,age)VALUES('A',18),('B',19);
插入或更新：INSERT INTO users(id,name)VALUES(1,'Tom') ON DUPLICATE KEY UPDATE name='Tom';
更新数据：UPDATE users SET age=22 WHERE name='Tom';
删除数据：DELETE FROM users WHERE id=1;
删除所有数据：DELETE FROM users;
查询所有：SELECT * FROM users;
查询部分列：SELECT name,age FROM users;
条件查询：SELECT * FROM users WHERE age>20;
多条件查询：SELECT * FROM users WHERE age>20 AND name='Tom';
模糊查询：SELECT * FROM users WHERE name LIKE 'T%';
IN 查询：SELECT * FROM users WHERE age IN(18,20,25);
NOT IN 查询：SELECT * FROM users WHERE age NOT IN(18,20,25);
BETWEEN 查询：SELECT * FROM users WHERE age BETWEEN 20 AND 30;
NULL 查询：SELECT * FROM users WHERE email IS NULL;
NOT NULL 查询：SELECT * FROM users WHERE email IS NOT NULL;
排序：SELECT * FROM users ORDER BY age DESC;
去重：SELECT DISTINCT name FROM users;
限制行数：SELECT * FROM users LIMIT 10;
分页：SELECT * FROM users LIMIT 10 OFFSET 20;
别名：SELECT name AS username FROM users;
聚合-计数：SELECT COUNT(*) FROM users;
聚合-平均：SELECT AVG(age) FROM users;
聚合-最大：SELECT MAX(age) FROM users;
聚合-最小：SELECT MIN(age) FROM users;
聚合-求和：SELECT SUM(age) FROM users;
分组：SELECT age,COUNT(*) FROM users GROUP BY age;
分组过滤：SELECT age,COUNT(*) FROM users GROUP BY age HAVING COUNT(*)>2;
联表查询：SELECT u.name,o.amount FROM users u JOIN orders o ON u.id=o.uid;
左连接：SELECT u.name,o.amount FROM users u LEFT JOIN orders o ON u.id=o.uid;

五、查询与条件

右连接：SELECT u.name,o.amount FROM users u RIGHT JOIN orders o ON u.id=o.uid;
全连接（模拟）：SELECT * FROM users u LEFT JOIN orders o ON u.id=o.uid UNION SELECT * FROM users u RIGHT JOIN orders o ON u.id=o.uid;
子查询：SELECT * FROM users WHERE id IN(SELECT uid FROM orders);
EXISTS 子查询：SELECT * FROM users u WHERE EXISTS(SELECT 1 FROM orders o WHERE o.uid=u.id);
CASE 表达式：SELECT name,CASE WHEN age>=18 THEN 'adult' ELSE 'child' END FROM users;
IF 函数：SELECT name,IF(age>=18,'Y','N') FROM users;
COALESCE：SELECT COALESCE(email,'N/A') FROM users;
CONCAT：SELECT CONCAT(name,'-',age) FROM users;
LENGTH：SELECT LENGTH(name) FROM users;
SUBSTRING：SELECT SUBSTRING(name,1,2) FROM users;
UPPER：SELECT UPPER(name) FROM users;
LOWER：SELECT LOWER(name) FROM users;
NOW：SELECT NOW();
DATE 函数：SELECT CURDATE();
时间加减：SELECT DATE_ADD(NOW(),INTERVAL 7 DAY);
DATEDIFF：SELECT DATEDIFF(NOW(),'2025-01-01');
RAND：SELECT RAND();
LIMIT 排序随机取样：SELECT * FROM users ORDER BY RAND() LIMIT 1;
正则查询：SELECT * FROM users WHERE name REGEXP '^T';
JSON 查询：SELECT JSON_EXTRACT(info,'$.age') FROM users;

六、索引 / 视图 / 存储过程 / 触发器

创建索引：CREATE INDEX idx_age ON users(age);
删除索引：DROP INDEX idx_age ON users;
唯一索引：CREATE UNIQUE INDEX idx_email ON users(email);
组合索引：CREATE INDEX idx_name_age ON users(name,age);
查看索引：SHOW INDEX FROM users;
创建视图：CREATE VIEW v_users AS SELECT name,age FROM users;
查看视图：SHOW CREATE VIEW v_users;
删除视图：DROP VIEW v_users;
更新视图：CREATE OR REPLACE VIEW v_users AS SELECT id,name FROM users;
创建存储过程：
DELIMITER //
CREATE PROCEDURE get_all()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

调用存储过程：CALL get_all();
删除存储过程：DROP PROCEDURE get_all;
创建带参数存储过程：
CREATE PROCEDURE get_user(IN uid INT) SELECT * FROM users WHERE id=uid;

创建函数：
CREATE FUNCTION add_age(a INT,b INT) RETURNS INT RETURN a+b;

调用函数：SELECT add_age(1,2);
删除函数：DROP FUNCTION add_age;
创建触发器：
CREATE TRIGGER before_insert BEFORE INSERT ON users FOR EACH ROW SET NEW.age=IFNULL(NEW.age,18);

七、事务与锁管理

开始事务：START TRANSACTION;
提交事务：COMMIT;
回滚事务：ROLLBACK;
设置保存点：SAVEPOINT sp1;
回滚到保存点：ROLLBACK TO sp1;
释放保存点：RELEASE SAVEPOINT sp1;
设置自动提交：SET autocommit=0;
查看隔离级别：SHOW VARIABLES LIKE 'tx_isolation';
设置隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
显式锁表：LOCK TABLES users WRITE;
解锁表：UNLOCK TABLES;
查看锁等待：SHOW ENGINE INNODB STATUS\G;
查看元数据锁：SELECT * FROM performance_schema.metadata_locks;
悲观锁：SELECT * FROM users WHERE id=1 FOR UPDATE;
乐观锁（版本号实现）：UPDATE users SET age=age+1, version=version+1 WHERE id=1 AND version=2;
查看死锁信息：SHOW ENGINE INNODB STATUS\G;
杀死事务：KILL 1234;
查看运行事务：SELECT * FROM information_schema.innodb_trx;
查看锁等待情况：SELECT * FROM information_schema.innodb_locks;
查看锁等待关系：SELECT * FROM information_schema.innodb_lock_waits;

八、日志与性能优化

查看变量：SHOW VARIABLES;
查看状态：SHOW STATUS;
查看慢查询：SHOW VARIABLES LIKE 'slow_query_log';
开启慢查询日志：SET GLOBAL slow_query_log=1;
设置慢查询时间：SET GLOBAL long_query_time=2;
查看执行计划：EXPLAIN SELECT * FROM users WHERE age>20;
分析执行计划：EXPLAIN FORMAT=JSON SELECT * FROM users;
查看缓存：SHOW VARIABLES LIKE 'query_cache%';
清空缓存：RESET QUERY CACHE;
分析表：ANALYZE TABLE users;
优化表：OPTIMIZE TABLE users;
修复表：REPAIR TABLE users;
查看引擎状态：SHOW ENGINE INNODB STATUS\G;
查看事务统计：SHOW GLOBAL STATUS LIKE 'Com_commit';
Profiling 开启：SET profiling=1;
查看 Profiling：SHOW PROFILES;
查看具体耗时：SHOW PROFILE FOR QUERY 1;
查看内存使用：SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
Performance Schema 开启：UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_statements_history';
查看连接延迟：SHOW STATUS LIKE 'Threads_running';

九、备份恢复与导入导出

导出数据库：mysqldump -u root -p db1 > db1.sql
导出表：mysqldump -u root -p db1 users > users.sql
导出带数据和结构：mysqldump -u root -p --databases db1 > db1.sql
导出所有库：mysqldump -u root -p --all-databases > all.sql
导入数据库：mysql -u root -p db1 < db1.sql
交互式导入：SOURCE /path/db1.sql;
仅导出表结构：mysqldump -u root -p -d db1 > db1_schema.sql
仅导出数据：mysqldump -u root -p -t db1 > db1_data.sql
压缩导出：mysqldump -u root -p db1 | gzip > db1.sql.gz
压缩导入：gunzip < db1.sql.gz | mysql -u root -p db1
按条件导出：mysqldump -u root -p db1 users --where="age>20" > users.sql
导出带触发器和存储过程：mysqldump -u root -p --routines --triggers db1 > db1.sql
导出 CSV：SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',';
导入 CSV：LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',';
检查备份完整性：mysqlcheck -u root -p --all-databases