一. 集合运算
前面说到,每个查询就是取了整张表的一个子集或者是衍生出的一个子集,总之可以把 select 操作看成是返回了一个集合,那么集合中的交并补就可以拿来运算,
1.加法运算
对应集合中的并集运算,使用的是 union 关键词
CREATE TABLE Product2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
下面将 product 表和 product2 进行合并
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
可以看出 union 运算是将得到的两张表按照上下顺序叠加在一起,但是也要注意 列名得取得一致。
值得注意的是,union运算会自动将重复的行去重。需要注意的事项:
- 作为运算对象的记录的列数必须相同,不能一个挑了两项,另一个表挑了三项
- 作为运算对象的记录中列的类型必须一致,也就是不能是一个数值型,一个日期型
- 可以使用任何SELECT语句,但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;
上面说到 union 运算会自动将重复的行去重,那么如果不想进行去重的话有没有办法呢?也是有的,但是需要使用 all 关键字。
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
可以发现0003 这一行并没有去重。
2.交运算
有了并运算,必然有交运算,intersect,和union的用法相同:
SELECT product_id, product_name
FROM Product
intersect
SELECT product_id, product_name
FROM Product2;
3.差运算
其实差运算可以有 并运算和 not 合并得到,但是为了方便,有对应的关键字:except
SELECT product_id, product_name
FROM Product
except
SELECT product_id, product_name
FROM Product2;
需要说明的是 except 运算会和两个集合的顺序相关,很明显 A-B 和B-A这俩就不是一个集合
SELECT product_id, product_name
FROM Product2
except
SELECT product_id, product_name
FROM Product;
二.表的连结
表的集合运算说的是将两个表上下叠起来,而表的连结则是把两张表左右连起来,可以发现左右连起来明显难度大得多
1.内连结
对于之前的两张表,product 和shop_product
这两张表的列名不同,但是我们在 子查询作为 in 参数的时候干过这件事,通过 关联子查询将两张表用 where联系起来了。
那我们可以通过两张表共同的列 product_id 把两张表联系起来。
select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity
from product as p inner join shopproduct as sp
on p.product_id =sp.product_id
需要的注意的有以下三点:
- from 子句中有两张表,这两张表通过 inner join 连在了一起,一般为了方便起见,我们会给每个表去一个别名。
- from 子句后面的 on 子句表示 的是两张表的连结条件,有点类似于关联子查询中的 where 条件。它能起到与where 相同的作用,如果需要指定多个连结条件,则需要使用 and 和 or 来进行连接。
- select 子句中,像 SP.shop_id 和 P.sale_price 这样使用“< 表的别名 >.< 列名 >”的形式来指定列,这是为了避免混淆。
inner join 是将两张表连在一起,两张表连在一起后仍然还是一张表,那也可以对这张表进行率和分组,比如和where的连用。
select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity
from product as p inner join shopproduct as sp
on p.product_id =sp.product_id
where SP.shop_id = '000A';
只需要注意这地方where的顺序是在from 后面就可以了,其实我们只要把 from inner join 这个整体看成一个整体,那么别的命令和与之前说的就没有区别。
当然,这张“表”只在 select 语句执行期间存在, select 语句执行之后就会消失。如果希望继续使用这张“表”,还是将它创建成视图吧。
2.外连结
所谓外连结,就是把内连结中的 inner join 换成 left outer join和right outer join 来看下面这个例子:
select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity
from product as p left outer join shopproduct as sp
on p.product_id =sp.product_id;
可以发现多出来的两行分别是第10行和第15行,这两行为什么在内连结中没有体现呢,其实也就是 sp这个表并没有0005和0008 这两个id 的信息,也就是 inner join 查询的是对于某个列名,两个表都有的那些行。而对于 left outer 和 right outer 他会显示出其中一张的所有行,这就是左右的区别,也就是这里面存在一个主表,left outer join 就是以左边的表为主表,right outer join 则是以右边的表为主表,主表的所有行都会显示,而对于主表中有,副表中没有的行,会将对副表的列赋值为 null ,而对应附表中有,主表中没有的行则不会显示,比如上面的代码如果改成右主表:
select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity
from product as p right outer join shopproduct as sp
on p.product_id =sp.product_id;
则还是13行数据。
对于多张表的连结,其实也是类似的道理,我们可以先创立一张表:
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));
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);
我们将三张表连在一起,注意其中的连结条件:
select p.product_id,p.product_name,p.product_type ,p.sale_price ,
p.purchase_price,p.regist_date ,sp.shop_id ,sp.shop_name ,sp.quantity ,ip.inventory_id ,ip.inventory_quantity
from product as p inner join shopproduct as sp on p.product_id =sp.product_id
inner join inventoryproduct as ip where ip.product_id =p.product_id ;
这里其实是这样的格式:
from table1
inner join table2 on condition1
inner join table3 on condition2
left outer join table4 on condition3
注意这时候其实也是按照从上到下的顺序依次进行内连结和外连结。
3.交叉连结
所谓的交叉连结,就是笛卡尔连结,很明显的数学定义,就是对于第一张表的每一行,和第二张表的每一行做连结,所以笛卡尔连结后后有 m*n行数据。
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
可以看到最后的数据很多,这里面并没有 on 的连结条件。交叉连结其实并没有什么实用价值。
总结
本小节讲了集合的运算,union,intersect,except 以及连结 inner join,left outer join ,right outer join ,cross join。