MySQL数据基础知识整理—3

聚合函数

我们先来看下定义：

       在数据库中，聚合函数是指能够对一组数据进行计算并返回一个单一值的函数，这个单一值通常是对这组数据的总体统计结果。

       简单来说，就是数据库提供给用户的一种常用函数，其中包括和，平均值，最大值，最小值等。下面我也会给出几个比较常用的聚合函数使用指令。

我们先来看下本次示例所演示的表

select count(id) from students;//计算出id有多少条结果，这里需要注意：count会忽略空值进行统计
select sum(id) from students;//计算id具体数值的和
select avg(id) from students;//计算id具体值的平均值，这里可以计算到小数点后4位
select max(sn) from students;//查看sn具体值的最大值
select min(sn) from students;//查看sn具体值的最小值

以上都是比较常用的聚合函数，都比较简单，这里我就不给出图片示例了，大家下去可以自己尝试下。

分组查询

首先我们来看下定义：

       分组查询是一种在数据库中检索数据的方法，它将数据按照指定的列进行分组，并对每个分组进行聚合计算，最终返回每个分组的聚合结果。关键字位：group by

       我们进行分组查询的本质并不是为了查询，而是为了对数据进行统计，将多张表中的数据结合到一张表中显示。以表中指定字段对数据库中数据进行分组，然后进行数据统计。

这里我们给出示例，还是以上表为例，在学生表中，选出每个班的最高成绩：

select max(sn) from students group by classes_id;//前者为选出条件，
//后者为分组条件；若选择条件不明确，则直接给出分组后的第一条数据

若分组后的条件还需要过滤，则我们需要使用having进行二次筛选，指令为：

select max(sn) from students group by classes_id having max(sn)>15;
//既要选出每个班的最大值，又要选出最大值大于15的结果，只有一条

以上就是分组查询的基本操作，其次我们还需要给出以下注意事项：

1. 分组字段：要确保使用的分组字段是正确的，以便将结果正确地分组。
2. 聚合函数：在select子句中使用的聚合函数必须与group by子句中指定的分组字段相匹配。
3. 过滤条件：可以在分组查询中使用where子句来过滤数据，但必须在group by子句之前执行过滤操作，之后就只能使用having进行二次过滤了
4. 空值处理：对于包含null值的分组字段，需要使用is null或is not null进行处理，以便正确分组。

多表联查

接下来是数据库学习中比较有难度的多表联查，同样的我们先来看下定义：

       多表联查是指在关系型数据库中，通过使用 SQL 语句将多个表合并起来，从而实现数据的查询和分析。多表联查通常是在涉及到多个表之间有关联关系的情况下使用。

在进行合并多张表数据合并时，我们一般采取：笛卡尔积的方式进行合并。

如下图所示：

       简单来说，笛卡尔积法就是将表A的每一项与表B的每一项集合起来，得到组合表。我们不难看出，该方法组合的表，在查询过程中效率会非常低，因为我们需要过滤的数据太多了。对于现在几个数据我们可能没有很深的体会，但是如果是100w条数据的表格和100w条数据的表格进行合并，生成的新表格可就有1w亿条数据需要进行过滤。因此我们在使用该方法时最好是在合并时就进行数据的筛选，设定好过滤条件。

       但是由于我们设置了合并条件后，会存在某张表中不存在符合条件的数据，导致表格无法合并，因此，基于这种情况，我们将表的连接分成三种不同形式：

1. 内连接
2. 外连接（左连接，右连接）
3. 自连接

内连接

       内连接（Inner Join）是一种关系型数据库中常用的数据连接方式，它通过比较两个表中的数据，把符合条件的数据行组合起来形成一个新的表。内连接的主要特点是仅保留两个表中都存在的数据行，即满足连接条件的数据行。内连接需要指定连接条件，通常使用等值连接（即两个表中某个列的值相等）。

简单来说，就是将两张表中符合条件的数据进行连接，不符合条件的数据舍弃。举个例子：

输入指令：

//关键字：inner join ... on ...
select*from A inner join B on A.id=B.id;

得到表格：

该命令中，inner 就是内连接的关键字，on后面就是连接的条件

外连接

左连接

左连接：以左表作为基表，在右表中寻找符合条件的数据进行连接，如果没找到，则左边数据连接null。关键字：left

输入指令：

select*from A left join B on A.id=B.id;

右连接

右连接：与左连接类似，是以右表作为基表，在左表中寻找符合条件的数据进行连接，如果没有找到，则右表数据连接null。关键字：right

输入指令：

select*from A right join B on A.id=B.id;

接下来我们来看下外连接的混合实例，我直接给出创建好的表和数据：

以上为四个表，分别是单科成绩表，班级科目信息，学生信息表和科目表。

1.假设我们需要查看刘备的各科成绩，只需要输入指令：

select score from score inner join students on score.student_id=students.id where students.name='刘备';
//解释：成绩表内连接学生表，每个学生的id与成绩表中的id对应，学生id筛选为刘备的id，打印出表格

2.查看刘备的各科成绩，需要显示出姓名，课程名称和课程成绩，需要输入指令：

select students.name,course.name,score.score from students//写出我们需要显示的数据
inner join score on students.id=score.student_id//内连接单科成绩，满足特定条件
inner join course on score.course_id=course.id//内连接单科成绩，满足特定条件
where students.name='刘备';//条件就是姓名为刘备，确定

这样我们就可以得到刘备的全部信息：

3.查询所有学生的全部学生信息，以单科显示：

select students.*,score.score from students left join score on score.student_id=students.id;
//解释：这里的.*就代表显示所有学生表信息

这样我们就能得到所有学生的信息和他们的成绩信息：

4.查询所有学生的总成绩，及学生的个人信息并以学生id为分组，使用指令：

select students.*,sum(score.score) from students//这里用到了聚合函数sum，合并了查询后的单科成绩
inner join score on score.student_id=students.id//寻找对应id的成绩
group by students.id;//以学生id为分组，也是为了防止同名情况

得到以下查询结果：

自连接

自连接：用一张表自己连接自己，也可以理解为以任意表为桥梁，定位一张表中的两个数据，然后显示指定数据。下面给出示例：

要比较同一个人的两科成绩，例如C++和Java，需要将两科成绩放在同一张表上，指令如下：

select score1.score,score2.score from score as score1 
inner join course as course1 on score1.course_id=course1.id and course1.name ='C++'
inner join score as score2 on score1.student_id=score2.student_id and score1.score<score2.score
inner join course as course2 on score2.course_id=course2.id and course2.name='Java';

得到查询结果如下：

子查询

直接给出定义：

       子查询是指在一个查询语句中嵌套另一个完整的SELECT语句，用于获取一组满足某种条件的数据。子查询可以出现在SELECT、FROM、WHERE、HAVING等语句中，可以用于过滤、排序、分组、计算、联合等操作。子查询的结果集通常作为外层查询的条件或者比较值使用。

定义都是比较抽象的，下面我们来从测试用例中体会什么是子查询

1.查询与刘备同班的同学，指令如下：

select*from students where classes_id=
(select classes_id from students where name='刘备');
//解释：这其实就是将两个语句结合到一起了，先是查询出刘备的classes_id
//然后再让该结果等于students表中classes_id，最终得到符合条件的学生信息

得到最终结果：

这里我们需要注意，当子查询语句只有一条结果时，我们可以使用=进行判断，多条则不可以使用该技巧。

2.查询C++和Java课程的成绩信息，指令如下：

select*from score where score.course_id in 
(select id from course where course.name='C++'or course.name='Java');
//解释：这里我们一定要注意，在有或的情况，我们需要使用in选出条件集合中的任意条件满足

查询出以下结果：

注意：这里我们需要了解in的查询流程和exists的查询流程

in的查询流程：

1. 先执行子语句，得到查询结果，放入内存中
2. 执行主语句，拿着全部数据与内存中的查询结果进行判断，如果在查询结果中，则说明符合条件，将数据输出。

exists的查询流程：

1. 执行主语句，获取全部数据
2. 拿着主语句中的数据，作为子语句的查询条件，进行子语句查询。如果子语句中有查询结果，则表明主语句中该语句符合条件；若没有查询结果，则表明不符合条件，舍弃。

       因此in和exists的使用是分场景的，当主语句查询结果较多，子语句较少时，我们使用in去进行筛选；当主语句查询结果较少，子语句查询结果较多时，我们使用exists进行筛选。

3.查询所有比数学与应用数学班平均分高的成绩信息，指令如下：

select*from score where score >//这里是选出成绩大于某个条件的语句
//下面全是条件
(select avg(score) from score//选出平均分
inner join students on score.student_id=students.id//成绩id与学生id关联
inner join classes on students.classes_id=classes.id and classes.name='数学与应用数学');//学生的班级id必须是数学与应用数学

合并查询

这个就比较好理解了，就是满足某个条件的结果合并到一起组成表格。其中要求查询结果的字段保持一致才能进行合并。关键字：or，union

这里or关键字我在子查询处已经讲过了，这里就不过多赘述了。接下来，我们来看看union关键字的用法，还是以示例说明：

查询id<6并且名字是C++的课程，指令如下：

select*from course where name='C++' 
union 
select*from course where id<6;

得出结果：

union关键字，其实就是将两个语句联合起来输出。这里还有一个技巧，如果我们使用union all关键字的话，就没有去重的效果。

其实，我们可以发现or和union的功能几乎是一样的，但是为什么还需要union呢？

       因为在执行or关键字查询语句时会忽略索引，这样会大大降低查询效率；而使用unoin关键字，虽然执行多条select语句也会降低性能，但是查询到效率还是可以得到保障的。

以上就是本次的全部内容了，比较难的还是在多表联查这块，比较绕，但是多敲敲代码就好多了，感谢支持！！