《酒店管理系统数据库代码》由会员分享，可在线阅读，更多相关《酒店管理系统数据库代码（11页珍藏版）》请在金锄头文库上搜索。
1、use Hotel_Ma nageme nt1select* from Customerselect* from Employeeselect* from RoomTypeselect* from Roomselect* from Orderl nfoselect* from Checkoutdrop databaseHotel_Ma nageme nt1创建数据库Hotel_ManagementcreatedatabaseHotel_MDBonprimary(name=Hotel_Management1,file name=F:Hotel_Ma nageme ntHotel_Ma nagem
2、e nt.MDF；size=10MB,filegrowth =20%If1I I)log on(n ame=Hotel_Ma nageme nt1,file name=F:Hotel_Ma nageme ntHotel_Ma nageme nt1 丄 DF：size=10MB,filegrowth =2MB)-使用数据库USE Hotel_Ma nageme nt1创建表-1顾客表createtable Customer(CustomerlD int primary key,CustomerNamenvarchar(40) not null,Customerlnfo nvarchar(18)
3、notnuII,Csex nv archa(1),X 1 CPhone nvarchar(11)not null,Notes n text)-drop table Customer-2员工表createtable Employee(EmployeelD int primary key,UserNamenvarchar(40) not nuII,Password nv archar(4 O)not n ull,EmployeeNamenvarchar(40) not null,Esex nv archar(1),EPhonenvarchar(11 )not null.Notes n text)-
4、3客房表(有外键)createtable Room(RoomID int primary key,RoomTypelD int notnull,RoomStatenvarchar(1)not nuII,Notes n text,FOREIGN KEY (RoomTypeID) REFERENCES RoomType(RoomTypeID),)-drop table Room-4客房类型表(有外键)createtable RoomType(RoomTypeID int primary key,RoomTypeName nchar(20) not nuII,Cost float,Total int
5、,Surplus int,Notes n text,i I)-drop table RoomType-5订房表select from OrderInfocreatetable OrderI nfo(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 n text,FOREIGN KEY (CustomerID) REFERE
6、NCES Customer(CustomerlD),FOREIGN KEY (RoomID) REFERENCES Room(RoomID),FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),)-alter table add constraint OI_D ORstatic default use-drop table OrderI nfo-6 退房表 check-outcreatetable Checkout(CheckoutID int primary key,RoomID int not nuII,CustomerID i
7、nt,EmployeeID int,Entertime datetime not null ,Endtime datetime not null,Total_c on sumpti on float,Notes n text,FOREIGN KEY (EmployeelD) REFERENCES Employee(EmployeelD),FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerlD),)-drop table Checkoutsp_help Checkout表插入信息-Employee表insert into Employee
8、values(zhoutonglu,123456,董洁,f,null)in sert into Employee values(limi nghao,123456,李明浩,m, null)insert into Employee values(yuxian,123456,余香,f,null)select* from Employee-RoomType 表select* from RoomTypein sert into RoomType values(1,单间,200,20,19, null)insert into RoomType values(2,标准间,260,20,19,null)in
9、sert into RoomType values(3,豪华单间,580,20,19,null)insert into RoomType values(4,行政套房,880,20,19,null)-Room 表select* from Roominsertinto Room values(1011,1,Y,null)insertinto Room values(1012,1,N,null)in sert into Room values(1021,2,Y, null)in sert into Room values(1022,2,N, null)in sert into Room values
10、(1031,3,Y, null)in sert into Room values(1032,3,N, null)in sert into Room values(1041,4,Y, null)in sert into Room values(1042,4,N, null)in sert into Room values(1013, 1,Y, null)insertinto Room values(1014,1,N,null)in sert into Room values(1023,2,Y, null)in sert into Room values(1024,2,N, null)insert
11、into Room values(1033,3,Y,null)in sert into Room values(1034,3,N, null)in sert into Room values(1051,4,Y, null)in sert into Room values(1052,4,N, null)-Customer 表 select* from Customer select* from Room insertinto Customer values(刘德华,m,null) in serti nto Customer values(张更硕,m, null) insert into Cust
12、omer values(周 辉,m,null) insertinto Customer values(刘美美,f,null) insertinto Customer values(范冰冰,f,null) 来源于网络in serti nto Customer values(佟大为,m, ,n ull)insertinto Customer values(范玮琪null)in serti nto Customer values(陈小春,m, null)in sert into Customer values(ke ni m,m, nu II)-Orderlnfo 表select* from Ord
13、erl nfoinsertinto Orderlnfo values(9001,1011,1,1,2013-09-03 9:00PM；250.00,use,null)insertinto OrderInfo values(9002,1021,2,2,2013-09-05 7:00PM,300.00,use；null)in serti nto OrderI nfo values(9003,1031,3,2,2013-09-04 8:00PM,600.00,use； null)insertinto OrderInfo values(9004,1041,4,2,2013-09-12 2:00PM,1
14、000.0Quse；null)in serti nto Orderl nfo values(9005,1021,9,2,2013-09-04 7:00PM,300.00,use； null)insertinto Orderlnfo values(9006,1031,10,2,2013-09-04 8:00PM,600.00,use；null)-insert into Orderlnfo values(9007,1041,11,2,2013-09-4 2:00PM,1000.00,use,null) exec proc_fi nd_stu 1041-库存-1-insert into Orderlnfo values(9005,1012,1,1,2013-09-03 9:00PM,250.00,use ,null) -delete Orderl nfo where OrderlD in(9005)-drop table Orderl nfo-Checkout表insertinto Checkout valuesU3001,1011,1,2,2013-09-03 9:00PM,2013-09-04,200,NULL ) insertinto Checkout values(13002,1021,2,2,2013-09-03 3:0
