MySQL SELECT 進階技巧大全：從基礎語法到多表查詢與分頁優化

SELECT 實用技巧彙總

1. 語句可換行書寫

$sql = "select * from article where id=1";
-- 等同於
$sql = "select * from article
where id=1";

長 SQL 分行更清晰。

2. 批量查詢用 IN

$sql = "select * from article where id in(1,3,5)";

3. CONCAT 合併欄位輸出

$sql = "select concat(id,'-',con) as res from article where id=1";
-- 返回 "1-article content"

4. LOCATE 字串定位

SELECT LOCATE("hello", "hello baby"); -- 返回 1
-- 不存在返回 0

5. GROUP BY 分組聚合

GROUP BY 將相同值分組，常搭配 AVG()、MIN()、MAX()、SUM()、COUNT()。

$sql = "select city, count(*) from customer group by city";
-- 列出所有不重複城市及其數量 (類似 DISTINCT 但帶統計)

6. HAVING 條件過濾分組

$sql = "select city, count(*), min(birth_day) from customer
group by city having count(*) > 10";
-- 先按 city 分組，再找出數量 > 10 的城市

注意：GROUP BY + HAVING 較慢；HAVING 中表達式必須在 SELECT 列表中出現過。

7. 子句標準順序

WHERE → GROUP BY → HAVING → ORDER BY（四者共用時按此序）

8. DISTINCT 去重

$sql = "select distinct city from customer order by id desc";
-- 查詢所有不重複 city

9. LIMIT 分頁與偏移

$sql = "select * from article limit 100, -1";
-- 顯示第 100 條之後所有記錄

10. 多表查詢必指定表前綴

$sql = "select user_name from user u, member m
where u.id = m.id and m.reg_date >= '2006-12-28'
order by u.id desc";

關鍵： 若兩表同有 user_name 欄位，必須指明屬哪表（如 u.user_name），否則 MySQL 報錯。