conanan's blog
conanan
2021-01-13

集合运算 🔥

# 集合运算

集合在数学领域表示“(各种各样的)事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

# 表的加减法

# 表的加法—去除重复行—UNION

UNION 等集合运算符默认都会除去重复（即合并，比较耗时，所以一般使用 UNION ALL）的记录（除了 UNION ALL）。注意（同样适用于其他）：

  • 作为运算对象的记录的列数必须相同
  • 作为运算对象的记录中列的类型必须一致。CAST 转换后一致也可以。可能隐式类型转换
  • 可以使用任何SELECT、WHERE、GROUP BY、HAVING 等语句，但ORDER BY、LIMIT子句只能在最后使用一次
  • 需要注意的一点是，由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名（别名也可以）。

如果我们只想单独为各个小的查询排序，而不为最终的汇总的结果集排序行不行呢？先试试：

mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12

从结果来看，我们为各个小查询加入的ORDER BY子句好像并没有起作用，这是因为设计MySQL的大叔规定组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的，也就是说我们在各个小查询中加入ORDER BY子句的作用和没加一样～ 不过如果我们只是单纯的想从各个小的查询中获取有限条排序好的记录加入最终的汇总，那是可以滴，比如这样：

mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10

如图所示，最终结果集中的(3, 'c')其实就是查询(SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1)的结果，(4, 'd')其实就是查询(SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1)的结果。

# 表的加法—包含重复行—UNION ALL

# 公共部分—INTERSECT

选取两个记录集合中公共部分的 INTERSECT(交集)

# 表的减法—EXCEPT

减法运算 EXCEPT(差集)，其语法也与 UNION 相同。

EXCEPT 有一点与 UNION 和 INTERSECT 不同，那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。

注意：

  • 只有 Oracle 不使用 EXCEPT，而是使用其特有的 MINUS 运算符
  • MySQL 还不支持 EXCEPT，因此也无法使用。

# 连接/联结(以列为单位)

联结(JOIN)就是将其他表中的列添加过来，进行“添加列”的集合运算。 UNION 是以行(纵向)为单位进行操作，而联结则是以列(横向)为单位 进行的。分为内联结和外联结两种。

ON 是专门用来指定联结条件的（联结键），它能起到与 WHERE 相同的作用。需要指定多个键时，同样可以使用 AND、OR。并且 ON 必须书写在 FROM 和 WHERE 之间。

联结条件也可以使用“=”来记述。在语法上，还可以使用 <= 和 BETWEEN 等谓词。但是九成以上都用=。

# 交叉联结——CROSS JOIN

这种联结在实际业务中并不会使用，但是交叉联结是所有联结运算的基础。

对于返回笛卡儿积的联结称为交叉联结，即不使用 WHERE 过滤记录的联结查询！

# 内连接——JOIN...ON

内连接其实是带有 WHERE 或 ON 过滤条件的交叉联结。所以内连接查询出的所有记录都满足条件

内连接中的 WHERE 子句和 ON 子句是等价的

  • 显式内连接 ([INNER] JOIN...ON)

    SELECT * FROM 表1 (AS) 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx -- 还有不等关系
    1

  • 隐式内连接( , WHERE)（MySQL 和 Oracle 都支持）

    SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx -- 还有不等关系
    1

# 外连接——* JOIN...ON

  • 左外(LEFT [OUTER] JOIN...ON)

    左表记录无论是否满足条件都会查询出，而右表满足条件才能查出。左表中不满条件的记录，右表补NULL

    SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx -- 还有不等关系
    1

  • 右外(RIGHT [OUTER] JOIN...ON)

    右表记录无论是否满足条件都会查询出，而左表满足条件才能查出。右表中不满条件的记录，左表补NULL

    SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx -- 还有不等关系
    1

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。在运行时（生成中间表时）会使用到 ON 过滤，生成中间表后使用 WHERE 过滤

# 例子

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 自连接

我们上边说的都是多个不同的表之间的连接，其实同一个表也可以进行连接。比方说我们可以对两个t1表来生成笛卡尔积，就像这样：

mysql> SELECT * FROM t1, t1;
ERROR 1066 (42000): Not unique table/alias: 't1'
mysql>
1
2
3

咦，报了个错，这是因为设计 MySQL 的大叔不允许FROM子句中出现相同的表名。我们这里需要的是两张一模一样的t1表进行连接，为了把两个一样的表区分一下，需要为表定义别名。比如这样：

mysql> SELECT * FROM t1 AS table1, t1 AS table2;
+------+------+------+------+
| m1   | n1   | m1   | n1   |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    1 | a    |
|    3 | c    |    1 | a    |
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
+------+------+------+------+
9 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这里相当于我们为t1表定义了两个副本，一个是table1，另一个是table2，这里的连接过程就不赘述了，大家把它们认为是不同的表就好了。由于被连接的表其实是源自同一个表，所以这种连接也称为自连接。我们看一下这个自连接的现实意义，比方说我们想查看与'史珍香'相同专业的学生有哪些，可以这么写：

mysql> SELECT s2.number, s2.name, s2.major FROM student_info AS s1 INNER JOIN student_info AS s2 WHERE s1.major = s2.major AND s1.name = '史珍香' ;
+----------+-----------+--------------+
| number   | name      | major        |
+----------+-----------+--------------+
| 20180103 | 范统      | 软件工程     |
| 20180104 | 史珍香    | 软件工程     |
+----------+-----------+--------------+
2 rows in set (0.01 sec)

mysql>
1
2
3
4
5
6
7
8
9
10

s1、s2都可以看作是student_info表的一份副本，我们可以这样理解这个查询：

  • 根据s1.name = '史珍香'搜索条件过滤s1表，可以得到该同学的基本信息：

    +----------+-----------+------+--------------------+-----------------+--------------+-----------------+
| number   | name      | sex  | id_number          | department      | major        | enrollment_time |
+----------+-----------+------+--------------------+-----------------+--------------+-----------------+
| 20180104 | 史珍香    | 女   | 141992199701078600 | 计算机学院      | 软件工程     | 2018-09-01      |
+----------+-----------+------+--------------------+-----------------+--------------+-----------------+
    1
    2
    3
    4
    5

  • 因为通过查询s1表，得到了'史珍香'所在的专业其实是'软件工程'，接下来就应该查询s2表了，查询s2表的时候的过滤条件s1.major = s2.major就相当于s2.major = '软件工程'，于是查询到 2 条记录：

    +----------+-----------+------+--------------------+-----------------+--------------+-----------------+
| number   | name      | sex  | id_number          | department      | major        | enrollment_time |
+----------+-----------+------+--------------------+-----------------+--------------+-----------------+
| 20180103 | 范统      | 男   | 17156319980116959X | 计算机学院      | 软件工程     | 2018-09-01      |
| 20180104 | 史珍香    | 女   | 141992199701078600 | 计算机学院      | 软件工程     | 2018-09-01      |
+----------+-----------+------+--------------------+-----------------+--------------+-----------------+
    1
    2
    3
    4
    5
    6

    而我们只需要s2表的number、name、major这 3 个列的数据，所以最终的结果就长这样：

    +----------+-----------+--------------+
| number   | name      | major        |
+----------+-----------+--------------+
| 20180103 | 范统      | 软件工程     |
| 20180104 | 史珍香    | 软件工程     |
+----------+-----------+--------------+
    1
    2
    3
    4
    5
    6

# 连接查询与子查询的转换 🔥

有的查询需求既可以使用连接查询解决，也可以使用子查询解决，比如

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
1

这个子查询就可以被替换：

SELECT s2.* FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.major = '计算机科学与工程';
1

大家在实际使用时可以按照自己的习惯来书写查询语句。

提示

小贴士： MySQL 服务器在内部可能将子查询转换为连接查询来处理，当然也可能用别的方式来处理，不过对于我们刚入门的小白来说，这些都不重要，知道这个语句会把哪些信息查出来就好了！

编辑
上次更新: 2021/01/17, 16:35:51
复杂查询 🔥
存储程序

← 存储程序→

最近更新
01
线程生命周期
 07-06
02
线程安全理论
 06-24
03
并发简史
 06-24
更多文章>
Theme by Vdoing | Copyright © 2019-2021 Evan Xu | MIT License
×