抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

实验1-hotel数据库

发布于：2025年5月1日

字数：1.5k 字

时长：7 分钟

某酒店预定系统的数据库中包含以下数据表，具体信息如下：

  • customer表：包含顾客id和顾客名字

    image-20241116104403890

  • hotel表：包含酒店id和酒店名

    image-20241116104429818

  • room_type表：包含房型id，房型名，对应的酒店id

    image-20241116104455095

  • room_info表：房间信息id，日期，当前日期价格，剩余数量，对应房型id

    image-20241116104819762

  • hotel_order表：订单id，订单对应的房型id，入店日期，离店日期，预定数量，订单价格，订单创建日期，发起订单的顾客id

    image-20241116104539453

  • rating表：评价id，评价分数，评价对应的订单id，发起评价的顾 客id

    image-20241116104601630

P1

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

SELECT *
FROM room_type;

image-20241116102940234

P2

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

SELECT *
FROM hotel
WHERE hotel_name LIKE '%希尔顿%';

image-20241116103002070

P3

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

SELECT hotel_order.order_id,
    hotel.hotel_id,
    hotel_order.room_id,
    (hotel_order.leave_date - hotel_order.start_date) AS period
FROM hotel_order
LEFT OUTER JOIN room_type ON room_type.room_id = hotel_order.room_id
LEFT OUTER JOIN hotel ON hotel.hotel_id = room_type.hotel_id
WHERE hotel_order.payment >= 9000;

image-20241116105156536

P4

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

SELECT hotel_order.room_id,
    room_type.room_name,
    hotel_order.order_id,
    hotel_order.payment
FROM hotel_order
    LEFT OUTER JOIN room_type ON room_type.room_id = hotel_order.room_id;

image-20241116103110873

P5

创建启悦酒店的订单视图。

CREATE OR REPLACE VIEW qiyue_hotel AS
SELECT hotel_order.*
FROM hotel_order
    LEFT OUTER JOIN room_type ON room_type.room_id = hotel_order.room_id
    LEFT OUTER JOIN hotel ON hotel.hotel_id = room_type.hotel_id
WHERE hotel.hotel_name = '启悦酒店';

image-20241116103144331

P6

在订单表的总价字段上创建降序的普通索引。索引名为 orderpayment. 用\di 命令查看创建的索引。

CREATE INDEX order_payment ON hotel_order(payment DESC);

image-20241116105540660

P7

创建函数：查询给定日期，给定酒店所有房型的平均价格。 执行函数，输入参数为2020-11-14，希尔顿大酒店

CREATE OR REPLACE FUNCTION get_average_price_on_date_hotel(DATE, VARCHAR(255))
RETURNS DECIMAL(10, 2)
AS $
    DECLARE average_price DECIMAL(10, 2);

    BEGIN
        SELECT AVG(room_info.price) INTO average_price
        FROM room_info
            LEFT OUTER JOIN room_type ON room_type.room_id = room_info.room_id
            LEFT OUTER JOIN hotel ON hotel.hotel_id = room_type.hotel_id
        WHERE room_info.date = $1
            AND hotel.hotel_name = $2;
        RETURN average_price;
    END
$ LANGUAGE plpgsql;

SELECT get_average_price_on_date_hotel('2020-11-14', '希尔顿大酒店') AS avg_price;

image-20241116103301111

P8

创建存储过程：从订单表中统计指定酒店、指定日期的各种房型 的预订情况，返回酒店名，房型，预定数量。执行存储过程：统计希尔顿大酒店2020-11-14当天各个房型预定情况

CREATE OR REPLACE PROCEDURE
getOrderSitu(para1 date, para2 cvarchar(255)) as 
BEGIN

SELECT hotel.hotel_name, room_type.room_name,SUM(hotel_order.amount)
FROM hotel, room_type, hotel_order
WHERE hotel.hotel_id=room_type.hotel_id AND room_type_room_id=hotel_order_id AND hotel_order.start_date=para1 AND hotel.hotel_name=para2
GROUP BY hotel.hotel_name, room_type.room_name;
END;

call getOrderSitu(para1:'2020-11-14', para2:'希尔顿大酒店');

CREATE OR REPLACE PROCEDURE get_room_type_order_on_date_hotel(para1 DATE, para2 VARCHAR(255))
AS
    CURSOR c IS
        SELECT FIRST(hotel.hotel_name) AS hotel_name,
            hotel_order.room_id,
            FIRST(room_type.room_name) AS room_name,
            SUM(hotel_order.amount) AS amount
        FROM hotel_order
            LEFT OUTER JOIN room_type ON room_type.room_id = hotel_order.room_id
            LEFT OUTER JOIN hotel ON hotel.hotel_id = room_type.hotel_id
        WHERE hotel_order.create_date = $1
            AND hotel.hotel_name = $2
        GROUP BY hotel_order.room_id;

    hotel_name VARCHAR(255);
    room_id INT;
    room_name VARCHAR(255);
    amount INT;

    BEGIN
        OPEN c;
        LOOP
            FETCH c INTO hotel_name, room_id, room_name, amount;
            EXIT WHEN c%notfound;
            RAISE INFO 'hotel_name: %', hotel_name;
            RAISE INFO 'room_id:    %', room_id;
            RAISE INFO 'room_name:  %', room_name;
            RAISE INFO 'amount:     %', amount;
            RAISE INFO '';
        END LOOP;
        CLOSE c;
    END
;

CALL get_room_type_order_on_date_hotel('2020-11-14', '希尔顿大酒店');

P9

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

SELECT *
FROM customer
WHERE uid IN (
    SELECT uid
    FROM rating
    GROUP BY uid
    HAVING COUNT(*) >= 2
);

image-20241116103826823

P10

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

SELECT DISTINCT customer.uname
FROM customer
JOIN rating ON customer.uid=rating.uid
JOIN hotel_order ON rating.order_id=hotel_order.order_id
JOIN room_type ON hotel_order.room_id=room_type.room_id
WHERE room_type.room_name='总统套房';

Select customer.uname 
from customer
where customer.uid in(
   select uid 
   from  rating left outer join hotel_order on rating.order_id = hotel_order.order_id
   where hotel_order.room_id = 7 or hotel_order.room_id =12 or hotel_order.room_id =16
   group by rating.uid
   having count(distinct hotel_order.room_id) = 3
)
;

image-20241116115603823

P11

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

SELECT FIRST(hotel.hotel_name) AS hotel_name,
FIRST(room_type.room_name) AS room_name,
AVG(room_info.price) AS average_price
FROM room_info
LEFT OUTER JOIN room_type ON room_type.room_id = room_info.room_id
LEFT OUTER JOIN hotel ON hotel.hotel_id = room_type.hotel_id
WHERE room_info.room_id IN (
    SELECT room_id
    FROM room_info
    WHERE room_id IN (
        SELECT room_id
        FROM room_info
        WHERE DATE = '2020-11-14'
            AND remain >= 4
    )
    INTERSECT
    (
        SELECT room_id
        FROM room_info
        WHERE DATE = '2020-11-15'
            AND remain >= 4
    )
    INTERSECT
    (
        SELECT room_id
        FROM room_info
        WHERE DATE = '2020-11-16'
            AND remain >= 4
    )
)
GROUP BY room_info.room_id;

image-20241116103940354

P12

编写触发器：完成预订房间，包括创建订单和更新房型信息。

该订单为预订11月14号-15号4号房型4间

CREATE OR REPLACE PROCEDURE order_room(t_start_date DATE, t_leave_date DATE, t_room_id INT, t_amount INT, t_customer_id INT)
AS
    BEGIN
        INSERT INTO hotel_order
        VALUES (
            (
                SELECT MAX(order_id) + 1
                FROM hotel_order
            ),
            t_room_id,
            t_start_date,
            t_leave_date,
            t_amount,
            (
                SELECT SUM(price) * t_amount
                FROM room_info
                WHERE room_id = t_room_id
                    AND date IN (
                        SELECT a
                        FROM generate_series(
                            t_start_date::DATE,
                            t_leave_date::DATE,
                            '1 day'
                        ) s(a)
                    )
            ),
            NOW(),
            t_customer_id
        );
    END
;

CREATE OR REPLACE FUNCTION check_remain()
RETURNS TRIGGER
AS $
    DECLARE total_count INT;
    DECLARE available_count INT;

    BEGIN
        SELECT COUNT(*) INTO total_count
        FROM room_info
        WHERE room_id = new.room_id
            AND date IN (
                SELECT a
                FROM generate_series(
                    new.start_date::DATE,
                    new.leave_date::DATE,
                    '1 day'
                ) s(a)
            );
        SELECT COUNT(*) INTO available_count
        FROM room_info
        WHERE room_id = new.room_id
            AND date IN (
                SELECT a
                FROM generate_series(
                    new.start_date::DATE,
                    new.leave_date::DATE,
                    '1 day'
                ) s(a)
            )
            AND remain >= new.amount;

        IF available_count < total_count THEN
            RAISE EXCEPTION 'Not enough rooms';
        END IF;

        RETURN new;
    END
$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS check_remain_trigger ON hotel_order;
CREATE TRIGGER check_remain_trigger
BEFORE INSERT ON hotel_order
FOR EACH ROW
EXECUTE PROCEDURE check_remain();

-- 在创建订单后，要减少房间余量。
CREATE OR REPLACE FUNCTION update_remain()
RETURNS TRIGGER
AS $
    DECLARE CURSOR c IS
        SELECT info_id, remain
        FROM room_info
        WHERE room_id = new.room_id
            AND date IN (
                SELECT a
                FROM generate_series(
                    new.start_date::DATE,
                    new.leave_date::DATE,
                    '1 day'
                ) s(a)
            );

    DECLARE t_info_id INT;
    DECLARE now_remain INT;

    BEGIN
        OPEN c;
        LOOP
            FETCH c INTO t_info_id, now_remain;
            EXIT WHEN c%notfound;

            UPDATE room_info
            SET remain = now_remain - new.amount
            WHERE info_id = t_info_id;
        END LOOP;
        CLOSE c;
        RETURN new;
    END
$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_remain_trigger ON hotel_order;
CREATE TRIGGER update_remain_trigger
AFTER INSERT ON hotel_order
FOR EACH ROW
EXECUTE PROCEDURE update_remain();

CALL order_room('2020-11-14', '2020-11-15', 4, 10, 2019018);

更新于：2025年5月1日

评论




博客内容遵循 [署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议](https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh)
本站使用 Volantis 作为主题
[Copyright © since 2023. All rights reserved](/)
字数统计：318.5k
<