SQL 基礎語法完整指南：DQL/DML/DDL、條件查詢、排序、聚合、分組、JOIN、子查詢與特殊排序

ANSI SQL 核心語法體系化總覽

本文依功能模組整理標準 SQL 語法，涵蓋四大基礎操作、條件與模糊查詢、排序分頁、聚合分組、多表聯 JOIN、子查詢、特殊排序技巧，適用於 MySQL、PostgreSQL、SQL Server、Oracle 等主流資料庫 (細節差異見註)。

一、四大基礎操作 (CRUD)

-- 插入
INSERT INTO stu (name, no, age) VALUES ('張三','s2t14',22);
INSERT INTO tel (name, addr, email) SELECT sname, saddr, semail FROM students;  -- INSERT SELECT
SELECT * INTO tel FROM students;  -- SELECT INTO (建新表)

-- 刪除
DELETE FROM stu WHERE age < 20;
TRUNCATE TABLE students;  -- 清空表、重置身分識別、不觸發 Trigger

-- 修改
UPDATE stu SET age = 25 WHERE name = '張三';
UPDATE scores SET score = score + 5 WHERE score <= 85;  -- 運算式更新

-- 查詢
SELECT * FROM students;
SELECT sname, sage FROM stu;
SELECT sname AS 姓名, sage AS 年齡 FROM stu;
SELECT sname + '.' + sname AS 姓名 FROM students;  -- 字串串接 (SQL Server)
SELECT sname FROM students WHERE semail IS NULL;   -- 空值判斷
SELECT '河北新龍' AS 學校, sname FROM students;    -- 常數欄

二、排序與分頁

ORDER BY score DESC;                    -- 降序
ORDER BY score, course_id;              -- 多欄位排序
SELECT TOP 10 * FROM students;          -- SQL Server 限制筆數
SELECT * FROM students LIMIT 10;        -- MySQL/PostgreSQL
SELECT * FROM students OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- 標準分頁 (SQL 2008+)

三、特殊排序：字串內數字自然排序

資料：'13-1','13-10','13-100','13-108','14-3','14-1','13-18'
目標：13-1,13-10,13-18,13-100,13-108,14-1,14-3

SELECT list_num
FROM sell_record
ORDER BY
    CAST(LEFT(list_num, CHARINDEX('-', list_num)-1) AS INT),  -- 前段數字
    CAST(STUFF(list_num, 1, CHARINDEX('-', list_num), '') AS INT);  -- 後段數字

• CHARINDEX 找分隔符位置。
• LEFT/STUFF 切分前後段。
• CAST(... AS INT) 數值化排序。

四、模糊查詢 LIKE 與範圍查詢 BETWEEN

SELECT * FROM students WHERE sname LIKE '張%';      -- 以張開頭
SELECT * FROM scores WHERE score BETWEEN 60 AND 80; -- 含邊界
SELECT * FROM sales WHERE ord_date NOT BETWEEN '1992-08-01' AND '1993-08-01';

五、集合查詢 IN

SELECT sname FROM students WHERE saddr IN ('北京','廣州','上海') ORDER BY saddr;

六、聚合函數

七、分組查詢 GROUP BY / HAVING

-- 每門課平均分
SELECT course_id, AVG(score) FROM scores GROUP BY course_id;

-- 只有補考過的學員 (同一學員同一課出現 >1 次)
SELECT student_id, course_id, AVG(score)
FROM scores
GROUP BY student_id, course_id
HAVING COUNT(score) > 1;  -- 分組後過濾，不可用 WHERE

八、多表 JOIN

-- 內聯接 (兩種寫法等價)
SELECT s.sname, sc.course_id, sc.score
FROM students s, scores sc WHERE s.scode = sc.student_id;

SELECT s.sname, sc.course_id, sc.score
FROM students s INNER JOIN scores sc ON s.scode = sc.student_id;

-- 左外聯接 (保留學生表所有列)
SELECT s.sname, sc.course_id, sc.score
FROM students s LEFT JOIN scores sc ON s.scode = sc.student_id;

-- 右外聯接 (保留出版社所有列)
SELECT t.title_id, t.title, p.pub_name
FROM titles t RIGHT JOIN publishers p ON t.pub_id = p.pub_id;

九、子查詢與 EXISTS

-- 標量子查詢
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

-- 相關子查詢 (EXISTS 高效能)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id AND o.amount > 1000);

十、UNION / UNION ALL

SELECT * FROM a UNION SELECT * FROM b;      -- 去重
SELECT * FROM a UNION ALL SELECT * FROM b;  -- 保留重複

總結：SQL 寫作鐵律

1. 明確列出欄位，禁用 SELECT * (生產環境)。
2. WHERE 條件欄位**勿加函數**，善用索引。
3. 分組聚合：SELECT 非聚合欄位必在 GROUP BY。
4. 分組後過濾用 HAVING，列過濾用 WHERE。
5. 外聯接選擇驅動表：小表驅動大表、或依業務主表決定 LEFT/RIGHT。
6. 子查詢優先改寫為 JOIN 或 EXISTS。
7. 分頁使用標準 OFFSET/FETCH 或資料庫專用語法。

內化這套語法框架，面對任何關聯式資料庫都能寫出正確、可維護、高效能的 SQL。