MySQL --- 聚合查询和联合查询

清疚 2024-02-21 09:58 40阅读 0赞

## 聚合查询： ##

下文中的所有**聚合查询**的示例操作都是基于此表：

![3fc13fb3ec7a4d79b738985d6a1e01bf.png][]

### 聚合函数 ###

聚合函数都是行与行之间的运算。

#### count() ####

**select count(列名) from 表名;**

统计该表中该列的行数，但是**null 值不会统计在内，****但是如果写为 count(\*) 那么 null 也会算进去（****就算一整行都是 null 也会算进去****）**

![3f4b7bc33141447cb75aaed982ab8551.png][]

例如我们暂时使用这个表进行演示：

![b67fc54e5e30496ebace6e9fa6854bd7.png][]

此表有一行全为空

![a6ce818be5954a4fbdc027bbd23da472.png][]![9c6bec90567042db9b27db65ffb3739d.png][]

#### sum() ####

**select sum(列名) from 表名；**

**只能针对数字类型使用，也可以对多列进行相加求一个总和；**

对该表中的math列的值进行求和。

**注：会将 null 值排除在外，不予计算。**

![16c1d1ef730041a981e5e7b958c0441a.png][]

#### avg() ####

**select avg(列名) from 表名;**

对该表中的该列进行求平均值。

**只能针对数字类型使用**

**注：会将 null 值排除在外，不予计算。**

![b6e2908d59f64dc29fbbe35b94475b08.png][]

#### max() ####

**select max(列名) from 表名;**

求出该表中该列的最大值。

**只能针对数字类型使用**

**注：会将 null 值排除在外，不予计算。**

![53b2676f121a421fa251a2201c4978e7.png][]

#### min() ####

**select min(列名) from 表名;**

求出该表中该列的最小值。

**只能针对数字类型使用**

**注：会将 null 值排除在外，不予计算。**

![8c0b55c5d2614dbf9ff466fc726c37ed.png][]

### group by ###

**group by 可以实现对数据进行分组**。

一般的使用方法都是先用 group by 进行分组，然后再利用聚合函数进行聚合查询。

**select \* from 表名 group by 列名;**

group by 会将该列中**值相同的分为一组**。因为**没有**使用order by **进行排序操作**所以**MySQL并不会对查询结果的顺序做出保证。**

![fcb407fb63b84bd69ac2b13de296fc86.png][]

此时我们在原有表的基础上再加一行数据。

![3a93f032a51e4dfe98c4e4ee7ab481f5.png][]

加入该条数据后该表变成了：

![246f68eea7ef43faa8b4b6466ad1f891.png][]

此时再进行分组查询操作：

![65607b4886fc443098f50f32a4d15264.png][]

此时因为没有使用聚合函数所以 **张三这一行 只是在张三这一组中选了一个代表**。

![36919554f1684a369583f79f4b47c2d3.png][]

如果**加入聚合函数**就可以很清楚的看到，**聚合函数会以每组为单位进行操作**。

![9990e4dbfe56478cbcacfbfd20049875.png][]

此时如果我们不想让某行（某几行）参与分组就可以在 group by 之前加入 where 进行条件筛选。

**select \* from 表名 where 条件 group by 列名;**

例：此时我们不想让 null 这个空值和 math=100 的行参与分组

![f99edd89837d42319163c601da8d5358.png][]

**注意：where 条件 必须在 group by 之前**

在后面就会报错。

![a773d83bb9e541f58af9de8f2376733e.png][]

### having ###

having 其实和 where 差不多都是进行条件筛选的语句。

**group by 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 where 语句，而需要用 having**

**having是对分组之后的数据进行筛选的。**

例：此时我们不想让 null 这个空值和 成绩=100 的行参与分组

![955d732588a44279ab4d636ef569dc78.png][]

此时的语句执行顺序为：

![7267e96b5df94fc5b2853869b64673e0.png][]

所以 having 子句中的条件一定要和 select 中的对应

![18cc21d644bd4de8b3008dcade199a74.png][]

**注：having 必须写在 group by 子句之后。**

![0888f338e9e34859b91a034e4d224b88.png][]

--------------------

## 联合查询： ##

实际开发中往往数据来自不同的表，所以需要多表联合查询。

**多表查询的关键是对多张表的数据取笛卡尔积：**

下面我先简单介绍一下笛卡尔积，假设我们现在有如下的两张表。而对他们的数据取笛卡尔积就是用第一个表的每行数据分别与第二张表的每行数据进行组合。

![4b38a8a0f41c4d32a1c4d165d78ec3da.png][]

这两张表进行笛卡尔积之后就变成了一个 9 行 4 列的新表，如图所示

![0ee48294ab06429aa0b3acda440307c5.png][]

这就是对两个表进行去取笛卡尔积的结果。

但是，我们很容易就可以发现这里面有非常多的所谓的非法数据，

![ad1916365f734cafbabfa07440b22d7b.png][]

从图中我们可以看到只有三条数据是合法的，而我们再进行数据库操作的时候肯定不能允许有这么多的非法数据出现在查询结果中，此时我们就可以通过寻找两张表的关联属性来通过 where 条件来进行筛选，比如上面的表就可以通过设置两张表的班级相同来进行筛选。

### 内连接 ###

内连接其实就是在多张表中**求交集**（或者说就是最终查询的结果中的每条数据的链接条件都存在于原本的多张表中）。

![60059ad05d454aa48627e38b5bbc3958.png][]

此处为了更好的演示，我先创建了三张表

![bd4b1c2f9eaf40cd8b5729cc835c5270.png][]

![3b6e7083b8b34f2e81720393c9ad3d2c.png][]

![a7e13556a39d4b689c01135bb8b90b84.png][]

内连接的语句就这两条：

**select 字段 from 表1  \[inner\] join 表2 on 连接条件 and 其他条件;**

**select 字段 from 表1 ,表2 where 连接条件 and 其他条件;**

上面这些格式看着很复杂其实完全没有必要记这些。只要按照下面这些步骤一步一步的多练习几次就能掌握。

内连接本质上分为以下几步：

例：查询凌华的各科成绩。

#### 第一步：先求出笛卡尔积 ####

**select \* from 表一,表二,……**

通过上述语句就可以求出多张表的笛卡尔积。

![ff055fce8c4a4778b3cdfb7026e9203d.png][]

此时一共输出了27条数据。

#### 第二步：加上连接条件，筛选出合法信息 ####

此处推荐再写where中的条件时，使用 **表名 点 列名** 的写法因为表中可能会有相同的列名。

![fb131a0685474a2cb9ff7dd123b233c9.png][]

#### 第三步：结合需求进一步增加条件，对结果进行筛选； ####

此处需要增加的条件就是名字是凌华。

![1ac474e055054f6f9b28d7a9fa1cc04f.png][]

#### 第四步：对列进行精简，筛选出需要展示的列 ####

![02dbf041515d4240ac75005b35bd06f9.png][]

此时你再对照开头的表达式就会发现一摸一样，基本上所有的内连接都可以套用这四大步。

**select 字段 from 表1  \[inner\] join 表2 on 连接条件 and 其他条件;**

**select 字段 from 表1 ,表2 where 连接条件 and 其他条件;**

**注：第一条语句中的** **\[inner\] join** **中的** **inner** **之所以用 \[\] 括起来是因为****可以省略不写。**

### 外连接 ###

如果多张表中的信息都存在对应关系那么内连接和外连接的结果就都是一样的，如果表中的数据不对应内外连接就会出现差别。

**外连接分为左外连接和右外连接。**

例：有如下两张数据没有完全对应的表

![a6aa3c11b24547c4a21effad12ec47e2.png][]![582b6c93a47743faa0d727e90edc8033.png][]

#### 左外连接 ####

![d179e7c8fec346779c21eb6f7291086b.png][]

**select 字段 from 表1 left join 表2 on 连接条件 and 其他条件;**

这个表达式除了 left 之外和内连接都是相同的。

![f623daac761f4f61bd96e8f3cbe071d2.png][]

**左外连接就是以左表为基础**，如果左表有右表没有那么就**用 null 代替。**

#### 右外连接 ####

![a78695ec779740b2ad3484e90f35cc4d.png][]

**select 字段 from 表1 right join 表2 on 连接条件 and 其他条件;**

这个表达式除了 right 之外和左外连接都是相同的。

![8c6680a514914742b19ea8dea7f011d2.png][]

**右外连接就是以右表为基础**，如果右表有左表没有那么就**用 null 代替。**

### 自连接 ###

**自连接就是自己和自己取笛卡尔积，和内连接的解题方法相同。**

**注：在自连接中必须要给表取别名否则会报错。**

![60540b1e580d4d269b6c274bed0bb16f.png][]

**自连接的优点：可以将行与行之间的关系转换成列与列。**

![3b6e7083b8b34f2e81720393c9ad3d2c.png][]![a7e13556a39d4b689c01135bb8b90b84.png][]

例：在上述表中查询出 C++ 成绩大于单片机成绩的人。

![d635252c772f4c26a05da3f36bea184c.png][]

### 子查询 ###

**子查询本质上就是‘套娃’，就是将多个简单的sql语句嵌套成一个复杂的sql语句。**

![a6aa3c11b24547c4a21effad12ec47e2.png][]![582b6c93a47743faa0d727e90edc8033.png][]

例：查找出名为可莉的学生在几年级。

**1.先找出可莉的班级号**

![ae802cc825b5454c8b0ac1cd5a88a1aa.png][]

**2.根据班级号在班级表中找到班级名**

![85b3ec74b90640919eb6481eb92097e5.png][]

**而子查询就是将这两句sql语句嵌套起来**

![c24bb785e8824a5db48b897ab2cd9eb6.png][]

### 合并查询 ###

**合并查询是通过 union 来实现的**

在MySQl中可以通过 union 来将多个表的查询结果合并成一张表。

**注：合并的两个结果的对应列之间列数和数据类型必须相同（列名系统会自动按照第一个表的列名），union 会去掉结果中的重复项。**

![936c8f902d534dc3ac810a7387161c66.png][]![b4715bf0dc9e4f5cb91b16de1cf9f832.png][]

例如查询这两张表中的 id 和 name 结果返回一张表；

![11a51920f896497eb820b77303db8a78.png][]

在这张结果中只有一个 id = 1 的学生。

如果你**不想对结果进行去重**处理，就可以使用 **union all**

![0aba331481db459bad1ee586377fae14.png][]

如果对**同一张表使用 union** 那么它的**结果会和使用 or 相同**。

![31ffa21008b54f9598ab98f2fa2bb6f3.png][]

![5bad49fa61854e659cb563db3dac4693.png][]

[3fc13fb3ec7a4d79b738985d6a1e01bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/2c92821905a6497195345b95cce22b72.png
[3f4b7bc33141447cb75aaed982ab8551.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/df3ee8297d6346869b28c9c5d23453ca.png
[b67fc54e5e30496ebace6e9fa6854bd7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/6ade4f370c3942f2896a576c60dc6225.png
[a6ce818be5954a4fbdc027bbd23da472.png]: https://img-blog.csdnimg.cn/a6ce818be5954a4fbdc027bbd23da472.png
[9c6bec90567042db9b27db65ffb3739d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/b4b633a5da074d03ba759a75bec8a470.png
[16c1d1ef730041a981e5e7b958c0441a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/035189a37b7e4827a9ec031e541813ea.png
[b6e2908d59f64dc29fbbe35b94475b08.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/72a3af08a99846ce856af45ecff1f05c.png
[53b2676f121a421fa251a2201c4978e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/08451a91b1374bf68f158ca8b62c8f1c.png
[8c0b55c5d2614dbf9ff466fc726c37ed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/bfe639d20bd54185a454346c9dea11e1.png
[fcb407fb63b84bd69ac2b13de296fc86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/70db9788d9b3449ea9f059a90dfcc29d.png
[3a93f032a51e4dfe98c4e4ee7ab481f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/97d4a2a0ae7a432d982addbba68959d4.png
[246f68eea7ef43faa8b4b6466ad1f891.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/9ca2013a09f4494a88d7fbb8da7d7743.png
[65607b4886fc443098f50f32a4d15264.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/a830c05ec2c64a65b7c90559690a0a0a.png
[36919554f1684a369583f79f4b47c2d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/c7ecc48783064a11b918e5dad05be8e2.png
[9990e4dbfe56478cbcacfbfd20049875.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/29e00a1581d04b95be6a764b5c1314e6.png
[f99edd89837d42319163c601da8d5358.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/47a6c93ff2f14fcd99c9bcaa1c2ece3e.png
[a773d83bb9e541f58af9de8f2376733e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/bb0f611a6a524b889712f16cb91e3539.png
[955d732588a44279ab4d636ef569dc78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/421ef3907bed4b15b3e9a20fa793d44f.png
[7267e96b5df94fc5b2853869b64673e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/4ab7c314663f4b14a3c28aa5d34439a1.png
[18cc21d644bd4de8b3008dcade199a74.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/d69e5cc7b0eb4aa1bbef449a97086660.png
[0888f338e9e34859b91a034e4d224b88.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/96c802388c85457a84e7d08d052b43b5.png
[4b38a8a0f41c4d32a1c4d165d78ec3da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/23246a3d00b64e28b5a3e3b982a94ce9.png
[0ee48294ab06429aa0b3acda440307c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/6d49bf77bae64921ac8900f820f4e1a4.png
[ad1916365f734cafbabfa07440b22d7b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/f52025d6e6d3414dbe4940b6114caaf5.png
[60059ad05d454aa48627e38b5bbc3958.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/5574e7748ab041788491a9f8871513f3.png
[bd4b1c2f9eaf40cd8b5729cc835c5270.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/68d56712d714400c9a4d2bb82ea760be.png
[3b6e7083b8b34f2e81720393c9ad3d2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/610c32c450564c4bb8b23895feec915b.png
[a7e13556a39d4b689c01135bb8b90b84.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/e0b1201d7c7746d2b41da37dcbfb3ef5.png
[ff055fce8c4a4778b3cdfb7026e9203d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/9acf17468e1748a89db39870348a9563.png
[fb131a0685474a2cb9ff7dd123b233c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/3c1334a92c6e4af0ad80e247fb310e79.png
[1ac474e055054f6f9b28d7a9fa1cc04f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/e3f0c33dcb7043c3981887219310e99c.png
[02dbf041515d4240ac75005b35bd06f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/ca59858e8da94d67895af85012382e40.png
[a6aa3c11b24547c4a21effad12ec47e2.png]: https://img-blog.csdnimg.cn/a6aa3c11b24547c4a21effad12ec47e2.png
[582b6c93a47743faa0d727e90edc8033.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/f0358347a00a4670a12ecf1f3ec4ae1d.png
[d179e7c8fec346779c21eb6f7291086b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/415751c614e548a3b0251701fbfd2329.png
[f623daac761f4f61bd96e8f3cbe071d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/e06a8a57bf054d13b03de5bb19322bd5.png
[a78695ec779740b2ad3484e90f35cc4d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/361d667aa46e4278a54cb99e6e7a5fe1.png
[8c6680a514914742b19ea8dea7f011d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/c07ec2b40d6d4171ad5e84d02161f0ae.png
[60540b1e580d4d269b6c274bed0bb16f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/3cb986ac850f4e02b447637a21259eeb.png
[d635252c772f4c26a05da3f36bea184c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/8d4c2ad1f1c14936a1f47732b05d3b8b.png
[ae802cc825b5454c8b0ac1cd5a88a1aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/1ce249d9867f4564a2b28f6b219cf3a8.png
[85b3ec74b90640919eb6481eb92097e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/042bd002ad324279ae879c4b76536ea5.png
[c24bb785e8824a5db48b897ab2cd9eb6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/ab4ac47ff232422b8d055b28ee2eb837.png
[936c8f902d534dc3ac810a7387161c66.png]: https://img-blog.csdnimg.cn/936c8f902d534dc3ac810a7387161c66.png
[b4715bf0dc9e4f5cb91b16de1cf9f832.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/4a7c2aa48adc42228facd79dafbed316.png
[11a51920f896497eb820b77303db8a78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/289896737914419ba70bb2cd7c6f1b6f.png
[0aba331481db459bad1ee586377fae14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/6db2dce96b2f4ad897e85f1616dd09f6.png
[31ffa21008b54f9598ab98f2fa2bb6f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/98aec8143b35475790931f56f263bda5.png
[5bad49fa61854e659cb563db3dac4693.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/f426d4df693c4c468f5e307b8c84f819.png