-- 顯示所有數據庫
SHOW DATABASES;
-- 切換數據庫
USE db_name;
-- 顯示當前庫所有表
SHOW TABLES;
-- 顯示表結構
DESC table_name;
-- 顯示建表語句
SHOW CREATE TABLE table_name;
-- 重命名表
ALTER TABLE old_name RENAME new_name;
-- 添加列
ALTER TABLE table_name ADD COLUMN col_name col_type [constraints];
-- 刪除列
ALTER TABLE table_name DROP COLUMN col_name;
-- 修改列類型/屬性 (CHANGE 需指定舊列名,MODIFY 僅修改定義)
ALTER TABLE table_name CHANGE old_col new_col VARCHAR(200) NOT NULL;
ALTER TABLE table_name MODIFY col_name VARCHAR(200) NOT NULL;
-- 添加索引
ALTER TABLE table_name ADD INDEX idx_name (col1, col2);
ALTER TABLE table_name ADD UNIQUE INDEX uniq_idx (col_name);
ALTER TABLE table_name ADD PRIMARY KEY (id);
-- 刪除索引
ALTER TABLE table_name DROP INDEX idx_name;
-- 插入 (指定列,防止表結構變更導致錯誤)
INSERT INTO table_name (col1, col2) VALUES ('val1', 123);
-- 批量插入
INSERT INTO table_name (col1, col2) VALUES ('a',1), ('b',2), ('c',3);
-- 更新 (務必帶 WHERE)
UPDATE table_name SET col1 = 'new_val' WHERE col2 = 3;
-- 刪除 (務必帶 WHERE)
DELETE FROM table_name WHERE condition;
-- 清空表 (重置自增 ID,速度快)
TRUNCATE TABLE table_name;
-- 分頁 (MySQL)
SELECT * FROM table_name ORDER BY id DESC LIMIT 10 OFFSET 20; -- 第3頁,每頁10條
-- 連接查詢
SELECT u.name, o.amount FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
-- 聚合統計
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY status
HAVING cnt > 10
ORDER BY total DESC;
DROP TABLE table_name;
DROP DATABASE db_name;
-- PHP 中可用 mysql_drop_db() (已棄用),建議用 DROP DATABASE SQL
<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;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 products WHERE category = ? AND price > ?");
$stmt->execute(['electronics', 100]);
$products = $stmt->fetchAll();
// 事務
$pdo->beginTransaction();
try {
$pdo->exec("INSERT INTO logs (msg) VALUES ('start')");
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
?>
熟練掌握上述 SQL 模式,配合 PDO 預處理語句,能應對 90% 以上的 PHP+MySQL 開發場景。複雜報表、大數據分析請學習窗口函數、CTE、物化視圖等進階特性 (MySQL 8.0+ 支持)。