集合运算

  • ​​前言​​
  • ​​思维导图​​
  • ​​表的加减法​​
  • ​​表的加法——UNION（并集）​​
  • ​​代码示例7.1 创建表Product2​​
  • ​​代码示例7.2 将数据插入到表Product2中（MYSQL）​​
  • ​​代码示例7.3 使用UNION对表进行加法运算​​
  • ​​包含重复行的集合运算——ALL选项​​
  • ​​代码示例7.5 保留重复行​​
  • ​​执行结果​​
  • ​​选取表中公共部分——INTERSECT(交集）​​
  • ​​代码示例 7.6 使用INTERSECT选取出表中公共部分​​
  • ​​执行结果​​
  • ​​记录的减法——EXCEPT（差集）​​
  • ​​代码示例7.7 使用EXCEPT对记录进行减法运算​​
  • ​​执行结果​​
  • ​​代码示例7.8 被减数和减数位置不同，得到的结果也不同​​
  • ​​执行结果​​
  • ​​联结（以列为单位对表进行联结）​​
  • ​​什么是联结​​
  • ​​内连接——INNER JOIN​​
  • ​​代码示例7.9 将两张表进行内联结​​
  • ​​执行结果​​
  • ​​内联结要点①——FROM子句​​
  • ​​内联结要点②——ON子句​​
  • ​​内联结要点③——SELECT子句​​
  • ​​代码示例7.10 内联结和WHERE子句结合使用​​
  • ​​执行结果​​
  • ​​外联结——OUTER JOIN​​
  • ​​代码清单7.11 将两张表进行外联结​​
  • ​​执行结果​​
  • ​​外联结要点①——选取出单张表中全部信息​​
  • ​​外联结要点②——每张表都是主表吗？​​
  • ​​代码示例7.12 使用左外联结​​

  • ​​3张以上的表的联结​​
  • ​​代码示例 7.14 对3张表进行内联结​​
  • ​​执行结果​​
  • ​​交叉联结​​
  • ​​代码示例7.15 将两张表进行交叉联结​​
  • ​​执行结果​​
  • ​​如果博主的文章对您有所帮助，可以<font color=orange>评论、<font color=green>点赞、<font color=bule>收藏，<font color=black>支持一下博主!!!​​

前言

系统学习SQL的笔记，用于记录学习过程。

思维导图

SQL基础——集合运算_mysql

表的加减法

集合运算就是对满足同一规则的记录进行的加减等四则运算。

表的加法——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;

执行结果为两张表的全部商品

SQL基础——集合运算_代码示例_02


注意： 集合运算符会除去重复的记录

集合运算的注意事项
注意事项①——作为运算对象的记录的列数必须相同
注意事项②——作为运算对象的记录中列的类型必须相同
注意事项③——可以使用任何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;
执行结果

SQL基础——集合运算_代码示例_03



法则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;
执行结果

SQL基础——集合运算_sql_04

记录的减法——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;
执行结果

SQL基础——集合运算_代码示例_05

代码示例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;
执行结果

SQL基础——集合运算_sql_06

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

什么是联结

联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。

SQL基础——集合运算_代码示例_07

内连接——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：

SQL基础——集合运算_sql_08


ShopProduct：

SQL基础——集合运算_sql_09

代码示例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;
执行结果

SQL基础——集合运算_mysql_10

内联结要点①——FROM子句

进行联结是需要在FROM子句中使用多张表

内联结要点②——ON子句

ON后面为联结条件，进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。

内联结要点③——SELECT子句

使用联结时SELECT子句中的列需要按照<表的别名>.<列名>的格式进行书写，避免书写格式出错。
内连接与WHERE子句结合使用

代码示例7.10 内联结和WHERE子句结合使用

--指定 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 
WHERE
    SP.shop_id = '000A';
执行结果

SQL基础——集合运算_sql_11

外联结——OUTER JOIN

代码清单7.11 将两张表进行外联结

--指定 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
    RIGHT OUTER JOIN Product AS P  ON SP.product_id = P.product_id;
执行结果

SQL基础——集合运算_mysql_12

外联结要点①——选取出单张表中全部信息

可以发现外联结相对于内联结多了两条记录。多出的两条记录是高压锅和圆珠笔，这两条记录在ShopProduct表中并不存在，也就是说，这2种商品在任何商店中都没有销售。由于内联结只能选取同时存在于两张表中的数据，因此只在Product表中存在的2种商品并没有出现在结果之中。

而对于外联结来说，只要数据存在于某一张表中，就能够读取出来。
注意：在实际的业务汇总，如果想生成固定行数的单据时，就需要使用外联结，如果使用内联结的话，根据SELECT语句执行是商品库存状况的不同，结果的行数也会发生改变。

外联结要点②——每张表都是主表吗？

外联结还有一点非常重要，那就是要把那张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是LEFT和RIGHT。
使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。

代码示例7.12 使用左外联结

--指定 SQL Server  DB2  PostgreSQL  MySQL
SELECT
    SP.shop_id,
    SP.shop_name,
    SP.product_id,
    P.product_name,
    P.sale_price 
FROM
    Product AS P
    LEFT OUTER JOIN ShopProduct AS SP  ON SP.product_id = P.product_id;

SQL基础——集合运算_代码示例_13


注意： 外联结中使用LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。

3张以上的表的联结

通常联结只涉及到两张表，但是有时候也会出现联结3张以上的表的情况。原则上联结表的数量并没有限制。
先创建一张用于管理库存商品的表。

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

插入数据

--指定mysql
START TRANSACTION;
    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 );
COMMIT;

代码示例 7.14 对3张表进行内联结

--指定SQL Server  DB2  PostgreSQL  MySQL
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';
执行结果

SQL基础——集合运算_数据库_14

交叉联结

其实这种联结在实际业务中并不会使用，交叉联结本身非常简单，但是其结果有点麻烦。
将Product表和ShopProduct表进行交叉联结。

代码示例7.15 将两张表进行交叉联结

--指定 SQL Server  DB2  PostgreSQL  MySQL
SELECT
    SP.shop_id,
    SP.shop_name,
    SP.product_id,
    P.product_name 
FROM
    ShopProduct AS SP
    CROSS JOIN Product AS P;
执行结果

ShopProduct表中有11条记录，Product表存在8条记录，所以结果中包含了11*8=88条记录。
交叉查询对满足相同规则的表进行交叉联结的集合运算符是CROSS JOIN（笛卡尔积）。进行交叉联结时无法使用内联结和外联结中所使用的ON子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。

内联结时交叉联结的一部分，“内”可以理解为“包含在交叉联结结果中的部分”。相反，外联结“外”可以理解为“交叉联结结果之外的部分”

注意：交叉联结没有应用到实际业务之中的原因有两个。一其结果没有实用价值，二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。

如果博主的文章对您有所帮助，可以评论、点赞、收藏，支持一下博主!!!