MySQL 常考语句分类整理

一、数据查询（SELECT）

类型	示例
基础查询	SELECT * FROM users;
指定列	SELECT id, name FROM users;
条件查询	SELECT * FROM users WHERE age > 25;
排序	SELECT * FROM users ORDER BY age DESC;
去重	SELECT DISTINCT city FROM users;
分页	SELECT * FROM users LIMIT 10 OFFSET 20;
别名	SELECT name AS username FROM users;
模糊匹配	SELECT * FROM users WHERE name LIKE 'A%';
范围查询	SELECT * FROM users WHERE age BETWEEN 20 AND 30;
多条件	SELECT * FROM users WHERE age > 20 AND city = 'Beijing';

---

二、多表操作（连接与子查询）

类型	示例
内连接	SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;
左连接	SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;
右连接	SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
子查询（WHERE中）	SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);
子查询（FROM中）	SELECT t.user_id, COUNT(*) FROM (SELECT * FROM orders WHERE amount > 100) t GROUP BY t.user_id;

---

三、聚合与分组

类型	示例
总数	SELECT COUNT(*) FROM users;
求和	SELECT SUM(amount) FROM orders;
平均值	SELECT AVG(age) FROM users;
最大/最小值	SELECT MAX(age), MIN(age) FROM users;
分组统计	SELECT city, COUNT(*) FROM users GROUP BY city;
分组条件	SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;

---

四、数据更新与写入

类型	示例
插入	INSERT INTO users(name, age) VALUES('Alice', 30);
批量插入	INSERT INTO users(name, age) VALUES ('Bob', 25), ('Cathy', 22);
插入或更新	INSERT INTO users(id, name) VALUES (1, 'Tom') ON DUPLICATE KEY UPDATE name='Tom';
更新数据	UPDATE users SET age = 28 WHERE id = 1;
删除数据	DELETE FROM users WHERE age < 18;

---

五、索引与性能优化相关

类型	示例
查看索引	SHOW INDEX FROM users;
创建索引	CREATE INDEX idx_age ON users(age);
删除索引	DROP INDEX idx_age ON users;
使用执行计划	EXPLAIN SELECT * FROM users WHERE age > 30;
查看慢查询日志	SHOW VARIABLES LIKE 'slow_query_log%';
强制使用索引	SELECT * FROM users FORCE INDEX (idx_age) WHERE age > 25;

---

六、事务与锁操作

类型	示例
开启事务	START TRANSACTION; 或 BEGIN;
提交事务	COMMIT;
回滚事务	ROLLBACK;
设置隔离级别	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
查看当前隔离级别	SELECT @@tx_isolation;
悲观锁	SELECT * FROM users WHERE id = 1 FOR UPDATE;
乐观锁	UPDATE users SET age = 26, version = version + 1 WHERE id = 1 AND version = 2;

---

Bonus：面试高频场景题

问题	示例
查询每个用户的最后一笔订单	使用 GROUP BY + MAX(order_time) 或 子查询 + JOIN
查询重复数据	SELECT name, COUNT(*) FROM users GROUP BY name HAVING COUNT(*) > 1;
查询某字段为空	SELECT * FROM users WHERE phone IS NULL;
查询某天注册的用户	SELECT * FROM users WHERE DATE(register_time) = '2024-01-01';
分页优化	SELECT * FROM users WHERE id > ? LIMIT 10; 替代 OFFSET