首页 > 代码库 > [SQL] SQL 基础知识梳理(七)- 集合运算

[SQL] SQL 基础知识梳理(七)- 集合运算

SQL 基础知识梳理(七)- 集合运算

目录

  • 表的加减法
  • 联结(以列为单位)

 

一、表的加减法

  1.集合:记录的集合(表、视图和查询的执行结果)。

 

  2.UNION(并集):表的加法

技术分享
-- DDL:创建表CREATE TABLE Shohin2(shohin_id     CHAR(4)      NOT NULL, shohin_mei    VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32)  NOT NULL, hanbai_tanka  INTEGER      , shiire_tanka  INTEGER      , torokubi      DATE         , PRIMARY KEY (shohin_id));-- DML:插入数据INSERT INTO Shohin2 VALUES (0001, T恤 ,衣服, 1000, 500, 2009-09-20);INSERT INTO Shohin2 VALUES (0002, 打孔器, 办公用品, 500, 320, 2009-09-11);INSERT INTO Shohin2 VALUES (0003, 运动T恤, 衣服, 4000, 2800, NULL);INSERT INTO Shohin2 VALUES (0009, 手套, 衣服, 800, 500, NULL);INSERT INTO Shohin2 VALUES (0010, 水壶, 厨房用具, 2000, 1700, 2009-09-20);CREATE TABLE Shohin(shohin_id     CHAR(4)      NOT NULL, shohin_mei    VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32)  NOT NULL, hanbai_tanka  INTEGER , shiire_tanka  INTEGER , torokubi      DATE , PRIMARY KEY (shohin_id));INSERT INTO Shohin VALUES (0001, T恤 ,衣服, 1000, 500, 2009-09-20);INSERT INTO Shohin VALUES (0002, 打孔器, 办公用品, 500, 320, 2009-09-11);INSERT INTO Shohin VALUES (0003, 运动T恤, 衣服, 4000, 2800, NULL);INSERT INTO Shohin VALUES (0004, 菜刀, 厨房用具, 3000, 2800, 2009-09-20);INSERT INTO Shohin VALUES (0005, 高压锅, 厨房用具, 6800, 5000, 2009-01-15);INSERT INTO Shohin VALUES (0006, 叉子, 厨房用具, 500, NULL, 2009-09-20);INSERT INTO Shohin VALUES (0007, 擦菜板, 厨房用具, 880, 790, 2008-04-28);INSERT INTO Shohin VALUES (0008, 圆珠笔, 办公用品, 100, NULL, 2009-11-11);
初始化测试数据

技术分享

 图

  看图,会移除重复的记录。

技术分享

 

  3.注意事项:

  (1)作为运算对象的记录的列数必须相同

技术分享

图:列数不匹配

  (2)作为运算对象的记录中列的类型必须一致

技术分享

图:类型不一致

  【备注】可以使用类型转换函数 CAST

 

  4.ALL 选项 - 包含重复行的集合运算

技术分享

图:保留重复行

 

   5.INTERSECT(交集) - 选取表中公共的部分

技术分享

   看图:

技术分享

 

  6.EXCEPT(差集) - 记录的减法

技术分享

  被减数和减数的位置调换,结果是不同的:

技术分享

 

二、联结(以列为单位)

  1.上一节集合运算的特征是以“行方向”为单位操作,在进行集合运算时会导致记录行数的增减,不过队列不会影响,因为它们的列数要一致。

  2.联结(JOIN)运算:“添加列”的运算,从其它表的列添加过来。

  3.INNER JOIN - 内联结

  联结运算:以 A 表中的列作为桥梁,将 B 表中满足同样条件的列汇集到同一结果中。

技术分享

  要点:

    ①FROM 子句:FROM dbo.TenpoShohin AS TS INNER JOIN dbo.Shohin AS S 

    这里使用 INNER JOIN 将两张表关联起来,TS 和 S 分别是两张表的别名,可以增强表的可读性。

    ②ON 子句:ON S.shohin_id = TS.shohin_id

    ON 后面记载的是联结条件,联结两张表所使用的的列,可以称为联结键。

    这是一个比较直观的例子,两个小镇,中间隔了一条河,通过 ON 子句作为他们两者之间的桥梁。

技术分享

    ③SELECT 子句:SELECT TS.tenpo_id ,TS.tenpo_mei ,S.shohin_mei ,S.hanbai_tanka

    请采用“<表的别名>.<列名>”格式来显示,这样就可以清楚的知道该列来自哪张表。

 

  4.OUTER JOIN - 外联结

  技术分享

  (1)要点:

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

    与之前的内联表相比,15 行比之前 13行多了 2 行。因为多出的 2 条记录在 TenpoShohin 表并不存在。内联结只能选取出同时存在于两张表中的数据,而外联结,只要数据存在某一张表中,就能够读取出来。也就是说,希望生成固定行数的记录,就需要使用外联结。

    ②主表的选择

    最终的结果只会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT,使用 LEFT 时左侧的表为主表,使用 RIGHT 时右侧的表为主表(如上图的 Shohin 表)。

  【备注】外联结使用 LEFT 和 RIGHT 来指定主表。

 

  5.多张表的联结

技术分享
-- DDL:创建表CREATE TABLE ZaikoShohin( souko_id        CHAR(4)      NOT NULL,  shohin_id     CHAR(4)      NOT NULL,  zaiko_suryo    INTEGER      NOT NULL,  PRIMARY KEY (souko_id, shohin_id));-- DML:插入数据INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0001,    0);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0002,    120);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0003,    200);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0004,    3);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0005,    0);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0006,    99);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0007,    999);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S001,    0008,    200);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0001,    10);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0002,    25);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0003,    34);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0004,    19);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0005,    99);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0006,    0);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0007,    0);INSERT INTO ZaikoShohin (souko_id, shohin_id, zaiko_suryo) VALUES (S002,    0008,    18);
初始化测试数据

技术分享

  3 表联结就是多 1 行 JOIN 子句而已,多表联结就多几行 JOIN 字句呗。

 

  6.CROSS JOIN(笛卡儿积)- 交叉联结

  这种联结在实际业务中很少使用。

技术分享

  TenpoShohin 表 13 条记录, Shohin 表 8 条记录,13*8=104 条记录。

 

备注

  这里采用 MS SQL Server 进行验证,不保证所有的 DBMS 执行结果正确。

 

《SQL 基础知识梳理》系列

  《SQL 基础知识梳理(一) - 数据库与 SQL》

  《SQL 基础知识梳理(二) - 查询基础》

  《SQL 基础知识梳理(三) - 聚合和排序》

  《SQL 基础知识梳理(四) - 数据更新》

  《SQL 基础知识梳理(五) - 复杂查询》

  《SQL 基础知识梳理(六)- 函数、谓词、CASE 表达式》

  《SQL 基础知识梳理(七)- 集合运算》

 

 


【博主】反骨仔

【原文】http://www.cnblogs.com/liqingwen/p/6575668.html 

【参考】《SQL ゼロからはじめるデータベース操作》

 

[SQL] SQL 基础知识梳理(七)- 集合运算