MySQL 四大核心操作 SELECT、INSERT、UPDATE、DELETE 是所有業務的基石。本文系統整理標準語法、條件運算、排序分頁、聚合函數,並提供 PHP PDO (取代已棄用 mysql_*) 連線、預處理、事務、資料讀取的現代化最佳實踐,最後封裝可復用的 Database 類。
-- 基礎
SELECT * FROM `test` WHERE 1;
SELECT `id`, `username` FROM `test` WHERE `id` = 2;
-- 條件運算
SELECT * FROM `test` WHERE `id` IN (1,2,4);
SELECT * FROM `test` WHERE `username` LIKE '%王%';
SELECT * FROM `test` WHERE `id` BETWEEN 1 AND 3;
SELECT * FROM `test` WHERE `id` = 2 AND `remark` = '學生';
-- 排序、分組、分頁 (優先級:GROUP BY → ORDER BY → LIMIT)
SELECT * FROM `test` GROUP BY `remark`;
SELECT * FROM `test` ORDER BY `regdate` ASC, `id` ASC;
SELECT * FROM `test` LIMIT 0, 10; -- 第 1 頁,每頁 10 筆
-- 聚合函數
SELECT COUNT(*) FROM `test`;
SELECT MAX(`id`), MIN(`regdate`), AVG(`score`), SUM(`amount`) FROM `test`;
INSERT INTO `test` (`id`, `username`, `regdate`, `remark`)
VALUES (NULL, '小方', NOW(), '學生'); -- 自增主鍵傳 NULL
UPDATE `test` SET `username` = '小方' WHERE `id` = 4 LIMIT 1;
DELETE FROM `test` WHERE `id` = 3 LIMIT 1; -- 生產環境務必加 LIMIT
$dsn = 'mysql:host=localhost;dbname=newdb;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
];
try {
$pdo = new PDO($dsn, 'root', 'root', $options);
} catch (PDOException $e) {
die('連線失敗: ' . $e->getMessage());
}
$sql = "INSERT INTO `test` (`username`, `regdate`, `remark`) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['小方', date('Y-m-d H:i:s'), '學生']);
$lastId = $pdo->lastInsertId();
$stmt = $pdo->query("SELECT * FROM `test`");
while ($row = $stmt->fetch()) {
echo $row['id'] . ' ' . $row['username'] . '
';
}
// 或一次性取得所有
$rows = $stmt->fetchAll();
print_r($rows);
try {
$pdo->beginTransaction();
$pdo->exec("INSERT INTO `test` (`username`) VALUES ('A')");
$pdo->exec("INSERT INTO `test` (`username`) VALUES ('B')");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
| 方法 | 用途 | 回傳值 |
|---|---|---|
query() | 執行 SELECT、無參數 SQL | PDOStatement |
exec() | 執行 INSERT/UPDATE/DELETE、DDL | 影響行數 (int) |
prepare() | 預處理語句 | PDOStatement |
lastInsertId() | 取得最後自增 ID | string/int |
beginTransaction() | 開啟事務 | bool |
commit() / rollBack() | 提交/回滾 | bool |
errorCode() / errorInfo() | 錯誤代碼/詳情 | string/array |