数据库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 ASC;

运行测试结果截图:

④　在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 Names
FROM fruits
GROUP BY s_id
HAVING COUNT(f_id) > 1;

运行测试结果截图:

2.多表查询
①　查询供应‘a1’的水果供应商提供的其他水果种类
源码：

SELECT DISTINCT f2.f_name 
FROM fruits f1 
JOIN fruits f2 ON f1.s_id = f2.s_id
WHERE f1.f_id = 'a1' AND f2.f_id <> 'a1';

运行测试结果截图:

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

SELECT DISTINCT orders.o_num, orders.c_id 
FROM orders 
JOIN orderitems ON orders.o_num = orderitems.o_num
WHERE orderitems.f_id = 'c0';

运行测试结果截图:

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

源码：

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

运行测试结果截图:

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

源码：

SELECT suppliers.s_id, GROUP_CONCAT(fruits.f_name) AS fruit_list 
FROM suppliers 
JOIN fruits ON suppliers.s_id = fruits.s_id
WHERE suppliers.s_city = 'Tianjin'
GROUP BY suppliers.s_id;

运行测试结果截图:

⑤　查询订单为‘30005’的所有水果供货商的名称
源码：

SELECT DISTINCT suppliers.s_name 
FROM suppliers 
JOIN fruits ON suppliers.s_id = fruits.s_id 
JOIN orderitems ON fruits.f_id = orderitems.f_id
WHERE orderitems.o_num = 30005;

运行测试结果截图:

⑥　查询销量最多的水果名称
源码：

SELECT fruits.f_name, SUM(orderitems.quantity) AS total_sold 
FROM orderitems
JOIN fruits ON orderitems.f_id = fruits.f_id
GROUP BY fruits.f_name 
ORDER BY total_sold 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 (o_num, o_date, c_id) VALUES 
(50010, NOW(), 10000),  
(50008, NOW(), 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 DISTINCT 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
GROUP BY customers.c_id
HAVING COUNT(DISTINCT fruits.f_id) = (SELECT COUNT(DISTINCT f_id) 
                                       FROM fruits 
                                       WHERE s_id = 107);

运行测试结果截图:

运行结果为空，即没有用户买了’107’号供货商所有水果。

三、思考题
结合实际应用并分析查找给定表中结构或数据是否存在问题，如何改进？
在第7小题查询购买了’107’号供货商所有水果的用户信息时，发现输出结果为空，影响用户体验。
改进：（1）输出购买’107’号供货商部分水果的用户
代码：

SELECT DISTINCT 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;

运行结果：

（2）手动插入数据
– 先新增一个用户

INSERT INTO customers (c_id, c_name) VALUES (10005, 'TestUser');

– 新增一个订单

INSERT INTO orders (o_num, o_date, c_id) VALUES (50011, NOW(), 10005);

– 确保 orderitems 里有 107 号供货商的所有水果

INSERT INTO orderitems (o_num, o_item, f_id, quantity, item_price) VALUES 
(50011, 1, 'b5', 5, 3.60),  
(50011, 2, 't4', 3, 3.60);  

运行结果：

插入数据后运行查询即可输出购买了’107’号供货商所有水果的用户信息：

四、实验小结
1．实验中遇到的问题及解决过程
问题：查询购买了‘107’号供货商所有水果的用户信息时结果为空
解决方案：
方案一：修改查询条件，查询购买了‘107’号供货商部分水果的用户，以提升查询结果的可用性。
方案二：手动插入数据，确保数据库中有用户购买了‘107’号供货商的所有水果，并重新执行查询，成功获取结果。

2.实验中产生的错误及原因分析
错误现象：多表连接查询时，返回的结果不符合预期，或者返回空结果。
原因分析：连接查询时，可能 JOIN 关联字段不匹配，或存在 NULL 值，影响查询结果。

学习感受

通过本次SQL查询实验，我对数据库的查询语句有了更深入的理解和掌握。尤其是在进行多表连接查询以及分组统计函数的使用时，我切实感受到了SQL在处理复杂数据关系时的强大能力。

实验中最让我印象深刻的是最后一道题“查询购买了某供货商所有水果的用户”。在实际操作过程中，虽然查询语句逻辑严谨，但由于数据本身不满足查询条件，导致结果为空。这一过程让我意识到，仅仅会写查询语句是不够的，还需要对数据本身有清晰的认知，甚至需要主动构造数据进行验证。这种从“语法正确”到“结果合理”的过渡，是我此次实验中很宝贵的体会。

另外，像GROUP_CONCAT函数的使用也让我眼前一亮，它能将多个值合并为一个字段，对于结果展示非常友好。在以往的学习中，这种细节很容易被忽略，但在实验中使用后让我真正理解了它的作用和优势。

总的来说，这次实验不仅提升了我对SQL查询语法的掌握，更让我认识到在实际应用中如何灵活运用所学知识来解决问题。也让我体会到，数据分析不仅仅是“查出结果”，更是一个逻辑严密、需要不断验证和优化的过程。