SQL 入門必讀：常用查詢、分組、聯集、多表聯結與子查詢實例總結

範例情境：學生成績管理資料庫

• `students(sno, name, gender, age, birth, native, mno)`
• `courses(cno, coursename)`
• `grades(sno, cno, mark)`
• `majors(mno, major)`

1. 欄位別名

SELECT name AS '姓名' FROM students ORDER BY age
SELECT name '姓名' FROM students ORDER BY age  -- AS 可省略

2. 精確篩選

-- IN 範圍
SELECT * FROM students WHERE native IN ('湖南', '四川')

-- BETWEEN 範圍
SELECT * FROM students WHERE age BETWEEN 20 AND 30

-- 等值
SELECT * FROM students WHERE name = '李山'

-- LIKE 部分比對
SELECT * FROM students WHERE name LIKE '李%'      -- 以 李 開頭
SELECT * FROM students WHERE name LIKE '%李%'     -- 含 李
SELECT * FROM students WHERE name LIKE '_李%'     -- 第二字為 李

-- [] 字元集合/範圍
SELECT * FROM courses WHERE cno LIKE '[AC]%'      -- A 或 C 開頭
SELECT * FROM courses WHERE cno LIKE '[A-C]%'     -- A~C 開頭

3. 日期時間處理

-- smalldatetime 直接字串比對
SELECT * FROM students WHERE birth >= '1980-1-1' AND birth <= '1980-12-31'

4. 聚合函數

SELECT COUNT(*) FROM students                    -- 總人數
SELECT AVG(mark) FROM grades WHERE cno = 'B2'    -- B2 平均分
SELECT MAX(mark), MIN(mark) FROM grades          -- 最高/最低分

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

外部聯結

-- LEFT JOIN：保留左表所有列
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

-- RIGHT JOIN：保留右表所有列
-- FULL JOIN：保留雙邊所有列
SELECT s.sno, s.name, m.major
FROM students s
FULL JOIN majors m ON s.mno = m.mno

關鍵觀念

• 內聯結：僅回傳雙方符合條件的列。
• 左聯結：左表全部 + 右表符合項（無符合則 NULL）。
• 右聯結：右表全部 + 左表符合項。
• 全聯結：雙表全部列，無符合處填 NULL。