1if exists (select * from sysdatabases where name='Hotel')
  2    drop database Hotel
  3exec xp_cmdshell ' md E:\HotelDB'
  4create database Hotel
  5on primary 
  6(
  7    name='Hotel_dat',
  8    filename='E:\HotelDB\Hotel_dat.mdf'
  9)
 10log on
 11(
 12    name='Hotel_log',
 13    filename='E:\HotelDB\Hotel_log.ldf'
 14)
 15go
 16use Hotel
 17go
 18create table RoomType
 19(
 20    TypeID int identity(1,1) primary key not null,
 21    TypeName nvarchar(50) unique not null, --客房类型名称
 22    TypePrice money not null, --客房类型价格
 23    IsAddBed nchar(1) not null,--是否可以加床
 24    AddBedPrice money not null,--加床价格
 25    Remark nvarchar(50) not null,--备注
 26    imageurl nvarchar(50) not null
 27)
 28create table Room
 29(
 30    RoomID int identity(1,1) primary key not null,
 31    Number nvarchar(50) unique not null,--房间号
 32    BedNumber int not null,--床位数
 33    Description nvarchar(255) not null,--客房描述
 34    State nvarchar(50) not null,--客房状态，入住，空闲，维修
 35    GuestNumber int not null,--入住人数
 36    TypeID int foreign key references RoomType(TypeID) not null,
 37)
 38
 39
 40create table HotelUser
 41(
 42    UserID int identity(1,1) primary key not null,
 43    UserName nvarchar(50) unique not null,--用户名
 44    Password nvarchar(50) not null,--密码
 45    Name nvarchar(50) not null,
 46    Status nvarchar(50) not null default(0),--1管理员    0普通用户
 47    Address nvarchar(50),
 48    Phone nvarchar(50),
 49    Email nvarchar(50),
 50)
 51
 52create table UserRoom
 53(
 54    UserID int foreign key references HotelUser(UserID) not null,
 55    RoomID int foreign key references Room(RoomID) not null,
 56    State nvarchar(50)
 57)
 58
 59create table Message
 60(
 61    UserID int foreign key references HotelUser(UserID) not null,
 62    Message nvarchar(500) not null,
 63    MTime datetime default(getdate())
 64)
 65
 66drop table message
 67drop table UserRoom
 68drop table HotelUser
 69drop table room
 70drop table roomtype
 71go
 72
 73insert into HotelUser values('admin','123','哈哈','1','珠海','123456','zhuhai@123.com')
 74insert into HotelUser values('aaa','aaa','小红','0','北京','456789','beijing@123.com')
 75insert into HotelUser values('sss','sss','小新','0','上海','789123','shanghai@123.com')
 76insert into HotelUser values('ddd','ddd','小张','0','深圳','123132','shenzhen@123.com')
 77
 78
 79insert into roomtype values('标间',60,'是',20,'很不错的房子','~/Image2/1.jpg')
 80insert into roomtype values('豪华间',120,'否',0,'这间房最好','~/Image2/2.jpg')
 81insert into roomtype values('总统套房',110,'是',40,'哇塞','~/Image2/3.jpg')
 82insert into roomtype values('商业房',50,'是',10,'很好的哦','~/Image2/4.jpg')
 83
 84insert into room values('1001',3,'很好','空房',3,4)
 85insert into room values('1002',3,'不错','空房',3,1)
 86insert into room values('1003',5,'很好','维修',0,2)
 87insert into room values('1004',6,'不错','空房',3,3)
 88insert into room values('1005',7,'很好','空房',0,2)
 89
 90insert into message (userid,message) values(3,'你那房子都没厕所的啊')
 91insert into userroom values(2,3,'入住')
 92insert into userroom values(3,5,'已预订')
 93
 94select * from roomtype
 95select * from room
 96select * from Hoteluser
 97select * from userroom
 98select * from message
 99
100delete userroom
101update room set state = '空房' where number = '1002'
102delete room
103delete from roomtype where typeid = 7
104delete from message
105
106
107
108
109select message.*,hoteluser.username from message inner join hoteluser on message.userid=hoteluser.userid
110
111create view view_report
112as
113SELECT dbo.HotelUser.UserName, dbo.Room.Number, dbo.RoomType.TypeName, 
114      dbo.RoomType.TypePrice, dbo.RoomType.AddBedPrice,dbo.RoomType.TypePrice+dbo.RoomType.AddBedPrice as tatal
115FROM dbo.RoomType INNER JOIN
116      dbo.Room ON dbo.RoomType.TypeID = dbo.Room.TypeID INNER JOIN
117      dbo.UserRoom ON dbo.Room.RoomID = dbo.UserRoom.RoomID INNER JOIN
118      dbo.HotelUser ON dbo.UserRoom.UserID = dbo.HotelUser.UserID
119