hoteldb.sql

酒店管理系统 用C#开发 程序简单明了 应用系统

use master
go
if exists(select * from sysdatabases where name='HotelDB')
	drop database HotelDB
go
create database HotelDB
go
use HotelDB
go

if exists (select * from sysobjects where id = OBJECT_ID('[HotelUser]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [HotelUser]

CREATE TABLE [HotelUser] (
[UserID] [int]  IDENTITY (1, 1)  NOT NULL,
[UserName] [nvarchar]  (100) NOT NULL,
[Password] [nvarchar]  (100) NOT NULL)

ALTER TABLE [HotelUser] WITH NOCHECK ADD  CONSTRAINT [PK_HotelUser] PRIMARY KEY  NONCLUSTERED ( [UserID] )SET IDENTITY_INSERT [HotelUser] ON

INSERT [HotelUser] ([UserID],[UserName],[Password]) VALUES ( 1,'admin','D033E22AE348AEB5660FC2140AEC35850C4DA997')

SET IDENTITY_INSERT [HotelUser] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Room]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [Room]

CREATE TABLE [Room] (
[RoomID] [int]  IDENTITY (1, 1)  NOT NULL,
[Number] [nvarchar]  (100) NOT NULL,
[TypeID] [int]  NOT NULL,
[BedNumber] [int]  NOT NULL,
[Description] [nvarchar]  (510) NOT NULL,
[State] [nvarchar]  (100) NOT NULL,
[GuestNumber] [int]  NOT NULL)

ALTER TABLE [Room] WITH NOCHECK ADD  CONSTRAINT [PK_Room] PRIMARY KEY  NONCLUSTERED ( [RoomID] )SET IDENTITY_INSERT [Room] ON

INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 9,'302',5,2,'总统套房内设宴会厅，多功能厅','modify',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 10,'303',8,8,'总统套房内设宴会厅，多功能厅','modify',6)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 11,'304',5,2,'总统套房内设宴会厅，多功能厅!!!!!!!!!!!!!','helpOneself',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 12,'305',6,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 13,'306',6,6,'总统套房内设宴会厅，多功能厅','helpOneself',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 14,'307',6,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 15,'308',6,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 16,'309',6,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 17,'401',6,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 18,'402',6,2,'总统套房内设宴会厅，多功能厅','modify',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 19,'403',6,2,'总统套房内设宴会厅，多功能厅','empty',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 22,'406',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 24,'408',10,2,'总统套房内设宴会厅，多功能厅','modify',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 25,'409',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 26,'501',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 27,'502',8,2,'总统套房内设宴会厅，多功能厅','housing',20)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 28,'503',8,2,'总统套房内设宴会厅，多功能厅','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 29,'504',8,2,'总统套房内设宴会厅，多功能厅','modify',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 30,'505',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 31,'506',8,2,'总统套房内设宴会厅，多功能厅','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 32,'507',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 33,'508',8,2,'总统套房内设宴会厅，多功能厅','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 34,'509',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 35,'601',8,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 36,'602',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 37,'603',10,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 38,'604',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 39,'605',10,2,'总统套房内设宴会厅，多功能厅','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 40,'606',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 41,'607',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 42,'608',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 43,'609',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 44,'701',10,2,'总统套房内设宴会厅，多功能厅','helpOneself',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 45,'702',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 46,'703',10,2,'太贵了！也不打个折！','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 47,'704',10,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 48,'705',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 49,'706',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 50,'707',10,2,'总统套房内设宴会厅，多功能厅','helpOneself',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 51,'708',10,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 52,'709',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 53,'801',1,2,'总统套房内设宴会厅，多功能厅','empty',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 54,'802',1,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 55,'803',1,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 56,'804',1,2,'总统套房内设宴会厅，多功能厅','leave',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 57,'805',1,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 58,'806',1,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 59,'807',10,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 60,'808',10,2,'总统套房内设宴会厅，多功能厅','housing',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 61,'809',10,2,'总统套房内设宴会厅，多功能厅','arrive',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 66,'206',8,4,'太贵了','empty',4)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 67,'B206',8,4,'太贵了','leave',4)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 69,'B201',5,2,'老总','modify',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 70,'B2001',6,2,'这类房间适合普通的工薪阶层!','empty',2)
INSERT [Room] ([RoomID],[Number],[TypeID],[BedNumber],[Description],[State],[GuestNumber]) VALUES ( 71,'333',1,10,'aaaaa','empty',10)

SET IDENTITY_INSERT [Room] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[RoomType]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [RoomType]

CREATE TABLE [RoomType] (
[TypeID] [int]  IDENTITY (1, 1)  NOT NULL,
[TypeName] [nvarchar]  (100) NOT NULL,
[TypePrice] [money]  NOT NULL,
[AddBedPrice] [money]  NOT NULL,
[IsAddBed] [nchar]  NOT NULL,
[Remark] [nvarchar]  (510) NULL)

ALTER TABLE [RoomType] WITH NOCHECK ADD  CONSTRAINT [PK_RoomType] PRIMARY KEY  NONCLUSTERED ( [TypeID] )SET IDENTITY_INSERT [RoomType] ON

INSERT [RoomType] ([TypeID],[TypeName],[TypePrice],[AddBedPrice],[IsAddBed],[Remark]) VALUES ( 1,'标间',65.0000,20.0000,'是','没有独立卫生间!!')
INSERT [RoomType] ([TypeID],[TypeName],[TypePrice],[AddBedPrice],[IsAddBed],[Remark]) VALUES ( 5,'套间',800.0000,80.0000,'是','包括两个卧室')
INSERT [RoomType] ([TypeID],[TypeName],[TypePrice],[AddBedPrice],[IsAddBed],[Remark]) VALUES ( 6,'豪华间',260.0000,160.0000,'是','数量有限抓紧预订！！')
INSERT [RoomType] ([TypeID],[TypeName],[TypePrice],[AddBedPrice],[IsAddBed],[Remark]) VALUES ( 8,'贵宾间',600.0000,0.0000,'否','数量有限抓紧预订！！')
INSERT [RoomType] ([TypeID],[TypeName],[TypePrice],[AddBedPrice],[IsAddBed],[Remark]) VALUES ( 10,'总统套房',2600.0000,10.0000,'是','数量有限抓紧预订！！')

SET IDENTITY_INSERT [RoomType] OFF

select * from Room
select * from RoomType
go
create view view_select
as
	select a.*,b.TypeName
	from Room as a,RoomType as b
	where a.TypeId=b.TypeID
go

select * from view_select
go
create view view_page
as
	select a.RoomID,a.Number,b.TypeName,a.State,a.BedNumber,a.GuestNumber,a.Description
	from Room as a,RoomType as b
	where a.TypeId=b.TypeID
go

select * from view_page
select * from RoomType
select * from Room