SQL 入門必學：常用查詢語句與技巧完整筆記

前言

以「學生成績管理資料庫」為情境，彙整新手最常用的 SQL 查詢模式與技巧，助您快速上手關聯式資料庫操作。

1. 查詢結果欄位別名

-- 使用 AS 關鍵字
SELECT name AS '姓名' FROM students ORDER BY age;

-- 直接指定別名
SELECT name '姓名' FROM students ORDER BY age;

2. 精確與範圍查詢

• IN 列舉： SELECT * FROM students WHERE native IN ('湖南','四川');
• BETWEEN 區間： SELECT * FROM students WHERE age BETWEEN 20 AND 30;
• 等值比對： SELECT * FROM students WHERE name = '李山';
• LIKE 模糊匹配：
  • name LIKE '李%' — 以「李」開頭
  • name LIKE '%李%' — 含「李」者
  • name LIKE '_李%' — 第二字為「李」
• [] 字元範圍匹配： SELECT * FROM courses WHERE cno LIKE '[AC]%'; 或 '[A-C]%'

3. 日期時間處理

smalldatetime 可直接以字串比較：

SELECT * FROM students 
WHERE birth >= '1980-1-1' AND birth <= '1980-12-31';

4. 聚合函數

• COUNT(*) — 統計筆數：SELECT COUNT(*) FROM students;
• AVG(欄位) — 平均值：SELECT AVG(mark) FROM grades WHERE cno='B2';
• MAX(欄位) / MIN(欄位) — 最大/最小值

5. 分組統計 GROUP BY

-- 單欄分組：統計男女生人數
SELECT gender, COUNT(sno) 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 courses.cno, MAX(coursename), COUNT(sno)
  FROM courses LEFT JOIN grades ON courses.cno = grades.cno
  GROUP BY courses.cno;
  

• 右外聯結： 與左聯結對稱
• 全外聯結： 兩表資料全顯示

  SELECT sno, name, major
  FROM students FULL JOIN majors ON students.mno = majors.mno;
  

• 自我聯結： 利用別名解決同表關聯

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

• 交叉聯結： 笛卡兒積

  SELECT lastname + firstname 
  FROM lastname CROSS JOIN firstname;
  

8. 子查詢

• IN 子查詢： 查詢李山的同鄉

  SELECT * FROM students 
  WHERE native IN (SELECT native FROM students WHERE name = '李山');
  

• EXISTS 子查詢： 等價於 IN 但效能常更佳

  SELECT * FROM students 
  WHERE EXISTS (
    SELECT * FROM grades 
    WHERE grades.sno = students.sno AND cno = 'B2'
  );
  

9. 排序 ORDER BY

• ASC 升序（預設），DESC 降序
• 可用選取清單位置編號排序：ORDER BY 3 表示按第三欄排序

10. 實用技巧與陷阱

• 識別名含空格時用 [] 括住
• 查詢 NULL 值：WHERE mark IS NULL
• ANY 相當邏輯 OR，ALL 相當邏輯 AND
• 否定查詢陷阱： 查「未選修 B2 的學生」

  -- 錯誤寫法（會產生笛卡兒積導致錯誤結果）
  SELECT students.* 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'
  );
  

11. 多重巢狀子查詢思維：查「選修所有課程的學生」

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

外層排除「存在未選課程」的學生，中層從課程表找出未被選修的課程，內層驗證該學生是否選修該課程。