📄 hotel.sql
字号:
------------------------------------------------------------------------------------------------------------
----------Hotel数据库的创建
use master
go
if exists (select * from dbo.sysdatabases where name = 'Hotel')
drop database Hotel
GO
create database Hotel
go
---------------------------------------------------------------------------------------------------------------
--otel数据库的入住客户表(Customer),入住客户记录表(CustomerRecord)，客房信息表(Room)，客房类型表(RoomType)的创建。
use Hotel
go
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.Customer') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Customer
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CustomerRecord') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.CustomerRecord
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.Room') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.Room
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.RoomType') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.RoomType
GO
CREATE TABLE dbo.Customer (
CustomerID char (18) primary key ,
CustomerName varchar (20) not null ,
Sex char(2) null ,
NativePlace varchar (50) null,
HasRecord char (20) not null
)
GO
CREATE TABLE dbo.CustomerRecord (
CustomerID char (18) primary key,
RoomID char (4) not null,
InDate datetime not null,
CheckDate datetime null,
TotalMoney money null,
Remark varchar (50) null
)
GO
CREATE TABLE dbo.Room (
RoomID char (4) primary key,
RoomTypeName varchar (20) not null,
RoomPosition varchar (20) not null,
HoldPeopeleNum smallint not null,
BedNum smallint not null,
FactPeopleNum smallint not null,
Remark varchar (50) null
)
GO
CREATE TABLE dbo.RoomType (
RoomTypeName varchar (20) not null,
Area smallint not null,
BedNum smallint not null,
Price money not null,
HasBreakfast bit not null,
AirCondition bit not null,
HasPhone bit not null,
HasTelevision bit not null,
HasToilet bit not null,
Remark varchar (50) null
)
GO
use Hotel
INSERT INTO dbo.Customer
VALUES('001106','田慧','女',null,'入住')
INSERT INTO dbo.Customer
VALUES('001107','李小玉','女',null,'入住')
INSERT INTO dbo.Customer
VALUES('001023','许艳波','女',null,'入住')
INSERT INTO dbo.Customer
VALUES('002158','刘玉佳','女',null,'预定')
INSERT INTO dbo.Customer
VALUES('003023','宋欢','男',null,'入住')
INSERT INTO dbo.Customer
VALUES('004124','曹会','男',null,'入住')
INSERT INTO dbo.Customer
VALUES('005201','王兵兵','男',null,'预定')
INSERT INTO dbo.Customer
VALUES('006210','沈茹','女',null,'入住')
INSERT INTO dbo.Customer
VALUES('007520','孟秀丽','女',null,'入住')
INSERT INTO dbo.Customer
VALUES('008212','陈帅','男',null,'入住')
GO
USE Hotel
INSERT INTO dbo.CustomerRecord
VALUES('001023','1001','2007-7-10','2007-7-15',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('002111','1002','2007-11-21','2007-11-26',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('003023','1003','2007-7-1','2007-9-30',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('004124','1034','2007-11-21','2007-11-25',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('005201','1023','2007-8-2','2007-11-1',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('006210','1025','2007-8-2','2007-11-1',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('007520','2067','2007-4-12','2007-4-15',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('008212','2099','2007-4-12','2007-7-14',null,null)
INSERT INTO dbo.CustomerRecord
VALUES('001106','1022','2007-5-26','2007-8-25',null,null)
GO
USE Hotel
INSERT INTO dbo.Room
VALUES('1001','普通','1#2',4,4,2,null)
INSERT INTO dbo.Room
VALUES('1002','普通','1#2',4,4,1,null)
INSERT INTO dbo.Room
VALUES('1003','普通','1#2',4,4,2,null)
INSERT INTO dbo.Room
VALUES('1034','标准','1#1',2,2,2,null)
INSERT INTO dbo.Room
VALUES('1023','标准','1#1',2,2,1,null)
INSERT INTO dbo.Room
VALUES('1025','标准','1#1',2,2,1,null)
INSERT INTO dbo.Room
VALUES('2067','贵宾','1#5',2,2,1,null)
INSERT INTO dbo.Room
VALUES('2099','贵宾','1#5',2,2,1,null)
INSERT INTO dbo.Room
VALUES('1022','标准','1#1',4,4,4,null)
go
USE Hotel
INSERT INTO dbo.RoomType
VALUES('普通',20,4,40,1,1,1,0,1,null)
INSERT INTO dbo.RoomType
VALUES('标准',40,2,80,1,1,1,1,1,null)
INSERT INTO dbo.RoomType
VALUES('贵宾',100,2,160,1,1,1,1,1,null)
go
--------------------------------------------------------------------------------------------------------
-----------客户入住信息视图(View_CustomerRecord) SQL 脚本
use Hotel
go
if exists(select name from sysobjects where name='View_CustomerRecord'and type='v')
drop view View_CustomerRecord
GO
create view View_CustomerRecord(CustomerName,HasRecord,RoomTypeName,Price,CustomerID,RoomID,
InDate,CheckDate,TotalMoney)
as
select dbo.Customer.CustomerName,dbo.Customer.HasRecord,dbo.Room.RoomTypeName,
dbo.RoomType.Price,dbo.CustomerRecord.CustomerID,dbo.CustomerRecord.RoomID,
dbo.CustomerRecord.InDate,dbo.CustomerRecord.CheckDate,dbo.CustomerRecord.TotalMoney
from dbo.CustomerRecord inner join
dbo.Customer on
dbo.CustomerRecord.CustomerID=dbo.Customer.CustomerID inner join
dbo.Room on dbo.CustomerRecord.RoomID=dbo.Room.RoomID inner join
dbo.RoomType on dbo.Room.RoomTypeName=dbo.RoomType.RoomTypeName
go
---------------------------------------------------------------------------------------------------------
---客房价格视图(View_RoomPrice) SQL 脚本
use Hotel
go
if exists(select name from sysobjects where name='View_RoomPrice'and type='v')
drop view View_RoomPrice
GO
create view View_RoomPrice(RoomID,RoomTypeName,Price )
as
select dbo.Room.RoomID,dbo.Room.RoomTypeName,dbo.RoomType.Price
from dbo.Room inner join
dbo.RoomType on dbo.Room.RoomTypeName=dbo.RoomType.RoomTypeName
go
------------------------------------------------------------------------------------------------------------
-------添加入住客户(ProcedureAddCustomer)的 SQL 脚本
create procedure dbo.ProcedureAddcustomer
(
@roomID char(4)
)
as
update Room set FactPeopleNum=FactPeopleNum+1 where RoomID=@roomID
go
-----------------------------------------------------------------------------------------------------------
---------减少入住客户(ProcedureMinusCustomer)的SQL脚本
create procedure dbo.ProcedureMinusCuatomer
(
@roomID char (4)
)
as
Update Room set FactPeopleNum=FactPeopleNum-1 where RoomID=@roomID
go
------------------------------------------------------------------------------------------------------------
----------结算总金额(TotalMoney) 的SQL脚本
use Hotel
declare @days char(4),@price money
set @days=(select datediff(dd,InDate,CheckDate) from View_CustomerRecord)
set @price=(select price from View_CustomerRecord where CustomerID='001023')
update CustomerRecord
set TotalMoney=@price*@days
where CustomerID='001023'
--------------------------------------------------------------------------------------------------------------
------------客户查询事件SQL代码
select CustomerID 客户ID,CustomerName 客户名称,RoomID 房间号,RoomTypeName 房间类型,Price 价钱,InDate 入住日期,CheckDate 结算日期,TotalMoney 总金额
from View_CustomerRecord
---------------------------------------------------------------------------------------------------------------
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -