萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SELECT 進階技巧:IN、LIMIT、CONCAT、DISTINCT、GROUP BY 實戰

實用技巧速查

1. SQL 多行撰寫提升可讀性

$sql = "SELECT * FROM article
        WHERE id = 1";

2. 批次查詢用 IN

SELECT * FROM article WHERE id IN (1,3,5);

3. CONCAT 串接欄位

SELECT CONCAT(id, '-', content) AS res FROM article WHERE id = 1;
-- 結果: "1-article content"

4. LOCATE 字串搜尋

SELECT LOCATE('hello', 'hello baby'); -- 回傳 1
SELECT LOCATE('xyz', 'hello');      -- 回傳 0 (不存在)

5. GROUP BY 分組統計

SELECT city, COUNT(*) AS cnt
FROM customer
GROUP BY city;

常搭配彙總函數:AVG(), MIN(), MAX(), SUM(), COUNT()。

6. HAVING 過濾分組

SELECT city, COUNT(*), MIN(birth_day)
FROM customer
GROUP BY city
HAVING COUNT(*) > 10;

HAVING 針對分組後結果過濾;WHERE 針對原始列。HAVING 條件欄位必須出現在 SELECT 或 GROUP BY 中。

7. 子句順序

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

8. DISTINCT 去重

SELECT DISTINCT city FROM customer ORDER BY id DESC;

9. LIMIT 分頁與尾部查詢

-- 前 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 表剩餘所有

10. 多表查詢別名與欄位限定

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 (游標式分頁)。
copyright © 萬盛學電腦網 all rights reserved