---数据库创建
USE [master]
GO
CREATE DATABASE [VCD_info] ON PRIMARY
( NAME = N'VCD_info', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCD_info.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'VCD_info_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VCD_info_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
---VCD信息表
create table VCD
(
Vno char(10)not null primary key,
Vname char(10) not null,
Actor char(10) null,
Price decimal(10,2) not null,
Vtype int not null,
amount int not null check(amount>=0)
)
create table Client
(
Cno char(10)not null primary key,
Cnum char(11) null,
Ctel char(11) null,
)
---客户信息表
create table Hire
(
Vno char(10) not null ,
Cno char(10)not null ,
Hprice decimal null,
Hamount int null,
Htime char(10) null,
Rtime char(10) null,
Vstate char(10)not null,
primary key(Vno,Cno),
foreign key (Vno)references VCD(Vno),
foreign key (Cno)references Client(Cno)
)
---零售信息表
create table Sell
(
Vno char(10)not null,
Stime char(10) not null,
Samount int not null,
primary key(Vno,Samount),
foreign key (Vno)references VCD(Vno)
)
---入库信息表
create table Stock
(
Vno char(10)not null,
In_time char(10)not null,
In_amount int not null,
primary key(Vno,In_amount),
foreign key (Vno)references VCD(Vno)
)
---客户编号索引
create index user_id
on Client(Cno)
---对VCD编号建索引
create index vcd_id
on VCD(Vno)
---对VCD名称建索引
Create index vcd_index
On VCD(Vname)
---客户信息视图
go
create view 用户
as
select * from Client
go
---查询各类VCD的库存情况
go
Create view V_type_number(A_ID,A_amount)
As
Select VCD.Vtype,sum(VCD.amount)
From VCD
Group by VCD.Vtype
go
---用户查询VCD信息视图
go
create view User_Query
as
select * from VCD
go
---管理员查询用户借阅信息视图
go
create view Manager_User(VCD编号,归还时间,归还状态,客户姓名,客户电话)
as
select Hire.Vno,Hire.Rtime,Hire.Vstate,Client.Cnum,Client.Ctel
from Hire, Client
where Hire.Cno=Client.Cno
go
---出租VCD时更新VCD数量信息
create trigger _Hire1 on Hire
for insert
as
update VCD set VCD.amount =VCD.amount-inserted.Hamount
from VCD,inserted
where VCD.Vno=inserted.Vno
---归还时自动修改VCD现货数
go
create trigger _Hire2 on Hire
for insert
as
update VCD set VCD.amount=VCD.amount+inserted.Hamount
from VCD,inserted
where VCD.Vno=inserted.Vno
go
---入库时自动更改库存
create trigger _Stock on Stock
for insert
as
update VCD set VCD.amount =VCD.amount+inserted.In_amount
from VCD,inserted
where VCD.Vno=inserted.Vno
---售出时自动更改现货量
go
create trigger _Sell on Sell
for insert
as
update VCD set VCD.amount=VCD.amount-inserted.Samount
from VCD,inserted
where VCD.Vno=inserted.Vno
go
---VCD编号不可修改其余参数可修改
Create Procedure Pro_VCD_change
@Vno_in char(10),
@Vname_out char(10),
@Actor_out char(10),
@Price_out decimal(10,2),
@Vtype_out char(10),
@Amount_out int
As
Begin
Update VCD set
Vname=@Vname_out,
Actor=@Actor_out,
Price=@Price_out,
Vtype=@Vtype_out,
amount=@Amount_out
Where Vno=@Vno_in
End
Go
---给所有VCD打九折
Create procedure Proc_TotalPrice
As
Begin
Update VCD set Price=Price*0.9
From VCD
End
---各类VCD的库存查询依据是使用名字查询
go
Create function selectscene( )
Returns int
As
Begin
Declare @Vtype int,@amount_out int,@Vname char(10)
Select @Vtype=Vtype
From VCD
Where Vname=@Vname
Select @Amount_out=sum(VCD.amount)
From VCD
Where Vtype=@Vtype
Return @Amount_out
End
Go
---设置借出归还销售的数量，将借出，归还，销售分别用0，1，2表示
Create function timescene(@begintime datetime,@endtime datetime,@flage int)
Returns int
As
Begin
Declare @countnumber int
set @countnumber=0
if @flage=0
begin
select @countnumber=@countnumber+Hamount
from Hire
where Htime<=@endtime and Htime>=@begintime
end
else if @flage=1
begin
select @countnumber=@countnumber+Hamount
from Hire
where Rtime<=@endtime and Rtime>=@begintime
end
else
begin
select @countnumber=@countnumber+Samount
from Sell
where Stime<=@endtime and Stime>=@begintime
end
return @countnumber
end
- 1
- 2
- 3
- 4
- 5
前往页