假設環境：學生成績管理資料庫
Students (Sno, Name, Gender, Age, Birth, Native, Dept)Courses (Cno, CourseName, Credit)Grades (Sno, Cno, Mark)
1. 欄位別名
-- 兩種寫法等價，建議統一用 AS 明確語意
SELECT Name AS '姓名', Age AS '年齡' FROM Students ORDER BY Age;
SELECT Name '姓名', Age '年齡' FROM Students ORDER BY Age;
2. 精確與模糊查詢
| 運算子 | 用途 | 範例 |
|---|
IN | 離散值清單 | WHERE Native IN ('湖南','四川') |
BETWEEN ... AND | 範圍 (含邊界) | WHERE Age BETWEEN 20 AND 30 |
= | 精確比對 | WHERE Name = '李山' |
LIKE | 模糊比對 | WHERE Name LIKE '李%' (以李開頭)
WHERE Name LIKE '%李%' (含李)
WHERE Name LIKE '_李%' (第二字為李) |
[] | 字元集合/範圍 | WHERE Cno LIKE '[AC]%' (A 或 C 開頭)
WHERE Cno LIKE '[A-C]%' (A~C 開頭) |
3. 時間型別處理
-- smalldatetime / datetime / date 直接用字串常數 (ISO 8601 最安全)
SELECT * FROM Students
WHERE Birth >= '1980-01-01' AND Birth <= '1980-12-31';
-- 或
WHERE Birth BETWEEN '1980-01-01' AND '1980-12-31';
**最佳實踐**：永遠使用 YYYY-MM-DD 或 YYYYMMDD 格式，避免語系/日期格式設定導致解析錯誤。
4. 聚合函數
SELECT COUNT(*) AS 總人數 FROM Students;
SELECT AVG(Mark) AS 平均分 FROM Grades WHERE Cno = 'B2';
SELECT MAX(Mark) AS 最高分, MIN(Mark) AS 最低分 FROM Grades WHERE Cno = 'B2';
5. 分組查詢 (GROUP BY)
-- 單欄位分組
SELECT Gender, COUNT(*) AS 人數 FROM Students GROUP BY Gender;
-- 多重分組 (依序：年級、科系、性別)
SELECT Grade, Dept, Gender, COUNT(*) AS 人數
FROM Students
GROUP BY Grade, Dept, Gender;
-- HAVING 過濾群組 (WHERE 過濾列，HAVING 過濾群組)
SELECT Sno, COUNT(*) AS 不及格科目數
FROM Grades
WHERE Mark < 60
GROUP BY Sno
HAVING COUNT(*) > 1; -- 至少 2 科不及格
6. 集合運算 (UNION / INTERSECT / EXCEPT)
-- UNION: 合併並去重 (UNION ALL 保留重複)
SELECT * FROM Students WHERE Name LIKE '張%'
UNION
SELECT * FROM Students WHERE Name LIKE '李%';
-- INTERSECT: 交集 (兩查詢結果皆存在)
-- EXCEPT: 差集 (第一查詢有、第二查詢無)
7. 多表連接
| 類型 | 語法 | 特性 | 範例 |
|---|
| 內連接 (INNER JOIN) | JOIN ... ON | 僅回傳雙方符合條件的列 | 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) | LEFT JOIN ... ON | 保留左表所有列，右表無對應則 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 |
| 右外連接 (RIGHT JOIN) | RIGHT JOIN ... ON | 保留右表所有列 (邏輯等同左連接交換順序) | 同上交換表順序 |
| 全外連接 (FULL JOIN) | FULL JOIN ... ON | 保留雙方所有列，無對應則 NULL | SELECT Sno, Name, ... FROM Students s FULL JOIN Grades g ON s.Sno=g.Sno |
8. 子查詢與衍生表
-- 標量子查詢 (回傳單值)
SELECT Name, (SELECT AVG(Mark) FROM Grades WHERE Sno = s.Sno) AS 平均分
FROM Students s;
-- 衍生表 (FROM 子查詢，需別名)
SELECT d.Dept, d.平均分
FROM (SELECT Dept, AVG(Mark) AS 平均分 FROM Students s JOIN Grades g ON s.Sno=g.Sno GROUP BY Dept) d
WHERE d.平均分 > 70;
9. 常用實用技巧
- 分頁**：
ORDER BY Id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (SQL 2012+) / ROW_NUMBER() OVER (ORDER BY Id) (通用)。
- 去重**：
SELECT DISTINCT Col1, Col2 FROM Table。
- 條件邏輯**：
CASE WHEN Sex='M' THEN '男' ELSE '女' END AS 性別。
- 空值處理**：
ISNULL(Col, '預設值') / COALESCE(Col1, Col2, '預設')。
結語
掌握這些基礎語法組合，即可應對 90% 日常查詢需求。進階之路：**窗格函數 (ROW_NUMBER, LAG, SUM OVER)**、CTE (WITH ...)、APPLY (CROSS/OUTER)、PIVOT/UNPIVOT、JSON/XML 處理、圖資料 (NODE/EDGE)。建立「標準 SQL 核心 + 廠商擴充」知識圖譜，查詢能力將無上限。
