SQL 三大分類速查
| 類別 | 全稱 | 核心指令 |
|---|
| DDL | Data Definition Language | CREATE、ALTER、DROP、TRUNCATE、RENAME |
| DML | Data Manipulation Language | SELECT、INSERT、UPDATE、DELETE |
| DCL | Data Control Language | GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT |
核心 DDL 模板
庫/表建立刪除
CREATE DATABASE db_name CHARSET utf8mb4;
DROP DATABASE db_name;
CREATE TABLE t (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL DEFAULT '',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE t;
欄位/索引/主鍵變更
ALTER TABLE t ADD COLUMN email VARCHAR(100) NOT NULL DEFAULT '' AFTER name;
ALTER TABLE t DROP COLUMN email;
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t DROP PRIMARY KEY;
CREATE INDEX idx_name ON t (name);
CREATE UNIQUE INDEX uk_email ON t (email);
DROP INDEX idx_name ON t;
視圖
CREATE VIEW v_active_users AS SELECT id, name FROM t WHERE status=1;
DROP VIEW v_active_users;
核心 DML 模板
增刪改查
INSERT INTO t (name) VALUES ('Alice'), ('Bob');
DELETE FROM t WHERE id = 1;
UPDATE t SET name='Alice2' WHERE id = 2;
SELECT * FROM t WHERE name LIKE 'A%';
多表關聯
-- 內連接
SELECT a.id, a.name, b.order_no
FROM users a
INNER JOIN orders b ON a.id = b.user_id;
-- 左連接 (保留左表所有行)
SELECT a.id, a.name, b.order_no
FROM users a
LEFT JOIN orders b ON a.id = b.user_id;
-- 右/全連接 (MySQL 不支援 FULL JOIN，用 UNION 模擬)
高階查詢運算子
-- UNION (去重) / UNION ALL (保留重複)
SELECT id FROM t1 UNION SELECT id FROM t2;
SELECT id FROM t1 UNION ALL SELECT id FROM t2;
-- EXCEPT (MySQL 用 NOT IN / NOT EXISTS 模擬)
SELECT id FROM t1 WHERE id NOT IN (SELECT id FROM t2);
-- INTERSECT (MySQL 用 INNER JOIN / IN 模擬)
SELECT id FROM t1 WHERE id IN (SELECT id FROM t2);
分頁查詢模板 (百萬級表高效)
-- 深分頁優化：延遲關聯
SELECT t.*
FROM t
INNER JOIN (
SELECT id FROM t WHERE status=1 ORDER BY created_at DESC LIMIT 100000, 20
) tmp USING (id)
ORDER BY t.created_at DESC;
實用片段庫
| 需求 | SQL |
|---|
| 隨機 1 行 | SELECT * FROM t ORDER BY RAND() LIMIT 1; (小表) / 優化版見前文 |
| 刪除重複 (保留 id 最大) | DELETE t1 FROM t t1 JOIN t t2 ON t1.key=t2.key AND t1.id < t2.id; |
| 列出所有表 | SHOW TABLES; / SELECT table_name FROM information_schema.tables WHERE table_schema='db'; |
| 列出表結構 | DESC t; / SHOW CREATE TABLE t; |
| 統計行數/求和/平均/最大/最小 | SELECT COUNT(*), SUM(col), AVG(col), MAX(col), MIN(col) FROM t; |
| Case 多條件聚合 | SELECT type, SUM(CASE WHEN vender='A' THEN pcs END) AS a, SUM(CASE WHEN vender='B' THEN pcs END) AS b FROM t GROUP BY type; |
結語
SQL 是資料庫的通用語言。掌握 DDL 結構變更、DML 資料操作、DCL 權限控制 三大類核心模板，配合 EXPLAIN 驗證執行計畫，即可應對 90% 開發與運維場景。
