MySQL 子查询和分组查询

2023-07-11 14:15:03 69

概述

子查询是SQL查询中的重要一块，是我们基于多表之间进行数据聚合和判断的一种手段，使得我们的处理复杂数据更加的便捷，这一节我们主要来了解一下子查询。

先做一下数据准备，这边建立三张表：班级、学生、毕业成绩表，用于后面的操作：

dropdatabaseifexists`Helenlyn_Class`;
createdatabase`Helenlyn_Class`;

/*班级表*/
DROPTABLEIFEXISTS`classes`;
CREATETABLE`classes`(
`classid`intprimarykeyAUTO_INCREMENTcomment'班级id',
`classname`varchar(30)DEFAULTNULLcomment'班级名称'
)ENGINE=InnoDBcomment'班级表';

insertinto`classes`(`classname`)
values('初三一班'),('初三二班'),('初三三班');

/*学生表：这边假设学生id和姓名都具有唯一性*/

DROPTABLEIFEXISTS`students`;
CREATETABLE`students`(
`studentid`intprimarykeyNOTNULLAUTO_INCREMENTcomment'学生id',
`studentname`varchar(20)DEFAULTNULLcomment'学生姓名',
`score`DECIMAL(10,2)DEFAULTNULLcomment'毕业成绩',
`classid`int(4)DEFAULTNULLcomment'所属班级id，来源于classes表的classid'
)ENGINE=InnoDBcomment'学生表';
insertinto`students`(`studentname`,`score`,`classid`)values
('brand',97.5,1),('helen',96.5,1),('lyn',96,1),('sol',97,1),('weng',100,1),('diny',92.7,1),
('b1',81,2),('b2',82,2),('b3',83,2),('b4',84,2),('b5',85,2),('b6',86,2),
('c1',71,3),('c2',72.5,3),('c3',73,3),('c4',74,3),('c5',75,3),('c6',76,3);


/*毕业考核分数排名表*/
DROPTABLEIFEXISTS`scores`;
CREATETABLE`scores`(
`scoregrad`varchar(3)primarykeycomment'等级：S、A、B、C、D',
`downset`intcomment'分数评级下限',
`upset`intcomment'分数评级上限'
)comment'毕业考核分数排名表';
INSERTINTO`scores`values('S',91,100),('A',81,90),('B',71,80),('C',61,70),('D',51,60);

子查询

SQL支持创建子查询（subquery），就是嵌套在其他查询中的查询，也就是说在select语句中会出现其他的select语句，我们称为子查询或内查询。而外部的select语句，称主查询或外查询。

子查询分类

按照查询的返回结果

1、单行单列（标量子查询）：返回的是一个具体列的内容，可以理解为一个单值数据；

2、单行多列（行子查询）：返回一行数据中多个列的内容；

3、多行单列（列子查询）：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

4、多行多列（表子查询）：查询返回的结果是一张临时表；

按子查询位置区分

select后的子查询：仅仅支持标量子查询，即只能返回一个单值数据。

from型子查询：把内层的查询结果当成临时表，供外层sql再次查询，所以支持的是表子查询。

where或having型子查询：指把内部查询的结果作为外层查询的比较条件，支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）。

一般会和下面这几种方式配合使用：

1）、in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较。

2）、any子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

3）、all子查询：内层子查询返回的结果需同时满足所有内层查询条件。

4）、比较运算符子查询：子查询中可以使用的比较运算符如 >、>=、<=、<、=、<>

exists子查询：把外层的查询结果（支持多行多列），拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。

下面我们一个个来测试。

select后子查询

位于select后面，仅仅支持标量子查询，即只能返回一个单值数据。比如上面的学生班级表，我们查询每个班级的学生数量，可以这么写：

mysql>selecta.classidas班级编号,a.classnameas班级名称,
(selectcount(*)fromstudentsbwhereb.classid=a.classid)as学生数量
fromclassesa;
+----------+----------+----------+
|班级编号|班级名称|学生数量|
+----------+----------+----------+
|1|初三一班|6|
|2|初三二班|6|
|3|初三三班|6|
+----------+----------+----------+
3rowsinset

查询学生brand所属的班级，可以这么写：

mysql>select
(selectclassnamefromclassesa,studentsbwherea.classid=b.classidandb.studentname='brand')
as班级;
+----------+
|班级|
+----------+
|初三一班|
+----------+
1rowinset

from后子查询

把内层的查询结果当成临时表，提供外层sql再次查询，支持的是表子查询。但是必须对子查询起别名，否则无法找到表。

查询每个班级的平均成绩：

mysql>selecta.classid,avg(a.score)fromstudentsagroupbya.classid;

+---------+--------------+
|classid|avg(a.score)|
+---------+--------------+
|1|96.616667|
|2|83.500000|
|3|73.583333|
+---------+--------------+
3rowsinset

查询毕业考核分数排名表：S开始从高到低排序。

mysql>select*fromscoresorderbyupsetdesc;

+-----------+---------+-------+
|scoregrad|downset|upset|
+-----------+---------+-------+
|S|91|100|
|A|81|90|
|B|71|80|
|C|61|70|
|D|51|60|
+-----------+---------+-------+
5rowsinset

如果综合两个查询结果，想查出各个班级的平均成绩是位于什么段位，就可以用from后子查询，代码如下：

selecta.classidas班级id,a.avgscore平均毕业分数,b.scoregrad分数评级from
(selectclassid,avg(score)asavgscorefromstudentsgroupbyclassid)asa,
scoresbwherea.avgscorebetweenb.downsetandb.upset;

+--------+--------------+----------+
|班级id|平均毕业分数|分数评级|
+--------+--------------+----------+
|1|96.616667|S|
|2|83.500000|A|
|3|73.583333|B|
+--------+--------------+----------+
3rowsinset

对于子表查询，必须提供别名，否则会提示：Everyderivedtablemusthaveitsownalias，可以试试。

where和having型的子查询

根据我们上面提到过的内容，where或having后面，可以使用3种方式：标量子查询（单行单列行子查询）；列子查询（单列多行子查询）行子查询（多行多列）；

他有如下共同的特点：

1、一般用括号将子查询包起来。

2、子查询一般放在条件的右侧。

3、标量子查询，一般搭配着单行操作符使用，多行操作符 >、<、>=、<=、=、<>

4、列子查询，一般搭配着多行操作符使用

5、配合in、notin、all、any使用，in是指列表中的任意一个，any是比较列表中任意一个score>any(60,70,80)则score>60即可；all是比较列表中所有,score>(60,70,80)，score需>80。

单个标量子查询应用

就是where或者having后面只跟一个标量查询的，比如查询出比diny(92.7分)成绩好的同学：

mysql>select*fromstudentsawherea.score>(selectb.scorefromstudentsbwhereb.studentname='diny');
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|1|brand|97.5|1|
|2|helen|96.5|1|
|3|lyn|96|1|
|4|sol|97|1|
|5|weng|100|1|
+-----------+-------------+-------+---------+
5rowsinset

多个标量子查询应用

where或者having后面只跟一个标量查询的，比如查询出比diny(92.7分)成绩差的同学，并且班级跟diny不在同一班：

mysql>select*fromstudentsawhere
a.score<(selectb.scorefromstudentsbwhereb.studentname='diny')
anda.classid<>(selectb.classidfromstudentsbwhereb.studentname='diny');
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|7|b1|81|2|
|8|b2|82|2|
|9|b3|83|2|
|10|b4|84|2|
|11|b5|85|2|
|12|b6|86|2|
|13|c1|71|3|
|14|c2|72.5|3|
|15|c3|73|3|
|16|c4|74|3|
|17|c5|75|3|
|18|c6|76|3|
+-----------+-------------+-------+---------+
12rowsinset

子查询+分组函数

分别取出三个班级的平均成绩，并筛选出低于全年级的平均成绩的班级信息，使用having表达式

mysql>selecta.classid,avg(a.score)asavgscorefromstudentsagroupbya.classid
havingavgscore<(selectavg(score)fromstudents);
+---------+-----------+
|classid|avgscore|
+---------+-----------+
|2|83.500000|
|3|73.583333|
+---------+-----------+
2rowsinset

列子查询说明

列的子查询需要搭配多行操作符：in(notin)、any/some、all。使用distinct关键字进行去重可以提高执行效率。

列子查询+in：所有非三班的同学

mysql>select*fromstudentsawherea.classidin(selectdistinctb.classidfromclassesbwhereb.classid<3);
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|1|brand|97.5|1|
|2|helen|96.5|1|
|3|lyn|96|1|
|4|sol|97|1|
|5|weng|100|1|
|6|diny|92.7|1|
|7|b1|81|2|
|8|b2|82|2|
|9|b3|83|2|
|10|b4|84|2|
|11|b5|85|2|
|12|b6|86|2|
+-----------+-------------+-------+---------+
12rowsinset

列子查询+any：任意非三班的同学

mysql>select*fromstudentsawherea.classid=any(selectdistinctb.classidfromclassesbwhereb.classid<3);
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|1|brand|97.5|1|
|2|helen|96.5|1|
|3|lyn|96|1|
|4|sol|97|1|
|5|weng|100|1|
|6|diny|92.7|1|
|7|b1|81|2|
|8|b2|82|2|
|9|b3|83|2|
|10|b4|84|2|
|11|b5|85|2|
|12|b6|86|2|
+-----------+-------------+-------+---------+
12rowsinset

列子查询+all：等同于notin

mysql>select*fromstudentsawherea.classid<>all(selectdistinctb.classidfromclassesbwhereb.classid<3);
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|13|c1|71|3|
|14|c2|72.5|3|
|15|c3|73|3|
|16|c4|74|3|
|17|c5|75|3|
|18|c6|76|3|
+-----------+-------------+-------+---------+
6rowsinset

行子查询说明

查询学生编号最小但是成绩最好的同学：

mysql>select*fromstudentsawhere(a.studentid,a.score)in(selectmax(studentid),min(score)fromstudents);
+-----------+-------------+-------+---------+
|studentid|studentname|score|classid|
+-----------+-------------+-------+---------+
|19|lala|51|0|
+-----------+-------------+-------+---------+
1rowinset

exists子查询

也叫做相关子查询，就是把外层的查询结果（支持多行多列），拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。

1、exists查询结果：1或0，1为true，0为false，exists查询的结果用来判断子查询的结果集中是否有值。

2、exists子查询，一般可以用in来替代，所以exists用的少。

3、和前面的那些查询方式不同，先执行主查询，然后根据主查询的结果，再用子查询的结果来过滤。因为子查询中包含了主查询中用到的字段，所以也叫相关子查询。

示例，查询所有学生的班级名称

mysql>selectclassnamefromclassesawhereexists(select1fromstudentsbwhereb.classid=a.classid);

+-----------+
|classname|
+-----------+
|初三一班|
|初三二班|
|初三三班|
+-----------+
3rowsinset

使用in来替代（看着更简洁）：

mysql>selectclassnamefromclassesawherea.classidin(selectclassidfromstudents);

+-----------+
|classname|
+-----------+
|初三一班|
|初三二班|
|初三三班|
+-----------+
3rowsinset

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个
查询结果集返回。这些组合查询通常称为并（union）或复合查询（compoundquery）。

单表多次返回

将不同查询条件的结果组合在一起

selectcname1,cname2fromtnamewherecondition1
union
selectcname1,cname2fromtnamewherecondition2

多表返回同结构

将同数量结构的字段组合

selectt1_cname1,t1_cname2fromtname1wherecondition
union
selectt2_cname1,t_2cname2fromtname2wherecondition

这边不赘述，后面有专门的章节说到这个

总结

可以按照查询的返回类型和语句中子查询的位置两个方面来学习

注意使用in、any、some、all的用法

无论是比较还是查询还是count，字段中有null值总会引起误解，建议建表时字段不为空，或者提供默认值。

以上就是MySQL子查询和分组查询的详细内容，更多关于MySQL查询的资料请关注毛票票其它相关文章！