Task04-天池龙珠计划SQL训练营

学习目标

• 集合运算-表的加减法和join等

---

学习内容

4.1表的加减法
4.1.1 什么是集合运算
4.1.2 表的加法–UNION
4.1.2.1 UNION
4.1.2.2UNION 与 OR 谓词
4.1.2.3 包含重复行的集合运算 UNION ALL
4.1.2.4[扩展阅读]bag 模型与 set 模型
4.1.2.5隐式类型转换
4.1.3 MySQL 8.0 不支持交运算INTERSECT
4.1.3.1[扩展阅读]bag 的交运算
4.1.4 差集,补集与表的减法
4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算
4.1.4.2 EXCEPT 与 NOT 谓词
4.1.4.3EXCEPT ALL 与bag 的差
4.1.4.4INTERSECT 与 AND 谓词
4.1.5对称差
4.1.5.1借助并集和差集迂回实现交集运算 INTERSECT

4.2连结(JOIN)
4.2.1 内连结(INNER JOIN)
4.2.1.1 使用内连结从两个表获取信息
4.2.1.2 结合 WHERE 子句使用内连结
4.2.1.3结合 GROUP BY 子句使用内连结
4.2.1.4自连结(SELF JOIN)
4.2.1.5内连结与关联子查询
4.2.1.6自然连结(NATURAL JOIN)
4.2.1.7使用连结求交集
4.2.2 外连结(OUTER JOIN)
4.2.2.1 左连结与右连接
4.2.2.2 使用左连结从两个表获取信息
4.2.2.3结合 WHERE 子句使用左连结
4.2.2.4在 MySQL 中实现全外连结
4.2.3多表连结
4.2.3.1 多表进行内连结
4.2.3.2多表进行外连结
4.2.4ON 子句进阶–非等值连结
4.2.4.1非等值自左连结(SELF JOIN)
4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)
4.2.5.1[扩展阅读]连结与笛卡儿积的关系
4.2.6 连结的特定语法和过时语法

---

学习产出

• 练习题一

找出 product 和 product2 中售价高于 500 的商品的基本信息。

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

• 练习题二

借助对称差的实现方式, 求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 );

交集：

SELECT
	* 
FROM
	( SELECT * FROM product UNION SELECT * FROM product2 ) AS u 
WHERE
	product_id NOT IN (
	SELECT
		product_id 
	FROM
		product 
	WHERE
		product_id NOT IN ( SELECT product_id FROM product2 ) UNION
	SELECT
		product_id 
	FROM
		product2 
	WHERE
	product_id NOT IN ( SELECT product_id FROM product ) 
	);

• 练习题三

每类商品中售价最高的商品都在哪些商店有售 ？

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.quantity,
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price,
	mp.maxp AS '该类商品的最大售价' 
FROM
	product AS p
	INNER JOIN shopproduct AS sp ON sp.product_id = p.product_id
	INNER JOIN ( SELECT product_type, max( sale_price ) AS maxp FROM product 
	GROUP BY product_type ) AS mp ON mp.product_type = p.product_type 
	AND p.sale_price = mp.maxp;

• 练习题四

分别使用内连结和关联子查询每一类商品中售价最高的商品。

内连结：

SELECT
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	product AS p
	INNER JOIN ( SELECT product_type, max( sale_price ) AS maxp FROM product GROUP BY product_type ) AS mp ON mp.product_type = p.product_type 
	AND p.sale_price = mp.maxp;

关联子查询：

SELECT
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	product AS p 
WHERE
	sale_price = ( SELECT max( sale_price ) FROM product AS p1 WHERE p.product_type = p1.product_type GROUP BY product_type );

• 练习题五

用关联子查询实现：在product表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

SELECT
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price,
	(
	SELECT
		sum( sale_price ) 
	FROM
		product AS p1 
	WHERE
		p.sale_price > p1.sale_price 
		OR ( p.sale_price = p1.sale_price AND p.product_id >= p1.product_id ) 
	) AS '累计求和' 
FROM
	product AS p 
ORDER BY
	sale_price;