第八章 数据修改(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 owhere c.custid=o.custid;--1-4 用select into创建一个orders表，将2006～2008年之间的订单填充到这个表if OBJECT_ID('dbo.orders', 'u') is not null drop table dbo.orders;goselect * into dbo.orders from TSQLFundamentals2008.Sales.Orderswhere orderdate between '20060101' and '20081231';--2 删除2006年8月之前的订单，使用output子句返回被删除订单的orderid和orderdate列。delete from dbo.ordersoutput deleted.orderid, deleted.orderdatewhere orderdate < N'20060801'--3 删除来自Brazil的客户下过的订单delete from ofrom dbo.orders as ojoin dbo.customers as con 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 customersset region = N'<None>'output inserted.custid, deleted.region as oldregion, inserted.region as newregionwhere region is null;--5 更新来自UK的客户所下的所有订单，将订单的shipcountry,shipregion以及shipcity列设置成所属客户的country,region及city列上的取值update oset o.shipcountry = c.country, o.shipregion = c.region, o.shipcity = c.cityfrom dbo.orders as ojoin dbo.customers as con o.custid = c.custidand c.country = N'UK';