$sql = "SELECT * FROM article
WHERE id = 1";
SELECT * FROM article WHERE id IN (1,3,5);
SELECT CONCAT(id, '-', content) AS res FROM article WHERE id = 1;
-- 結果: "1-article content"
SELECT LOCATE('hello', 'hello baby'); -- 回傳 1
SELECT LOCATE('xyz', 'hello'); -- 回傳 0 (不存在)
SELECT city, COUNT(*) AS cnt
FROM customer
GROUP BY city;
常搭配彙總函數:AVG(), MIN(), MAX(), SUM(), COUNT()。
SELECT city, COUNT(*), MIN(birth_day)
FROM customer
GROUP BY city
HAVING COUNT(*) > 10;
HAVING 針對分組後結果過濾;WHERE 針對原始列。HAVING 條件欄位必須出現在 SELECT 或 GROUP BY 中。
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...
SELECT DISTINCT city FROM customer ORDER BY id DESC;
-- 前 10 筆
SELECT * FROM article LIMIT 10;
-- 第 11-20 筆 (分頁)
SELECT * FROM article LIMIT 10 OFFSET 10;
-- 或
SELECT * FROM article LIMIT 10, 10;
-- 第 101 筆到最後
SELECT * FROM article LIMIT 100, -1; -- MySQL 支援 -1 表剩餘所有
SELECT u.user_name
FROM user u, member m
WHERE u.id = m.id
AND m.reg_date >= '2006-12-28'
ORDER BY u.id DESC;
注意: 兩表同名欄位必須加表別名限定 (如 u.user_name),否則報錯「Column 'user_name' in field list is ambiguous」。
GROUP_CONCAT() 將群組值串成字串。WITH ROLLUP 產生超級彙總行。EXPLAIN 分析執行計畫。WHERE id > last_id LIMIT 10 (游標式分頁)。