《SQL Server 2012 T-SQL基础》读书笔记 - 8.数据修改

Chapter 8 Data Modification

SQL Server 2008开始，支持一个语句中插入多行：

INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
VALUES
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C');


这个语句作为一个原子操作，所以如果任何行插入失败了，那么整句都不会有效果。

VALUES这个子句还可以用作一个“table value constructor”来创建一个派生表：

SELECT *
FROM (VALUES
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C') )
AS O(orderid, orderdate, empid, custid);


这里的O就是表名，但是这些列的数据类型怎么算，书上没说。

在2008以前的版本，可以用这种技巧一次性插入多行：

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
SELECT 10007, '20090215', 2, 'B' UNION ALL
SELECT 10008, '20090215', 1, 'C' UNION ALL
SELECT 10009, '20090216', 2, 'C' UNION ALL
SELECT 10010, '20090216', 3, 'A';


同样，这也是一个原子操作。

还有一种是INSERT EXEC，就是把一个存储过程（或一个dynamic SQL batch）的结果集插到表里，举例：

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
EXEC Sales.usp_getorders @country = 'France';


这里的Sales.usp_getorders会返回匹配目标表的一个结果集。

有种非标准SQL的语法：

SELECT orderid, orderdate, empid, custid
INTO dbo.Orders
FROM Sales.Orders;


作用就是创建了一个新的表叫dbo.Orders，并且用所有Sales.Orders表中的行来填充它。新表的数据类型都和源表一样，但是constraints, indexes,triggers, and permissions不会被copy。这种语法也可以和set operator（UNION什么的）一起用。

BULK INSERT可以从文件里拿数据，书上讲得十分简略：

BULK INSERT dbo.Orders FROM 'c:\temp\orders.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);


The Identity Property and the Sequence Object:

SELECT $identity FROM dbo.T1;


里面的$identity代表这个表里面被设为IDENTITY的那一列。SCOPE_IDENTITY函数可以返回当前scope下最近生成的一个自增量的值，也可以用陈旧的 @@identity，但是 @@identity 不会考虑scope（比如当前的存储过程和被INSERT引发的trigger属于不同的scope）。但他俩都是对当前的会话（session）而言的，如果你想查最近的自增量的值(任何session中的)，你可以用IDENT_CURRENT('dbo.T1')，参数为表名。

如果你INSERT失败了（比如违反了某个Constraint，或者是在一个事务中ROLL BACK了），那么自增量还是会增，不会撤销。

SET IDENTITY_INSERT dbo.T1 ON;之后你就可以显式地指定一个值插到自增列：

SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1(keycol, datacol) VALUES(5, 'FFFFF');
SET IDENTITY_INSERT dbo.T1 OFF;


只有当你指定的值大于表当前的自增值的时候，这个表的自增值才会变，否则不变。而且自增量本身并不强制唯一性（uniqueness in the column）。

Sequence object是SQL Server 2012加入的，和Identity类似，但更灵活。Sequence是一个单独的object，你可以这么创建它：

CREATE SEQUENCE dbo.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;


然后用NEXT VALUE FOR dbo.SeqOrderIDs就可以得到一个新生成的值。现在讲一下创建sequence时候的选项：如果不指定AS INT，默认是BIGINT。如果不指定MINVALUE <val>或者MAXVALUE <val>)，默认就是这个数据类型支持的最小和最大值。CYCLE | NO CYCLE估计就是到了最大值之后再返回最小的值。以及START WITH <val>和INCREMENET BY <val>，默认分别是最小值和1。CACHE <val> | NO CACHE的意思貌似是：每生成val个值就往硬盘写一下，默认是CACHE x（这个x我们不知道）。当然你也可以用ALTER SEQUENCE来改变这些选项。用的时候可以这么用：

DECLARE @neworderid AS INT = NEXT VALUE FOR dbo.SeqOrderIDs;
INSERT INTO dbo.T1(keycol, datacol) VALUES(@neworderid, 'a');


这里从dbo.SeqOrderIDs生成了一个值，存入一个变量 @neworderid，然后用这个变量新增了一行，比如有一个一对多的关系的话，先生成一个“一”的值，由于那些“多”的值的外键的那列也应该是这个值，可以继续用 @neworderid。

查询某个Sequence object的当前值：

SELECT current_value
FROM sys.sequences
WHERE OBJECT_ID = OBJECT_ID('dbo.SeqOrderIDs');


再举个用法的例子：

INSERT INTO dbo.T1(keycol, datacol)
SELECT
NEXT VALUE FOR dbo.SeqOrderIDs OVER(ORDER BY hiredate),
LEFT(firstname, 1) + LEFT(lastname, 1)
FROM HR.Employees;


也就是hiredate越早的员工，生成的key越小。

再举个例子：

ALTER TABLE dbo.T1
ADD CONSTRAINT DFT_T1_keycol
DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs)
FOR keycol;


这样的话就有点类似于Identity的功能了。因为Identity只能在创建表的时候指定（或者add column的时候指定），但是用sequence就可以像这样随时加（或删）一个约束。

用sp_sequence_get_range貌似可以把某个sequence object的值一次增加很多，比如1000。

与Identity同样，如果在一个事务里面生成了一个新值，然后又回滚了，那么不会撤销对sequence的改变。

TRUNCATE是从一个表删除所有行，与DELETE不同的是，TRUNCATE是minimally logged的，而DELETE是fully logged。但既然他们都会被log，所以在事务中ROLL BACK的话还是完全可以恢复的。顺便一提，TRUNCATE会重置Identity值。

T-SQL支持一种非标准的DELETE：

DELETE FROM O
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = N'USA';


其实和SELECT里面的JOIN完全一样，只不过最后你DELETE FROM（注意有两个FROM）后面加你要删除的表就行了，这里的话就只会删除Orders表里面对应的行。这里的Join只是起到一个过滤的作用。

实现同样功能的标准SQL：

DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT *
FROM dbo.Customers AS C
WHERE Orders.Custid = C.Custid
AND C.Country = 'USA');


记得考虑第一章说过的All-at-once operations，所以说以下语句：

UPDATE dbo.T1 SET col1 = col1 + 10, col2 = col1 + 10;

假设一开始col1是100，那么执行后col1和col2都是110。

交换col1和col2的值可以这么做：UPDATE dbo.T1 SET col1 = col2, col2 = col1;

与DELETE类似，也有一种基于Join的非标准语法：

UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1;


UPDATE的目标表是OrderDetails。相比用标准SQL的子查询，这种语法的好处体现在：

UPDATE T1
SET col1 = T2.col1,
col2 = T2.col2,
col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
ON T2.keycol = T1.keycol
WHERE T2.col4 = 'ABC';


也就是说UPDATE的新值可以是基于T2表里面的东西。如果一行T1对应多行T2怎么办？书上没说这个问题，但我猜会报错，因为参考下面的标准SQL：

UPDATE dbo.T1
SET col1 = (SELECT col1
FROM dbo.T2
WHERE T2.keycol = T1.keycol), col2 = (SELECT col2
FROM dbo.T2
WHERE T2.keycol = T1.keycol), col3 = (SELECT col3
FROM dbo.T2
WHERE T2.keycol = T1.keycol)
WHERE EXISTS
(SELECT *
FROM dbo.T2
WHERE T2.keycol = T1.keycol
AND T2.col4 = 'ABC');


子查询那章说过了，如果上面的SELECT出来不是标量，那么就会报错。这样的写法的效率远不如刚才。

下面这种语法，就相当于省了你一次UPDATE+另一次SELECT：

UPDATE dbo.Sequences
SET @nextval = val += 1
WHERE id = 'SEQ1';
SELECT @nextval;


这里的 @nextval 变量在UPDATE之后，会变成val的新值。

假设有两个表，他们除了名字，其他都一模一样，但是数据不一样，现在想把CustomersStage表中的数据合并到Customers表里：

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);


这里的ON后面的TGT.custid = SRC.custid其实是定义一下：什么时候算Match，上面时候不算Match。但是书上没说如果目标表里的一行匹配源表中的多行，咋整。注意上面的UPDATE和INSERT子句后面都不需要写表名了。这里的WHEN NOT MATCHED其实指的是那些“不在Customers表，但在CustomersStage”的行，你也可以用WHEN NOT MATCHED BY SOURCE来指定那些“在Customers表，但不在CustomersStage”的行，自己脑子里想一下集合的文氏图就知道了。比如我们可以给上面的SQL再加上：

WHEN NOT MATCHED BY SOURCE THEN
DELETE;


但是这样我觉得还不如直接全删了Customers，然后直接把CustomersStage复制过去。

顺便一提，书上说可以这样稍微优化一下：

WHEN MATCHED AND
( TGT.companyname <> SRC.companyname
OR TGT.phone <> SRC.phone
OR TGT.address <> SRC.address) THEN
UPDATE SET
...


于是乎，那些其实其实没区别的行就不需要重写一遍了。

其实也可以通过表表达式来修改数据，因为表表达式你就可以看成一张表，然后直接可以，比如INSERT INTO这张表，最终会影响它的“underlying table”。但有几点限制：1.如果定义表表达式的SELECT里面有JOIN，那么你对其进行修改的语句只能影响JOIN的一边，而不能同时影响JOIN两边的两个表。2.不能对作为计算结果的列进行更新（这条的意思应该是比如表表达式中的SELECT有个(col1 + 1) AS result，那么你就不能更新这个result，SQL Server不会做“反运算”）。 如果你想想通过以下语句把col2改成1,2,3...（根据col1的大小来生成）：

UPDATE dbo.T1
SET col2 = ROW_NUMBER() OVER(ORDER BY col1);


那么你会得到以下错误：

Msg 4108, Level 15, State 1, Line 2

Windowed functions can only appear in the SELECT or ORDER BY clauses.

所以用表表达式吧：

WITH C AS
(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS rownum
FROM dbo.T1
)
UPDATE C
SET col2 = rownum;


修改数据的时候可以指定TOP或OFFSET-FETCH，比如：

DELETE TOP(50) FROM dbo.Orders;
UPDATE TOP(50) dbo.Orders SET freight += 10.00;


但是修改数据不支持ORDER BY，所以这里只是随机删除50行，但用表表达式可以控制：

WITH C AS
(
SELECT TOP(50) *
FROM dbo.Orders
ORDER BY orderid
)
DELETE FROM C;


我认为也可以用子查询来完成。

OUPUT的作用主要就是可以从你修改的数据中返回一些数据。比如：

INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
SELECT lastname
FROM HR.Employees
WHERE country = N'USA';


这里的keycol是自增列，结果：



这里的OUTPUT子句很类似SELECT，“insert.”表示插入后的值。还有一个例子：

DECLARE @NewRows TABLE(keycol INT, datacol NVARCHAR(40));INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
INTO @NewRows
SELECT lastname
FROM HR.Employees
WHERE country = N'UK';SELECT * FROM @NewRows;


唯一的不同就是多了一个INTO @NewRows，意思就是把OUTPUT返回的结果集放到 @NewRows 这个表变量里。

删除的例子：

DELETE FROM dbo.Orders
OUTPUT
deleted.orderid,
deleted.orderdate,
deleted.empid,
deleted.custid
WHERE orderdate < '20080101';


结果：



OUTPUT会返回被删除的行，如果你想把这些行放到某个表，加个INTO和目标表表名就行。

用UPDATE的时候，既可以用deleted前缀表示更新前的值，也可以用inserted前缀表示更新后的值，例子：

UPDATE dbo.OrderDetails
SET discount += 0.05
OUTPUT
inserted.productid,
deleted.discount AS olddiscount,
inserted.discount AS newdiscount
WHERE productid = 51;


结果：



也可以在MERGE中用OUTPUT：

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action AS theaction, inserted.custid,
deleted.companyname AS oldcompanyname,
inserted.companyname AS newcompanyname,
deleted.phone AS oldphone,
inserted.phone AS newphone,
deleted.address AS oldaddress,
inserted.address AS newaddress;


结果（部分结果）：



这里的$action函数告诉你用的是INSERT还是UPDATE。

再举个比较复杂的例子：

INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE supplierid = 1) AS D
WHERE oldval < 20.0 AND newval >= 20.0;


这里干了什么？首先FROM后面定义了一个表表达式，这里的表表达式其实是通过它里面的OUTPUT子句定义的，你可以把OUTOUT的结果看成一张表，然后在这张表上再过滤一点数据，然后插入到最外层的那个表里去。