MySQL 子查询和分组查询

吾爱主题阅读：174 2024-04-01 23:51:32 评论：0

概述

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 drop database if exists `Helenlyn_Class`; create database `Helenlyn_Class`; /*班级表*/ DROP TABLE IF EXISTS `classes`; CREATE TABLE `classes` ( `classid` int primary key AUTO_INCREMENT comment '班级id' , `classname` varchar (30) DEFAULT NULL comment '班级名称' ) ENGINE=InnoDB comment '班级表' ; insert into `classes`(`classname`) values ( '初三一班' ),( '初三二班' ),( '初三三班' ); /*学生表：这边假设学生id和姓名都具有唯一性*/ DROP TABLE IF EXISTS `students`; CREATE TABLE `students` ( `studentid` int primary key NOT NULL AUTO_INCREMENT comment '学生id' , `studentname` varchar (20) DEFAULT NULL comment '学生姓名' , `score` DECIMAL (10,2) DEFAULT NULL comment '毕业成绩' , `classid` int (4) DEFAULT NULL comment '所属班级id，来源于classes表的classid' ) ENGINE=InnoDB comment '学生表' ; insert into `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); /*毕业考核分数排名表*/ DROP TABLE IF EXISTS `scores`; CREATE TABLE `scores`( `scoregrad` varchar (3) primary key comment '等级：S、A、B、C、D' , `downset` int comment '分数评级下限' , `upset` int comment '分数评级上限' ) comment '毕业考核分数排名表' ; INSERT INTO `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后面，仅仅支持标量子查询，即只能返回一个单值数据。比如上面的学生班级表，我们查询每个班级的学生数量，可以这么写：

1 2 3 4 5 6 7 8 9 10 11 mysql> select a.classid as 班级编号,a.classname as 班级名称, ( select count (*) from students b where b.classid = a.classid) as 学生数量 from classes a; + ----------+----------+----------+ | 班级编号 | 班级名称 | 学生数量 | + ----------+----------+----------+ | 1 | 初三一班 | 6 | | 2 | 初三二班 | 6 | | 3 | 初三三班 | 6 | + ----------+----------+----------+ 3 rows in set

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

1 2 3 4 5 6 7 8 9 mysql> select ( select classname from classes a,students b where a.classid = b.classid and b.studentname= 'brand' ) as 班级; + ----------+ | 班级 | + ----------+ | 初三一班 | + ----------+ 1 row in set

from后子查询

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

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

1 2 3 4 5 6 7 8 9 10 mysql> select a.classid, avg (a.score) from students a group by a.classid; + ---------+--------------+ | classid | avg (a.score) | + ---------+--------------+ | 1 | 96.616667 | | 2 | 83.500000 | | 3 | 73.583333 | + ---------+--------------+ 3 rows in set

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

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select * from scores order by upset desc ; + -----------+---------+-------+ | scoregrad | downset | upset | + -----------+---------+-------+ | S | 91 | 100 | | A | 81 | 90 | | B | 71 | 80 | | C | 61 | 70 | | D | 51 | 60 | + -----------+---------+-------+ 5 rows in set

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

1 2 3 4 5 6 7 8 9 10 11 12 select a.classid as 班级id,a.avgscore 平均毕业分数,b.scoregrad 分数评级 from ( select classid, avg (score) as avgscore from students group by classid) as a, scores b where a.avgscore between b.downset and b.upset; + --------+--------------+----------+ | 班级id | 平均毕业分数 | 分数评级 | + --------+--------------+----------+ | 1 | 96.616667 | S | | 2 | 83.500000 | A | | 3 | 73.583333 | B | + --------+--------------+----------+ 3 rows in set

对于子表查询，必须提供别名，否则会提示：Every derived table must have its own alias，可以试试。

where和having型的子查询

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

他有如下共同的特点：

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

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

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

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

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

单个标量子查询应用

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

1 2 3 4 5 6 7 8 9 10 11 mysql> select * from students a where a.score >( select b.score from students b where b.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 | + -----------+-------------+-------+---------+ 5 rows in set

多个标量子查询应用

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> select * from students a where a.score <( select b.score from students b where b.studentname= 'diny' ) and a.classid <> ( select b.classid from students b where b.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 | + -----------+-------------+-------+---------+ 12 rows in set

子查询+分组函数

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

1 2 3 4 5 6 7 8 9 mysql> select a.classid, avg (a.score) as avgscore from students a group by a.classid having avgscore < ( select avg (score) from students); + ---------+-----------+ | classid | avgscore | + ---------+-----------+ | 2 | 83.500000 | | 3 | 73.583333 | + ---------+-----------+ 2 rows in set

列子查询说明

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> select * from students a where a.classid in ( select distinct b.classid from classes b where b.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 | + -----------+-------------+-------+---------+ 12 rows in set

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> select * from students a where a.classid = any ( select distinct b.classid from classes b where b.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 | + -----------+-------------+-------+---------+ 12 rows in set

列子查询+all：等同于 not in

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select * from students a where a.classid <> all ( select distinct b.classid from classes b where b.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 | + -----------+-------------+-------+---------+ 6 rows in set