sql（task04）

最新推荐文章于 2024-11-12 14:54:23 发布

空间转换

最新推荐文章于 2024-11-12 14:54:23 发布

阅读量138

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_54102797/article/details/126472869

版权

集合运算（union、intersect、except）

**注意后两者已不在出现在MySQL8.0，需要改用in、and来解决

连结（join）

1、inner join

连结需要注意的点：1、连结时在from语句中使用多张表 2、需要用on来设置连结条件 3、select语句中按照表名.列名格式来使用

2、natural join

自然连结是内连结的一个特例

3、outer join

外连结特点：1、挑选单张表的所有信息。 2、使用left、right当指定主表，主表保留所有信息

4.1 
SELECT * FROM product WHERE sale_price > 500
UNION
SELECT * FROM product2 WHERE sale_price > 500;

4.2
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
select p1.product_name, p1.product_type, p1.sale_price, p2.shop_name, p3.max_price as '每类商品的最高价格'
from product as p1
inner join shopproduct as p2
on p1.product_id = p2.product_id
inner join 
(select product_type, max(sale_price) as max_price
 from product group by product_type) as p3
on p3.product_type = p1.product_type and p1.sale_price = p3.max_price;

4.4
内连结
select p1.product_id,p1.product_id,p1.product_type,p1.sale_price, p2.max_price as '该类商品最大价格'
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
and p1.sale_price = p2.max_price;

关联子查询
select p1.product_id,p1.product_id,p1.product_type,p1.sale_price, p2.max_price as '该类商品最大价格'
from product as p1，
(select product_type,max(sale_price) as max_price
					 from product
					 group by product_type)as p2
where p1.product_type = p2.product_type
and p1.sale_price = p2.max_price;

4.5
select p1.product_id, p1.product_name, p1.sale_price, 
(select sum(sale_price) from product as p2 where p1.sale_price > p2.sale_price or p1.sale_price = p2.sale_price)
as '累加求和'
from product as p1
order by sale_price;