您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 進階查詢技巧：子查詢、多表連接、集合運算與分組進階

編輯：MySQL 綜合教學

超越基本 SELECT，本文整理 MySQL 進階查詢模式：精確/模糊/範圍查詢、時間處理、聚合函數、多重分組、HAVING、UNION、內/外/交叉/自我連接、子查詢 (IN/EXISTS)、排序技巧與常見陷阱。

1. 精確與範圍查詢

IN 列舉：SELECT * FROM students WHERE native IN ('湖南','四川');
BETWEEN ... AND：SELECT * FROM students WHERE age BETWEEN 20 AND 30;
= 精確：SELECT * FROM students WHERE name = '李山';

2. 模糊查詢 (LIKE)

% 任意長度：name LIKE '李%' (以李開頭)、'%李%' (含李)、'%李' (以李結尾)
_ 單一字元：'_李%' (第二字為李)
[] 字元集合 (MySQL 不支援，屬 T-SQL)：MySQL 用 REGEXP 或 IN 替代。

3. 時間型別處理


SELECT * FROM students WHERE birth >= '1980-01-01' AND birth <= '1980-12-31';
-- 或
SELECT * FROM students WHERE YEAR(birth) = 1980;

4. 聚合函數

COUNT(*) 總筆數
AVG(mark) 平均分
MAX(mark)/MIN(mark) 最高/最低分

5. 分組進階 (GROUP BY + HAVING)


-- 單欄位分組
SELECT gender, COUNT(*) FROM students GROUP BY gender;

-- 多重分組：年級、系所、性別
SELECT grade, mno, gender, COUNT(*) FROM students GROUP BY grade, mno, gender;

-- HAVING 過濾分組結果：不及格超過 1 門的學生
SELECT sno, COUNT(*) FROM grades WHERE mark < 60 GROUP BY sno HAVING COUNT(*) > 1;

6. 集合運算 (UNION)


SELECT * FROM students WHERE name LIKE '張%'
UNION [ALL]
SELECT * FROM students WHERE name LIKE '李%';

UNION 去重，UNION ALL 保留重複 (效能較好)。

7. 多表連接

內連接：僅回傳雙方符合條件的列


    SELECT g.sno, s.name, c.coursename
    FROM grades g
    JOIN students s ON g.sno = s.sno
    JOIN courses c ON g.cno = c.cno;

左外連接：保留左表所有列，右表無對應則為 NULL


    SELECT c.cno, MAX(c.coursename), COUNT(g.sno)
    FROM courses c
    LEFT JOIN grades g ON c.cno = g.cno
    GROUP BY c.cno;

右外連接：保留右表所有列
全外連接：MySQL 不直接支援，可用 LEFT JOIN UNION RIGHT JOIN 模擬

自我連接：同一表以別名關聯 (如課程前置條件)


    SELECT c1.cno, c1.coursename, c1.pno, c2.coursename
    FROM courses c1, courses c2 WHERE c1.pno = c2.cno;

交叉連接：笛卡兒積 CROSS JOIN

8. 子查詢 (IN / EXISTS)


-- IN 子查詢：查詢李山的同鄉
SELECT * FROM students WHERE native IN (SELECT native FROM students WHERE name = '李山');

-- EXISTS 相關子查詢：選修 B2 課程的學生
SELECT * FROM students WHERE EXISTS (
    SELECT * FROM grades WHERE grades.sno = students.sno AND cno = 'B2'
);

9. 排序技巧

ORDER BY 3：依 SELECT 清單第 3 個運算式排序 (如 ORDER BY AVG(mark))。
ASC (預設) / DESC。

10. 常見陷阱與正確寫法

空值判斷：WHERE mark IS NULL (不可用 = NULL)

否定查詢陷阱：


    -- 錯誤：未選 B2 的學生 (會列出選了其他課的同學)
    SELECT * FROM students, grades WHERE students.sno = grades.sno AND grades.cno <> 'B2';

    -- 正確：NOT EXISTS
    SELECT * FROM students WHERE NOT EXISTS (
        SELECT * FROM grades WHERE grades.sno = students.sno AND cno = 'B2'
    );

全選查詢 (Relational Division)：選修「所有」課程的學生


    SELECT * FROM students WHERE NOT EXISTS (
        SELECT * FROM courses WHERE NOT EXISTS (
            SELECT * FROM grades WHERE grades.sno = students.sno AND grades.cno = courses.cno
        )
    );