数据管理技术-Lab-4

数据管理技术Lab4实验报告

Task1

导入结果

表customer

表food

表order

Q1

查询与CID=1的顾客同一个城市的所有顾客ID

代码如下

select CID
from customer
where City = (select City
              from customer
              where CID = 1);

结果如下

Q2

查询购买过所有省份（Food表中出现过的City）的食物的顾客ID

代码如下：

select customer.CID
from customer, food, orders
where customer.CID = orders.CID
and food.FID = orders.FID
group by customer.CID
having count(distinct food.City) = 9

结果如下(导入数据中没有符合的项所以加了几条数据)

Q3

查询至少购买过ID为4的顾客买过的全部食物的顾客ID

代码如下：

select distinct od1.CID from orders as od1
where od1.CID != 4
and not exists(
    select * from orders as od2
    where od2.CID = 4
    and not exists(
        select * from orders as od3
        where od3.CID = od1.CID
        and od3.FID = od2.FID
        )
    )

结果如下

Task2

Q1

创建一个新表Sales，字段为：Food ID（主键） 数字型，食物名（非空）字符型 长度20，总销量 数字型。查询每种食物的总销量，将结果插入表中。（两条SQL语句，一条为create语句，一条为insert语句）。

创建：

create table Sales(
    Food_ID int primary key ,
    Food_name char(20) not null ,
    amount int
);

结果

查询插入：

insert into sales
select food.FID, Name, a.quantity
from food,(
    select FID as q_fid, sum(quantity) as quantity
    from orders
    group by orders.FID
    order by orders.FID
    )a
where q_fid = food.FID

结果

Q2

向Order表添加一条交易记录，内容自定义，并更新对应食物的总销量字段（两条SQL语句）

添加代码：

INSERT INTO lab4.orders (OID, CID, FID, Quantity) VALUES (24, 3, 3, 10);

结果

更新字段代码：

update sales
set amount = (select sum(Quantity)
              from orders
              where orders.FID = 3)
where Food_ID = 3;

结果

Q3

为新表添加一个评分字段（数字型），要求分数范围限定为0-10，并设置默认值6

代码：

alter table sales
    add score int default 6,
    check ((score >= 0) and (score <= 10))
;

结果

Task3

Q1

建立购买过重庆或四川食物的顾客视图Shu-view（包含Customer中CID，City）

create view `Shu-view` as
select distinct customer.CID, customer.City
from food, orders, customer
where customer.CID = orders.CID
  and orders.FID = food.FID
  and (food.City = '四川' or food.City = '重庆');

结果

Q2

查询购买过重庆或四川食物的顾客中订单总消费最高的顾客CID（使用视图Shu-view，思考使用视图的好处）

select distinct `shu-view`.CID
from `shu-view`, orders, food
where `shu-view`.CID = orders.CID
and orders.FID = food.FID
group by `shu-view`.CID
having sum(Quantity * Price) >= all (
    select sum(Quantity * Price)
    from `shu-view`, orders, food
    where `shu-view`.CID = orders.CID
      and orders.FID = food.FID
    group by `shu-view`.CID
)

结果

使用视图的好处：可以直接从已经查好部分内容的表中与其他表查找，相当于省去写一个子查询的步骤，精简查询代码，便于操作。

Q3

向视图Shu-view加入表项（16，湖南），能成功吗，为什么？

代码：

INSERT INTO lab4.`shu-view` (CID, City) VALUES (16, '湖南')

结果

原因：表中的项都满足’’购买过重庆或四川食物’’的特点，其是根据orders、food查询得到，而该表项没有这些限制，故不能插入。

Q4

建立男性顾客的视图Male-view（包含Customer中CID，City）,并要求对该视图进行的更新操作 只涉及男性顾客。（WITH CHECK OPTION）

代码如下：

create view `Male-view` as
select CID, City from customer
where Gender = '男'
with check option ;

结果如下

Q5

向视图Male-view加入表项（17，湖南），能成功吗，为什么？

代码：

INSERT INTO lab4.`male-view` (CID, City) VALUES (17, '湖南')

结果

原因：插入的值实际上在gender属性是NULL，而不是男，违背了check option， 因此无法插入。