《数据库原理及应用》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
实验内容
- 查询所有房型的具体信息,包括room_id, Room_name, hotel_id.
select *
from room_type;
- 查询所有酒店名称中包含“希尔顿”的酒店,返回酒店名称和酒店id.
select hotel_name,hotel_id
from hotel
where hotel_name like '希尔顿%';
- 查询订单总价在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;
- 查询所有房型的订单情况,包括房型编号,房型名称,订单编号、价格.
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);
- 创建启悦酒店的订单视图.
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 = '启悦酒店'
- 在订单表的总价字段上 创建降序的普通索引,索引名为orderpayment.
drop index orderpayment
on `order`;
create index orderpayment
on `order` (payment desc);
- 查询所有酒店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;
- 从订单表中统计希尔顿大酒店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 = '希尔顿大酒店';
- 查找同时评价了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;
- 查询评价过所有总统套房的顾客姓名.
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.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;
- 更新预订房间,包括更新房型信息和创建订单。更新信息为:订单为预订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