数据库实验之查询

集美大学计算机工程学院实验报告

班级：智能2112      学号：202121335041              姓名：黄秦琨             实验编号： 2       

实验二：查询

一、实验目的与要求：

1、掌握SQL单表及多表之间的查询

2、掌握统计及分组函数

二、实验内容：

1. 简单查询

• 从fruits表中检索s_id为100的供货商所供货的水果名和价格

源码：select f_name,f_price from fruits where s_id=100;

运行测试结果截图:

• 查找名称为“apple”的水果的价格

源码：select f_price from fruits where f_name='apple';

运行测试结果截图:

• 查询价格在2.00元到10.20元之间的水果名称和价格，先按f_price降序排序，再按f_name排序。

源码：select f_name,f_price

from fruits

where f_price between 2.00 and 10.20

order by f_price desc,f_name desc;

运行测试结果截图:

• 在fruits表中，查询f_name中包含字母‘g’的记录

源码：select * from fruits where f_name like '%g%';

运行测试结果截图:

• 查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值

源码：select c_id,c_name,c_email

from customers

where c_email is not null;

运行测试结果截图:

• 根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，效果如下图所示（GROUP_CONCAT函数）

或

源码：SELECT s_id,GROUP_CONCAT(f_name)AS name

FROM fruits

GROUP BY s_id HAVING COUNT(f_id)>1

运行测试结果截图:

1. 多表查询

• 查询供应‘a1’的水果供应商提供的其他水果种类

源码：SELECT x.f_id FROM fruits AS x,fruits AS y

WHERE x.s_id=y.s_id AND y.f_id='a1';

运行测试结果截图:

• 在orderitems表中查询f_id为c0的订单号，并显示具有该订单号的客户c_id

源码：select c_id from orders

where o_num in (

                 select o_num from orderitems

                 where f_id='c0'

                 );

运行测试结果截图:

• 查询客户c_id为10000的所有订单的总价格（客户购买水果所花费的金额），效果类似如下图所示

源码：select c_id,SUM(item_price*quantity) as 金额

from orderitems left join orders

on orderitems.o_num=orders.o_num

where c_id=10000

运行测试结果截图:

• 查询s_city等于“Tianjin”的供应商，并显示所有该供应商提供的水果的种类，效果如下图所示

源码：select suppliers.s_id,fruits.f_name

from fruits left join suppliers on suppliers.s_id=fruits.s_id

where suppliers.s_city='Tianjin'

运行测试结果截图:

• 查询订单为‘30005’的所有水果供货商的名称

源码：select s_name

from (

        select suppliers.s_name,orderitems.o_num

        from fruits,orderitems,suppliers

        where fruits.f_id=orderitems.f_id and fruits.s_id=suppliers.s_id

        )s

where s.o_num=30005

运行测试结果截图:

• 查询销量最多的水果名称

源码：select f_name

from orderitems left join fruits on fruits.f_id=orderitems.f_id

order by quantity desc limit 1;

运行测试结果截图:

• 显示购买了’107’号供货商所有水果的用户信息

步骤：先向数据表orders表中插入两条记录（50010,当前时间,10000）和（50008,当前时间,10004），在orderitems表中插入五条记录（50010，1，b5，10，3.6）、（50010，2，b2，5，7.6）、（50010，3，t2，7，3.6）、（50008，1，b1，10，102）、（50008，2，b5，10，3.6）；然后再做题

源码：

insert into orders values(50010,'2023-03-25-10:00',10000);

insert into orders values(50008,'2023-03-25-10:00',10004);

insert into orderitems values(50010,1,'b5',10,3.6),

    -> (50010,2,'b2',5,7.60),

    -> (50010,3,'t2',7,3.6),

    -> (50008,1,'b1',10,102),

    -> (50008,2,'b5',10,3.6);

运行测试结果截图:

源码：

select customers.*

from customers

join orders

on customers.c_id=orders.c_id

join orderitems

on orders.o_num=orderitems.o_num

join fruits

on orderitems.f_id=fruits.f_id

where fruits.s_id=107;

运行测试结果截图:

• 思考题

结合实际应用并分析查找给定表中结构或数据是否存在问题，如何改进？

答：表的数量很多，并且有些数据还并没有用到，存在冗余的情况，优化的话可以对外键的字段编入索引而不进行全表扫描

四、实验小结

1．实验中遇到的问题及解决过程

问题：在查询表中含有字母“g”的数据时，查不到结果：

解决过程：只需要在字母“g”的两边加上%即可

2.实验中产生的错误及原因分析

       错误：在查询购买了’107’号供货商所有水果的用户信息时，出现错误

       原因分析：因为显示一个表中的全部数据可以用符号“*”，但是如果显示一个具体表的数据的话，格式应该为：表名.*，所以上面漏了一个小数点，加上之后便可以正常运行了：

3．实验体会和收获。

         本次实验是我第一次接触到如何去查询数据库里面的数据，虽然过程出现了许许多多的困难，例如多表查询的连接问题，但经过不懈努力的查资料，也是能够一一解决这些问题；

本次实验给我的收获颇多，激发我对sql语言的学习兴趣。