MySQL 標準 SQL 語句實戰：連接、子查詢、聯合、分頁與約束

連接查詢完整解析

交叉連接

-- 笛卡爾積，TABLE1 5行 × TABLE2 7行 = 35行
SELECT * FROM CUSTOMERS, ORDERS;
-- 或
SELECT * FROM CUSTOMERS CROSS JOIN ORDERS;

內連接

-- 僅返回符合條件的行
SELECT * FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

外連接

-- 左外連接：左表所有行 + 右表匹配行
SELECT * FROM CUSTOMERS c
LEFT OUTER JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

-- 右外連接：右表所有行 + 左表匹配行
SELECT * FROM CUSTOMERS c
RIGHT OUTER JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

子查詢實戰模式

-- WHERE 子句中使用
SELECT * FROM CUSTOMERS c 
WHERE 3 <= (SELECT COUNT(*) FROM ORDERS o WHERE c.ID = o.CUSTOMER_ID);

-- IN 子查詢
SELECT * FROM ORDERS o 
WHERE o.CUSTOMER_ID IN (SELECT ID FROM CUSTOMERS WHERE NAME LIKE 'MIKE');

-- NOT EXISTS
SELECT * FROM CUSTOMERS c 
WHERE NOT EXISTS (SELECT * FROM ORDERS o WHERE c.ID = o.CUSTOMER_ID);

-- SELECT 列表中使用（標量子查詢）
SELECT NAME, AGE, (SELECT SUM(PRICE) FROM ORDERS WHERE CUSTOMER_ID = 1) AS TOTAL_PRICE
FROM CUSTOMERS WHERE ID = 1;

聯合查詢

-- UNION：去重合併
SELECT * FROM CUSTOMERS WHERE AGE < 25
UNION
SELECT * FROM CUSTOMERS WHERE AGE >= 24;

-- UNION ALL：保留重複（效能較好）
SELECT * FROM CUSTOMERS WHERE AGE < 25
UNION ALL
SELECT * FROM CUSTOMERS WHERE AGE >= 24;

報表查詢與分組統計

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

分頁查詢

-- 從第 100 筆開始，取 1000 筆
SELECT col FROM table1 WHERE ... LIMIT 100, 1000;

-- 等價寫法
SELECT col FROM table1 WHERE ... LIMIT 1000 OFFSET 100;

級聯操作

-- 建立外鍵時指定級聯
ALTER TABLE child_table
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

效能小撇步

• 統計筆數用 COUNT(*) 而非 COUNT(column)，且不加 WHERE 最快
• 避免 SELECT *，僅選取必要欄位
• 適當使用索引覆蓋查詢

附註：文中提及的 Navicat 註冊碼屬舊版本資訊，請勿用於商業用途，建議購買正版授權。