7 SQL 集合运算

 

7 集合运算

7-1 表的加减法

 

本章将会和大家一起学习“集合运算”操作。在数学领域，“集合”表示“（各种各样的）事物的总和”；在数据库领域，表示“记录的集合”。具体来说，表、视图和查询的执行结果都是“记录的集合”。

所谓集合运算，就是对满足同一规则的记录进行的加减等“四则运算”。通过集合运算，可以得到两张表中记录的集合，或者是公共记录的集合，又或者是其中某张表记录的集合。像这样用来进行集合运算的运算符称为“集合运算符”。

 

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

 

表的加法-union

create table `shohin2` (

  `shohin_id` char(4) not null,

  `shohin_mei` varchar(100) not null,

  `shohin_bunrui` varchar(32) not null,

  `hanbai_tanka` int(11) default null,

  `shiire_tanka` int(11) default null,

  `torokubi` date default null,

  primary key (`shohin_id`)

) engine=innodb default charset=utf8;

 

 

start transaction;

insert into shohin2 values ('0001', 't恤衫', '衣服', 1000, 500, '2009-09-20');

insert into shohin2 values ('0002', '打孔器', '办公用品', 500, 300, '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');

commit;

 

 

select shohin_id, shohin_mei from shohin union select shohin_id, shohin_mei from shohin2;

 

 

 

-- 错误例子

select shohin_id, shohin_mei from shohin union select shohin_id, shohin_mei, hanbai_tanka from shohin2;

select shohin_id, hanbai_tanka from shohin union select shohin_id, torokubi from shohin2;

 

 

 

select shohin_id, shohin_mei from shohin where shohin_bunrui = '厨房用具' union

   select shohin_id, shohin_mei from shohin2 where shohin_bunrui = '厨房用具' order by shohin_id;

 

select shohin_id, shohin_mei from shohin union all select shohin_id, shohin_mei from shohin2;

 

 

法则7-1

集合运算符会除去重复的记录。

 

 

只需要在union后面添加all关键字就可以保留重复行。

 

包含重复行的集合运算-all选项

select shohin_id, shohin_mei，from shohin union all select shohin_id,shohin_mei from shohin2；

 

 

 

 

 

法则7-2

在集合运算中使用all选项，可以保留重复行。

 

 

 

与使用and可以选取一张表中满足多个条件的公共部分不同，intersect应用于两张表，选取出它们当中的公共记录。

希望保留重复行时同样需要使用interesect all。

 

选取表中公共部分-intersect

-- oracle sql server     db2    postgresql     目前mysql不支持

select shohin_id, shohin_mei from shohin intersect select shohin_id, shohin_mei from shohin2;

 

 

 

 

 

 

集合运算符：减法运算的except（差集）。

 

except有一点与union和intersect不同，需要注意一下。那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。

 

记录的减法-except

-- sql server    db2     postgresql      目前mysql还不支持，oracle有特定写法

select shohin_id, shohin_mei from shohin except select shohin_id, shohin_mei from shohin2;

 

-- oracle

select shohin_id, shohin_mei from shohin minus select shohin_id, shohin_mei from shohin2;

 

 

-- sql server    db2     postgresql      目前mysql还不支持，oracle有特定写法   表的位置不同得到的结果也不同

select shohin_id, shohin_mei from shohin2 except select shohin_id, shohin_mei from shohin;

 

 

 

集合运算的注意事项：

第一， 作为运算对象的记录的列数必须相同；

第二， 作为运算对象的记录中列的类型必须一致；

第三， 可以使用任何select语句，但order by子句只能在最后使用一次。

 

 

 

 

 

7-2 联结（以列为单位对表进行联结）

 

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

本节将要学习的联结（join）运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算。

 

 

 

所谓联结运算，一言以弊之，就是“以a中的列作为桥梁，将b中满足同样条件的列汇集到同一结果之中”。

 

使用关键字inner join就可以将两张表联结在一起了。我们可以在on之后指定两张表联结所使用的列（联结键）。也就是说on就是专门用来指定联结条件的，它能起到与where相同的作用。需要指定多个键时，同样可以使用and、or。on子句在进行内联结时是必不可少的。并且on必须书写在from和where之间。

 

内联结-inner join

-- sql server    db2   postgresql    mysql    不区分from 后面表的顺序

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin as ts

   inner join shohin as s on ts.shohin_id = s.shohin_id;

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from shohin as s

   inner join tenposhohin as ts on ts.shohin_id = s.shohin_id;

 

-- oracle

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin ts

   inner join shohin s on ts.shohin_id = s.shohin_id;

 

 

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin as ts

   inner join shohin as s on ts.shohin_id = s.shohin_id where ts.tenpo_id = '000a';

 

-- oracle

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin ts

   inner join shohin s on ts.shohin_id = s.shohin_id where ts.tenpo_id = '000a';

 

 

法则7-3

进行联结时需要在from子句中使用多张表。

 

 

法则7-4

进行内联结时必须使用on子句，并且要书写在from和where之间。

 

 

法则7-5

使用联结时select子句中的列需要按照<表的别名>.<列名>的格式进行书写。

 

 

 

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是left和right。通常，使用left的情况会多一些，但并没有非使用这个不可的理由，使用right也没有问题。

 

外联结-outer join

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin as ts

   right outer join shohin as s on ts.shohin_id = s.shohin_id;

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin as ts

   left outer join shohin as s on ts.shohin_id = s.shohin_id;

 

-- oracle

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin ts

   right outer join shohin s on ts.shohin_id = s.shohin_id;

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka from tenposhohin ts

   left outer join shohin s on ts.shohin_id = s.shohin_id;

 

 

法则7-6

外联结中使用left、right来指定主表。使用二者所得到的结果完全相同。

 

 

3张表以上的联结

-- 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 ：插入语句

start transaction;

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

commit;

 

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei, s.hanbai_tanka, zs.zaiko_suryo

   from tenposhohin as ts inner join shohin as s on ts.shohin_id = s.shohin_id

      inner join zaikoshohin as zs on ts.shohin_id = zs.shohin_id where zs.souko_id = 's001';

 

-- sql server    db2   postgresql    mysql

select ts.tenpo_id, ts.ten_mei, ts.shohin_id, s.shohin_mei from tenposhohin as ts cross join shohin as s;

 

 

法则7-7

那些过时和特定的联结书写方式，虽然我们自己不会使用，但还是希望大家能够读懂。