18 高级查询(分组函数)

18.1分组函数概述

请思考如下问题？查询所有员工的每个月工资总和，平均工资？查询工资最高和最低的工资是多少？查询公司的总人数？查询有奖金的总人数？

分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件

18.1 为什么使用分组函数

单行函数和分组函数区别

分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。分组函数常用到以下五个函数：

MIN
MAX
SUM
AVG
COUNT

18.2 分组函数

MIN函数和MAX函数
MIN和MAX函数主要是返回每组的最小值和最大值。
MIN([DISTINCT|ALL] column|expression)
MAX([DISTINCT|ALL] column|expression)
MIN和MAX可以用于任何数据类型
查询入职日期最早和最晚的日期

SUM函数和AVG函数
SUM和AVG函数分别返回每组的总和及平均值。
SUM([DISTINCT|ALL] column|expression)
AVG([DISTINCT|ALL] column|expression)
SUM和AVG函数都是只能够对数值类型的列或表达式操作。

需求1：员工中工资最小(min)的值,最大max,求平均工资avg，求总工资sum（）

SELECT 
  MAX(sal) 最高工资,
  MIN(sal) 最低工资,
  AVG(sal) 平均工资,
  SUM(sal) 总工资 
FROM
  emp

COUNT函数
COUNT函数的主要功能是返回满足条件的每组记录条数。
COUNT( *|{[DISTINCT|ALL] column|expression})

组函数中DISTINCT
DISTINCT会消除重复记录后再使用组函数

sql

-- 总条数 15  count(*) 15 , COUNT(1)
SELECT COUNT(*) FROM emp 
-- 配合分页查询 ，查询总条数
SELECT COUNT(1) FROM emp

除了COUNT（*）,count(常量) 之外，其它所有分组函数都会忽略列中的空值，然后再进行计算 null值不会计算
在分组函数中使用IFNULL函数，IFNULL 函数可以使分组函数强制包含含有空值的记录

18.2.1 练习

查询部门20的员工，每个月的工资总和及平均工资。
查询工作在CHICAGO的员工人数，最高工资及最低工资。
查询员工表中一共有几种岗位类型。
答案

sql

-- 1. 查询部门20的员工，每个月的工资总和及平均工资。

SELECT SUM(sal) 工资总和,AVG(sal) 平均工资 FROM emp WHERE deptno = 20



-- 2. 查询工作在CHICAGO的员工人数，最高工资及最低工资。
SELECT 
COUNT(1) 人数 ,
  MAX(sal) 最高工资,
  MIN(sal) 最低工资 
FROM
  emp 
  JOIN dept 
    ON emp.deptno = dept.deptno 
WHERE loc = 'CHICAGO' 


-- 3. 查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT job) FROM emp

18.3 GROUP BY子句

通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组其中GROUP BY子句指定要分组的列,

查询每个部门的编号，平均工资,最大工资、最小工资，部门人数

sql

SELECT deptno , MAX(sal) ,MIN(sal),AVG(sal) ,COUNT(1)  FROM emp GROUP BY deptno

GROUP BY 可以使用多个字段进行分组

sql

 SELECT   deptno, job, SUM(sal)
 FROM     emp
GROUP BY deptno, job;

18.3.1 group by 练习

查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

sql

-- 1. 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。


select 
  dept.deptno,
  dname,
  count(1) 部门人数,
  max(sal) 最高工资,
  min(sal) 最低工资,
  sum(sal) 工资总和,
  avg(sal)  平均工资
from
  emp 
  join dept 
    on emp.`deptno` = dept.`deptno` 
group by dept.deptno ,dname  




-- 2. 查询每个部门，每个岗位的部门编号，部门名称，岗位名称及对应的人数、最高工资、最低工资、工资总和、平均工资。
SELECT 
  dept.deptno,
  dname,
  job,
  COUNT(1) 部门人数,
  MAX(sal) 最高工资,
  MIN(sal) 最低工资,
  SUM(sal) 工资总和,
  AVG(sal)  平均工资
FROM
  emp 
  JOIN dept 
    ON emp.`deptno` = dept.`deptno` 
GROUP BY dept.deptno,   dname  ,job


-- 3. 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
select mgr.empno , mgr.ename , count(1) 人数  from emp mgr left join emp e1  on mgr.empno =  e1.mgr
group by mgr.empno

18.4 HAVING子句

使用 HAVING 子句限制组记录已经分组. 使用过组函数. 与 HAVING 子句匹配的结果才输出

sql

-- 按照部门分组，每个部门自高工资大于4000的部门的最高工资
SELECT 
  deptno,
  MAX(sal) 
FROM
  emp 
GROUP BY deptno 
HAVING MAX(sal) > 4000

18.5 SELECT语句执行过程

在基础表上过滤， where条件生效，针对的是单行数据过滤
在过滤之后的数据上分组，group by
可以在分组的基础上，过滤 having 子句生效
枚举select 中的字段
order by子句生效，针对查询结果排序

18.7 练习

查询部门人数大于2的部门编号，部门名称，部门人数。
查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

答案:

sql

-- 1.查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT 
  dept.deptno,
  dname,
  COUNT(1) 
FROM
  dept,
  emp 
WHERE dept.deptno = emp.deptno 
GROUP BY dept.deptno,
  dname 
HAVING COUNT(1) > 3 

-- 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，
-- 并按照部门人数升序排序。

SELECT 
  dept.deptno,
  dname,
  COUNT(1) ,
  AVG(sal)
FROM
  dept,
  emp 
WHERE dept.deptno = emp.deptno 
GROUP BY dept.deptno,
  dname 
HAVING COUNT(1) > 3  AND AVG(sal) > 2000

18.6 子查询概述

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

子查询中常用的运算符

1) IN子查询

结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为：

<表达式> [NOT] IN <子查询>

语法说明如下。

<表达式>：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
<子查询>：用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用 SELECT 语句实现子查询的多层嵌套。

2) 比较运算符子查询

比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为：

<表达式> {= | < | > | >= | <= | <=> | < > | != } { ALL | SOME | ANY} <子查询>

语法说明如下。

<子查询>：用于指定子查询。
<表达式>：用于指定要进行比较的表达式。
ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。其中，关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回 TRUE，否则返回 FALSE；关键字 SOME 和 ANY 是同义词，表示表达式只要与子查询结果集中的某个值满足比较关系，就返回 TRUE，否则返回 FALSE。

3) EXIST子查询

关键字 EXIST 所使用的子查询主要用于判断子查询的结果集是否为空。其语法格式为：

EXIST <子查询>

若子查询的结果集不为空，则返回 TRUE；否则返回 FALSE。

子查询可以嵌于以下SQL子句中：

WHERE子句
HAVING子句
FROM子句

18.7单行子查询

子查询只返回一行一列使用单行运算符

练习
1.查询入职日期最早的员工姓名，入职日期
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

答案：

sql

-- 1.查询入职日期最早的员工姓名，入职日期
SELECT ename 员工姓名 ,hiredate 入职日期 FROM  emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp  )


-- 2.查询工资比MARTIN工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
SELECT 
  * 
FROM
  emp 
  INNER JOIN dept 
    ON emp.deptno = dept.deptno 
WHERE sal > (SELECT sal FROM emp WHERE ename ='MARTIN')
  AND loc = 'CHICAGO' 


-- 3.查询入职日期比30部门入职日期最早的员工还要早的员工姓名，入职日期
SELECT 
  ename 员工姓名,
  hiredate 入职日期 
FROM
  emp 
WHERE hiredate < 
  (SELECT 
    MIN(hiredate) 
  FROM
    emp 
  WHERE deptno = 30)

-- 4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
SELECT 
  dept.deptno  ,dname  ,COUNT(1)
FROM
  emp 
  INNER JOIN dept 
    ON emp.deptno = dept.deptno 
    GROUP BY   dept.deptno  ,dname 
    HAVING COUNT(1) > (   SELECT COUNT(deptno)/COUNT(DISTINCT deptno) FROM emp     )

18.8多行子查询

多行子查询子查询返回记录的条数可以是一条或多条。和多行子查询进行比较时，需要使用多行操作符，多行操作符包括： IN ANY ALL

IN操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

ANY的使用 ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。 < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。
ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。
= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

ALL的使用 ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。
< ALL:表示小于子查询结果集中的所有行，即小于最小值。
ALL:表示大于子查询结果集中的所有行，即大于最大值。
= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

练习

查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
查询入职日期比20部门所有员工晚的员工姓名、入职日期，不包括10部门员工
查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

答案:

sql

-- 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

SELECT 
  * 
FROM
  emp 
WHERE deptno <> 10 
  AND hiredate > ANY 
  (SELECT 
    hiredate 
  FROM
    emp 
  WHERE deptno = 10)


-- 2.查询入职日期比20部门所有员工晚的员工姓名、入职日期，不包括20部门员工
SELECT 
  * 
FROM
  emp 
WHERE deptno <> 20 
  AND hiredate > ALL 
  (SELECT 
    hiredate 
  FROM
    emp 
  WHERE deptno = 20)

-- 3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

SELECT 
  ename, job  
FROM
  emp 
WHERE deptno <> 10 
AND job = ANY (SELECT job  FROM emp WHERE deptno = 10 )

18.9 在FROM子句中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

sql

SELECT 
  ename,
  sal,
  emp.deptno,
  b.salavg 
FROM
  emp,
  (SELECT 
    deptno,
    AVG(sal) salavg 
  FROM
    emp 
  GROUP BY deptno) b 
WHERE emp.deptno = b.deptno 
  AND emp.sal > b.salavg

18.10. exists not Exists

查询存在 SELEMAN职位的部门编号、名称、所在地

18.11 练习

sql

CREATE TABLE `detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `pname` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `price` decimal(6,2) DEFAULT NULL COMMENT '单价',
  `count` int(11) DEFAULT NULL COMMENT '数量',
  `amount` decimal(6,2) DEFAULT NULL COMMENT '商品总金额',
  `order_num` varchar(100) DEFAULT NULL COMMENT '订单编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;


insert  into `detail`(`id`,`pname`,`price`,`count`,`amount`,`order_num`) values (1,'苹果手机','5000.05',1,'5000.05','T00001'),(2,'通讯话费','100.00',2,'200.00','T00001'),(3,'富士苹果','5.00',3,'15.00','T00002'),(4,'华为手机','4000.00',2,'8000.00','T00002'),(5,'红牛饮料','5.00',10,'50.00','T00003');


DROP TABLE IF EXISTS `myorder`;

CREATE TABLE `myorder` (
  `order_num` varchar(100) NOT NULL COMMENT '订单号',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `amount` decimal(6,2) DEFAULT NULL COMMENT '订单总金额',
  PRIMARY KEY (`order_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


insert  into `myorder`(`order_num`,`ctime`,`amount`) values ('T00001','2021-08-26 15:14:53','100.02'),('T00002','2021-08-26 15:14:56','5000.80'),('T00003','2021-08-26 15:15:01','300.20'),('T00004','2021-08-26 15:15:09','500.90'),('T00005','2021-08-26 15:15:35','0.00');

18 高级查询(分组函数) ​

18.1分组函数概述 ​

18.1 为什么使用分组函数 ​

18.2 分组函数 ​

18.2.1 练习 ​

18.3 GROUP BY子句 ​

18.3.1 group by 练习 ​

18.4 HAVING子句 ​

18.5 SELECT语句执行过程 ​

18.7 练习 ​

18.6 子查询概述 ​

子查询中常用的运算符 ​

1) IN子查询 ​

2) 比较运算符子查询 ​

3) EXIST子查询 ​

18.7单行子查询 ​

18.8多行子查询 ​

18.9 在FROM子句中使用子查询 ​

18.10. exists not Exists ​

18.11 练习 ​