SQL基础之集合运算

Database SQL
DB浅入浅出
发布日期:   2019-02-08
更新日期:   2020-12-18
文章字数:   1.9k
阅读时长:   8 分
阅读次数:  

本文主要是阅读《SQL 基础教程》这本书总结而来，有需求的话可以阅读原著。

SQL基础之集合运算

1. 表的加减法

  • 集合运算就是对满足同一规则的记录进行的加减等四则运算
  • 并集（union），交集（intersect），差集（except）
  • 集合运算符可以去除重复行
  • 如果希望集合运算符保留重复行，就需要使用all选项
# 新建Shohin2与Shohin结构相同
mysql> create table Shohin2(
    -> shohin_id char(4) not null,
    -> shohin_mei varchar(100) not null,
    -> shohin_bunrui varchar(32) not null,
    -> hanbai_tanka int,
    -> shiire_tanka int,
    -> torokubi date,
    -> primary key (shohin_id));
Query OK, 0 rows affected (0.01 sec)

# 添加数据
mysql> select * from Shohin2;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T恤衫    | 衣服        |         1000 |          500 | 2008-09-20 |
| 0002      | 打孔器  | 办公用品  |          500 |          320 | 2009-09-11 |
| 0003      | 运动T恤 | 衣服        |         4000 |         2800 | NULL       |
| 0009      | 手套     | 衣服        |          800 |          500 | NULL       |
| 0010      | 水壶     | 厨房用具  |         2000 |         1700 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
5 rows in set (0.00 sec)

1.1 并集（union）

  • 并集就如同数学中的集合运算
  • 默认去除重复的记录
mysql> select shohin_id, shohin_mei
    -> from Shohin union
    -> select shohin_id, shohin_mei
    -> from Shohin2;
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0001      | T恤衫    |
| 0002      | 打孔器  |
| 0003      | 运动T恤 |
| 0004      | 菜刀     |
| 0005      | 高压锅  |
| 0006      | 叉子     |
| 0007      | 切菜板  |
| 0008      | 圆珠笔  |
| 0009      | 印章     |
| 0009      | 手套     |
| 0010      | 水壶     |
+-----------+------------+
11 rows in set (0.00 sec)

注意事项

  • 作为运算对象的记录的列数必须相同
  • 作为运算对象的记录中列的类型必须一致
  • 可以使用select语句，但是order by自己只能在最后使用一次
# 作为运算对象的记录的列数必须相同，否则报错
mysql> select shohin_id, shohin_mei
    -> from Shohin union
    -> select shohin_id, shohin_mei, hanbai_tanka
    -> from Shohin2;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

# 作为运算对象的记录中列的类型必须一致，否则报错
mysql> select shohin_id, shohin_mei
    -> from Shohin union
    -> select shohin_id, torokubi
    -> from Shohin2;

# 可以使用`select`语句，但是`order by`自己只能在最后使用一次
mysql> 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;
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0004      | 菜刀     |
| 0005      | 高压锅  |
| 0006      | 叉子     |
| 0007      | 切菜板  |
| 0010      | 水壶     |
+-----------+------------+
5 rows in set (0.00 sec)

包含重复行的集合运算选项——all

mysql> select shohin_id, shohin_mei
    -> from Shohin
    -> union all
    -> select shohin_id, shohin_mei
    -> from Shohin2;
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0001      | T恤衫    |
| 0002      | 打孔器  |
| 0003      | 运动T恤 |
| 0004      | 菜刀     |
| 0005      | 高压锅  |
| 0006      | 叉子     |
| 0007      | 切菜板  |
| 0008      | 圆珠笔  |
| 0009      | 印章     |
| 0001      | T恤衫    |
| 0002      | 打孔器  |
| 0003      | 运动T恤 |
| 0009      | 手套     |
| 0010      | 水壶     |
+-----------+------------+
14 rows in set (0.00 sec)

1.2 交集（intersect）

  • 交集就如同数学中的集合运算
  • intersect应用于两张表，选取出他们当中的公共记录
  • 其语法与union相同
  • MySQL没有提供交集，可以使用group by来完成
# Oracle/DB2/PostgreSQL
mysql> select shohin_id, shohin_mei
    -> from Shohin
    -> intersect
    -> select shohin_id, shohin_mei
    -> from Shohin
    -> order by shohin_id;

1.3 差集（except）

  • 差集就如同数学中的集合运算
  • 默认去除重复的记录
  • 其语法与union相同
  • MySQL没有提供差集，可以使用group by来完成
  • 需要注意的是，减法运算中的减数和被减数的位置不同，导致结果也不相同
# DB2/PostgreSQL
mysql> select shohin_id, shohin_mei
    -> from Shohin
    -> except
    -> select shohin_id, shohin_mei
    -> from Shohin
    -> order by shohin_id;

2. 联结——以列为单位对表进行联结

  • 联结（join）就是将其他表中的列添加过来，进行添加列的集合运算
  • union是将表中满足的相同规则的记录以行为单位进行联结，而联结则是以列为单位进行联结
  • 通俗的来说，以行为单位的是对于行数的增减，而列为单位的是对于列数的增减
  • 联结分为内联结和外联结

2.1 内联结（inner join）

  • 通过相同的列，将两个表联系在一起
# 将两张表进行内联结
mysql> select TS.tenpo_id, TS.tenpo_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;
+----------+-----------+-----------+------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka |
+----------+-----------+-----------+------------+--------------+
| 000A     | 东京    | 0001      | T恤衫    |         1000 |
| 000A     | 东京    | 0002      | 打孔器  |          500 |
| 000A     | 东京    | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0002      | 打孔器  |          500 |
| 000B     | 名古屋 | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0004      | 菜刀     |         3000 |
| 000B     | 名古屋 | 0006      | 叉子     |          500 |
| 000B     | 名古屋 | 0007      | 切菜板  |          880 |
| 000C     | 大阪    | 0003      | 运动T恤 |         4000 |
| 000C     | 大阪    | 0004      | 菜刀     |         3000 |
| 000C     | 大阪    | 0006      | 叉子     |          500 |
| 000C     | 大阪    | 0007      | 切菜板  |          880 |
| 000D     | 福冈    | 0001      | T恤衫    |         1000 |
+----------+-----------+-----------+------------+--------------+
13 rows in set (0.00 sec)

# 内联结和where子句结合使用
mysql> select TS.tenpo_id, TS.tenpo_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';
+----------+-----------+-----------+------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka |
+----------+-----------+-----------+------------+--------------+
| 000A     | 东京    | 0001      | T恤衫    |         1000 |
| 000A     | 东京    | 0002      | 打孔器  |          500 |
| 000A     | 东京    | 0003      | 运动T恤 |         4000 |
+----------+-----------+-----------+------------+--------------+
3 rows in set (0.00 sec)

主要事项

  • 进行联结时需要在from子句中使用多张表
  • 进行联结时必须使用on子句，并且要书写在from和where之间
  • 使用联结时select子句中的列必须按照<表的别名>.<列名>的格式进行书写

2.2 外联结（outer join）

  • 外联结也是通过on子句使用联结建将两张表进行联结，同时从两张表中选取相应的列。基本的使用方式并没有发生改变，只是结果却有所不同。
  • 内联结只能选取出同时存在于两张表中的数据，而外联结则只要数据存在于某一张表当中，就能够读取出来
# 右边为主表
mysql> select TS.tenpo_id, TS.tenpo_mei,
    -> S.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;
+----------+-----------+-----------+------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka |
+----------+-----------+-----------+------------+--------------+
| 000A     | 东京    | 0001      | T恤衫    |         1000 |
| 000D     | 福冈    | 0001      | T恤衫    |         1000 |
| 000A     | 东京    | 0002      | 打孔器  |          500 |
| 000B     | 名古屋 | 0002      | 打孔器  |          500 |
| 000A     | 东京    | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0003      | 运动T恤 |         4000 |
| 000C     | 大阪    | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0004      | 菜刀     |         3000 |
| 000C     | 大阪    | 0004      | 菜刀     |         3000 |
| NULL     | NULL      | 0005      | 高压锅  |         6800 |
| 000B     | 名古屋 | 0006      | 叉子     |          500 |
| 000C     | 大阪    | 0006      | 叉子     |          500 |
| 000B     | 名古屋 | 0007      | 切菜板  |          880 |
| 000C     | 大阪    | 0007      | 切菜板  |          880 |
| NULL     | NULL      | 0008      | 圆珠笔  |          100 |
| NULL     | NULL      | 0009      | 印章     |           95 |
+----------+-----------+-----------+------------+--------------+

主要事项

  • 选取出但表中的全部信息
  • 需要指定主表，通过使用left或者right关键字指定
  • 指定的主表，最终的结果中会包含主表内所有的数据
# 左边为主表
mysql> select TS.tenpo_id, TS.tenpo_mei,
    -> S.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;
+----------+-----------+-----------+------------+--------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka |
+----------+-----------+-----------+------------+--------------+
| 000A     | 东京    | 0001      | T恤衫    |         1000 |
| 000A     | 东京    | 0002      | 打孔器  |          500 |
| 000A     | 东京    | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0002      | 打孔器  |          500 |
| 000B     | 名古屋 | 0003      | 运动T恤 |         4000 |
| 000B     | 名古屋 | 0004      | 菜刀     |         3000 |
| 000B     | 名古屋 | 0006      | 叉子     |          500 |
| 000B     | 名古屋 | 0007      | 切菜板  |          880 |
| 000C     | 大阪    | 0003      | 运动T恤 |         4000 |
| 000C     | 大阪    | 0004      | 菜刀     |         3000 |
| 000C     | 大阪    | 0006      | 叉子     |          500 |
| 000C     | 大阪    | 0007      | 切菜板  |          880 |
| 000D     | 福冈    | 0001      | T恤衫    |         1000 |
+----------+-----------+-----------+------------+--------------+

2.3 多表联结

mysql> select TS.tenpo_id, TS.tenpo_mei, TS.shohin_id,
    -> S.shohin_id, S.hanbai_tanka, ZS.zaiko_suryo
    -> from TenpoShohin as TS inner join Shohin as S
    -> inner join ZaikoShohin as ZS
    -> on TS.shohin_id, ZS.shohin_id
    -> where ZS.souko_id = 'S001';

2.4 交叉联结（cross join）

  • 进行交叉连接的集合运算符是
  • 用处不大，不太使用cross join（笛卡尔积）
  • 进行交叉连接的时候无法使用on子句
  • 交叉联结是对两站表中的全部记录进行交叉组合，因此结果为两站表中行数的乘积
mysql> select TS.tenpo_id, TS.tenpo_mei, TS.shohin_id,
    -> S.shohin_mei
    -> from TenpoShohin as TS cross join Shohin as S;
+----------+-----------+-----------+------------+
| tenpo_id | tenpo_mei | shohin_id | shohin_mei |
+----------+-----------+-----------+------------+
| 000A     | 东京    | 0001      | T恤衫    |
| 000A     | 东京    | 0001      | 打孔器  |
| 000A     | 东京    | 0001      | 运动T恤 |
| 000A     | 东京    | 0001      | 菜刀     |
| 000A     | 东京    | 0001      | 高压锅  |
| 000A     | 东京    | 0001      | 叉子     |
| 000A     | 东京    | 0001      | 切菜板  |
| 000A     | 东京    | 0001      | 圆珠笔  |
| 000A     | 东京    | 0001      | 印章     |
| 000A     | 东京    | 0002      | T恤衫    |
...
文章作者: Escape
文章链接: https://www.escapelife.site/posts/4cf8d31f.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Escape !
Database SQL
赏
  目录