电子科技大学《数据库原理及应用》MySQL实验参考

---

SQL文件包含的主要内容

各表简要说明：
1. customer表：包含顾客ID与顾客姓名
2. hotel表：包含酒店ID与酒店名称
3. room_type表：包含房型ID，房型名称与对应的酒店价格
4. room_info表：包含房间信息ID、日期、当前日期对应的价格、剩余数量和对应的房型ID
5. order表：订单ID，订单对应的房型ID，入店日期，离店日期，预定数量，订单价格，订单创建日期，发起订单的顾客ID
6. rating表：评价ID，评价分数，评价对应的订单ID，发起评价的顾客ID

---

实验内容

1. 查询所有房型的具体信息，包括room_id, Room_name, hotel_id.

select * 
from room_type;

2. 查询所有酒店名称中包含“希尔顿”的酒店，返回酒店名称和酒店id.

select hotel_name,hotel_id
from hotel
where hotel_name like '希尔顿%';

3. 查询订单总价在10000元及以上的所有订单详情，包括订单编号、酒店编号、房型编号及居住时长.

select order_id,hotel_id,(leave_date-start_date+1) 居住天数,`order`.room_id
from `order`,room_type
where `order`.room_id = room_type.room_id and payment >= 10000;

4. 查询所有房型的订单情况，包括房型编号，房型名称，订单编号、价格.

SELECT room_id,room_name,order_id,payment from room_type natural join dbexperiment.order
union
select room_id,room_name,null,null 
from room_type 
where room_id not in(select distinct room_id from dbexperiment.order);

5. 创建启悦酒店的订单视图.

create view QiYueHotel
as
select order_id, room_type.room_id, start_date, leave_date, amount, payment, create_date, customer_id
from dataexperiment.order, hotel, room_type
where dataexperiment.order.room_id = room_type.room_id
	and hotel.hotel_id = room_type.hotel_id
	and hotel.hotel_name = '启悦酒店'

6. 在订单表的总价字段上 创建降序的普通索引，索引名为orderpayment.

drop index orderpayment 
on `order`;
create index orderpayment
on `order` (payment desc);

7. 查询所有酒店2020-11-14所有房型的平均价格并从低到高排序.

select hotel_name, room_info.date, avg(price) avgprice
from room_info
	NATURAL JOIN room_type NATURAL JOIN hotel
where room_info.date = '2020-11-14'
group by hotel_name, date
order by avgprice;

8. 从订单表中统计希尔顿大酒店2020-11-14当天各个房型预定情况，返回酒店名，房型，预定数量.

select hotel_name, room_name, amount
from `order`
	NATURAL JOIN room_type NATURAL JOIN hotel
where start_date = '2020-11-14' and hotel_name = '希尔顿大酒店';

9. 查找同时评价了2次及以上的用户信息.

select customer.uID,customer.uName,count(rating.uID) Rating_Times
from customer,rating
where rating.uID=customer.uID
group by rating.uID
having Rating_Times>=2;

10. 查询评价过所有总统套房的顾客姓名.

select distinct customer.uname 
from customer,rating,`order` ,room_type 
where customer.uID = rating.uID 
	and rating.order_id = `order` .order_id 
    and room_type.room_id=`order` .room_id
    and	room_name="总统套房"
group by rating.uID
having count(distinct room_type.room_id)>=(select count(*) from room_type where room_name="总统套房");

11. 指定时间区间和每天要预定的房间数量，查询满足条件“预定11.14-16日每天房间数量4间”（时间区间，将预定房间数）的房型及其平均价格，并按平均价格从低到高进行排序。查询结果应包含酒店，房型及平均价格信息.

select hotel_name, room_name, avg(price) avgprice
from hotel
	NATURAL JOIN room_type NATURAL JOIN room_info
where date <= '2020-11-16' and date >= '2020-11-14'
group by room_id
having min(remain) >= 4
order by avgprice;

12. 更新预订房间，包括更新房型信息和创建订单。更新信息为：订单为预订11月14号-15号4号房型4间。

insert into `order`
values (null, 4, '2020-11-14', '2020-11-15', 4
	, 4 * (
		select price
		from room_info
		where room_id = 4
			and date = '2020-11-14'
	), '2020-11-01', 201901);

update room_info
set remain = remain - 4
where room_id = 4
	and date between '2020-11-14' and '2020-11-15';

SQL源文件获取地址

链接：https://pan.baidu.com/s/1sD2fKcWJdwQsmzzgoFm08Q 提取码：a751