微软认证考试70-461 Modify Data 数据修改 --24%比重--(2)

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第三节Modify Data数据修改。

第一部分直通车

第二部分：Modify data by using INSERT, UPDATE, and DELETE statements. May include but not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement. 使用INSERT，UPDATE和DELETE语句更新数据。可能包含但不仅限于：根据给出的具有默认值，约束和触发器的一组语句判断DDL的OUTPUT值；了解并制定最好的SQL语句解决普通需求；使用OUTPUT子句。

INSERT子句

测试表：

create table Test
(
ID int identity(1,1),
Name varchar(100) default('Hui Li'),
Name2 as '计算列 '+Name,
RN rowversion,

UI uniqueidentifier,
Note varchar(100) NULL
)

@@ROWCOUNT

@@ROWCOUNT返回插入的行数。

插入单行数据

insert into test(name,note) values('jack','test')

注意，列顺序要匹配。上面语句等同于：

insert into test(note,name) values('test','jack')

如果不指定列名，则相当于指定所有列：

INSERT INTO TEST VALUES...相当于INSERT INTO TEST(ID,Name,RN,UI,Note) VALUES...

插入多行数据

insert into test(name,note) values('jack','test'),('lily','test2')

或者

insert into test(name,note) select 'jack','test' union all select 'lily','test2'

默认值列

insert into test(note) values('test4')由于没有提定Name，则插入默认值Hui Li。

语句INSERT INTO TEST DEFAULT VALUES将入一行默认值列，没有默认值的列将插入NULL。

注意，uniqueidentifier列有所不同，不会像标识列，rowversion列插入默认值，而是插入NULL。

Timestamp时间戳列不能指定值插入

insert into test(RN) values('test4')则出错。

计算列不能指定值插入

insert into test(name2) values('test4')则出错。

插入数据到标识列

SET IDENTITY_INSERT test ON
insert into test(id,name)values(10,'test10')
SET IDENTITY_INSERT test OFF

插入后，下次INSERT操作将以10为基数计算标识列。

使用NEWID()插入GUID到uniqueidentifier列

INSERT INTO Test(UI) VALUES(NEWID())

使用SELECT和EXECUTE插入他表数据

INSERT INTO 表名(列1,列2) SELECT ...FROM 表名，注意列类型的匹配。

INSERT INTO 表名 (列1,列2) EXECUTE 存储过程

INSERT INTO 表名 (列1,列2) EXECUTE('SELECT语句');

使用 TOP 限制从源表插入的数据

INSERT TOP(5) INTO 表名1 SELECT...FROM 表名2，将随机从表2中抽取5条数据插入到表1。

如果想插入排序的前5条数据：

INSERT INTO 表名1 SELECT TOP(5)... FROM 表名2 ORDER BY ....

通过使用 OPENQUERY 函数向远程表插入数据

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');

通过使用 OPENDATASOURCE 函数向远程表插入数据

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');

将数据插入堆中并按最小方式记录日志

ALTER DATABASE AdventureWorks2012
SET RECOVERY BULK_LOGGED;
GO
INSERT INTO....
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

将 OPENROWSET 函数与 BULK 一起使用来将数据大容量加载到表中

INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

使用 TABLOCK 提示指定锁定方法

INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

使用OUTPUT捕获 INSERT 语句的结果

使用变量

DECLARE @TMP table(id int,name varchar(100),name2 varchar(100))
INSERT INTO Test(Name,UI,Note)
OUTPUT inserted.ID,inserted.Name,inserted.Name2 INTO @TMP
VALUES('Microsoft Exam',NEWID(),'test note')
select * from @TMP

或者不使用变量
INSERT INTO Test(Name,UI,Note)
OUTPUT inserted.ID,inserted.Name,inserted.Name2
VALUES('Microsoft Exam',NEWID(),'test note')

参考：http://msdn.microsoft.com/zh-cn/library/ms174335(v=sql.110).aspx

BULK INSERT 参考：http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

UPDATE子句

@@ROWCOUNT返回影响行数。

更新 text、ntext 和 image 列

使用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化，向其列分配有效的文本指针，并且分配至少一个数据页（除非使用 NULL 更新该列）。  
若要替换或修改大型 text、ntext 或 image 数据块，请使用 WRITETEXT 或 UPDATETEXT，而不使用 UPDATE 语句。  
如果 UPDATE 语句在更新聚集键以及一个或者多个 text、 ntext 或 image 列时可以更改多个行，则对这些列的部分更新将作为替换所有值来执行。

在 Microsoft SQL Server 的未来版本中，将删除 ntext、text 和 image 数据类型。  请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。  请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。

更新大值数据类型

使用 .WRITE (expression, @Offset,@Length) 子句执行对 varchar(max)、nvarchar(max) 和 varbinary(max) 等数据类型的部分或完整更新。  例如，对 varchar(max) 列的部分更新可能只删除或修改该列的前 200 个字符，而完整更新则删除或修改该列中的所有数据。  .如果将数据库恢复模式设置为大容量日志模式或简单模式，则对插入或追加新数据的 WRITE 更新进行最小日志记录。  在更新现有值时，不使用最小日志记录。  有关详细信息，请参阅事务日志 (SQL Server)。  
当 UPDATE 语句导致下列任一操作时，数据库引擎便会将部分更新转换为完整更新：  
•更改分区视图或表的键列。  
•修改多行并且还将非唯一的聚集索引的键更新为非常量值。  
不能使用 .WRITE 子句更新 NULL 列或将 column_name 的值设置为 NULL。  
对于 varbinary 和 varchar 数据类型，以字节为单位指定 @Offset 和 @Length；对于 nvarchar 数据类型，则以字符为单位进行指定。  已针对双字节字符集 (DBCS) 排序规则计算了适当的偏移量。  
为了获得最佳性能，建议按照块区大小为 8040 字节倍数的方式插入或更新数据。  
如果在 OUTPUT 子句中引用了由 .WRITE 子句修改的列，则该列的完整值（deleted.column_name 中的前像或 inserted.column_name 中的后像）都返回到表变量中的指定列。  
若要针对其他字符或二进制数据类型获得相同的 .WRITE 功能，请使用 STUFF (Transact-SQL)。 

锁定行为

UPDATE 语句总是在其修改的表上获取排他 (X) 锁并在事务完成之前持有该锁。 有了排他锁，其他事务都不可以修改数据。 您可以指定表提示，以便通过指定其他锁定方法来覆盖 UPDATE 语句的持续时间的这一默认行为，但只建议经验丰富的开发人员和数据库管理员将提示用作最后的手段来执行。 有关详细信息，请参阅表提示 (Transact-SQL)。

基本UPDATE语句

更新所有行 UPDATE 表名 SET 列名=值

更新多列所有行 UPDATE 表名 SET 列名1=值1,列名2=值2

更新指定行 UPDATE 表名 SET 列名=值 WHERE...

与JOIN一起使用 UPDATE 表名1 SET 列名=值 FROM 表名1 JOIN 表名2 ON...

随机更新5行 UPDATE TOP(5) 表名 SET 列名=值

更新前5行 UPDATE 表名 SET 列名=值 WHERE ID IN(SELECT TOP(5) ID FROM 表名 ORDER BY ID)这里假设ID为主键

SET指定子查询 UPDATE 表名 SET 列名=(SELECT...)

用默认值更新UPDATE 表名 SET 列名= DEFAULT

更新视图只能偿试更新一个基表中的列

使用 WHERE CURRENT OF 子句

以下示例使用 WHERE CURRENT OF 子句来只更新游标位于其上的行。 如果游标基于某个联接，则只修改 UPDATE 语句中指定的 table_name。 其他参与该游标的表不会受到影响。

DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

使用OUTPUT

UPDATE TEST SET NAME='JACKKKKKKKKKKKKKKKKK' 
OUTPUT inserted.Name,deleted.Name
WHERE ID=1 

也可以如同INSERT示例一样，将OUTPUT值插入变量。

更新大型对象数据类型

使用包含 .WRITE 的 UPDATE 来修改 nvarchar(max) 列中的数据

UPDATE Document
SET DocumentSummary .WRITE (N'features',28,10)

28是DocumentSummary字段的开始位置，10是要替换的字符长度。

使用包含 .WRITE 的 UPDATE 在 nvarchar(max) 列中添加和删除数据

-- 不能使用 .WRITE 子句修改 NULL 列，因此先使用临时数据填充该列.
UPDATE Document
SET DocumentSummary = N'替换NULL'
WHERE ...;


-- 再用.WRITE替换值。方法： 开始位置0，长度设为NULL 
UPDATE Document
SET DocumentSummary .WRITE(N'新值',0,NULL)
WHERE ...;


-- 追加数据
-- 方法：开始位置设为 NULL，长度设为0.
UPDATE Document
SET DocumentSummary .WRITE (N'追加数据', NULL, 0)
WHERE ...;


-- 删除从指定位置开始的所有字符
-- 方法：值设为NULL，长度设为0. 
UPDATE Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE ...;

从位置56开始的字符将被全部删除


-- 删除指定段数据.
UPDATE Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE ...;

从位9到21的数据将被删除

使用包含 OPENROWSET 的 UPDATE 修改 varbinary(max) 列

UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
    SELECT *
    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB) AS x )
WHERE ProductPhotoID = 1;

使用 UPDATE 来修改 FILESTREAM 数据

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

通过使用提示覆盖查询优化器的默认行为

指定表提示

UPDATE Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ...;

指定查询提示

以下示例在 UPDATE 语句中指定查询提示OPTIMIZE FOR (@variable)。 此提示指示查询优化器在编译和优化查询时对局部变量使用特定值。 仅在查询优化期间使用该值，在查询执行期间不使用该值。

CREATE PROCEDURE Production.uspProductUpdate
@Product nvarchar(25)
AS
SET NOCOUNT ON;
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE @Product
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );
GO
-- Execute the stored procedure 
EXEC Production.uspProductUpdate 'BK-%';

Delete语句

Delete FROM 表名 WHERE...

对于TOP，OUTPUT，关联表，游标的使用况与上面类似。

参考：

http://msdn.microsoft.com/zh-cn/library/ms174335(v=sql.110).aspx

http://msdn.microsoft.com/zh-cn/library/ms177523(v=sql.110).aspx

http://msdn.microsoft.com/zh-cn/library/ms189835(v=sql.110).aspx