MySQL —— 聚合查询，分组查询 与 联合查询

聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

函数	说明
count()	统计数据总数
sum()	求和
avg()	求平均值
max()	求最大值
min()	求最小值

注意凡是涉及运算的，数据库会自动掉 NULL 值
注意NULL 是不参与比较 max 与 min 的

解析：

以此表为例

---

count()

count()，会统计数据总数

使用 count(*) 会查询一共有多少条数据行

使用 count(列名)，会统计该列有多少行数据

如果列中有NULL 值，则不会被统计在内

建议使用 count(*) 来统计数据行，这是SQL 标准提出的。

---

sum()

如果运算中有NULL 值会自动过滤NULL，因为NULL 经过运算后为 NULL这个数据是没有意义的，所以数据库的开发者们进行了这样的运算设计。

如果运算遇到非数字型数据，则无法进行运算，会报警告：

注意可以使用表达式，但是如果想分别求每一列的总分还是要分开写的。

---

avg()

---

max() 与 min()

注意NULL 是不参与比较的

实践

1.统计班级共有多少同学

select count(name) from exam;

2.统计班级收集的 math 数学成绩数据 有多少个

select count(math) from exam;

3.统计数学成绩总分

select sum(math) from exam;

4.统计所有数学成绩不及格 （< 60） 的同学的数学总分

select sum(math) from exam where math < 60;

5.统计三科的平均总分

select avg(chinese + math + english) 三科平均分 from exam;

6.返回英语最高分

select max(english) from exam;

7.返回 > 70 分以上的数学最低分

select min(math) from exam where math > 70;

分组查询

group by

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

select column1, sum(column2), .. from table group by column1,column3;

演示表：

计算每种职位的平均工资：

这里的执行顺序是先分组再计算。

---

拓展 round

可以使用 round(数值,小数点后的位数) 来指定数值的形式：
group by 后面可以跟 order by 子句

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

select role 职位, max(salary) 最高工资, min(salary) 最低工资 from emp group by 职位;

having

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用
HAVING

where 是对表中每一行的真实数据进行过滤的
having 是在 group by 之后，对计算结果进行过滤的。
所以两个执行顺序是不一样的，having 可以使用别名来过滤

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

select role 职位, avg(salary) 平均工资 from emp group by 职位 having 平均工资 < 1500;

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

笛卡尔积实际上就是对数据进行全排列，举个例子，有两张表，其中一张表的一条数据要和另一张表的所有的数据进行组合：

---

我们也可以通过 SQL 代码来查看笛卡尔积：select * from table_name1, table_name2;
![在这

通过观察我们得知上面全排列的数据不全是正确的，那我们如果过滤掉这些无效的数据，从而获取正确的数据？
请看下面揭晓

---

内连接

语法格式：select 字段 from 表1 别名1, 表2 别名2 where 条件; 或者 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 条件;

两个表之间存在主外键关系的话，只需要判断这两个表中主外键字段是否相同即可。

查询列表的字段 可以使用 表名.列名

我们可以通过给表名取字段的方式来减少我们的书写量。

演示：

select s.student_id, s.sn, s.name, s.mail, c.name from student s, class c where s.class_id = c.class_id;

---

select s.student_id, s.sn, s.name, s.mail, c.name from student s inner join class c on s.class_id = c.class_id;

---

当你给表取了别名之后，那就将表名的地方全部替换成别名，否则 where 子句会识别不出。

---

联合查询的步骤：
首先确定查询中涉及哪些表，然后对这些表取笛卡尔积，再确定连接条件与过滤条件，最后简化语句（使用别名）

实践:

查询白素贞的成绩：

首先确定需要哪些表：学生表和成绩表，取笛卡尔积：

然后确定连接条件：student_id 是相同的

确定过滤条件：姓名是白素贞

简化 sql 语句，将student 取 stu , score 取 sco

select stu.name, sco.score from student stu, score sco where name = '白素贞' and stu.student_id = sco.student_id;

select stu.name, sco.score from student stu join score sco on name = '白素贞' and stu.student_id = sco.student_id;

---

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

首先确定需要什么表：学生表，成绩表；然后取笛卡尔积：

然后确定连接条件与过滤条件：首先是由于需要的是总成绩，所以要使用聚合函数 sum()，那么就要使用到 分组查询 group by 子句，接着成绩表和学生表的连接是 student_id 要相同

这里要注意分组的依据，我们是对成绩表进行分组的，成绩表有学生的 id 和 成绩，那就应该是要按学生的 id 作为分组的依据。

最后简化 sql 语句 将student 取 stu , score 取 sco

select stu.name, stu.mail, sum(sco.score) from student stu, score sco where stu.student_id = sco.student_id group by sco.student_id;

---

查询所有同学的总成绩，及同学的个人信息 以及 学生所在的班级信息：

首先确定要几张表：学生表，班级表 以及 成绩表，然后取笛卡尔积：

然后确定连接条件与过滤条件：学生表和班级表的联系是 class_id 相同，学生表和成绩表的联系是 student_id 相同，总成绩就和上面的方式一样使用 sum() 通过 student_id 来进行分组。

然后简化 sql 语句：

select stu.sn 学号, stu.name 姓名, stu.mail 邮箱, sum(sco.score) 总成绩, c.name from student stu, score sco, class c where stu.student_id = sco.student_id and stu.class_id
= c.class_id group by sco.student_id;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示就是左外连接；右侧的表完全显示就是右外连接。

语法：左外连接： select 字段名 from 表名1 left join 表名2 on 连接条件; 与 右外连接： select 字段 from 表名1 right join 表名2 on 连接条件;

大家来看一下下面两张表，你会发现 3班 是没有学生的。

现在我们基于上述的式子，演示左外连接：select * from class c left join student s on c.class_id = s.class_id;

即使 3 班是没有同学的，但是3班这个字段还是会显示出来，只是对应的学生列表为空。

---

现在我们插入一个没有班级的学生数据：

然后我们来演示右外连接：select * from class c right join student s on c.class_id = s.class_id; 这里会将 student 表全部显示，即使有学生没有班级这个数据。

进行外连接如果遇到没有数据的时候，数据库会使用 NULL 填充。

自连接

自连接是指在同一张表连接自身进行查询。

语法：select * from 表名1 别名1, 表名1 别名2;

注意一定要起别名，不然MySQL 无法识别：

一般自连接会用在自己要和自己比较的时候

演示:
查询哪些学生的 Java 成绩 比 计算机原理要低：可以先查出Java 和 计算机原理的 course_id
select * from score s1, score s2 where s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;

子查询

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

单行子查询：
基本语法：select * from table_name where 列名 = (select 列名 from table_name where 条件);

查询与“白素贞” 同学的同班同学：select * from student where class_id = (select class_id from student where name = '白素贞');

---

多行子查询：

[NOT] IN

in 之前提到过就是在不在 in 括号的字段范围内
语法：select * from table_name1 where 列名 [not] in (select * from table_name2);

举例：查询 Java 和 计算机的成绩
select * from score where course_id in (select course_id from course where name = 'Java' or name = '计算机原理');

[NOT] EXISTS

exists 表示存在，如果 exists 后面括号中的查询语句，如果返回的是空结果集，那就类似flase ，不会执行外层的查询，如果返回的是 true ,就会执行外层的查询。

语法：select * from table_name where [not] exists (select * from table_name);

注意如果集合是 select null; 集合不为空（empty），只是集合内容是 null

演示：

合并查询

合并查询可以将多个结果集合并。

使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

union 【会去重】

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

union all 【不会去重】

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

查询id小于3，或者名字为“英文”的课程：

在单表查询时，推荐使用 or， 多表查询时可以使用 union 或者 union all