MySQL 常用命令及技巧

连接与登录

连接MySQL服务器

1
2
3
4
5
6
7
8
# 本地连接
mysql -u root -p

# 指定主机和端口连接
mysql -h 127.0.0.1 -P 3306 -u root -p

# 连接指定数据库
mysql -u root -p -D database_name

退出MySQL

1
2
3
exit;
-- 或
quit;

数据库操作

创建数据库

1
2
3
4
5
6
7
8
-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS database_name;

查看数据库

1
2
3
4
5
-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

切换数据库

1
USE database_name;

删除数据库

1
2
3
4
5
-- 删除数据库
DROP DATABASE database_name;

-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;

表操作

创建表

1
2
3
4
5
6
7
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看表结构

1
2
3
4
5
6
7
8
9
-- 查看表结构
DESC table_name;
DESCRIBE table_name;

-- 查看表的创建语句
SHOW CREATE TABLE table_name;

-- 查看所有表
SHOW TABLES;

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 添加列
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100);

-- 修改列
ALTER TABLE table_name MODIFY COLUMN column_name INT NOT NULL;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
-- 或
RENAME TABLE old_table_name TO new_table_name;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX unique_index_name (column_name);

-- 删除索引
ALTER TABLE table_name DROP INDEX index_name;

删除表

1
2
3
4
5
6
7
8
-- 删除表
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

-- 清空表数据（保留表结构）
TRUNCATE TABLE table_name;

数据操作（CRUD）

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
-- 插入单条数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 插入多条数据
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);

-- 从查询结果插入
INSERT INTO table1 (column1, column2) 
SELECT column1, column2 FROM table2;

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查询所有数据
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2 FROM table_name;

-- 条件查询
SELECT * FROM table_name WHERE condition;

-- 排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

-- 限制结果数量
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 5, 10;  -- 跳过前5条，取10条

-- 分组查询
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 分组过滤
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

-- 去重
SELECT DISTINCT column1 FROM table_name;

更新数据

1
2
3
4
5
-- 更新数据
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

-- 更新多个值
UPDATE table_name SET column1 = value1 WHERE id IN (1, 2, 3);

删除数据

1
2
3
4
5
-- 删除数据
DELETE FROM table_name WHERE condition;

-- 删除所有数据
DELETE FROM table_name;

高级查询

连接查询

1
2
3
4
5
6
7
8
9
10
11
-- 内连接
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id;

-- 左连接
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id;

-- 右连接
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.table1_id;

-- 自连接
SELECT a.*, b.* FROM table1 a, table1 b WHERE a.parent_id = b.id;

子查询

1
2
3
4
5
6
7
8
-- WHERE子句中的子查询
SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2);

-- FROM子句中的子查询
SELECT * FROM (SELECT * FROM table1 WHERE condition) AS temp;

-- EXISTS子查询
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.table1_id = table1.id);

联合查询

1
2
3
4
5
6
7
8
9
-- UNION（去重）
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- UNION ALL（不去重）
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

索引操作

创建索引

1
2
3
4
5
6
7
8
9
10
11
-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name (column_name);

-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 创建全文索引
CREATE FULLTEXT INDEX fulltext_index_name ON table_name (column_name);

查看索引

1
2
-- 查看表索引
SHOW INDEX FROM table_name;

删除索引

1
2
-- 删除索引
DROP INDEX index_name ON table_name;

用户与权限管理

用户管理

1
2
3
4
5
6
7
8
9
10
11
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 删除用户
DROP USER 'username'@'host';

-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 或使用旧版本语法
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';

-- 授予所有数据库权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';

事务管理

事务操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

事务隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 隔离级别选项：
-- READ UNCOMMITTED（读未提交）
-- READ COMMITTED（读已提交）
-- REPEATABLE READ（可重复读，MySQL默认）
-- SERIALIZABLE（串行化）

备份与恢复

数据备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > backup.sql

# 只备份表结构
mysqldump -u root -p --no-data database_name > backup.sql

# 只备份数据
mysqldump -u root -p --no-create-info database_name > backup.sql

数据恢复

1
2
3
4
5
# 恢复数据库
mysql -u root -p database_name < backup.sql

# 在MySQL命令行中恢复
source /path/to/backup.sql;

性能优化

查看执行计划

1
2
3
4
5
-- 查看查询执行计划
EXPLAIN SELECT * FROM table_name WHERE condition;

-- 查看详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM table_name WHERE condition;

分析表

1
2
3
4
5
6
7
8
9
10
11
-- 分析表
ANALYZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

-- 修复表
REPAIR TABLE table_name;

查看性能指标

1
2
3
4
5
6
7
8
9
10
11
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';

-- 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';

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

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

常用函数

字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World');

-- 字符串长度
SELECT LENGTH('Hello');

-- 转换大小写
SELECT UPPER('hello');
SELECT LOWER('HELLO');

-- 字符串截取
SELECT SUBSTRING('Hello World', 1, 5);

-- 字符串替换
SELECT REPLACE('Hello World', 'World', 'MySQL');

-- 去除空格
SELECT TRIM('  Hello  ');
SELECT LTRIM('  Hello');
SELECT RTRIM('Hello  ');

数值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 4

-- 向下取整
SELECT FLOOR(3.14);  -- 3

-- 绝对值
SELECT ABS(-10);  -- 10

-- 取模
SELECT MOD(10, 3);  -- 1

-- 随机数
SELECT RAND();

日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 当前日期时间
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- 日期差值
SELECT DATEDIFF('2024-12-31', '2024-01-01');

-- 提取日期部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 计数
SELECT COUNT(*) FROM table_name;

-- 求和
SELECT SUM(column_name) FROM table_name;

-- 平均值
SELECT AVG(column_name) FROM table_name;

-- 最大值
SELECT MAX(column_name) FROM table_name;

-- 最小值
SELECT MIN(column_name) FROM table_name;

实用技巧

批量插入优化

1
2
3
4
5
6
7
8
9
10
-- 使用批量插入代替单条插入
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4);

-- 使用LOAD DATA导入大量数据
LOAD DATA INFILE '/path/to/file.csv' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

查询优化技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 使用索引覆盖
SELECT indexed_column FROM table_name WHERE condition;

-- 避免SELECT *
SELECT column1, column2 FROM table_name;

-- 使用LIMIT优化分页
SELECT * FROM table_name LIMIT offset, page_size;

-- 使用EXISTS代替IN（大数据量时）
SELECT * FROM table1 WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table2.id = table1.id
);

字符集与排序规则

1
2
3
4
5
6
7
8
9
10
11
-- 查看数据库字符集
SHOW VARIABLES LIKE 'character%';

-- 查看排序规则
SHOW VARIABLES LIKE 'collation%';

-- 修改表字符集
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改列字符集
ALTER TABLE table_name MODIFY column_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看MySQL状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看MySQL版本
SELECT VERSION();

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

-- 查看当前用户
SELECT USER();

-- 查看MySQL状态
SHOW STATUS;

-- 查看MySQL变量
SHOW VARIABLES;

常见问题解决

忘记root密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 1. 停止MySQL服务
sudo systemctl stop mysql

# 2. 以安全模式启动MySQL
sudo mysqld_safe --skip-grant-tables &

# 3. 无密码登录
mysql -u root

# 4. 修改密码
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

# 5. 重启MySQL服务
sudo systemctl restart mysql

修复损坏的表

1
2
3
4
5
6
7
8
9
10
11
-- 检查表
CHECK TABLE table_name;

-- 分析表
ANALYZE TABLE table_name;

-- 修复表
REPAIR TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

查看锁信息

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看InnoDB锁
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前进程
SHOW PROCESSLIST;

-- 杀死进程
KILL process_id;

总结

本文整理了MySQL常用的命令和技巧，包括：

  • 数据库和表的基本操作
  • 数据的增删改查
  • 高级查询技巧
  • 索引和性能优化
  • 用户权限管理
  • 备份恢复
  • 常用函数
  • 实用技巧和问题解决

掌握这些命令和技巧，可以帮助你更高效地使用MySQL数据库。