聚合函数和子查询

最新推荐文章于 2023-05-06 19:38:42 发布
最新推荐文章于 2023-05-06 19:38:42 发布
阅读量553 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 开发语言 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_67201964/article/details/128269353
版权

一 聚合函数

1.1 聚合函数的介绍
  • 什么是聚合函数
    聚合函数作用于一组数据，并对一组数据返回一个值。
    在这里插入图片描述
1.2 AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。
代码演示：
在这里插入图片描述

1.3 MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。
在这里插入图片描述

1.4 COUNT函数

  • COUNT(*)返回表中记录总数，适用于任意数据类型。
    在这里插入图片描述

  • COUNT(expr) 返回expr不为空的记录总数。
    在这里插入图片描述

  • 问题：用count(*)，count(1)，count(列名)谁好呢?
    其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
    Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好
    于具体的count(列名)。

  • 问题：能不能使用count(列名)替换count(*)?
    不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数
    据库无关，跟 NULL 和非 NULL 无关。
    说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2.1 GROUP BY 的使用

在这里插入图片描述

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

明确：WHERE一定放在FROM后面

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

在这里插入图片描述

在这里插入图片描述

2.2 使用多个列分组

在这里插入图片描述

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

在这里插入图片描述

2.3 GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所
有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

3.1 HAVING的基本使用

过滤分组：HAVING子句

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
  5. GROUP BY 后不能用 WHERE 可以用 HAVING。
  • 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。如下：
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

在这里插入图片描述

3.2 WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；
HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一
个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

优点 缺点:
在这里插入图片描述

开发中的选择：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4 SELECT的执行过程
方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页

二 子查询

1.1子查询的基本使用

  • 子查询的基本语法结构：
    在这里插入图片描述

  • 子查询（内查询）在主查询之前一次执行完成。

  • 子查询的结果被主查询（外查询）使用 。

  • ·注意事项
    子查询要包含在括号内
    将子查询放在比较条件的右侧
    单行操作符对应单行子查询，多行操作符对应多行子查询

1.2 子查询的分类

分类方式1：
我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。
分类方式2：
我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查
询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

1.2.1 单行子查询

单行子查询的比较操作符：
在这里插入图片描述

代码演示相关子查询，不相关子查询和在CASE中演示子查询：

#相关子查询
# 查询员工工资大于部门平均工资的员工
SELECT last_name,salary,department_id
FROM employees e
WHERE salary > (
                 SELECT AVG(salary)
				 FROM employees s
				 WHERE s.department_id =e.department_id
								 );

#不相关子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
                 SELECT AVG(salary)
				 FROM employees
								 );
								 

SELECT employee_id,last_name,(CASE department_id
							  WHEN (SELECT department_id
							        FROM departments d
							        WHERE location_id = 1800 ) THEN 'Canada'
							  ELSE 'USA' END ) "location"
FROM employees;
1.2.2 子查询中的空值问题
SELECT last_name, job_id
FROM employees
WHERE job_id =
				(SELECT job_id
				FROM employees
				WHERE last_name = 'Haas');
1.2.3多行子查询
  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

多行子查询的比较操作符：
在这里插入图片描述

代码演示：

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (  SELECT salary
				    FROM employees
			    	WHERE job_id = 'IT_PROG'
			  							);

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salary
				  FROM employees
			      WHERE job_id = 'IT_PROG'
										);

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary)
						   FROM employees
						   GROUP BY department_id);
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(sal) 
					  FROM (SELECT AVG(salary) sal 
							FROM employees
							GROUP BY department_id) sal_1 );
1.2.4相关子查询

相关子查询执行流程
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

在这里插入图片描述

2 EXISTS 与 NOT EXISTS关键字
  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    -----------条件返回 FALSE
    -----------继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    -----------不在子查询中继续查找
    -----------条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
Lailaaaaa
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL常见技巧：子查询 + 聚合函数
Robin_Pi的博客
02-17 2754
聚合函数 + 多字段的情况
聚合查询+联合查询+子查询
爱敲代码的三毛的博客
11-01 3940
文章目录一、聚合查询1.聚合函数二、使用步骤1.引入库2.读入数据总结 一、聚合查询 1.聚合函数 常见的统计总数，计算平均值的操作，可以用聚合函数来实现，常见的聚合函数有： 这些函数都比较简单： 示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。 二、使用步骤 1.引入库 代码如下（示例）： import numpy as np import pandas as pd import matplotlib.pyplot as plt import seaborn a
不能对包含聚合或子查询的表达式执行聚合函数_Apache Doris 查询原理
weixin_39784972的博客
12-06 1637
Doris 查询简介Doris Query 接收Doris Query ParseDoris Query AnalyzeDoris Query RewriteDoris Query 单机PlanDoris Query 分布式PlanDoris Query 调度Doris Query 执行总结Doris 查询简介Doris 的查询和大多数数据库一样，需要经过 Parse,Analyze,O...
聚合函数作子查询以及聚合运算原理
qq_40333348的博客
01-31 2661
常用的聚合函数有 avg、sum、max、min、count等等，它们的用法非常简单，在这里不再单独介绍。 1、聚合函数作子查询 我们常常用mysql作聚合查询，例如：select max(id) as notdog from student where lover is not null; 但是我们像获得max（id）下的完整信息该怎么办呢，这时候可以嵌套查询，即select * fr
mysql子查询、聚合函数
程序三两行
07-19 875
mysql子查询
mysql带有聚合函数的子查询怎么优化_SQL中子查询为聚合函数时的优化
weixin_31080559的博客
02-10 332
测试数据：create table test1 as select * from dba_objects where rownum<=10000;--10000条记录create table test2 as select * from dba_objects;--13438条记录分析执行计划：SQL1：SQL> select *2 from test3 where obje...
Eclipse Birt的列绑定错误
01-15 181
nnot be used in column bindings of subquery. [/color]翻译一下大概是："父查询绑定列包含了聚合函数不能被用于子查询绑定列",什么父查询子查询？晕了！！ goole一下吧，只找到一篇相关的文章看了一下大概是说"原因是表格上的字段绑定，必需设置bingding到table,而不能设置binding到data元素",跟我这个完全没有关系,不过受到启发我查看了一下我的报表结构，一个Gird中放了一个Chart,G
数据库的高级查询二：聚合函数（SUM、MAX，MIN，AVG，COUNT）
热门推荐
小枯林的博客
04-01 1万+
聚合函数又称作汇总函数，是用来做简单的数据统计的；比如统计员工表里的平均工资，员工表里一共有多少条记录等； 目录 一：聚合函数简介 二：聚合函数详解 1.SUM函数：求和函数 2.MAX函数：求最大值 3.MIN函数：求最小值 4.AVG函数：求平均值 5.COUNT函数：求记录数量 一：聚合函数简介 二：聚合函数详解 1.SUM函数：求和函数 如： 2.MAX函数：求最大值 3.MIN函数：求最小值 4.AVG函数：求平均值 5.CO
SQL中的子查询——可以结合聚合函数
汪雯琦的博客
03-29 3024
文章目录1. 子查询的介绍2. 子查询的使用3. 小结 1. 子查询的介绍 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询. 主查询和子查询的关系: 1.子查询是嵌入到主查询中 2.子查询是辅助主查询的,要么充当条件,要么充当数据源 3.子查询是可以独立存在的语句,是一条完整的 select...
MySQL单表查询、聚合函数、多表连接查询、子查询
yangenguang的博客
07-08 549
文章目录单表查询聚合函数多表查询（select查询的是虚拟表）连接查询多表连接查询案例多表连接查询之物理表和虚拟表链接子查询带in关键字的子查询带ANY关键字的子查询带ALL关键字的子查询带比较运算符的子查询带EXISTS关键字的子查询逻辑梳理图作业 单表查询 select distinct 字段1,字段2,... from 库.表 where 过滤条件 group by 分组字段 having 过滤条件 order by 排序字段 limit 条数 聚合
sql查询语句执行顺序
weixin_33794672的博客
03-12 109
如下的sql查询语句执行顺序 (1)from (3) join (2) on (4) where (5)group by (6) with (7)having (8) select (9) distinct (10) order by 从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的...
sql聚合函数,子查询
weixin_73744913的博客
05-06 687
创建一张表，该表中记录了每个科目老师的工资情况，要求展示，最高，最低，总和和平均工资以及该科目的id。将结果集中所有的记录按照指定的字段进行统计并最终得到一条结果。查看每个科目老师的平均工资，前提是该科目老师最高工资要超过。此时会将结果集按照这些字段值组合相同的记录看作一组。那么班级号相同的记录就可以看作同一个班的学 生,子句可以将结果集按照指定的字段值相同的记录分组。未知条件：科目2和科目4老师的最高工资是多少？该子查询的结果集就是我们期望的表的样子。查看工资最高的老师的工资和奖金是多少。
【转】聚合函数、分组、子查询及组合查询
beidaol的专栏
04-19 249
转自：https://www.cnblogs.com/ghost-xyx/p/3811036.html 聚合函数： SQL中提供的聚合函数可以用来统计、求和、求最值等等。 分类： –COUNT：统计行数量 –SUM：获取单个列的合计值 –AVG：计算某个列的平均值 –MAX：计算列的最大值 –MIN：计算列的最小值 执行列、行计数（count）： 标准格式 SELECT...
SQL语句汇总（三）——聚合函数、分组、子查询及组合查询
weixin_30571465的博客
06-28 6520
拖了一个星期，终于开始写第三篇了。走起！ 聚合函数： SQL中提供的聚合函数可以用来统计、求和、求最值等等。 分类： –COUNT：统计行数量 –SUM：获取单个列的合计值 –AVG：计算某个列的平均值 –MAX：计算列的最大值 –MIN：计算列的最小值 首先，创建数据表如下： 执行列、行计数（count）： 标准格式 SELECT CO...
ORCALE之子查询和常用函数
weixin_40645193的博客
07-22 151
子查询 子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有： 单行子查询：不向外部返回结果，或者只返回一行结果。 多行子查询：向外部返回零行、一行或者多行结果。 例： 查询出销售部（SALES）下面的员工姓名，工作，工资。 SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE DEPTNO=(SELECT DEPTNO FROM D
多表查询、子查询、聚合函数
rainy_killer的博客
04-10 1516
一、多表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; notice：下列代码参照上条博客用户和身份证关联（一对一） 1、交叉连接 第一个表中的所有行乘以第二个表中的所有行，也就是笛卡尔积 -- 自然连接 -- 隐式语法 select * from card,users; -- 显式语法 select * from card c...
聚合函数查询
weixin_30904593的博客
10-14 446
聚合函数的最大特点是：它们根据一组数据求出一个值。聚合函数的结果值只根据选定行中非null的值进行计算，null值被忽略。 一、count()函数 count函数用于对除*以外的任何参数，返回选择集合中非null值的行的数目； 对于参数*，返回选择集合中所有行的数目，包含null值的行。没有where 子句的count（*）是经过内部优化的，，能快速返回表中所有的记录总数； select ...
mysql聚合函数查询，子查询，关联查询
weixin_42467346的博客
01-25 510
聚合函数 MAX() MIN() COUNT() SUM() AVG() 求最高成绩 from django.db.models import Max Stu.objects.aggregate(Max('score')) {'score__max': 99} showsql() SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`...
hive子查询和聚合函数
最新发布
11-17
下面是一个使用聚合函数和GROUP BY进行数据运算聚合的例子： ```sql SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 5000; ``` 上述语句的执行逻辑如下...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值