hotel.sql

酒店管理系统项目需求说明 第一部分 引言 1 1.1编写目的 1 1.2背景 1 第二部分 任务概述 2 3.1实现目标 2 3.1.1客房类型模块： 2 3.1.2客房信息

if exists (select * from sysdatabases where name='Hotel')
	drop database Hotel
exec xp_cmdshell ' md E:\HotelDB'
create database Hotel
on primary 
(
	name='Hotel_dat',
	filename='E:\HotelDB\Hotel_dat.mdf'
)
log on
(
	name='Hotel_log',
	filename='E:\HotelDB\Hotel_log.ldf'
)
go
use Hotel
go
create table RoomType
(
	TypeID int identity(1,1) primary key not null,
	TypeName nvarchar(50) unique not null, --客房类型名称
	TypePrice money not null, --客房类型价格
	IsAddBed nchar(1) not null,--是否可以加床
	AddBedPrice money not null,--加床价格
	Remark nvarchar(50) not null,--备注
	imageurl nvarchar(50) not null
)
create table Room
(
	RoomID int identity(1,1) primary key not null,
	Number nvarchar(50) unique not null,--房间号
	BedNumber int not null,--床位数
	Description nvarchar(255) not null,--客房描述
	State nvarchar(50) not null,--客房状态，入住，空闲，维修
	GuestNumber int not null,--入住人数
	TypeID int foreign key references RoomType(TypeID) not null,
)


create table HotelUser
(
	UserID int identity(1,1) primary key not null,
	UserName nvarchar(50) unique not null,--用户名
	Password nvarchar(50) not null,--密码
	Name nvarchar(50) not null,
	Status nvarchar(50) not null default(0),--1管理员    0普通用户
	Address nvarchar(50),
	Phone nvarchar(50),
	Email nvarchar(50),
)

create table UserRoom
(
	UserID int foreign key references HotelUser(UserID) not null,
	RoomID int foreign key references Room(RoomID) not null,
	State nvarchar(50)
)

create table Message
(
	UserID int foreign key references HotelUser(UserID) not null,
	Message nvarchar(500) not null,
	MTime datetime default(getdate())
)

drop table message
drop table UserRoom
drop table HotelUser
drop table room
drop table roomtype
go

insert into HotelUser values('admin','123','哈哈','1','珠海','123456','[e-mail]')
insert into HotelUser values('aaa','aaa','小红','0','北京','456789','[e-mail]')
insert into HotelUser values('sss','sss','小新','0','上海','789123','[e-mail]')
insert into HotelUser values('ddd','ddd','小张','0','深圳','123132','[e-mail]')


insert into roomtype values('标间',60,'是',20,'很不错的房子','~/Image2/1.jpg')
insert into roomtype values('豪华间',120,'否',0,'这间房最好','~/Image2/2.jpg')
insert into roomtype values('总统套房',110,'是',40,'哇塞','~/Image2/3.jpg')
insert into roomtype values('商业房',50,'是',10,'很好的哦','~/Image2/4.jpg')

insert into room values('1001',3,'很好','空房',3,4)
insert into room values('1002',3,'不错','空房',3,1)
insert into room values('1003',5,'很好','维修',0,2)
insert into room values('1004',6,'不错','空房',3,3)
insert into room values('1005',7,'很好','空房',0,2)

insert into message (userid,message) values(3,'你那房子都没厕所的啊')
insert into userroom values(2,3,'入住')
insert into userroom values(3,5,'已预订')

select * from roomtype
select * from room
select * from Hoteluser
select * from userroom
select * from message

delete userroom
update room set state = '空房' where number = '1002'
delete room
delete from roomtype where typeid = 7
delete from message




select message.*,hoteluser.username from message inner join hoteluser on message.userid=hoteluser.userid

create view view_report
as
SELECT dbo.HotelUser.UserName, dbo.Room.Number, dbo.RoomType.TypeName, 
      dbo.RoomType.TypePrice, dbo.RoomType.AddBedPrice,dbo.RoomType.TypePrice+dbo.RoomType.AddBedPrice as tatal
FROM dbo.RoomType INNER JOIN
      dbo.Room ON dbo.RoomType.TypeID = dbo.Room.TypeID INNER JOIN
      dbo.UserRoom ON dbo.Room.RoomID = dbo.UserRoom.RoomID INNER JOIN
      dbo.HotelUser ON dbo.UserRoom.UserID = dbo.HotelUser.UserID