MySQL是一款非常流行的关系型数据库管理系统，广泛应用于不同领域。以下是MySQL常用语句50条，希望对大家的开发和学习有所帮助。
1. 创建数据库CREATE DATABASE mydatabase;2. 删除数据库DROP DATABASE mydatabase;3. 选择数据库USE mydatabase;4. 显示所有数据库SHOW DATABASES;5. 显示当前数据库的所有表SHOW TABLES;6. 创建表CREATE TABLE mytable (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT,email VARCHAR(100));7. 删除表DROP TABLE mytable;8. 查看表的结构DESCRIBE mytable;9. 插入数据INSERT INTO mytable (name, age, email) VALUES ('Tom', 23, 'tom@example.com');10. 更新数据UPDATE mytable SET age = 24 WHERE name = 'Tom';11. 删除数据DELETE FROM mytable WHERE name = 'Tom';12. 查找数据SELECT * FROM mytable WHERE age >20;13. 查找特定字段的数据SELECT name, age FROM mytable;14. 按字段排序SELECT * FROM mytable ORDER BY age DESC;15. 分组统计SELECT age, COUNT(*) FROM mytable GROUP BY age;16. 模糊搜索SELECT * FROM mytable WHERE name LIKE '%to%';17. 区间搜索SELECT * FROM mytable WHERE age BETWEEN 20 AND 30;18. 联合查询SELECT * FROM mytable1 UNION SELECT * FROM mytable2;19. 左连接查询SELECT * FROM mytable1 LEFT JOIN mytable2 ON mytable1.id = mytable2.id;20. 子查询SELECT * FROM mytable WHERE age >(SELECT AVG(age) FROM mytable);21. 更新表结构ALTER TABLE mytable ADD COLUMN address VARCHAR(200);22. 修改表结构ALTER TABLE mytable MODIFY COLUMN name VARCHAR(100);23. 删除表字段ALTER TABLE mytable DROP COLUMN email;24. 增加外键约束ALTER TABLE mytable ADD FOREIGN KEY (user_id) REFERENCES users(id);25. 删除外键约束ALTER TABLE mytable DROP FOREIGN KEY mytable_user_id_foreign;26. 创建索引CREATE INDEX myindex ON mytable(name);27. 删除索引DROP INDEX myindex ON mytable;28. 备份数据mysqldump -u root -p mydatabase >mydatabase.sql;29. 还原数据mysql -u root -p mydatabase< mydatabase.sql;30. 设置默认字符集ALTER DATABASE mydatabase CHARACTER SET utf8;31. 更改表字符集ALTER TABLE mytable CONVERT TO CHARACTER SET utf8;32. 创建用户CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';33. 修改用户密码SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('newpassword');34. 授权用户GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';35. 撤销权限REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'myuser'@'localhost';36. 刷新权限FLUSH PRIVILEGES;37. 查看当前连接SHOW PROCESSLIST;38. 中止连接KILL 1234;39. 启用二进制日志SET GLOBAL binlog_format = ROW;40. 查看二进制日志SHOW BINARY LOGS;41. 停止二进制日志SET GLOBAL SQL_LOG_BIN = OFF;42. 调整缓冲区大小SET GLOBAL key_buffer_size = 256M;43. 查看连接数SHOW STATUS WHERE `variable_name` = 'Threads_connected';44. 查看锁信息SHOW OPEN TABLES WHERE `In_use` >0;45. 手动加锁SELECT * FROM mytable WHERE id = 1 FOR UPDATE;46. 手动解锁COMMIT;47. 清空表数据TRUNCATE TABLE mytable;48. 执行系统命令system ls;49. 查看MySQL版本SELECT VERSION();50. 退出MySQL命令行EXIT;
