表的加减法

什么是集合运算

  • 集合在数学领域表示“(各种各样的)事物的总和”,在数据库领域表示记录的集合
  • 表、视图和查询的执行结果都是记录的集合
  • 集合运算,就是对满足同一规则的记录进行的加减等四则运算
  • 进行集合运算的运算符称为集合运算符

表的加法

  • UNION(并集)
  • 集合运算符会除去重复的记录
  • -- 使用UNION对表进行加法运算 
    SELECT product_id, product_name
    FROM Product
    UNION
    SELECT product_id, product_name
    FROM Product2;

集合运算的注意事项

  • ①作为运算对象的记录的列数必须相同 
  • ②作为运算对象的记录中列的类型必须一致
  • ③可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次 
  • -- 列数不一致时会发生错误 
    SELECT product_id, product_name
    FROM Product
    UNION
    SELECT product_id, product_name, sale_price
    FROM Product2;
    
    -- 数据类型不一致时会发生错误 
    SELECT product_id, sale_price
    FROM Product
    UNION
    SELECT product_id, regist_date
    FROM Product2;
    
    -- ORDER BY子句只在最后使用一次 
    SELECT product_id, product_name
    FROM Product
    WHERE product_type = '厨房用具'
    UNION
    SELECT product_id, product_name
    FROM Product2
    WHERE product_type = '厨房用具'
    ORDER BY product_id;

包含重复行的集合运算

  • ALL选项
  • 在集合运算符中使用ALL选项,可以保留重复行
  • -- 保留重复行 
    SELECT product_id, product_name
    FROM Product
    UNION ALL
    SELECT product_id, product_name
    FROM Product2;

选取表中公共部分

  • INTERSECT(交集)
  • INTERSECT 应用于两张表,选取出它们当中的公共记录
  • -- 使用INTERSECT选取出表中公共部分
    SELECT product_id, product_name
    FROM Product
    INTERSECT
    SELECT product_id, product_name
    FROM Product2
    ORDER BY product_id;

记录的减法

  • EXCEPT(差 集)
  • 减法运算中减数和被减数的位置不同,所得到的结果也不相同
  • -- 使用EXCEPT对记录进行减法运算
    SELECT product_id, product_name
    FROM Product
    EXCEPT
    SELECT product_id, product_name
    FROM Product2
    ORDER BY product_id;

联结(以列为单位对表进行联结)

什么是联结

  • 联结(JOIN)运算
  • 将其他表中的 列添加过来,进行“添加列”的运算
  • 实际上,期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表(3 张以上的表也没关系) 中选取数据了

内联结

  • INNER JOIN 

  • 所谓联结运算,以A中的列作为桥梁,将 B中满 足同样条件的列汇集到同一结果之中

  • 内联结要点

    • ①FROM子句 

      • FROM ShopProduct AS SP INNER JOIN Product AS P
      • 进行联结时需要在FROM子句中使用多张表
    • ②ON子句 

      • ON SP.product_id = P.product_id
      • 在 ON 之后指定两张表联结所使用的列(联结键)
      • 进行内联结时必须使用ON子句,并且要书写在FROM和WHERE之间
    • ③SELECT子句 

      • SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
      • 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写
  • 内联结和WHERE子句结合使用 

    • 联结运算将满足相同规则的表联结起来时,WHERE、 GROUP BY、HAVING、ORDER BY 等工具都可以正常使用
    • 将联结之后的结果想象为新创建出来的一张表,对这张表使用 WHERE 子句等工具
    • 这张“表”只在 SELECT 语句执行期间存在,SELECT 语 句执行之后就会消失。如果希望继续使用这张“表”,将它创建成视图
-- 将两张表进行内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id;

-- 内联结和WHERE子句结合使用
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';

外联结

  • OUTER JOIN 

  • 外联结要点

    • ①选取出单张表中全部的信息 

    • ②每张表都是主表吗

      • 把哪张表作为主表,最终的结果中会包含主表内所有的数据

      • 指定主表的关键字是 LEFT 和 RIGHT

        • 使用LEFT 时FROM 子句中写在左侧的表是主表,使用RIGHT 时右侧的表是主表
  • 3张以上的表的联结 

  • 交叉联结

    • CROSS JOIN(笛卡 儿积)

      • 对满足相同规则的表进行交叉联结的集合运算符
    • 进行交叉联结时无法使用内联结和外联结中所使用的ON 子句, 这是因为交叉联结是对两张表中的全部记录进行交叉组合,因此结果中 的记录数通常是两张表中行数的乘积

  • 联结的特定语法和过时语法 

    • 对于联结的过时语法和特定语法,虽然不建议使用,但还是希望能够读懂。
-- 将两张表进行外联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,  P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;

-- 对3张表进行内联结
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';

-- 将两张表进行交叉联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name  FROM ShopProduct AS SP CROSS JOIN Product AS P;

-- 使用过时语法的内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP, Product P
WHERE SP.product_id = P.product_id
AND SP.shop_id = '000A';
12-14 04:31