【MySQL】查询进阶

二号摸鱼人

于 2024-08-17 17:25:04 发布

阅读量1.1k

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库性能优化

本文链接：https://blog.csdn.net/Starc_/article/details/141275971

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

系列文章目录

第一章数据库基础
第二章数据库基本操作
第三章数据库约束
第四章表的设计

文章目录

系列文章目录
前言
一、聚合查询
二、分组查询
三、多表查询
四、外连接
五、自连接
六、子查询
七、联合查询
总结

前言

对数据库已经有了基本的认识，在数据库增删改查的内容中，查询是其中最核心的操作，接下来讲解的是查询的进阶方法。

一、聚合查询

在之前我们已经学过使用表达式查询，而表达式查询的方式是列与列之间的运算。聚合查询是行与行之间的运算。
以成绩而言，计算某一课程的总成绩就是行与行之间的运算了。同时，SQL也提供了聚合函数，是类似于库函数一样的东西，我们可以通过它进行更简便的运算。但是聚合函数中，遇到数据为NULL的会跳过。
在这里插入图片描述

count-可以计算出行的数量，同时也支持针对某一列进行计算。作为统计行数进行计算当计算总体数量时，并不会关注数据是否为NULL，当针对某一列进行计算时，会关注数据是否为NULL。
sum-就像平时使用的一样，计算总成绩/总分等数据时候使用的。sum并不能像count一样使用通配符*计算，由于sum操作涉及到相加操作，因此只支持数据的计算。
演示如下图所示。
AVG、min、max-即分别计算平均值、最小值、最大值。所使用的聚合函数。与sum的计算方式一样，这边不再赘述。

二、分组查询

分组查询中使用到了group by子句的形式。它指定某个列，针对这个列中相同值的行分到同一组中。之后可以针对每个组分别进行聚合查询。
在下面的职位表中，我们可以根据角色的职位来进行分组。

//1.首先执行select role from emp;
//2.针对role进行group by分组
//3.对分组之后的数据进行count(*) 聚合操作，并返回给客户端
select role,count(*) from emp group by role;

在这里插入图片描述
除了简单的分组聚合之外，group by还可以计算调用avg、sum等聚合函数进行计算。而对于聚合之后的数据，要进行条件判断，需要使用到having子句。

select role,avg(salary) from emp group by role having role != '董事长';

在这里插入图片描述
当然，在分组聚合之前，也可以使用where条件判断将不要的数据剔除。

select role,avg(salary) from emp 
where name != '孙悟空' 
group by role having avg(salary) < 10000;

在这里插入图片描述

三、多表查询

在上面的查询中，针对的都是单表查询。而在工作中，会面对更复杂的场景，多表联合查询是十分常用的一种查询方式。
在多表查询中，**SQL会使用笛卡尔积的方式将所有的数据进行全排列。**当然，这些数据中存在着无效的数据需要我们进行剔除。
这时候就需要我们的条件判断了。
下图中使用笛卡尔积合成的更大表，但是我们知道两个表之间的classId应该是要相等的，而不相等的就属于无效数据，需要提出。
在这里插入图片描述
这时我们根据where条件判断，获取到符合条件的数据。

接下来我们也可以进行数据上的运算，

//1.首先查询的数据来自于哪些表，接着使用where查询将无效的数据剔除，最后返回结果。
select student.name,chinese+math+english as total 
from student,exam_result 
where student.name = exam_result.name
and student.name = '孙悟空';

在这里插入图片描述
除了使用where条件判断，多表查询还可以使用join on的连接方式进行查询。
表A JOIN 表B ON 条件

select * from student 
JOIN exam_result on student.name = exam_result.name;

假设现在有三张表：学生表、课程表和成绩表。需要查询每个学生每个科目的成绩，这就涉及到了三表查询的内容。
我们可以使用where判断条件来编写

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

我们也可以使用join on的方式，会更具有可读性
select student.name,course.name,score.score from student join score on student.id = score.student_id join course on course.id = score.course_id;

四、外连接

在前面的查询方式中，我们称之为内连接，可以使用where条件/join on的方式查询数据。而外连接和内连接一样，都是基于笛卡尔积的方式进行计算的。但是对于空值/不存在的值，它的处理方式是不同的。
对于外连接的查询，只能使用join on的方式进行查询。

在两个表中，一一对应的数据使用外连接和内连接的结果是一样的。
以下面的SQL为例，在学生表中id与成绩表中的id一一对应。
外连接可以选择左外连接和右外连接两种方式。
当数据并非一一对应的情况下，所产生的效果会因为外连接中连接方式的选择而放生改变。我们可以看下面
当student left join score时，会以student表为基准，将所有student的信息列表出来，而score没有与之对应的数据则置空。
当student right join score时，就以score表为基准，所有的score信息列表出来，而在student表中没有与之对应的信息则置空。

五、自连接

自连接即自己与自己计算笛卡尔积，使用的场景较少。
以下面的sql来看，当score表直接进行自连接的时候，MySQL是不允许这样的写法的，我们需要给表起别名。
在这里插入图片描述

在自连接的时候最初的笛卡尔积大部分是我们不需要的，需要在后面加上条件判断才能得到最后的结果。
通过自连接将行与行之间的关系转换成列和列之间的关系，才是自连接常用的地方。