1. 基本查询查询所有记录和字段SELECT * FROM table_name;查询特定字段SELECT column1, column2 FROM table_name;查询并限制结果SELECT column1, column2 FROM table_name LIMIT 10;条件查询SELECT column1, column2 FROM table_name WHERE condition;模糊匹配SELECT column1 FROM table_name WHERE column2 LIKE pattern%;匹配多个值SELECT column1 FROM table_name WHERE column2 IN (value1, value2);不匹配某些值SELECT column1 FROM table_name WHERE column2 NOT IN (value1, value2);2. 数据插入插入单条记录INSERT INTO table_name (column1, column2) VALUES (value1, value2);插入多条记录INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4);从另一个表插入数据INSERT INTO table1 (column1, column2) SELECT column1, column2 FROM table2 WHERE condition;3. 数据更新更新单条记录UPDATE table_name SET column1 value1 WHERE condition;批量更新UPDATE table_name SET column1 value1, column2 value2 WHERE condition;基于其他表的更新(关联更新)UPDATE table1 JOIN table2 ON table1.common_column table2.common_column SET table1.column1 table2.column2 WHERE table2.condition;4. 数据删除删除单条记录DELETE FROM table_name WHERE condition;批量删除DELETE FROM table_name WHERE condition;删除表中的所有记录(也叫截断表)TRUNCATE TABLE table_name;5. 表操作创建表CREATE TABLE table_name ( ? ? id INT AUTO_INCREMENT PRIMARY KEY, ? ? column1 VARCHAR(255) NOT NULL, ? ? column2 INT DEFAULT 0, ? ? created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );修改表结构添加列ALTER TABLE table_name ADD column_name VARCHAR(255);删除列ALTER TABLE table_name DROP COLUMN column_name;修改列类型ALTER TABLE table_name MODIFY column_name INT;重命名列ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(255);删除表DROP TABLE table_name;删除数据库DROP DATABASE database_name;6. 索引管理创建索引CREATE INDEX index_name ON table_name (column_name);创建唯一索引CREATE UNIQUE INDEX index_name ON table_name (column_name);删除索引DROP INDEX index_name ON table_name;7. 表连接内连接INNER JOINSELECT * FROM table1 INNER JOIN table2 ON table1.common_column table2.common_column;左连接LEFT JOINSELECT * FROM table1 LEFT JOIN table2 ON table1.common_column table2.common_column;右连接RIGHT JOINSELECT * FROM table1 RIGHT JOIN table2 ON table1.common_column table2.common_column;全连接FULL JOINSELECT * FROM table1 LEFT JOIN table2 ON table1.common_column table2.common_column UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.common_column table2.common_column;8. 聚合函数计数SELECT COUNT(*) FROM table_name;平均值SELECT AVG(column_name) FROM table_name;总和SELECT SUM(column_name) FROM table_name;最大值SELECT MAX(column_name) FROM table_name;最小值SELECT MIN(column_name) FROM table_name;9. 分组与排序分组SELECT column1, COUNT(*) FROM table_name GROUP BY column1;排序SELECT * FROM table_name ORDER BY column1 ASC; SELECT * FROM table_name ORDER BY column1 DESC;分页SELECT * FROM table_name LIMIT 10 OFFSET 20;10. 子查询在 WHERE 子句中SELECT * FROM table_name WHERE column1 (SELECT MAX(column1) FROM table_name);在 FROM 子句中SELECT * FROM (SELECT column1, column2 FROM table_name) AS subquery WHERE condition;11. 条件表达式使用 CASESELECT column1, ? ? CASE ? ? ? ? WHEN condition1 THEN result1 ? ? ? ? WHEN condition2 THEN result2 ? ? ? ? ELSE default ? ? END AS new_column FROM table_name;12. 事务管理开始事务START TRANSACTION;提交事务COMMIT;回滚事务ROLLBACK;13. 备份与恢复备份mysqldump -u username -p database_name backup_file.sql恢复mysql -u username -p database_name backup_file.sql14. 用户管理创建用户CREATE USER usernamehost IDENTIFIED BY password;授予权限GRANT ALL PRIVILEGES ON database_name.* TO usernamehost;撤销权限REVOKE ALL PRIVILEGES ON database_name.* FROM usernamehost;删除用户DROP USER usernamehost;15. 表和数据库信息查看表结构DESCRIBE table_name;查看数据库列表SHOW DATABASES;查看表列表SHOW TABLES;查看表的索引SHOW INDEX FROM table_name;查看表的创建语句SHOW CREATE TABLE table_name;16. 查询优化查看查询计划EXPLAIN SELECT * FROM table_name WHERE condition;查看慢查询日志SHOW VARIABLES LIKE slow_query_log;启用慢查询日志SET GLOBAL slow_query_log ON;查看当前的数据库状态SHOW STATUS;17. 其他实用操作计算字段的平均值和总和SELECT column1, AVG(column2), SUM(column2) FROM table_name GROUP BY column1;使用 GROUP_CONCAT 函数SELECT column1, GROUP_CONCAT(column2) FROM table_name GROUP BY column1;这些 SQL 语句和技巧覆盖了数据库操作的广泛领域从基础查询到高级管理和优化。掌握这些可以帮助你更高效地管理和操作 MySQL 数据库。