实验07 集合查询和统计查询

实验目的

使学生进一步掌握加深MDSQL语言中集合和统计查询语句的理解和运用。

实验要求

（1） 掌握数据查询中的分组方法；
（2） 掌握使用统计函数查询数据的方法；
（3） 掌握使用分组统计函数查询数据的方法；
（4） 掌握使用集合查询的方法；
（5） 掌握计算和分组计算查询方法。

实验内容

查询客户表中的男性总人数。

select count(*) 男性总人数
from 客户表 
where 性别='男'

通过购买记录查询客户总人数。

select count(distinct 客户编号) 客户总人数
from 购买记录表 

统计每个卖家所拥有的商品种类数。

select 卖家表.卖家编号,count(distinct 商品编号) 商品种类数
from 商品表 right join 卖家表 on (卖家表.卖家编号=商品表.卖家编号)
group by 卖家表.卖家编号

查询购买商品种类大于3的客户姓名及电话。

select 姓名,电话
from 客户表,购买记录表
where 购买记录表.客户编号=客户表.客户编号
group by 购买记录表.客户编号,姓名,电话
having count(distinct 购买记录表.商品编号)>3

统计店名为“晓晓之家”所卖商品的平均价格。

select avg(单价) 平均价格
from 卖家表,商品表
where 商品表.卖家编号=卖家表.卖家编号
and 店名='晓晓电脑之家'

找出折扣率最低的销售记录。

select *
from 购买记录表
where 折扣率 =
(select min(折扣率)
from 购买记录表)

统计每个客户购买商品的总数量以及节省的费用。

select 姓名,sum(数量) 购买商品数,sum(单价*数量*(100-折扣率)/100) 节省的费用
from 客户表,购买记录表,商品表
where 购买记录表.客户编号=客户表.客户编号
and 商品表.商品编号=购买记录表.商品编号
group by 姓名

统计各种型号（不同编号）打印机的销售总额。

select 名称,sum(数量*单价) 销售总额
from 商品表,购买记录表
where 商品表.商品编号=购买记录表.商品编号
and 名称 like '%打印机%'
group by 名称

找出销售额最高的店名。

select 店名,sum(数量*单价*折扣率/100) 销售总额
from 商品表,购买记录表,卖家表
where 商品表.商品编号=购买记录表.商品编号
and 商品表.卖家编号=卖家表.卖家编号
group by 店名
having sum(数量*单价*折扣率/100)>=all
(select sum(数量*单价*折扣率/100)
from 商品表,购买记录表
where 商品表.商品编号=购买记录表.商品编号
group by 商品编号)

通过集合查询方式统计购买记录中男性客户有几人，女性客户有几人。

select 性别,count(姓名) 人数
from 客户表,购买记录表
where  购买记录表.客户编号=客户表.客户编号
group by 性别
having 性别='女'
union
select 性别,count(姓名) 人数
from 客户表,购买记录表
where  购买记录表.客户编号=客户表.客户编号
group by 性别
having 性别='男'

查询既购买了“计算机”又购买了“打印机”的客户姓名。（通过集合查询实现）

 select 姓名
from 客户表,商品表,购买记录表
where 购买记录表.客户编号=客户表.客户编号
and 商品表.商品编号=购买记录表.商品编号
and 商品表.名称='计算机'
intersect
select 姓名
from 客户表,商品表,购买记录表
where 购买记录表.客户编号=客户表.客户编号
and 商品表.商品编号=购买记录表.商品编号
and 商品表.名称='打印机'

通过集合查询未购买过任一商品的客户信息。（用except操作符完成）

select *
from 客户表
except
select *
from 客户表
where 客户编号 in
(select 客户编号
from 购买记录表)

问题与思考

结合本实验，举例说明集合查询是否可以用其他方式的查询代替？

可以
可以用连接查询方式代替
比如教材例3-66

请简要说明where子句和having子句的不同之处？

Where作用在group by 和having语句之前，having对成组后的数据进行筛选。
分组时where对分组进行限定，不满足条件，不会参与分组。而having 对分组结果进行限定，不满足的结果不会显示到分组。
where不能跟聚合函数，having可以跟聚合函数。