MySQL 子查询和分组查询

2022-05-12 19:20

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

概述

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

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


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 ('初三一班'),('初三二班'),('初三三班');



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语句，称主查询或外查询。

子查询分类

按照查询的返回结果

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

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

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

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

按子查询位置区分

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

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

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

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

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

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

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

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

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

下面我们一个个来测试。

select后子查询

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


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 所属的班级，可以这么写：


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再次查询，支持的是表子查询。但是必须对子查询起别名，否则无法找到表。

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


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开始从高到低排序。


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后子查询，代码如下：


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种方式：标量子查询（单行单列行子查询）；列子查询（单列多行子查询）行子查询（多行多列）；

他有如下共同的特点：

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

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

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

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

配合 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分)成绩好的同学：


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不在同一班：


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表达式


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：所有非三班的同学


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：任意非三班的同学


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


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

行子查询说明

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


mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
|    19 | lala    | 51  |    0 |
+-----------+-------------+-------+---------+
1 row in set

exists子查询

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

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

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

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

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


mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid);

+-----------+
| classname |
+-----------+
| 初三一班 |
| 初三二班 |
| 初三三班 |
+-----------+
3 rows in set

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


mysql> select classname from classes a where a.classid in(select classid from students);

+-----------+
| classname |
+-----------+
| 初三一班 |
| 初三二班 |
| 初三三班 |
+-----------+
3 rows in set

组合查询

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

单表多次返回

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


 select cname1,cname2 from tname where condition1
 union
 select cname1,cname2 from tname where condition2

多表返回同结构

将同数量结构的字段组合


 select t1_cname1,t1_cname2 from tname1 where condition
 union
 select t2_cname1,t_2cname2 from tname2 where condition

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

总结

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

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

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

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

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL 子查询 mysql 分组查询

阅读原文内容投诉

MySQL 子查询和分组查询

下载Word文档到电脑，方便收藏和打印～

下载Word文档

猜你喜欢

MySQL 子查询和分组查询

概述子查询是SQL查询中的重要一块，是我们基于多表之间进行数据聚合和判断的一种手段，使得我们的处理复杂数据更加的便捷，这一节我们主要来了解一下子查询。先做一下数据准备，这边建立三张表：班级、学生、毕业成绩表，用于后面的操作：drop da

2022-05-12

MYSQL查询--子查询，合并查询

2024-04-02

MySQL之连接查询和子查询

多表连接的基本语法多表连接，就是将几张表拼接为一张表，然后进行查询select 字段1, 字段2, ...from 表1 {inner|lift|right} join 表2on 连接条件;有如下两张表：部门表和员工表交叉连接和笛卡尔积现象交叉连接交叉连接，又

2015-01-24

Mysql---子查询的三种查询方式（表子查询，条件子查询，列中子查询）

2023-09-04

详解MySQL子查询（嵌套查询）、联结表、组合查询

2024-04-02

MySQL子查询详解(单行子查询、多行子查询与相关子查询)

2024-04-02

MySQL子查询（嵌套查询）

2023-09-04

mysql中怎么实现查询和子查询

2024-04-02

MySQL子查询

子查询（嵌套查询）子查询是指一个查询语句嵌套在另一个查询语句的内部的查询eg:要查询员工工资比‘Abel’高的员工名字SELECT name,salary #外查询(主查询)FROM employeesWHERE salary > (#查询"Abel"

2021-02-10

mysql标量子查询和非法子查询的区别

2024-04-02

MYSQL——子查询

2024-04-02

MySQL中连接查询和子查询的问题

2024-04-02

MYSQL聚合查询、分组查询、联合查询举例详解

目录聚合查询聚合函数count()sum()avg()max()和min()总结分组查询group by 子句having 子句联合查询笛卡尔积内连接外连接自连接子查询单行子查询多行子查询from子句使用子查询合并查询总结聚合查询聚合查

2024-09-23

MySQL数据查询之子查询的示例分析

2024-04-02

MySQL分组查询、排序查询、分页查询以及执行顺序

本文介绍了MySQL中多种查询类型，包括分组查询、排序查询、分页查询，以及它们的执行顺序。分组查询用于根据指定列对结果分组并计算聚合函数；排序查询用于根据指定列对结果排序；分页查询用于将结果拆分为多个页面。MySQL查询的执行顺序从FROM子句开始，依次执行WHERE、GROUPBY、HAVING、ORDERBY、LIMIT子句，其中包含子查询的子查询将优先执行。

2024-04-02

MySQL 中，子查询能否替代联合查询？(MySQL子查询能替代联合查询吗)

在 MySQL 数据库中，子查询和联合查询都是非常常用的查询技术，它们各自具有独特的优势和适用场景。那么，子查询能否替代联合查询呢？这是一个值得探讨的问题。

一、子查询的基本概念

子查询是在一个查询语句中嵌套另一个查询语句。外层查询可以基于子查询的结果进行进一步的操作，如筛选、排序等。子查询通常用于获取中间结果或满足特定条件的数据集。

例如，以下是一个简单的子查询示例：

SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

在这个例子中，子查询 (SELECT column3 FROM table2 WHERE condition) 首先在 table2 中查找满足条件的 column3 值，然后外层查询在 table1 中筛选出 column2 等于子查询结果的行，并返回 column1 的值。

二、联合查询的基本概念

联合查询用于将多个查询的结果合并成一个结果集。它通过 UNION 或 UNION ALL 操作符将多个查询的结果组合在一起。联合查询通常用于合并具有相似结构的数据集或在不同的表中查找相关的数据。

例如，以下是一个简单的联合查询示例：

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

在这个例子中，两个查询分别从 table1 和 table2 中选择 column1 的值，然后通过 UNION 操作符将它们合并成一个结果集，去除重复的行。

三、子查询与联合查询的适用场景

子查询的适用场景
- 当需要在查询中使用另一个查询的结果作为条件时，子查询非常有用。例如，在筛选数据时，可以使用子查询来获取特定条件的中间结果，并在主查询中使用该结果进行进一步的筛选。
- 子查询可以用于获取聚合函数的结果或计算中间值。例如，可以使用子查询计算某个表中的最大值或平均值，并在主查询中使用该值进行条件筛选。
- 子查询可以用于在不同的表之间进行关联查询。通过在子查询中获取关联表的相关数据，然后在主查询中使用该数据进行进一步的操作。
联合查询的适用场景
- 当需要合并多个具有相似结构的数据集时，联合查询是一个很好的选择。例如，可以使用联合查询将多个表中的相同字段合并成一个结果集，以便进行统一的分析和处理。
- 联合查询可以用于在不同的数据库或数据源之间进行数据集成。通过将多个数据源的查询结果合并成一个结果集，可以实现数据的整合和共享。
- 联合查询可以用于在查询结果中去除重复的行。通过使用 UNION ALL 操作符，可以保留所有的行，包括重复的行；而使用 UNION 操作符，则会去除重复的行。

四、子查询能否替代联合查询

子查询和联合查询在某些情况下可以相互替代，但在其他情况下，它们各自具有不可替代的优势。

子查询可以替代简单的联合查询
- 对于简单的联合查询，即只需要合并两个具有相似结构的数据集且不需要去除重复行的情况，子查询可以通过嵌套查询来实现相同的功能。
- 例如，以下两个查询是等价的：
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
```
SELECT column1 FROM (SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2) AS temp;
```
  在这个例子中，第二个查询使用子查询将两个表的查询结果合并成一个临时表，然后在主查询中选择 column1 的值。
联合查询可以替代简单的子查询
- 对于简单的子查询，即只需要在一个查询中使用另一个查询的结果作为条件的情况，联合查询可以通过连接操作来实现相同的功能。
- 例如，以下两个查询是等价的：
```
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);
```
```
SELECT t1.column1
FROM table1 t1
JOIN (SELECT column3 FROM table2 WHERE condition) t2 ON t1.column2 = t2.column3;
```
  在这个例子中，第二个查询使用连接操作将 table1 和一个子查询的结果连接起来，然后在主查询中选择 column1 的值。
子查询和联合查询在复杂查询中的应用
- 在复杂的查询中，子查询和联合查询都有各自的优势。子查询可以更灵活地处理嵌套查询和中间结果，而联合查询可以更方便地合并多个数据集。
- 例如，以下是一个使用子查询和联合查询的复杂查询示例：
```
SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition1)
OR column2 IN (SELECT column4 FROM table3 WHERE condition2);
```
```
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition1 AND table1.column2 = table2.column3)
OR EXISTS (SELECT 1 FROM table3 WHERE condition2 AND table1.column2 = table3.column4);
```
  在这个例子中，第一个查询使用子查询在 table2 和 table3 中查找满足条件的 column3 和 column4 值，并将结果作为 IN 条件用于在 table1 中筛选数据；第二个查询使用存在量词 EXISTS 在 table2 和 table3 中查找满足条件的行，并通过连接操作将结果与 table1 关联起来。