MySQL 子查询和分组查询

概述

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

/*学生表:这边假设学生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后面,仅仅支持标量子查询,即只能返回一个单值数据。比如上面的学生班级表,我们查询每个班级的学生数量,可以这么写:

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种方式:标量子查询(单行单列行子查询);列子查询(单列多行子查询)行子查询(多行多列);

他有如下共同的特点:

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

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,外层(也就是前面的语句)才会执行,否则不执行。

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

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

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

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

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 查询的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL之select in 子查询优化的实现

    下面的演示基于MySQL5.7.27版本 一.关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询,优化器会选择不同的策略. 1. 对于 IN.=ANY 子查询,优化器有如下策略选择: semijoin Materialization exists 2. 对于 NOT IN.<>ALL 子查询,优化器有如下策略选择: Materialization exists 3. 对于 derived 派生表,优化器有如下策略选择: derived_merge,将派生表合并到外部查询

  • mysql连接查询、联合查询、子查询原理与用法实例详解

    本文实例讲述了mysql连接查询.联合查询.子查询原理与用法.分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询: 连接查询就是将多个表联合起来查询,连接查询方式有内连接.外连接.自然连接.交叉连接.连接查询使得可以同时查看多张表中数据. 内连接:有条件连接,多个表之间依据指定条件连接,匹配结果是保留符合匹配结果的记录. 外连接:与内连接不同的是不管匹配符不符合都保留,根据外连接

  • mysql实现多表关联统计(子查询统计)示例

    本文实例讲述了mysql实现多表关联统计的方法.分享给大家供大家参考,具体如下: 需求: 统计每本书打赏金额,不同时间的充值数据统计,消费统计, 设计四个表,book 书本表,orders 订单表  reward_log打赏表   consume_log 消费表 ,通过book_id与book表关联, 问题: 当关联超过两张表时导致统计时数据重复,只好用子查询查出来,子查询只能查一个字段,这里用CONCAT_WS函数将多个字段其拼接 实现: 查询代码如下 SELECT b.id, b.book_

  • MySQL多表查询实例详解【链接查询、子查询等】

    本文实例讲述了MySQL多表查询.分享给大家供大家参考,具体如下: 准备工作:准备两张表,部门表(department).员工表(employee) create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'ma

  • MySQL分组查询Group By实现原理详解

    由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作.当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算.所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引. 在MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用.下面我们分别针对这三种实现方式做一个分

  • MySQL 分组查询和聚合函数

    概述 相信我们经常会遇到这样的场景:想要了解双十一天猫购买化妆品的人员中平均消费额度是多少(这可能有利于对商品价格区间的定位):或者不同年龄段的化妆品消费占比是多少(这可能有助于对商品备货量的预估). 这个时候就要用到分组查询,分组查询的目的是为了把数据分成多个逻辑组(购买化妆品的人员是一个组,不同年龄段购买化妆品的人员也是组),并对每个组进行聚合计算的过程:. 分组查询的语法格式如下: select cname, group_fun,... from tname [where conditio

  • MySql中流程控制函数/统计函数/分组查询用法解析

    路漫漫其修远兮,吾将上下而求索,又到了周末,我继续带各位看官学习回顾Mysql知识. 上次说到了流程控制函数,那就从流程控制函数来继续学习吧! #五.流程控制函数 #1.if函数:if else的效果 IF(条件表达式,成立返回1,不成立返回2) #与Java三元运算相同 SELECT IF(10>5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金呵呵','有奖金嘻嘻') AS 备注 FROM em

  • MySql中子查询内查询示例详解

    西北望乡何处是,东南见月几回圆. 月亮又慢悠悠的挂上了天空,趁着睡前梦呓,我就带领各位可爱的读者们探索MySql最后的子查询部分. 说明:有些查询结果出来结果截图与题目要求不一样会出现多余的字段是为了方便展示结果的可读性.实际操作的读者可以删除SELECT后面多余的字段得到正确的结果. #WHERE或HAVING后面 #1.标量子查询(单行子查询) #2.列子查询(多行子查询) #3.行子查询(多列多行) #特点: # ①子查询放在小括号内 # ②子查询一般放在条件的右侧 # ③标量子查询:一般

  • 详解MySQL子查询(嵌套查询)、联结表、组合查询

    一.子查询 MySQL 4.1版本及以上支持子查询 子查询:嵌套在其他查询中的查询. 子查询的作用: 1.进行过滤: 实例1:检索订购物品TNT2的所有客户的ID = + 一般,在WHERE子句中对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询. 注意:列必须匹配 --在WHERE子句中使用子查询(如这里所示),应该保证SELECT语句具有与WHERE子句中相同数目的列.通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列. 示例2:返回订购

  • 详解MySQL中的分组查询与连接查询语句

    分组查询 group by group by 属性名 [having 条件表达式][ with rollup] "属性名 "指按照该字段值进行分组:"having 条件表达式 "用来限制分组后的显示,满足条件的结果将被显示:with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和. 1)单独使用 group by 单独使用,查询结果只显示一个分组的一条记录. 实例: select * from employee group by sex;

随机推荐