【MySQL】一文带你掌握聚合查询和联合查询

1. 聚合函数

概念：
聚合函数是一种用于处理数据集合的函数，它将多个数据行作为输入，执行特定的计算，然后返回单个结果。聚合函数通常用于统计和汇总数据，例如计算平均值、总和、最大值、最小值等。
常见的聚合函数包括：

1. COUNT - 统计数据行数
2. SUM - 计算数据总和
3. AVG - 计算数据的平均值
4. MAX - 找出数据的最大值
5. MIN - 找出数据的最小值

聚合函数通常与 GROUP BY 子句一起使用，以便根据一个或多个列对数据进行分组，并将聚合函数应用于每个分组。

以下举例都将以下表为例：

mysql> select * from student;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 猪八戒 |    90.5 | 54.5 |    80.0 |
|  2 | 唐僧   |    95.0 | 85.0 |    70.0 |
|  3 | 孙悟空 |    75.0 | 75.5 |    87.0 |
|  4 | 沙僧   |    80.0 | 45.0 |    90.5 |
+----+--------+---------+------+---------+

1.1 COUNT

统计数据行数

mysql> -- count 查询数据数量，为 NULL 的数据不会计入结果
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)mysql> select count(id) from student;
+-----------+
| count(id) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

1.2 SUM

计算数据总和

mysql> -- 计算数据之和，只能用于数值类型
mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
|     260.0 |
+-----------+
1 row in set (0.00 sec)mysql>-- 当用于非数值类型将为0
mysql> select sum(name) from student;
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 4 warnings (0.00 sec)mysql>-- SUM 函数只能用于数值类型的列，不能用于 *
mysql> select sum(*) from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from student' at line 1

1.3 AVG

计算数据的平均值

mysql> -- 平均值，只可以作用于数值类型列
mysql> select avg(math) from student;
+-----------+
| avg(math) |
+-----------+
|  65.00000 |
+-----------+
1 row in set (0.00 sec)mysql> select avg(math+chinese+english) from student;
+---------------------------+
| avg(math+chinese+english) |
+---------------------------+
|                 232.00000 |
+---------------------------+
1 row in set (0.00 sec)mysql> select avg(math+chinese+english) as avg from student;
+-----------+
| avg       |
+-----------+
| 232.00000 |
+-----------+
1 row in set (0.00 sec)

1.4 MAX，MIN

最大值，最小值

mysql> -- 最大值，最小值
mysql> select max(math), min(math) from student;
+-----------+-----------+
| max(math) | min(math) |
+-----------+-----------+
|      85.0 |      45.0 |
+-----------+-----------+
1 row in set (0.00 sec)mysql> select max(chinese+math+english) as max,min(chinese+math+english) as min from student;
+-------+-------+
| max   | min   |
+-------+-------+
| 250.0 | 215.5 |
+-------+-------+
1 row in set (0.00 sec)

2. GROUP BY

概念：
GROUP BY是SQL命令的一部分，用于按一个或多个列中具有相同值的行进行分组。通常与聚合函数一起使用，例如SUM、AVG、COUNT、MIN和MAX，以对每个组应用计算并对数据进行分组。GROUP BY子句通常用于报告和数据分析中，以对大量数据进行汇总和组织。它有助于识别数据中的模式和趋势，并使其更易于理解和解释。

语法：

SELECT column1，column2，aggregate_function（column3）FROM table_name GROUP BY column1，column2;

以下举例以此表为例：

mysql> select * from emp;
+----+--------+----------+----------+
| id | name   | role     | salary   |
+----+--------+----------+----------+
|  1 | 马晕   | 服务员   |  1000.20 |
|  2 | 马华藤 | 游戏陪玩 |  2000.99 |
|  3 | 孙悟空 | 游戏角色 |   999.11 |
|  4 | 猪无能 | 游戏角色 |   333.50 |
|  5 | 沙和尚 | 游戏角色 |   700.33 |
|  6 | 老王   | 董事长   | 12000.66 |
+----+--------+----------+----------+

查询每个角色的最高工资、最低工资和平均工资：

mysql> select role, max(salary),min(salary),avg(salary) from emp group by role;
+----------+-------------+-------------+--------------+
| role     | max(salary) | min(salary) | avg(salary)  |
+----------+-------------+-------------+--------------+
| 服务员   |     1000.20 |     1000.20 |  1000.200000 |
| 游戏角色 |      999.11 |      333.50 |   677.646667 |
| 游戏陪玩 |     2000.99 |     2000.99 |  2000.990000 |
| 董事长   |    12000.66 |    12000.66 | 12000.660000 |
+----------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

3. HAVING

概念：
HAVING子句是SQL的一部分，通常与GROUP BY子句一起使用，用于对分组后的数据进行过滤。它允许使用聚合函数计算并过滤分组后的数据，以便只输出特定条件的结果。

语法：

SELECT column1，aggregate_function（column2） FROM table_name GROUP BY column1 HAVING condition;

显示平均工资低于1500的角色：

mysql> select role,max(salary),min(salary),avg(salary) from emp group by role　having avg(salary)<1500;
+----------+-------------+-------------+-------------+
| role     | max(salary) | min(salary) | avg(salary) |
+----------+-------------+-------------+-------------+
| 服务员   |     1000.20 |     1000.20 | 1000.200000 |
| 游戏角色 |      999.11 |      333.50 |  677.646667 |
+----------+-------------+-------------+-------------+
2 rows in set (0.00 sec)

显示平均工资高于2000的角色：

mysql> select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)>2000;
+----------+-------------+-------------+--------------+
| role     | max(salary) | min(salary) | avg(salary)  |
+----------+-------------+-------------+--------------+
| 游戏陪玩 |     2000.99 |     2000.99 |  2000.990000 |
| 董事长   |    12000.66 |    12000.66 | 12000.660000 |
+----------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

4. 联合查询

概念：
联合查询（UNION）是SQL中的一种操作，用于将两个或多个SELECT语句的结果合并为一个结果集。联合查询可以将两个或多个表的数据合并在一起，并返回一个新的结果集。联合查询是对多张表的数据取笛卡尔积：

联合查询一般都会有一些行是不符合要求的，这个时候我们可以添加条件精选筛选。

以下举例以此为例：

mysql> select * from student;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
|    1 | 唐僧   |        1 |
|    2 | 沙僧   |        1 |
|    3 | 孙悟空 |        1 |
|    4 | 宋江   |        2 |
|    5 | 李逵   |        2 |
|    6 | 诸葛亮 |        3 |
|    7 | 张飞   |        3 |
|    8 | 曹操   |        3 |
|    9 | 周瑜   |        3 |
+------+--------+----------+
9 rows in set (0.00 sec)mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 西游班 |
|    2 | 水浒班 |
|    3 | 三国班 |
+------+--------+
3 rows in set (0.00 sec)

4.1 内连接

概念：
内连接是一种关系型数据库的查询方式，它是联合查询的一种，但并不是所有联合查询都是内连接。内连接是基于两个或多个表之间的公共键将记录连接在一起的查询方式。内连接仅返回两个表中都包含匹配键值的行，其他行将被排除在结果之外。内连接通常使用JOIN关键字实现。

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

当我们查询时，如果不进行条件筛选，那么将会造成下面场景：

这种结果显然不是我们想要的，所以我们可以加上条件，进行筛选，精简表格，就是上面语法两种：

mysql> --　join on 
mysql> select student.name,class.name from student join class on student.class_id = class.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 唐僧   | 西游班 |
| 沙僧   | 西游班 |
| 孙悟空 | 西游班 |
| 宋江   | 水浒班 |
| 李逵   | 水浒班 |
| 诸葛亮 | 三国班 |
| 张飞   | 三国班 |
| 曹操   | 三国班 |
| 周瑜   | 三国班 |
+--------+--------+
9 rows in set (0.00 sec)mysql> --　where 
mysql> select student.name,class.name from student, class where student.class_id = class.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 唐僧   | 西游班 |
| 沙僧   | 西游班 |
| 孙悟空 | 西游班 |
| 宋江   | 水浒班 |
| 李逵   | 水浒班 |
| 诸葛亮 | 三国班 |
| 张飞   | 三国班 |
| 曹操   | 三国班 |
| 周瑜   | 三国班 |
+--------+--------+
9 rows in set (0.00 sec)mysql> --　别名
mysql> select stu.name,cla.name from student stu, class cla where stu.class_id = cla.id;
+--------+--------+
| name   | name   |
+--------+--------+
| 唐僧   | 西游班 |
| 沙僧   | 西游班 |
| 孙悟空 | 西游班 |
| 宋江   | 水浒班 |
| 李逵   | 水浒班 |
| 诸葛亮 | 三国班 |
| 张飞   | 三国班 |
| 曹操   | 三国班 |
| 周瑜   | 三国班 |
+--------+--------+
9 rows in set (0.00 sec)

4.2 外连接

概念：
在数据库中，外连接（outer join）是指通过两个或多个表中的一个或多个共同数据列将它们连接起来，产生一个新的查询结果集。不同于Inner Join（内连接），外连接能够返回那些在一个表中存在但在另一个表中不存在的数据行。
外连接有左连接（left join）、右连接（right join）和全连接（full join）三种类型。
左连接（left join）返回左表中的所有数据行，即使在右表中没有匹配的数据行，也不会过滤掉左表中的数据。右连接（right join）则是返回右表中的所有数据行。
全连接（full join）是指返回两个表中的所有数据，不管它们在另一个表中是否有与之匹配的数据。全连接通常不常用，因为它可能会返回非常大的结果集。
外连接和内连接不同点在于外连接可以返回任何表中的数据，而内连接只能返回两个表中共有的数据行。

语法：

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

代码使用和外连接基本相同，为了防止博客臃肿，大家可以自己敲打试试，不会的话随时可以私信问我。

4.3 自连接

概念：
自链接（self join）是指在同一张表中进行联接操作的过程。它和普通的表之间连接操作类似，但是表名出现了两次，也就是同一个表名在同一查询中出现了两次或以上。
自链接的语法格式与普通的表之间连接非常相似，只需要在表名后加上别名即可，这里的别名可以是任何合法的标识符，用于区分同一个表中不同的记录。

语法：

SELECT a.name, b.name AS leader　FROM staff a, staff b　WHERE　a.leader_id = b.id;

4.4 子连接

概念：
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

使用示例：

mysql> -- 借助子查询查询诸葛亮同班同学
mysql> select * from student where class_id=(select class_id from student where name='诸葛亮');
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
|    6 | 诸葛亮 |        3 |
|    6 | 张飞   |        3 |
|    6 | 曹操   |        3 |
|    6 | 周瑜   |        3 |
+------+--------+----------+
4 rows in set (0.00 sec)mysql> -- 也可以将=换成in
mysql> select * from student where class_id in (select class_id from student where name='诸葛亮');
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
|    6 | 诸葛亮 |        3 |
|    6 | 张飞   |        3 |
|    6 | 曹操   |        3 |
|    6 | 周瑜   |        3 |
+------+--------+----------+
4 rows in set (0.00 sec)mysql> -- 使用not in可以查询非诸葛亮同班同学
mysql> select * from student where class_id not in (select class_id from student where name='诸葛亮');
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
|    1 | 唐僧   |        1 |
|    2 | 沙僧   |        1 |
|    3 | 孙悟空 |        1 |
|    4 | 宋江   |        2 |
|    5 | 李逵   |        2 |
+------+--------+----------+
5 rows in set (0.00 sec)

5.合并查询

概念：
MySQL的合并查询（union）是一种在多个查询结果中获取不同数据行并将它们整合成一个查询结果集的查询方式。合并查询需要满足一定的条件，比如数据行必须具有相同的列数和数据类型，并且必须按照相同的顺序排列。
合并查询通常用于需要从多个表或查询结果中获取数据的查询需求。合并查询分为两种类型：UNION和UNION ALL。UNION去重并合并查询结果，而UNION ALL只是简单地合并查询结果。

5.1 UNION

mysql> -- student表
mysql> select * from student;
+--------+----------+
| name   | class_id |
+--------+----------+
| 唐僧   |        1 |
| 沙僧   |        1 |
| 孙悟空 |        1 |
| 宋江   |        2 |
| 李逵   |        2 |
| 诸葛亮 |        3 |
+--------+----------+
6 rows in set (0.00 sec)mysql> -- class表
mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 西游班 |
|    2 | 水浒班 |
|    3 | 三国班 |
+------+--------+
3 rows in set (0.00 sec)mysql> -- 自己合并自己使用union就只能得到一个自己表
mysql> select * from student-> union-> select * from student;
+--------+----------+
| name   | class_id |
+--------+----------+
| 唐僧   |        1 |
| 沙僧   |        1 |
| 孙悟空 |        1 |
| 宋江   |        2 |
| 李逵   |        2 |
| 诸葛亮 |        3 |
+--------+----------+
6 rows in set (0.00 sec)mysql> -- 两个不同的表列数要相同，不然不能拼接
mysql> select * from student-> union-> select * from class;
+--------+----------+
| name   | class_id |
+--------+----------+
| 唐僧   | 1        |
| 沙僧   | 1        |
| 孙悟空 | 1        |
| 宋江   | 2        |
| 李逵   | 2        |
| 诸葛亮 | 3        |
| 1      | 西游班   |
| 2      | 水浒班   |
| 3      | 三国班   |
+--------+----------+
9 rows in set (0.00 sec)

5.2 UNION ALL

mysql> 必须列数相同，全部拼接在一张表
mysql> select * from student-> union all-> select * from class;
+--------+----------+
| name   | class_id |
+--------+----------+
| 唐僧   | 1        |
| 沙僧   | 1        |
| 孙悟空 | 1        |
| 宋江   | 2        |
| 李逵   | 2        |
| 诸葛亮 | 3        |
| 1      | 西游班   |
| 2      | 水浒班   |
| 3      | 三国班   |
+--------+----------+
9 rows in set (0.00 sec)mysql> -- 没有去重效果
mysql> select * from student-> union all-> select * from student;
+--------+----------+
| name   | class_id |
+--------+----------+
| 唐僧   |        1 |
| 沙僧   |        1 |
| 孙悟空 |        1 |
| 宋江   |        2 |
| 李逵   |        2 |
| 诸葛亮 |        3 |
| 唐僧   |        1 |
| 沙僧   |        1 |
| 孙悟空 |        1 |
| 宋江   |        2 |
| 李逵   |        2 |
| 诸葛亮 |        3 |
+--------+----------+
12 rows in set (0.00 sec)