您的位置：首页 > 数据库 > > 正文

mysql子查询用法（MySQL 子查询和分组查询）

更多 时间：2022-03-29 10:03:36 类别：数据库 浏览量：1211

mysql子查询用法

MySQL 子查询和分组查询

 

概述

子查询是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
    		•

    •  

    行子查询说明

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
    		•
  • 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,外层（也就是前面的语句）才会执行，否则不执行。

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

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

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

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

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

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
    		•
  • 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）。

     

    单表多次返回

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

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

    •  

    多表返回同结构

    将同数量结构的字段组合

  • ?
  • 1
  • 2
  • 3
    		•
  • 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 查询的资料请关注开心学习网其它相关文章！

    原文链接：https://www.cnblogs.com/wzh2010/p/13843036.html?utm_source=tuicool&utm_medium=referral

    标签：mysql 子查询 分组查询
  • 上一篇：react组件封装成函数方法（React虚拟列表的实现）
  • 下一篇：pythonsocket建立多用户通讯（Python socket实现多对多全双工通信的方法）
    • 您可能感兴趣
    首页 编程学习 Web前端 数据库 软件设计
    开心学习 ©2013-2021 保留所有权利