实验05 连接查询

实验目的

要求学生掌握在数据库中通过DMSQL语句通过连接查询语句查询出相关数据的方法。

实验要求

（1） 笛卡儿积的查询。
（2） 等值(自然)连接的查询。
（3） 左（右）外连接的查询。
（4） 自连接的查询。

实验内容

查询有“计算机”的店名，负责人，客服电话。

select 店名,负责人,客服电话
from 销售管理.卖家表,销售管理.商品表
where 销售管理.商品表.名称='计算机' 
 and 销售管理.卖家表.卖家编号=销售管理.商品表.卖家编号

查询购买了“无盘工作站”的客户姓名、年龄、电话、地址。

select 姓名,年龄,电话,地址
from 销售管理.客户表,销售管理.商品表,销售管理.购买记录表
where 销售管理.商品表.名称='无盘工作站' 
 and 销售管理.购买记录表.商品编号=销售管理.商品表.商品编号
 and 销售管理.购买记录表.客户编号=销售管理.客户表.客户编号

查询商品表中商品种类大于三种的卖家店名、负责人、商品数量。

select 店名,负责人,count(distinct 销售管理.商品表.商品编号) 商品数量
from 销售管理.卖家表,销售管理.商品表
where 销售管理.卖家表.卖家编号=销售管理.商品表.卖家编号
group by 销售管理.商品表.卖家编号,店名,负责人
having count(distinct 销售管理.商品表.商品编号)>3

查询购买了五种以上商品的客户姓名、电话、商品名称、数量、地址。

select 姓名,电话,名称,数量,地址
from 销售管理.客户表,销售管理.购买记录表,销售管理.商品表
where 销售管理.客户表.客户编号=销售管理.购买记录表.客户编号
 and 销售管理.购买记录表.商品编号=销售管理.商品表.商品编号
 and 销售管理.购买记录表.客户编号 in
 （select 客户编号
 from 销售管理.购买记录表
group by 客户编号
having count(distinct 商品编号)>=5）

查询所有店铺的店名、负责人、客服电话及其商品名称，单价。（不论该店铺是否有商品，均需要列出店铺信息）

select 店名,负责人,客服电话,名称,单价
from 销售管理.卖家表 left join 销售管理.商品表
on 销售管理.卖家表.卖家编号=销售管理.商品表.卖家编号

查询营业额超过50000元的卖家店名、负责人。

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

统计每种商品的销售数量，显示为商品名称、销量，并按销量降序排列。

select 名称,sum(数量) 销量
from 销售管理.购买记录表,销售管理.商品表
where 销售管理.购买记录表.商品编号=销售管理.商品表.商品编号
group by 销售管理.购买记录表.商品编号,名称
order by sum(数量) desc

查询没有购买商品的客户姓名、电话、地址。

select 姓名,电话,地址
from 销售管理.客户表
where 客户编号 not in
(select 客户编号
from 销售管理.购买记录表)

查询同时购买了“计算机”和“打印机”的客户姓名、电话、商品名称、数量、地址。

select姓名
from客户表
where客户编号in (select 客户编号
				from购买记录表
				where商品编号in (select 商品编号
								from商品表
								where名称='计算机'))
and 客户编号in （select客户编号
				from购买记录表
				where商品编号in (select 商品编号
								from商品表
								where名称='打印机'））

总结与思考

关系代数中的笛卡尔积、等值（不等值）连接的意义？

1. 笛卡尔积
对两个关系R和S进行操作，产生的关系中元组个数为两个关系中元组个数之积。
2. 等值联接
在笛卡尔积的结果上再进行选择操作，挑选关系第i个分量与第(r+j)个分量值相等的元组

连接查询中SQL语句的执行顺序是什么？如何优化？

1 from>on>join>where>group by>having>select>distinct>order by>limit
2 优化:最大化利用索引,尽可能避免全表扫描,减少无效数据的查询；
3 避免出现select *,取出全部列
4 多表关联查询时，小表在前，大表在后, 执行 from 后的表关联查询是从左往右执行的，第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高
5 调整Where字句中的连接顺序.SQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。