1---------------------------酒店管理系统-----51aspx.com------------------------------------------
  2use master
  3go
  4if exists(select * from sysdatabases where name='MyHotelManager')
  5    begin
  6        drop database MyHotelManager
  7    end
  8go
  9create database MyHotelManager
 10go
 11use MyHotelManager
 12go
 13---------------------------------------------------------------------------------------
 14/**//*用户表*/
 15create table UserInfo
 16(
 17    UserId int identity(1,1) primary key, --用户Id
 18    LoginName varchar(50) not null, --登录名
 19    LoginPass varchar(50) not null, --登录密码
 20    UserName varchar(50) not null, --真实姓名
 21    Remark varchar(200) --备注
 22)
 23go
 24insert into UserInfo values('admin','admin','陈万乐','老板撒')
 25insert into UserInfo values('aaa','aaa','不晓得','普通员工撒')
 26go
 27---------------------------------------------------------------------------------------
 28/**//*房间状态表*/
 29create table RoomState
 30(
 31    StateId int identity(1,1) primary key, --状态Id
 32    StateName varchar(20) not null --状态名称
 33)
 34go
 35insert into RoomState values('空闲')
 36insert into RoomState values('入住')
 37insert into RoomState values('已订')
 38insert into RoomState values('维修')
 39insert into RoomState values('自用')
 40go
 41---------------------------------------------------------------------------------------
 42/**//*是否表*/
 43create table  IsTable
 44(
 45    IsId int identity(1,1) primary key,
 46    IsName varchar(10) not null
 47)
 48go
 49insert into IsTable values('是')
 50insert into IsTable values('否')
 51go
 52---------------------------------------------------------------------------------------
 53/**//*房间类型表*/
 54create table RoomType
 55(
 56    TypeId int identity(1,1) primary key, --房间类型Id
 57    TypeName varchar(20) not null, --类型名称
 58    TypePrice money, --类型价格
 59    IsTv varchar(10) not null, --是否配备电视
 60    IsKongTiao varchar(10) not null, --是否配备空调
 61    Remark varchar(200) --房间类型备注 
 62)
 63go
 64insert into RoomType values('终点房',50,'是','否','普通的终点房，经济实惠')
 65insert into RoomType values('普通单间',70,'是','是','普通的单间，适合一个人住')
 66insert into RoomType values('豪华单间',90,'是','是','豪华的单间，但也不贵')
 67insert into RoomType values('双人间',120,'是','是','双人房，两个人住嘛')
 68insert into RoomType values('贵宾间',150,'是','是','有点贵哟')
 69insert into RoomType values('总统套房',200,'是','是','这个不说了，贵死人')
 70go
 71---------------------------------------------------------------------------------------
 72/**//*客房表*/
 73create table Room
 74(
 75    RoomId int identity(1,1) primary key, --客房表Id
 76    Number varchar(20) not null, --房间编号
 77    BedNumber int not null, --床位数
 78    TypeId int foreign key references RoomType(TypeId) not null, --房间类型Id
 79    StateId int foreign key references RoomState(StateId) not null, --房间状态Id
 80    Remark varchar(200) --房间备注
 81)
 82go
 83insert into Room values('301',1,1,1,'还可以哈')
 84insert into Room values('302',1,1,1,'还可以哈')
 85insert into Room values('303',1,1,1,'还可以哈')
 86insert into Room values('304',1,1,1,'还可以哈')
 87insert into Room values('305',1,1,1,'还可以哈')
 88insert into Room values('306',1,1,1,'还可以哈')
 89insert into Room values('307',1,2,1,'还可以哈')
 90insert into Room values('308',1,2,1,'还可以哈')
 91insert into Room values('309',1,2,1,'还可以哈')
 92insert into Room values('310',1,2,1,'还可以哈')
 93insert into Room values('311',1,2,1,'还可以哈')
 94insert into Room values('312',1,2,1,'还可以哈')
 95insert into Room values('313',1,3,1,'还可以哈')
 96insert into Room values('314',1,3,1,'还可以哈')
 97insert into Room values('315',1,3,1,'还可以哈')
 98insert into Room values('316',1,3,1,'还可以哈')
 99insert into Room values('317',1,3,1,'还可以哈')
100insert into Room values('318',1,3,1,'还可以哈')
101insert into Room values('319',1,3,1,'还可以哈')
102insert into Room values('320',1,3,1,'还可以哈')
103insert into Room values('321',1,3,1,'还可以哈')
104insert into Room values('322',2,4,1,'还可以哈')
105insert into Room values('323',2,4,1,'还可以哈')
106insert into Room values('324',2,4,1,'还可以哈')
107insert into Room values('325',2,4,1,'还可以哈')
108insert into Room values('326',2,4,1,'还可以哈')
109insert into Room values('327',2,4,1,'还可以哈')
110insert into Room values('328',2,4,1,'还可以哈')
111insert into Room values('329',3,5,1,'还可以哈')
112insert into Room values('330',3,5,1,'还可以哈')
113insert into Room values('331',3,5,1,'还可以哈')
114insert into Room values('332',3,5,1,'还可以哈')
115insert into Room values('333',4,6,1,'还可以哈')
116insert into Room values('334',4,6,1,'还可以哈')
117insert into Room values('335',4,6,1,'还可以哈')
118go
119---------------------------------------------------------------------------------------
120/**//*开房信息表*/
121create table OpenRoomInfo
122(
123    OpenRoomId int identity(1,1) primary key, --开房Id
124    RoomId int foreign key references Room(RoomId) not null, --房间Id
125    GuestNumber varchar(100) not null, --客人的身份证号
126    GuestName varchar(50) not null, --客人姓名
127    GuestMoney money not null, --预交金额
128    OpenTime datetime default(getdate()) not null, --开房时间
129    Remark varchar(200) --备注
130)
131go
132---------------------------------------------------------------------------------------
133/**//*酒店收入表*/
134create table MoneyInfo
135(
136    MoneyInfoId int identity(1,1) primary key, --收入Id
137    OpenTime datetime not null, --开房时间
138    RoomNumber varchar(20) not null, --房间号,
139    GuestNumber varchar(100) not null, --登记身份证
140    GuestName varchar(50) not null, --登记姓名,
141    MoneyDate datetime default(getdate()) not null, --账目时间(其实就是退房时间)
142    DetailsMoney money check(DetailsMoney>0) not null --账目金额
143)
144go
145insert into MoneyInfo values('2008-05-19 15:17:18.623','302','12345','chen','2008-05-20 15:15:13:123',50.000)
146insert into MoneyInfo values('2008-05-19 18:17:18.623','303','54321','aaaa','2008-05-20 16:15:13:123',50.000)
147insert into MoneyInfo values('2008-05-20 11:11:18.663','311','97854','bbbb','2008-05-21 09:10:13:123',70.000)
148insert into MoneyInfo values('2008-05-20 15:17:18.623','313','12345','cccc','2008-05-21 13:13:13:123',90.000)
149insert into MoneyInfo values('2008-05-23 13:09:20.623','305','32184','dddd','2008-05-24 06:06:23:123',50.000)
150insert into MoneyInfo values('2008-05-27 22:02:15.233','319','05252','eeee','2008-05-28 05:03:13:123',90.000)
151insert into MoneyInfo values('2008-05-30 21:21:22.623','320','52325','ffff','2008-06-01 09:22:32:123',90.000)
152insert into MoneyInfo values('2008-06-05 20:30:10.665','315','45352','gggg','2008-06-06 11:22:33:123',90.000)
153insert into MoneyInfo values('2008-06-11 19:55:25.623','323','65852','hhhh','2008-06-12 10:15:13:123',120.000)
154insert into MoneyInfo values('2008-06-23 23:45:20.623','301','85236','jjjj','2008-06-24 08:45:13:123',50.000)
155insert into MoneyInfo values('2008-07-29 20:35:35.623','314','17455','llll','2008-07-30 08:36:13:123',90.000)
156insert into MoneyInfo values('2008-08-22 21:13:18.623','325','32512','rrrr','2008-08-23 10:10:15:123',120.000)
157go
158--------------------------------------是否表存储过程------------------------------------------
159/**//*查询所有*/
160create proc GetIsAll
161as
162    select * from IsTable
163go
164--------------------------------------用户表存储过程------------------------------------------
165/**//*用户登录*/
166create proc GetLogin(@LoginName varchar(50),@LoginPass varchar(50))
167as
168    select LoginName,LoginPass from UserInfo
169        where LoginName = @LoginName and LoginPass = @LoginPass
170go
171---------------------------------------------------------------------------------------
172/**//*添加普通员工*/
173create proc AddUser(@LoginName varchar(50),@LoginPass varchar(50),
174                @UserName varchar(50),@Remark varchar(200))
175as
176    insert into UserInfo values(@LoginName,@LoginPass,@UserName,@Remark)
177go
178---------------------------------------------------------------------------------------
179/**//*删除员工(根据Id删除)*/
180create proc DelUser(@UserId int)
181as
182    delete from UserInfo where UserId = @UserId
183go
184---------------------------------------------------------------------------------------
185