sqlserver 2005 简单总结、实例讲述
本帖最后由 dongfang1017 于 2009-7-7 17:04 编辑sql 简单总结、实例讲述（一）
创建数据库 create database sale;
创建四个基本表use sale
create table Customer(
CusNo
char (3) not null ,CusName
Char(10) not null ,Address
char(20) ,Tel
char(20)
);
create table Product(
ProNoChar (5) primary key,
ProName Char (20) not null,
Price Decimal(8,2)not null,
Stocks Decimal (8,0)
not null
);
create tableProln(
InputDate
DateTime not null ,ProNo
char (5)not null ,Quantity
Decimal (6,0)not null
);
create table ProOut(
SaleDate
DateTime not null ,CusNo
char (3)not null ,ProNo
Char (5) not null ,Quantity
Decimal (6,0)
not null
)
在表中插入多条数据
insert into Customer
select '001', '杨婷', '深圳', '0755-22221111' union
select '002', '陈萍', '深圳', '0755-22223333' union
select '003', '李东', '深圳', '0755-22225555' union
select'004','叶合', '广州', '020-22227777' union
select'005', '谭新', '广州', '020-22229999' ;
insert into Product
select '00001', '电视' ,'3000.00', '800' union
select '00002', '空调' ,'2000.00', '500' union
select '00003', '床' , '1000.00', '300' union
select '00004', '餐桌', '1500.00', '200' union
select '00005', '音响', '5000.00', '600' union
select '00006', '沙发', '6000.00', '100' ;
insert into Proln
select '2002-1-1', '00001' ,'10' union
select '2002-1-1', '00002' ,'5' union
select '2002-1-1', '00001' ,'5' union
select '2002-1-1', '00003' ,'10' union
select '2002-1-2', '00001' ,'10' union
select '2002-1-2', '00003' ,'20' union
select '2002-1-2', '00001' ,'10' union
select '2002-1-2', '00004' ,'30' union
select '2002-1-3', '00003', '20' ;
insert into ProOut
select '2002-1-1', '001', '00001', '10' union
select '2002-1-1', '001', '00002' ,'5' union
select '2002-1-1', '002', '00001', '5' union
select '2002-1-1', '002', '00003' ,'10' union
select '2002-1-2', '001', '00001', '10' union
select '2002-1-2', '001', '00003', '20' union
select '2002-1-2', '003', '00001' ,'10' union
select '2002-1-2', '003', '00004' ,'30' union
select '2002-1-3', '002', '00003' ,'20';
修改表 增加 约束
在ProOut表上设置CHECK约束，使数量列Quantity的值>=1
alter table ProOut
add constraint quanti check (Quantity>=1);
在ProOut表上设置DEFAULT约束，使数量列Quantity的默认值为1。
alter table ProOut
add constraint defaudefault('1') for Quantity;
在ProOut表上设置外键，保证录入时录入的产品代码都是在产品表中存在的。
alter table ProOut
add constraint pronumber foreign key(ProNo) references Product(ProNo);
创建视图
创建视图view2，要求能统计汇总每种产品的销售量和销售金额。
create view view2
as select
b.Proname 产品,sum(Quantity) 销售数量,sum(Quantity*b.Price) 总计
from ProOut a join Product b on a.ProNo=b.ProNo
group by b.Proname;
go
创建视图view1，要求显示销售日期、客户姓名、产品名称、单价、数量、金额
create view view1
as select a.saleDate 销售日期, b.CusName 客户姓名,c.proname产品名称, c.price 单价, a.Quantity 数量, c.price*a.Quantity 金额
fromProOut a join Product con a.prono=c.prono join Customer b on a.cusno=b.cusno;
go
创建视图view3，要求显示销售金额在10万元以下的产品清单
create view view3
as select a.SaleDate 销售日期,a.prono 产品编号,a.Quantity 销售数量,c.ProName 品名,
c.Price 单价,c.Stocks 库存数,b.InputDate 入库时间,b.Quantity 入库数量
fromProOut a join Product con a.prono=c.prono join proln b on a.prono=b.prono
where a.Quantity<100000;
创建索引
在Customer表上基于CusName列创建非惟一索引
create index cus
on customer(CusName);
在Product表上基于ProName列创建惟一索引
create unique index pronm
on product(ProName);
在ProOut表上基于SaleDate列创建非惟一索引。
create index saledt
on proout(SaleDate);
创建一默认，要求能够取得当前日期并将其绑定到ProOut表的SaleDate
create default timeing as getdate();
go
use sale
exec sp_bindefault 'timeing' ,'ProOut.SaleDate';
go
创建一规则限定只能录入大于0的数值，然后将其绑定到ProOut表的Quantity列。
create rule zero as @value>0;
go
exec sp_bindrule 'zero','ProOut.Quantity';
创建一自定义函数，要求能够利用该函数计算出金额（数量Quantity*单价Price）。
go
create function sum1(@price Decimal(8,2),@Quantity Decimal (6,0))
returns Decimal(8,2)
as
begin
declare @sum Decimal(8,2)
select @sum=@price*@Quantity
return @sum
end;
go
select dbo.sum1(10.00,10);
创建存储过程
创建存储过程produ，要求能够统计汇总每种产品的销售量和销售金额。
go
create procedure produ @name char(20)
as select
a.proname 名称,sum(b.Quantity) 数量 ,sum(a.price*b.Quantity) 金额
fromproduct a joinproout b on a.prono=b.prono
where a.proname=@name
group by a.proname;
go
execute produ '床'
创建存储过程produ2，要求能够根据指定的客户统计汇总该客户购买每种产品的销售量和销售金额
go
create procedure produ2 @cusname char(20)
as select
c.cusname 客户,a.proname 名称,sum(b.Quantity) 数量 ,sum(a.price*b.Quantity) 金额
fromproduct a joinproout b on a.prono=b.prono join Customer c
on b.CusNo=c.CusNo
where c.CusName=@cusname
group by c.cusname,a.proname;
go
execute produ2 '杨婷';
创建触发器
创建触发器，要求能够即时得到每种产品的库存数量
go
create trigger proout_insert
on proout
after insert
as
begin
declare @Stocks Decimal (6,0),@prono char(5),@Qua Decimal (6,0)
select @Stocks=Stocks,@Qua=Quantity from
product b join inserted a
on a.prono=b.prono
if @Stocks<@Qua
begin
print '此书库存量只有'
print @Stocks
print '而你要销售'
print @Qua
rollback transaction
end
else
begin
print '销售成功'
select @Qua=Quantity,@prono=prono from inserted
update product set Stocks=@Stocks-@Qua where
prono=@prono
end
end
select proname,stocks
from product;
go
insert into proout
values ('2006-1-1','001','00001','500');
go
create trigger proln_insert
on proln
after insert
as
begin
declare @Stocks Decimal (6,0),@prono char(5),@Qua Decimal (6,0)
select @Stocks=Stocks,@Qua=Quantity from
product b join inserted a
on a.prono=b.prono
print '此书库存量已有'
print @Stocks
print '现在入库'
print @Qua
print '总计库存量'
print @Qua+@Stocks
begin
select @Qua=Quantity,@prono=prono from inserted
update product set Stocks=@Stocks+@Qua where
prono=@prono
end
end
select proname,stocks
from product
go
insert into proln
values ('2002-1-1', '00001', '10');
创建存储过程，要求能显示销售量最大的产品代码、产品名称和销售量（使用游标的方式）。
-create procedure mount_product
as
declare @prono char(5),@Quantity Decimal (6,0),@max Decimal (6,0),@p char(5)
declare product_cursor cursor for
select prono ,sum(quantity)
from proout
group by prono
open product_cursor
fetch next from product_cursor into @prono,@quantity
set @max=0
while @@fetch_status=0
begin
if @max<@quantity
begin
set @max=@quantity
set @p=@prono
end
fetch next from product_cursor into @prono,@quantity
end
close product_cursor
deallocate product_cursor
select a.prono 编号,proname 名称,sum(quantity) 销售总量
from proout a join product b on a.prono=b.prono
where a.prono=@p
group by a.prono ,proname
go
execute mount_product；
go
一般情部下，有足够的库存时才允许销售该产品。创建一事务当向ProOut表插入新记录时，如果库存数大于销售数量，则允许销售，否则拒绝销售。
declare
@error_sum int
begin transaction
set @error_sum=0
begin try
insert into proout(SaleDate,CusNo
,ProNo
,Quantity
)
values('2006-1-1','001','00001','100')
end try
begin catch
set @error_sum=1
print '销售量大于库存量 !!销售不成功!!!'
begin transaction
end catch
if @error_sum<>0
rollback
else
begin
commit
print ' 销售成功 !!!'
end
select * from product 在补充下
备份数据库到本地 文件夹下' e:\sale命名为1.bak
backup database sale to disk='e:\sale\1.bak'
--use master
--drop database sale
--create database sale
恢复数据库、
use master
restore database sale from disk='e:\sale\1.bak'
with file=1,
replace
页:
[1]