【MySQL 数据库 进阶】 面试考题两大查询 （聚合查询，联合查询）

MySQL进阶

一、聚合查询

聚合查询用到的函数和用法跟Excel的用法很像，如果你Excel玩的NB，那你基本上联合查询，就很轻松了。😁😁🤗🤗

聚合查询是跟行相关的，把查询结果按照行的维度进行合并，就是把多行合并起来。

1.1聚合函数

聚合查询也提供了很多的聚合函数，可以理解成SQL给我们提供的“库函数”。

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

• COUNT

-- 查询当前表有多少行
select count(*) from 表明;

-- 如果属性是null，那么这条属性不计数
select count(属性) from 表明;

• SUM

-- 计算数学总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

• AVG

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

• MAX

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

• MIN

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

1.2 group by

上面的聚合函数是把所有的行都合并起来，那么group by就是用来进行分组聚合，每一个组再进行聚合函数来计算或统计。

案例：

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

• 通过group by来进行分组查询
• 通过 role这个字段来分组

select role from teachers group by role;

运行结果：

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

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

运行结果：

1.3Having

group by分组过后的数据，需要用Having来进行条件的筛选，where是分组之前进行的条件筛选。

📜案列：

查询每个角色的最高工资> 4500

select role,max(salary) from teachers group by role having max(salary) >4500;

运行结果：

---

聚合查询相对来说是比较好理解的，Excel用的熟练那么你的聚合查询也差不到哪去，主要注意里面的group by。

---

二、联合查询

2.1笛卡尔积

联合查询也称作为多表查询，多表查询是对多张表的数据取笛卡尔积。意思就是把两个表的结果进行一个排列组合。 笛卡尔积也是在联合查询中一个核心概念。

什么是笛卡尔积？？？

笛卡尔积就是把两张表产生的所有可能的结果都列出来，形参新的一张表。

• 这张表的列数，是两张表的列数之和
• 这张表的行数，是两张表的行书之积

笛卡尔积会列出很多种可能所以里面就很存在不科学的数据，我们需要过滤掉这些不科学的数据就会加上连接条件来过滤这些多余的数据，留下这些合理的数据。

那些是合理的数据尼？？？

所谓合理的数据就是两张表里面会有对应相等的数据

知道了笛卡尔积就可以来练习了

假设我创建了一个数据库，里面有 classes， course， score， student；

• student 和classes这件事一堆多的关系
• student和course之间是多对多的关系，多对多关系时就会有一个中间表score，来连接这两个表实体之间的关系

student表：

2.2 内连接

📜假设我们查询“白素贞”同学的成绩

语法：

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

先用第二种语法演示：

select * from student ,score; -- 笛卡尔积查询所有情况 

select * from student ,score where student.id =score.student_id;-- 设置条件过滤不合理的数据

已经把所有同学的信息已经列出来了，下面只需要加一个条件name=“白素贞”就行了

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

这样我们就查找出了白素贞同学的成绩，为了精简点，我们可以再加上一些条件

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

---

当然你熟练了亦可以一步到位：

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

但是你这样写会看起来比较复杂，难以阅读难以理解，并且运行效率低，因为链表查询本质就是进行笛卡尔积操作，如果是四五张表那么计算出来的数据会非常大，浪费开销，所以联合查询在实际工作中，因为数据量可能比较大，所以要慎重使用，但是在面试/笔试中，是经常考的东西，

另外一种语法：

select * from student inner join score on student.id=score.student_id;
//既进行了笛卡尔积，也进行了条件判断

上面两者语法都是可以的，都能表示内连接，但是有的时候就会有左外连接和右外连接，这种就需要用join on的形式了。

2.3 外连接

外连接分为左外连接和右外连接 ，他们也是对两个表做笛卡尔积操作，当这两个表里存在一些序数据没有对应关系，那他们的表示形式就会有差别。

语法：

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

2.3 自连接

自连接，一张表里面自己和自己比较，也是首先进行笛卡尔积，设置条件，让他奇淫巧技，使行转换成列。让列与列之间比较。

📜案例：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息 ，

select * from score s1,score s2;-- 进行笛卡尔积

select * from score s1,score s2 where s1.student_id=s2.student_id;
-- 进行条件筛选让他成为列与列之间比较

select * from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3; -- 让查询列之间精简

select * from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3 and s1.score <s2.score; -- 得到最后的结果

 select s1.student_id, s1.score as java, s2.score as '计算机原理' from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3 and s1.score <s2.score; -- 更精简

2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询 。就是把多条SQL语句合在一起的就是子查询，

📜案列：

查询与“某某某” 同学的同班同学

正常情况下：

1.先查询“某某某”的班级id

2.根据查到的班级id再次查找相同班级id的同学

 select classes_id from student where name='某某某'; -- 假设查询结果班级id为1
 
 select student.name from student where classes_id=1;

但是子查询是一条语句完成，所有我们把第一条语句嵌入进第二条语句中就行。

select student.name from student where classes_id=(select classes_id from student where name='某某某' );

这就是子查询。

子查询中使用 IN 来进行多行子查询：

📜案列：

查询“语文”或“英文”课程的成绩信息

正常情况下：

1.查询课程id。

2.根据课程id查询分数表里面的成绩详情。

select id from course where name='语文' or name ='英文'; -- 假设 课程id是（4，6）
select * from score where course_id in (4,6);

子查询代码：

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

子查询中使用 exists 来进行多好子查询：

select * from score where exists (select score.course_id from course where (name='语文' or name ='英文') and course.id=score.course_id);

不建议使用，代码复杂难懂，效率低，但是要掌握，面试要考。

2.5 合并查询

把多个结果集合，合并成一个，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致

📜案例：

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

不用合并查询写：

select * from course where id <3 or name='英文';

用合并查询写：

select * from course where id <3 union select * from course where name ='英文';

union 和union all 的区别

union自动去重复行，union all不能去重。

---

这里面重点掌握联合查询里的 内连接和笛卡尔积 ，因为这是笔试和面试中经常考的题，因为SQL比较简单所以不会问道SQL语句，而是问一些数据库的底层，下一篇帖子是关于数据库底层的一些原理，关于索引和事务的，也是重点掌握的知识。

铁汁们，觉得笔者写的不错的可以点个赞哟❤🧡💛💚💙💜🤎🖤🤍💟，收藏关注呗，你们支持就是我写博客最大的动力