MySQL常用SQL语句大全与实战技巧

1. MySQL 常用 SQL 语句概述

MySQL 作为最流行的开源关系型数据库管理系统，其 SQL 语句的熟练掌握是每个开发者和 DBA 的基本功。本文将系统梳理 MySQL 中最常用、最核心的 SQL 语句，涵盖数据定义、数据操作、数据查询和数据控制四大类，帮助读者快速掌握 MySQL 数据库操作的精髓。

在实际工作中，我们每天都需要与数据库打交道，无论是简单的数据查询还是复杂的业务逻辑实现，都离不开 SQL 语句的支持。掌握这些常用语句不仅能提高工作效率，还能避免许多潜在的性能问题和安全隐患。

2. 数据定义语言（DDL）

2.1 数据库操作

创建数据库是最基础的操作之一，语法如下：

CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这里特别推荐使用 utf8mb4 字符集，因为它支持完整的 Unicode 字符集，包括 emoji 表情符号。相比传统的 utf8，它能避免很多字符存储问题。

删除数据库需要谨慎操作：

DROP DATABASE IF EXISTS dbname;

使用 IF EXISTS 可以避免数据库不存在时报错，这是个实用的安全技巧。

2.2 表操作

创建表时需要考虑字段类型、索引、约束等多个因素：

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

几个关键点：

1. 使用自增主键是常见做法
2. 为常用查询字段添加索引
3. 时间戳字段自动更新
4. 明确指定存储引擎和字符集

修改表结构也是常见需求：

ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email, MODIFY COLUMN username VARCHAR(60), DROP INDEX idx_email, ADD INDEX idx_username_email (username, email);

3. 数据操作语言（DML）

3.1 插入数据

基础插入语句：

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

批量插入能显著提高性能：

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'), ('user3', 'user3@example.com');

3.2 更新数据

基本更新操作：

UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

使用 JOIN 进行复杂更新：

UPDATE orders o JOIN users u ON o.user_id = u.id SET o.status = 'completed' WHERE u.email = 'john@example.com';

3.3 删除数据

删除特定记录：

DELETE FROM users WHERE id = 1;

清空表数据：

TRUNCATE TABLE logs;

TRUNCATE 比 DELETE 更快，但无法回滚且会重置自增值。

4. 数据查询语言（DQL）

4.1 基础查询

简单查询：

SELECT id, username FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;

使用 DISTINCT 去重：

SELECT DISTINCT department FROM employees;

4.2 连接查询

内连接：

SELECT u.username, o.order_date, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id;

左连接（获取所有用户，即使没有订单）：

SELECT u.username, o.order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id;

4.3 聚合查询

常用聚合函数：

SELECT COUNT(*) as total_users, AVG(age) as avg_age, MAX(created_at) as latest_user FROM users;

GROUP BY 分组：

SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department HAVING employee_count > 5;

5. 高级查询技巧

5.1 子查询

WHERE 子句中的子查询：

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

FROM 子句中的子查询：

SELECT dept.name, emp_count.count FROM departments dept JOIN ( SELECT department_id, COUNT(*) as count FROM employees GROUP BY department_id ) emp_count ON dept.id = emp_count.department_id;

5.2 窗口函数

MySQL 8.0 引入的窗口函数非常强大：

SELECT product_name, category, price, RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank FROM products;

5.3 公用表表达式(CTE)

WITH 子句使复杂查询更清晰：

WITH top_customers AS ( SELECT user_id, SUM(amount) as total_spent FROM orders GROUP BY user_id ORDER BY total_spent DESC LIMIT 10 ) SELECT u.username, tc.total_spent FROM top_customers tc JOIN users u ON tc.user_id = u.id;

6. 索引优化相关语句

查看表索引：

SHOW INDEX FROM users;

添加索引：

ALTER TABLE users ADD INDEX idx_username (username);

创建复合索引：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

删除索引：

ALTER TABLE users DROP INDEX idx_username;

7. 事务控制语句

开始事务：

START TRANSACTION;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

设置保存点：

SAVEPOINT savepoint_name;

回滚到保存点：

ROLLBACK TO savepoint_name;

8. 用户和权限管理

创建用户：

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

授予权限：

GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';

撤销权限：

REVOKE INSERT ON dbname.* FROM 'username'@'localhost';

查看权限：

SHOW GRANTS FOR 'username'@'localhost';

9. 实用技巧和注意事项

9.1 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE username = 'john';

9.2 备份和恢复

导出数据库：

mysqldump -u username -p dbname > backup.sql

导入数据库：

mysql -u username -p dbname < backup.sql

9.3 性能优化建议

1. 避免 SELECT *，只查询需要的列
2. 为常用查询条件添加合适的索引
3. 注意 LIKE 查询的通配符位置（前导通配符无法使用索引）
4. 大表分页使用 WHERE id > ? LIMIT ? 代替 LIMIT ?, ?
5. 定期使用 ANALYZE TABLE 更新统计信息

10. 常见问题解决方案

10.1 忘记 root 密码

1. 停止 MySQL 服务
2. 启动 MySQL 跳过权限检查：

mysqld_safe --skip-grant-tables &

3. 连接 MySQL 并修改密码
4. 刷新权限并重启服务

10.2 处理死锁

查看最近死锁信息：

SHOW ENGINE INNODB STATUS;

10.3 大表修改结构

使用 pt-online-schema-change 工具避免锁表

11. MySQL 8.0 新特性

11.1 窗口函数

SELECT name, salary, AVG(salary) OVER (PARTITION BY department) as avg_dept_salary FROM employees;

11.2 公用表表达式(CTE)

递归查询组织架构：

WITH RECURSIVE org_tree AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org_tree ot ON e.manager_id = ot.id ) SELECT * FROM org_tree;

11.3 JSON 支持

SELECT id, JSON_EXTRACT(profile, '$.address.city') as city FROM users WHERE JSON_CONTAINS(profile, '"NY"', '$.address.state');

12. 性能监控语句

查看当前连接：

SHOW PROCESSLIST;

查看服务器状态：

SHOW STATUS LIKE 'Threads_connected';

查看变量设置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

13. 存储过程和函数

创建存储过程：

DELIMITER // CREATE PROCEDURE update_user_email(IN user_id INT, IN new_email VARCHAR(100)) BEGIN UPDATE users SET email = new_email WHERE id = user_id; SELECT ROW_COUNT() as rows_affected; END // DELIMITER ;

调用存储过程：

CALL update_user_email(1, 'new@example.com');

14. 触发器

创建触发器：

DELIMITER // CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END // DELIMITER ;

15. 视图

创建视图：

CREATE VIEW active_users AS SELECT id, username, email FROM users WHERE status = 'active' AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

使用视图：

SELECT * FROM active_users ORDER BY username;

16. 分区表

创建范围分区表：

CREATE TABLE logs ( id INT AUTO_INCREMENT, log_date DATE, message TEXT, PRIMARY KEY (id, log_date) ) PARTITION BY RANGE (YEAR(log_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pmax VALUES LESS THAN MAXVALUE );

17. 全文检索

创建全文索引：

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body);

使用全文检索：

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE);

18. 正则表达式查询

使用 REGEXP：

SELECT * FROM users WHERE username REGEXP '^[a-z]{3}[0-9]{2}$';

19. 临时表

创建临时表：

CREATE TEMPORARY TABLE temp_results ( id INT, name VARCHAR(100) );

20. 导入导出数据

导出查询结果到文件：

SELECT * INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM users;

从文件导入数据：

LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

21. 数据库维护

优化表：

OPTIMIZE TABLE large_table;

修复表：

REPAIR TABLE corrupted_table;

检查表：

CHECK TABLE important_table;

22. 复制相关语句

查看主库状态：

SHOW MASTER STATUS;

查看从库状态：

SHOW SLAVE STATUS\G

23. 性能调优语句

设置变量（会话级）：

SET SESSION sort_buffer_size = 1000000;

查看执行计划：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 'active';

24. 元数据查询

查看所有数据库：

SHOW DATABASES;

查看表结构：

DESCRIBE users;

或

SHOW CREATE TABLE users;

25. 日期和时间函数

常用日期操作：

SELECT NOW(), DATE_FORMAT(NOW(), '%Y-%m-%d'), DATEDIFF('2023-12-31', NOW()), DATE_ADD(NOW(), INTERVAL 1 MONTH);

26. 字符串函数

字符串处理：

SELECT CONCAT(first_name, ' ', last_name) as full_name, UPPER(username), SUBSTRING(email, 1, 5), LENGTH(password) FROM users;

27. 数学函数

数值计算：

SELECT ROUND(price, 2), CEILING(total), FLOOR(average), RAND() as random_value FROM products;

28. 流程控制函数

条件判断：

SELECT username, CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 65 THEN 'Adult' ELSE 'Senior' END as age_group FROM users;

29. 数据类型转换

类型转换：

SELECT CAST(price AS DECIMAL(10,2)), CONVERT('2023-01-01', DATE), CONCAT('ID: ', CAST(id AS CHAR)) FROM products;

30. 安全相关语句

密码加密：

SELECT PASSWORD('mypassword'); -- MySQL 5.7及之前 SELECT SHA2('mypassword', 256); -- MySQL 8.0推荐

创建加密用户：

CREATE USER 'secureuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

31. 空间数据函数

空间数据查询：

SELECT name, ST_AsText(location) as coordinates FROM places WHERE ST_Distance_Sphere(location, POINT(-73.935242, 40.730610)) < 1000;

32. 实用技巧总结

1. 使用 \G 替代 ; 在命令行客户端中垂直显示结果
2. 使用 --xml 选项以 XML 格式输出结果
3. 使用 tee 命令记录会话输出
4. 使用 \s 查看服务器状态和版本信息
5. 使用 source 命令执行外部 SQL 文件

33. 性能监控工具

查看表打开情况：

SHOW OPEN TABLES WHERE In_use > 0;

查看引擎状态：

SHOW ENGINE INNODB STATUS;

查看表统计信息：

ANALYZE TABLE important_table;

34. 备份策略语句

创建逻辑备份：

FLUSH TABLES WITH READ LOCK; -- 执行备份操作 UNLOCK TABLES;

35. 大型数据库管理

查看数据库大小：

SELECT table_schema as database_name, SUM(data_length + index_length) / 1024 / 1024 as size_mb FROM information_schema.tables GROUP BY table_schema;

查看表大小：

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) as size_mb FROM information_schema.tables WHERE table_schema = 'your_database' ORDER BY size_mb DESC;

36. 锁相关语句

查看当前锁：

SELECT * FROM performance_schema.metadata_locks; SELECT * FROM performance_schema.table_handles;

37. 字符集和校对规则

查看可用字符集：

SHOW CHARACTER SET;

查看校对规则：

SHOW COLLATION WHERE Charset = 'utf8mb4';

38. 事件调度器

创建定时事件：

CREATE EVENT daily_cleanup ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP DO DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

39. 插件管理

查看已安装插件：

SHOW PLUGINS;

40. 资源组管理

MySQL 8.0 新增资源组功能：

CREATE RESOURCE GROUP reports TYPE = USER VCPU = 0-1 THREAD_PRIORITY = 5;

41. 组复制相关

查看组复制状态：

SELECT * FROM performance_schema.replication_group_members;

42. 克隆插件

MySQL 8.0 克隆功能：

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

43. 数据字典操作

查看数据字典：

SELECT * FROM information_schema.tables WHERE table_schema = 'mysql';

44. 角色管理

创建和使用角色：

CREATE ROLE read_only; GRANT SELECT ON *.* TO read_only; GRANT read_only TO 'user1'@'localhost';

45. 动态权限管理

MySQL 8.0 动态权限：

GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'admin'@'localhost';

46. 密码管理策略

设置密码策略：

SET GLOBAL validate_password.policy = STRONG;

47. 连接控制插件

安装连接控制：

INSTALL PLUGIN connection_control SONAME 'connection_control.so'; INSTALL PLUGIN connection_control_failed_login_attempts SONAME 'connection_control.so';

48. 审计插件

安装企业审计：

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

49. 防火墙语句

安装企业防火墙：

INSTALL PLUGIN mysql_firewall SONAME 'mysql_firewall.so';

50. 版本升级检查

检查升级兼容性：

SELECT * FROM sys.schema_object_overview;

51. 性能模式查询

查询性能模式：

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;

52. sys 模式查询

使用 sys 模式：

SELECT * FROM sys.schema_table_lock_waits;

53. 诊断报告

生成诊断报告：

CALL sys.diagnostics(120, 30, 'current');

54. 内存使用查询

查看内存分配：

SELECT * FROM sys.memory_by_host_by_current_bytes;

55. IO 使用查询

查看 IO 统计：

SELECT * FROM sys.io_global_by_file_by_bytes;

56. 用户统计

查看用户连接：

SELECT * FROM sys.user_summary;

57. 语句分析

查看慢查询：

SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

58. 索引统计

查看未使用索引：

SELECT * FROM sys.schema_unused_indexes;

59. 等待事件

查看等待事件：

SELECT * FROM sys.wait_classes_global_by_avg_latency;

60. 存储引擎转换

转换表引擎：

ALTER TABLE large_table ENGINE = InnoDB;

61. 表空间管理

查看表空间：

SELECT * FROM information_schema.INNODB_TABLESPACES;

62. 缓冲池状态

查看缓冲池：

SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

63. 事务信息

查看当前事务：

SELECT * FROM information_schema.INNODB_TRX;

64. 锁等待

查看锁等待：

SELECT * FROM sys.innodb_lock_waits;

65. 临时表空间

查看临时表：

SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;

66. 压缩表信息

查看压缩表：

SELECT * FROM information_schema.INNODB_CMP;

67. 全文索引状态

查看 FTS 状态：

SELECT * FROM information_schema.INNODB_FT_CONFIG;

68. 数据字典统计

查看字典统计：

SELECT * FROM information_schema.INNODB_DATAFILES;

69. 表状态信息

查看表状态：

SHOW TABLE STATUS LIKE 'users';

70. 系统变量查询

查看变量：

SHOW VARIABLES LIKE 'innodb%';

71. 状态变量查询

查看状态：

SHOW STATUS LIKE 'Threads_%';

72. 字符集设置

设置字符集：

SET NAMES utf8mb4;

73. 时区设置

设置时区：

SET time_zone = '+8:00';

74. SQL 模式设置

设置 SQL 模式：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

75. 日志管理

查看日志设置：

SHOW VARIABLES LIKE 'log_%';

76. 二进制日志

查看 binlog：

SHOW BINARY LOGS;

77. 慢查询日志

查看慢查询：

SHOW VARIABLES LIKE 'slow_query_log%';

78. 错误日志

查看错误日志位置：

SHOW VARIABLES LIKE 'log_error';

79. 通用日志

开启通用日志：

SET GLOBAL general_log = 'ON';

80. 连接控制

查看连接：

SHOW VARIABLES LIKE 'max_connections';

81. 内存配置

设置缓冲池：

SET GLOBAL innodb_buffer_pool_size = 2147483648;

82. 线程池

查看线程：

SHOW VARIABLES LIKE 'thread%';

83. 查询缓存

查看查询缓存：

SHOW VARIABLES LIKE 'query_cache%';

84. 临时表配置

设置临时表：

SET GLOBAL tmp_table_size = 268435456;

85. 排序缓冲

设置排序缓冲：

SET GLOBAL sort_buffer_size = 4194304;

86. 连接缓冲

设置连接缓冲：

SET GLOBAL join_buffer_size = 8388608;

87. 表定义缓存

设置表缓存：

SET GLOBAL table_definition_cache = 2000;

88. 表打开缓存

设置打开表缓存：

SET GLOBAL table_open_cache = 4000;

89. 线程缓存

设置线程缓存：

SET GLOBAL thread_cache_size = 100;

90. 刷新方法

设置刷新方法：

SET GLOBAL innodb_flush_method = O_DIRECT;

91. 刷新日志

刷新日志：

FLUSH LOGS;

92. 刷新权限

刷新权限：

FLUSH PRIVILEGES;

93. 刷新表

刷新表：

FLUSH TABLES;

94. 重置状态

重置状态：

FLUSH STATUS;

95. 重置主机缓存

重置主机缓存：

FLUSH HOSTS;

96. 重置查询缓存

重置查询缓存：

RESET QUERY CACHE;

97. 主从复制

查看主从状态：

SHOW MASTER STATUS; SHOW SLAVE STATUS;

98. 组复制

查看组复制：

SELECT * FROM performance_schema.replication_group_members;

99. 半同步复制

设置半同步：

SET GLOBAL rpl_semi_sync_master_enabled = 1;

100. 最佳实践总结

1. 始终为表设置主键
2. 合理使用索引，但不要过度索引
3. 使用 InnoDB 作为默认存储引擎
4. 为生产环境配置适当的缓冲池大小
5. 定期维护和优化表
6. 监控慢查询并优化
7. 使用 EXPLAIN 分析查询计划
8. 考虑使用连接池管理连接
9. 实施适当的备份策略
10. 保持 MySQL 版本更新