一、DDL 基礎操作
| 操作 | SQL 範例 |
| 建庫 | CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 刪庫 | DROP DATABASE db_name; |
| 建表 (完整) | CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB; |
| 依表建表 (結構) | CREATE TABLE t_new LIKE t_old; |
| 依查詢建表 (結構+資料) | CREATE TABLE t_new AS SELECT id, name FROM t_old WHERE status=1; |
| 刪表 | DROP TABLE IF EXISTS t; |
| 加欄位 | ALTER TABLE t ADD COLUMN email VARCHAR(100) AFTER name; |
| 加主鍵 | ALTER TABLE t ADD PRIMARY KEY (id); |
| 刪主鍵 | ALTER TABLE t DROP PRIMARY KEY; |
| 建索引 | CREATE UNIQUE INDEX uk_email ON t(email); |
| 刪索引 | DROP INDEX uk_email ON t; |
| 建視圖 | CREATE VIEW v_active_users AS SELECT id, name FROM t WHERE status=1; |
| 刪視圖 | DROP VIEW IF EXISTS v_active_users; |
二、DML 核心語句
-- 查詢
SELECT * FROM t WHERE name LIKE '%keyword%';
-- 插入
INSERT INTO t (name, email) VALUES ('John', 'john@example.com');
-- 刪除
DELETE FROM t WHERE id = 10;
-- 更新
UPDATE t SET status=2 WHERE id = 10;
-- 聚合
SELECT COUNT(*) AS total, SUM(amount) AS sum_amt, AVG(score) AS avg_score, MAX(created_at) AS latest, MIN(created_at) AS earliest FROM orders;
三、高級集合運算
| 運算 | 關鍵字 | 行為 | 去重 |
| 聯集 | UNION / UNION ALL | 合併結果集 | UNION 去重 / ALL 保留重複 |
| 差集 | EXCEPT / EXCEPT ALL (MySQL 8.0+) | 在第一集不在第二集 | 同 |
| 交集 | INTERSECT / INTERSECT ALL (MySQL 8.0+) | 兩集合皆有 | 同 |
注意:參與運算的 SELECT 欄位數、類型、順序**必須一致**。
四、外連接完全圖解
-- LEFT JOIN:左表全保留
SELECT a.id, a.name, b.order_no
FROM users a
LEFT JOIN orders b ON a.id = b.user_id;
-- RIGHT JOIN:右表全保留
SELECT a.id, a.name, b.order_no
FROM users a
RIGHT JOIN orders b ON a.id = b.user_id;
-- FULL JOIN (MySQL 不支援,用 UNION 模擬)
SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
LEFT JOIN 找「只在左表」
SELECT a.id, a.name
FROM users a
LEFT JOIN orders b ON a.id = b.user_id
WHERE b.user_id IS NULL; -- b.user_id 必須 NOT NULL
五、子查詢與衍生表
-- IN 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_members);
-- 衍生表 (內聯視圖)
SELECT * FROM (SELECT id, name FROM users WHERE status=1) AS active_users WHERE id > 100;
六、實用進階技巧
1. BETWEEN 邊界陷阱
-- 含邊界 [start, end]
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'
-- 推薦半開區間
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'
2. 條件邏輯 IF / CASE
SELECT IF(priv=1, 'admin', 'guest') AS role FROM users;
SELECT CASE WHEN score>=90 THEN 'A' WHEN score>=60 THEN 'B' ELSE 'C' END AS grade FROM exams;
3. 索引提示 (強制/忽略)
SELECT * FROM t USE INDEX (idx_name) WHERE name='John';
SELECT * FROM t IGNORE INDEX (idx_bad) WHERE name='John';
4. 跨資料庫拷貝 (Access/同機)
INSERT INTO db1.t (a,b) SELECT a,b FROM db2.t WHERE cond;
5. 分頁優化 (延遲關聯)
SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE cond ORDER BY id LIMIT 100000, 20);
七、命名規範建議
- 資料庫/表/欄位:
snake_case、小寫、名詞單數 (user_order)
- 主鍵:
id (BIGINT UNSIGNED)
- 外鍵:
user_id, order_id
- 時間:
created_at, updated_at, deleted_at
- 布林:
is_deleted, has_paid (TINYINT(1))
- 索引:
idx_欄位名, uk_欄位名 (唯一)