ch04 集合运算
表的加减运算
union会去重并将两表合并
如:
SELECT product_id, product_name
FROM product
UNION
SELECT product_id, product_name
FROM product2;
如果想要包含重复行,则使用union all
mysql中只能使用 inner join 求交集
如:
SELECT p1.product_id, p1.product_name
FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id
差集则只能使用not in 谓词
如:
-- 使用 NOT IN 子句的实现方法
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM Product2)
连结JOIN
总共可分为 : inner join,self join,natural join, outer join
而外/内连接又可分为:左外/内连接,右外/内连接
练习题
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
select *
from product
where sale_price > 500
union
select *
from product2
where sale_price > 500
4.2
借助对称差的实现方式, 求product和product2的交集。
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 shop_id, shop_name, product_id, product_name
from shopProduct inner join product
where sale_price = max(select sale_price
from product
group by product_type
)
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
select product_id, product_name, sale_price
from prodcut as p1
where sale_price = max(select sale_price
from product
group by product_type
)
SELECT P1.product_id
,P1.product_name
,P1.product_type
,P1.sale_price
,P2.avg_price
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.5
用关联子查询实现:在 product 表中,取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
select product_id, product_name, sale_price, sum(sale_price) as all_sum
from product
order by sale_price