数据库实验二：查询

一、实验目的与要求：

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

2、掌握统计及分组函数

二、实验所需的数据库表

fruits表

customers表

orderitems表数据

suppliers表数据

orders表数据

三、实验内容

1.简单查询

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

对于表的查询，只需要查询需要的内容，在where中填写要求，s_id=100

select f_name,f_price
from fruits
where s_id = 100;

(2)查找名称为“apple”的水果的价格

对于表的查询，只需要查询需要的内容，在where中填写要求，f_name='apple'

select f_price
from fruits
where f_name='apple';

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

对于表的查询，只需要查询需要的内容，在where中填写要求，f_price >2 and f_price<10.2

对于表的排序，需要用order by f_price desc，desc为降序，如果填asc则为升序，不写为默认asc升序

select f_name,f_price 
from fruits
where f_price>2 and f_price <10.2
order by f_price desc , f_name asc;

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

对于单字的查询，我们需要在where条件中设置

f_name like '%g%',俩个百分号中间加字母=搜索带有g字符的f_name

如果填 'g%',则为搜索f_name中g字符开头的

如果填'%g',则为搜索f_name中以g字符为结尾的

select f_name 
from fruits

where f_name like '%g%';

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

查询c_email不为空的记录只需要在where中写 c_email is not null就可以达到目的

select c_id, c_name, c_email
from customers
where c_email is not null;

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

GROUP_CONCAT 是 SQL 中的一个聚合函数，用于将多行数据合并为一个字符串结果，通常与 GROUP BY 子句一起使用。

GROUP_CONCAT(字段 [SEPARATOR '分隔符'])

常用形式：

1. 简单合并：GROUP_CONCAT(name)

2. 指定分隔符：GROUP_CONCAT(name SEPARATOR '|')

3. 去重合并：GROUP_CONCAT(DISTINCT name)

4. 排序合并：GROUP_CONCAT(name ORDER BY id DESC)

 然后用having count(*)>1 来保留那些组内有多于一条记录的分组

select s_id,group_concat(f_name)
from fruits
group by s_id
having count(*)>1;

二、多表查询

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

要达到此目的，需要查到水果供应商和水果的联系，也就是俩个表的连接

但是提供'a1'的供应商的s_id可以在fruits中找到，所以可以用查询'a1'的s_id

来查询该s_id所供应的f_id（水果种类）

SELECT f_id,f_name
FROM fruits 
WHERE s_id IN (
      SELECT s_id
      FROM fruits 
     WHERE f_id= 'a1'
)
AND f_id != 'a1';

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

为达到此目的，我们需要从orders订单表中查询客户的c_id，而所需要的条件是orderitems表中的订单号f_id=‘c0’，因此很容易得到以下步骤

SELECT c_id
FROM orders
WHERE o_num IN (
      SELECT o_num
      FROM orderitems
     WHERE f_id= 'c0'
);

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

SELECT c_id, SUM(item_price * quantity) AS total_amount
FROM orders
JOIN orderitems ON orders.o_num = orderitems.o_num
WHERE c_id = 10000
GROUP BY c_id;

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

查询‘Tianjin’的供应商，需要找到suppliers中的s_city='Tianjin'

查询该供应商的提供的水果的种类，需要找到该从fruits中找到供应商s_id，

也就是从suppliers中找到供应商的s_id,需求是s_city='Tianjin'

SELECT s_id,f_name from fruits
where s_id IN (SELECT s_id
from suppliers
where s_city='Tianjin');

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

查询订单为‘30005’的所有水果供货商的名称s_name，需要查询该供应商的s_id，然后还需要从

o_num=30005入手，从orderitems表中找到f_id,利用f_id，找到供应商的s_id,从而达到目的

SELECT s_name from suppliers
WHERE s_id IN(SELECT s_id
from fruits
WHERE f_id IN (SELECT f_id
from orderitems
WHERE o_num=30005));

（6）查询销量最多的水果名称

从 orderitems 表中按 f_id（水果ID），分组计算每个水果的总销量（SUM(quantity)），按总销量降序排列，只返回销量最高的1个水果的ID

将子查询结果（销量最高的水果ID）与 fruits 表连接

通过 f_id 匹配，获取该水果的名称（f_name）

SELECT f.f_name
FROM fruits f
JOIN (
    SELECT f_id
    FROM orderitems
    GROUP BY f_id
    ORDER BY SUM(quantity) DESC
    LIMIT 1
) o ON f.f_id = o.f_id;

（7）显示购买了’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(o_num,o_date,c_id) 
VALUES(50010,'2025-04-03 13:16:00',10000),
(50008,'2025-04-03 13:17:00',10004);


INSERT INTO orderitems(o_num,o_item,f_id,quantity,item_price)
VALUES(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);

SELECT * from suppliers
WHERE s_id IN (SELECT s_id
from fruits
WHERE f_id IN (SELECT f_id
from orderitems
WHERE o_num IN (SELECT o_num
from orders
WHERE c_id IN (SELECT c_id
from customers
WHERE c_id IN (SELECT c_id
from orders
WHERE o_num IN (SELECT o_num
from orderitems
WHERE f_id IN (SELECT f_id
from fruits
WHERE s_id=107)))))));

四、实验小结

1. 遇到的问题及解决：

   • 多表嵌套查询时逻辑混乱

     先拆分子查询逐步验证，再组合成完整语句

   • GROUP_CONCAT的使用错误

     查询GROUP_CONCAT的使用方法，并针对要求进行设置

   • HAVING条件报错

     误将WHERE用于分组后筛选

2. 错误分析：

   • AND f_id != 'a1'未正确关联括号

     • 原因：未注意子查询的括号闭合

     • 修正：调整括号位置确保逻辑正确

   • 多表连接时出现笛卡尔积

     • 原因：漏写JOIN条件

     • 修正：显式指定所有关联字段

3. 实验收获：

   • 掌握了多表查询的方式，嵌套嵌套，通过外键的连接，将各个表连接起来。从每一个表中的各个数据联系起来，从而查询到所需要的数据

   • 理解了分组统计（GROUP BY+HAVING），group by 可以进行升序和降序来设置，通过dsec和asc中设置降序和升序。having 函数的统计能够将数据联系起来。