SQL Server溫故系列(1)：SQL 數據操作 CRUD 之增刪改合

来源:https://www.cnblogs.com/hanzongze/archive/2019/07/06/tsql-crud.html
-Advertisement-
Play Games

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


毋庸置疑，開發者最常用的資料庫技術就是 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 ];

本文參考鏈接：

去導航目錄篇下載創建本系列博文通用庫表及數據的 SQL 語句

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 超級簡單，把PuppyLinux安裝到U盤
    下載最新版puppylinux，從官網下載 現在U盤引導程式製作工具Unetbootin 打開下載的UNetbootin，進行下麵的操作： 製作完畢後，修改U盤的配置文件：用編輯器打開syslinux.cfg，把：append initrd=/initrd.gz pmedia=cd改為：append ...
  • （3）Linux命令分類彙總（7~12）
    用戶管理命令、網路操作命令、磁碟與文件系統命令、系統許可權及用戶授權相關命令、內置命令、 ...
  • ps命令入門使用指南
    常用的ps命令，如ps aux，ps lax，ps f，ps -A | grep java，ps aux > ps001.txt， ps aux --sort cmd等 ...
  • Linux系統：centos7下搭建ZooKeeper3.4中間件，常用命令總結
    一、下載解壓 1、Zookeeper簡介 Zookeeper 作為一個分散式的服務框架，主要用來解決分散式集群中應用系統的一致性問題，它能提供基於類似於文件系統的目錄節點樹方式的數據存儲，但是 Zookeeper 並不是用來專門存儲數據的，它的作用主要是用來維護和監控你存儲的數據的狀態變化。通過監控 ...
  • Windows 2003 IIS6.0下配置ASP+MySQL+PHP+ISAPI_Rewrite+Zend+Xcache
    windows 2003,自己買吧...安裝IIS6.0:安裝系統後在"控制面板"->"添加或刪除程式"->"添加/刪除Windows組件"->雙擊"應用程式伺服器"->然後選中"Internet信息服務(IIS)"->確定->安裝完畢...下載PHP:http://cn2.php.net/get/ ...
  • 總結關於CPU的一些基本知識
    關於CPU和程式的執行 CPU是電腦的大腦。 1. 程式的運行過程，實際上是程式涉及到的、未涉及到的一大堆的指令的執行過程 。 當程式要執行的部分被裝載到記憶體後，CPU要從記憶體中取出指令，然後指令解碼(以便知道類型和操作數，簡單的理解為CPU要知道這是什麼指令)，然後執行該指令。再然後取下一個指令 ...
  • IIS下配置Php+Mysql+zend的圖文教程(完整版)
    網上有很多關於PHP在IIS下配置的教程,但都是一些很理性化的東西,我從裡面整理出來這個教程 發出來為了方便參考,,有什麼問題也可以大家一起交流,,如果有什麼不對的地方,請指正.. 下麵的教程都是在windows 2000下實現的,其他系統請自己參照修改, 操作系統目錄:c:\winnt PHP預設 ...
  • Linux(二)各種實用命令
    繼續Linux命令學習，沒有什麼捷徑，每個命令都去敲幾遍就熟悉了，第二篇學習的是一些比較實用類的命令，主要是從開發的角度進行學習，並不深入，話不多說，開始！ 一、系統管理類 1.1 stat --stat 顯示指定文件的相關信息,比ls命令顯示內容更多 1.2 who --顯示線上登錄用戶 1.3 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類