2024年1月20日发(作者：)

酒店管理系统数据库代码

use Hotel_Management1

select *from Customer

select *from Employee

select *from RoomType

select *from Room

select *from OrderInfo

select *from Checkout

drop database Hotel_Management1

------------------------创建数据库Hotel_Management----------------------------------------------------------------------

create database Hotel_MDB

on

primary

( name=Hotel_Management1,

filename='F:Hotel_ManagementHotel_',

size=10MB,

filegrowth=20%

)

log on

(name=Hotel_Management1,

filename='F:Hotel_ManagementHotel_',

size=10MB,

filegrowth=2MB)

--使用数据库

USE Hotel_Management1

--------------------------------------------创建表---------------------------------------------------------------

--1顾客表

create table Customer

(CustomerID int primary key,

CustomerName nvarchar(40) not null,

CustomerInfo nvarchar(18) not null,

Csex nvarchar(1),

CPhone nvarchar(11)not null,

Notes ntext

)

--drop table Customer

--2员工表

create table Employee

(EmployeeID int primary key,

UserName nvarchar(40) not null,

Password nvarchar(40)not null,

EmployeeName nvarchar(40) not null,

Esex nvarchar(1),

EPhone nvarchar(11)not null,

Notes ntext

)

--3客房表(有外键)

create table Room

(RoomID int primary key,

RoomTypeID int not null,

RoomState nvarchar(1)not null,

Notes ntext,

FOREIGN KEY (RoomTypeID) REFERENCES RoomType(RoomTypeID),

)

--drop table Room

--4客房类型表(有外键)

create table RoomType

(RoomTypeID int primary key,

RoomTypeName nchar(20) not null,

Cost float,

Total int,

Surplus int,

Notes ntext,

)

--drop table RoomType

--5订房表

select* from OrderInfo

create table OrderInfo

(OrderID int not null primary key,

RoomID int not null ,

CustomerID int,

EmployeeID int,

Entertime datetime not null,

Deposit float,

ORstatic nvarchar(10)not null ,

Notes ntext,

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

FOREIGN KEY (RoomID) REFERENCES Room(RoomID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),

)

--alter table add constraint OI_D ORstatic default 'use'

--drop table OrderInfo

--6退房表check-out

create table Checkout

(CheckoutID int primary key,

RoomID int not null,

CustomerID int,

EmployeeID int,

Entertime datetime not null ,

Endtime datetime not null,

Total_consumption float,

Notes ntext,

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

)

--drop table Checkout

sp_help Checkout

----------------------------------------表插入信息----------------------------------------------------------------------

--Employee表

insert into Employee values('zhoutonglu',123456,'董洁','f',,null)

insert into Employee values('liminghao',123456,'李明浩','m',,null)

insert into Employee values('yuxian',123456,'余香','f',,null)

select *from Employee

---RoomType表

select *from RoomType

insert into RoomType values(1,'单间',200,20,19,null)

insert into RoomType values(2,'标准间',260,20,19,null)

insert into RoomType values(3,'豪华单间',580,20,19,null)

insert into RoomType values(4,'行政套房',880,20,19,null)

----Room表

select *from Room

insert into Room values('1011',1,'Y',null)

insert into Room values('1012',1,'N',null)

insert into Room values('1021',2,'Y',null)

insert into Room values('1022',2,'N',null)

insert into Room values('1031',3,'Y',null)

insert into Room values('1032',3,'N',null)

insert into Room values('1041',4,'Y',null)

insert into Room values('1042',4,'N',null)

insert into Room values('1013',1,'Y',null)

insert into Room values('1014',1,'N',null)

insert into Room values('1023',2,'Y',null)

insert into Room values('1024',2,'N',null)

insert into Room values('1033',3,'Y',null)

insert into Room values('1034',3,'N',null)

insert into Room values('1051',4,'Y',null)

insert into Room values('1052',4,'N',null)

---Customer 表

select *from Customer

select *from Room

insert into Customer values('刘德华',,'m',,null)

insert into Customer values('张更硕',,'m',,null)

insert into Customer values('周 辉',,'m',,null)

insert into Customer values('刘美美',,'f',,null)

insert into Customer values('范冰冰',,'f',,null)

insert into Customer values('佟大为',,'m',,null)

insert into Customer values('范玮琪',,'f',,null)

insert into Customer values('陈小春',,'m',,null)

insert into Customer values('kenim',,'m',,null)

--OrderInfo 表

select *from OrderInfo

insert into OrderInfo values(9001,'1011',1,1,'2013-09-03 9:00PM',250.00,'use' ,null)

insert into OrderInfo values(9002,'1021',2,2,'2013-09-05 7:00PM',300.00,'use',null)

insert into OrderInfo values(9003,'1031',3,2,'2013-09-04 8:00PM',600.00,'use',null)

insert into OrderInfo values(9004,'1041',4,2,'2013-09-12 2:00PM',1000.00,'use',null)

insert into OrderInfo values(9005,'1021',9,2,'2013-09-04 7:00PM',300.00,'use',null)

insert into OrderInfo values(9006,'1031',10,2,'2013-09-04 8:00PM',600.00,'use',null)

--insert into OrderInfo values(9007,'1041',11,2,'2013-09-4 2:00PM',1000.00,'use',null)

exec proc_find_stu 1041---库存-1

--insert into OrderInfo values(9005,'1012',1,1,'2013-09-03 9:00PM',250.00,'use' ,null)

--delete OrderInfo where OrderID in(9005)

--drop table OrderInfo

---Checkout表

insert into Checkout values(13001,'1011',1,2,'2013-09-03 9:00PM','2013-09-04',200,NULL )

insert into Checkout values(13002,'1021',2,2,'2013-09-03 3:00PM','2013-09-04',200,NULL )

insert into Checkout values(13003,'1031',3,2,'2013-09-03 10:00PM','2013-09-04',200,NULL )

--insert into Checkout values(13004,'1041',4,2,'2013-09-03 8:00PM','2013-09-04',200,NULL )

insert into Checkout values(13003,'1021',9,2,'2013-09-03 10:00PM','2013-09-04',880,NULL )

delete Checkout where CheckoutID in (13001,13002)

--drop table Checkout

select *from Checkout

select *from OrderInfo

select *from RoomType

select *from Room

exec proc_find_stu 1041---库存-1

exec proc_find_stu2 1011---库存+1

--insert into Checkout values('O2001','R003',1002,2,'2013-09-06',570,NULL )

--insert into Checkout values('O2002','R001',1003,2,'2013-09-04',570,NULL )

----------------------------------------创建触发器----------------------------------------------------------------------

--1创建客房使用状态触发器(插入)

create trigger RoomState_1

on OrderInfo

for insert

as

update Room

set RoomState='Y'

where RoomID=any(select RoomID from INSERTED )

--drop trigger RoomState_1

--2-创建客房使用状态触发器（删除）

create trigger RoomState_2

on Checkout

for insert

as

update Room

set RoomState='N'

where RoomID in(select RoomID from INSERTED )

--drop trigger RoomState_2

---3--创建修改订单状态触发器

create trigger ORstatic_1

on Checkout

for insert

as

update OrderInfo

set ORstatic='NO'

where RoomID=any(select RoomID from INSERTED )

--drop trigger ORstatic_1

---------------------------------------存储过程-----------------------------------------

-----------------------------------------------------------------------------------------

--1--创建修改客房库存触发器（减少）

create proc proc_find_stu(@startId int)

as

update RoomType set Surplus=(Surplus-1)

where RoomTypeID in(select RoomTypeID from Room where RoomID=@startId)

go

exec proc_find_stu 1011

--2--创建修改客房库存触发器（增长）

create proc proc_find_stu2(@startId int)

as

update RoomType set Surplus=(Surplus+1)

where RoomTypeID in(select RoomTypeID from Room where RoomID=@startId)

go

exec proc_find_stu2 1011

---系统功能流程

use Hotel_Management1

select *from Customer

select *from Employee

select *from RoomType

select *from Room

select *from OrderInfo

select *from Checkout

--（）顾客入住员工查询闲置房间

select *from Room where RoomState='N'

select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where

peID=peID and RoomState='N'

---A。顾客要求住'标准间'

select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType

where peID=peID and RoomState='N' and RoomTypeName= '标准间'

---B。顾客要求住'豪华单间'

select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType

where peID=peID and RoomState='N' and RoomTypeName= '豪华单间'

---C。顾客要求住'行政套房'或是'豪华单间'

select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType

where peID=peID and RoomState='N' and RoomTypeName in('豪华单间','行政套房')

-------------------------------------------------------------------------------------------------------------------------

--（）登记住房订单信息----当客户入住房间,房间状态变为使用状态(RoomState='Y'，订单表'ORstatic'=USE

表示顾客入住登记成功

--例如：某客人（林大帅）要入住“标准客房”

--1.员工查询是否还有闲置“标准客房”（还剩下个房间）

select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType

where peID=peID and RoomState='N' and RoomTypeName= '标准间'

--2.登记受理，先登记入住顾客信息

select *from Customer

insert into Customer values('林大帅',,'m',,null)

select *from Customer

---3.登记订单信息

insert into OrderInfo values(9007,'1022',12,2,'2013-09-4 2:00PM',400.00,'use',null)

select *from Room --可以看到该房间状态为‘Y’（触发器起作用）

select *from OrderInfo --登记成功

----------调用存储过程把该类房间剩余量减一

select *from RoomType--可以看到该房间类型还有个

exec proc_find_stu 1022---库存-1

--（）顾客退房登记信息(订单表'ORstatic'=NO 表示顾客已经退房，同时房间表中该房间编号'RoomState'=N 表示该房间空闲)

--例如：房间号为的顾客退房

select *from Checkout

select *from OrderInfo

select *from Room

insert into Checkout values(13003,'1021',9,2,'2013-09-03 10:00PM','2013-09-04',880,NULL )

select *from Checkout--确定办理成功

select *from OrderInfo

---同时调用存储过程把该类房间数量+1

select *from RoomType--可以看到该房间类型还有个

exec proc_find_stu2 1021---库存+1

----当客户退房时，登记成功，房间状态变为空闲状态(RoomState='N')

select *from Room--可以看到该房间状态为‘N’（触发器起作用）

select *from OrderInfo--可以看到该ORstatic为‘NO’（触发器起作用）

select *from Checkout

--------------------------------------------------------------------------------------------------

--（）查询当前酒店入住的客户数量

select count(*) as '当前入住顾客人数'from OrderInfo where( ORstatic = 'use')

---(5)查询-09-04的营业额

select *from Checkout

select SUM(Total_consumption) from Checkout WHERE Endtime='2013-09-04'

---(6)查询现在入住的客户中性别为男的客户信息

select CustomerName,Csex,RoomID,Entertime,Deposit from Customer,OrderInfo

where erID=erID and Csex='m'

---(7)查询现在入住的客人姓范的有那些

select CustomerName,Csex,RoomID,Entertime,Deposit from Customer,OrderInfo

where erID=erID and CustomerName like '范%'

---(7)查询当前入住''行政套房'的客人信息

select CustomerName,,Entertime,Deposit from Customer,OrderInfo

where erID=erID and like'104_'