Mysql数据库--聚合查询、分组查询、联合查询（不同的连接方式）

1.查询的进阶版

1.1查询搭配插入进行使用

我们首先创建两张表，一个叫做student,一个叫做student2,两个表都是只有这个int和varchar 的数据类型，这个时候，我们向这个第一个表里面直接使用这个insert插入数据，但是这个时候student2这个表还是空的；

我们查询这个student这个表里面插入的内容：

把这个查询的结果直接插入到我们的这个student2这个表里面去：这个实际上就是把两个步骤合成了一步，但是因为这个是查询结果的插入，所以就可以直接把两个语句合并了，查询发现这个是成功的；

但是上面的这个查询结果的直接插入显然是有局限性的，就是两个表的结构必须是一样的，否则这个插入注定无法成功，我们的两个表都是只有这个id和name两列，因此这个是可以把我们查询到的结果直接插入的；

1.2聚合查询

聚合查询的这个里面主要就是我们的这个sum,count,min,max等等之类的这个函数的用法添加到了我们原来的这个语句里面去罢了，下面我们会依次去演示这个例如count,sum,min,max的用法；

首先还是要创建一张表，然后向这个表里面插入一些数据，例如下面的这个所示，我下面的这个演示也是基于下面的这个表进行的：

下面的这个就是聚合查询里面的这个count的使用，我们可以使用这个count计算这个数据的行数，虽然我们通过这个select好像是可以看到这个数据的行数的，但是这个是客户端，当我们在这个服务器里面通过代码来操作的时候，我们是看不到这个信息的，因此这个count还是很有必要的；

我们不仅可以使用这个通配符（表示所有）作为我们的查询的内容，我们也可以使用这个name进行指定，反正我们这个表里面无论使用哪一个索引，查询出来的这个结果都是一样的；

但是如果哦我们的这个表里面有些数据是空的，这个时候我们再次使用这个select进行这个count计数的时候，这个null也是会被算上的，我们可以insert进去一行全部都是空的数据尝试一下，但是我们使用这个name进行统计的时候，这个null就不会被统计进去；

当然，在上面的这个基础之上，我们也可以添加上前面的这个约束，例如下面的这个就是使用的distinct约束之后对于这个数学这一列进行统计的，这个时候分数相同的情况下就不会重复统计了；

下面的这个是使用的sum函数进行这个统计的，）因为这个里面的这个语句写的有问题，中间出现了小状况，因此这个打上了马赛克），我们使用这个sum的时候，如果这个里面的参数是我们的这个具体的学科的时候，因为这个分数是double类型的数据，这个是可以进行相加操作的，但是如果我们对于这个name进行求和的操作，这个时候输出的结果是0，而且会有警告；

其实这个主要就是因为我们的name这个类型的数据如果是想要进行求和，需要首先进行转化，但是这个转化失败了，所以这个输出的结果就是0，如果我们的这个名字是类似于这个代号001,002,003之类的这个，其实这个就是可以正常转换的了；

下面的这个就是插入了一行名字是006的数据，这个时候再次进行sum求和，就会发现这个时候的结果就是我们的6，因此这个就可以说明我们的这个006这样类似的这个name是可以被转换成功的，但是其他的这个“孙悟空”之类的这个不会被正确转换；

此外，这个sum里面可以添加一些这个表达式之类的，像下面的这个就是在我们的这个sum里面添加了一个表达式，表达的就是三门学科的平均成绩，这个时候我们的这个sql语句执行的时候，就会首先去执行这个求和的过程（chinese+math+english），然后再去把每一个人的这个分数进行sum操作，这其实就是两个步骤，使用一个sql语句进行表达罢了；

除此之外，这个SQL语句里面，还可以使用这个min,max,avg这样的函数，进行一些数据的处理：下面的这个就是求出来某一门科目的这个最大值，最小值，以及平均值的SQL语句，其实也不是很困难；

1.3group by分组查询

我们的操作还是基于这个表来完成的，因此我们首要的任务就是创建一张表：我下面的这个是一个简单的和这个公司的业务类型以及这个薪资，姓名制作的一张表；

这个时候，我们计算一下这几个人的平均的薪资，使用的就是我们的这个avg聚合函数：但是这个是所有人的平均薪资，这个显然是不可以客观反应我们的实际情况的，因此我们需要按照这个置为分别去求解这个对应岗位的平均薪资，这个就是我们的group by分组查询；

下面的这个就是按照岗位进行的平均薪资的计算，这个就是在原来的基础上面加上了这个group by+分组的依据，select后面的两个参数就是我们打印输出的选定的内容；

我们使用这个groupby的时候去，即使是可以搭配条件进行使用的，例如这个需要注意的是，我们的这个条件是分组之前的条件还是分组之后的条件：

例如下面的两个情况：

1.查询每一个岗位的平均工资，但是排除张三；

2.查询每一个岗位的平均薪资，但是排除平均薪资超过了2w的结果；

上面的这两个情况就是很明显的，第一个就是属于分组前就需要使用这个条件，第二个则是分组之后，计算出来的结果结合我们的条件再去进行判断；

首先看第一个情况：这个时候的条件我们只需要使用这个sql语句里面的where加上这个条件进行筛选就可以了；

select role,avg(salary) from emp where name != ‘张三’ group by role;

再看第二个情况：这个时候我们需要使用这个having子句，加在这个group by的后面；

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

在我们玩呢SQL语句里面，可以只使用一个语句同时进行上面两个条件的筛选：

select role,avg(salary) from emp where name != ‘张三’ group by role having avg(salary) < 20000;

1.4联合查询之笛卡尔积

想要知道这个联合查询，我们时候选要来学习一下这个笛卡尔积

下面的这个是两张表：一个是我们的class班级表，一个是我们的student学生表，两个表里面都有学生班级的信息，只不过这个列名不一样，一个是使用的id表示的，一个是使用的classid表示的；

什么是笛卡尔积：实际上就是排列组合，我们的第一个表里面的每一行都要和第二个表里面的内容进行组合，因此这个新的表的列数就是两个表的和，新的表的行数就是两个表的行的乘积（数学知识，不难理解哈）；

我们首先创建四张表：

学生表，班级表，分数表和课程表，内容如下：

这个分数表里面虽然都是数字，但是这个是可以看出来一定的信息的，这个score就是我们的分数，但是这个student_id和course_id就需要到上面的表里面去找到对应的这个课程的名字和学生的名字，例如这个里面的第一行的student_id对象的就是李逵，course_id对应的就是课程表里面的这个java课程，因此这个就是李逵同学的这个java课程的分数就是70.5，通过这样的方式，我们在每一行数据代表的都是某一个同学的某一门课程的实际分数；

练习案例：查询许仙同学的成绩

我们首先要知道需要合并那两个表，一个就是我们学生表（因为是许仙同学），一个就是分数表（因为要找的是成绩）

我们的这个联合查询也是很简单的，就是使用的这个

select * from student,score;(这个顺序决定了哪个表在左边，哪个表在右边）

先取出来哪个表和另外的表进行排列组合，都是可以的；

我们要选出来上面的这个笛卡尔积之后的这个里面两个id相等的情况，其他的数据都是无意义的，这个时候我们可以使用这个

select * from student,score where id = student_id；

但是这个语句会有问题，因为如果我们的这两个表里面出现了相同名字的类，这个时候的连接就会出现问题；

下面的这个是建议的写法：指出来自己的这个咧处于哪一个表里面：

下面的这个就是我们只需要选择出来这个成绩的name是许仙的：

select * from student,score where student.id=score.student_id and student.name=‘许仙’;

上面的这个依然显得很冗余，这个时候，我们让这个select后面的这个东西制定出来具体的类，不要显示出阿里所有的这个信息，因此，我们针对查询结果进行精简：

select student.name score.score from student,score where student.id=score.student_id and student.name=‘许仙’;

1.5左外连接，右外连接介绍join on

上面介绍的这个内容都是属于内连接，实际上我们还有外连接等连接方式，其中这个外连接里面还分为这个左外连接和右外连接；

在下面的这个表里面，如果我们的右边的这个表里面的这个4换成3，两个表里面的这个id是对应的，这个时候无论我们是使用这个内连接，还是外连接，其实这个结果都是一样的，但是我们为什么要了解这个外连接呢，就是如果这个是4，两个表里面的这个id不是完全对应的，这个时候需要我们的外连接方式；

就上面的这个数据，我们可以按照下面的这个流程逐步过渡到这个外连接：

==select * from student,score where student.id=score.id；==这个方式就是内连接的方式，得到的是两边的这个公共属性的数据；

==select * from student join score on student.id=score.id;==这个加上了join on这个选项，实际上输出的结果和上面的这个是一样的；

join on和上面的这个内连接可以达到相同的效果，为什么还要知道这个，就是因为我们的这个左外连接和有外连接是基于这个进行改造的

左外连接：select * from student left join score on student.id=score.id；

右外连接：select * from student right join score on student.id=score.id；

仔细观察不难发现，两个连接方式很显然，就是因为两个表里面的一些内容是你有我没有或者是我有你没有的，这个时候左外连接就是把左边的这个表里面的特有内容添加上去（添加到我们的内连接的这个结果里面，因为我们的内连接是显示的公共的属性内容，左外连接和右外链接都是在这个公共的上面去添加内容的），这个时候右边的这个表里面没有对应内容，就是用null进行填充，反之亦然；

1.6自连表

就是自己和自己连接：首先通过下面的这个，看一下为什么要搞这个自连接

我们想要显示这个计算机原理比这个java课程的成绩高的这个相关的信息，我们的这个成绩表里面，这个成绩都是一行一行进行读取的，这个时候我们的这个每一行是没有办法直接比较的，因为我们上面的这个各种函数和操作都是列与列作为判断的条件的；

这个时候我们想要比较，需要是这个相同的student_id，找出这个对应的这个1,3（分别对应这个计算机原理和java课程）对应的成绩进行比较，这个就需要使用我们的自连接；

但是链接的时候，名字不可以一样，如果我们直接使用score 链接这个score就会报错，我们可以给这个表取两个别名，然后自连接；

select * from score as s1,score as s2 where s1.student_id = s2.student_id;

select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id =1;

这个时候，其实打印的这个结果就已经有些眉目了：我们的每一行里面，这个student=1代表的就是一个学生的，这一行数据里面既有这个1课程的成绩，又有这个3课程的成绩，这个就可以使用这个列进行这个成绩的比较了-------这个就是把行之间的比较转换为我们的列之间的比较------------------自连接的核心精魂

select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id =1 and s1.score>s2.score;---------------进一步筛选；

select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id = s2.student_id and s1.score > s2.score;—选出来有用信息的列；

最后的这个结果和学生表进行笛卡尔积就可以得到这个学生的名字；

最后的结果和我们的课程表进行笛卡尔积就可以得到这个课程的名字；