MySQL复合查询

之前在学MySQL的基本查询的时候，我们了解到查询语句的where子句，order by 子句，聚合函数以及分组聚合。

这里我们可以了解到，实际上查询也是可以嵌套查询的。

比如学生表，我们想查询数学成绩最好的学生的id和学生名，如果通过聚合来查会出现错误。

原因就是当出现聚合函数的时候，除了聚合的元素，其他查询的成员必须是用以分组的成员。

而这种问题我们可以通过嵌套来解决。

 即先通过括号内的查询语句来找到最大的math值，然后再在外部通过 where 子句来找到复合条件的语句。

多表查询

大部分时候，我们有时候需要查询的数据可能不是一个表的，可能只是互相之间有关联，但是有时候又需要其他表的数据，因此出现了多表查询。

比如我们创建一个学生表和班级班，并且分别插入数据。

而有时候，我们可能需要查询哪些学生所在班级的名称。

比如这里我们查询学生的id，名字，数学成绩以及班级名称，就需要采用多表查询。

 多表查询有几个需要注意的点。

1. 如果不同的表有相同的列属性名称，但是列属性的含义不同，就需要在前面指定该属性所在表的位置，比如这里学生表的 id 和班级表的 id 名称相同，但是含义不同，就需要通过 student.id 和 classed.id 来进行区别。
2. 如果进行多表查询，一定要设定好限定条件，比如这里我限定 学生的 class_id 必须等于 classes.id，这样才能查询到每个学生正确的表。

如果我们没做好限定条件，就会出现错误查询。

比如这里，我没设置限定条件，结果每一个学生都对应三个班。

自连接 

能够让两个不同的表互相连接进行多表查询，自然可以一个表自己查询自己，这种自己查询自己的行为就是自连接。

自连接有两种方式；

• 使用子查询
• 使用多表查询
  • 多表下的自连接需要添加表的别名

子查询

如上图，这样就是通过子查询的自连接。

多表查询

 也可以自己添加别名，然后通过别名来获得数据。

子查询

和普通的查询语句一样，子查询也能分为单行，单列，多列，多行等不同类型。

单行子查询

单行的子查询实际上之前已经用过了。

比如我想查询和张三一个班的同学的信息，就可以用单行子查询。

单行子查询一次只返回了一行数据，比如这里只返回了 student 表中 name=张三 的 class_id，外部的查询就只要查看 class_id 等于单行查询的数据即可。

多行子查询

而多行子查询自然就是一次返回多个数据了，而一次返回多个数据可能比较就困难一点，因此mysql提供三个关键字供用户使用：in 关键字，all 关键字，any 关键字。

我们先直接使用再理解用法。

查询数学成绩等于三班学生数学成绩的同学

该查询需要用到 in 关键字，因为子查询返回的表的数据有多个，而我们要求的只是数学成绩和三班学生成绩相同的学生信息，也就是说只要求数学成绩在子查询中返回的数学成绩即可。

查询数学成绩大于所有三班学生的数学成绩的同学

该查询需要用到 all 关键字，因为子查询返回三班学生的数学成绩，而我们要求找到比三班所有数学成绩都要高的学生。

查询数学成绩大于任意三班学生的数学成绩的同学

该查询需要用到 any 关键字，因为子查询返回三班学生的数学成绩，而我们要求找到比三班任意数学成绩都要高的学生。

 

通过三个示例我们也能够发现三个关键字的作用。

• in 关键字：只需要我们限定的条件在子查询的数据当中即符合条件
• all 关键字：会将限定的条件逐个和子查询的数据比较，所有数据比较结果符合条件即可返回
• any 关键字：会将限定条件和子查询的数据比较，有一个数据比较结果符合调节即可。

多列子查询

多列子查询自然是返回多列的数据，比如我们查询和张三的class_id相同并且语文成绩相同的同学，就需要用到多列子查询。

此时子查询返回的是多列的数据，因此限定条件也必须是多列的，而且限定条件内部的内容也必须对应，否则会出现查询失败之类的错误。

 当然我们还有多列多行子查询，不过使用的很少，因此不做说明。

使用from子句进行子查询

在之前学习的查询语句中，有些操作比较难处理。

比如如果我想查询每个班中数学成绩最好的学生的姓名，id，班级号以及数学成绩，那么我们必须用到子查询。

当然我们可以使用子查询的单行子查询来完成这个查询。

语句：select student.id,student.name,student.math,student.class_id from student where math in (select max(math) form student group by class_id);

不过除此之外，mysql还退出了使用from子句进行子查询的操作。

语句：select student.id,student.name,student.math,student.class_id from student,(select max(math) max_math from student group by class_id) tmp where tmp.class_id=student.class_id and student.math=tmp.max_math;

 需要注意，如果需要用到子查询中的数据来当作条件，那么最好是给这个数据添加一个别名，比如这里我就将 max(math) 设置别名 max_math，否则直接使用 tmp.math 是行不通的。

而我们可以看到，这里是将我们的子查询当作一个表，其格式有点像多表查询。

从这里我们就能够认识到，实际上多表问题就是将多表转换成单表，来一个个解决问题。

在mysql 中，一切皆表，不管是我们通过select显示出来的表，还是from子句中进行的子查询，实际上都可以当作是一个临时的表，像上面的操作就是将 select 的每个班的最高数学成绩当作一个表，并且给了一个别名，然后通过限定条件获得一个最终的表。 

合并查询

union关键字：取得两个结果集的并集，会自动去除重复行。

比如我想查询所有数学>70或者语文>80分的同学，我们就可以采用 union 关键字

我们看到只要符合一个条件即可显示，有点像 or，不过他会去除重复行 

unoin all 关键字：取得两个结果集的并集，并且不会去除重复行。

而unoin all 也是类似的，只是不去除重复行。

不过有一点需要注意的是，两个结果集的列必须相同，否则会报错。

总结

本次讲解了select的多表查询，也明白了多表查询实际上就是将多表问题拆成一个个单表问题来解决，也了解到不同子查询，如单行，多行和多列，还有比如多行子查询使用的关键字，也明白了mysql中一切皆表，select所查询的结果实际上可以当成一个临时表来和from子句，where子句配合使用，还有union关键字的作用，相信会对大家有所帮助。。