WonderfulSQL-task4

最新推荐文章于 2024-09-19 16:47:21 发布

yxyibb

最新推荐文章于 2024-09-19 16:47:21 发布

阅读量129

点赞数

分类专栏：数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/u012835414/article/details/119885571

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

WonderfulSQL-task4

(Datawhale28期组队学习)

基础点-集合运算

表加减
- 表的加法
  - union
  - or
  - union all
  - 隐式数据类型转换
- 不支持交运算INTERSECT-inner join
- 差集、补集、表的减法
  - MySQL 8.0 不支持 EXCEPT 运算
  - not in
  - intersect and
  - 对称差
  - 并集和差集迂回实现交集运算
连结(join)
- 内连结 inner join
  - 自连结 self join
  - 自然连结 natural join
  - 连结求交集
- 外连结 outer join
  - 左右连结
- 多表连结
- 交叉连结 cross join

知识点

1 集合运算（行的运算）

集合，记录的总和。表、视图、查询的执行结果都是记录的集合，其中元素为表或查询结果的每一行。
集合运算符， UNION, INTERSECT, EXCEPT

1.1 集合加法 union

并集：UNION 等集合运算符通常都会除去重复的记录

select xx,yy
from cc
union
select xx,yy
from cc1

用or谓词将条件合并写在where语句中，也可实现并运算。得到union相同的结果。
引入union也有必要性，筛选不同表结果合并时，只能用union，不能用or
当不需要去重，需要全部并集时，可用union all
数据类型不完全相同时，可以通过隐式类型转换将两个类型不同的列放在一列显示
- 注：hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜
- 时间日期类型和字符串,数值以及缺失值均能兼容
- SYSDATE()函数可以返回当前日期时间, 日期时间类型

union 练习

#练习1
select product_id, product_name, product_type, sale_price, purchase_price
from product
where ((sale_price-purchase_price) / purchase_price) > 0.5
union 
select product_id, product_name, product_type, sale_price, purchase_price
from product
where sale_price < 800
#不用union，可以直接合并where语句，两个条件用or连接

#练习2
select *
from product
where  ((sale_price-purchase_price) / purchase_price) < 0.3
or ((sale_price-purchase_price) / purchase_price) is null

#练习3
select *
from product
where (sale_prict - purchase_price)/purchase_price< 0.5
union all
select *
from product
where sale_price < 1000

1.2 交，INTERSECT（MySQL 8.0 不支持）

交：MySQL 8.0 版本仍然不支持 INTERSECT 操作
用inner join实现
两个集合的交可以看作是两个集合的并去掉两个集合的对称差

SELECT p1.product_id, p1.product_name
FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

1.3 集合减法

求集合差集的减法运算：集合A和B做减法只是将集合A中也同时属于集合B的元素减掉

减：MySQL 8.0 还不支持 EXCEPT 运算
not in实现
同一个表的两个查询条件，可以等价用AND连接，实现交运算

not in 练习

#练习1
SELECT * ,(sale_price-purchase_price)/purchase_price as p
from product
where sale_price >2000
and product_id not in(
	select product_id
    from product
    where (sale_price-purchase_price)/purchase_price >=0.3
)
#题目中参考，个人认为有问题，not in，子查询内部应该是>=0.3

#练习2
select * ,(sale_price-purchase_price)/purchase_price as p
from product
where (sale_price-purchase_price)/purchase_price > 0.5
and sale_price < 1500

1.4 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合

两个集合的对称差等于 A-B并上B-A,

对称差练习

select *
from product
where product_id not in (select product_id from product2)
union 
select *
from product2
where product_id not in (select product_id from product)

2 内连接 join（多个表引起列的变换）

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算

简化关联子查询
进行多表间更复杂的查询
连结时需要在 FROM 子句中使用多张表，可以使用as别称，简化
必须使用 ON 子句来指定连结条件
SELECT 子句中的列最好按照表名.列名的格式来使用

2.1 内连接 inner join

找到两个表的公共列
类似excel的vlookup函数

from aa 
inner join bb
on <condition(s)>

2.2 where子句+内连结

where子句放在内连结后：把内连结检索结果作为新表，在外层进行where约束筛选
- 执行顺序： FROM 子句->WHERE 子句->SELECT 子句
任务分解：先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来

2.3 group by 子句+内连结

内连结之前就使用 GROUP BY 子句
分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合

2.4 自连结(SELF JOIN)

一张表与自身作连结, 这种连接称之为自连结
可以是内连结，也可以是外连结，只是连结的对象是自己！

2.5 内连结与关联子查询

找出每个商品种类当中售价高于该类商品的平均售价的商品

# 关联子查询
SELECT product_type, product_name, sale_price
  FROM product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type
                      GROUP BY product_type);
# 内连结+关联子查询
#avg_price
select product_type, avg(sale_price) as avg_price
from product
group by product_type

# >avg_price product's message
select p1.product_id, p1.product_name,p1.product_type, p1.sale_price, p2.avg_price
from product p1
inner join
	(select product_type, avg(sale_price) as avg_price
	 from product
	 group by product_type 
    ) p2
on p1.product_type = p2.product_type
and p1.sale_price > p2.avg_price

2.6 自然连结(NATURAL JOIN)

select *
from shopproduct
natural join product

内连结的一种特例
按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件
会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来
l两个表逐字段进行等值连结，若有值为null的情况，最好提前T出来，否则两个缺失值用“=”比较结果不为真！连结只会返回对连结条件返回为真的那些行。

2.7 求交集

注意：缺失值！！！null情况特殊，不可以用“=”比较，提前T出来！

内连结练习

#练习1:每个商店里的衣服类商品的名称及价格等信息
select sp.shop_id, sp.shop_name, p.product_id, p.product_name, p.product_type, p.purchase_price
from shopproduct as sp
inner join product as p
on sp.product_id = p.product_id
where p.product_type ='衣服';

#练习2:东京商店里, 售价低于 2000 的商品信息
select sp.*, p*
from shopproduct as sp
inner join product as p
on sp.product_id = p.product_id
where sp.shop_name='东京'
and s.sale_price < 2000;

#练习3
#每个商店里售价最高的商品和售价
select sp.shop_id, sp.shop_name, max(P.sale_price) as max_price
from shopproduct as sp
inner join product as p
on sp.product_id = p.product_id
group by sp.shop_id, sp.shop_name

#每个商店里售价最高的商品的名称和售价
#max_price
select product_type, max(sale_price) as max_price
from product
group by product_type;

# max_price is who->product_id
select p1.product_id 
from product as p1
inner join (
	select product_type, max(sale_price) as max_price
	from product
	group by product_type) as p2
on p1.product_type = p2.product_type
where p1.sale_price = p2.max_price

# max_price's product_id -> join stopproduct's shop message
select sp.shop_id, sp.shop_name, sp.product_id,pp.product_type, pp.product_name, pp.sale_price
from shopproduct as sp
inner join product as pp
on sp.product_id = pp.product_id
where pp.product_id in (
	select p1.product_id 
	from product as p1
	inner join (
		select product_type, max(sale_price) as max_price
		from product
		group by product_type) as p2
	on p1.product_type = p2.product_type
	where p1.sale_price = p2.max_price
	) 
#练习4：写出与上述自然连结等价的内连结
SELECT  sp.product_id, sp.shop_id, sp.shop_name, sp.quantity, p.product_name, p.product_type, p.sale_price, p.purchase_price, p.regist_date
FROM shopproduct AS sp 
INNER JOIN Product AS p 
ON sp.product_id = p.product_id

3 外连结 outer join

左连结：保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
右连结：保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值
FROM <tb_1>RIGHT OUTER JOIN <tb_2> ON <condition(s)>
全外连结：同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充
FROM <tb_1>FULL OUTER JOIN <tb_2> ON <condition(s)>

3.1 左连结

选取出单张表中全部的信息
使用 LEFT、RIGHT 来指定主表
将 Product 和 ShopProduct 进行内连结、左连结，对比前后的结果：找到2条记录（在P不在SP，说明这两种商品在所有商店都处于缺货状态, 需要及时补货，很重要的业务信息）

3.2 左连结+where子句

从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店

# 找到某个商店库存少于50的商品，与主表左连结，而不是直接与主表连结再找库存少于50的商品，防止丢掉有null的记录
SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,SP.quantity
FROM Product AS P
LEFT OUTER JOIN (
	select * 
    from shopproduct as sp
    where quantity< 50
) AS SP
ON SP.product_id = P.product_id

3.3 全外连结

全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充
MySQL8.0 目前还不支持全外连结, 可以对左连结和右连结的结果进行 UNION 来实现全外连结

4 多表连结

原则上连结表的数量并没有限制

使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少

#添加多层inner join
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name,p.sale_price,ip.inventory_id, ip.inventory_quantity
from shopproduct as sp
inner join product as p
on sp.product_id = p.product_id
inner join inventoryProduct as ip
on sp.product_id = ip.product_id
where ip.inventory_id='p001'

多表进行外连结

#添加多层inner join
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name,p.sale_price,ip.inventory_id, ip.inventory_quantity
from shopproduct as sp
left join product as p
on sp.product_id = p.product_id
left join inventoryProduct as ip
on sp.product_id = ip.product_id
where ip.inventory_id='p001'

5 ON 子句进阶–非等值连结

比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.

5.1 非等值自左连结(SELF JOIN)

使用非等值自左连结实现排名:

对 Product 表中的商品按照售价赋予排名:
1. 对每一种商品,找出售价不低于它的所有商品
2. 对售价不低于它的商品使用 COUNT 函数计数
排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数)

#对 Product 表中的商品按照售价赋予排名
# p1.sale_price > p2.sale_price
select p1.product_id, p1.product_name, p1.sale_price,
 p2.product_id p2_id, p2.product_name p2_name, p2.sale_price p2_price
from product p1
left join product p2
on ((p1.sale_price > p2.sale_price) or (p1.sale_price = p2.sale_price and p1.product_id <= p2.product_id ))
order by p1.sale_price, p1.product_id
 
#count(pick product_id)
select product_id, product_name, sale_price, sum(p2_price) cum_price
from (
	select p1.product_id, p1.product_name, p1.sale_price,
	p2.product_id p2_id, p2.product_name p2_name, p2.sale_price p2_price
	from product p1
	left join product p2
	on ((p1.sale_price > p2.sale_price) or (p1.sale_price = p2.sale_price and p1.product_id <= p2.product_id ))
	order by p1.sale_price, p1.product_id
) as p
group by product_id, product_name, sale_price
order by sale_price, cum_price

6交叉连结—— CROSS JOIN(笛卡尔积)

连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积
A,B两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合
交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列
ShopProduct 表存在 13 条记录,Product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录
CROSS JOIN连结的 ON 子句是一个恒为真的谓词

#1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP
 CROSS JOIN Product AS P;
 
#2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP , Product AS P;

练习

#4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息
# and
select *
from product, product2
where product.sale_price > 500
and product2.sale_price > 500

#union
select *
from product
where product.sale_price > 500
union
select *
from product2
and product2.sale_price > 500

#4.2 借助对称差的实现方式, 求product和product2的交集
select * 
from 
	(select *
	from product
	union 
	select *
	from product2) pu
where pu.product_id not in  
	(select *
    from product
    where product_id not in (select product_id from product2)
    union
    select *
    from product2
    where product_id not in (select product_id from product)
    )

#4.3 每类商品中售价最高的商品都在哪些商店有售
# max_price
select product_type, max(sale_price) max_price
from product
group by product_type

# max_price-> product_id
select p1.product_id
from product p1
inner join(
	select product_type, max(sale_price) max_price
	from product
	group by product_type
) p2
on p1.product_type = p2.product_type
where p1.sale_price = p2.max_price

# product_id -> shop_id
select sp.shop_id, sp.shop_name, pp.product_name, pp.sale_price
from shopproduct sp
inner join product pp
on sp.product_id = pp.product_id
where pp.product_id in (
	select p1.product_id
	from product p1
	inner join(
		select product_type, max(sale_price) max_price
		from product
		group by product_type
	) p2
	on p1.product_type = p2.product_type
	where p1.sale_price = p2.max_price
)

#4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品
#inner join
select p.product_name, p.product_type, p.sale_price
from product p 
inner join (
	select product_type, max(sale_price) max_price
	from product
	group by product_type
) pp
on p.product_type = pp.product_type
where p.sale_price = pp.max_price

#4.5 用关联子查询实现：在 product 表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和
select product_id, product_name, sale_price, sum(p2_price) cum_price
from (
	select p1.product_id, p1.product_name, p1.sale_price,
	p2.product_id p2_id, p2.product_name p2_name, p2.sale_price p2_price
	from product p1
	left join product p2
	on ((p1.sale_price > p2.sale_price) or (p1.sale_price = p2.sale_price and p1.product_id <= p2.product_id ))
	order by p1.sale_price, p1.product_id
) as p
group by product_id, product_name, sale_price
order by sale_price, cum_price