基于openGauss的sql实验

该文档为一个大致解析，实验报告及结果在这里

实验数据库

一、实验项目名称

基于openGauss的sql实验

二、实验学时

4个学时

三、实验内容

在给定数据集中，利用openGauss对数据集进行数据库简单数据管理和高级数据管理的应用，包括对函数，存储过程，触发器，索引的使用等

四、实验环境

openGauss(2.0.0)

五、实验数据及结果分析

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

select * from room_type ;

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

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

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

select order_id, hotel_id, room_id, leave_date - start_date  as living_time 
from hotel_order natural join room_type where payment >= 10000;

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

select distinct room_id, room_name, order_id, price 
from  hotel_order natural join room_type natural join room_info 
order by room_id asc;

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

create view qiyu_order1 as
select order_id, room_id, start_date, leave_date, amount, payment, create_date, customer_id 
from hotel natural join room_type natural join hotel_order 
where hotel_name = '启悦酒店';
select * from qiyu_order1;

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

create index orderpayment on hotel_order (payment desc);
\di

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

create or replace function avg_price(date1 date, name varchar) 
returns decimalas
$$
declare 
    res decimal(10, 2);
begin 
    select distinct avg(price) into res from 
    (select price from hotel natural join room_type natural join room_info 
    where hotel_name = name and date = date1);
    return res;
end;
$$
language plpgsql;

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

利用存储过程创建一个新表，将结果插入到新表中

create or replace procedure  order_status(date1 date, name varchar)
as
declare 
    hname varchar(20);
    rname varchar(20);
    rsum integer;

    cursor c1 is
    select hotel_name, room_name, sum(amount)
    from hotel natural join room_type natural join hotel_order 
    where hotel_name=name and start_date<=date1 and leave_date>=date1
    group by hotel_id, room_id;

begin
    create table order_status_table(hotel_name varchar(20), room_name varchar(20), sum integer);
    open c1;
    loop
        fetch c1 into hname, rname, rsum;
        exit when c1%notfound;
        insert into order_status_table values (hname, rname, rsum);
    end loop;
    close c1;
end;
/
call order_status('2020-11-14', '希尔顿大酒店');
select * from order_status_table;

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

select distinct r1.uid, uname 
from rating r1, rating r2, customer 
where r1.uid = r2.uid and r1.uid = customer.uid and r1.rid < r2.rid;

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

select distinct uname 
from customer natural join rating natural join hotel_order natural join room_type 
where room_name = '总统套房';

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

SELECT hotel_name,room_name,AVG(price) 
FROM hotel NATURAL JOIN room_type NATURAL JOIN room_info WHERE date <= '2020-11-16 00:00:00' AND
date >= '2020-11-14 00:00:00' 
GROUP BY hotel_name,room_name 
HAVING MIN(remain) >= 4 ORDER BY AVG(price);

12.编写触发器：完成预订房间，包括创建订单和更新房型信息。
该订单为预订11月14号-15号4号房型4间。

--触发器函数
CREATE OR REPLACE FUNCTION update_roominfo() RETURNS TRIGGER AS
$$
declare
begin
    update room_info set remain = remain - new.amount
    when date >= new.start_date and date <= new.leave_date and room_id = new.room_id;
    return new;
end
$$
language plpgsql;

--触发器
create or replace trigger insert_hotelorder
after insert on hotel_order
for each row
execute procedure update_roominfo();

--执行函数
insert into hotel_order 
values(28, 4, '2020-11-14', '2020-11-15', 4, 8000, '2020-11-12', 201904);

插入后，room_info中对应日期的remain也发生了相应的变化

六、实验结论

• 通过课堂上的限时测试与课下的思考，对openGauss中的触发器，函数，索引，存储过程都有了更深的理解，了解了用sql语句处理更复杂的业务流程
• 不同数据库间sql语句有一定的区别，高级数据的管理结构也有较大差异，需要根据不同的数据库做不同的操作