酒店管理系统数据库代码.doc

酒店管理系统数据库代码use Hotel_Management1select *from Customerselect *from Employeeselect *from RoomTypeselect *from Roomselect *from OrderInfoselect *from Checkout drop database Hotel_Management1-创建数据库Hotel_Management-create database Hotel_MDBon primary( name=Hotel_Management1,filename=F:Hotel_ManagementHotel_Management.MDF,size=10MB,filegrowth=20%)log on(name=Hotel_Management1,filename=F:Hotel_ManagementHotel_Management1.LDF,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 OrderInfocreate 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-outcreate 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 Checkoutsp_help Checkout-表插入信息-Employee表insert into Employee values(zhoutonglu,123456,董洁,f,18767567876,null)insert into Employee values(liminghao,123456,李明浩,m,13667566547,null)insert into Employee values(yuxian,123456,余香,f,13967567777,null)select *from Employee-RoomType表select *from RoomTypeinsert 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 Roominsert 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 Customerselect *from Roominsert into Customer values(刘德华,330276187908142673,m,15676156274,null)insert into Customer values(张更硕,330276187908142673,m,18976156654,null)insert into Customer values(周 辉,330276189708142673,m,16376156666,null)insert into Customer values(刘美美,330276189308142673,f,18776157654,null)insert into Customer values(范冰冰,330276187908142673,f,15676156274,null)insert into Customer values(佟大为,330276187908142673,m,18976156654,null)insert into Customer values(范玮琪,330276189708142673,f,16376156666,null)insert into Customer values(陈小春,330276189308142673,m,18776157654,null)insert into Customer values(kenim,330276189308142673,m,18776157654,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 Checkoutselect *from Checkoutselect *from OrderInfo select *from RoomTypeselect *from Roomexec proc_find_stu 1041-库存-1exec 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_1on OrderInfofor insertasupdate Roomset RoomState=Ywhere RoomID=any(select RoomID from INSERTED )-drop trigger RoomState_1-2-创建客房使用状态触发器（删除）create trigger RoomState_2on Checkoutfor insertasupdate Roomset RoomState=Nwhere RoomID in(select RoomID from INSERTED )-drop trigger RoomState_2-3-创建修改订单状态触发器create trigger ORstatic_1on Checkoutfor insertasupdate OrderInfoset ORstatic=NOwhere 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)goexec 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)goexec proc_find_stu2 1011-系统功能流程use Hotel_Management1select *from Customerselect *from Employeeselect *from RoomTypeselect *from Roomselect *from OrderInfoselect *from Checkout-（）顾客入住员工查询闲置房间 select *from Room where RoomState=N select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState=N -A。顾客要求住标准间 select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState=N and RoomTypeName= 标准间 -B。顾客要求住豪华单间 select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState=N and RoomTypeName= 豪华单间 -C。顾客要求住行政套房或是豪华单间 select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState=N and RoomTypeName in(豪华单间,行政套房)- -（）登记住房订单信息-当客户入住房间,房间状态变为使用状态(RoomState=Y，订单表ORstatic=USE 表示顾客入住登记成功 -例如：某客人（林大帅）要入住“标准客房” -1.员工查询是否还有闲置“标准客房”（还剩下个房间） select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from Room,RoomType where Room.RoomTypeID=RoomType.RoomTypeID and RoomState=N and RoomTypeName= 标准间 -2.登记受理，先登记入住顾客信息 select *from Customer insert into Customer values(林大帅,330276189308142673,m,18776157654,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 Customer.CustomerID=OrderInfo.CustomerID and Csex=m-(7)查询现在入住的客人姓范的有那些 select CustomerName,Csex,RoomID,Entertime,Deposit from Customer,OrderInfo where Customer.CustomerID=OrderInfo.CustomerID and CustomerName like 范%-(7)查询当前入住行政套房的客人信息 select CustomerName,OrderInfo.RoomID,Entertime,Deposit from Customer,OrderInfo where Customer.CustomerID=OrderInfo.CustomerID and OrderInfo.RoomID like104_