第八章 数据修改(5)

use tempdb;
go
--1-1 在tempdb中创建Customers表
if OBJECT_ID('dbo.customers','u') is not null drop table dbo.customers;
create table dbo.customers
(custid int not null primary key,
companyname nvarchar(40) not null,
country nvarchar(15) not null,
region nvarchar(15) null,
city nvarchar(15) not null);

--1-2 向customers表中插入一行数据
insert into dbo.customers(custid, companyname, country, region, city)
values(100, N'Company ABCDE', N'USA', N'WA', N'Redmond');

--1-3 将Sales.Customers表中所有下过订单的客户插入tempdb数据库的Customers表。
insert into dbo.customers 
select distinct c.custid, c.companyname, c.country, c.region, c.city 
from tsqlfundamentals2008.sales.customers as c, tsqlfundamentals2008.sales.orders as o
where c.custid=o.custid;

--1-4 用select into创建一个orders表，将2006～2008年之间的订单填充到这个表
if OBJECT_ID('dbo.orders', 'u') is not null drop table dbo.orders;
go
select * into dbo.orders from TSQLFundamentals2008.Sales.Orders
where orderdate between '20060101' and '20081231';

--2 删除2006年8月之前的订单，使用output子句返回被删除订单的orderid和orderdate列。
delete from dbo.orders
output deleted.orderid, deleted.orderdate
where orderdate < N'20060801'

--3 删除来自Brazil的客户下过的订单
delete from o
from dbo.orders as o
join dbo.customers as c
on c.custid=o.custid 
and c.country = N'Brazil'

--4-1 运行以下对customers表的查询
select * from customers;

--4-2 更新customers表,将所有值为null的region列设置为"<None>",使用output子句显示custid、region列原来的值以及region列新的值
update customers
set region = N'<None>'
output inserted.custid, deleted.region as oldregion, inserted.region as newregion
where region is null;

--5 更新来自UK的客户所下的所有订单，将订单的shipcountry,shipregion以及shipcity列设置成所属客户的country,region及city列上的取值
update o
set o.shipcountry = c.country, o.shipregion = c.region, o.shipcity = c.city
from dbo.orders as o
join dbo.customers as c
on o.custid = c.custid
and c.country = N'UK';