放牧的风

SQL Server温故系列(1)：SQL 数据操作 CRUD 之增删改合

毋庸置疑，开发者最常用的数据库技术就是 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