SELECT
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORITY] [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
方括號內為可選子句,實際開發中高頻使用的是 SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT 核心鏈路。
SELECT 100 + 100, 50 >= (2+3), 100/3;
-- 200, 1, 33.3333
SELECT * FROM wp_threadtwitter_users; -- 全列(調試用)
SELECT name, url FROM wp_threadtwitter_users; -- 指定列(生產推薦)
SELECT url FROM wp_threadtwitter_users WHERE name = 'simaopig';
SELECT id, post_title, comment_count FROM wp_posts
WHERE comment_count > 30 AND comment_count < 50 LIMIT 2;
SELECT MAX(comment_count), MIN(comment_count) FROM wp_posts; -- 362, 0
SELECT COUNT(*) FROM wp_posts; -- 340
SELECT u.name, u.url FROM wp_threadtwitter_users AS u WHERE u.followers_count > 200;
SELECT id FROM wp_posts WHERE comment_count > 30 LIMIT 2; -- 前 2 行
SELECT id FROM wp_posts LIMIT 3, 2; -- 偏移 3 行,取 2 行(0 基)
SELECT id FROM wp_posts ORDER BY id DESC LIMIT 3; -- 降序
SELECT link_rel, link_name FROM wp_links GROUP BY link_rel LIMIT 2; -- 分組取首行
| 關鍵字 | 作用 |
|---|---|
| DISTINCT | 去重結果集 |
| SQL_CALC_FOUND_ROWS | 配合 FOUND_ROWS() 獲取無 LIMIT 總行數 |
| SQL_CACHE / SQL_NO_CACHE | 控制查詢緩存(MySQL 5.7+ 已棄用查詢緩存) |
| SQL_BUFFER_RESULT | 強制結果存入臨時表,釋放表鎖 |
| SQL_BIG_RESULT / SQL_SMALL_RESULT | 提示優化器結果集大小,選擇磁盤/內存臨時表 |
| HIGH_PRIORITY | 提升優先級,插隊執行 |
-- 1. 去重統計
SELECT DISTINCT state FROM president;
-- 2. 分組聚合 + 排序
SELECT sex, COUNT(*) AS cnt FROM student GROUP BY sex;
SELECT state, COUNT(*) AS cnt FROM president GROUP BY state ORDER BY cnt DESC LIMIT 4;
-- 3. 多維統計函數
SELECT MIN(score), MAX(score), SUM(score), AVG(score) FROM exam;
表名.列名 避免歧義。WHERE 或 ON 子句。INNER JOIN(內連接),再考慮 LEFT/RIGHT JOIN(外連接)。-- 查詢某天考試成績(學號版)
SELECT sc.student_id, e.date, sc.score, e.type
FROM event e, score sc
WHERE e.date = '2003-09-12' AND e.event_id = sc.event_id;
-- 查詢某天考試成績(姓名版,三表關聯)
SELECT stu.name, e.name, sc.score, e.type
FROM event e, score sc, student stu
WHERE e.date = '2003-09-12'
AND e.event_id = sc.event_id
AND sc.student_id = stu.student_id;
-- 缺席學生統計
SELECT stu.student_id, stu.name, COUNT(a.date) AS absences
FROM student stu, absence a
WHERE stu.student_id = a.student_id
GROUP BY stu.student_id;
type(避免 ALL)、key(用上索引)、rows(掃描行數)、Extra(避免 Using filesort/temporary)。SELECT *,減少網絡傳輸與緩存污染。SELECT 是 SQL 的靈魂,掌握其完整語法、聚合統計、多表關聯、優化器提示,能解決 90% 的業務查詢需求。記住:好的 SQL 是寫出來的,更是 EXPLAIN 出來的。