系列文章：
MySQL常用命令与查询操作详解
一、MySQL基础命令
1.1 连接和退出命令
1.1.1 连接MySQL
# 基本连接方式
mysql -u root -p
# 连接到指定数据库
mysql -u root -p database_name
# 连接到远程服务器
mysql -h 192.168.1.100 -P 3306 -u username -p
# 使用Socket文件连接（Linux/macOS）
mysql -u root -p -S /var/run/mysqld/mysqld.sock
# 连接后执行命令并退出
mysql -u root -p -e "SHOW DATABASES;"
1.1.2 退出MySQL
-- 退出MySQL的几种方式
EXIT;
QUIT;
-- 或者使用快捷键 Ctrl+D
1.2 数据库管理命令
1.2.1 查看数据库
-- 查看所有数据库
SHOW DATABASES;
-- 查看当前数据库
SELECT DATABASE();
-- 查看数据库创建信息
SHOW CREATE DATABASE database_name;
1.2.2 创建数据库
-- 创建基本数据库
CREATE DATABASE my_database;
-- 创建数据库时指定字符集
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 创建数据库时指定字符集和排序规则
CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 如果数据库不存在则创建
CREATE DATABASE IF NOT EXISTS my_database;
1.2.3 使用数据库
-- 切换到指定数据库
USE my_database;
-- 查看当前使用的数据库
SELECT DATABASE();
1.2.4 修改数据库
-- 修改数据库字符集
ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改数据库名称（MySQL 8.0+）
RENAME DATABASE old_name TO new_name;
1.2.5 删除数据库
-- 删除数据库
DROP DATABASE my_database;
-- 如果数据库存在则删除
DROP DATABASE IF EXISTS my_database;
1.3 表管理命令
1.3.1 查看表
-- 查看当前数据库中的所有表
SHOW TABLES;
-- 查看表结构
DESCRIBE table_name;
-- 或者
DESC table_name;
-- 查看创建表的SQL语句
SHOW CREATE TABLE table_name;
-- 查看表的详细状态信息
SHOW TABLE STATUS LIKE 'table_name';
1.3.2 创建表
-- 创建基本表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建表时指定字符集
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 创建表时添加约束
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18),
status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 如果表不存在则创建
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.3.3 修改表结构
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 添加列到指定位置
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 修改列的数据类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
-- 修改列名和数据类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);
-- 删除列
ALTER TABLE users DROP COLUMN mobile;
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
-- 添加外键
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);
-- 删除索引
ALTER TABLE users DROP INDEX idx_username;
-- 重命名表
ALTER TABLE users RENAME TO customers;
1.3.4 删除表
-- 删除表
DROP TABLE users;
-- 如果表存在则删除
DROP TABLE IF EXISTS users;
-- 删除多个表
DROP TABLE table1, table2, table3;
1.4 数据操作命令
1.4.1 插入数据
-- 插入完整数据
INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25);
-- 插入部分数据（其他列使用默认值）
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');
-- 插入多行数据
INSERT INTO users (username, email, age) VALUES
('user1', 'user1@example.com', 30),
('user2', 'user2@example.com', 28),
('user3', 'user3@example.com', 35);
-- 插入数据时忽略错误
INSERT IGNORE INTO users (username, email) VALUES ('john_doe', 'john2@example.com');
-- 插入数据时如果主键冲突则更新
INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'new_email@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);
1.4.2 更新数据
-- 更新单条记录
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
-- 更新多个字段
UPDATE users SET email = 'new_email@example.com', age = 26 WHERE id = 1;
-- 基于条件更新多条记录
UPDATE users SET status = 'inactive' WHERE age < 18;
-- 使用子查询更新
UPDATE users SET status = 'premium'
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);
-- 更新时限制影响的行数
UPDATE users SET status = 'active' LIMIT 10;
1.4.3 删除数据
-- 删除单条记录
DELETE FROM users WHERE id = 1;
-- 基于条件删除多条记录
DELETE FROM users WHERE status = 'inactive';
-- 删除所有数据（保留表结构）
DELETE FROM users;
-- 删除所有数据的更快方式
TRUNCATE TABLE users;
-- 删除时限制影响的行数
DELETE FROM users WHERE status = 'banned' LIMIT 100;
1.5 用户管理命令
1.5.1 用户管理
-- 创建用户
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';
-- 创建远程用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password123';
-- 修改用户密码
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_password123';
-- 删除用户
DROP USER 'new_user'@'localhost';
-- 查看所有用户
SELECT user, host FROM mysql.user;
1.5.2 权限管理
-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';
-- 授予特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'user'@'host';
-- 授予只读权限
GRANT SELECT ON database_name.* TO 'user'@'host';
-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM 'user'@'host';
-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'host';
-- 查看用户权限
SHOW GRANTS FOR 'user'@'host';
-- 刷新权限使更改生效
FLUSH PRIVILEGES;
二、基础查询操作
2.1 SELECT语句基础
SELECT是MySQL中最常用的命令。
2.1.1 基本查询语法
-- 查询所有列
SELECT * FROM users;
-- 查询指定列
SELECT username, email FROM users;
-- 查询时使用别名
SELECT username AS name, email AS contact_email FROM users;
-- 查询时去重
SELECT DISTINCT status FROM users;
-- 查询时限制行数
SELECT * FROM users LIMIT 10;
-- 查询时限制行数并指定偏移量（分页）
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或者
SELECT * FROM users LIMIT 20, 10;
2.1.2 条件查询
WHERE子句用于过滤查询结果，这是查询中最常用的功能。
-- 等于条件
SELECT * FROM users WHERE id = 1;
-- 不等于条件
SELECT * FROM users WHERE status != 'active';
-- 大于小于条件
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age <= 65;
-- 范围条件
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
-- 列表条件
SELECT * FROM users WHERE status IN ('active', 'inactive');
-- 模糊查询
SELECT * FROM users WHERE username LIKE 'john%'; -- 以john开头
SELECT * FROM users WHERE username LIKE '%john'; -- 以john结尾
SELECT * FROM users WHERE username LIKE '%john%'; -- 包含john
SELECT * FROM users WHERE username LIKE 'j_hn'; -- j开头，h结尾，中间任意字符
-- NULL值查询
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
-- 多条件组合（AND）
SELECT * FROM users WHERE age > 18 AND status = 'active';
-- 多条件组合（OR）
SELECT * FROM users WHERE status = 'active' OR status = 'inactive';
-- 组合条件使用括号
SELECT * FROM users WHERE (age > 18 OR age < 65) AND status = 'active';
-- NOT条件
SELECT * FROM users WHERE NOT status = 'banned';
2.1.3 排序查询
ORDER BY子句用于对查询结果进行排序。
-- 升序排序（默认）
SELECT * FROM users ORDER BY username ASC;
-- 或者
SELECT * FROM users ORDER BY username;
-- 降序排序
SELECT * FROM users ORDER BY username DESC;
-- 按多个字段排序
SELECT * FROM users ORDER BY status ASC, age DESC;
-- 按表达式排序
SELECT * FROM users ORDER BY LENGTH(username) DESC;
-- 按别名排序
SELECT username, LENGTH(username) AS name_length FROM users ORDER BY name_length DESC;
2.2 聚合函数
聚合函数用于对一组值进行计算，返回单个值。
2.2.1 常用聚合函数
-- COUNT：计数
SELECT COUNT(*) FROM users; -- 计算总行数
SELECT COUNT(id) FROM users; -- 计算非NULL值的数量
SELECT COUNT(DISTINCT status) FROM users; -- 计算不同状态的数量
-- SUM：求和
SELECT SUM(age) FROM users;
SELECT SUM(age) FROM users WHERE status = 'active';
-- AVG：平均值
SELECT AVG(age) FROM users;
SELECT AVG(age) FROM users WHERE status = 'active';
-- MAX：最大值
SELECT MAX(age) FROM users;
SELECT MAX(created_at) FROM users;
-- MIN：最小值
SELECT MIN(age) FROM users;
SELECT MIN(created_at) FROM users;
2.2.2 聚合函数与GROUP BY
GROUP BY子句用于将结果集按一个或多个列进行分组。
-- 按状态分组统计用户数量
SELECT status, COUNT(*) as user_count FROM users GROUP BY status;
-- 按年龄分组统计
SELECT age, COUNT(*) as user_count FROM users GROUP BY age;
-- 按多个字段分组
SELECT status, age, COUNT(*) as user_count FROM users GROUP BY status, age;
-- 按状态分组计算平均年龄
SELECT status, AVG(age) as avg_age FROM users GROUP BY status;
-- 按状态分组计算年龄总和
SELECT status, SUM(age) as total_age FROM users GROUP BY status;
-- 使用HAVING过滤分组结果
SELECT status, COUNT(*) as user_count FROM users GROUP BY status HAVING COUNT(*) > 10;
-- 按状态分组并按数量排序
SELECT status, COUNT(*) as user_count FROM users GROUP BY status ORDER BY user_count DESC;
2.3 高级查询技巧
2.3.1 连接查询
连接查询用于从多个表中查询数据。
-- 内连接（INNER JOIN）
SELECT u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左连接（LEFT JOIN）
SELECT u.username, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 右连接（RIGHT JOIN）
SELECT u.username, o.order_id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 多表连接
SELECT u.username, o.order_id, p.product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
-- 带条件的连接查询
SELECT u.username, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed' AND o.total_amount > 100;
2.3.2 子查询
子查询是嵌套在其他查询中的查询。
-- WHERE子句中的子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);
-- FROM子句中的子查询（派生表）
SELECT user_count, avg_amount FROM (
SELECT user_id, COUNT(*) as user_count, AVG(total_amount) as avg_amount
FROM orders
GROUP BY user_id
) AS order_stats;
-- SELECT子句中的子查询
SELECT username,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;
-- EXISTS子查询
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed'
);
-- NOT EXISTS子查询
SELECT * FROM users u WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
2.3.3 联合查询
UNION用于合并多个SELECT语句的结果。
-- 基本UNION查询（去重）
SELECT username FROM users WHERE status = 'active'
UNION
SELECT username FROM users WHERE age > 30;
-- UNION ALL（不去重）
SELECT username FROM users WHERE status = 'active'
UNION ALL
SELECT username FROM users WHERE age > 30;
-- 带排序的UNION查询
SELECT username, 'active' as status FROM users WHERE status = 'active'
UNION
SELECT username, 'senior' as status FROM users WHERE age > 60
ORDER BY username;
三、实用查询示例
3.1 数据分析查询
这些查询在实际工作中经常用到，帮你快速分析数据。
3.1.1 统计分析
-- 用户注册趋势（按月统计）
SELECT
DATE_FORMAT(created_at, '%Y-%m') as month,
COUNT(*) as new_users
FROM users
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;
-- 用户年龄分布
SELECT
CASE
WHEN age < 20 THEN '0-19'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-39'
WHEN age BETWEEN 40 AND 49 THEN '40-49'
ELSE '50+'
END as age_group,
COUNT(*) as user_count
FROM users
GROUP BY age_group
ORDER BY age_group;
-- 用户状态统计
SELECT
status,
COUNT(*) as user_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM users), 2) as percentage
FROM users
GROUP BY status
ORDER BY user_count DESC;
3.1.2 排名查询
-- 用户消费金额排名
SELECT
username,
(SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE user_id = users.id) as total_spent,
RANK() OVER (ORDER BY (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE user_id = users.id) DESC) as rank
FROM users
ORDER BY total_spent DESC;
-- 用户订单数量排名
SELECT
username,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count,
DENSE_RANK() OVER (ORDER BY (SELECT COUNT(*) FROM orders WHERE user_id = users.id) DESC) as rank
FROM users
ORDER BY order_count DESC;
3.2 复杂条件查询
3.2.1 时间范围查询
-- 查询今天注册的用户
SELECT * FROM users WHERE DATE(created_at) = CURDATE();
-- 查询昨天注册的用户
SELECT * FROM users WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- 查询最近7天注册的用户
SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);
-- 查询本月注册的用户
SELECT * FROM users WHERE MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE());
-- 查询指定时间段内的用户
SELECT * FROM users
WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
3.2.2 文本搜索查询
-- 用户名包含特定字符串的用户
SELECT * FROM users WHERE username LIKE '%john%';
-- 邮箱域名为特定值的用户
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- 用户名以特定字符开头且长度大于5的用户
SELECT * FROM users WHERE username LIKE 'a%' AND LENGTH(username) > 5;
-- 使用正则表达式查询（MySQL支持基本正则）
SELECT * FROM users WHERE username REGEXP '^j.*n$'; -- 以j开头，以n结尾
3.3 分页查询
分页查询在Web应用中非常常用。
3.3.1 基础分页
-- 第一页，每页10条记录
SELECT * FROM users LIMIT 0, 10;
-- 第二页
SELECT * FROM users LIMIT 10, 10;
-- 第三页
SELECT * FROM users LIMIT 20, 10;
-- 通用分页公式：LIMIT (page-1)*pageSize, pageSize
3.3.2 高效分页（针对大数据量）
-- 使用WHERE子句进行分页（比OFFSET更高效）
-- 第一页
SELECT * FROM users WHERE id > 0 ORDER BY id LIMIT 10;
-- 第二页（需要记住上一页的最后ID）
SELECT * FROM users WHERE id > 10 ORDER BY id LIMIT 10;
-- 第三页
SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 10;
四、查询优化技巧
4.1 使用EXPLAIN分析查询
EXPLAIN命令可以帮助你理解MySQL是如何执行查询的。
4.1.1 基本用法
-- 分析简单查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 分析连接查询
EXPLAIN SELECT u.username, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;
-- 分析子查询
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);
4.1.2 理解EXPLAIN输出
EXPLAIN输出的关键字段：
- id：查询的标识符
- select_type：查询类型（SIMPLE, PRIMARY, SUBQUERY等）
- table：访问的表
- type：访问类型（const, eq_ref, ref, range, index, ALL）
- possible_keys：可能使用的索引
- key：实际使用的索引
- rows：预估扫描的行数
- Extra：额外信息
-- 查看详细的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;
4.2 索引优化查询
合理使用索引可以大幅提升查询性能。
4.2.1 创建合适的索引
-- 为常用查询条件创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_status_created ON users(status, created_at);
CREATE INDEX idx_email ON users(email);
-- 为连接字段创建索引
CREATE INDEX idx_user_id ON orders(user_id);
4.2.2 避免索引失效
-- 避免在索引列上使用函数
-- 不好的写法（索引失效）
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 好的写法（索引有效）
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 避免在索引列上进行计算
-- 不好的写法（索引失效）
SELECT * FROM users WHERE id + 1 = 10;
-- 好的写法（索引有效）
SELECT * FROM users WHERE id = 9;
4.3 查询性能优化
4.3.1 选择合适的列
-- 避免SELECT *
-- 不好的写法
SELECT * FROM users WHERE status = 'active';
-- 好的写法
SELECT id, username, email, status FROM users WHERE status = 'active';
4.3.2 使用LIMIT限制结果
-- 在测试查询时使用LIMIT
-- 不好的写法（可能返回大量数据）
SELECT * FROM users ORDER BY created_at DESC;
-- 好的写法
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;
4.3.3 优化JOIN查询
-- 避免笛卡尔积
-- 不好的写法
SELECT * FROM users, orders WHERE users.id = orders.user_id;
-- 好的写法
SELECT u.username, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;
五、常用管理命令
5.1 状态查看命令
这些命令帮助你了解MySQL的运行状态。
5.1.1 服务器状态
-- 查看MySQL版本
SELECT VERSION();
-- 查看服务器状态
SHOW STATUS;
-- 查看特定状态变量
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Slow_queries';
-- 查看服务器变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'character_set%';
5.1.2 进程信息
-- 查看当前进程
SHOW PROCESSLIST;
-- 查看完整的进程信息
SELECT * FROM information_schema.processlist;
-- 杀死特定进程
KILL 1234; -- 1234是进程ID
-- 杀死空闲时间过长的进程
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;
5.2 备份恢复命令
5.2.1 数据备份
# 备份单个数据库
mysqldump -u root -p database_name > database_name.sql
# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > databases.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql
# 备份特定表
mysqldump -u root -p database_name table1 table2 > tables.sql
# 备份时压缩
mysqldump -u root -p database_name | gzip > database_name.sql.gz
5.2.2 数据恢复
# 恢复数据库
mysql -u root -p database_name < database_name.sql
# 恢复压缩的备份
gunzip < database_name.sql.gz | mysql -u root -p database_name
# 恢复时指定字符集
mysql -u root -p --default-character-set=utf8mb4 database_name < database_name.sql
5.3 性能监控命令
5.3.1 慢查询日志
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
-- 查看慢查询
SELECT * FROM mysql.slow_log;
5.3.2 性能指标
-- 计算QPS（每秒查询数）
SHOW GLOBAL STATUS LIKE 'Questions';
-- 计算TPS（每秒事务数）
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
-- 查看缓冲池命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
六、常见问题解决
6.1 查询错误处理
6.1.1 语法错误
问题：ERROR 1064 (42000): You have an error in your SQL syntax
解决：
- 检查拼写错误
- 确保括号和引号匹配
- 检查关键字拼写
-- 常见语法错误示例
-- 错误：拼写错误
SLECT * FROM users; -- 应该是SELECT
-- 错误：引号不匹配
SELECT * FROM users WHERE username = 'john; -- 缺少结束引号
-- 错误：括号不匹配
SELECT * FROM users WHERE (status = 'active' AND age > 18; -- 缺少结束括号
6.1.2 权限错误
问题：ERROR 1142 (42000): SELECT command denied
解决：
-- 检查用户权限
SHOW GRANTS FOR 'current_user'@'localhost';
-- 授予所需权限
GRANT SELECT ON database_name.* TO 'user'@'host';
-- 刷新权限
FLUSH PRIVILEGES;
6.2 性能问题
6.2.1 查询缓慢
问题：查询执行时间过长
解决：
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username LIKE '%john%';
-- 创建合适的索引
CREATE INDEX idx_username ON users(username);
-- 优化查询语句
SELECT * FROM users WHERE username = 'john'; -- 避免前导通配符
6.2.2 连接过多
问题：ERROR 1040 (42000): Too many connections
解决：
-- 增加最大连接数
SET GLOBAL max_connections = 500;
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 杀死空闲连接
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;
七、实用技巧
7.1 命令行技巧
7.1.1 命令历史
-- 查看MySQL命令历史
cat ~/.mysql_history
-- 清除命令历史
cat /dev/null > ~/.mysql_history
7.1.2 批量执行
-- 从文件执行SQL命令
mysql -u root -p database_name < script.sql
-- 在MySQL中执行外部文件
SOURCE /path/to/script.sql;
7.2 查询技巧
7.2.1 使用变量
-- 设置用户变量
SET @min_age = 18;
SET @max_age = 65;
-- 使用变量
SELECT * FROM users WHERE age BETWEEN @min_age AND @max_age;
7.2.2 使用CASE语句
-- 使用CASE进行条件判断
SELECT
username,
age,
CASE
WHEN age < 20 THEN 'Young'
WHEN age BETWEEN 20 AND 35 THEN 'Adult'
WHEN age BETWEEN 36 AND 50 THEN 'Middle-aged'
ELSE 'Senior'
END as age_group
FROM users;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
