优化MySchool 第八章 酒店管理系统
来源:互联网 发布:常用cmd 网络命令 编辑:程序博客网 时间:2024/05/01 18:16
--创建Hotel数据库 use master go if exists (select * from sysdatabases where name='Hotel') drop database Hotel create database Hotel on primary ( name='Hotel_data', filename='D:\Project_S2\Hotel_data.mdf', size=5mb, maxsize=100mb, filegrowth=15% ) log on ( name='Hotel_log', filename='D:\Project_S2\Hotel_log.ldf', size=5mb, maxsize=100mb, filegrowth=15% ) go --创建ROOM表(客房信息表) use Hotel go if exists (select * from sysobjects where name='Room') drop table Room create table Room ( RoomID int identity(1,1) primary key, BedNum int not null, GuestNum int not null, Description nvarchar(32) not null, RoomStateID int, RoomTypeID int ) go --创建RoomType表(客房类型) create table RoomType ( TypeID int identity(1,1) primary key, TypeName nvarchar(32) , TypePrice Decimal ) go --创建RoomState表(客房状态) create table RoomState ( RoomStateID int identity(1,1) primary key, RoomStateName nvarchar(32) ) go --创建ResideState表(结账状态表) create table ResideState ( ResideID int identity(1,1) primary key, ResideName nvarchar(32) not null ) go --创建GuestRecord表(客人信息表) use Hotel go if exists (select * from sysobjects where name='GuestRecord') drop table GuestRecord create table GuestRecord ( GuestID int identity(1,1) primary key, GuestName nvarchar(32) not null, IdentityID varchar(32) , RoomID int, ResideId int, ResideDate datetime, LeaveDate datetime, Deposit decimal, TotalMoney decimal ) go --客房类型表约束 alter table RoomType add constraint CK_TypePrice check (TypePrice>=0) go --客房信息表约束 alter table Room add constraint DF_BedNum default(2) for BedNum go alter table Room add constraint DF_GuestNum default ('0') for GuestNum go alter table Room add constraint CK_GuestNum check (GuestNum>=0) go alter table Room add constraint FK_RoomStateID foreign key (RoomStateID)references RoomState (RoomStateID) go alter table Room add constraint FK_RoomTypeID foreign key (RoomTypeID)references RoomType (TypeID) go --客人信息表约束 alter table GuestRecord add constraint CK_IdentityID check (IdentityID=18) go alter table GuestRecord add constraint FK_RoomID foreign key (RoomID) references Room (RoomID) go alter table GuestRecord add constraint FK_ResideID foreign key (ResideID) references ResideState (ResideId) go alter table GuestRecord add constraint DF_ResideId default ('未结账') for ResideID go alter table GuestRecord add constraint CK_LeaveDate check (LeaveDate>=ResideDate) go ---------------------------------------------------------------------------------------------------------------------------- --添加数据 -------------------------------------客房类型表(RoomType)------------------------------------------------------------------ insert into RoomType(TypeName, TypePrice) values ('标准间',CAST(180.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('单人间',CAST(128.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('三人间',CAST(208.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('总统套房',CAST(998.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('长包房',CAST(108.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('豪华标准间',CAST(268.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('单人套房',CAST(368.00 as Decimal(18,2))) insert into RoomType(TypeName, TypePrice) values ('双人套房',CAST(568.00 as Decimal(18,2))) ---------------------------------------客房状态表(RoomState)--------------------------------------------------------------- insert into RoomState(RoomStateName) values ('已入住') insert into RoomState(RoomStateName) values ('空闲') insert into RoomState(RoomStateName) values ('维修') ---------------------------------------结账状态表(ResideState)-------------------------------------------------------------- insert into ResideState (ResideName) values ('已结账') insert into ResideState (ResideName) values ('未结账') ---------------------------------------客人信息表(GuestRecord)-------------------------------------------------------------- insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('张三','211022188708197801','1001',1,'2009-09-12 12:30:08.000','2009-09-13 12:30:00.000',1000.00,180.00) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('李四','211022199808196707','1002',1,'2009-09-10 12:30:08.000','2009-09-11 12:30:00.000',1000.00,208.00) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('王五','211022188608197809','1003',2,'2009-09-12 12:30:08.000',null,3000.00,null) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('张三三','211022188708197806','1004',2,'2009-09-12 12:30:08.000',null,800.00,null) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('石小岩','211022188708197808','1005',2,'2009-09-12 12:30:08.000',null,1500.00,null) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('岩岩','211022188708197810','1006',2,'2009-09-12 12:30:08.000',null,5000.00,null) insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney) values ('张小蛋','211022188708197811','1007',2,'2009-09-12 12:30:08.000',null,1000.00,null) ------------------------------------------客房信息表(Room)-------------------------------------------------------------------- insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1001',1,'标准间',1,1) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1002',3,'三人间',1,3) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1003',2,'豪华套房',2,6) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1004',1,'长包房',2,5) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1005',1,'单人套房',2,7) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1006',2,'总统套房',3,4) insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID) values ('1007',1,'标准间',2,1) ------------------------------------------------------------------------------------------------------------------------------------ --1. create procedure usp_GetGuestNumByTypeName @RoomType varchar(20), @count int output as set @RoomType='标准间' declare @num int select @num=count(@RoomType) from Room where RoomID=1 and RoomStateID=1 print @num GO set nocount on declare @count int declare @RoomType varchar(20) set @RoomType='标准间' exec usp_GetGuestNumByTypeName @RoomType,@count output print '入住酒店'+@RoomType+'的客人总人数是:'+cast(@count as varchar(10)) --检查判断错误 declare @err int set @err =@@ERROR if @err<>0 begin print '错误号:'+convert(varchar(5),@err) return --退出批处理,后续语句不再执行 end go --删除 use Hotel go if exists (select * from sysobjects where name='usp_GetGuestNumByTypeName') drop procedure usp_GetGuestNumByTypeName go --2. --想要RoomId有固定的数然后查询,如果查不到则等于-1然后查询所有,不然查询单行RoomId等于什么就查什么????? create procedure usp_GetGuestRoom @Room int as --declare @Room int select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room where RoomType.TypeID=Room.RoomTypeID and @Room=RoomID print @Room go declare @Room int exec usp_GetGuestRoom @Room if @Room!=-1 begin select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room where RoomType.TypeID=Room.RoomTypeID and @Room=RoomID end else begin select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room where RoomType.TypeID=Room.RoomTypeID end --3.删除酒店客房类型编号是三人间的记录1条 create procedure usp_deleteRoomTypeById @RoomTYPE varchar(20), @Result int as --select 语句 go declare @RoomTYPE varchar(20) declare @Result int set @RoomTYPE='三人间' exec @Result=usp_deleteRoomTypeById @RoomTYPE if (@Result>0) print '删除酒店客房类型是'+@RoomTYPE+'的记录'+cast(@Result as varchar(10))+'条' else print '删除酒店客房类型是'+@RoomTYPE+'的记录,失败' --4. --(1行受影响) --(1行受影响) --插入客户记录操作成功 --客户编号是11 create procedure usp_insertGuestRecord @Result1 int, @GuestId int output, @identityID varchar(20), @guestName varchar(32), @roomID int, @ResideDate datetime, @deposit money as ----select 语句 go declare @Result1 int declare @GuestId int declare @identityID varchar(20) declare @guestName varchar(32) declare @roomID int declare @ResideDate datetime declare @deposit money --set @GuestId='' exec @Result1=usp_insertGuestRecord @GuestId output,@identityID ,@guestName,@roomID,@ResideDate,@deposit print @Result1 if (@Result1=0) begin print '插入客户记录操作成功' print '客人编号是'+cast(@GuestId as varchar) end else print '插入客户记录操作失败'
0 0
- 优化MySchool 第八章 酒店管理系统
- 优化myschool 第八章 酒店管理系统
- 优化MySchool 第八章 酒店管理系统
- 第八章酒店管理系统
- 优化MySchool 第八章
- 优化MySchool 第八章
- SQL高级 第八章 酒店管理系统
- S2 优化MySchool 数据库设计 第八章
- MySchool第八章
- ACCP7.0优化myschool数据库设计第八章项目
- MySchool管理系统
- ACCP7.0S2优化MySchool数据库设计管理系统脚本
- 优化MySchool第九章
- 优化MySchool 第二章
- 优化myschool 第九章
- 酒店管理系统
- 酒店管理系统
- 酒店管理系统心得
- Java杂谈3——类加载机制与初始化顺序
- SQL 第三章 练习
- java中的8种基本数据类型占的位数以及取值范围
- js对象知识
- Class.asSubclass浅谈
- 优化MySchool 第八章 酒店管理系统
- HBase 安装
- NS2资料
- android:Image View
- SpringMVC multipart文件上传详细教程之二:控制器的编写及异常处理
- 数据告诉你 :移动互联网下半场的机会在哪里
- Matrial Design(一)
- 皮皮java笔记——java数据库连接:JDBC API
- placeholder 修改颜色