MySQL全面瓦解11：子查询和组合查询

最新推荐文章于 2024-05-31 14:43:45 发布

smart哥

最新推荐文章于 2024-05-31 14:43:45 发布

阅读量10

点赞数 40

分类专栏： mysql专题文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/smart_an/article/details/138387690

版权

mysql专题专栏收录该内容

29 篇文章 0 订阅

订阅专栏

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

学习必须往深处挖，挖的越深，基础越扎实！

阶段1、深入多线程

 阶段2、深入多线程设计模式

 阶段3、深入juc源码解析

阶段4、深入jdk其余源码解析

阶段5、深入jvm源码解析

码哥源码部分

码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】

码哥讲源码【炸雷啦！炸雷啦！黄光头他终于跑路啦！】

码哥讲源码-【jvm课程前置知识及c/c++调试环境搭建】

码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】

码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】

码哥讲源码【你水不是你的错,但是你胡说八道就是你不对了！】

码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】

终结B站没人能讲清楚红黑树的历史，不服等你来踢馆！

打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】

概述

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

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

     1 drop database if exists `Helenlyn_Class`;
     2 create database `Helenlyn_Class`;
     3 
     4 /*班级表*/
     5 DROP TABLE IF EXISTS `classes`;
     6 CREATE TABLE `classes` (
     7   `classid` int primary key AUTO_INCREMENT comment '班级id',
     8   `classname` varchar(30) DEFAULT NULL comment '班级名称'
     9 ) ENGINE=InnoDB comment '班级表';
    10 
    11 insert  into `classes`(`classname`) 
    12 values ('初三一班'),('初三二班'),('初三三班');
    13 
    14 /*学生表：这边假设学生id和姓名都具有唯一性*/
    15 
    16 DROP TABLE IF EXISTS `students`;
    17 CREATE TABLE `students` (
    18   `studentid` int primary key NOT NULL AUTO_INCREMENT comment '学生id',
    19   `studentname` varchar(20) DEFAULT NULL comment '学生姓名',
    20   `score` DECIMAL(10,2) DEFAULT NULL comment '毕业成绩',
    21   `classid` int(4) DEFAULT NULL comment '所属班级id，来源于classes表的classid'
    22 ) ENGINE=InnoDB comment '学生表';
    23 insert  into `students`(`studentname`,`score`,`classid`) values 
    24 ('brand',97.5,1),('helen',96.5,1),('lyn',96,1),('sol',97,1),('weng',100,1),('diny',92.7,1),
    25 ('b1',81,2),('b2',82,2),('b3',83,2),('b4',84,2),('b5',85,2),('b6',86,2),
    26 ('c1',71,3),('c2',72.5,3),('c3',73,3),('c4',74,3),('c5',75,3),('c6',76,3);
    27 
    28 
    29 /*毕业考核分数排名表*/
    30 DROP TABLE IF EXISTS `scores`;
    31 CREATE TABLE `scores`(
    32   `scoregrad` varchar(3) primary key  comment '等级：S、A、B、C、D',
    33   `downset`  int comment '分数评级下限',
    34   `upset` int comment '分数评级上限'
    35 ) comment '毕业考核分数排名表';
    36 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 mysql> select a.classid as 班级编号,a.classname as 班级名称,
     2 (select count(*) from students b where b.classid = a.classid) as 学生数量 
     3 from classes a;
     4 +----------+----------+----------+
     5 | 班级编号 | 班级名称 | 学生数量 |
     6 +----------+----------+----------+
     7 |        1 | 初三一班 |        6 |
     8 |        2 | 初三二班 |        6 |
     9 |        3 | 初三三班 |        6 |
    10 +----------+----------+----------+
    11 3 rows in set

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

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

from后子查询

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

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

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

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

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

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

     1 select a.classid as 班级id,a.avgscore 平均毕业分数,b.scoregrad 分数评级 from 
     2 (select classid,avg(score) as avgscore from students group by classid) as a,
     3 scores b where a.avgscore between b.downset and b.upset; 
     4 
     5 +--------+--------------+----------+
     6 | 班级id | 平均毕业分数 | 分数评级 |
     7 +--------+--------------+----------+
     8 |      1 | 96.616667    | S        |
     9 |      2 | 83.500000    | A        |
    10 |      3 | 73.583333    | B        |
    11 +--------+--------------+----------+
    12 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 mysql> select * from students a where a.score >(select b.score from students b where b.studentname='diny');
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 |         1 | brand       | 97.5  |       1 |
     6 |         2 | helen       | 96.5  |       1 |
     7 |         3 | lyn         | 96    |       1 |
     8 |         4 | sol         | 97    |       1 |
     9 |         5 | weng        | 100   |       1 |
    10 +-----------+-------------+-------+---------+
    11 5 rows in set

多个标量子查询应用

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

     1 mysql>  select * from students a where 
     2 a.score <(select b.score from students b where b.studentname='diny') 
     3 and a.classid <> (select b.classid from students b where b.studentname='diny') ;
     4 +-----------+-------------+-------+---------+
     5 | studentid | studentname | score | classid |
     6 +-----------+-------------+-------+---------+
     7 |         7 | b1          | 81    |       2 |
     8 |         8 | b2          | 82    |       2 |
     9 |         9 | b3          | 83    |       2 |
    10 |        10 | b4          | 84    |       2 |
    11 |        11 | b5          | 85    |       2 |
    12 |        12 | b6          | 86    |       2 |
    13 |        13 | c1          | 71    |       3 |
    14 |        14 | c2          | 72.5  |       3 |
    15 |        15 | c3          | 73    |       3 |
    16 |        16 | c4          | 74    |       3 |
    17 |        17 | c5          | 75    |       3 |
    18 |        18 | c6          | 76    |       3 |
    19 +-----------+-------------+-------+---------+
    20 12 rows in set

子查询+分组函数

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

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

列子查询说明

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

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

     1 mysql> select * from students a where a.classid in (select distinct b.classid from classes b where b.classid <3);
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 |         1 | brand       | 97.5  |       1 |
     6 |         2 | helen       | 96.5  |       1 |
     7 |         3 | lyn         | 96    |       1 |
     8 |         4 | sol         | 97    |       1 |
     9 |         5 | weng        | 100   |       1 |
    10 |         6 | diny        | 92.7  |       1 |
    11 |         7 | b1          | 81    |       2 |
    12 |         8 | b2          | 82    |       2 |
    13 |         9 | b3          | 83    |       2 |
    14 |        10 | b4          | 84    |       2 |
    15 |        11 | b5          | 85    |       2 |
    16 |        12 | b6          | 86    |       2 |
    17 +-----------+-------------+-------+---------+
    18 12 rows in set

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

     1 mysql> select * from students a where a.classid = any (select distinct b.classid from classes b where b.classid <3);
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 |         1 | brand       | 97.5  |       1 |
     6 |         2 | helen       | 96.5  |       1 |
     7 |         3 | lyn         | 96    |       1 |
     8 |         4 | sol         | 97    |       1 |
     9 |         5 | weng        | 100   |       1 |
    10 |         6 | diny        | 92.7  |       1 |
    11 |         7 | b1          | 81    |       2 |
    12 |         8 | b2          | 82    |       2 |
    13 |         9 | b3          | 83    |       2 |
    14 |        10 | b4          | 84    |       2 |
    15 |        11 | b5          | 85    |       2 |
    16 |        12 | b6          | 86    |       2 |
    17 +-----------+-------------+-------+---------+
    18 12 rows in set

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

     1 mysql> select * from students a where a.classid <> all (select distinct b.classid from classes b where b.classid <3);
     2 +-----------+-------------+-------+---------+
     3 | studentid | studentname | score | classid |
     4 +-----------+-------------+-------+---------+
     5 |        13 | c1          | 71    |       3 |
     6 |        14 | c2          | 72.5  |       3 |
     7 |        15 | c3          | 73    |       3 |
     8 |        16 | c4          | 74    |       3 |
     9 |        17 | c5          | 75    |       3 |
    10 |        18 | c6          | 76    |       3 |
    11 +-----------+-------------+-------+---------+
    12 6 rows in set

行子查询说明

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

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

exists子查询

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

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

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

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

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

     1 mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid);
     2 
     3 +-----------+
     4 | classname |
     5 +-----------+
     6 | 初三一班  |
     7 | 初三二班  |
     8 | 初三三班  |
     9 +-----------+
    10 3 rows in set

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

     1 mysql> select classname from classes a where a.classid in(select classid from students);
     2 
     3 +-----------+
     4 | classname |
     5 +-----------+
     6 | 初三一班  |
     7 | 初三二班  |
     8 | 初三三班  |
     9 +-----------+
    10 3 rows in set

组合查询

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

单表多次返回

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

    1 select cname1,cname2 from tname  where condition1
    2 union
    3 select cname1,cname2 from tname where condition2

多表返回同结构

将同数量结构的字段组合

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

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

总结

可以按照查询的返回类型和语句中子查询的位置两个方面来学习
注意使用 in、any、some、all的用法
无论是比较还是查询还是count，字段中有null值总会引起误解，建议建表时字段不为空，或者提供默认值。

smart哥

关注

40
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
MySQL全面瓦解11：子查询和组合查询

子查询是SQL查询中的重要一块，是我们基于多表之间进行数据聚合和判断的一种手段，使得我们的处理复杂数据更加的便捷，这一节我们主要来了解一下子查询。34 /*班级表*/7 `classid` int primary key AUTO_INCREMENT comment '班级id',8 `classname` varchar(30) DEFAULT NULL comment '班级名称'9 ) ENGINE=InnoDB comment '班级表';10。
复制链接

扫一扫