酒店管理系统数据库代码use Hotel_Management1select*from Customerselect*from Employeeselect*from RoomTypeselect*from Roomselect*from OrderInfoselect*from Checkoutdrop database Hotel_Management1------------------------创建数据库Hotel_Management----------------------------------------------------------------------create database Hotel_MDBonprimary(name=Hotel_Management1,filename='F:\Hotel_Management\Hotel_Management.MDF',size=10MB,filegrowth=20%)log on(name=Hotel_Management1,filename='F:\Hotel_Management\Hotel_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,,ntext NotesFOREIGN KEY(EmployeeID)REFERENCES Employee(EmployeeID),FOREIGN KEY(CustomerID)REFERENCES Customer(CustomerID),)--drop table Checkoutsp_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 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('刘德华',,'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),,null)'m',,'陈小春'(values Customer into insertinsert into Customer values('kenim',,'m',,null)--OrderInfo 表select*from OrderInfoinsert 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 OrderInfoselect*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='Y'where RoomID=any(select RoomID from INSERTED )--drop trigger RoomState_1--2-创建客房使用状态触发器（删除）create trigger RoomState_2on Checkoutfor insertasRoomupdateset RoomState='N'where RoomID in(select RoomID from INSERTED )--drop trigger RoomState_2---3--创建修改订单状态触发器create trigger ORstatic_1on Checkoutfor insertasupdate OrderInfoset ORstatic='NO'where RoomID=any(select RoomID from INSERTED )--drop trigger ORstatic_1---------------------------------------存储过程------------------------------------------------------------------------------------------------------------------------------------1--创建修改客房库存触发器（减少）create proc proc_find_stu(@startId int)asupdate 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)asupdate 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 Roomselect*from OrderInfoselect*from Checkout--（）顾客入住员工查询闲置房间select*from Room where RoomState='N'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplusfrom Room,RoomTypewhereRoom.RoomTypeID=RoomType.RoomTypeID and RoomState='N'---A。