一、基礎 DDL/DML 速查

-- 建庫/刪庫
CREATE DATABASE db_name;
DROP DATABASE db_name;

-- 建表/刪表
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20));
DROP TABLE t;

-- 複製表結構
CREATE TABLE new_t LIKE old_t;
CREATE TABLE new_t AS SELECT * FROM old_t WHERE 1=0;

-- 增刪改查
INSERT INTO t (id, name) VALUES (1, 'a');
DELETE FROM t WHERE id = 1;
UPDATE t SET name='b' WHERE id = 1;
SELECT * FROM t WHERE name LIKE '%a%';

二、進階查詢技巧

集合運算

• UNION：合併去重；UNION ALL：合併保留重複（效能更好）。
• EXCEPT（MINUS）：差集；INTERSECT：交集。

外部連接

-- LEFT JOIN：左表全保留
SELECT a.*, b.col FROM a LEFT JOIN b ON a.id = b.a_id;
-- RIGHT JOIN：右表全保留
-- FULL JOIN：MySQL 不直接支援，用 UNION 模擬

分頁查詢（高效能）

-- 深分頁優化：延遲關聯
SELECT b.* FROM (
    SELECT id FROM t ORDER BY sort_col LIMIT 10000, 20
) a JOIN t b ON b.id = a.id;

隨機取樣

-- 小表
SELECT * FROM t ORDER BY RAND() LIMIT 10;
-- 大表：MAX(id)*RAND() + JOIN（見文章 178104）

刪除重複保留最小 ID

DELETE FROM t WHERE id NOT IN (
    SELECT MIN(id) FROM t GROUP BY col1, col2
);

三、效能優化實用指令

-- 重建索引
ANALYZE TABLE t;
OPTIMIZE TABLE t;        -- 整理碎片

-- 壓縮資料庫
-- 縮減資料/日誌檔案
DBCC SHRINKDATABASE (dbname);  -- SQL Server 語法，MySQL 用 OPTIMIZE

-- 檢查備份集
RESTORE VERIFYONLY FROM DISK = 'E:\backup.bak';

四、維護與監控

• SHOW PROCESSLIST：查看當前執行緒。
• SHOW ENGINE INNODB STATUS：InnoDB 內部狀態。
• SHOW GLOBAL STATUS LIKE 'Qcache%'：查詢快取命中率。
• pt-query-digest（Percona Toolkit）分析慢查詢日誌。

五、條件構建技巧

-- 動態 WHERE 子句
SET @sql = CONCAT('SELECT * FROM ', @tbl, ' WHERE 1=1 ', @where);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

WHERE 1=1 方便後續直接 AND ... 拼接，無需判斷第一個條件。