4.1表的加法--UNION

SELECT product_id, product_name

  FROM product

UNION

SELECT product_id, product_name

  FROM product2;

假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

结果应该类似于:

SELECT product_id,product_name,product_type,sale_price,purchase_price

FROM product

WHERE sale_price>1.5*purchase_price

UNION

SELECT product_id,product_name,product_type,sale_price,purchase_price

FROM product

WHERE sale_price>800

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

-- 保留重复行

SELECT product_id, product_name

  FROM Product

UNION ALL

SELECT product_id, product_name

  FROM Product2;

result:

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

SELECT  product_id,product_name,product_type,sale_price,purchase_price,regist_date

FROM product

WHERE sale_price<1.5*purchase_price

UNION ALL

SELECT  product_id,product_name,product_type,sale_price,purchase_price,regist_date

FROM product

WHERE sale_price<1000;

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

SELECT product_id, product_name, '1'

  FROM Product

UNION

SELECT product_id, product_name,sale_price

  FROM Product2;

需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

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

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

SELECT SYSDATE(), SYSDATE(), SYSDATE()

UNION

SELECT 'chars', 123,  null

上述代码的查询结果:

4.2MySQL 8.0 不支持交运算INTERSECT

此时需要用 inner join 来求得交集

4.3差集,补集与表的减法

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

找出只存在于Product表但不存在于Product2表的商品。

--使用 NOT IN 子句的实现方法

SELECT *

  FROM Product

WHERE product_id NOT IN (SELECT product_id

                           FROM Product2)

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

 INTERSECT 与 AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。

4.4对称差

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

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

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

SELECT *

FROM product1 

WHERE product_id NOT IN (SELECT * FROM product2)

UNION

SELECT *

FROM product2

WHERE product_id NOT IN (SELECT * FROM product1)

 4.6连结(JOIN)

4.6.1内连结(INNER JOIN)

-- 内连结

FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

关于内连结,需要注意以下三点:

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

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

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

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

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

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

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

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

结合 WHERE 子句使用内连结

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

上述查询的执行顺序:FROM 子句->WHERE 子句->SELECT 子句

找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:

SELECT SP.shop_id,SP.shop_name,SP.product_id

       ,P.product_name, P.product_type, P.purchase_price

FROM shopproduct  AS SP

INNER JOIN Product AS P

ON SP.product_id = P.product_id

WHERE P.product_type = '衣服';

分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

不使用子查询
SELECT SP.shop_id,SP.shop_name,SP.product_id

       ,P.quantity,P.product_id,P.product_name, P.product_type, P.sale_price

FROM shopproduct  AS SP

INNER JOIN Product AS P

ON SP.product_id = P.product_id

WHERE P.sale_price<2000 and SP.shop_name="东京";

使用子查询

SELECT SP.shop_id,SP.shop_name,SP.product_id

       ,P.quantity,P.product_id,P.product_name, P.product_type, P.sale_price

FROM shopproduct  AS SP

WHERE SP.shop_name="东京"

INNER JOIN

（SELECT P.quantity,P.product_id,P.product_name, P.product_type, P.sale_price

FROM product AS P

WHERE P.sale_price<2000)

ON SP.product_id = P.product_id

结合 GROUP BY 子句使用内连结

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

4.6.2自连结(SELF JOIN)

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

找出每个商品种类当中售价高于该类商品的平均售价的商品

自然连结(NATURAL JOIN)

SELECT * FROM shopproduct NATURAL JOIN Product

当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

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

求表 Product 和表 Product2 中的公共部分, 也可以用自然连结来实现:

SELECT *

  FROM (SELECT product_id, product_name

          FROM Product ) AS A 

NATURAL JOIN 

  (SELECT product_id, product_name

     FROM Product2) AS B;

使用内连结求 Product 表和 Product2 表的交集。

SELECT P1.*

  FROM Product AS P1

 INNER JOIN Product2 AS P2

   ON P1.product_id = P2.product_id

4.6.3外连结(OUTER JOIN)

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行。

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。

-- 左连结

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)>

统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品。

SELECT SP.shop_id

      ,SP.shop_name

      ,SP.product_id

       ,P.product_name

      ,P.sale_price

  FROM Product AS P

  LEFT OUTER JOIN ShopProduct AS SP

   ON SP.product_id = P.product_id;

外连结要点 1: 选取出单张表中全部的信息

外连结要点 2：使用 LEFT、RIGHT 来指定主表.

结合 WHERE 子句使用左连接

 在 MySQL 中实现全外连结

多表进行内连结

多表进行外连结

ON 子句进阶--非等值连结

交叉连结—— CROSS JOIN(笛卡尔积)

交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 

练习题

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 * FROM product2)

UNION

SELECT *

FROM product2

WHERE product_id not in (SELECT * FROM product)

4.3

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

SELECT P.product_name,MAX(P.sale_price) AS max_price,SP.shop_name

FROM product AS P

INNER JOIN shop_product AS SP

ON P.product_id=SP.product_id

GROUP BYSP.shop_name

4.4

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

内连结

SELECT  P1.product_id

      ,P1.product_name

       ,P1.product_type

      ,P1.sale_price

       ,P2.max_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;

关联子查询

SELECT product_name

FROM 

(SELECT product_name,max(sale_price),product_type

FROM product

GROUP BY product_type) AS P2;

4.5

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

SELECT product_id, produc_name, slae_price,(SELECT sum(p2.sale_price) FROM product as p2 where p2.sale_price<=p1.sale_price)as cum_sales

FROM product as p1

ORDER BY sale_price DESC