• 1、插入語(yǔ)句 INSERT INTO
  • 1.1、用 INSERT 插入單行數(shù)據(jù)
  • 1.2、用 INSERT 插入多行數(shù)據(jù)
  • 1.3、用 INSERT 插入子查詢結(jié)果行
  • 1.4、INSERT 小結(jié)及特殊字段插入方法
• 2、刪除語(yǔ)句 DELETE
  • 2.1、用 DELETE 刪除表中指定行
  • 2.2、用 TRUNCATE TABLE 高效清空表
• 3、更新語(yǔ)句 UPDATE
• 4、合并語(yǔ)句 MERGE
• 5、用 TOP 子句限制受影響的行
• 6、用 OUTPUT 子句返回受影響的數(shù)據(jù)
• 7、本文小結(jié)

毋庸置疑，開(kāi)發(fā)者最常用的數(shù)據(jù)庫(kù)技術(shù)就是 SQL 了，即便是 ORM 大行其道的今天也常常需要寫 SQL 語(yǔ)句。而 SQL 語(yǔ)句中最常用的就是增刪改查了，本系列就先對(duì)增刪改查語(yǔ)句來(lái)個(gè)系統(tǒng)的回顧吧！

1、插入語(yǔ)句 INSERT INTO

1.1、用 INSERT 插入單行數(shù)據(jù)

INSERT INTO 的作用是向表中添加新行，語(yǔ)法如下：

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

譬如要向好學(xué)生表中添加 1 條數(shù)據(jù)，示例如下：

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

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

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

1.2、用 INSERT 插入多行數(shù)據(jù)

INSERT INTO 還可以一次向表中添加多條數(shù)據(jù)，如要一次性向?qū)W生表中添加 3 條數(shù)據(jù)，示例如下：

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

注意：在插入全部字段時(shí)，插入多行數(shù)據(jù)也可以像插入單行數(shù)據(jù)那樣省略字段列表，但必須確保各行之間的數(shù)據(jù)個(gè)數(shù)相同、類型兼容。

1.3、用 INSERT 插入子查詢結(jié)果行

向表中插入數(shù)據(jù)時(shí)，既可以通過(guò) VALUES 子句顯示地列出插入值，也可以通過(guò) SELECT 子句來(lái)獲得插入值。語(yǔ)法如下：

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

該語(yǔ)句的效果類似于把一張表的數(shù)據(jù)復(fù)制到另一張表，要復(fù)制的字段和行都可以顯示的指定。當(dāng)要將大量行從源表傳輸?shù)侥繕?biāo)表中時(shí)，該語(yǔ)句還能夠以最小日志記錄的方式高效的完成。示例如下：

INSERT INTO T_GoodStudents SELECT Id,Name,Gender,Birthday FROM T_Students; -- 完全復(fù)制（數(shù)據(jù)） INSERT INTO T_GoodStudents(Name,Gender) SELECT Name,Gender FROM T_Students; -- 指定部分字段復(fù)制 INSERT INTO T_GoodStudents(Name) SELECT Name FROM T_Students WHERE Gender=1; -- 指定部分?jǐn)?shù)據(jù)復(fù)制

如果目標(biāo)表和源表的表結(jié)構(gòu)相同，子查詢的字段列表還可以用 * 來(lái)代替。在指定字段復(fù)制時(shí)，無(wú)需表結(jié)構(gòu)相同，只要對(duì)應(yīng)字段的數(shù)據(jù)類型兼容即可，甚至還可以沒(méi)有源表，一個(gè)子查詢就夠了。示例如下：

INSERT INTO T_GoodStudents SELECT 999,'李敏',0,'1991-02-02'; -- 插入 1 條（來(lái)自子查詢的）數(shù)據(jù)INSERT INTO T_GoodStudents(Id,Name,Birthday) SELECT 11,'王陽(yáng)','1991-03-02' UNION ALL SELECT 12,'李玉','1991-07-02' UNION ALL SELECT 13,'鄭爽','1991-02-02'; -- 插入 3 條（來(lái)自子查詢的）數(shù)據(jù)

1.4、INSERT 小結(jié)及特殊字段插入方法

在使用 INSERT INTO 語(yǔ)句向表中插入新行時(shí)，除了帶默認(rèn)值和帶標(biāo)識(shí)的字段，其它必填的字段都需要顯示的給出值，而非必填字段不給值時(shí) SQL Server 默認(rèn)會(huì)給它一個(gè) NULL 值，也可以顯示的給定一個(gè) NULL 值。

1.4.1、將數(shù)據(jù)插入有默認(rèn)值的字段中 時(shí)，如果沒(méi)有為指定了默認(rèn)值的字段指定值，那么新行的該字段的值將會(huì)是默認(rèn)值。假如要添加一行，有默認(rèn)值的字段就讓它為默認(rèn)值，沒(méi)有默認(rèn)值的字段就讓它為 NULL，那么就可以用如下語(yǔ)句：

INSERT INTO T_GoodStudents DEFAULT VALUES;

1.4.2、將數(shù)據(jù)插入到標(biāo)識(shí)列中 時(shí)，無(wú)論是指定插入字段還是不指定插入字段，都無(wú)需考慮標(biāo)識(shí)列，因?yàn)?SQL Server 的關(guān)系引擎會(huì)根據(jù)標(biāo)識(shí)增量和標(biāo)識(shí)種子自動(dòng)為標(biāo)識(shí)列賦值。如果需要為標(biāo)識(shí)列指定值，就需要先把 IDENTITY_INSERT 打開(kāi)，然后才能插入，示例如下：

SET IDENTITY_INSERT T_Students ON; -- 當(dāng)前會(huì)話有效，別的會(huì)話不受影響 INSERT INTO T_Students(Id,Name) VALUES(-1,'李哈哈'); -- Id 字段為標(biāo)識(shí)列

注意1：必須在 INTO 子句中顯示列出標(biāo)識(shí)列，否則即便在 VALUES 子句中提供所有字段的值也還是會(huì)報(bào)錯(cuò)。

注意2：如果想在當(dāng)前會(huì)話中繼續(xù)像默認(rèn)情況那樣忽略標(biāo)識(shí)列，就需要把 IDENTITY_INSERT 關(guān)掉，示例如下：

SET IDENTITY_INSERT T_Students OFF;

2、刪除語(yǔ)句 DELETE

2.1、用 DELETE 刪除表中指定行

DELETE 語(yǔ)句用于從表中刪除現(xiàn)有行，語(yǔ)法如下：

DELETE FROM table-name WHERE delete-conditions;

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

DELETE FROM T_GoodStudents WHERE Id >= 20; -- 刪除 Id 大于等于 20 的數(shù)據(jù) DELETE FROM T_GoodStudents WHERE Id NOT IN(SELECT Id FROM T_Students); -- 刪除 Id 不在學(xué)生表中的數(shù)據(jù)

注意：在 PL/SQL 中可以方便的給要?jiǎng)h數(shù)據(jù)的表取個(gè)別名，以便限定 WHERE 子句中的字段，但在 T-SQL 中卻不能直接給 DELETE 語(yǔ)句中要?jiǎng)h數(shù)據(jù)的表取別名。如果想要限定刪除條件中的字段，可以用如下兩種寫法：

DELETE FROM T_Students WHERE T_Students.Id = 4; -- 直接用表名來(lái)限定（條件字段少時(shí)比較方便） DELETE T_Students FROM T_Students t WHERE t.Id = 5; -- 在 DELETE 子句中加上表名（條件字段多時(shí)更方便）

理論上 DELETE 語(yǔ)句是可以不帶 WHERE 子句的，但這個(gè)操作很危險(xiǎn)，因?yàn)樗馕吨鴦h除表中所有行。

2.2、用 TRUNCATE TABLE 高效清空表

TRUNCATE TABLE 用于刪除表中的所有行，如果表中有標(biāo)識(shí)列，標(biāo)識(shí)列會(huì)重新開(kāi)始計(jì)數(shù)，相當(dāng)于清空了整個(gè)表。語(yǔ)法如下：

TRUNCATE TABLE table-name;

如要清空好學(xué)生表，示例如下：

TRUNCATE TABLE T_GoodStudents;

注意：盡管不帶 WHERE 條件的 DELETE 語(yǔ)句就可以刪除表中所有數(shù)據(jù)，但 TRUNCATE TABLE 比 DELETE 的速度更快，使用的系統(tǒng)資源和事務(wù)日志資源也更少。

3、更新語(yǔ)句 UPDATE

UPDATE 語(yǔ)句用于更新指定表中的現(xiàn)有數(shù)據(jù)，語(yǔ)法如下：

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

WHERE 子句用于限定哪些行需要被更新，如果不帶 WHERE 子句就會(huì)更新所有行，當(dāng)然這很危險(xiǎn)，一般也沒(méi)有這種需求。可以一次更新一個(gè)字段，也可以一次更新多個(gè)字段，字段的值可以顯示給出，也可以是個(gè)表達(dá)式，表達(dá)式中還可以引用表中的字段。示例如下：

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

3.1、SET 子句內(nèi)包含子查詢時(shí)，示例如下（把班級(jí)名更新到學(xué)生備注中）：

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

注意1：上例中沒(méi)有 WHERE 子句，這意味著（不論學(xué)生表中的 ClassId 是否在班級(jí)表中出現(xiàn)過(guò)）都會(huì)更新整個(gè)學(xué)生表，ClassId 未在班級(jí)表中出現(xiàn)過(guò)的學(xué)生備注會(huì)被更新為 NULL。盡管看似簡(jiǎn)單，但筆者就曾在職場(chǎng)中多次遇到工作數(shù)年的技術(shù)人員因忽略這點(diǎn)而誤改了數(shù)據(jù)。

注意2，如果恰好兩個(gè)表中的關(guān)聯(lián)字段名相同，大概率上會(huì)出問(wèn)題或報(bào)錯(cuò)，為了穩(wěn)妥起見(jiàn)需要限定一下字段。在 Oracle 中可以方便的通過(guò)表別名來(lái)限定，然而 SQL Server 卻不支持給 UPDATE 語(yǔ)句的 UPDATE 子句中的表取別名，但可以直接通過(guò)表名來(lái)限定字段。示例如下：

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); -- 將學(xué)生表的姓名同步到好學(xué)生表

3.2、WHERE 子句內(nèi)含子查詢時(shí)，示例如下（將單科考試 3 次不及格的寫入到學(xué)生備注中）：

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 語(yǔ)句，示例如下（把所有學(xué)生最近一次考試的總成績(jī)更新到學(xué)生備注中）：

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;

如果只需要更新部分學(xué)生，比如僅更新 1 班的學(xué)生，就可以在 ON 后面直接加AND t1.ClassId=1，或者在整個(gè)語(yǔ)句后面加WHERE t1.ClassId=1。有意思的是，這種 UPDATE 語(yǔ)句即便沒(méi)有 WHERE 條件，也不會(huì)對(duì)未在 FROM 子句中限定的行產(chǎn)生影響。

4、合并語(yǔ)句 MERGE

相比較 INSERT、DELETE、UPDATE 和 SELECT 來(lái)說(shuō)，MERGE 出現(xiàn)的要晚一些，但也有十多年了，各大 SQL 數(shù)據(jù)庫(kù)在 21 世紀(jì)頭幾年陸續(xù)提供了對(duì) MERGE 的支持。簡(jiǎn)單來(lái)說(shuō)，MERGE 語(yǔ)句就是對(duì)增刪改查的“合并”，使得可以在一個(gè)語(yǔ)句內(nèi)根據(jù)查詢的匹配情況來(lái)決定是否要增、刪或改某些數(shù)據(jù)，而不必再寫冗長(zhǎng)的邏輯判斷和事物處理了。語(yǔ)法如下：

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 在單個(gè)語(yǔ)句中對(duì)表執(zhí)行 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 在單個(gè)語(yǔ)句中對(duì)表執(zhí)行 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 THENINSERT(Name,Gender,Birthday) VALUES(source.Name,source.Gender,source.Birthday);

5、用 TOP 參數(shù)限制受影響的行

熟悉 SQL Server 的開(kāi)發(fā)者估計(jì)都知道 TOP 參數(shù)可以用來(lái)限制查詢語(yǔ)句的返回行數(shù)，但其實(shí) TOP 參數(shù)不僅可以限制 SELECT 的結(jié)果集，還以限制受 INSERT、DELETE 或 UPDATE 影響的行。

5.1、帶 TOP 參數(shù)的 INSERT 語(yǔ)句，示例如下（隨機(jī)將 3 個(gè)女學(xué)生添加到好學(xué)生表）：

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

如果想要按某種特定的順序插入數(shù)據(jù)，譬如要把年齡最大的 3 個(gè)學(xué)生添加到好學(xué)生表，示例如下：

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 參數(shù)的 DELETE 語(yǔ)句，示例如下（隨機(jī)刪除 3 個(gè)女學(xué)生）：

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

如果想要按某種特定的順序刪除數(shù)據(jù)，譬如要?jiǎng)h除年齡最大的 3 個(gè)學(xué)生的信息，示例如下：

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

5.3、帶 TOP 參數(shù)的 UPDATE 語(yǔ)句，示例如下（隨機(jī)將 3 個(gè)男學(xué)生的性別更新為 0）：

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

如果想要按某種特定的順序更新數(shù)據(jù)，譬如要將年齡最大的 3 個(gè)男學(xué)生的性別更新為 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 子句返回受影響的數(shù)據(jù)

試想一下，如果需要在插入的一條數(shù)據(jù)的同時(shí)返回這條數(shù)據(jù)，或者在刪除一條數(shù)據(jù)的同時(shí)備份這條數(shù)據(jù)，我們當(dāng)然可以用多條簡(jiǎn)單語(yǔ)句來(lái)共同完成，并且通過(guò)事務(wù)來(lái)確保操作的原子性。但其實(shí)這類需求可以通過(guò) OUTPUT 子句來(lái)更好的完成，而且一個(gè)語(yǔ)句就能搞定，不必加事務(wù)，因?yàn)樗旧砭途邆湓有浴?/p>

在使用 OUTPUT 返回?cái)?shù)據(jù)時(shí)，需要借助 INSERTED 或 DELETED 來(lái)引用字段值。INSERTED 用來(lái)引用插入操作或更新操作添加的值，DELETED 用來(lái)引用刪除操作或更新操作刪除的值。在 INSERT 語(yǔ)句中不能訪問(wèn) DELETED，在 DELETE 語(yǔ)句中不能訪問(wèn) INSERTED，在 UPDATE 語(yǔ)句中兩個(gè)都能訪問(wèn)。示例如下：

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 條信息并輸出

還可以結(jié)合 INTO 把 OUTPUT 返回的數(shù)據(jù)插入到另一張表中，示例如下：

INSERT T_GoodStudents OUTPUT inserted.* INTO T_GoodStudents VALUES(9,'黃強(qiáng)',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、本文小結(jié)

本文主要講述了 T-SQL 語(yǔ)句中的 INSERT、DELETE、UPDATE 和 MERGE 共 4 個(gè) DML 語(yǔ)句及其子句，以及一個(gè) DDL 語(yǔ)句 TRUNCATE TABLE，而且這幾個(gè)語(yǔ)句都是實(shí)際開(kāi)發(fā)中特別常用的語(yǔ)句。

在 Oracle 中總是給表取別名是個(gè)很好的習(xí)慣，但 SQL Server 的增刪改語(yǔ)句均不支持對(duì)目標(biāo)表取別名，只有合并語(yǔ)句和查詢語(yǔ)句支持別名。不過(guò) SQL Server 中的所有 DML 語(yǔ)句都支持用表名來(lái)限定字段名。

有些讀者可能會(huì)有疑問(wèn)“為什么 SQL Server 管理工具生成的語(yǔ)句總是要給對(duì)象名前后加上中括號(hào)？”。盡管不好看，但的確有道理，因?yàn)樗梢苑乐褂脩糇远x名稱跟系統(tǒng)關(guān)鍵字沖突。譬如你要用 USER 做表名或字段名，就得用中括號(hào)包裹一下。另外，如果想用某些特殊符號(hào)來(lái)命名也需要用中括號(hào)包裹，但一般不建議這么做，太變態(tài)了！

如果你不幸遇到頭尾帶空格的對(duì)象名，你會(huì)發(fā)現(xiàn)只寫空格以外的名稱部分是訪問(wèn)不到該對(duì)象的，這種情況也可以用中括號(hào)來(lái)解決。如果你有修改權(quán)限的話建議還是把空格刪掉吧，太惡心了！假如學(xué)生表前后有空格，查詢示例如下：

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

去導(dǎo)航目錄篇下載創(chuàng)建本系列博文通用庫(kù)表及數(shù)據(jù)的 SQL 語(yǔ)句

本文鏈接：http://www.cnblogs.com/hanzongze/p/tsql-crud.html
版權(quán)聲明：本文為博客園博主 韓宗澤 原創(chuàng)，作者保留署名權(quán)！歡迎通過(guò)轉(zhuǎn)載、演繹或其它傳播方式來(lái)使用本文，但必須在明顯位置給出作者署名和本文鏈接！個(gè)人博客，能力有限，若有不當(dāng)之處，敬請(qǐng)批評(píng)指正，謝謝！

轉(zhuǎn)載于:https://www.cnblogs.com/hanzongze/p/tsql-crud.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server温故系列(1)：SQL 数据操作 CRUD 之增删改合的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。