Mysql(六) --- 聚合函数，分组和联合查询

---

前言

上一篇博客中，我们学习了数据库的设计，下来我们就要根据多种表来进行联合查询

---

1.聚合函数

在将联合查询之前，我们先讲两个内容，一个是聚合函数，第二个是GROUP BY 分组查询

1.1.常用的函数

函数	说明
COUNT([DISTINCT]expr)	返回查询数据的数量
SUM([DISTINCT]) expr	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回传销到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

1.2.COUNT()

count()函数用于查询数据的数量
例如查询exam表中的数据

select count(*) from exam;

其中也可以写成

select count(1) from exam;

推荐使用 第一种 方法
当然count() 括号也可以写列名
例如

select count(id) from exam;

如果列中出现 NULL，在进行查询数量，会出现什么情况呢？
首先先添加一组数据

insert into exam values (8,'测试用户',86,98,null);

在进行对id进行查询数据数量

select count(id) from exam;

如果是对English进行查询的数据数量

select count(english) from exam;

我们发现两个结果是不一样的，因此如果列中有NULL值，那么就不会被统计在内

1.3.SUM()

sum(列名) 将所有行中指定列进行相加
例如：计算所有学生的语文成绩总和

select sum(Chinese) from exam;

之前我们发现，null跟任意的值相互运算，返回值都是null
我们使用sum()函数来演示一下。
例如，计算所有学生的英语成绩总和

select sum(English) from exam;

由此发现，在sum()求和时，NULL不参与计算。

提醒：如果以后我们做一些公共接口的开发，一定要考虑到用户的真实意图是什么，有时要把特殊的值做特殊处理

如果sum()，传入的参数是非数值的列进行运算，就会得到一些警告信息。
例如，

查询警告信息

show warnings;

1.4.AVG()

avg() 对所有行的指定列进行求平均值运算
例如：
1.对所有同学的语文成绩求平均值

select avg(chinese) from exam;

2. 求语文，数学，英语三门课的 总分平均值

select avg(chinese+math+english) from exam;

1.5.MIN()、MAX()

min(),max() 分别求出所有行中指定列的最小最大值。
例如
1.找出语文成绩的最高分和英语成绩的最低分

select max(chinese),min(english) from exam;

2。查出语文成绩的最高分和最低分

select max(chinese) 语文成绩最高分,min(Chinese) 语文成绩最低分 from exam;

---

2.GROUP BY 分组查询

SELECT中使用 GROUP BY子句可以对指定列进行分组查询。需满足：使用GROUP BY 进行分组查询时。SELECT 指定的字段必须是 “分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中

2.1.语法

select colunm1, sum(column2),… from table group by column1,colunm2;

2.2.示例

计算不同角色工资的平均值

select role,avg(salary) from emp group by role;

其中可以使用round(数值,小数点位数)来精确小数值的位数
例如
计算不同角色工资的平均值，并且保留小数点后两位

select role,round(avg(salary),2) from emp group by role;

当然了使用GROUP BY 之后，也可以添加别的子句。
例如
计算不同角色工资的平均值，并且保留小数点后两位，并且按照平局工资升序排列

select role,round(avg(salary),2) 平均工资 from emp group by role order by 平均工资 asc;

2.3.HAVING 子句

问题：对分组之后的结果进行过滤，比如说，找出平均工资大于1万，小于20万的角色
使用GROUP BY 对结果进行分组处理之后，对分组的结果进行过滤时，不能使用 WHERE 子句，而要
使用 HAVING 子句

解决一下上面的问题

select role,avg(salary) from emp group by role having avg(salary) > 10000 and avg(salary) < 200000;

我们在写几个对上面的操作进行巩固。
1.查询每个角色的最高工资，最低工资和平均工资
第一步：按照角色分组
第二步：使用相应的聚合函数

select role,max(salary),min(salary),avg(salary) from emp group by role;

2.显示平均工资低于1500的角色和他的平均工资
第一步：按照角色分组
第二步：使用相应的聚合函数
第三步：使用having子句对分组的结果进行过滤

select role,avg(salary) from emp group by role having avg(salary) < 1500;

---

3.联合查询

3.1.为什么要进行联合查询

根据上一篇博客中，我们知道，设计中数据时会把表进行拆分，为了消除表中的字段之间的依赖关系，比如部分的函数依赖以及传递依赖，这时会导致一条SQL语句查出来的数据是不完整的，这是我们就可以使用联合查询把存在关系中的数据全部查询出来，在一个数据行中显示详细信息

3.2.那么是如何进行联合查询的

参与查询的所有表取笛卡尔积，结果集在临时表中
观察哪些记录是有效数据，根据两个表的关联关系过滤掉无效数据

3.3.示例：一个完整的联合查询的过程

查询每个人的姓名和班级信息。
1.读取两张表的笛卡尔积。

select * from class,student;

那么如何过滤掉无效的数据呢？
2.通过连接过滤掉无效的数据

select * from student,class where class_id = class_id;

这样写是不行的，因为这样的话，不知道class_id是属于那张表的
但是可以根据表名.列名的方法来解决上述问题

select * from student,class where student.class_id = class.class_id;

此时就已经解决问题了。
因此，联合查询的步骤：
1,首先确定哪几张表要参与查询
2.再根据表与表之间的主外键关系，确定过滤条件
3.精简查询字段，得到想要的结果

3.4.内连接

语法：

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

根据上面的联合查询的步骤，完成下面的任务。
示例:
1.查询"许仙"同学的成绩
第一步,首先确定哪几张表要参与查询,然后建立笛卡尔积

select * from score,student;

注：返回的结果集太多，只截取了一些

第二步.再根据表与表之间的主外键关系，确定过滤条件

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

确定结果集的过滤条件

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

第三步.精简查询字段，得到想要的结果

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

联合查询步骤细化：
(1). 确定查询中涉及到哪些表
(2). 对目标表取笛卡尔积
(3). 确定连接条件
(4). 确定对整个结果集的过滤条件
(5). 精简查询字段

2.查询所有同学的总成绩，以及同学的个人消息
按照上述的步骤来即可，在这只显示最后的结果：

3.5.外连接

外连接分为左外连接、右外连接和全外连接三种类型，MySQL不支持全外连接。

• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显示为NULL。

语法：

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

示例：
1.查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员
根据上述的要求，我们发现最后返回的主要是雇员的信息，
因此使用左外连接

select e.last_name,d.department_name from employees e left join departments d on e.department_id = d.department_id;

2.查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门
根据上述的要求，我们发现最后返回的主要是部门，
因此使用右外连接

select e.last_name,d.department_name from employees e right join departments d on e.department_id = d.department_id;

3.查询哪位同学没有考试成绩

SELECT * from student st left join score sc on st.student_id = sc.student_id where sc.score_id is null;

3.6.自连接

3.6.1 应用场景

自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名。

3.6.2 示例

显示所有 “计算机原理” 成绩比 “Java” 成绩高的成绩信息
第一步：确定涉及到的表 — 课程表 和 成绩表
第二步：取出笛卡尔积

发现数据太多，因此都要加入条件了
连接条件是student_id必须相同

观察上面的结果集，需要找到那些是我们要找的结果，从而确定过滤条件

3.7.子查询

子查询是把⼀个SELECT语句的结果当做别一个SELECT语句的条件，也叫嵌套查询。

3.7.1.语法

select * from table1 where col_name1 {= | IN} (
select col_name1 from table2 where col_name2 {= | IN} [(
select …)
] …
)

3.7.2.单行子查询

嵌套的查询中只返回一行数据
示例：查询与"不想毕业"同学的同班同学
第一步：确定参与查询的表 — 学生表

select * from student;

第二步：先查出不想毕业这位同学的班级编号

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

第三步：在学生表中查出与"不想毕业"的同班同学，条件就是与"不想毕业"这条记录中class_id相同的所有学生信息

第四步：建立联合查询

select * from student where class_id = (select class_id from student where name = '不想毕业');

其中还没有完，我们要查询与"不想毕业"同学的同班同学，其中应该是不包含“不想毕业”的同学的信息(自己的理解)，因此再加上一个约束条件。

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

3.7.3.多行子查询

多行子查询：返回多行记录的子查询

select * from table1 where table.id in (select id from table2 where xxx =);

示例：查询"语文"或"英文"课程的成绩信息
第一步：涉及哪些表
成绩表和课程表

select * from course;

select * from score;

第二步：在课程表中获取"语文"和"英文"的课程编号

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

第三步：根据获得到的课程id，在成绩表中查询对应课程的分数

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

第四步：进行联合查询

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

3.7.4.多列子查询

多列子查询：单行子查询和多行子查询都只返回一列数据，多列子查询可以返回多个列的数据，外层查询与嵌套的内存查询的列要匹配
例如：查询重复录入的分数
首先，向score表中插入重复数据

insert into score(score, student_id, course_id) values (70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);

其次就是，将同一位学生，同一门课程，同样的成绩，按照这三个列同时去分组

其次，分组之后再having子句中，用count(*) 判断分组中的记录数

最后进行联合查询

select * from score where (student_id,course_id,score) in (select student_id,course_id,score from score group by student_id,course_id,score having count(*) >1); 

3.7.5.exists关键字

语法：

select * from 表名 where exists (select * from 表名1);

exists后面括号中的查询语句，如果有结果返回，则执行外层的查询，如果返回的是一个空结果集，则不再执行外层的查询

select * from student where student_id = 1;

和

select * from student where exists (select * from student where student_id = 1);

例如：
1.内层查询返回非空结果集

2. 内层查询返回空结果集

select * from student where student_id = 100;

和

select * from student where exists (select * from student where student_id = 100);

3.7.6.在from子句中使用子查询

当⼀个查询产生结果时，MySQL自动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回
给用户，在from子句中也可以使用临时表进行子查询或表连接操作。

例如：
查询所有比"中文系2019级3班"平均分高的成绩信息。

1. 确定涉及到的表 ---- 班级表，成绩表，分数表

2. 再算出"中文系2019级3班"的平均分

select avg(sc.score) from score sc,student st, class cls where cls.class_id = st.class_id
and st.student_id = sc.student_id and cls.name = '中文系2019级3班';

3. 最后用表中学生的真实成绩和以上平均分作比较

select * from score sc,
(select avg(sc.score) score from sc,student st,class cls 
where cls.class_id = st.class_id
and st.student_id = sc.student_id
and c.name = '中文系2019级3班') tmp
where sc.score > tmp.score;

3.8.合并查询

在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符union，union all
先做一下准备工作：

1.创建一个跟student一样结构的表

create table student2 like student;

2. 插入数据

insert into student2 values (1,'00982','李鬼','xuanfeng@qq.com',1),(2,'00001','测试用户',NULL,1),(3,'00391','白素贞',NULL,1),(4,'00031','许仙','xuxian@qq.com');

3.8.1.Union

union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

select * from student union select * from student2;

3.8.2.Union all

union all：该操作符用于取得两个结果集的并集。使用该操作符时，不会去掉结果集中的重复项

select * from student union all select * from student2;

---

完