MySQL SELECT 語法大全：排序、分組、分頁、導出、UNION、去重實例

MySQL SELECT 語法大全：排序、分組、分頁、導出、UNION、去重實例

完整語法骨架

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, ...
  [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name']
  [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 col_a, col_b FROM my_table;

ORDER BY 排序

SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;
SELECT college, region, seed FROM tournament ORDER BY 2, 3;  -- 按位置
SELECT a, COUNT(b) FROM test_table ORDER BY a DESC;  -- 降序

GROUP BY 分組 + HAVING

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
-- HAVING 用於聚合條件，WHERE 用於非聚合
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;

LIMIT 分頁

SELECT * FROM tbl LIMIT 10;        -- 前 10 行 (0-9)
SELECT * FROM tbl LIMIT 5, 10;     -- 第 6-15 行
SELECT * FROM tbl LIMIT 95, 18446744073709551615;  -- 第 96 行到末尾

SELECT ... INTO OUTFILE 導出文件

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
-- INTO DUMPFILE 僅寫入單行無轉義，適合導出 BLOB

UNION 合併結果集

(SELECT a FROM t WHERE a=10 AND b=1)
UNION
(SELECT a FROM t WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;
-- UNION 去重，UNION ALL 保留重複

去重選項

SELECT DISTINCT a FROM table_name;
SELECT COUNT(DISTINCT a) FROM table_name;