MySQL数据库增删改查进阶 — 聚合查询、分组查询、联合查询

1.聚合查询

聚合查询是针对行与行之间进行的计算。

聚合查询需要搭配聚合函数来实现。

SQL常见的聚合函数：

• count — 返回查询到的数据的数量。
• sum — 返回查询到的数据的总和，不是数字没有意义。
• avg — 返回查询到的数据的平均值，不是数值没有意义。
• max — 返回查询到的数据的最大值，不是数字没有意义。
• min — 返回查询到的数据的最小值，不是数字没有意义。

都是针对某一列的所有行来进行计算的！！！

1.1 count 函数

count 函数计算的是数据的行数。

示例：查询分数表中数据的行数

命令格式：

select count(*) from 表名;
1

演示：

count后面括号里的内容不一定要写 “ * ”，也可以写列名或者表达式。


如果数据有全为 NULL 的一行，写列名或者表达式不会将这一行计算进去

演示：

根据图示看出行数减少了一个。


注意：

count 函数后面不能有空格！！！

1.2 sum 函数

sum 函数的作用是将这一列所有的行进行加和。

要求：这个列要是数字，不能是字符串或者日期

示例：求所有同学数学成绩总和

命令格式：

select sum(math) from 表名;
1

演示：

得到的结果就是所有同学的数学成绩总和。

1.3 avg 函数

用来求某一列每一行的平均分

示例：求所有同学语文成绩的平均分

命令格式：

select avg(chinese) from 表名;
1

演示：

求得所有同学语文成绩平均分

当前只是针对某一列进行计算，还可以针对表达式进行聚合查询！！！

示例：计算三门课程总分的平均分

命令格式：

select avg(chinese + math + english) from 表名;
1

演示：

1.4 max 和 min 函数

用来求最大值和最小值。

示例：求英语成绩的最大值和最小值

命令格式：

select max(english), min(english) from 表名;
1

演示：

2.分组查询

使用 group by 对表中行进行分组。

不用 group by 分组的时候，相当于就只有一组，把所有的组进行聚合。

引入 group by 就可以针对不同的组来分别进行聚合！！！

2.1 group by 子句

示例：分组计算每个工作岗位的平局薪水

命令格式：

select 列名, avg(列名) from 表名 group by 列名;
1

演示：

求出的就是每个岗位的平均薪水。

把 role 这一列值相同的行分为一组。
计算平均值的时候也是针对每个分组，分别计算！！！

2.2 分组查询可以指定条件

指定条件，有以下几种情况：

• 分组之前，指定条件。先筛选，再分组。（where）
• 分组之后，指定条件。先分组，再筛选。（having）
• 分组之前和之后，都指定条件。

2.2.1 分组之前，指定条件

示例：统计每个岗位的平均工资，但是抛去马云的。

命令格式：

select 列名, avg(列名) from 表名 where 列名 != '马云' group by 列名;
1

演示：

结果是老板的平局薪水发生了改变。

2.2.2 分组之后，指定条件

示例：查询每个岗位的平均工资，但是抛去工资在10w之上的

命令格式：

select 列名, avg(列名) from 表名 group by 列名 having avg(列名) <100000;
1
2

演示：

发现程序猿岗位薪水小于100000。

很明显，这个平均值是先进分组。在针对每一组进行筛选的！！！

2.2.3 分组前后都指定条件

示例：查询每个岗位的平均工资，但是抛去张三和老板的

命令格式：

select 列名, avg(列名) from 表名 where 列名 != '张三' group by 列名 having 列名 != '老板';
1

演示：

3.联合查询

联合查询也较多表查询。

创建一个学生表：
student (id name classId)

id（1）； name（张三）；classId（1）
id（2）； name（李四）；classId（2）
id（3）； name（王五）；classId（3）
id（4）； name（赵六）；classId（4）

创建一个班级表：
class(classId name)

classId（1）； name（java105）
classId（2）； name（java106）

3.1 笛卡尔积

笛卡尔积就是把这两个表放到一起进行计算。

思路：

分别取出第一张表的每一行和第二张表的每一行配对，得到一个新的记录。


匹配后的笛卡尔积：

命令格式：

select * from 表名, 表名...;
1

演示：

笛卡尔积是通过排列组合来的！！！

笛卡尔积可以通过排列得到一个更大的表！！！

列数就是两个表列数之后，行数就是两个表行数之和！！！

3.1.1 笛卡尔积中无意义数据的去除

笛卡尔积中有很多是无意义的数据，只有一部分是有意义的。

需要把无意义的数据去掉，怎么做？

什么是无意义的数据？

根据学生表可以看出张三是 1 班的学生，
因此笛卡尔积张三是在1班的数据即为无意义的数据。

其他同学的情况类似，图中笛卡尔积中画圈的即为无意义的数据！！！

去除的思路：classId相同的即是有意义的数据，相同的保留，不同的删除。

命令格式：

select * from 表名, 表名 where 表名.列名 = 表名.列名;
1

演示：

筛选数据的条件，称为连接条件。

3.1.2 笛卡尔积练习

创建一个学生表、班级表、课程表、分数表。

学生表：

班级表：

课程表：


分数表：


学生和课程是多对多的关系。
要想表述这个多对多的关系，需要引入多个关联表！！！

分数表正是描述了学生和课程之间的关联关系，顺便又把分数也给列出来了！！！

3.2 内连接

示例1：查询许仙同学的成绩

思路：

• 许仙同学是学生姓名，属于学生表。
• 成绩是分数，属于分数表。
• 把两个表进行联合查询

如何进行联合查询？

1、先计算两个表的笛卡尔积

命令：

select * from student, classes;
1

因为数据行数过多这里就不演示了，与上述的笛卡尔积类似，只是大小不一样。

2、引入连接条件

命令：

select * from student, score where student.id = score.student_id;
1

演示：

这是去除无用数据的笛卡尔积。


3、根据需求加上必要的条件

命令：

select * from student, score where student.id = score.student_id and student.name = '许仙';
1

演示：

这里就查到了许仙同学的全部信息。

4、把不必要的数据去掉

命令：

select student.name, score.score from student, score where student.id = score.student_id and student.name = '许仙';
1

演示：

这里就得到了许仙同学的成绩。

还有一种写法：使用 join 来完成~~

命令：

select * from student join score on student.id = score.student_id and student.name = '许仙';
1

还可以写成下面的命令：

select * from student inner join score on student.id = score.student_id and student.name = '许仙';
1

示例2：查询所有同学的总成绩，及同学的的个人信息。

思路：

1. 要通过student表列出每个同学的姓名。
2. 根据分数表列出总分

1、先计算笛卡尔积~~

命令：

select * from student,score;
1

还是因为合并的笛卡尔积过大，不展示。

2、加上连接条件~~

命令：

select * from student,score where student.id = score.student_id;
1

演示：

3.、加上聚合查询，把同一个同学的行，合并到一个组里，同时计算总分~~

命令：

select name, sum(score.score) from student, score where student.id = score.student_id group by student.name;
1
2

演示：

示例3：查询所有同学的成绩，及同学的个人信息。

思路：

期望查询的结果中，有个人信息、有课程名字、有分数。
个人信息在学生表中查询、课程名字在课程表中查询、分数在分数表中查询。

1、先计算笛卡尔积

命令：

select * from student,score, course;

1
2

还是不做展示。


2、引入链接条件。

注意：三张表需要两个链接条件。

命令：

select * from student, score, course where student.id = score.student_id and course.id = score.course_id;
1

演示：

3、针对需求，进行精简。

命令：

select student.name as 学生名字, course.name as 课程名字, score.score from student, course, score where student .id = score.student_id and course.id = score.course_id;
1

演示：



使用 join on 同样也可以进行三个表的查询~~

命令：

select student.name as 学生名字, course.name as 课程名字, score.score from student join score on student.id = score.student_id join course on score.course_id = course.id;
1

表1和表2 join ，完成之后；再和表3 join。

内外链接的区别：

先创建一张学生表和一张分数表：

此时学生和分数相当与一对一的关系，使用表来表示。

要想知道某个同学的成绩，拿着 id对比一下就可以了。

命令：

select * from student1, score1 where student1.id = score1.student_id;
1

演示：

如果表里的数据有变，情况有可能不同。

此时王五是没有分数的，90分不知道是谁。

这两张表的数据就不再是一一对应的了。



这两种写法都是内连接，如果要是使用外连接结果就不尽相同！！！

3.3 外连接

在 join 前面加个 left 或者 right。

外连接有两种：左外连接和右外连接！！！

左外连接：

student1为左表，score1为右表。

左表连接会把左表的结果尽量列出来。
哪怕在右表中没有对应的记录，就是要 NULL 来填充。

右外连接：

右表连接会把右表的结果尽量列出来。
哪怕在左表中没有对应的记录，就是要 NULL 来填充。

3.4 自连接

自连接就是自己和自己进行笛卡尔积。

自连接不是一个通用的的解决方案，而是一个特殊问题的处理方式。

自连接就是把行转成列。

sql 无法针对行与行之间使用比较条件。！！！
但是有的需求，有要求要行和行之间比较，这是就可以使用自连接，

示例1：显示所有“计算机原理”成绩比“java”成绩高的成绩信息

要比较的是行与行之间的数据，但是sql不能比较行与行之间的数据。

1、先把分数表实现笛卡尔积

错误命令：

select * from score, score;
1

因为表的名字是一样的，所以会有错误。

正确的命令是采取起别名的方法实现的。

正确的命令：

select * from score as s1, score as s2;
1

还是因为笛卡尔积过大，不方便演示。

2、指定链接条件，取出无用的数据。

此处是需要每个同学自己的计算机原理和自己的 java 比较。
因此使用的是 student_id 作为连接条件，保证每行记录，所有列都是针对于同一个同学描述的。

命令：

 select * from score as s1, score as s2 where s1.student_id = s2.student_id;
1

数据行数过多这里就不在展示。

写一个判断条件，来判断计算机原理成绩大于java成绩的信息。


3、分数表中有些记录，是符合左侧是计算机原理，右侧是java 。把这样的行挑出来

命令：

select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1;
1

演示：

4、接下来就是，看着这三行中谁是属于 计算机原理大于java的数据。

命令：

select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
1

演示：

3.5 子查询

子查询本质上就是套娃，把多个 sql 组成了一个。

然而需要注意的是，在实际开发中要慎重使用子查询！！！
因为子查询可能会构造出非常复杂、非常不好理解的 sql 。
对于代码的可读性、很可能是毁灭性的打击。
对于 sql 的执行效率，也很可能是毁灭性的打击。

3.5.1 单行子查询

示例：查询与“不想毕业”同学同班的的同学。

思路：

• 先查询 不想毕业 这个同学的班级 id。
• 再按照班级 id 来查哪些同学和他一个班。

1、查询 不想毕业 同学班级 id 的命令：

select classes_id from student where name = '不想毕业';
1

演示：

2、查哪些同学和他一个班级的命令：

select name from student where classes_id = 1 and name != '不想毕业';
1

演示：

子查询就是把这两步操作合并在一起。

合并的命令：

select name from student where classes_id = (select classes_id from student where name = '不想毕业') and name != '不想毕业';
1

演示：

划线的部分，是把一个查询作为了另一个查询的子条件，也就是套娃。

必须要后面的子查询只返回一条记录，此时才可以写作 = ，否则是不行的。

3.5.2 多行子查询

多行子查询就是返回多行记录的子查询

使用 in 实现多行子查询

示例：查询语文和英语课程的成绩信息。

思路1：

• 先根据课程名字查出课程 id 。
• 再根据课程 id 查出课程 id 。

1、查询课程 id 的命令：

select id from course where name = '语文' or name = '英文';
1

演示：

2、查询课程分数的命令：

select * from score where course_id = 4 or course_id = 6;
1

演示：

思路2：一条命令直接搞定

命令：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');
1

演示：

使用exists实现多行子查询

exists 关键字：可读性比较差。执行效率也大大低于 in 写法，使用这个是解决特殊场景。

查询的结果是在内存中，如果查询结果太大了，内存就放不下，in 就无法使用了。
这是就可以使用exists代替。

实际上更推荐分成多个步骤查询。

3.6 合并查询

合并查询是把两个查询的结果集合并成一个，要求是这两个结果集的列相同，才能合并。

主要使用的关键字：union 和 union all

示例：查询id小于3，或者名字为英文的课程。

命令：

命令：select * from course where id < 3 union select * from course where name = '英文';
1

演示：

使用 union 查询结果可以是来自于不同的表，只要查询的结果的列匹配即可。

union all 和 union 差不多。
union 是会进行去重的（把重复的行只保留一份）
union all 则是可以保留多份，不去重。