| 運算子 | 說明 |
| =, <> (!=) | 等於、不等於 |
| >, >=, <, <= | 比較 |
| BETWEEN ... AND ... | 閉區間範圍 |
| IN (值清單) | 符合清單中任一值 |
| LIKE 模式 | 模糊比對 (% _ [] [^]) |
| IS [NOT] NULL | NULL 判斷 |
LIKE 萬用字元速查
LIKE 'AB%' -- 以 AB 開頭
LIKE '%abc' -- 以 abc 結尾
LIKE '%abc%' -- 包含 abc
LIKE '_ab' -- 任一字元 + ab (共三字元)
LIKE '[ACK]%' -- 以 A 或 C 或 K 開頭
LIKE '[A-T]ing' -- 以 A~T 開頭,以 ing 結尾 (四字元)
LIKE 'M[^c]%' -- 以 M 開頭,第二字元非 c
4. ORDER BY 子句:排序結果
SELECT * FROM students WHERE name LIKE '%影%' ORDER BY number; -- 遞增 (ASC)
SELECT * FROM students WHERE name LIKE '%影%' ORDER BY number DESC; -- 遞減
SELECT * FROM students ORDER BY class_id, number; -- 多欄位排序
5. TOP 子句:限制回傳筆數
SELECT TOP 3 * FROM students; -- 前 3 筆
SELECT TOP 10 PERCENT * FROM students; -- 前 10%
SELECT TOP 3 * FROM students ORDER BY number DESC; -- 最大 3 筆
6. 合併查詢 (UNION / UNION ALL)
SELECT * FROM students
UNION
SELECT * FROM students1; -- 合併並去除重複列
SELECT * FROM students
UNION ALL
SELECT * FROM students1; -- 合併保留重複列 (效能較好)
規則:兩查詢欄位數、順序、相容資料類型必須一致。結果欄位名稱取自第一個查詢。
7. 連接查詢 (JOIN)
ANSI-92 標準語法 (推薦)
-- 內連接:僅回傳符合條件的配對列
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;
-- 左外部連接:保留左表所有列,右表無配對則為 NULL
SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;
-- 右外部連接:保留右表所有列
SELECT s.name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;
-- 完整外部連接:保留兩表所有列
SELECT s.name, c.class_name
FROM students s
FULL JOIN classes c ON s.class_id = c.id;
-- 交叉連接:笛卡兒積
SELECT * FROM students CROSS JOIN classes;
自連接 (Self Join)
-- 員工與主管對照
SELECT e.name AS 員工, m.name AS 主管
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;
多表連接
SELECT o.order_id, c.name, p.product_name, od.qty
FROM orders o
INNER JOIN customers c ON o.cust_id = c.id
INNER JOIN order_details od ON o.id = od.order_id
INNER JOIN products p ON od.prod_id = p.id;
8. 子查詢
獨立標量子查詢 (回傳單值)
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
獨立多值子查詢 (回傳清單)
SELECT * FROM students
WHERE class_id IN (SELECT id FROM classes WHERE dept = 'CS');
相關子查詢 (參考外部查詢欄位)
SELECT * FROM students s
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.cust_id = s.id AND o.amount > 1000
);
9. 表運算式
派生表 (Derived Table)
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY stuId) AS rn, *
FROM Student
) AS d
WHERE d.rn BETWEEN 11 AND 20; -- 分頁
共用表運算式 (CTE)
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY stuId) AS rn, * FROM Student
)
SELECT * FROM cte WHERE rn BETWEEN 11 AND 20;
10. 檢視表 - 虛擬表
CREATE VIEW vw_student_summary AS
SELECT class_id, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM student
GROUP BY class_id;
SELECT * FROM vw_student_summary WHERE cnt > 30;
總結
從基礎的 SELECT/FROM/WHERE 到 JOIN、子查詢、CTE、VIEW,SQL 提供層層遞進的抽象能力。初學者應先精通單表查詢與基本 JOIN,再逐步掌握子查詢與 CTE 解決複雜邏輯,最終善用 VIEW 封裝常用查詢模式。
copyright ©
萬盛學電腦網 all rights reserved