集合运算
前言
系统学习SQL的笔记，用于记录学习过程。
思维导图
表的加减法
集合运算就是对满足同一规则的记录进行的加减等四则运算。
表的加法——UNION（并集）
在学习具体的使用方法之前，先添加一张表
代码示例7.1 创建表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 ));
为表Product2添加记录，商品编号(product_id)为0001-0003的商品与之前Product表中的商品相同，而编号为0009和手套和0010的水壶是Product表中没有的商品。
代码示例7.2 将数据插入到表Product2中（MYSQL）
--指定mysql START 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;
进行两张表的加法运算，Product表+Product2表的加法运算
代码示例7.3 使用UNION对表进行加法运算
SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name FROM Product2;
执行结果为两张表的全部商品
注意： 集合运算符会除去重复的记录
集合运算的注意事项
注意事项①——作为运算对象的记录的列数必须相同
注意事项②——作为运算对象的记录中列的类型必须相同
注意事项③——可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次
包含重复行的集合运算——ALL选项
在UNION的结果中保留重复行的语法。只需要在UNION后面添加ALL关键字就可以了。
代码示例7.5 保留重复行
SELECT product_id, product_name FROM Product UNION ALL SELECT product_id, product_name FROM Product2;
执行结果
法则7.2 在集合运算符中使用ALL选项，可以保留重复行
选取表中公共部分——INTERSECT(交集）
选取两个记录集合中公共部分。
目前仅支持：Oracle SQL Server DB2 PostgreSQL
代码示例 7.6 使用INTERSECT选取出表中公共部分
--目前仅支持：Oracle SQL Server DB2 PostgreSQL SELECT product_id, product_name FROM Product INTERSECT SELECT product_id, product_name FROM Product2ORDER BY product_id;
执行结果
记录的减法——EXCEPT（差集）
代码示例7.7 使用EXCEPT对记录进行减法运算
目前仅支持：SQL Server DB2 PostgreSQL
--目前仅支持：SQL Server DB2 PostgreSQL SELECT product_id, product_name FROM Product EXCEPT SELECT product_id, product_name FROM Product2 ORDER BY product_id;
执行结果
代码示例7.8 被减数和减数位置不同，得到的结果也不同
--SQL Server DB2 PostgreSQL -- 从Product2的记录中除去Product中的记录 SELECT product_id, product_nam e FROM Product2 EXCEPT SELECT product_id, product_nam e FROM Product ORDER BY product_id;
执行结果
联结（以列为单位对表进行联结）
什么是联结
联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。
内连接——INNER JOIN
使用product表和Product表表进行练习
相应的建表语句
CREATE TABLE ShopProduct ( shop_id CHAR ( 4 ) NOT NULL, shop_name VARCHAR ( 200 ) NOT NULL, product_id CHAR ( 4 ) NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY ( shop_id, product_id ));
插入的数据
--指定mysql START TRANSACTION; INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000A', '东京', '0001', 30 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000A', '东京', '0002', 50 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000A', '东京', '0003', 15 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000B', '名古屋', '0002', 30 ); INSERT INTO ShopProduct ( sho _id, shop_name, product_id, quantity ) VALUES ( '000B', '名古屋', '0003', 120 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000B', '名古屋', '0004', 20 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000B', '名古屋', '0006', 10 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000B', '名古屋', '0007', 40 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000C', '大阪', '0003', 20 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000C', '大阪', '0004', 50 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000C', '大阪', '0006', 90 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000C', '大阪', '0007', 70 ); INSERT INTO ShopProduct ( shop_id, shop_name, product_id, quantity ) VALUES ( '000D', '福冈', '0001', 100 ); COMMIT;
Product表
CREATE TABLE `Product` ( `product_id` char(4) CHARACTER SET utf8 NOT NULL, `product_name` varchar(100) CHARACTER SET utf8 NOT NULL, `product_type` varchar(32) CHARACTER SET utf8 NOT NULL, `sale_price` int(11) DEFAULT NULL, `purchase_price` int(11) DEFAULT NULL, `regist_date` date DEFAULT NULL, PRIMARY KEY (`product_id`) )
Product表与ShopProduct表的数据
Product：
ShopProduct：
代码示例7.9 将两张表进行内联结
--支持 SQL Server DB2 PostgreSQL MySQL 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;
执行结果
内联结要点①——FROM子句
进行联结是需要在FROM子句中使用多张表
内联结要点②——ON子句
ON后面为联结条件，进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
内联结要点③——SELECT子句
使用联结时SELECT子句中的列需要按照<表的别名>.<列名>的格式进行书写，避免书写格式出错。
内连接与WHERE子句结合使用
