MySQL 子查询和分组查询
作者:翁智华 发布时间:2024-01-18 22:05:36
目录
概述
子查询
子查询分类
按照查询的返回结果
按子查询位置区分
select后子查询
from后子查询
where和having型的子查询
单个标量子查询应用
多个标量子查询应用
子查询+分组函数
列子查询说明
行子查询说明
exists子查询
组合查询
单表多次返回
多表返回同结构
总结
概述
子查询是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值总会引起误解,建议建表时字段不为空,或者提供默认值。
来源:https://www.cnblogs.com/wzh2010/p/13843036.html?utm_source=tuicool&utm_medium=referral


猜你喜欢
- 首先说一个小技巧,True可看作1,False可看作0 ,并且可以参与运算!正文开始!!! 一、map()map(func,ite
- 最近在写测试平台,需要实现一个节点服务器的api,正好在用django,准备使用djangorestframework插件实现。需求实现一个
- 快捷键1、编辑(Editing)Ctrl + Space 基本的代码完成(类、方法、属性)Ctrl + Alt + Space 快速导入任意
- plt.imshow(image)无法显示图片的解决使用plt.imshow()发现不能显示图片,加了plt.show()也还是不能显示先引
- 基本要点:程序中储存的所有数据都是对象(可变对象:值可以修改 不可变对象:值不可修改)每个对象都有一个身份、一个类型、一个值例: >&
- 如下所示:# u [32,30,200]# u_logits [400,32,30]q_j_400 = [] for j in range(
- 我们可以先建立一个包含文件名,文件标题的待检索文件的数据库,然后,用ADO方式来访问它,并建立记录集对象。具体代码和说明见下:
- 本文实例分析了Python中的异常处理try/except/finally/raise用法。分享给大家供大家参考,具体如下:异常发生在程序执
- 总的来说视觉设计是一个很大的范畴,囊括了我们身边很多产品的再创作设计,比如工业产品设计,广告设计,新媒体设计,服饰设计,还有我们这里要讨论的
- 一、创建飞书机器人自定义飞书机器人操作步骤,具体详见飞书官方文档:《机器人 | 如何在群聊中使用机器人?》二、调用飞书发送消息自定义机器人添
- 如果按本文操作遇到一些问题报错,如C:\Users\milyyy\AppData\Roaming\npm-cache\_logs\2018-
- 本系列不会对python语法,理论作详细说明;所以不是一个学习教材;而这里只是我一个学习python的某些专题的总结。1. random()
- csv是Comma-Separated Values的缩写,是用文本文件形式储存的表格数据,比如如下的表格:就可以存储为csv文件,文件内容
- 1.在查询分析器理启动或停止SQL Agent服务 启动 use master go xp_cmdshell
- 一、前言相关知识来自《python算法设计与分析》。初级排序算法是指几种较为基础且容易理解的排序算法。初级排序算法包括插入排序、选择排序和冒
- 如下所示:# -*- coding: utf-8 -*-# @Time : 2018/1/17 16:37# @Author :
- 目录一.前提二.token加密与解密三.视图CBV四.framework认证功能五.利用postman软件在前端提交一.前提首先是这个代码基
- 有些时候我们需要得到刚刚插入数据库中的条目id,用于相关系统的更改,在以前我是使用select+top 1+desc 这种笨方法实现的,在没
- 有很多时候,我们会在python的运行过程中得到一些重要的变量,比如一个数据量很庞大的dict。而且,后面的某些程序也会用到这个dict,那
- PHP hebrev() 函数实例反向显示希伯来字符:<?php echo hebrev("á çù&