?? 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 + -