【100条mysql常用命令】

当然，这里是一些常用的 MySQL 命令列表，用于管理数据库和执行各种查询操作：

1. SHOW DATABASES; - 显示所有数据库
2. CREATE DATABASE database_name; - 创建新数据库
3. USE database_name; - 选择要操作的数据库
4. DROP DATABASE database_name; - 删除数据库
5. SHOW TABLES; - 显示所有的表
6. SHOW COLUMNS FROM table_name; - 显示表的字段
7. DESCRIBE table_name; - 显示表的结构
8. SELECT * FROM table_name; - 显示表中的所有数据
9. SELECT column1, column2, … FROM table_name; - 显示指定列的数据
10. SHOW INDEX FROM table_name; - 显示表的索引信息
11. SHOW CREATE TABLE table_name; - 显示表的创建语句
12. CREATE TABLE table_name (column1 datatype, column2 datatype, …); - 创建新表
13. ALTER TABLE table_name ADD column_name datatype; - 在表中添加新列
14. ALTER TABLE table_name DROP column_name; - 从表中删除列
15. ALTER TABLE table_name MODIFY column_name datatype; - 修改列的数据类型
16. DROP TABLE table_name; - 删除表
17. INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …); - 向表中插入新行
18. UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; - 更新表中的数据
19. DELETE FROM table_name WHERE condition; - 从表中删除数据
20. TRUNCATE TABLE table_name; - 清空表中的数据
21. CREATE INDEX index_name ON table_name (column1, column2, …); - 创建新索引
22. DROP INDEX index_name ON table_name; - 删除索引
23. SELECT COUNT(*) FROM table_name; - 检索表中的记录数量
24. SELECT MAX(column_name) FROM table_name; - 检索表中某列的最大值
25. SELECT MIN(column_name) FROM table_name; - 检索表中某列的最小值
26. SELECT AVG(column_name) FROM table_name; - 计算表中某列的平均值
27. SELECT SUM(column_name) FROM table_name; - 计算表中某列的总和
28. SELECT DISTINCT column_name FROM table_name; - 检索表中列的唯一值
29. SELECT * FROM table_name WHERE condition; - 检索满足条件的记录
30. SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name; - 连接两个表
31. SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]; - 按指定列对结果进行排序
32. SELECT column_name FROM table_name LIMIT offset, count; - 限制检索结果的数量
33. SELECT * FROM table_name WHERE column_name LIKE pattern; - 搜索符合指定模式的数据
34. SELECT * FROM table_name WHERE column_name IN (value1, value2, …); - 检索符合指定条件的数据
35. SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2; - 检索指定范围内的数据
36. SELECT * FROM table_name WHERE column_name IS NULL; - 检索为空值的数据
37. SELECT * FROM table_name WHERE column_name IS NOT NULL; - 检索非空值的数据
38. SHOW GRANTS; - 显示用户权限
39. GRANT type_of_permission ON database_name.table_name TO ‘username’@‘localhost’; - 向用户授予权限
40. REVOKE type_of_permission ON database_name.table_name FROM ‘username’@‘localhost’; - 取消用户权限
41. CREATE USER ‘username’@‘localhost’ IDENTIFIED BY ‘password’; - 创建新用户
42. DROP USER ‘username’@‘localhost’; - 删除用户
43. FLUSH PRIVILEGES; - 刷新权限
44. USE table_name; - 选择要操作的表
45. START TRANSACTION; - 开始事务
46. COMMIT; - 提交事务
47. ROLLBACK; - 回滚事务
48. SET autocommit = 0; - 关闭自动提交
49. SET autocommit = 1; - 开启自动提交
50. SET TRANSACTION ISOLATION LEVEL level; - 设置事务隔离级别
51. SHOW VARIABLES LIKE ‘variable_name’; - 显示指定变量的值
52. SET GLOBAL variable_name = value; - 设置全局变量的值
53. SET SESSION variable_name = value; - 设置会话变量的值
54. SHOW STATUS LIKE ‘status_name’; - 显示指定状态的值
55. KILL CONNECTION connection_id; - 终止指定连接
56. SHOW PROCESSLIST; - 显示当前的连接和查询
57. SHOW ENGINE engine_name STATUS; - 显示指定存储引擎的状态信息
58. SHOW ENGINE engine_name MUTEX; - 显示指定存储引擎的锁信息
59. SHOW ENGINE engine_name INNODB STATUS; - 显示InnoDB存储引擎的状态信息
60. ALTER USER ‘username’@‘localhost’ IDENTIFIED BY ‘new_password’; - 修改用户密码
61. SET PASSWORD FOR ‘username’@‘localhost’ = PASSWORD(‘new_password’); - 修改用户密码
62. EXPLAIN SELECT * FROM table_name; - 显示查询执行计划
63. SHOW WARNINGS; - 显示警告信息
64. SHOW ERRORS; - 显示错误信息
65. SHOW CREATE USER ‘username’@‘localhost’; - 显示用户的创建语句
66. CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name FOR EACH ROW trigger_body; - 创建触发器
67. DROP TRIGGER trigger_name; - 删除触发器
68. SHOW TRIGGERS; - 显示所有触发器
69. CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition; - 创建视图
70. DROP VIEW view_name; - 删除视图
71. SHOW CREATE VIEW view_name; - 显示创建视图的语句
72. OPTIMIZE TABLE table_name; - 优化表
73. ANALYZE TABLE table_name; - 分析表
74. REPAIR TABLE table_name; - 修复表
75. CREATE PROCEDURE procedure_name (parameter1, parameter2, …) BEGIN procedure_body END; - 创建存储过程
76. DROP PROCEDURE procedure_name; - 删除存储过程
77. CALL procedure_name; - 调用存储过程
78. CREATE FUNCTION function_name (parameter1, parameter2, …) RETURNS datatype BEGIN function_body END; - 创建函数
79. DROP FUNCTION function_name; - 删除函数
80. SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ‘database_name’; - 显示指定数据库中的表
81. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ‘database_name’ AND table_name = ‘table_name’; - 显示指定表的字段
82. SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = ‘database_name’ AND table_name = ‘table_name’; - 显示指定表的索引
83. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema = ‘database_name’ AND table_name = ‘table_name’; - 显示指定表的外键
84. SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = ‘database_name’; - 显示指定数据库中的存储过程和函数
85. FLUSH TABLES WITH READ LOCK; - 锁定表
86. UNLOCK TABLES; - 解锁表
87. SHOW MASTER STATUS; - 显示主服务器的状态
88. SHOW BINARY LOGS; - 显示二进制日志
89. SHOW SLAVE STATUS; - 显示从服务器的状态
90. CHANGE MASTER TO …; - 配置从服务器连接主服务器
91. RESET SLAVE; - 重置从服务器
92. mysqldump -u username -p database_name > /path/to/dump.sql; - 导出数据库
93. mysql -u username -p database_name < /path/to/dump.sql; - 导入数据库
94. CREATE EVENT event_name ON SCHEDULE schedule DO event_body; - 创建事件调度
95. ALTER EVENT event_name ON SCHEDULE schedule; - 修改事件调度
96. DROP EVENT event_name; - 删除事件调度
97. SHOW EVENTS; - 显示所有事件调度
98. SET GLOBAL event_scheduler = ON; - 启用事件调度器
99. SET GLOBAL event_scheduler = OFF; - 禁用事件调度器
100. SELECT * FROM performance_schema.table_name; - 查询性能监控数据

这些命令可以帮助您管理数据库、执行查询或者对数据库进行进一步的维护。