資料庫操作是 PHP 後端開發的核心技能。本文整理 10 大類最常用的 MySQL SQL 語句，涵蓋資料庫/資料表管理、結構修改、索引優化、資料增刪改查 (CRUD) 與權限控制，附帶 PHP PDO 執行範例與最佳實務提醒。
-- 列出所有資料庫
SHOW DATABASES;
-- 切換預設資料庫
USE my_database;
-- 列出目前資料庫所有資料表
SHOW TABLES;
-- 檢視資料表結構
DESCRIBE users; -- 簡略版
SHOW COLUMNS FROM users; -- 完整版
SHOW CREATE TABLE users; -- 顯示建表 SQL (含引擎、字符集、索引)RENAME TABLE old_name TO new_name;
-- 或
ALTER TABLE old_name RENAME TO new_name;-- 新增欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- 修改欄位定義 (型別、長度、預設值、註解)
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) COMMENT '手機號碼';
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30) COMMENT '行動電話'; -- 同時改名
-- 刪除欄位
ALTER TABLE users DROP COLUMN mobile;-- 一般索引 (加速查詢)
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD INDEX idx_name_email (name, email); -- 組合索引
-- 唯一索引 (防重複)
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
-- 主鍵 (通常建表時指定，補充用)
ALTER TABLE users ADD PRIMARY KEY (id);
-- 全文索引 (MySQL 5.6+ InnoDB)
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
-- 刪除索引
ALTER TABLE users DROP INDEX idx_email;| 語法 | 用途 | 範例 |
|---|---|---|
CHANGE | 修改欄位名稱 + 定義 (須重複舊名) | CHANGE old_name new_name VARCHAR(100) |
MODIFY | 僅修改定義 (型別、長度、預設值、註解) | MODIFY email VARCHAR(255) NOT NULL |
-- 單筆 (指定欄位，防止結構變更出錯)
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', '$2y$10$...');
-- 批次插入 (效能遠優於迴圈單筆)
INSERT INTO logs (level, message, created_at) VALUES
('INFO', 'User login', NOW()),
('WARN', 'Disk 80%', NOW()),
('ERROR', 'DB timeout', NOW());
-- 插入或更新 (ON DUPLICATE KEY UPDATE)
INSERT INTO stats (date, views) VALUES ('2024-01-01', 1)
ON DUPLICATE KEY UPDATE views = views + 1;-- 基本更新 (務必加 WHERE！)
UPDATE users SET last_login = NOW(), login_count = login_count + 1
WHERE id = 123;
-- 多表關聯更新
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'shipped'
WHERE u.email = 'john@example.com' AND o.status = 'paid';-- 條件刪除
DELETE FROM sessions WHERE last_activity < DATE_SUB(NOW(), INTERVAL 1 HOUR);
-- 多表關聯刪除
DELETE u, p FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.id = 999;
-- 清空資料表 (重置自增 ID、釋放空間、速度極快)
TRUNCATE TABLE logs;DROP TABLE IF EXISTS temp_table;
DROP DATABASE IF EXISTS test_db;
-- PHP 函式對應：mysqli_drop_db() / PDO 無直接對應，需執行 SQL-- 建立使用者
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
-- 授權 (遵循最小權限原則)
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';
-- 刷新權限
FLUSH PRIVILEGES;
-- 移除權限
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';
-- 刪除使用者
DROP USER 'app_user'@'%';<?php
$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8mb4", 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);
// 1. 預處理語句 (防 SQL 注入)
$stmt = $pdo->prepare("INSERT INTO users (email, password_hash) VALUES (?, ?)");
$stmt->execute(['user@example.com', password_hash('secret', PASSWORD_DEFAULT)]);
$id = $pdo->lastInsertId();
// 2. 具名參數 (可讀性佳)
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND active = 1");
$stmt->execute([':email' => $email]);
$user = $stmt->fetch();
// 3. 交易控制 (ACID)
try {
$pdo->beginTransaction();
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
// 4. 批次插入優化
$stmt = $pdo->prepare("INSERT INTO logs (msg) VALUES (?)");
$pdo->beginTransaction();
foreach ($messages as $msg) {
$stmt->execute([$msg]);
}
$pdo->commit();EXPLAIN，檢查 type (目標: ref/range/eq_ref)、key (使用索引)、rows (掃描行數)。idx_col、組合索引遵循「最左前綴原則」idx_a_b_c 可支援 WHERE a=1 / WHERE a=1 AND b=2 / WHERE a=1 AND b=2 AND c=3。LIMIT 100000, 20 極慢。改用游標分頁 WHERE id > 100000 ORDER BY id LIMIT 20。utf8mb4 (支援 Emoji)，避免編碼轉換開銷與截斷風險。熟練掌握這 10 類 SQL 語句，搭配 PDO 預處理、交易控制與索引優化，即能應對 90% 以上的 PHP 專案資料庫操作需求。持續精進 SQL 調校能力，配合 EXPLAIN、慢查詢日誌、Performance Schema 監控，是資深後端工程師的必修課。
