SQL 入門基礎教學：SELECT、WHERE、JOIN、UNION 與子查詢

1. SELECT 子句：指定回傳欄位

SELECT * FROM students;              -- 所有欄位 (不建議生產用)
SELECT name, number FROM students;   -- 指定欄位，效能佳

2. FROM 子句：指定資料來源

SELECT * FROM students;  -- 單表
-- 多表查詢見 JOIN 章節

3. WHERE 子句：篩選條件

SELECT * FROM students WHERE name = '影子';
SELECT * FROM students WHERE name = '影子' AND number > 100;
SELECT * FROM students WHERE name = '影子' AND (number > 100 OR number < 50);

常用運算子：=, <>, >, <, >=, <=, BETWEEN, LIKE, IN

4. LIKE 與萬用字元

5. ORDER BY 排序

SELECT * FROM students WHERE name LIKE '%影%' ORDER BY number;      -- 遞增
SELECT * FROM students WHERE name LIKE '%影%' ORDER BY number DESC; -- 遞減

6. TOP 限制筆數

SELECT TOP 3 * FROM students;
SELECT TOP 10 PERCENT * FROM students;
SELECT TOP 3 * FROM students ORDER BY number DESC; -- 取最大 3 筆

7. AS 別名

SELECT number AS 學號, name AS 姓名 FROM students;

8. UNION 合併查詢

SELECT * FROM students
UNION
SELECT * FROM students1;          -- 預設去重

SELECT * FROM students
UNION ALL
SELECT * FROM students1;          -- 保留重複

規則：欄位數、型別、順序必須一致；結果欄位名取自第一個 SELECT。

9. JOIN 連接查詢

ANSI-92 語法 (推薦)

-- 內連接
SELECT t1.col, t2.col FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

-- 左外連接
SELECT t1.col, t2.col FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;

-- 右外連接
SELECT t1.col, t2.col FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;

ANSI-89 舊語法

SELECT * FROM table1, table2 WHERE table1.id = table2.id;  -- 內連接

10. 子查詢

-- 獨立標量子查詢
SELECT * FROM students WHERE class_id = (SELECT id FROM classes WHERE name = 'A班');

-- 獨立多值子查詢
SELECT * FROM students WHERE class_id IN (SELECT id FROM classes WHERE grade = 3);