SELECT
查询数据，支持条件过滤、多表连接、分组聚合、排序和分页
语法
SELECT [DISTINCT] columns FROM table [JOIN ...] [WHERE conditions] [GROUP BY columns [HAVING conditions]] [ORDER BY columns [ASC|DESC]] [LIMIT offset, count];
参数
| 参数 | 说明 | 示例 | 级别 |
|---|---|---|---|
WHERE |
条件过滤 | SELECT * FROM users WHERE status = 1; |
常用 |
JOIN |
多表连接查询 | SELECT * FROM orders o JOIN users u ON o.user_id = u.id; |
常用 |
GROUP BY |
分组聚合 | SELECT status, COUNT(*) FROM orders GROUP BY status; |
常用 |
ORDER BY |
排序 | SELECT * FROM orders ORDER BY created_at DESC; |
常用 |
LIMIT |
分页限制 | SELECT * FROM users LIMIT 10 OFFSET 20; |
常用 |
HAVING |
对分组结果过滤 | SELECT user_id, COUNT(*) c FROM orders GROUP BY user_id HAVING c > 5; |
进阶 |
示例
条件查询 + 分页
SELECT id, username, email, created_at FROM users WHERE status = 1 AND created_at >= '2026-01-01' ORDER BY created_at DESC LIMIT 20 OFFSET 0;
第一页 20 条，OFFSET 0 可省略
多表 JOIN 查询
SELECT o.order_no, o.total_amount, u.username, o.created_at FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 1 ORDER BY o.created_at DESC LIMIT 10;
INNER JOIN 只返回两表都匹配的记录
分组统计
SELECT DATE(created_at) AS order_date,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_total
FROM orders
WHERE status IN (1, 2, 3)
GROUP BY DATE(created_at)
ORDER BY order_date DESC
LIMIT 30;按天统计订单数和金额
子查询
SELECT username, email FROM users WHERE id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) >= 10 );
查找下单 10 次以上的用户
常见错误
ERROR 1054 (42S22): Unknown column 'xxx' in 'where clause'
列名拼写错误或该列不在当前表中，检查列名和表别名
ERROR 1055: 'column' isn't in GROUP BY (sql_mode=ONLY_FULL_GROUP_BY)
SELECT 的非聚合列必须出现在 GROUP BY 中，或使用 ANY_VALUE() 包裹
技巧
- 避免 SELECT *，只查需要的列，减少网络传输和内存占用
- 大表分页用 WHERE id > last_id LIMIT 20 代替 LIMIT offset, 20（深分页性能差）
- JOIN 时确保关联字段有索引