数据库——查询

智能2112杨阳

一、目的与要求：

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 Names from fruits group by s_id having count(f_name)>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 orderitems.o_num,orders.c_id from orderitems,orders where orderitems.o_num=orders.o_num and orderitems.f_id='c0';

运行测试结果截图:

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

源码：

select orders.c_id,sum(orderitems.item_price)金额 from orders,orderitems where orderitems.o_num=orders.o_num and orders.c_id=10000;

运行测试结果截图:

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

源码：select suppliers.s_id,fruits.f_name from suppliers,fruits where suppliers.s_id=fruits.s_id and suppliers.s_city='Tianjin';

运行测试结果截图:

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

源码：select distinct suppliers.s_name from suppliers,fruits,orderitems where fruits.f_id=orderitems.f_id and fruits.s_id=suppliers.s_id and orderitems.o_num=30005;

运行测试结果截图:

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

源码：select fruits.f_name from fruits,orderitems where orderitems.f_id=fruits.f_id order by 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）；然后再做题

源码：

insert into orders values(50010,now(),10000);

insert into orders values(50008,now(),10004);

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

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

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

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

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

运行测试结果截图:

 

 

源码：select * from customers,orders,orderitems,fruits where customers.c_id=orders.c_id and orderitems.o_num=orders.o_num and fruits.f_id=orderitems.f_id and fruits.s_id=107;

运行测试结果截图:

三、思考

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

建立的表的数量略多，数据有些冗杂，可以考虑适当进行优化，如将外键的字段编入索引而不进行全表扫描，可以在整体的操作变得更加方便操作

四、总结

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

问题：

1. 查询表中含有字母“g”的数据时，没有查到有效结果
2. 插入数据时报错

解决：

1. 给需要查找的字母加一对’%’
2. 根据所需数据类型添加或删除’’

2.运行时产生的错误及原因分析

错误：

1. 查询销量最多的水果名时，返回了多组数据
2. 计算订单总价格错误

原因分析：

1. 没有用LIMIT限制返回数据的条数
2. 没有运用sum函数算出总价格

3．体会和收获。

本次博客让我对查询的使用有了新的理解和认识，让我对查询的使用更加得心应手，经过这次博客，我学会了sum求和函数的用法，函数group_concat用法，学会了统计及分组函数，总的来说收获满满。