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

nainai1

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

• Create Database Objects创建数据库对象 (24%)
• Work with Data数据处理 (27%)
• Modify Data数据修改 (24%)
• 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 语句导致下列任一操作时，数据库引擎便会将部分更新转换为完整更新：  
&#8226;更改分区视图或表的键列。  
&#8226;修改多行并且还将非唯一的聚集索引的键更新为非常量值。  
不能使用 .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;



使用OUTPUTUPDATE TEST SET NAME='JACKKKKKKKKKKKKKKKKK'
OUTPUT inserted.Name,deleted.Name
WHERE ID=1

也可以如同INSERT示例一样，将OUTPUT值插入变量。
更新大型对象数据类型使用包含 .WRITE 的 UPDATE 来修改 nvarchar(max) 列中的数据UPDATE DocumentSET 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.ProductPhotoSET ThumbNailPhoto = (    SELECT *    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB) AS x )WHERE ProductPhotoID = 1;使用 UPDATE 来修改 FILESTREAM 数据
UPDATE Archive.dbo.RecordsSET [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)ASSET NOCOUNT ON;UPDATE Production.ProductSET ListPrice = ListPrice * 1.10WHERE ProductNumber LIKE @ProductOPTION (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

darkpoon

学习了，不错，讲的太有道理了

吸毒的虫子

禽兽尚且有半点怜悯之心，而我一点也没有，所以我不是禽兽。

虚幻0516

不在放荡中变坏，就在沉默中变态！

座机

只有假货是真的，别的都是假的!

yanglgzh

如果跟导师讲不清楚，那么就把他搞胡涂吧!

nawawa001

如果恐龙是人，那人是什么？