mysql学习笔记-集合运算-表的加减法和join等

Skill数据分析 2021-04-13

600

drop table if exists product2;
CREATE TABLE product2 LIKE product;
INSERT INTO product2 VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product2 VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product2 VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product2 VALUES('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO product2 VALUES('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');

一、表的加减法

1、表的加法–UNION

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

上述结果包含了两张表中的全部商品. 你会发现,这就是我们在学校学过的集合中的并集运算,通过文氏图会看得更清晰（图 7-1):

UNION 等集合运算符通常都会除去重复的记录,想要不去重结果为 union all

练习题 :

分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品.
参考答案:
-- 使用 OR 谓词
SELECT * 
  FROM product 
 WHERE sale_price  purchase_price < 1.3 
    OR sale_price  purchase_price IS NULL;
-- 使用 UNION
SELECT * 
  FROM product 
 WHERE sale_price  purchase_price < 1.3
 
 UNION
SELECT * 
  FROM product 
 WHERE sale_price  purchase_price IS NULL;

2、包含重复行的集合运算 UNION ALL

-- 保留重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

练习题:

商店决定对product表中利润低于50%和售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:

参考答案

SELECT * 
  FROM product 
 WHERE sale_price < 1000
 UNION ALL
SELECT * 
  FROM product 
 WHERE sale_price < 1.5 * purchase_price

3、[扩展阅读]bag 模型与 set 模型

Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素.

4、隐式类型转换

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

上述查询能够正确执行,得到如下结果:

练习题:

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容.

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 
 UNION
 
SELECT 'chars', 123,  null

上述代码的查询结果:

5、MySQL 8.0 不支持交运算INTERSECT # 交集运算

MySQL 8.0 还不支持 EXCEPT 运算

使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果

二、连结(JOIN)

1、内连结的语法格式是:

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

自然连结(NATURAL JOIN)

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.

SELECT *  FROM shopproduct NATURAL JOIN product

2、外连结(OUTER JOIN)

三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

在 MySQL 中实现全外连结

MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

3、多表连结

通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。

练习题

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  IN (SELECT product_id FROM product2);


SELECT * FROM product p1 INNER JOIN product2 p2 
ON p1.`product_id`=p2.`product_id`;

4.3

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

答：

先求商品最高价：

SELECT product_type,MAX(sale_price) AS max_price FROM product
GROUP BY product_type;

根据最高价，求商品id:

SELECT p1.product_id FROM product p1
INNER JOIN
(SELECT product_type,MAX(sale_price) AS max_price FROM product
GROUP BY product_type) AS p2
ON p1.`sale_price`=p2.max_price
AND p1.`product_type`=p2.product_type;

最终：

SELECT shop_id,shop_name,product_id FROM shopproduct 
WHERE product_id IN
(SELECT p1.product_id FROM product p1
INNER JOIN 
(SELECT product_type,MAX(sale_price) AS max_price FROM product
GROUP BY product_type) AS p2
ON p1.`sale_price`=p2.max_price
AND p1.`product_type`=p2.product_type);

4.4

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

答：

内连接

SELECT p.product_id,p.product_name,p.product_type,p.sale_price FROM product p
INNER JOIN 
(SELECT product_type,MAX(sale_price) AS max_sale_price FROM product 
GROUP BY product_type) AS s
ON p.`product_type`= s.product_type 
AND p.`sale_price`= s.max_sale_price;

子查询

SELECT p1.product_id,p1.product_name,p1.product_type,p1.sale_price 
FROM product p1
WHERE p1.sale_price IN (SELECT MAX(sale_price) AS max_sale_price 
FROM product p2 
WHERE p1.`product_type`=p2.`product_type`
GROUP BY product_type);

4.5

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

答：

累加功能的实现

SET @csum := 0;
SELECT product_id, product_name, sale_price,(@csum:=@csum + sale_price)
    AS '累计销售价格'
FROM product 
ORDER BY sale_price,;


窗口函数：
SELECT product_id, product_name, sale_price,
  SUM(sale_price)over(ORDER BY sale_price,product_id) AS "累计售价"     
FROM product;

mysql

文章转载自Skill数据分析，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mysql学习笔记-集合运算-表的加减法和join等

评论