酒店管理系统数据库代码.docx
《酒店管理系统数据库代码.docx》由会员分享,可在线阅读,更多相关《酒店管理系统数据库代码.docx(10页珍藏版)》请在冰豆网上搜索。
![酒店管理系统数据库代码.docx](https://file1.bdocx.com/fileroot1/2023-1/2/63a6f16d-5db6-4261-aa3d-1a680fe1f03b/63a6f16d-5db6-4261-aa3d-1a680fe1f03b1.gif)
酒店管理系统数据库代码
Revisedat2pmonDecember25,2020.
酒店管理系统数据库代码
酒店管理系统数据库代码
useHotel_Management1
select*fromCustomer
select*fromEmployee
select*fromRoomType
select*fromRoom
select*fromOrderInfo
select*fromCheckout
dropdatabaseHotel_Management1
------------------------创建数据库Hotel_Management----------------------------------------------------------------------
createdatabaseHotel_MDB
on
primary
(name=Hotel_Management1,
filename='F:
\Hotel_Management\',
size=10MB,
filegrowth=20%
)
logon
(name=Hotel_Management1,
filename='F:
\Hotel_Management\',
size=10MB,
filegrowth=2MB)
--使用数据库
USEHotel_Management1
--------------------------------------------创建表---------------------------------------------------------------
--1顾客表
createtableCustomer
(CustomerIDintprimarykey,
CustomerNamenvarchar(40)notnull,
CustomerInfonvarchar(18)notnull,
Csexnvarchar
(1),
CPhonenvarchar(11)notnull,
Notesntext
)
--droptableCustomer
--2员工表
createtableEmployee
(EmployeeIDintprimarykey,
UserNamenvarchar(40)notnull,
Passwordnvarchar(40)notnull,
EmployeeNamenvarchar(40)notnull,
Esexnvarchar
(1),
EPhonenvarchar(11)notnull,
Notesntext
)
--3客房表(有外键)
createtableRoom
(RoomIDintprimarykey,
RoomTypeIDintnotnull,
RoomStatenvarchar
(1)notnull,
Notesntext,
FOREIGNKEY(RoomTypeID)REFERENCESRoomType(RoomTypeID),
)
--droptableRoom
--4客房类型表(有外键)
createtableRoomType
(RoomTypeIDintprimarykey,
RoomTypeNamenchar(20)notnull,
Costfloat,
Totalint,
Surplusint,
Notesntext,
)
--droptableRoomType
--5订房表
select*fromOrderInfo
createtableOrderInfo
(OrderIDintnotnullprimarykey,
RoomIDintnotnull,
CustomerIDint,
EmployeeIDint,
Entertimedatetimenotnull,
Depositfloat,
ORstaticnvarchar(10)notnull,
Notesntext,
FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),
FOREIGNKEY(RoomID)REFERENCESRoom(RoomID),
FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),
)
--altertableaddconstraintOI_DORstaticdefault'use'
--droptableOrderInfo
--6退房表check-out
createtableCheckout
(CheckoutIDintprimarykey,
RoomIDintnotnull,
CustomerIDint,
EmployeeIDint,
Entertimedatetimenotnull,
Endtimedatetimenotnull,
Total_consumptionfloat,
Notesntext,
FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),
FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),
)
--droptableCheckout
sp_helpCheckout
----------------------------------------表插入信息----------------------------------------------------------------------
--Employee表
insertintoEmployeevalues('zhoutonglu',123456,'董洁','f',,null)
insertintoEmployeevalues('liminghao',123456,'李明浩','m',,null)
insertintoEmployeevalues('yuxian',123456,'余香','f',,null)
select*fromEmployee
---RoomType表
select*fromRoomType
insertintoRoomTypevalues(1,'单间',200,20,19,null)
insertintoRoomTypevalues(2,'标准间',260,20,19,null)
insertintoRoomTypevalues(3,'豪华单间',580,20,19,null)
insertintoRoomTypevalues(4,'行政套房',880,20,19,null)
----Room表
select*fromRoom
insertintoRoomvalues('1011',1,'Y',null)
insertintoRoomvalues('1012',1,'N',null)
insertintoRoomvalues('1021',2,'Y',null)
insertintoRoomvalues('1022',2,'N',null)
insertintoRoomvalues('1031',3,'Y',null)
insertintoRoomvalues('1032',3,'N',null)
insertintoRoomvalues('1041',4,'Y',null)
insertintoRoomvalues('1042',4,'N',null)
insertintoRoomvalues('1013',1,'Y',null)
insertintoRoomvalues('1014',1,'N',null)
insertintoRoomvalues('1023',2,'Y',null)
insertintoRoomvalues('1024',2,'N',null)
insertintoRoomvalues('1033',3,'Y',null)
insertintoRoomvalues('1034',3,'N',null)
insertintoRoomvalues('1051',4,'Y',null)
insertintoRoomvalues('1052',4,'N',null)
---Customer表
select*fromCustomer
select*fromRoom
insertintoCustomervalues('刘德华',,'m',,null)
insertintoCustomervalues('张更硕',,'m',,null)
insertintoCustomervalues('周辉',,'m',,null)
insertintoCustomervalues('刘美美',,'f',,null)
insertintoCustomervalues('范冰冰',,'f',,null)
insertintoCustomervalues('佟大为',,'m',,null)
insertintoCustomervalues('范玮琪',,'f',,null)
insertintoCustomervalues('陈小春',,'m',,null)
insertintoCustomervalues('kenim',,'m',,null)
--OrderInfo表
select*fromOrderInfo
insertintoOrderInfovalues(9001,'1011',1,1,'2013-09-039:
00PM',,'use',null)
insertintoOrderInfovalues(9002,'1021',2,2,'2013-09-057:
00PM',,'use',null)
insertintoOrderInfovalues(9003,'1031',3,2,'2013-09-048:
00PM',,'use',null)
insertintoOrderInfovalues(9004,'1041',4,2,'2013-09-122:
00PM',,'use',null)
insertintoOrderInfovalues(9005,'1021',9,2,'2013-09-047:
00PM',,'use',null)
insertintoOrderInfovalues(9006,'1031',10,2,'2013-09-048:
00PM',,'use',null)
--insertintoOrderInfovalues(9007,'1041',11,2,'2013-09-42:
00PM',,'use',null)
execproc_find_stu1041---库存-1
--insertintoOrderInfovalues(9005,'1012',1,1,'2013-09-039:
00PM',,'use',null)
--deleteOrderInfowhereOrderIDin(9005)
--droptableOrderInfo
---Checkout表
insertintoCheckoutvalues(13001,'1011',1,2,'2013-09-039:
00PM','2013-09-04',200,NULL)
insertintoCheckoutvalues(13002,'1021',2,2,'2013-09-033:
00PM','2013-09-04',200,NULL)
insertintoCheckoutvalues(13003,'1031',3,2,'2013-09-0310:
00PM','2013-09-04',200,NULL)
--insertintoCheckoutvalues(13004,'1041',4,2,'2013-09-038:
00PM','2013-09-04',200,NULL)
insertintoCheckoutvalues(13003,'1021',9,2,'2013-09-0310:
00PM','2013-09-04',880,NULL)
deleteCheckoutwhereCheckoutIDin(13001,13002)
--droptableCheckout
select*fromCheckout
select*fromOrderInfo
select*fromRoomType
select*fromRoom
execproc_find_stu1041---库存-1
execproc_find_stu21011---库存+1
--insertintoCheckoutvalues('O2001','R003',1002,2,'2013-09-06',570,NULL)
--insertintoCheckoutvalues('O2002','R001',1003,2,'2013-09-04',570,NULL)
----------------------------------------创建触发器----------------------------------------------------------------------
--1\创建客房使用状态触发器(插入)
createtriggerRoomState_1
onOrderInfo
forinsert
as
updateRoom
setRoomState='Y'
whereRoomID=any(selectRoomIDfromINSERTED)
--droptriggerRoomState_1
--2-创建客房使用状态触发器(删除)
createtriggerRoomState_2
onCheckout
forinsert
as
updateRoom
setRoomState='N'
whereRoomIDin(selectRoomIDfromINSERTED)
--droptriggerRoomState_2
---3--创建修改订单状态触发器
createtriggerORstatic_1
onCheckout
forinsert
as
updateOrderInfo
setORstatic='NO'
whereRoomID=any(selectRoomIDfromINSERTED)
--droptriggerORstatic_1
---------------------------------------存储过程-----------------------------------------
-----------------------------------------------------------------------------------------
--1--创建修改客房库存触发器(减少)
createprocproc_find_stu(@startIdint)
as
updateRoomTypesetSurplus=(Surplus-1)
whereRoomTypeIDin(selectRoomTypeIDfromRoomwhereRoomID=@startId)
go
execproc_find_stu1011
--2--创建修改客房库存触发器(增长)
createprocproc_find_stu2(@startIdint)
as
updateRoomTypesetSurplus=(Surplus+1)
whereRoomTypeIDin(selectRoomTypeIDfromRoomwhereRoomID=@startId)
go
execproc_find_stu21011
---系统功能流程
useHotel_Management1
select*fromCustomer
select*fromEmployee
select*fromRoomType
select*fromRoom
select*fromOrderInfo
select*fromCheckout
--()顾客入住员工查询闲置房间
select*fromRoomwhereRoomState='N'
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomTypewhere=andRoomState='N'
---A。
顾客要求住'标准间'
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomType
where=andRoomState='N'andRoomTypeName='标准间'
---B。
顾客要求住'豪华单间'
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomType
where=andRoomState='N'andRoomTypeName='豪华单间'
---C。
顾客要求住'行政套房'或是'豪华单间'
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomType
where=andRoomState='N'andRoomTypeNamein('豪华单间','行政套房')
-------------------------------------------------------------------------------------------------------------------------
--()登记住房订单信息----当客户入住房间,房间状态变为使用状态(RoomState='Y',订单表'ORstatic'=USE表示顾客入住登记成功
--例如:
某客人(林大帅)要入住“标准客房”
--1.员工查询是否还有闲置“标准客房”(还剩下个房间)
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomType
where=andRoomState='N'andRoomTypeName='标准间'
--2.登记受理,先登记入住顾客信息
select*fromCustomer
insertintoCustomervalues('林大帅',,'m',,null)
select*fromCustomer
---3.登记订单信息
insertintoOrderInfovalues(9007,'1022',12,2,'2013-09-42:
00PM',,'use',null)
select*fromRoom--可以看到该房间状态为‘Y’(触发器起作用)
select*fromOrderInfo--登记成功
----------调用存储过程把该类房间剩余量减一
select*fromRoomType--可以看到该房间类型还有个
execproc_find_stu1022---库存-1
--()顾客退房登记信息(订单表'ORstatic'=NO表示顾客已经退房,同时房间表中该房间编号'RoomState'=N表示该房间空闲)
--例如:
房间号为的顾客退房
select*fromCheckout
select*fromOrderInfo
select*fromRoom
insertintoCheckoutvalues(13003,'1021',9,2,'2013-09-0310:
00PM','2013-09-04',880,NULL)
select*fromCheckout--确定办理成功
select*fromOrderInfo
---同时调用存储过程把该类房间数量+1
select*fromRoomType--可以看到该房间类型还有个
execproc_find_stu21021---库存+1
----当客户退房时,登记成功,房间状态变为空闲状态(RoomState='N')
select*fromRoom--可以看到该房间状态为‘N’(触发器起作用)
select*fromOrderInfo--可以看到该ORstatic为‘NO’(触发器起作用)
select*fromCheckout
--------------------------------------------------------------------------------------------------
--()查询当前酒店入住的客户数量
selectcount(*)as'当前入住顾客人数'fromOrderInfowhere(ORstatic='use')
---(5)查询-09-04的营业额
select*fromCheckout
selectSUM(Total_consumption)fromCheckoutWHEREEndtime='2013-09-04'
---(6)查询现在入住的客户中性别为男的客户信息
selectCustomerName,Csex,RoomID,Entertime,DepositfromCustomer,OrderInfo
where=andCsex='m'
---(7)查询现在入住的客人姓范的有那些
selectCustomerName,Csex,RoomID,Entertime,DepositfromCustomer,OrderInfo
where=andCustomerNamelike'范%'
---(7)查询当前入住''行政套房'的客人信息
selectCustomerName,,Enterti