MySQL查询操作进阶

查询操作是MySQL中使用最广泛的操作，基础操作在前面的博客中已经详细讲解了。接下来对查询操作进行进阶详解。

目录

一.聚合查询

二.联合查询（多表查询）

三.子查询

四.合并查询

一.聚合查询

聚合查询本质上是在针对行与行之间进行运算。前面我们讲过查询针对列于列之间进行运算。

进行聚合查询，需要搭配聚合函数。聚合函数是SQL中内置的一组函数

 这些操作都是针对某个列的所有行来进行的运算。比如求和、求平均值、求最大值、求最小值这几个操作，都是需要针对数字类型的列进行的。（注意一定要是数字列，否则无法进行计算，并且会报错）我们举几个例子：

首先创建一个分数表，内容如下：

 现在想查询一下所有人的语文成绩的平均值，可以使用avg函数，如图：

 接下来查询一下所有成绩的平均值，如图：

 接下来再查询一下语文成绩的最高分以及最低分：

查询中我们还可以使用group by对表中的行进行分组。不用group by分组的时候就相当于只有一组，把所有的行进行聚合。引入group by就可以针对不同的组来分别进行聚合。我们创建一个工资表：

这时候如果我们算平均工资就没有什么意义。因为工资差距过大了。符合事实的应该是根据岗位来分别统计，这时候就需要根据表中的role这个类来进行分组。如图：

 如果我们直接group by，不求平均值，那么得到的结果就是每个组的第一条数据：

 这里如果没有order by，那么顺序就是不可预期的。对于这种分组查询，也是可以指定条件的。指定这个条件有两种情况：

1.分组之前指定条件。也就是先筛选，再分组。比如统计上述每个岗位的平均薪资，但是要刨除程序猿小李的数据：

2.分组之后指定条件。也就是先分组，再筛选。注意要使用关键字having。比如我们要查询每个岗位的平均薪资，但是需要刨除平均薪资在10w之上的。

 很明显，这个平均值是分组之后才可以算出来的。

3.分组之前和之后都指定条件：

二.联合查询（多表查询）

多表查询的基本执行过程引入了一个新方法：笛卡尔积。笛卡尔积，就是把两个表放到一起进行计算。分别取出第一张表的每一行和第二张表的每一行，配对后得到一个新的记录。我们通过SQL来理解一下，首先创建两个表：

 

 这时候我们进行如下操作来对两个表进行笛卡尔积：

 此时就很好理解了，笛卡尔积是通过排列组合得到的，新的表的列数是之前两个表的列数之和；行数是两个表行数之积。但是这种全排列方式得到的数据很多会是无效的数据，只有一部分是有意义的，因此要把无意义的数据给去掉。比如在这个表中，只有classId和学生id（表右边的id）相同的数据才是有效的，我们直接可以想到的方法可能是这样的：

 发现报错，分析报错原因是因为id这个变量名称产生了歧义，表中有两个id，不确定执行哪个。所以，应该使用" .  "成员访问运算符来访问（类似JAVA），如图：

 同样，如果我们在选中数据库的时候如果不想使用use+数据库名的方法，也可以直接 数据库.表名来选中表。在实际开发中，多表查询也会用，但是用的一般不会特别多，因为进行笛卡尔积运算的运行开销是比较大的，尤其是针对大表。

针对多组查询，我们再举一个例子。创建一个新表，表中有学生姓名、分数等一系列数数据：

 

接下来我们需要查询所有同学的总成绩以及同学的个人信息：

a)先对student,score两个表进行笛卡尔积：

表太长，这里只列出一部分。

b)加上连接条件：

 c)加上聚合查询，把同一个同学的行合并到一个组里，计算总分：

在这里不得不提一下自连接：自己和自己进行笛卡尔积。这个操作不是一个通用的解决方案，而是特殊问题的特殊处理方法。自连接的效果就是把行转成列。因为SQL中无法针对行和行之间使用条件比较，但是有的需求中又需要行和行之间进行比较，就可以使用自连接的方法，把行转为列。比如：显示所有“计算机原理”成绩比“JAVA”成绩高的同学的成绩信息：

 此处错误表示名字重复了。解决方法应该是取别名：

此时自连接后的排列组合也产生了大量的无效数据，也需要指定连接条件。 此处是需要每个同学自己的计算机原理和自己的JAVA比较，因此使用student_id作为连接条件，保证每一行的记录，所有列都是对一个同学描述的，如图：

 仔细观察，就会发现其中有些记录符合左侧是计算机原理，右侧是JAVA这样的行，把这样的行筛选出来：

 接下来就看这三行里，谁是属于计算机原理>JAVA的数据了。

三.子查询

子查询本质上可以理解为套娃。就是把多个SQL语句组成一个。比如要查询与“不想毕业”同学的同班同学，正常方法是：

 使用子查询的方法：

 也就是把查询，作为另一个查询的一部分条件。再比如，查询“语文”或者“英语”课程的成绩信息，正常方法是：

 使用子查询的方法是：

 以上可以看出，这里的套娃是可以无穷无尽的，但是不建议这样做。在实际开发中，子查询还是要慎用。因为子查询可能会构造出非常复杂且不好理解的SQL，对于代码的可读性和SQL的执行效率很可能是毁灭性的打击！

四.合并查询

合并查询本质上就是把两个查询的结果集合并成一个。比如，查询id小于3，或者名字为“英文”的课程：

 很多人可能会问了，直接来个or不就行了，何必用union呢？如果用or，那么查询的结果只能来自于同一个表。而用union，查询结果可以是来自于不同的表，只要查询的结果的列匹配即可！还可以使用union all来查询，区别是union会对得到的结果进行去重（把重复的行只保留一份）union all则是可以保留多份，不去重。

综上基本上是MySQL查询操作进阶，如果有错误，欢迎修改指正。