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 |
那些过时和特定的联结书写方式,虽然我们自己不会使用,但还是希望大家能够读懂。 |