SQL基础教程学习第九站：集合运算

仅用于记录学习，欢迎批评指正，共同交流，共同进步，大神勿喷

系列文章

SQL基础教程学习第一站：PostgreSQL下载安装以及如何创建并登录数据库；
SQL基础教程学习第二站：数据库基本知识；
SQL基础教程学习第三站：创建表；
SQL基础教程学习第四站：查询基础；
SQL基础教程学习第五站：聚合和排序；
SQL基础教程学习第六站：数据更新；
SQL基础教程学习第七站：复杂查询；
SQL基础教程学习第八站：函数、谓词、CASE表达式；
SQL基础教程学习第九站：集合运算；
SQL基础教程学习第十站：SQL高级处理；
SQL基础教程示例代码

SQL基础教程第七章

7-1 集合运算：表的加减法（记录行数的增减）

–表的加法-UNION(并集,去除重复记录)
DELETE FROM Product;

BEGIN TRANSACTION;
INSERT INTO Product VALUES (‘0001’ , ‘T恤衫’,‘衣服’,1000,500,‘2009-09-20’);
INSERT INTO Product VALUES (‘0002’ , ‘打孔器’,‘办公用品’,500,320,‘2009-09-11’);
INSERT INTO Product VALUES (‘0003’ , ‘运动T恤’,‘衣服’,4000,2800,NULL);
INSERT INTO Product VALUES (‘0004’ , ‘菜刀’,‘厨房用具’,3000,2800,‘2009-09-20’);
INSERT INTO Product VALUES (‘0005’ , ‘高压锅’,‘厨房用具’,6800,5000,‘2009-01-15’);
INSERT INTO Product VALUES (‘0006’ , ‘叉子’,‘厨房用具’,500,NULL,‘2009-09-20’);
INSERT INTO Product VALUES (‘0007’ , ‘擦菜板’,‘厨房用具’,880,790,‘2008-04-28’);
INSERT INTO Product VALUES (‘0008’ , ‘圆珠笔’,‘办公用品’,100,NULL,‘2009-11-11’);
COMMIT;

SELECT *
FROM Product ;

DROP TABLE Product2;

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

BEGIN TRANSACTION;
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’);
COMMIT;

SELECT *
FROM Product2 ;

SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2

– 集合运算的注意事项:
– 1.作为运算对象的记录的列数必须相同
– 错误示例:
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name,sale_price
FROM Product2
– 2.作为运算对象的记录的列的数据类型必须一致
– 错误示例：
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, sale_price
FROM Product2
– 突发奇想：列不同，类型同，能运行得出结果，但运行出来的结果是错误的，没有实际效用
SELECT product_id, purchase_price
FROM Product
UNION
SELECT product_id, sale_price
FROM Product2

– 3.可以使用任何的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;

–包含重复行的集合运算-ALL选项，即在UNION后加ALL
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2

–选取表中的公共部分-INTERSECT(交集)
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

–记录的减法-EXCEPT(差集)
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id；

–将被减数和减数调换位置
SELECT product_id, product_name
FROM Product2
EXCEPT
SELECT product_id, product_name
FROM Product
ORDER BY product_id;

SELECT *
FROM ShopProduct

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

–内联结，使用Product表和ShopProduct表
SELECT *
FROM Product
SELECT *
FROM ShopProduct

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子句可以结合使用,GROUP　BY, ORDER BY, HAVING 等工具都可以正常使用
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’;

–外联结，使用Product表和ShopProduct表
SELECT SP.shop_id , SP.shop_name , P.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
ORDER BY SP.shop_id;

–要点：
– 1.会选取出单张表（主表）中的全部信息
– 2.指定主表的关键字是LEFT和RIGHT,写在FROM子句左侧表和右侧表
SELECT SP.shop_id , SP.shop_name , P.product_id, P.product_name,P.sale_price
FROM ShopProduct AS SP LEFT OUTER JOIN Product AS P
ON SP.product_id = P.product_id
ORDER BY SP.shop_id; --和内联结一样

–3张以上的表的联结
DROP TABLE InventoryProduct;

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

BEGIN TRANSACTION;
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0001’,0);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0002’,120);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0003’,200);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0004’,3);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0005’,0);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0006’,99);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0007’,999);
INSERT INTO InventoryProduct VALUES (‘P001’ , ‘0008’,200);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0001’,10);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0002’,25);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0003’,34);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0004’,19);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0005’,99);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0006’,0);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0007’,0);
INSERT INTO InventoryProduct VALUES (‘P002’ , ‘0008’,18);
COMMIT;

–对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’;

–交叉联结-CROSS JOIN(笛卡尔积)–直接粘贴过来
– (注：一般不会使用，介绍原因：交叉联结是所有联结运算的基础)
SELECT SP.shop_id , SP.shop_name , SP.product_id, P.product_name,P.sale_price
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’;
–与之前所说的标准语法相同，会碰到过时语法，希望有所了解
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’;