萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 入門教學:從 SELECT 基礎到 JOIN 進階查詢

1. SELECT 子句:指定要回傳的欄位


SELECT *           -- 所有欄位 (除錯用,生產避免)
SELECT 欄位1, 欄位2 -- 指定欄位 (效能佳)
SELECT 欄位1 AS 別名, 欄位2 別名 -- 別名 (AS 可省略)

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


SELECT * FROM students;           -- 單一表
SELECT * FROM students, classes;  -- 交叉聯結 (笛卡兒積)

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);

常用運算子

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
運算子說明
=, <> (!=)等於、不等於
>, >=, <, <=比較
BETWEEN ... AND ...閉區間範圍
IN (值清單)符合清單中任一值
LIKE 模式模糊比對 (% _ [] [^])
IS [NOT] NULLNULL 判斷