酒店管理系统数据库代码

酒店管理系统数据库代码useHotel_Management1select*fromCustomerselect*fromEmployeeselect*fromRoomTypeselect*fromRoomselect*fromOrderInfoselect*fromCheckoutdropdatabaseHotel_Management1------------------------创建数据库Hotel_Management----------------------------------------------------------------------createdatabaseHotel_MDBonprimary(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*fromOrderInfocreatetableOrderInfo(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-outcreatetableCheckout(CheckoutIDintprimarykey,RoomIDintnotnull,CustomerIDint,EmployeeIDint,Entertimedatetimenotnull,Endtimedatetimenotnull,Total_consumptionfloat,Notesntext,FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),)--droptableCheckoutsp_helpCheckout----------------------------------------表插入信息------------------------------------------------------------------------Employee表insertintoEmployeevalues('zhoutonglu',123456,'董洁','f',,null)insertintoEmployeevalues('liminghao',123456,'李明浩','m',,null)insertintoEmployeevalues('yuxian',123456,'余香','f',,null)select*fromEmployee---RoomType表select*fromRoomTypeinsertintoRoomTypevalues(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*fromRoominsertintoRoomvalues('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*fromCustomerselect*fromRoominsertintoCustomervalues('刘德华',,'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*fromOrderInfoinsertintoOrderInfovalues(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)--droptableCheckoutselect*fromCheckoutselect*fromOrderInfoselect*fromRoomTypeselect*fromRoomexecproc_find_stu1041---库存-1execproc_find_stu2