数据库第八次作业建表，存储过程和视图举例

1）用DDL写出Booking表的定义，包括主键，外键，域约束。**

主键由三个字段组成，外键来自多个表，分开写更清晰，
域约束用check来限制，另外一种方法是用Domain，在建表之前就限制

CREATE DOMAIN DateFrom AS DATE CHECK (dateFrom>=CURRENT_DATE); 
CREATE DOMAIN DateTo AS DATE CHECK(dateTo>dateFrom);
CREATE TABLE Booking
(
hotelNo INT NOT NULL,
guestNo INT NOT NULL,
dateFrom DateFrom NOT NULL ,
dateTo DateTo ,
roomNo INT NOT NULL,
PRIMARY KEY(hotelNo,roomNo,dateFrom),
FOREIGN KEY(hotelNo) REFERENCES hotel(holtelNo)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY(guestNo) REFERENCES guest(guestNo)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY(roomNo) REFERENCES room(roomNo)
ON DELETE NO ACTION ON UPDATE CASCADE
);

由于没有其他表，暂时无法写外键语句

但是，在mysql中也可以插入不符合条件的三个语句，也就是会出现dateTo<dateFrom,dateFrom<CURRENT_DATE的情况


查过发现
mysql所有的存储引擎均不支持check约束，MySQL会对check子句进行分析，但是在插入数据时会忽略，因此check并不起作用，因此实现对数据约束有两种方法：

1.在mysql种约束，如使用enum类型或者触发器等。
2.在应用程序里面对数据进行检查再插入。

2）写出一个存储过程，求某个时间段（住宿时间），某个宾馆，某种房间可预订的房间号码；

传入四个参数，注意dateFrom和dateTo的大小关系即可

DELIMITER //
CREATE PROCEDURE telnumber (
IN hotelNo INT,
IN type VARCHAR(1),
IN dateFrom DATE,
IN dateTo DATE
)
BEGIN
SELECT roomNo FROM room  
WHERE hotelNo=@hotelNo AND type=@type AND roomNo NOT IN(
SELECT roomNo FROM Booking WHERE hotelNo=@hotelNo AND dateFrom<=@dateTo AND dateTo>=dateFrom);
END //
DELIMITER ;

注意：另一种关于参数的写法

@hotelNo IN INT,
@type IN VARCHAR,
@dateFrom IN DATE,
@dateTo IN DATE

是sqlserver的写法,mysql中识别不了会报错

3）写出一个视图，求2018年11月shelldon酒店每个房间利润从大到小排列

说明：
1每个房间可能被多次预定
2预定跨月的房间按dateFrom为主

CREATE VIEW profit_sigroom(roomNo,NoverProfit) AS
SELECT roomNo,SUM(price) 
FROM hotel h,room r,booking b
WHERE h.hotelname=’shelldon’ AND h.hotelNo=r.hotelNo AND h.hotelNo=b.hotelNo
AND dateFrom>=’2018-11-01’ AND dateFrom<=’2018-11-30’
GROUP BY roomNo DESC;