MySQL 常用 SQL 語句速查手冊 (DDL/DML/DQL/DCL)

庫/表結構 (DDL)

-- 庫
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET utf8mb4;

-- 表
CREATE TABLE tbl (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名稱',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='業務表';

DROP TABLE tbl;
TRUNCATE TABLE tbl;  -- 速刪全表、重置自增、釋放空間 (獨立表空間)

ALTER TABLE tbl ADD COLUMN email VARCHAR(100) NOT NULL DEFAULT '' AFTER name;
ALTER TABLE tbl MODIFY COLUMN name VARCHAR(150) NOT NULL DEFAULT '';
ALTER TABLE tbl DROP COLUMN email;
ALTER TABLE tbl ADD INDEX idx_name (name);
ALTER TABLE tbl DROP INDEX idx_name;
ALTER TABLE tbl RENAME TO new_tbl;

數據操作 (DML)

-- 插入
INSERT INTO tbl (name, status) VALUES ('Alice', 1);
INSERT INTO tbl (name, status) VALUES ('Bob', 1), ('Carol', 2), ('Dave', 1);  -- 批量
INSERT INTO tbl (name) SELECT name FROM other_tbl WHERE status=1;  -- 從查詢插入
INSERT IGNORE INTO tbl (id, name) VALUES (1, 'Alice');  -- 忽略重複鍵
INSERT INTO tbl (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name='Alice_new';  -- 覆蓋更新

-- 更新
UPDATE tbl SET status=2, updated_at=NOW() WHERE id=1;
UPDATE tbl t1 JOIN tbl2 t2 ON t1.id=t2.tbl_id SET t1.name=t2.name;  -- 多表更新

-- 刪除
DELETE FROM tbl WHERE status=0;
DELETE t1 FROM t1 JOIN t2 ON t1.id=t2.tbl_id WHERE t2.type=3;  -- 多表刪除
TRUNCATE TABLE tbl;  -- 不走事務、不觸發 Trigger、重置自增、極快

查詢進階 (DQL)

-- 分頁 (Keyset 推薦)
SELECT * FROM tbl WHERE status=1 ORDER BY id LIMIT 20;
SELECT * FROM tbl WHERE status=1 AND id > 1000 ORDER BY id LIMIT 20;  -- 游標

-- 聚合
SELECT status, COUNT(*) AS cnt, MAX(created_at) AS latest FROM tbl GROUP BY status;
SELECT status, COUNT(*) FROM tbl GROUP BY status HAVING COUNT(*) > 100;

-- 連接
SELECT t1.name, t2.order_no FROM users t1 LEFT JOIN orders t2 ON t1.id=t2.user_id;
SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.t1_id;

-- 子查詢 / CTE (8.0+)
SELECT * FROM tbl WHERE id IN (SELECT tbl_id FROM tbl2 WHERE type=1);
WITH recent AS (SELECT * FROM tbl WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY))
SELECT * FROM recent WHERE status=1;

-- 窗口函數 (8.0+)
SELECT id, name, ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at DESC) AS rn FROM tbl;

-- 條件構造
SELECT * FROM tbl WHERE name LIKE 'A%' AND status IN (1,2) AND created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT *, CASE status WHEN 1 THEN '正常' WHEN 2 THEN '禁用' ELSE '未知' END AS status_desc FROM tbl;

權限控制 (DCL)

CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'Str0ngP@ss!';
GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO 'app'@'10.0.%';
GRANT EXECUTE ON PROCEDURE biz_db.proc_name TO 'app'@'10.0.%';
REVOKE INSERT ON biz_db.* FROM 'app'@'10.0.%';
DROP USER 'app'@'10.0.%';
SHOW GRANTS FOR 'app'@'10.0.%';

管理/維護

SHOW PROCESSLIST;  -- 當前連接
KILL 12345;        -- 斬殺會話
SHOW ENGINE INNODB STATUS\G  -- InnoDB 內部狀態
ANALYZE TABLE tbl; -- 更新統計信息
OPTIMIZE TABLE tbl; -- 整理碎片 (獨立表空間釋放空間)
CHECK TABLE tbl;   -- 檢查損壞
REPAIR TABLE tbl;  -- 修復 (MyISAM)
FLUSH PRIVILEGES;  -- 刷新權限 (8.0 多為無操作)
SET GLOBAL innodb_buffer_pool_size=53687091200; -- 動態調整 (部分參數)

字符串/數學/時間 常用函數