第八章 数据修改

use tempdb;

if object_id('dbo.orders', 'U') is not null

drop table dbo.orders;

go

create table dbo.orders

(orderid int not null

constraint PK_Orders primary key,

orderdate date not null

constraint dft_orderdate default(current_timestamp),

empid int not null,

custid varchar(10) not null

);

insert into dbo.orders(orderid, orderdate, empid, custid)

values

(1001, '20090212', 3, 'A');

insert into dbo.orders(orderid, empid, custid)

values

(1002, 3, 'B');

insert into dbo.orders(orderid, orderdate, empid, custid)

values

(1003, '20090213', 4, 'B'),

(1004, '20090214', 1, 'A'),

(1005, '20090215', 1, 'C'),

(1006, '20090215', 3, 'C');

select * from

(values

(1003, '20090213', 4, 'B'),

(1004, '20090214', 1, 'A'),

(1005, '20090215', 1, 'C'),

(1006, '20090215', 3, 'C'))

as o(orderid, orderdate, empid, custid);

use tempdb;

insert into dbo.orders(orderid, orderdate, empid, custid)

select orderid, orderdate, empid, custid

from tsql2012.sales.orders

where shipcountry = 'UK';

insert into dbo.orders(orderid, orderdate, empid, custid)

select 10007, '20090215', 2, 'B' union all

select 10008, '20090215', 1, 'C' union all

select 10009, '20090216', 2, 'C' union all

select 10010, '20090216', 3, 'A';

use tsql2012;

if object_id('Sales.usp_getorders', 'P') is not null

drop proc sales.usp_getorders;

go

create proc sales.usp_getorders

@country as nvarchar(40)

as

select orderid, orderdate, empid, custid

from sales.orders

where shipcountry = @country;

go

use tempdb;

insert into dbo.orders(orderid, orderdate, empid, custid)

exec tsql2012.sales.usp_getorders @country = 'France';

use tempdb;

if object_id('dbo.orders', 'U') is not null

drop table dbo.orders;

select orderid, orderdate, empid, custid

into dbo.orders

from tsql2012.sales.orders;

use tempdb;

if object_id('dbo.locations', 'U') is not null

drop table dbo.locations

select country, region, city

into dbo.locations

from tsql2012.sales.customers

except

select country, region, city

from tsql2012.hr.employees;

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_K1 primary key,

datacol varchar(10) not null

constraint chk_t1_datacol check(datacol like '[A-Za-z]%'));

insert into dbo.t1 values('A');

declare @new_key as int;

set @new_key = scope_identity();

select @new_key as new_key;

use tempdb;

if object_id('dbo.orders', 'U') is not null

drop table.dbo.orders;

if object_id('dbo.customers', 'U') is not null

drop table dbo.customers;

select * into dbo.orders from tsql2012.sales.orders;

select * into dbo.customers from tsql2012.sales.customers;

alter table dbo.customers add

constraint PK_Customers primary key(custid);

alter table dbo.orders add

constraint PK_Orders primary key(orderid);

alter table dbo.orders add

constraint FK_Orders foreign key(custid)

references dbo.customers(custid);

use tempdb;

delete from dbo.orders

where orderdate < '20070101';

use tempdb;

delete from o

from dbo.orders as o

join dbo.customers as c

on o.custid = c.custid

where c.country = N'USA';

delete from dbo.orders

where exists

(select * from dbo.customers as c

where orders.custid = c.custid

and c.country = 'USA');

use tempdb;

if object_id('dbo.orderdetails', 'U') is not null

drop table dbo.orderdetails;

if object_id('dbo.orders', 'U') is not null

drop table dbo.orders;

select * into dbo.orders from tsql2012.sales.orders;

select * into dbo.orderdetails from tsql2012.sales.orderdetails;

alter table dbo.orders add

constraint pk_orders primary key(orderid);

alter table dbo.orderdetails add

constraint pk_orderdetails primary key(orderid, productid),

constraint fk_orderdetails foreign key(orderid)

references dbo.orders(orderid);

use tempdb;

update dbo.orderdetails

set discount = discount + 0.05

where productid = 51;

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;

update dbo.orderdetails

set discount = discount + 0.05

where exists

(select * from dbo.orders as o

where o.orderid = orderdetails.orderid

and custid = 1);

use tempdb;

if object_id('dbo.sequence', 'U') IS NOT NULL

DROP TABLE DBO.sequence;

create table dbo.sequence

(val int not null);

insert into dbo.sequence values(0);

declare @nextval as int;

update dbo.sequence set @nextval = val + 1;

select @nextval;

use tempdb;

update od

set discount = discount + 0.05

from dbo.orderdetails as od

join dbo.orders as o

on o.orderid = od.orderid

where custid = 1;

with c as

(select custid, od.orderid, productid, discount, discount + 0.05 as newdiscount

from dbo.orderdetails as od

join dbo.orders as o

on od.orderid = o.orderid

where custid = 1

)update c set discount = newdiscount;

update d

set discount = newdiscount

from (select custid, od.orderid, productid, discount, discount + 0.05 as newdiscount

from dbo.orderdetails as od

join dbo.orders as o

on od.orderid = o.orderid

where custid = 1) as d;

use tempdb;

if object_id('dbo.t1', 'U') is not null

drop table dbo.t1;

create table dbo.t1(col1 int, col2 int);

go

insert into dbo.t1(col1) values(10);

insert into dbo.t1(col1) values(20);

insert into dbo.t1(col1) values(30);

select * from dbo.t1;

with c as

(select col1, col2,row_number() over(order by col1) as rownum

from dbo.t1 as t)

update c

set col2 = c.rownum;

use tempdb;

if object_id('dbo.orderdetails', 'U') is not null

drop table dbo.orderdetails;

if object_id('dbo.orders', 'U') is not null

drop table dbo.orders;

select * into dbo.orders from tsql2012.sales.orders;

delete top(50) from dbo.orders;

update top(50) dbo.orders

set freight = freight + 10.00;

with c as

(select top(50) * from dbo.orders

order by orderid)

delete from c;

with c as

(select top(50) *

from dbo.orders

order by orderid desc)

update c set freight = freight + 10.00;

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 nvarchar(50) not null);

insert into dbo.t1(datacol)

output inserted.keycol, inserted.datacol

select lastname

from tsql2012.hr.employees

where country = N'USA';

declare @newrows table(keycol int, datacol nvarchar(40));

insert into dbo.t1(datacol)

output inserted.keycol, inserted.datacol

into @newrows

select lastname

from tsql2012.hr.employees

where country = N'UK';

select * from @newrows;

use tempdb;

if object_id('dbo.orders', 'U') is not null

drop table dbo.orders;

select * into dbo.orders from tsql2012.sales.orders;

delete from dbo.orders

output

deleted.orderid,

deleted.orderdate,

deleted.empid,

deleted.custid

where orderdate < '20080101';