SQL--Task04 集合运算

已于 2022-08-28 18:25:54 修改
阅读量909 收藏 2
点赞数 1
分类专栏： SQL 文章标签： sql 数据库
于 2022-08-28 18:19:29 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_68175518/article/details/126572232
版权

4.1 表的加减法

4.1.1 什么是集合运算

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

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

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

在数据库中, 所有的表--以及查询结果--都可以视为集合, 因此也可以把表视为集合进行上述集合运算, 在很多时候, 这种抽象非常有助于对复杂查询问题给出一个可行的思路。

4.1.2 表的加法--UNION

4.1.2.1 UNION--并集运算

对不同的两张表：

SELECT product_id, product_name

  FROM product

 UNION

SELECT product_id, product_name

  FROM product2;

上述结果包含了两张表中的全部商品，即并集运算。

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

对于同一张表：

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

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM PRODUCT 
 WHERE sale_price<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 
    OR sale_price > 1.5 * purchase_price;

4.1.2.2 UNION 与 OR 谓词

对于同一个表的结果合在一起，使用 UNION或 OR 谓词连接, 会得到相同的结果（UNION效率较高）, 但若要将两个不同的表中的结果合并在一起, 就只能用 UNION 了.。

例如,分别使用 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;

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

SQL 语句的 UNION 会对查询的结果集进行合并和去重, 但若想在 UNION 的结果中保留重复行,就需要在 UNION 后面添加 ALL 关键字。

例如,  想要知道 product 和 product2 中所包含的商品种类及每个种类里每种商品的数量

-- 保留重复行
SELECT product_type
  FROM Product
 UNION ALL
SELECT product_type
  FROM Product2;

查询结果如下:

4.1.2.4 隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 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.1.3交运算INTERSECT，inner join

集合的交, 就是两个集合的公共部分, 遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。

如果：

SELECT product_id, product_name
  FROM Product
  INTERSECT
SELECT product_id, product_name
  FROM Product2

则会报错，此时需要用 inner join 来求得交集

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

4.1.4 差集,补集与表的减法

差集：集合A和B做减法，即将集合A中也同时属于集合B的元素减掉。

4.1.4.1 EXCEPT 运算--差集

MySQL 8.0 还不支持表的减法运算符 EXCEPT. 此时需要用NOT IN 谓词实现表的减法。

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

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

4.1.4.2 EXCEPT 与 NOT 谓词

使用 NOT IN 谓词和EXCEPT运算效果大致相同。

例如，使用NOT谓词进行集合的减法运算, 求出 Product 表中, 售价高于2000、成本利润率不低于 30% 的商品。

SELECT * 
  FROM Product
 WHERE sale_price > 2000 
   AND product_id NOT IN (SELECT product_id 
                            FROM Product 
                           WHERE sale_price < 1.3*purchase_price)

4.1.4.3 INTERSECT 与 AND 谓词

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

如上题：

SELECT * 
  FROM Product
 WHERE sale_price > 2000 
   AND sale_price > 1.3*purchase_price

4.1.5 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合.

求对称差：

使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集。

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此要使用差集运算，两个集合的对称差等于 A-B并上B-A。

例如，使用Product表和Product2表的对称差来查询哪些商品只在其中一张表。

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

*借助并集和差集迂回实现交集运算 INTERSECT：两个集合的交可以看作是两个集合的并去掉两个集合的对称差。

4.2 连结(JOIN)

UNION和INTERSECT 等集合运算的特征就是以行方向为单位进行操作：使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数。但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息. 如果想要从多个表获取信息,就需要使用连结了。

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.连结能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。

4.2.1 内连结(INNER JOIN)

语法:

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

其中 INNER 关键词表示使用了内连结。

例如,找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。

4.2.1.1 使用内连结从两个表获取信息

我们先来分别观察所涉及的表,

 所以问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来。这就是连结运算所要作的事情。

对比上述两张表, 可以发现,商品编号列是一个公共列, 因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”，将Product和ShopProduct这两张表连接起来。我们把上述问题进行分解:首先, 找出每个商店的商店编号, 商店名称, 商品编号, 商品名称,  商品类别,  商品售价,商品数量信息。按照内连结的语法, 在 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
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id;

在上述查询中, 我们分别为两张表指定了简单的别名,让查询语句看起来更加简洁。

查询结果如下:

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

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

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

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

4.2.1.2 结合 WHERE 子句使用内连结

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

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

方法一：把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。

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

子查询实质是数据库中其他表经过筛选,聚合等查询操作后得到的一个"视图"。

方法二：两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选,  最后, SELECT 子句选出了那些我们需要的列。

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

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

方法三：将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来。（不是太容易阅读, 不建议使用。）

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON (SP.product_id = P.product_id
   AND SP.shop_name = '东京'
   AND P.product_type = '衣服') ;

方法四：先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
        SELECT *
          FROM ShopProduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
   (SELECT *
      FROM Product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

*练习题:

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

 
--方案 1--不使用子查询
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 = '衣服';
-- 方案 2--使用子查询
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 表找出衣服类商品的信息
  (SELECT product_id, product_name, product_type, purchase_price
     FROM Product        
    WHERE product_type = '衣服')AS P 
   ON SP.product_id = P.product_id;

2、找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。

 

SELECT SP.*, P.*
 

  FROM shopproduct AS SP 
 

 INNER JOIN product AS P 
 

    ON SP.product_id = P.product_id
 

 WHERE shop_id = '000A'
 

   AND sale_price < 2000;
 

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

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待：最简单的情形, 是在内连结之前就使用 GROUP BY 子句. 但如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
 

*练习题:
 

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

SELECT SP.shop_id
 

      ,SP.shop_name
 

      ,MAX(P.sale_price) AS max_price
 

  FROM shopproduct AS SP
 

 INNER JOIN product 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.5 内连结与关联子查询
 

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

使用关联子查询来实现：
 

SELECT product_type, product_name, sale_price
 

  FROM product AS P1
 

 WHERE sale_price > (SELECT AVG(sale_price)
 

                       FROM product AS P2
 

                      WHERE P1.product_type = P2.product_type
 

                      GROUP BY product_type);
 

使用内连结实现:
 

首先, 使用 GROUP BY 按商品类别分类计算每类商品的平均价格。
 

SELECT  product_type
 

       ,AVG(sale_price) AS avg_price 
 

  FROM Product 
 

 GROUP BY product_type;
 

接下来, 将上述查询与表 Product 按照 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,AVG(sale_price) AS avg_price 
 

      FROM Product 
 

     GROUP BY product_type) AS P2 
 

    ON P1.product_type = P2.product_type;
 

最后, 增加 WHERE 子句, 找出那些售价高于该类商品平均价格的商品.完整的代码如下:
 

WHERE P1.sale_price > P2.avg_price;
 

4.2.1.6 自然连结(NATURAL JOIN)
 

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

SELECT *  FROM shopproduct NATURAL JOIN Product
 

上述查询得到的结果, 会把两个表的公共列放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。
 

与上述自然连结等价的内连结：
 

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

       ,P.product_name,P.product_type,P.sale_price
 

       ,P.purchase_price,P.regist_date  
 

  FROM shopproduct AS SP 
 

 INNER JOIN Product AS P 
 

    ON SP.product_id = P.product_id
 

使用自然连结还可以求出两张表或子查询的公共部分。
 

例如：求表 Product 和表 Product2 中的公共部分
 

SELECT * FROM Product NATURAL JOIN Product2
 

 

 

但是，结果少了regist_date 字段为空的运动 T 恤。因为ISNULL,IS NOT NULL两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行。因此，要将查询语句进行修改:
 

SELECT * 
 

  FROM (SELECT product_id, product_name
 

          FROM Product ) AS A 
 

NATURAL JOIN 
 

   (SELECT product_id, product_name 
 

      FROM Product2) AS B;
 

4.2.1.7 使用连结求交集
 

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

SELECT P1.*
 

  FROM Product AS P1
 

 INNER JOIN Product2 AS P2
 

    ON (P1.product_id  = P2.product_id
 

   AND P1.product_name = P2.product_name
 

   AND P1.product_type = P2.product_type
 

   AND P1.sale_price   = P2.sale_price
 

   AND P1.regist_date  = P2.regist_date)
 

得到如下结果
 

 

但结果少了 product_id='0003'这一行, 观察源表数据可发现, 少的这行数据的 regist_date 为缺失值,同理，修改如下： 
 

SELECT P1.*
 

  FROM Product AS P1
 

 INNER JOIN Product2 AS P2
 

    ON P1.product_id = P2.product_id
 

查询结果:
 

 

 

4.2.2 外连结(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)>
 

4.2.2.1 左连结与右连结
 

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.我们先以左连结为例，所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果。
 

4.2.2.2 使用左连结从两个表获取信息
 

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

使用左连结的代码如下:
 

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;
 

上述查询得到的检索结果如下(由于未使用 ORDER BY 子句指定顺序,故顺序不唯一):
 

 

 

观察上述结果, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售
 

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

内连结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结。外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了。
 

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

外连结的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表。
 

区别：
 

1.含义不同
 

左连接：只要左边表中有记录，数据就能检索出来，而右边有的记录必需在左边表中有的记录才能被检索出来。
 

2.运行空值不同
 

左连接是已左边表中的数据为基准，若左表有数据右表没有数据，则显示左表中的数据右表中的数据显示为空。右联接是将返回右表的所有行，如果右表的某行在左表中没有匹配行，则将为左表返回空值。
 

通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。
 

4.2.2.3 结合 WHERE 子句使用左连结
 

例如，使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。
 

 

 

注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。
 

使用内连结加上WHERE quantity< 50和 `OR quantity IS NULL` 的条件, 便可以得到预期的结果。然而在真实的查询环境中, 由于数据量大且数据质量并非设想的那样"干净", 我们并不能容易地意识到缺失值等问题数据的存在, 因此,还需另想方法使得它能够适应更复杂的真实数据的情形：
 

SQL查询的执行顺序(FROM->WHERE->SELECT),所以问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。所以可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从ShopProduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。
 

我们把上述思路写成SQL查询语句:
 

SELECT P.product_id
 

      ,P.product_name
 

      ,P.sale_price
 

      ,SP.shop_id
 

      ,SP.shop_name
 

      ,SP.quantity 
 

  FROM Product AS P
 

  LEFT OUTER JOIN-- 先筛选quantity<50的商品
 

   (SELECT *
 

      FROM ShopProduct
 

     WHERE quantity < 50 ) AS SP
 

    ON SP.product_id = P.product_id
 

4.2.2.4 在 MySQL 中实现全外连结
 

全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。
 

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

4.2.3 多表连结
 

4.2.3.1 多表进行内连结
 

先创建一个用于三表连结的表 InventoryProduct：首先我们创建一张用来管理库存商品的表,  假设商品都保存在 P001 和 P002 这 2 个仓库之中。
 

 

 

建表语句如下:
 

CREATE TABLE InventoryProduct
 

( inventory_id       CHAR(4) NOT NULL,
 

product_id         CHAR(4) NOT NULL,
 

inventory_quantity INTEGER NOT NULL,
 

PRIMARY KEY (inventory_id, product_id));
 

然后插入一些数据:
 

--- DML：插入数据
 

START TRANSACTION;
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0001', 0);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0002', 120);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0003', 200);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0004', 3);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0005', 0);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0006', 99);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0007', 999);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P001', '0008', 200);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0001', 10);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0002', 25);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0003', 34);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0004', 19);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0005', 99);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0006', 0);
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0007', 0 );
 

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
 

VALUES ('P002', '0008', 18);
 

COMMIT;
 

接下来, 我们根据上表及 ShopProduct 表和 Product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少。
 

SELECT SP.shop_id
 

       ,SP.shop_name
 

       ,SP.product_id
 

       ,P.product_name
 

       ,P.sale_price
 

       ,IP.inventory_quantity
 

  FROM ShopProduct AS SP
 

 INNER JOIN Product AS P
 

    ON SP.product_id = P.product_id
 

 INNER JOIN InventoryProduct AS IP
 

    ON SP.product_id = IP.product_id
 

 WHERE IP.inventory_id = 'P001';
 

直接往后连，即使想要把连结的表增加到 4 张、5 张……使用 INNER JOIN 进行添加的方式也是完全相同的。
 

4.2.3.2 多表进行外连结
 

SELECT P.product_id
 

       ,P.product_name
 

       ,P.sale_price
 

       ,SP.shop_id
 

       ,SP.shop_name
 

       ,IP.inventory_quantity
 

  FROM Product AS P
 

LEFT OUTER JOIN ShopProduct AS SP
 

ON SP.product_id = P.product_id
 

LEFT OUTER JOIN InventoryProduct AS IP
 

ON SP.product_id = IP.product_id
 

查询结果
 

 

 

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

除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。
 

4.2.4.1 非等值自左连结(SELF JOIN)
 

使用非等值自左连结实现排名。
 

例如:希望对 Product 表中的商品按照售价赋予排名.
 

一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 如, 对于价格最高的商品, 
 

SELECT  product_id
 

       ,product_name
 

       ,sale_price
 

       ,COUNT(p2_id) AS my_rank
 

  FROM (--使用自左连结对每种商品找出价格不低于它的商品
 

        SELECT P1.product_id
 

               ,P1.product_name
 

               ,P1.sale_price
 

               ,P2.product_id AS P2_id
 

               ,P2.product_name AS P2_name
 

               ,P2.sale_price AS P2_price 
 

          FROM Product AS P1 
 

          LEFT OUTER JOIN Product AS P2 
 

            ON P1.sale_price <= P2.sale_price 
 

        ) AS X
 

 GROUP BY product_id, product_name, sale_price
 

 ORDER BY my_rank; 
 

注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值。
 

注 2: 上述排名方案存在一些问题--如果两个商品的价格相等, 则会导致两个商品的排名错误。
 

注 3: 实际上, 进行排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习), 但在较低版本的 MySQL 中只能使用上述自左连结的思路。
 

练习题
 

4.1
 

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

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

4.2
 

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

-- 两个集合的交集就是两个集合的并集减去两个集合的对称差
-- 先求并集
SELECT * 
FROM
(SELECT * FROM product
 UNION
 SELECT * FROM product2) AS T
-- 减去对称差
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));
 

4.3
 

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

-- 取出想要的字段
SELECT sp.shop_id, sp.shop_name, sp.product_id ,p.product_type
    FROM shopproduct sp
JOIN product p
  ON sp.product_id=p.product_id
 WHERE sp.product_id in
-- 过滤每个类型售价最高的商品
(SELECT product_id 
     FROM product p1
 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 
        AND p1.sale_price=p2.max_price);
 

4.4
 

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

 --方法1：关联子查询
 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

--方法2：先连接，再过滤
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
WHERE P1.sale_price= p2.max_price; 
 


                

        

        

    




    

      

        

            

              
                
                  努摆的皮卡皮
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    1
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    2
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
SQL练习题——店铺UV、访客信息

				
			

			

				

					ws19920726的博客
				

				

					05-16
					
					1861
					
				

			

		

		

			
				
数据表

问题：

每个店铺的UV（访客数）
每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

问题1解答：
select 
	shop, count(distinct user_id) as count_user
from jd
group by shop


问题2解答：
根据题意，我们可以先将问题拆分：
1）获取每个店铺的访客次数信息
select 
	shop, user_id, count(user_id) as count_user
from jd
group by sho

			
		

	



                

              
                



	

		

			

				
					
SQL04：集合运算

				
			

			

				

					weixin_53026537的博客
				

				

					03-23
					
					561
					
				

			

		

		

			
				
练习题

4.1

select *
from product
where sale_price > 500
union
select *
from product2
where sale_price > 500;
4.2

select *
from (select * from product
   union
   select * from product2) as p
where product_id not in
   ...

			
		

	



                


  
              

                


	

		

			

				
					
集合——离散数学part 1

				
			

			

				

					yun_gao_的博客
				

				

					06-11
					
					8311
					
				

			

		

		

			
				
集合
集合基本概念
一.集合的表示方法
二.集合的关系：相等，包含
三.特殊结合：全集，补集，幂集
☆幂集P(A)：集合A的所有子集作为元素构成的集合称为集合A的幂集
☆基：有限集A含有的元素个数
|A|=n,则|P(A)|=2^n
四.集合的运算
1.交和，
交换律，结合律，分配律，德摩根律
☆2.差，对称差
①差：A-B={x|x∈A∧x∉B}
A−B=AB‾A-B=A\overline{B}A−B=AB
②对称差：A⊕B=B⊕A=（A-B）∪（B-A）
A⊕B=A∪B-A∩B
☆五.证明题
在证明时，

			
		

	





	

		

			

				
					
【数据库基础】数据库的数据查询

				
			

			

				

					m0_64740068的博客
				

				

					10-26
					
					6941
					
				

			

		

		

			
				
1.  查询全部商品信息。2．查询类别为“cn002”的所有商品信息。3. 查询类别为“cn001”和“cn002”对应的所有商品信息。4．查询类别为“cn005”且进价大于20的商品信息。5．查询库存数量小于10的商品号，供应商号，商品名和数量。6．查询有购买记录学生的学号。7．查询计算机专业“IT”学生的学号，姓名和年龄8．查询年龄在22到24岁学生的学号，姓名，学院，专业和年龄9． 查询姓张的同学的学号，姓名，学院。10．查询库存数量在20以上的商品信息，并按数量的降序排列。

			
		

	



		

			
		



	

		

			

				
					
产品——仓库表查询

				
			

			

				

					weixin_33755649的博客
				

				

					11-03
					
					1037
					
				

			

		

		

			
				
数据库说明：本次查询采用的是MYSQL
一、建表
1.1 需建立的表及字段
员工表E（employeeID,name,department）:员工编号，姓名，部门；
产品表P（prodectID,name,model,size,color）：产品编号，产品名称，型号，尺寸，颜色；
仓库表W（warehouseID,name,address,employeeID）:仓库编号，仓库名称，地址...

			
		

	





	

		

			

				
					
3-2 集合的运算

					
最新发布

				
			

			

				

					tang7mj的博客
				

				

					03-02
					
					1490
					
				

			

		

		

			
				
设有两个任意集合A和B，我们可以通过找出集合A和B的所有共同元素，组成一个新的集合S。这个由共同元素构成的集合S，称为A和B的交集，用符号“A∩B”表示。数学表达式为：S=A∩B={x|(x∈A)∧(x∈B)}。在数学中，定义是理解任何概念的起点。定义提供了一个明确的框架，说明了对象的基本性质和它们是如何相互关联的。在集合论中，了解集合的基本定义是至关重要的。一旦掌握了基本定义，下一步就是使用这些定义来证明新的性质或定理。这些证明通常通过逻辑推理进行，可能包括直接证明、反证法、数学归纳法等技巧。

			
		

	





	

		

			

				
					
MySQL：Task04-集合运算

				
			

			

				

					weixin_41360531的博客
				

				

					03-23
					
					866
					
				

			

		

		

			
				
主要针对MySQL表的加减法及内、外连接进行的学习总结梳理。

			
		

	





	

		

			

				
					
Task04：集合运算 - 表的加减法和 join 等.pdf

				
			

			

				

					01-13
				

			

		

		

			
				
SQL集合运算 - 表的加减法和 Join 等  SQL 集合运算是指对数据库中的数据进行操作，以获得所需的结果。这种操作可以分为两大类：集合运算和 Join 运算。  集合运算是指对数据库中的表进行加减法操作，以获得所需的...

			
		

	





	

		

			

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

				
			

			

				

					ALVIN23333的博客
				

				

					03-12
					
					815
					
				

			

		

		

			
				
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

4.1表的加减法

4.1.1 什么是集合运算

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

在标准 SQL 中, 分别对检索结果使用UNION,INTERSECT,EXCEPT来将检索结果进行并,交和差...

			
		

	





	

		

			

				
					
Task04-MySQL基础学习之集合运算

				
			

			

				

					lxt11122的博客
				

				

					12-22
					
					259
					
				

			

		

		

			
				
集合运算1 表的加减法1.1 集合运算1.2 表的加法-UNION1.2.1 UNION1.2.2 UNION ALL1.2.3 bag模型与set模型1.2.4 隐式类型转换1.3 INTERSECT的使用1.4 差集,补集与表的减法1.4.1 EXCEPT 与 NOT 谓词1.4.2 EXCEPT ALL 与bag 的差1.4.3 INTERSECT 与 AND 谓词1.5 对称差2 连结（JOIN）2.1 等值连接2.2 非等值连接2.2.1 非等值自左连接(SELF JOIN)2.2.2 交叉连结—

			
		

	





	

		

			

				
					
简单的入库查询程序－－无数据库

				
			

			

				

					04-13
				

			

		

		

			
				
这是我用eclipse做的一个小程序，，因为还不会连接数据库，里面只用了文档的建立来实现数据的存储与输出。

			
		

	





	

		

			

				
					
Task04：集合运算-表的加减法和join等（笔记4）

				
			

			

				

					wxiaoyiwenrong的博客
				

				

					07-04
					
					183
					
				

			

		

		

			
				
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

			
		

	





	

		

			

				
					
SQL集合与链接草稿

				
			

			

				

					weixin_41814086的博客
				

				

					12-22
					
					253
					
				

			

		

		

			
				
一、集合

1、UNION 存在的价值；

2、通过上述 bag 与 set 定义之间的差别我们就发现, 使用 bag 模型来描述数据库中的表在很多时候更加合适：用UNION 实现并、用 IN 实现交、用NOT IN实现差（补集）；

3、用视图实现：





二、连接

1、

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

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

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

但是如果分组列和被聚合的.

			
		

	





	

		

			

				
					
数据库作业-sql语句查询

					
热门推荐

				
			

			

				

					m0_53967061的博客
				

				

					04-09
					
					2万+
					
				

			

		

		

			
				
--1. 查询全部商品信息。
select * from Goods;
--2．查询类别为“cn002”的所有商品信息。
select * from Category join Goods on 
Category.CategoryNO=Goods.CategoryNO
where Category.CategoryNO='cn002';
--3 查询类别为“cn001”和“cn002”对应的所有商品信息。
select * from Category join Goods on 
Category....

			
		

	





	

		

			

				
					
【task06】复杂一点的查询---谓词

				
			

			

				

					weixin_45746689的博客
				

				

					02-02
					
					618
					
				

			

		

		

			
				
目录什么是谓词LIKE谓词 -- 用于字符串的部分一致查询BETWEEN谓词 -- 用于范围查询IS NULL、 IS NOT NULL -- 用于判断是否为NULLIN谓词 -- OR的简便用法使用子查询作为IN谓词的参数EXIST 谓词
什么是谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

LIKE谓词 – 用于字符串的部分一致查询
当需要进行字符串的部分

			
		

	





	

		

			

				
					
SQL数据库案例1——创建商店数据库

				
			

			

				

					weixin_64841524的博客
				

				

					01-10
					
					5792
					
				

			

		

		

			
				
创建商店数据库Commodities_Management。库名：Commodities_Management。创建商品表、供应商表、进货表、销售表。

			
		

	





	

		

			

				
					
实现集合之间的交 并 补 差 对称差

				
			

			

				

					weixin_48180484的博客
				

				

					04-01
					
					4969
					
				

			

		

		

			
				
通过类进行实现集合之间的关系题目相关数学知识功能实现学生类学生类实现学生集合类学生集合类实现主函数总结
题目
通过使用c++的类 ，容器等相关知识来实现集合间的：交 并 补 差 对称差这五种关系
这里不直接set中有关集合关系的函数
相关数学知识
并集：设A与B是任意两个集合，由至少属于集合A与集合B之一的一切元素构成的集合成为A与B的并集，记作A∪B（或B∪A），读作“A并B”（或“B并A”），即A∪B={x|x∈A,或x∈B}。
交集：对于给定的两个集合A 和 集合B 的交集是指含有所有既属于 A 又属

			
		

	





	

		

			

				
					
离散数学-集合的简单运算【并、交、差、补、环合（对称差）】

				
			

			

				

					Snikai1的博客
				

				

					08-14
					
					1万+
					
				

			

		

		

			
				
关于集合的并交差
1.集合的常用运算：
并集：X∪Y={x | x∈X或x∈Y}。
交集：X∩Y={x | x∈X且x∈Y}。
差集：X-Y={x | x∈X且x∉Y}。
如果X∩Y=∅，那么集合X与Y不相交。如果集合X和Y是集族S中的不同集合且X和Y不相交，那么集族S两两不相交。

#include <stdio.h>  
#define N 100  
main()
{  
    int a[N],b[N],c[N],d[N],e[N],h[N];  
    int aa[N],bb[.

			
		

	





	

		

			

				
					
union和group by使用日记

				
			

			

				

					Lanyangyangb的专栏
				

				

					06-05
					
					9546
					
				

			

		

		

			
				
1、union和union all的用法 


union all是将连接的两个查询结果表连接起来；

union是将连接的两个查询结果表连接起来并做去重处理；

注意：如果有大量的查询结果表需要连接起来，可以先用union all，这样除去了去重的时间效率会更高，外部再使用distinct来去重

SELECT DISTINCT name from (

SELECT

			
		

	





	

		

			

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

				
			

			

				

					
				

			

		

		

			
				
在《Task04：集合运算 - 表的加减法和 join 等.pdf》中，介绍了集合运算、表的加减法和join等相关内容。  4.1 集合运算 4.1.1 并集 并集操作是将两个或多个集合中的所有元素合并成一个集合。在MySQL中，可以使用...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值