萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL 三大核心 DML 敘述句深度解析:INSERT、UPDATE、DELETE

INSERT、UPDATE、DELETE 是資料操作語言 (DML) 的三大支柱。掌握其完整語法、行為細節與效能特徵,是資料庫開發的基本功。

一、INSERT:插入資料

基本語法

INSERT [INTO] {table_name | view_name} [(column_list)] 
{DEFAULT VALUES | VALUES (value_list) | SELECT_statement};

完整範例

-- 指定欄位插入單筆
INSERT INTO MyTable (FirstCol, SecondCol, ThirdCol) 
VALUES ('Value1', 'Value2', 'Value3');

-- 多筆值插入 (SQL 2008+)
INSERT INTO MyTable (Col1, Col2) 
VALUES ('A', 1), ('B', 2), ('C', 3);

-- 從查詢結果插入
INSERT INTO ArchiveTable (Id, Data, ArchiveDate)
SELECT Id, Data, GETDATE() FROM SourceTable WHERE IsActive = 0;

-- 預設值插入
INSERT INTO MyTable DEFAULT VALUES;  -- 所有欄位皆有 DEFAULT 或可為 NULL

缺失欄位處理規則

欄位屬性未指定值時行為
有 DEFAULT 約束使用預設值
允許 NULL、無 DEFAULT插入 NULL
NOT NULL、無 DEFAULT報錯:不能插入 NULL
IDENTITY 識別欄自動產生新值 (忽略外部輸入)

@@IDENTITY vs SCOPE_IDENTITY()

INSERT INTO Parent (Name) VALUES ('Parent1');
-- @@IDENTITY 受觸發器影響,可能返回觸發器內插入的識別值
-- SCOPE_IDENTITY() 僅返回當前範圍 (預存程序/觸發器/批次) 的識別值
SELECT SCOPE_IDENTITY() AS NewParentId;  -- 推薦使用

二、UPDATE:更新資料

基本語法

UPDATE {table_name | view_name}
SET column1 = expression1, column2 = expression2, ...
[WHERE search_condition]
[FROM table_source];  -- SQL Server 擴充:支援 JOIN 更新

進階用法

-- JOIN 更新 (SQL Server 特有)
UPDATE T1
SET T1.ColA = T2.ColB
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Id = T2.RefId
WHERE T2.Status = 'Active';

-- CASE 條件更新
UPDATE Products
SET Price = CASE 
    WHEN Category = 'A' THEN Price * 1.1
    WHEN Category = 'B' THEN Price * 0.9
    ELSE Price
END;

-- 使用 OUTPUT 子句回傳變更前後值 (SQL 2005+)
UPDATE Employees
SET Salary = Salary * 1.05
OUTPUT inserted.EmpId, deleted.Salary AS OldSalary, inserted.Salary AS NewSalary
WHERE DeptId = 10;

三、DELETE:刪除資料

基本語法

DELETE [FROM] {table_name | view_name}
[WHERE search_condition]
[FROM table_source];

TRUNCATE TABLE vs DELETE

特性DELETETRUNCATE TABLE
可帶 WHERE是否 (全表清空)
觸發器啟動 AFTER/INSTEAD OF DELETE不啟動
記錄方式逐列記錄 (可回復)記錄頁面釋放 (極少量記錄)
識別欄重置不重置重置為種子值
權限需求DELETE 權限ALTER TABLE 權限
外來鍵限制檢查參考完整性被參考時失敗
速度較慢極快

效能與最佳實務

  • 大量刪除:分批次 DELETE TOP (10000) ... WHERE ... 避免記錄檔暴增與長時間鎖定
  • 大量插入:BULK INSERT / SqlBulkCopy / INSERT ... SELECT 並配合 TABLOCK、最小記錄模式
  • 更新大量列:考慮建立新表 → 插入轉換後資料 → 交換表名 (Partition Switch)
  • 永遠使用 WHERE 子句,避免全表更新/刪除
  • 適當索引支援 WHERE/JOIN 條件,但過多索引拖慢寫入

DML 操作看似簡單,細節決定成敗。理解行為語義、記錄機制、鎖定特性,才能寫出高效能、可維護的資料存取程式碼。

copyright © 萬盛學電腦網 all rights reserved