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 JOIN)
SELECT * FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;
左连接(LEFT JOIN)
SELECT * FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;
右连接(RIGHT JOIN)
SELECT * FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
全连接(FULL JOIN)
SELECT * 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. 条件表达式
使用 CASE
SELECT 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
14. 用户管理
创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
删除用户
DROP USER 'username'@'host';
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 数据库。