Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营

最新推荐文章于 2022-04-13 23:36:53 发布

瓜wz

最新推荐文章于 2022-04-13 23:36:53 发布

阅读量162

点赞数

文章标签： dba

本文链接：https://blog.csdn.net/JCPP__/article/details/123912187

版权

4.1表的加减法

4.1.1 什么是集合运算

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

以下的文氏图展示了几种集合的基本运算.

4.1.2 表的加法–UNION

SELECT product_id, product_name FROM product

UNION SELECT product_id, product_name FROM product2;

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

但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.

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

4.1.2.5隐式类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

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

上述代码的查询结果:

4.1.4 差集,补集与表的减法

求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算

MySQL 8.0 还不支持表的减法运算符 EXCEPT. 不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法.

练习题**:**

找出只存在于product表但不存在于product2表的商品.

-- 使用 IN 子句的实现方法
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

4.1.5对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差.

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差.

练习题:

使用product表和product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:

提示: 使用 NOT IN 实现两个表的差集.

参考答案:

-- 使用 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.2连结(JOIN)

前一节我们学习了 UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作. 通俗地说, 就是进行这些集合运算时, 会导致记录行数的增减. 使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数.

但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息. 如果想要从多个表获取信息, 例如, 如果我们想要找出某个商店里的衣服类商品的名称,数量及价格等信息, 则必须分别从shopproduct 表和product 表获取信息.

4.2.1 内连结(INNER JOIN)

内连结的语法格式是:

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

按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 shopproduct.product_id=product.product_id, 就得到了如下的查询语句:

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id;

在上述查询中, 我们分别为两张表指定了简单的别名, 这种操作在使用连结时是非常常见的, 通过别名会让我们在编写查询时少打很多字, 并且更重要的是, 会让查询语句看起来更加简洁.
上述查询将会得到如下的结果:

要点一: 进行连结时需要在 FROM 子句中使用多张表.

之前的 FROM 子句中只有一张表, 而这次我们同时使用了shopproduct 和product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:

FROMshopproduct AS SP INNER JOINproduct AS P

要点二:必须使用 ON 子句来指定连结条件.

在进行内连结时 ON 子句是必不可少的(大家可以试试去掉上述查询的 ON 子句后会有什么结果).

ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用, 我们会在本章的结尾部分进一步探讨这个话题.

要点三: SELECT 子句中的列最好按照表名.列名的格式来使用.

当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.

但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.

我们回到上述查询所回答的问题. 通过观察上述查询的结果, 我们发现, 这个结果离我们的目标: 找出东京商店的衣服类商品的基础信息已经很接近了. 接下来,我们只需要把这个查询结果作为一张表, 给它增加一个 WHERE 子句来指定筛选条件.

4.2.1.2 结合 WHERE 子句使用内连结

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.

例如, 对于上述查询问题, 我们可以在前一步查询的基础上, 增加 WHERE 条件.

增加 WHERE 子句的方式有好几种,我们先从最简单的说起.

第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件.

SELECT *
  FROM (-- 第一步查询的结果
        SELECT SP.shop_id
               ,SP.shop_name
               ,SP.product_id
               ,P.product_name
               ,P.product_type
               ,P.sale_price
               ,SP.quantity
          FROMshopproduct AS SP
         INNER JOINproduct AS P
            ON SP.product_id = P.product_id) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;

还记得我们学习子查询时的认识吗? 子查询的结果其实也是一张表,只不过是一张虚拟的表, 它并不真实存在于数据库中, 只是数据库中其他表经过筛选,聚合等查询操作后得到的一个"视图".
这种写法能很清晰地分辨出每一个操作步骤, 在我们还不十分熟悉 SQL 查询每一个子句的执行顺序的时候可以帮到我们.

但实际上, 如果我们熟知 WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法:

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

我们首先给出上述查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句

4.2.1.3结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.

最简单的情形, 是在内连结之前就使用 GROUP BY 子句.

但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合.

练习题:

每个商店中, 售价最高的商品的售价分别是多少?

-- 参考答案
SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

4.2.1.4自连结(SELF JOIN)

之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法.

4.2.1.6自然连结(NATURAL JOIN)

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

SELECT *  FROM shopproduct NATURAL JOIN product

上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来.

练习题

4.1

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

select * from product

where sale_price>500

union select * from procuct2

where sale_price>500

4.2

借助对称差的实现方式, 求product和product2的交集。

select * from product

#并集 not in #差集

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

union

select * from product2

#并集 not in #差集

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

4.3

每个商店中, 售价最高的商品的售价分别是多少?

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

SELECT SP.shop_id

,SP.shop_name

P.product_type

, MAX(P.sale_price) AS max_price

FROM shopproduct AS SP

INNER JOINproduct AS P

ON SP.product_id = P.product_id ,

product_type=product2_type

GROUP BY SP.shop_id,SP.shop_name,P.product_type

4.4

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

SELECT product_type, product_name, sale_price

FROM product AS P1 WHERE sale_price > (SELECT MAX(sale_price)

FROM product AS P2 WHERE P1.product_type = P2.product_type

GROUP BY product_type);

4.5

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

select product_id, produc_name, slae_price,SUM(sale_price) as count

from product

order by asc

瓜wz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营

4.1表的加减法4.1.1 什么是集合运算集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。以下的文氏图展示了几种集合的基本运算.4.1.2
复制链接

扫一扫