[数据库实验二]查询

目录

一、实验目的与要求：

二、实验内容：

简单查询

多表查询

三、实验小结

---

本次实验基于实验一相关数据库及信息[数据库实验一]数据库和表-CSDN博客

一、实验目的与要求：

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

2、掌握统计及分组函数

二、实验内容：

简单查询

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

源码：

mysql> select f_name as 水果名,f_price as 价格

    -> from fruits

    -> where s_id =100;

运行测试结果截图:

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

源码：

mysql>  select f_name as 水果名,f_price as 价格

    -> from fruits

    -> where f_name='apple';

运行测试结果截图:

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

源码：

mysql> select f_name as 水果名,f_price as 价格

    -> from fruits

    -> where f_price >=2.00 and f_price<=10.20

    -> order by f_price desc;

mysql>  select f_name as 水果名,f_price as 价格

    -> from fruits

    -> where f_price >=2.00 and f_price<=10.20

    -> order by f_name asc;

运行测试结果截图:

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

源码：

mysql> select *

    -> from fruits

    -> where f_name like '%g%';

运行测试结果截图:

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

源码：

mysql> select c_id,c_name,c_email

    -> from customers

    -> where c_email is not null;

运行测试结果截图:

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

或

源码：

mysql> select s_id,group_concat(f_name) as Names

    -> from fruits

    -> group by s_id

    -> having count(f_name)>1;

或者

mysql> select s_id,group_concat(f_name) as f_name,count(f_name) as number

    -> from fruits

    -> group by s_id

    -> having count(f_name)>1;

运行测试结果截图:

或者

多表查询

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

源码：

mysql> select f_id as 水果编号（种类）,f_name as 水果名,f_price as 价格

    -> from fruits

    -> where s_id=(..

    -> select s_id

    -> from fruits

    -> where f_id='a1'

-> );

运行测试结果截图:

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

源码：

mysql> select orderitems.o_num,orders.c_id

    -> from orderitems,orders

    -> where orderitems.o_num=orders.o_num and orderitems.f_id='c0';

运行测试结果截图:

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

源码：

mysql> select orders.c_id ,

sum(orderitems.quantity*orderitems.item_price) as 金额

    -> from orders,orderitems

    -> where orders.o_num=orderitems.o_num and orders.c_id=10000;

运行测试结果截图:

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

源码：

mysql> select suppliers.s_id,fruits.f_name

    -> from suppliers,fruits

    -> where suppliers.s_id=fruits.s_id and suppliers.s_city='Tianjin';

运行测试结果截图:

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

源码：

mysql> select orderitems.o_num,suppliers.s_name

    -> from orderitems,suppliers,fruits

    -> where orderitems.f_id=fruits.f_id and fruits.s_id=suppliers.s_id and orderitems.o_num=30005;

运行测试结果截图:

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

源码：

mysql> select sum(orderitems.quantity) as 数量,fruits.f_name as 水果名称

    -> from orderitems , fruits

    ->  where orderitems.f_id = fruits.f_id

    -> group by fruits.f_name

    -> order by sum(orderitems.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）；然后再做题

源码：

mysql> insert into orders values(50010,'2024-4-7 16:00:30',10000);

mysql> insert into orders values(50008,'2024-4-7 16:00:30',10004);

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

mysql>  insert into orderitems values(50010,2,'b2',5,7.6);

mysql>  insert into orderitems values(50010,3,'t2',7,3.6);

mysql>  insert into orderitems values(50008,1,'b1',10,102);

mysql>  insert into orderitems values(50008,2,'b5',10,3.6);

因为没有用户同时购买b5和t4商品，所以新插入一条数据：

mysql>  insert into orderitems values(50008,3,'t4',10,3.6);

运行测试结果截图:

源码：

mysql> select distinct c.*

    -> from customers c

    -> where not exists(

    -> select 1

    -> from suppliers s

    -> join fruits f on f.s_id=s.s_id

    -> where s.s_id=107 and not exists(

    -> select 1

    -> from orderitems o

    -> join orders o1 on o1.o_num=o.o_num

    -> where o.f_id=f.f_id and o1.c_id=c.c_id)

-> );

运行测试结果截图:

• 思考题

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

答：因为基本表的数量略多，数据有些冗杂，可以考虑适当进行优化，如外键的字段编入索引而不进行全表扫描。同时有时需要将查询后的结果进行保存，而本次实验中没有将查询结果进行保存，那么下一次查询便需要重复执行该操作。其次，在fruits表格中的属性f_price‘水果价格’和orderitems表格中的属性item_price’单价’的值相同，在插入价格数据时，可以通过将f_price作为外键，保证水果单价相同，这样就不用重复输入相同的值。

三、实验小结

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

问题一：在HAVING子句中引用了别名Names，但是having子句无法直接使用别名

解决过程：需要重新使用原始的聚合函数或表达式。可以通过将HAVING子句中的条件改为 count(f_name) 来解决这个问题

问题二：对于orderitems和orders两个表格中相同的属性 o_num没有明确的定义，导致系统对于o_num的位置不清晰

解决过程：在每个属性前添加对应的实体或者表格定位其具体位置

问题三：在多表查询中没有理清对应的逻辑关系

解决过程：在完成多表查询之前需要自己理清查询的过程以及对其他表的引用、连接等都不能随意处理。

问题四：在使用order by语句中，先使用desc降序，再使用asc升序时，查询后的表格只能降序，没有根据要求升序

解决过程：这是因为在SQL中，ORDER BY子句是用来指定对结果集中的列进行排序的，而不是对同一列进行多个方向的排序。可以通过多次排序实现或者先升序再降序也可实现。

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

错误一及原因分析：mysql语句不能在查询中使用两个及以上的 order by 语句，可以将两个排序条件合并到一个 order by 语句中

错误二及原因分析：语言规范性出错，在 MySQL 中，not null 关键字需要写成 IS NOT NULL，而不是 not null

错误三及原因分析：因为子查询返回了多行数据，而在这个查询中，子查询被用作等值条件的一部分，所以无法确定应该选择哪一行数据进行匹配。

解决方法：

1修改子查询，确保只返回一行数据。可以使用LIMIT关键字来限制子查询返回的结果集数量，或者使用其他条件来过滤数据，确保只返回一个结果。

2修改查询语句的逻辑，使用JOIN语句来连接orderitems和orders表，而不是使用子查询作为等值条件。

3．实验体会和收获

通过本次实验我完成了对数据库进行一系列查询操作，掌握了对SQL单表、多表之间的查询、统计与分组函数的应用，更深入了解了数据库原理课程的内容。相较于单表查询，多表查询需要在掌握表与表之间的联系的基础上，实现相关表的连接。因此，在实现多表查询时，不要急于马上写出SQL语句，需要提前理清楚逻辑关系后再执行。在实验过程中也出现了一些以前没遇到过的问题，通过上网查询资料也使我对数据库的相关知识、语法等有了更深入的掌握。