集美大学计算机工程学院实验报告
班级:智能2112 学号:202121335041 姓名:黄秦琨 实验编号: 2
实验二:查询
一、实验目的与要求:
1、掌握SQL单表及多表之间的查询
2、掌握统计及分组函数
二、实验内容:
- 简单查询
- 从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
运行测试结果截图:
- 多表查询
- 查询供应‘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语言的学习兴趣。