按子查询位置区分
============
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 ❤️);
2 ±----------±------------±------±--------+
3 | studentid | studentname | score | classid |
4 ±----------±------------±------±--------+
5 | 1 | brand | 97.5 | 1 |
6 | 2 | helen | 96.5 | 1 |
7 | 3 | lyn | 96 | 1 |
小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!*
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
[外链图片转存中…(img-NBZ1JK6y-1710835138436)]
[外链图片转存中…(img-rMiRWLx8-1710835138437)]
[外链图片转存中…(img-ZGxNWJEb-1710835138437)]
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注Java)
[外链图片转存中…(img-ur7kI4lF-1710835138438)]