SQL Server溫故系列(1)：SQL 資料操作 CRUD 之增删改合

1、插入語句 INSERT INTO
- 1.1、用 INSERT 插入單行資料
- 1.2、用 INSERT 插入多行資料
- 1.3、用 INSERT 插入子查詢結果行
- 1.4、INSERT 小結及特殊字段插入方法
2、删除語句 DELETE
- 2.1、用 DELETE 删除表中指定行
- 2.2、用 TRUNCATE TABLE 高效清空表
3、更新語句 UPDATE
4、合并語句 MERGE
5、用 TOP 子句限制受影響的行
6、用 OUTPUT 子句傳回受影響的資料
7、本文小結

毋庸置疑，開發者最常用的資料庫技術就是 SQL 了，即便是 ORM 大行其道的今天也常常需要寫 SQL 語句。而 SQL 語句中最常用的就是增删改查了，本系列就先對增删改查語句來個系統的回顧吧！

1、插入語句 INSERT INTO

1.1、用 INSERT 插入單行資料

INSERT INTO 的作用是向表中添加新行，文法如下：

INSERT INTO table-name(column1,column2,...column-n) VALUES(value1,value2,...value-n);

譬如要向好學生表中添加 1 條資料，示例如下：

INSERT INTO T_GoodStudents(Name,Birthday) VALUES('李爾','1990-01-09'); -- 顯示指定要插入字段

如果按表中的字段順序給出全部字段的值，那麼就不用顯示指定字段了，示例如下：

INSERT INTO T_GoodStudents VALUES(1,'邱晨',1,'1990-09-01');

1.2、用 INSERT 插入多行資料

INSERT INTO 還可以一次向表中添加多條資料，如要一次性向學生表中添加 3 條資料，示例如下：

INSERT INTO T_GoodStudents(Name,Gender,Birthday) 
VALUES('張三',1,'1993-03-03'),('李四',1,'1994-04-04'),('王五',1,'1995-05-05');

注意：在插入全部字段時，插入多行資料也可以像插入單行資料那樣省略字段清單，但必須確定各行之間的資料個數相同、類型相容。

1.3、用 INSERT 插入子查詢結果行

向表中插入資料時，既可以通過 VALUES 子句顯示地列出插入值，也可以通過 SELECT 子句來獲得插入值。文法如下：

INSERT INTO target-table-name SELECT columns FROM source-table-name;

該語句的效果類似于把一張表的資料複制到另一張表，要複制的字段和行都可以顯示的指定。當要将大量行從源表傳輸到目标表中時，該語句還能夠以最小日志記錄的方式高效的完成。示例如下：

INSERT INTO T_GoodStudents SELECT Id,Name,Gender,Birthday FROM T_Students;   -- 完全複制（資料）
INSERT INTO T_GoodStudents(Name,Gender) SELECT Name,Gender FROM T_Students;  -- 指定部分字段複制
INSERT INTO T_GoodStudents(Name) SELECT Name FROM T_Students WHERE Gender=1; -- 指定部分資料複制

如果目标表和源表的表結構相同，子查詢的字段清單還可以用 * 來代替。在指定字段複制時，無需表結構相同，隻要對應字段的資料類型相容即可，甚至還可以沒有源表，一個子查詢就夠了。示例如下：

INSERT INTO T_GoodStudents SELECT 999,'李敏',0,'1991-02-02'; -- 插入 1 條（來自子查詢的）資料

INSERT INTO T_GoodStudents(Id,Name,Birthday)
SELECT 11,'王陽','1991-03-02' UNION ALL
SELECT 12,'李玉','1991-07-02' UNION ALL
SELECT 13,'鄭爽','1991-02-02';                               -- 插入 3 條（來自子查詢的）資料

1.4、INSERT 小結及特殊字段插入方法

在使用 INSERT INTO 語句向表中插入新行時，除了帶預設值和帶辨別的字段，其它必填的字段都需要顯示的給出值，而非必填字段不給值時 SQL Server 預設會給它一個 NULL 值，也可以顯示的給定一個 NULL 值。

1.4.1、将資料插入有預設值的字段中時，如果沒有為指定了預設值的字段指定值，那麼新行的該字段的值将會是預設值。假如要添加一行，有預設值的字段就讓它為預設值，沒有預設值的字段就讓它為 NULL，那麼就可以用如下語句：

INSERT INTO T_GoodStudents DEFAULT VALUES;

1.4.2、将資料插入到辨別列中時，無論是指定插入字段還是不指定插入字段，都無需考慮辨別列，因為 SQL Server 的關系引擎會根據辨別增量和辨別種子自動為辨別列指派。如果需要為辨別列指定值，就需要先把 IDENTITY_INSERT 打開，然後才能插入，示例如下：

SET IDENTITY_INSERT T_Students ON;                  -- 目前會話有效，别的會話不受影響
INSERT INTO T_Students(Id,Name) VALUES(-1,'李哈哈'); -- Id 字段為辨別列

注意1：必須在 INTO 子句中顯示列出辨別列，否則即便在 VALUES 子句中提供所有字段的值也還是會報錯。

注意2：如果想在目前會話中繼續像預設情況那樣忽略辨別列，就需要把 IDENTITY_INSERT 關掉，示例如下：

SET IDENTITY_INSERT T_Students OFF;

2、删除語句 DELETE

2.1、用 DELETE 删除表中指定行

DELETE 語句用于從表中删除現有行，文法如下：

DELETE FROM table-name WHERE delete-conditions;

WHERE 子句的作用在于确定删除哪些行，示例如下：

DELETE FROM T_GoodStudents WHERE Id >= 20;                             -- 删除 Id 大于等于 20 的資料
DELETE FROM T_GoodStudents WHERE Id NOT IN(SELECT Id FROM T_Students); -- 删除 Id 不在學生表中的資料

注意：在 PL/SQL 中可以友善的給要删資料的表取個别名，以便限定 WHERE 子句中的字段，但在 T-SQL 中卻不能直接給 DELETE 語句中要删資料的表取别名。如果想要限定删除條件中的字段，可以用如下兩種寫法：

DELETE FROM T_Students WHERE T_Students.Id = 4;     -- 直接用表名來限定（條件字段少時比較友善）
DELETE T_Students FROM T_Students t WHERE t.Id = 5; -- 在 DELETE 子句中加上表名（條件字段多時更友善）

理論上 DELETE 語句是可以不帶 WHERE 子句的，但這個操作很危險，因為它意味着删除表中所有行。

2.2、用 TRUNCATE TABLE 高效清空表

TRUNCATE TABLE 用于删除表中的所有行，如果表中有辨別列，辨別列會重新開始計數，相當于清空了整個表。文法如下：

TRUNCATE TABLE table-name;

如要清空好學生表，示例如下：

TRUNCATE TABLE T_GoodStudents;

注意：盡管不帶 WHERE 條件的 DELETE 語句就可以删除表中所有資料，但 TRUNCATE TABLE 比 DELETE 的速度更快，使用的系統資源和事務日志資源也更少。

3、更新語句 UPDATE

UPDATE 語句用于更新指定表中的現有資料，文法如下：

UPDATE table-name 
SET column1 = value1,column2 = value2,...column-n = value-n 
WHERE update-conditions;

WHERE 子句用于限定哪些行需要被更新，如果不帶 WHERE 子句就會更新所有行，當然這很危險，一般也沒有這種需求。可以一次更新一個字段，也可以一次更新多個字段，字段的值可以顯示給出，也可以是個表達式，表達式中還可以引用表中的字段。示例如下：

UPDATE T_GoodStudents SET Name = '王娜' WHERE Id = 7;            -- 更新一個字段的值
UPDATE T_GoodStudents SET Name = '徐莉',Gender = 0 WHERE Id = 7; -- 更新多個字段的值
UPDATE T_GoodStudents SET Birthday = GETDATE()-10 WHERE Id = 7;  -- 用表達式給字段指派
UPDATE T_GoodStudents SET Birthday = Birthday-10 WHERE Id = 7;   -- 在表達式中引用字段
UPDATE T_GoodStudents SET Name += '學生' WHERE Id > 3;           -- 在姓名後面加上"學生"

3.1、SET 子句内包含子查詢時，示例如下（把班級名更新到學生備注中）：

UPDATE T_Students SET Remark = (SELECT t.Name FROM T_Classes t WHERE t.Id = ClassId);

注意1：上例中沒有 WHERE 子句，這意味着（不論學生表中的 ClassId 是否在班級表中出現過）都會更新整個學生表，ClassId 未在班級表中出現過的學生備注會被更新為 NULL。盡管看似簡單，但筆者就曾在職場中多次遇到工作數年的技術人員因忽略這點而誤改了資料。

注意2，如果恰好兩個表中的關聯字段名相同，大機率上會出問題或報錯，為了穩妥起見需要限定一下字段。在 Oracle 中可以友善的通過表别名來限定，然而 SQL Server 卻不支援給 UPDATE 語句的 UPDATE 子句中的表取别名，但可以直接通過表名來限定字段。示例如下：

UPDATE T_GoodStudents 
SET Name = (SELECT t.Name FROM T_Students t WHERE t.Id = T_GoodStudents.Id) 
WHERE T_GoodStudents.Id IN(SELECT Id FROM T_Students); -- 将學生表的姓名同步到好學生表

3.2、WHERE 子句内含子查詢時，示例如下（将單科考試 3 次不及格的寫入到學生備注中）：

UPDATE T_Students SET Remark = '單科3次不及格' 
WHERE Id IN(
    SELECT t.StudentId 
    FROM T_ExamResults t 
    WHERE t.Scores < 60 
    GROUP BY t.StudentId,t.CourseId HAVING COUNT(1) >= 3
);

3.3、帶 FROM 子句的 UPDATE 語句，示例如下（把所有學生最近一次考試的總成績更新到學生備注中）：

UPDATE T_Students SET Remark = t2.SumScore
FROM T_Students t1 
JOIN(
    SELECT t.StudentId,SUM(t.Scores) SumScore 
    FROM T_ExamResults t 
    WHERE t.Counts = (SELECT MAX(Counts) FROM T_ExamResults) 
    GROUP BY t.StudentId
) t2 
ON t1.Id=t2.StudentId;

如果隻需要更新部分學生，比如僅更新 1 班的學生，就可以在 ON 後面直接加

AND t1.ClassId=1

，或者在整個語句後面加

WHERE t1.ClassId=1

。有意思的是，這種 UPDATE 語句即便沒有 WHERE 條件，也不會對未在 FROM 子句中限定的行産生影響。

4、合并語句 MERGE

相比較 INSERT、DELETE、UPDATE 和 SELECT 來說，MERGE 出現的要晚一些，但也有十多年了，各大 SQL 資料庫在 21 世紀頭幾年陸續提供了對 MERGE 的支援。簡單來說，MERGE 語句就是對增删改查的“合并”，使得可以在一個語句内根據查詢的比對情況來決定是否要增、删或改某些資料，而不必再寫冗長的邏輯判斷和事物處理了。文法如下：

MERGE target-table-name
USING source-table-expressions ON merge-search-conditions
WHEN MATCHED AND clause-search-conditions THEN merge-matched
WHEN NOT MATCHED AND clause-search-conditions THEN merge-not-matched;

使用 MERGE 在單個語句中對表執行 INSERT 或 UPDATE 操作，示例如下：

MERGE T_Students AS target 
USING(SELECT '朱丹丹',0) AS source (Name,Gender) ON(target.Name = source.Name)
WHEN MATCHED THEN 
    UPDATE SET Gender = source.Gender 
WHEN NOT MATCHED THEN 
    INSERT(Name,Gender) VALUES(source.Name,source.Gender);

使用 MERGE 在單個語句中對表執行 INSERT、DELETE 或 UPDATE 操作，示例如下：

MERGE T_Students AS target 
USING(SELECT '劉天寶',1,'1990-09-09') AS source (Name,Gender,Birthday) 
ON(target.Name = source.Name)
WHEN MATCHED AND target.Birthday < source.Birthday THEN 
    DELETE 
WHEN MATCHED THEN 
    UPDATE SET target.Gender = source.Gender,target.Birthday = source.Birthday
WHEN NOT MATCHED THEN
    INSERT(Name,Gender,Birthday) VALUES(source.Name,source.Gender,source.Birthday);

5、用 TOP 參數限制受影響的行

熟悉 SQL Server 的開發者估計都知道 TOP 參數可以用來限制查詢語句的傳回行數，但其實 TOP 參數不僅可以限制 SELECT 的結果集，還以限制受 INSERT、DELETE 或 UPDATE 影響的行。

5.1、帶 TOP 參數的 INSERT 語句，示例如下（随機将 3 個女學生添加到好學生表）：

INSERT TOP(3) INTO T_GoodStudents 
SELECT t.Id,t.Name,t.Gender,t.Birthday FROM T_Students t WHERE t.Gender = 0;

如果想要按某種特定的順序插入資料，譬如要把年齡最大的 3 個學生添加到好學生表，示例如下：

INSERT INTO T_GoodStudents 
SELECT TOP(3) t.Id,t.Name,t.Gender,t.Birthday FROM T_Students t ORDER BY t.Birthday;

5.2、帶 TOP 參數的 DELETE 語句，示例如下（随機删除 3 個女學生）：

DELETE TOP(3) FROM T_GoodStudents WHERE Gender = 0;

如果想要按某種特定的順序删除資料，譬如要删除年齡最大的 3 個學生的資訊，示例如下：

DELETE FROM T_GoodStudents 
WHERE Id IN(SELECT TOP(3) t.Id FROM T_GoodStudents t ORDER BY t.Id DESC);

5.3、帶 TOP 參數的 UPDATE 語句，示例如下（随機将 3 個男學生的性别更新為 0）：

UPDATE TOP(3) T_Students SET Gender = 0 WHERE Gender = 1;

如果想要按某種特定的順序更新資料，譬如要将年齡最大的 3 個男學生的性别更新為 0，示例如下：

UPDATE T_GoodStudents SET Gender = 0 
FROM(SELECT TOP(3) t1.Id FROM T_GoodStudents t1 ORDER BY t1.Id DESC) t2 
WHERE T_GoodStudents.Id = t2.Id;

6、用 OUTPUT 子句傳回受影響的資料

試想一下，如果需要在插入的一條資料的同時傳回這條資料，或者在删除一條資料的同時備份這條資料，我們當然可以用多條簡單語句來共同完成，并且通過事務來確定操作的原子性。但其實這類需求可以通過 OUTPUT 子句來更好的完成，而且一個語句就能搞定，不必加事務，因為它本身就具備原子性。

在使用 OUTPUT 傳回資料時，需要借助 INSERTED 或 DELETED 來引用字段值。INSERTED 用來引用插入操作或更新操作添加的值，DELETED 用來引用删除操作或更新操作删除的值。在 INSERT 語句中不能通路 DELETED，在 DELETE 語句中不能通路 INSERTED，在 UPDATE 語句中兩個都能通路。示例如下：

INSERT T_GoodStudents OUTPUT inserted.* VALUES(7,'高鵬',1,'1979-11-11'); -- 插入 1 條資訊并輸出
DELETE TOP(1) FROM T_GoodStudents OUTPUT deleted.Id,deleted.Name;       -- 删除 1 條資訊并輸出

UPDATE TOP(2) T_GoodStudents SET Gender = 1 
OUTPUT deleted.Name,inserted.Name,deleted.Gender,inserted.Gender;       -- 更新 2 條資訊并輸出

還可以結合 INTO 把 OUTPUT 傳回的資料插入到另一張表中，示例如下：

INSERT T_GoodStudents OUTPUT inserted.* INTO T_GoodStudents VALUES(9,'黃強',1,'1999-11-11');
DELETE TOP(1) FROM T_GoodStudents OUTPUT deleted.* INTO T_GoodStudents;
UPDATE TOP(2) T_GoodStudents SET Gender = 1 OUTPUT deleted.* INTO T_GoodStudents;

7、本文小結

本文主要講述了 T-SQL 語句中的 INSERT、DELETE、UPDATE 和 MERGE 共 4 個 DML 語句及其子句，以及一個 DDL 語句 TRUNCATE TABLE，而且這幾個語句都是實際開發中特别常用的語句。

在 Oracle 中總是給表取别名是個很好的習慣，但 SQL Server 的增删改語句均不支援對目标表取别名，隻有合并語句和查詢語句支援别名。不過 SQL Server 中的所有 DML 語句都支援用表名來限定字段名。

有些讀者可能會有疑問“為什麼 SQL Server 管理工具生成的語句總是要給對象名前後加上中括号？”。盡管不好看，但的确有道理，因為它可以防止使用者自定義名稱跟系統關鍵字沖突。譬如你要用 USER 做表名或字段名，就得用中括号包裹一下。另外，如果想用某些特殊符号來命名也需要用中括号包裹，但一般不建議這麼做，太變态了！

如果你不幸遇到頭尾帶空格的對象名，你會發現隻寫空格以外的名稱部分是通路不到該對象的，這種情況也可以用中括号來解決。如果你有修改權限的話建議還是把空格删掉吧，太惡心了！假如學生表前後有空格，查詢示例如下：

SELECT * FROM [ T_Students ];

本文參考連結：

1、SQL Server 2016 INSERT
2、SQL Server 2016 DELETE
3、SQL Server 2016 TRUNCATE TABLE
4、SQL Server 2016 UPDATE
5、SQL Server 2016 MERGE
6、SQL Server 2016 TOP
7、SQL Server 2016 OUTPUT

本文連結：http://www.cnblogs.com/hanzongze/p/tsql-crud.html

版權聲明：本文為部落格園部落客韓宗澤原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文連結！個人部落格，能力有限，若有不當之處，敬請批評指正，謝謝！