2005级信管专业2班数据库应用系统课程设计课程论文
附录4 SQL语句
4.1 建立数据表SQL语句
（1）顾客基本信息表的建立： create table guest
( Gno char(20) not null, Gname char(20)not null, Gsex char(20) not null, Gid char(18) unique not null, Gtel char(11), Gaddress char(20), Account float, Grade int,
discount float not null, balance float, primary key (Gno),
check (Account >= 0.0 and Grade>0)
)
（2）客房基本信息表的建立： create table Roominfo
( Rno char(10), Rtype char(20)not null, Rprice float not null, Rfloor smallint not null,
Toward char(10)not null, primary key (Rno),
check (Rfloor between 1 and 100),
check (Toward in('正北','正南','正西','正东','东北','西南','西北','东南')), check (Rtype in('标准1','标准2','豪华1','豪华2','高级1','高级2')), )
（3）房态表的建立： create table RoomState
(Rno char(10), Gno char(20), Atime datetime , Ltime datetime,
27
2005级信管专业2班数据库应用系统课程设计课程论文
Rtime datetime, Rltime datetime, IntoPrice float , Days int , Stime datetime, flag char(1) ,
primary key (Rno,Gno),
foreign key (Rno)references Roominfo(Rno), foreign key (Gno)references guest(Gno), check (flag in('1','2','3')),
)
（4）娱乐项目基本信息表的建立： create table Atariff
( Atno char(20), Atname char(20)not null, Atprice float not null, primary key (Atno), check (Atprice >0.0) )
（5）顾客娱乐消费信息表的建立： create table Consumelist
( Gno char(20), Atno char(20), Amount float,
Wtime datetime not null, primary key(Gno,Atno),
foreign key (Gno)references guest(Gno), foreign key (Atno)references Atariff(Atno) )
（6）客房物品基本信息表的建立： create table RoGoInfo
( Goodsno char(20),
Goodsname char(20)not null, Oprice float not null, Dmultiple float not null, primary key (Goodsno) )
（7）顾客赔偿物品信息表的建立： create table GoAmInfo
(Gno char(20),
28
2005级信管专业2班数据库应用系统课程设计课程论文
Rno char(10), Goodsno char(20), Dnum int ,
Amendstime datetime not null, primary key(Gno,Rno,Goodsno), foreign key (Gno)references guest(Gno), foreign key(Rno)references Roominfo(Rno), foreign key(Goodsno)references RoGoInfo(Goodsno) )
4.2建立视图SQL语句
（1）用于查询预订房信息的视图定义如下：
create view
BookView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime) as select
RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime from Roominfo,RoomState,guest
where flag='1' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno
（2）用于查询已入住房的试图如下：
create view
IntoView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account) as select
RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account from Roominfo,RoomState,guest
where flag='2' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno
（3）用于查询空房的视图定义如下：
create view
EmRoView(Rno,Rtype,Rprice,Rfloor,Toward) as
select Rno,Rtype,Rprice,Rfloor,Toward from Roominfo
where Rno not in (select Rno From RoomState)
29
2005级信管专业2班数据库应用系统课程设计课程论文
4.3建立存储过程SQL语句
1.lsqSearchDate的定义
create procedure lsqSearchDate @date datetime as
select Atno,sum(Amount) from Consumelist where Wtime=@date group by Atno
2.lsqSearchEmpty的定义
create procedure lsqSearchEmpty @floor int as
select Rno,Rtype,Rprice,Rfloor,Toward from EmRoView where Rfloor=@floor lsqSearchEmpty 2
3.lsqWatchGuest的定义 create procedure lsqWatchGuest as
select Gno,Gname,Gsex,Gid from guest
4.lsqSearchGuest的定义
create procedure lsqSearchGuest @Gno char(20) as begin
select Gno,Gname,Account,balance from guest where Gno=@Gno
select RoomState.Rno,Rtype,IntoPrice from RoomState,Roominfo
where RoomState.Gno=@Gno and RoomState.Rno=Roominfo.Rno select c.Atno,Atname,Amount, Amount*Atprice AmuMoney,Wtime from Consumelist c,Atariff a
where c.Gno=@Gno and c.Atno=a.Atno
30
2005级信管专业2班数据库应用系统课程设计课程论文
selectg.Rno,r.Goodsname,g.Dnum,r.Oprice,r.Dmultiple,Oprice*g.Dnum*r.Dmultiple AmendMoney,g.Amendstime from GoAmInfo g,RoGoInfo r
where g.Gno=@Gno and g.Goodsno=r.Goodsno end
5.lsqConsumeList的定义
CREATE PROCEDURE lsqConsumeList @Consumelist_Gno char(20), @Consumelist_Atno char(20), @Consumelist_Amount float, @Consumelist_wtime datetime as insert
into Consumelist
values(@Consumelist_Gno,@Consumelist_Atno , @Consumelist_Amount ,@Consumelist_wtime ) lsqConsumeList 'G00004','A-KTV-M',2,'2007-1-5' 6.lsqAddRoomGoods的定义 create procedure lsqAddRoomGoods @GDnumber char(20), @GDname char(20), @GDprice float, @GDmultiple float as insert
into RoGoInfo(Goodsno,Goodsname,Oprice,Dmultiple) values(@GDnumber,@GDname,@GDprice,@GDmultiple) 7.lsqAddAmusement的定义 create procedure lsqAddAmusement
@Atno char(20), @Atname char(20), @Atprice float as insert into Atariff
values(@Atno,@Atname,@Atprice)
8.lsqAddGuest的定义
create procedure lsqAddGuest @Gno char(20), @Gname char(20), @Gsex char(20),
31