SQL 四大語言類別速覽
| 類別 | 全稱 | 核心動作 | 典型語句 | 佔比參考 |
|---|
| DDL | Data Definition Language | 定義/管理資料庫物件 | CREATE, ALTER, DROP, TRUNCATE, RENAME | ~10% |
| DML | Data Manipulation Language | 操作表中資料記錄 | INSERT, UPDATE, DELETE, REPLACE, MERGE | ~25% |
| DQL | Data Query Language | 查詢資料並返回結果集 | SELECT, WITH (CTE), SHOW, EXPLAIN | ~60% |
| DCL | Data Control Language | 權限控制與事務管理 | GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT | ~5% |
DDL：物件生命週期管理
資料庫層級
CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE app_db CHARACTER SET utf8mb4; -- 僅影響後續建表
DROP DATABASE app_db;
資料表層級
-- 建表最佳實踐：顯式引擎、字符集、註釋、主鍵、索引
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
`order_no` CHAR(20) NOT NULL COMMENT '訂單號',
`amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '金額',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款 2已發貨 3完成 4取消',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_status` (`user_id`, `status`),
KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單主表';
-- 修改表結構 (Online DDL 5.6+ 大多操作不鎖表)
ALTER TABLE orders ADD COLUMN `pay_time` DATETIME NULL COMMENT '支付時間' AFTER `status`;
ALTER TABLE orders MODIFY `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00;
ALTER TABLE orders DROP INDEX `idx_created`;
索引與約束
-- 索引類型
CREATE INDEX idx_name ON users(name); -- 普通
CREATE UNIQUE INDEX uk_email ON users(email); -- 唯一
CREATE FULLTEXT INDEX ft_content ON articles(content); -- 全文 (MyISAM/InnoDB 5.6+)
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
DML：資料增刪改核心模式
INSERT 多種寫法
-- 單行
INSERT INTO users(name, email) VALUES('Alice', 'alice@example.com');
-- 多行批次 (高效，單次網絡往返)
INSERT INTO users(name, email) VALUES
('Bob', 'bob@example.com'),
('Cathy', 'cathy@example.com');
-- 插入查詢結果 (ETL 常用)
INSERT INTO dw.daily_summary(dt, total_orders, total_amount)
SELECT DATE(created_at), COUNT(*), SUM(amount) FROM orders
WHERE created_at >= '2024-01-01' GROUP BY DATE(created_at);
-- 衝突處理 (MySQL 8.0.19+ 支援 ON DUPLICATE KEY UPDATE 別名)
INSERT INTO counters(id, cnt) VALUES('page_view', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
UPDATE 安全模式與優化
-- 必須帶 WHERE，防止全表誤更新 (sql_safe_updates=ON 時強制)
UPDATE orders SET status = 1, pay_time = NOW()
WHERE order_no = 'ORD202401150001' AND status = 0; -- 條件含舊值防併發覆蓋
-- 大表分批更新模板
DELIMITER //
CREATE PROCEDURE batch_update_status()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_id BIGINT;
DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 0 AND created_at < '2024-01-01' LIMIT 1000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO v_id;
IF NOT done THEN
UPDATE orders SET status = 4 WHERE id = v_id AND status = 0;
COMMIT;
DO SLEEP(0.01);
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END//
DELIMITER ;
DELETE vs TRUNCATE
| 特性 | DELETE | TRUNCATE |
|---|
| 可帶 WHERE | 是 | 否 |
| 觸發器 | 觸發 | 不觸發 |
| 自增計數器 | 保留 | 歸零 |
| 事務支援 | 支援 (可回滾) | 隱式提交 (不可回滾) |
| 速度 | 逐行刪除，慢 | 釋放頁面，極快 |
| Binlog 記錄 | 逐行 (ROW 模式) | 單條 DDL 語句 |
DQL：查詢進階與優化要點
核心子句執行順序
SELECT select_list -- 5
FROM table_references -- 1
WHERE where_condition -- 2
GROUP BY group_list -- 3
HAVING having_condition -- 4
ORDER BY order_list -- 6
LIMIT offset, row_count -- 7
高效分頁 (避免深分頁 OFFSET 掃描)
-- 鍵值分頁 (適合唯一遞增主鍵/索引)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 子查詢延遲關聯
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE user_id = 123 ORDER BY id LIMIT 100000, 20) t USING (id);
CTE (Common Table Expression) 簡化複雜查詢
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) rn
FROM orders WHERE created_at >= '2024-01-01'
)
SELECT * FROM ranked WHERE rn <= 3; -- 每用戶金額 Top3
DCL：權限體系與事務控制
權限模型 (MySQL 8.0 角色化)
-- 建立角色
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
-- 用戶授予角色
CREATE USER 'api_svc'@'%' IDENTIFIED BY 'pass';
GRANT 'app_read', 'app_write' TO 'api_svc'@'%';
SET DEFAULT ROLE ALL TO 'api_svc'@'%'; -- 自動啟用
-- 撤銷
REVOKE 'app_write' FROM 'api_svc'@'%';
事務控制
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 檢查業務邏輯
IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
-- 保存點 (部分回滾)
SAVEPOINT sp1;
DELETE FROM logs WHERE created_at < '2023-01-01';
ROLLBACK TO sp1; -- 僅回滾 DELETE
實戰建議：SQL 開發規範摘要
- 命名：庫表欄位小寫 + 下劃線 (
snake_case)；主鍵 id、外鍵 xxx_id、時間 xxx_at/on、狀態 status、標誌 is_xxx。
- SELECT 禁用 *：明確列出欄位，減少網絡傳輸、利用覆蓋索引、避免 schema 變更衝擊。
- WHERE 必須命中索引：EXPLAIN 驗證
type=ref/range/eq_ref，避免 ALL/index。
- 大事務拆小：單事務 < 5000 行、< 1 秒，降低鎖競爭、主從延遲、Undo Log 壓力。
- DDL 線上執行：5.6+ 使用
ALGORITHM=INPLACE, LOCK=NONE；大表變更走 pt-online-schema-change/gh-ost。
