Microsoft SQL Server 2008技术内幕：T-SQL语言基础（数据修改）

八、数据修改

8.1 插入数据

8.1.1 insert values

• 可一次插入多条数据
  • 属于原子操作
    • 同时成功或同时失败

insert into dbo.orders
(orderid,orderdate,empid,custid)
values
	(10003,'20090213',4,'B'),
	(10004,'20090214',1,'A'),
	(10005,'20090215',1,'C');

• 表值构造函数
  • 表表达式

（values
	(10003,'20090213',4,'B'),
	(10004,'20090214',1,'A'),
	(10005,'20090215',1,'C')) 
	as temp(orderid,orderdate,empid,custid);

8.1.2 insert select

• 将select的查询结果插入目标表

insert into dbo.orders
(orderid,orderdate,empid,custid)
select orderid,orderdate,empid,custid
from sales.orders
where shipcountry = 'UK';

8.1.3 insert exec

• 将存储过程或动态SQL批处理返回的结果插入目标表

insert into dbo.orders
(orderid,orderdate,empid,custid)
exec sales.usp_getorders @country = 'France';

8.1.4 select into

• 创建一个新表，并用select的查询结果填充该表
  • 会复制来源表的基本结构
    • 列名、数据类型、是否允许为null及identity
  • 不会复制约束、索引及触发器

select country,region,city
into dbo.locations
from sales.customers
except
select country,region,city
from hr.employees;

8.1.5 bulk insert

• 将文件中的数据导入一个已经存在的表

bulk insert dbo.orders from 'c:\temp\orders.txt'
	with
	(
		datafiletype='char',
		fieldterminator=',',  /*字段终止符*/
		rowterminator = '\n'
	};

• orders.txt
  

8.1.6 identity属性

• 自增标识
  • 不需要指定值，可以根据种子值、步长进行自增
• 创建identity标识列

use tempdb;
if object_id('dbo.t1','U') is not null drop table dbo.t1;
create table dbo.t1
(
	keycol int not null identity(1,1)
		constraint PK_t1 primary key,
	datacol varchar(10) not null
		constraint CHK_datacol
		check(datacol like '[A-Za-z]%')
)

• 查询标识列
  • $identity代表自增列
  • select $identity from dbo.t1;
• 获取插入数据时自动生成的标识值
  • @@identity
    • 从以前版本遗留下的功能
    • 返回会话最后生成的一个标识值，而不考虑作用域
  • scope_identity()
    • 返回当前作用域内会话生成的最后一个标识值
  • 对于一次插入多条数据的情况，只能返回最后一个标识值
• 获取表中当前标识值
  • ident_current(tableName)
• 特点
  • identity属性即使当前插入操作发生错误也会自增
  • 不能在现有的列上增加或删除identity属性
    • 只能在新建表或者新增列时设置identity属性
  • identity属性不会检查值的唯一性
    • 因为可以显式指定identity属性值
      • set identity_insert tableName on;
      • set identity_insert tableName off;
    • 需要手动添加unique或primary key约束

8.2 删除数据

8.2.1 delete语句

• ANSI
• 只有两个子句
  • from tableName
  • where condition
• 显示删除行数
  • set nocount off（默认）;

8.2.2 truncate语句

• T-sql
• truncate table tableName;
• 作用
  • 删除表中所有行
• 和delete的区别
  • truncate速度快、性能好
  • truncate会重置identity属性的种子值，而delete不会

8.2.3 基于联接的delete

• 根据对另一个表中相关行的属性定义的过滤器来删除本表中的数据行
• 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';

• ANSI-SQL实现
  • 基于子查询的delete

delete from dbo.orders
where exists
	(select * 
	from dbo.customers as c
	where orders.custid=c.custid
	and c.country = N'USA');

8.3 更新数据

8.3.1 update语句

update dbo.t1
	set col1 = col1 + 10,
		col2 = col1 + 10
where col1=100;

• 同时操作
  • 结果：col1 = 110，col2 = 110

8.3.2 基于联接的update

• T-SQL
• 联接的作用
  • 过滤
    • 根据另一个表中相关行的属性值进行过滤
  • 作为赋值来源
    • 通过另一个表中的值对本表进行赋值操作

8.3.2.1 过滤

• T-SQL实现

update od set discount += 0.05
from dbo.orderdetails as od
	join dbo.orders as o
	on od.orderid = o.orderid
where custid = 1;

• ANSI实现

update dbo.orderdetails set discount += 0.05
where exists
	(select * 
	from dbo.orders as o
	where o.orderid = orderdetails.orderid
	and custid = 1);

8.3.2.2 作为赋值来源

• T-SQL实现

update T1
	set col1 = T2.col1,
		col2 = T2.col2,
		col3 = T2.col3
from dbo.T1 join dbo.T2
	on T1.keycol = T2.keycol
where T2.col4 = 'ABC';

• ANSI实现

update dbo.T1
	set col1 = (select col1
				from dbo.T2
				where T2.keycol = T1.keycol),
	set col2 = (select col2
				from dbo.T2
				where T2.keycol = T1.keycol),
	set 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');

• SQL Server2008中尚未实现的行构造函数-ANSI

update dbo.T1
	set (col1,col2,col3) = (select col1,col2,col3
				from dbo.T2
				where T2.keycol = T1.keycol)
where exists
	(select * 
	from dbo.T2
	where T2.keycol = T1.keycol
	and T2.col4 = 'ABC');

8.3.3 赋值update

• T-SQL
• 在对表中的数据进行更新的同时为变量赋值
  • 原子操作

declare @nextval as int;
update sequence set @nextval = val = val +1;

• 常用场景
  • 自增标识列无法满足需求，需要维护自定义编号机制
  • 自定义编号机制中
    • 将当前编号保存在一个表中
    • 当需要插入新数据时
      • 在插入之前，先更新当前编号 +1
      • 同时获取更新后的当前编号作为新数据的编号进行插入操作

8.4 合并数据

• ANSI
• 作用
  • 在一条语句中根据逻辑条件对数据进行不同的修改操作
    • insert、update、delete
• 例如，用来源表的数据更新目标表
  • 对目标表中已经存在了的数据，执行更新操作
    • 也可以增加逻辑条件
      • 当数据不一致时才执行更新操作
  • 对目标表中尚不存在的数据，执行插入操作
  • 对来源表中不存在但在目标表中存在的数据，执行删除操作
    • T-SQL

merge into dbo.customers as tgt
using dbo.customersstage as src
	on tgt.custid = src.custid
when matched and
		(tgt.companyname <> src.companyname or
		tgt.phone <> src.phone or
		tgt.address <> src.address) 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)
when not matched by source then
	delete;

8.5 通过表表达式修改数据

• 表表达式虽然不是实际存在的表，但对表表达式的结果进行修改，能转化为对基础表的数据的修改

8.5.1 转化为基础表的数据修改的条件

• 如果表表达式需要联接多表，则同一修改语句中的修改只能影响联接的一边，不能同时影响两边
• 不能对作为计算结果的列进行更新
• 当需要通过表表达式插入新行时，如果部分列没有赋值且没有默认值，则无法成功

update od set discount = discount + 0.05
from dbo.orderdetails as od
	join dbo.orders as o
	on od.orderid = o.orderid
	where custid = 1

8.5.2 简化调试

• 对于在更新之前想先看看update语句将要修改哪些行的情况
  • 如果不使用表表达式
    • 则需要先将update改为select
    • 查询出将要修改的数据行，再切换回update
  • 使用表表达式进行update
    • 不需要在select和update之间来回切换

with c as
(
	select custid,od.orderid,productid,discount
	from dbo.orderdetails as od
	join dbo.orders as o
	on od.orderid = o.orderid
	where custid = 1
)
update c set discount = discount + 0.05;

8.5.3 避开限制

• 例如，在update语句中不允许使用row_number()函数
• 使用表表达式，可以先生成row_number()，再将结果作为数据来源进行赋值修改

with c as
(
	select col1,col2
		,row_number() over(order by col1) as rownum
	from dbo.t1
)
update c set col2 = rownum;

8.6 带有top选项的数据更新

• 在insert、update、delete以及merge语句中允许使用top选项
  • 当处理的行数达到指定数据或百分比时，将停止执行修改语句
    • 例如，update top(50) dbo.orders set freight = freight + 10.00;
  • 但无法保证修改的顺序
    • 因为无法为修改语句指定order by
• 可以借助表表达式，先将需要修改的数据行查询出来再进行修改

with c as
(
	select top(50) *
	from dbo.orders
	order by orderid desc
)
update c set freight +=10.00;

8.7 output子句

• 在执行修改的同时，返回修改前和修改后的值
• 指定output子句的语法
  • 类似select子句
  • 列举出需要返回的列名
  • 不同之处
    • 对于insert语句需要引用 inserted 关键字
    • 对于delete语句需要引用 deleted 关键字
    • 对于update语句，更新前的数据需要引用 deleted 关键字，更新后的需要引用 inserted 关键字
• 将output的结果导入到另一个表或变量中
  • 在output子句后使用into子句
• 可以同时指定多个output子句

8.7.1 带有output的insert语句

• 将插入后生成的identity列和数据导出到表变量中

declare @newrows table(keycol int,datacol nvarchar(40));
insert into dbo.t1(datacol)
output inserted.keycol,inserted.datecol
into @newrows
	select lastname
	from hr.employees
	where country = 'UK';

8.7.2 带有output的delete语句

delete from dbo.orders
output
	deleted.orderid,
	deleted.orderdate,
	deleted.empid,
	deleted.custid
where orderdate < '20080101';

8.7.3 带有output的update语句

update dbo.orderdetails
 set discount += 0.05
output 
	inserted.productid,
	deleted.discount as olddiscount,
	inserted.discount as newdiscount
where productid = 51;

8.7.4 带有output的merge语句

• $action
  • 返回代表相应操作的字符串，如“update"

merge into dbo.customers as tgt
using dbo.customersstage as src
	on tgt.custid = src.custid
when matched and
		(tgt.companyname <> src.companyname or
		tgt.phone <> src.phone or
		tgt.address <> src.address) 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,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;

8.7.5 可组合的DML

• 应用场景
  • 将修改过的数据行的子集导入一个表中
  • 例如，更新由供应商1提供的所有产品，将其价格提高15%，但需要审核那些被更新的产品的原有价格和新价格
    • 只审核原价低于20，而新价格高于或等于20的产品
• 将output返回的结果作为数据来源进行操作

insert into dbo.productsaudit(productid,colname
	,oldval,newval)
select productid,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;