MySQL 常用 SQL 語句速查手冊：帳號管理、備份還原、DDL/DML/DCL 模板、慢查詢定位

帳號與權限

-- 建帳號 + 授權 (5.7 語法)
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'strong_pass';
GRANT SELECT,INSERT,UPDATE,DELETE ON prod.* TO 'app'@'10.0.1.%';
FLUSH PRIVILEGES;

-- 8.0+ 推薦語法 (caching_sha2_password)
CREATE USER 'app'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'strong_pass';
GRANT SELECT,INSERT,UPDATE,DELETE ON prod.* TO 'app'@'10.0.1.%';

-- 修改密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_pass';
-- 或
SET PASSWORD FOR 'root'@'localhost' = 'new_pass';

備份與還原

-- 邏輯備份
mysqldump --single-transaction --routines --triggers -u root -p prod > prod_$(date +%F).sql
-- 壓縮
mysqldump -u root -p prod | gzip > prod_$(date +%F).sql.gz
-- 還原
mysql -u root -p prod < prod_2024-01-15.sql
-- 跨機直傳
mysqldump -u root -p prod | mysql -h target -u root -p -C prod

DDL 結構操作

CREATE DATABASE db CHARSET utf8mb4;
DROP DATABASE db;
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB;
DROP TABLE t;
ALTER TABLE t ADD COLUMN email VARCHAR(100);
ALTER TABLE t DROP COLUMN email;
ALTER TABLE t ADD INDEX idx_name (name);
CREATE VIEW v AS SELECT id, name FROM t WHERE status=1;

DML 資料操作

INSERT INTO t (name) VALUES ('A'), ('B');
UPDATE t SET name='C' WHERE id=1;
DELETE FROM t WHERE id=2;
SELECT * FROM t WHERE name LIKE 'A%';

慢查詢定位與分析

-- 開啟慢查詢日誌 (my.cnf)
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 秒
log_queries_not_using_indexes = 1

-- 分析工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log  # Percona Toolkit

實用片段

結語

將上述模板收藏為 snippets.json 導入 IDE，或印成卡片貼在工位。熟練核心 20 條 SQL，覆蓋 90% 日常操作，剩下 10% 查手冊、問社區、看源碼。