MySQL 作為最流行的開源關聯式資料庫，掌握核心操作命令是開發與運維的基本功。本文系統整理資料庫/資料表管理、CRUD 操作、條件查詢、聚合函數、鍵約束、多表關聯等高頻指令，並附帶實戰範例，可作為日常開發的速查手冊。

一、資料庫級操作

連線與基礎管理

# 連線本地資料庫 (root/root 為帳密)
mysql -uroot -proot

# 連線遠端並指定資料庫、關閉自動重雜湊 (加快啟動)
mysql -h 192.168.1.100 -P 3306 -u user -p password db_name -A

資料庫生命週期

操作	SQL 指令	備註
查看所有庫	SHOW DATABASES;
建立資料庫	CREATE DATABASE db_name;
建庫並指定編碼	CREATE DATABASE db_name CHARACTER SET utf8mb4;	推薦 utf8mb4 支援 Emoji
查看編碼設定	SHOW VARIABLES LIKE 'character%';
修改客戶端/結果集編碼	SET character_set_client = utf8mb4; SET character_set_results = utf8mb4;	Session 級別生效
刪除資料庫	DROP DATABASE [IF EXISTS] db_name;	加 IF EXISTS 防報錯
修改資料庫編碼	ALTER DATABASE db_name CHARACTER SET utf8mb4;
切換資料庫	USE db_name;

備份與還原 (命令列模式)

# 備份：退出 mysql 後在 Shell 執行
mysqldump -uroot -proot db_name > /backup/db_name_$(date +%F).sql

# 還原：先建庫再導入
mysql -uroot -proot -e "DROP DATABASE IF EXISTS db_name; CREATE DATABASE db_name;"
mysql -uroot -proot db_name < /backup/db_name_2024-01-15.sql

# 或在 mysql 客戶端內執行
SOURCE /backup/db_name.sql;

二、資料表操作

結構變更 (DDL)

-- 新增欄位
ALTER TABLE tbl_name ADD COLUMN image BLOB;

-- 修改欄位型別/長度
ALTER TABLE tbl_name MODIFY job VARCHAR(50);

-- 重命名資料表
RENAME TABLE old_name TO new_name;

-- 修改表字元集
ALTER TABLE tbl_name CHARACTER SET = utf8mb4;

-- 修改欄位名稱 (需同時指定型別)
ALTER TABLE tbl_name CHANGE COLUMN old_name new_name VARCHAR(20);

資訊查詢

SHOW TABLES;                    -- 列出所有表
SHOW CREATE TABLE tbl_name;     -- 查看建表語句 (含索引、引擎)
DESC tbl_name;                  -- 查看表結構 (欄位、型別、鍵、預設值)

刪除資料表

DROP TABLE [IF EXISTS] tbl_name;

三、資料操作 (DML) 核心語法

建表示例

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    chinese INT,
    math INT,
    english INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新增 (INSERT)

INSERT INTO student (name, chinese, math, english) VALUES ('張三', 85, 90, 88);
INSERT INTO student (name, chinese, math, english) VALUES 
('李四', 78, 82, 80), ('王五', 92, 88, 95);  -- 批次插入

修改 (UPDATE)

-- 單條件更新
UPDATE student SET math = 100 WHERE name = '張三';

-- 運算式更新
UPDATE student SET math = math + 10 WHERE name LIKE '李%';

-- 模糊匹配更新 (單字元 _)
UPDATE student SET english = 90 WHERE name LIKE '王_';

刪除 (DELETE vs TRUNCATE)

DELETE FROM student WHERE name = '張三';  -- 可回滾、觸發 Trigger、不重置 AUTO_INCREMENT
DELETE FROM student;                      -- 刪除所有資料 (保留結構)
TRUNCATE TABLE student;                   -- 重建表、不可回滾、重置 AUTO_INCREMENT、速度極快

選擇原則： 保留自增 ID 需回滾用 DELETE；清空測試資料、重置 ID 用 TRUNCATE。

四、查詢 (SELECT) 進階技巧

基礎查詢與去重

SELECT * FROM student;
SELECT DISTINCT english FROM student;           -- 去重
SELECT name, chinese AS c, english AS e FROM student;  -- 別名

範圍與集合條件

SELECT * FROM student WHERE english BETWEEN 80 AND 90;
SELECT * FROM student WHERE math IN (89, 90, 30);

模糊查詢 (LIKE)

SELECT * FROM student WHERE name LIKE '李%';   -- 姓李的所有學生
SELECT * FROM student WHERE name LIKE '李_';   -- 姓李且名字為單字

排序 (ORDER BY)

SELECT * FROM student ORDER BY math;           -- 預設 ASC 升序
SELECT * FROM student ORDER BY math DESC;      -- 降序
SELECT * FROM student ORDER BY math DESC, english ASC;  -- 多欄位排序

五、聚合函數與分組

函數	範例	說明
COUNT	SELECT COUNT(*) FROM student;	統計行數
MAX/MIN	SELECT MAX(chinese), MIN(chinese) FROM student;	極值
SUM	SELECT SUM(math) FROM student;	求和
AVG	SELECT AVG(chinese) FROM student;	平均值

GROUP BY 分組統計

-- 按產品分組查詢訂單數
SELECT product, COUNT(*) AS order_count FROM orders GROUP BY product;

-- 進階：分組後篩選 (HAVING)
SELECT product, COUNT(*) AS cnt FROM orders GROUP BY product HAVING cnt > 10;

六、鍵約束設計精要

約束	關鍵字	特性	適用場景
非空	NOT NULL	欄位不可為 NULL	業務必填欄位
唯一	UNIQUE	值唯一，允許一個 NULL	身分證、Email、手機號
主鍵	PRIMARY KEY	非空 + 唯一，一表僅一個	業務主鍵 / 代理鍵 (ID)
自增主鍵	PRIMARY KEY AUTO_INCREMENT	自動分配唯一整數	無業務含義的代理鍵 (推薦)

七、多表關聯關係建模

一對一 (1:1)

雙方均可作為主鍵，通常將從表主鍵同時設為外鍵指向主表。

多對一 / 一對多 (N:1 / 1:N)

在「多」的一方建立外鍵 指向「一」的一方主鍵。

-- 訂單表 (多) -> 用戶表 (一)
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

多對多 (M:N)

需建立中間表，包含兩個外鍵欄位，組成聯合主鍵。

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id)
);

八、實戰效能小貼士

• 避免 SELECT *： 明確列出欄位，減少網路傳輸、利用覆蓋索引
• 索引覆蓋： EXPLAIN 出現 Using index 表示無需回表
• LIMIT 分頁優化： 深分頁改用 WHERE id > last_id LIMIT 20 (游標分頁)
• 字元集統一： 資料庫、表、欄位、連線全鏈路使用 utf8mb4

將本文加入書籤，下次寫 SQL 時不再翻閱文檔，專注業務邏輯實現。