連線與基礎操作

# 命令列連線
mysql -h host -u user -p

# 進入後基本操作
SHOW DATABASES;
USE database_name;
SHOW TABLES;
DESCRIBE table_name;  -- 或 DESC table_name

核心 DDL：建立與修改資料表

資料類型選擇指南

類別	類型	儲存/範圍	適用場景
數值	TINYINT	1 byte (-128~127 / 0~255)	布林旗標、極小整數
	SMALLINT	2 bytes (-32K~32K)	小範圍計數
	MEDIUMINT	3 bytes (-8M~8M)	中等整數
	INT	4 bytes (-21億~21億)	標準主鍵、計數器
	BIGINT	8 bytes (±9×10¹⁸)	大量資料主鍵、時間戳
定點/浮點	DECIMAL(p,s)	精確小數	金額、價格、比率（必用！）
	FLOAT	4 bytes 近似	科學計算、不需精確
	DOUBLE	8 bytes 近似	高精度科學計算
日期時間	DATE	3 bytes	純日期
	TIME	3 bytes	時間/間隔
	DATETIME	8 bytes	固定時區時間點
	TIMESTAMP	4 bytes	UTC 自動轉換、自動更新
字串	CHAR(n)	固定 n 字元	定長代碼（身分證、國家代碼）
	VARCHAR(n)	可變 0~n 字元 + 1-2 bytes 長度	一般文字、名稱、描述
	TEXT	65KB	長文本、文章內容

建表範例

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,  -- bcrypt 固定 60 字元
    full_name VARCHAR(100),
    birth_date DATE,
    balance DECIMAL(12,2) DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

核心 DML：增刪改查 (CRUD)

INSERT 插入資料

-- 單筆
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('john', 'john@example.com', '$2y$10$...', 'John Doe');

-- 批次插入（效能最佳）
INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@example.com', '$2y$10$...'),
('bob', 'bob@example.com', '$2y$10$...'),
('charlie', 'charlie@example.com', '$2y$10$...');

-- 插入查詢結果
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';

SELECT 查詢（核心語法順序）

SELECT [DISTINCT] 欄位清單
FROM 資料表
[WHERE 條件]
[GROUP BY 分組欄位]
[HAVING 分組條件]
[ORDER BY 排序欄位 [ASC|DESC]]
[LIMIT 偏移量, 筆數];

實用查詢模式

-- 分頁（第 2 頁，每頁 20 筆）
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- 模糊搜尋
SELECT * FROM users WHERE username LIKE 'john%';

-- 多條件
SELECT * FROM users 
WHERE is_active = 1 
  AND (username LIKE '%keyword%' OR email LIKE '%keyword%')
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC;

-- 聚合統計
SELECT DATE(created_at) AS 日期, COUNT(*) AS 註冊數
FROM users
GROUP BY DATE(created_at)
ORDER BY 日期 DESC;

UPDATE 更新資料

-- 單筆更新
UPDATE users SET last_login = NOW() WHERE id = 1001;

-- 條件批次更新
UPDATE users SET is_active = 0 WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 複雜條件更新
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.is_active = 0 AND o.status = 'pending';

DELETE 刪除資料

-- 條件刪除
DELETE FROM users WHERE is_active = 0 AND last_login < '2020-01-01';

-- 清空表（重置自增 ID，速度極快）
TRUNCATE TABLE temp_logs;

常用內建函數速查

分類	函數	用途
字串	CONCAT(), CONCAT_WS()	串聯字串
	SUBSTRING(), LEFT(), RIGHT()	擷取子字串
	REPLACE(), TRIM()	替換、去空白
	LOWER(), UPPER()	大小寫轉換
日期時間	NOW(), CURDATE(), CURTIME()	當前時間
	DATE_ADD(), DATE_SUB()	日期加減
	DATEDIFF(), TIMESTAMPDIFF()	時間差
	DATE_FORMAT(), STR_TO_DATE()	格式化/解析
	LAST_DAY()	月最後一天
聚合	COUNT(), SUM(), AVG(), MIN(), MAX()	基礎統計
	GROUP_CONCAT()	群組串聯
	STDDEV(), VARIANCE()	統計分析
	JSON_ARRAYAGG(), JSON_OBJECTAGG()	JSON 聚合 (8.0+)
流程控制	CASE, IF(), IFNULL(), NULLIF(), COALESCE()	條件邏輯
	GREATEST(), LEAST()	最大/最小值
	COALESCE()	第一個非 NULL
	IFNULL()	NULL 替換
資訊	VERSION(), DATABASE(), USER(), CONNECTION_ID()	系統資訊

進階查詢技巧

多表關聯 (JOIN)

-- 內連接：雙方都有匹配
SELECT u.username, o.order_no, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左連接：保留所有用戶（無訂單也顯示）
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

子查詢與 CTE (MySQL 8.0+)

-- 子查詢
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- CTE 遞迴查詢（組織架構、分類樹）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS level, name AS path
    FROM categories WHERE parent_id = 0
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

索引與效能基礎

• 主鍵：叢集索引，InnoDB 以主鍵順序儲存資料
• 唯一索引：UNIQUE INDEX 或 UNIQUE KEY
• 普通索引：INDEX idx_name (col1, col2)（最左前綴原則）
• 全文索引：FULLTEXT INDEX（配合 ngram 中文）
• 覆蓋索引：查詢欄位全包含於索引中，避免回表
• EXPLAIN 分析：EXPLAIN SELECT ... 檢查 type, key, rows, Extra

交易控制

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 檢查業務邏輯
COMMIT;  -- 或 ROLLBACK;

-- 自動提交控制
SET autocommit = 0;
-- ... 多條 SQL ...
COMMIT;
SET autocommit = 1;

PHP PDO 最佳實務範例

<?php
$pdo = new PDO("mysql:host=localhost;dbname=app;charset=utf8mb4", "user", "pass", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,  // 真實預處理
]);

// 參數化查詢
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? AND is_active = 1");
$stmt->execute([$email]);
$user = $stmt->fetch();

// 批次插入
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO logs (level, message) VALUES (?, ?)");
foreach ($logs as $log) {
    $stmt->execute([$log['level'], $log['message']]);
}
$pdo->commit();
?>

這份速查表涵蓋 MySQL 開發 90% 的日常操作。建議收藏、實作練習，並深入掌握索引設計、執行計畫分析、交易控制三大進階主題！