SQL 資料操作語言 (DML) 核心：INSERT、UPDATE、DELETE 完全指南

SQL DML 三大核心語句深度解析

INSERT、UPDATE、DELETE 是資料庫增刪改的基石。理解其語法細節、效能特性、鎖定行為與最佳實踐，能避免資料不一致與效能災難。

INSERT 新增資料

基本語法

INSERT [INTO] table_name [(column_list)]
{ VALUES (value_list) | DEFAULT VALUES | select_statement }

三種新增模式

1. VALUES 子句 — 明確指定值

-- 全欄位插入 (須符合定義順序)
INSERT INTO publisher VALUES ('9900', 'DELPHI', 'Beijing', NULL, 'China')

-- 部分欄位插入 (推薦：明確、防錯)
INSERT INTO publishers (pub_id, pub_name, country, city)
VALUES ('9900', 'DELPHI', 'China', 'Beijing')

-- 使用 DEFAULT 關鍵字
INSERT INTO orders (order_id, customer_id, amount, status)
VALUES (1001, 'C001', 500.00, DEFAULT)

2. DEFAULT VALUES — 全部使用預設值

INSERT INTO log_table DEFAULT VALUES
-- 適用於全欄位有 DEFAULT 或允許 NULL 的表

3. INSERT ... SELECT — 批次複製/轉換資料

-- 方法一：欄位順序對應
INSERT INTO tab1 (col1, col3, col2)
SELECT * FROM tab2

-- 方法二：明確 SELECT 列表 (推薦)
INSERT INTO tab1 (col1, col2, col3)
SELECT col1, col2, col3 FROM tab2

-- 搭配條件過濾
INSERT INTO archive_orders
SELECT * FROM orders WHERE order_date < '2023-01-01'

UPDATE 修改資料

語法結構

UPDATE table_name
SET column1 = expression1 [, column2 = expression2 ...]
[FROM table_source]
[WHERE condition]

關鍵範例

-- 全表更新 (謹慎使用！)
UPDATE discounts SET discount = discount + 0.1

-- 條件更新多欄位
UPDATE discounts
SET discount = discount + 0.5, lowqty = lowqty + 200
WHERE discounttype = 'volume discount'

-- 子查詢更新
UPDATE products
SET price = price * 1.1
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics')

-- JOIN 更新 (SQL Server 語法)
UPDATE p
SET p.price = p.price * 1.1
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics'

DELETE vs TRUNCATE TABLE 刪除資料

語法對照

實戰範例

-- 刪除全表資料 (大表首選 TRUNCATE)
TRUNCATE TABLE discounts

-- 條件刪除
DELETE FROM titles WHERE type = 'business'

-- 子查詢刪除
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled')

-- 刪除並返回被刪資料 (OUTPUT 子句)
DELETE FROM logs
OUTPUT DELETED.* INTO audit_logs
WHERE log_date < DATEADD(year, -1, GETDATE())

效能與鎖定最佳實踐

大批次操作分塊處理

-- 避免長交易鎖表、記錄檔暴增
DECLARE @batch_size INT = 5000, @rows INT = 1
WHILE @rows > 0
BEGIN
    DELETE TOP (@batch_size) FROM large_table
    WHERE created_at < '2023-01-01'
    SET @rows = @@ROWCOUNT
    -- 可選：CHECKPOINT / WAITFOR DELAY '00:00:01'
END

避免常見陷阱

• ❌ UPDATE table SET col = col + 1 無 WHERE — 全表掃描+鎖定
• ❌ DELETE FROM table 大表 — 記錄檔爆炸，改用 TRUNCATE 或分批
• ✅ 更新/刪除前先 SELECT COUNT(*) 確認影響範圍
• ✅ 重要操作包在交易中，驗證後再 COMMIT
• ✅ 使用 TOP (n) 限制單次影響行數

OUTPUT 子句 — 審計與同步神器

INSERT INTO orders (customer_id, amount)
OUTPUT INSERTED.order_id, INSERTED.created_at INTO @new_orders
SELECT customer_id, total FROM cart WHERE session_id = 'abc'

UPDATE products
SET stock = stock - 1
OUTPUT DELETED.product_id, DELETED.stock AS old_stock, INSERTED.stock AS new_stock
WHERE product_id = 100