Learning Objectives
- 用 INSERT 的幾種寫法新增資料：全部欄位、指定欄位、多列、INSERT … SET、INSERT … SELECT
- 說明 AUTO_INCREMENT 的編號規則，用 LAST_INSERT_ID() 取得剛產生的編號
- 看懂嚴格模式下常見的資料錯誤代碼，知道錯在哪個欄位
- 用 UPDATE、DELETE 修改與刪除資料，說明沒有 WHERE 的後果與預防方法
- 分辨 REPLACE、ON DUPLICATE KEY UPDATE、INSERT IGNORE、TRUNCATE 的用途，看懂外鍵擋下刪除的錯誤
把資料放進表格
上一課建好了 employee 資料庫的五張表格，但它們都是空的。這一課學處理資料的三個敘述：
| 敘述 | 作用 |
|---|---|
INSERT |
新增資料列 |
UPDATE |
修改現有資料列的欄位值 |
DELETE |
刪除資料列 |
它們屬於 DML（Data Manipulation Language）。和上一課的 DDL 不同，DML 改的是表格裡的資料，表格的結構不變。
寫值的規則：
- 字串與日期用單引號括起來：
'Research and Development'、'2026-09-01'。MySQL 預設也接受雙引號，但標準 SQL 與其他資料庫都用單引號，養成習慣比較好 - 數字不加引號：
128、8.5 - NULL 表示「沒有值」，不加引號。
'NULL'是四個字母的字串，''是空字串，三者都不同
INSERT 的寫法
給全部欄位：值的順序必須和表格欄位的順序相同（用 DESCRIBE 查看）：
INSERT INTO department VALUES (42, 'Finance');
指定欄位：只給列出的欄位，順序依你寫的；沒列出的欄位使用預設值（沒有預設值就是 NULL）。 表格以後新增欄位，這種寫法也不必修改，所以程式裡建議用這種：
INSERT INTO department (name) VALUES ('Finance');
一次新增多列：括號之間用逗號隔開，比逐筆執行快很多：
INSERT INTO department VALUES (42, 'Finance'), (128, 'Research and Development');
INSERT … SET：MySQL 特有的寫法，欄位與值寫在一起，欄位多時比較不容易對錯位置，但一次只能新增一列：
INSERT INTO department SET name = 'Asset Management';
INSERT … SELECT：把查詢的結果新增到另一張表格，常用來複製或彙整資料：
INSERT INTO 目標表格 (欄位, ...) SELECT ... FROM 來源表格 WHERE ...;
AUTO_INCREMENT 欄位：給 NULL（或乾脆不列出這個欄位），MySQL 就自動編號；給一個數字，就用那個數字。
輸入課程的資料
執行前，資料庫裡有上一課建立的五張空表格。這是原課程輸入資料的敘述：department 的前兩個部門指定編號 42 與 128，後兩個給 NULL 讓 MySQL 自動編號。請注意 Asset Management 拿到的編號。
AUTO_INCREMENT 的編號規則
舊教材說「AUTO_INCREMENT 是目前最大值加 1」，大致正確，但不是全部。MySQL 為每張表格記錄一個計數器（下一個要用的號碼）：
- 自動編號時，使用計數器的值，計數器再往上加
- 指定一個比計數器大的數字（例如 128），計數器會跳到它的下一個號碼（129）
- 剛才的範例中，第一個 INSERT 同時有指定值與 NULL。InnoDB 遇到這種多列 INSERT，會先依列數一次保留一段號碼， 4 列就保留 129～132，實際只用了 129 與 130，沒用到的 131、132 不會還回去，所以下一個是 133
- 新增失敗（例如主鍵重複）或交易回復時，已經分配的號碼同樣不會還回去
所以 AUTO_INCREMENT 只保證不重複而且越來越大，不保證連續。不要用編號來計算筆數，也不要在程式裡假設「下一個號碼是目前最大值加 1」。
取得剛產生的編號：新增一筆主表資料後，常常要用它的編號新增子表資料。用 LAST_INSERT_ID()，不要用 SELECT MAX(...)：
INSERT INTO client (name) VALUES ('Acme Ltd');
INSERT INTO assignment VALUES (LAST_INSERT_ID(), 9842, '2026-09-01', 4);
LAST_INSERT_ID() 是每個連線各自記錄的：別人同時新增資料，不會影響你拿到的值；MAX() 卻可能拿到別人剛新增的那一筆。
它只記錄自動產生的編號，指定編號的 INSERT 不會改變它。
指定欄位、LAST_INSERT_ID 與 INSERT … SELECT
這個範例使用已經有資料的 employee 資料庫：先新增一位客戶，用它的編號新增一筆派工；再建立一張統計表格，用 INSERT … SELECT 把每位員工的技能數一次寫進去。
嚴格模式：資料不合規格就拒絕
MySQL 8 預設開啟嚴格模式（sql_mode 包含 STRICT_TRANS_TABLES）：值不符合欄位的規格時，整個敘述失敗，資料不會存進去。
常見的錯誤：
| 錯誤 | 原因 | 例子 |
|---|---|---|
1048 Column '…' cannot be null |
NOT NULL 的欄位給了 NULL | departmentID 給 NULL |
1364 Field '…' doesn't have a default value |
NOT NULL 又沒有預設值的欄位，INSERT 時沒有列出 | 沒給 departmentID |
1406 Data too long for column '…' |
字串超過長度 | skill 是 VARCHAR(15)，給了 23 個字元 |
1264 Out of range value for column '…' |
數字超過範圍 | INT 欄位給了 30 億 |
1366 Incorrect integer value |
型別不對 | 整數欄位給了 'E1001' |
1292 Incorrect date value |
不存在的日期 | '2026-02-30' |
錯誤訊息會指出是哪個欄位、第幾列（at row 1），先讀訊息再修改敘述。
舊教材與舊版 MySQL 的不同：原課程使用的 XAMPP（MySQL 4、5 時代）預設不是嚴格模式，同樣的資料會被「調整」後存進去，只留下警告。
例如在 MySQL 8.0.44 用 SET SESSION sql_mode = '' 關掉嚴格模式後實際執行（assignment 原本就有 2003-01-20 那一筆）：
mysql> INSERT INTO assignment VALUES (1, 7513, '2003-02-30', 8.5);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT * FROM assignment;
+----------+------------+------------+-------+
| clientID | employeeID | workdate | hours |
+----------+------------+------------+-------+
| 1 | 7513 | 0000-00-00 | 8.5 |
| 1 | 7513 | 2003-01-20 | 8.5 |
+----------+------------+------------+-------+
不存在的日期變成 0000-00-00，太長的字串被截斷，錯誤悄悄進了資料庫。嚴格模式讓問題在新增的當下就被發現，請保持開啟。
六種常見的資料錯誤
每個 INSERT 都有一個值不合規格，觀察錯誤訊息怎麼指出問題。最後的 SELECT 確認：失敗的敘述沒有留下任何資料。
想一想：AUTO_INCREMENT 與資料錯誤
employee 資料庫的 department 是用課程的敘述輸入的（42、128 指定，另外兩個 NULL 得到 129、130）。接著執行：
INSERT INTO department (name) VALUES ('Asset Management');
SELECT LAST_INSERT_ID();
結果是什麼？
client.contactNumber 是 CHAR(12)。執行下面的敘述會出現哪個訊息？
INSERT INTO client (name, contactNumber) VALUES ('Acme Ltd', '+886-3-4227151');
employee.departmentID 宣告為 INT NOT NULL，沒有預設值。下面的敘述會怎樣？
INSERT INTO employee (name, job) VALUES ('Mia Lin', 'DBA');
UPDATE 與 DELETE
UPDATE 表格 SET 欄位1 = 值1, 欄位2 = 值2, ... WHERE 條件;
DELETE FROM 表格 WHERE 條件;
WHERE決定改哪些列，寫法和第 06 課的 SELECT 相同；符合條件的每一列都會被修改或刪除SET可以同時改好幾個欄位，值可以用欄位本身計算，例如hours = hours * 2- UPDATE 的訊息有兩個數字：
Rows matched是符合 WHERE 的列數，Changed是值真的改變的列數。把 DBA 再改成 DBA，matched 是 1、changed 是 0 - 原課程寫
WHERE employeeID = '6651'，數字加了引號。MySQL 會自動轉換，結果相同，但數字欄位直接寫數字比較正確 - MySQL 的 UPDATE 與 DELETE 還可以加
ORDER BY與LIMIT，例如DELETE FROM assignment ORDER BY workdate LIMIT 1刪除日期最早的一筆 - 跨多張表格的 UPDATE 與 DELETE（原課程實習的
DELETE employee, employeeSkills FROM ...）要先學會連接表格，留到第 07 課
沒有 WHERE，就是整張表格。 UPDATE employee SET job = 'DBA'; 會把所有員工都改成 DBA，DELETE FROM employeeSkills; 會刪掉所有技能，
而且沒有「上一步」可以按。預防的方法：
- 先用同樣的 WHERE 寫一個
SELECT，確認找到的列正是你要改的，再把 SELECT 換成 UPDATE 或 DELETE - 開啟安全更新模式：
mysql --safe-updates登入，或執行SET sql_safe_updates = 1;。 之後 UPDATE、DELETE 的 WHERE 沒有用到索引欄位（例如主鍵）時，MySQL 會拒絕執行（ERROR 1175） - 重要的修改放在交易裡，確認無誤再
COMMIT（第 10 課）
修改與刪除資料
UPDATE 和 DELETE 每個都分成兩步：先標出 WHERE 找到的列，再顯示修改（黃色）或刪除（紅色）的結果。
外鍵與 TRUNCATE
外鍵會擋下刪除。原課程的實習有一行 DELETE FROM department;，在 InnoDB 加上外鍵之後會失敗：
還有員工屬於這些部門，刪掉部門會讓員工指向不存在的部門。MySQL 回報 ERROR 1451 Cannot delete or update a parent row，
並指出是哪個外鍵（例如 employee_ibfk_1）擋下的，整個 DELETE 都不執行，連沒有員工的部門也不會被刪。
所以刪除的順序和建立相反：先刪參照別人的子表格資料（assignment、employeeSkills），再刪 employee，最後才是 department 與 client。
反過來，新增或修改子表格時給了不存在的父表格值，則是 ERROR 1452 Cannot add or update a child row。
（外鍵也可以設定成刪除父表格資料時自動刪除子表格資料，也就是 ON DELETE CASCADE，方便但危險，使用前要想清楚。）
TRUNCATE TABLE 清空整張表格：
DELETE FROM 表格; |
TRUNCATE TABLE 表格; |
|
|---|---|---|
| 做法 | 逐列刪除 | 直接重建一張空表格，資料量大時快很多 |
| AUTO_INCREMENT | 計數器不變 | 重設，從 1 開始 |
| 交易 | 可以回復 | 屬於 DDL，不能回復 |
| 被其他表格的外鍵參照時 | 子表格沒有對應資料就可以刪 | 一律拒絕（ERROR 1701），即使子表格是空的 |
| 回報的筆數 | 刪除的列數 | 固定是 0 rows affected |
被擋下的刪除
這個範例故意執行幾個會被拒絕的敘述：安全更新模式擋下沒有 WHERE 的 DELETE，外鍵擋下刪除部門，TRUNCATE 遇到被參照的表格也會失敗。
主鍵重複時怎麼辦：REPLACE、ON DUPLICATE KEY UPDATE、INSERT IGNORE
新增的資料和現有資料的主鍵（或 UNIQUE 欄位）重複時，一般的 INSERT 會出現 ERROR 1062 Duplicate entry。MySQL 有三種寫法可以處理：
| 寫法 | 主鍵重複時 | 回報的筆數 |
|---|---|---|
INSERT … ON DUPLICATE KEY UPDATE 欄位 = … |
不新增，改為修改那一列 | 新增 1、修改 2、值沒變 0 |
REPLACE INTO … |
先刪除舊的那一列，再新增 | 有舊資料時是 2（刪 1 列＋新增 1 列） |
INSERT IGNORE INTO … |
跳過這一列，只留下警告 | 實際新增的列數 |
原課程用 warning 表格示範第一種：每記一次警告，次數加 1，第一次則新增一列。
INSERT INTO warning (employeeID) VALUES (6651)
ON DUPLICATE KEY UPDATE count = count + 1;
要注意的地方：
- REPLACE 是「刪除再新增」，不是修改：沒有給值的欄位會回到預設值；如果有其他表格的外鍵參照這一列，刪除那一步會被擋下或連帶刪除子資料。只想改某些欄位時用 ON DUPLICATE KEY UPDATE
- INSERT IGNORE 會把其他錯誤也變成警告：例如太長的字串會被截斷後存入，等於關掉了這個敘述的嚴格模式。用完記得
SHOW WARNINGS檢查 - 課程原本的
warning表格把REFERENCES寫在欄位後面，MySQL 8.0 會忽略它（上一課的測驗題），下面的範例改寫成真正的外鍵
警告次數表
觀察每個敘述回報的 rows affected，以及右邊 warning 表格哪些列是新增（綠）、哪些是修改（黃）。注意第二個 REPLACE 之後，9842 的 count 從 5 變回 1。
想一想：重複的主鍵與刪除的順序
warning 表格原本是空的。下面的 INSERT 連續執行三次之後，SELECT 顯示什麼？
INSERT INTO warning (employeeID) VALUES (6651)
ON DUPLICATE KEY UPDATE count = count + 1;
（count INT DEFAULT 1）
warning 已經有 (6651, 3) 這一列。執行下面兩個敘述，ROW_COUNT()（上一個敘述影響的列數）是多少？
REPLACE INTO warning VALUES (6651, 10);
SELECT ROW_COUNT();
要清空 employee 資料庫的全部資料（保留表格），用 DELETE 逐一刪除，哪一個順序不會被外鍵擋下？
動手寫：Nora 的新職務
Nora Edwards（員工編號 7513）升任 Senior Programmer，學會了 Python，也不再使用 Perl。請依序：
- 把她的
job改成Senior Programmer - 在
employeeSkills新增她的技能Python - 刪除她的技能
Perl（只刪她的這一項） - 列出她的所有技能，依
skill排序
重點整理
| 敘述 | 作用 |
|---|---|
INSERT INTO 表格 (欄位, ...) VALUES (...), (...) |
新增一列或多列；建議列出欄位 |
INSERT INTO 表格 SET 欄位 = 值, ... |
新增一列，欄位與值寫在一起 |
INSERT INTO 表格 SELECT ... |
把查詢結果新增到表格 |
UPDATE 表格 SET ... WHERE ... |
修改符合條件的列 |
DELETE FROM 表格 WHERE ... |
刪除符合條件的列 |
INSERT … ON DUPLICATE KEY UPDATE |
主鍵重複時改為修改 |
REPLACE INTO |
主鍵重複時刪除舊列再新增 |
INSERT IGNORE |
主鍵重複時跳過，留下警告 |
TRUNCATE TABLE |
清空表格、重設編號，不能回復 |
- AUTO_INCREMENT 不重複、越來越大，但不保證連續；取得剛產生的編號用
LAST_INSERT_ID() - 嚴格模式讓不合規格的資料直接失敗：1048、1364、1406、1264、1366、1292
- UPDATE、DELETE 沒有 WHERE 就是整張表格；先用 SELECT 確認，或開啟
sql_safe_updates - 外鍵擋下刪除時是 ERROR 1451，刪除順序和建立相反
下一課開始查詢資料：SELECT、WHERE、ORDER BY 與分組統計。