-----高级查询
---分组函数
分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组
-- 分组函数常用到以下五个函数
-- min() max() sum() avg() count()
-- 分组函数中空值处理
除了count(*)之外，其它所有分组函数都会忽略列中的空值然后再进行计算
-- ifnull()函数
在分组函数中使用ifnull函数
ifnull函数可以使分组函数强制包含含有空值的记录
select avg(ifnull(comm,0))
from emp;//求奖金的平均值 如果奖金为空用0代替
练习练习
1.查询部门20的员工，每个月的工资总和及平均工资
select deptno,avg(sal),sum(sal)
from emp where deptno=20;
2.查询工作在CHICAGO的员工人数，最高工资及最低工资
select min(sal),max(sal),count(*)from emp
natural join dept
where loc='CHICAGO';
3.查询员工表中一共有几种岗位类型
两种写法
select count(distinct job)from emp;
或者
select count(*) from (select e.job from emp e group by job) as e;
4.查询部门人数大于2的部门编号，部门名称，部门人数。
select deptno,dname,count(*)
from emp
natural join dept group by deptno
having count(*)>2;
5.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
select deptno,dname,count(*),avg(sal)
from emp
natural join dept group by deptno
having count(*)>2 and avg(sal)>2000
order by count(*);
---group by 所指定的列并不是必须出现在select列表中
----- 子查询
select select_list
from table
where expr operator
(select select_list from table);
--括号内的查询叫做子查询，也叫内部查询，先于主查询执行
--子查询的结果被主查询（外部查询）使用
--expr operator 包括比较运算符
--单行运算符:>,=,>=,<,<>不等于,<=
--多行运算符:in ,any ,all
-- 子查询可以嵌于以下SQL语句中：where子句，having子句，from子句
--子查询要使用括号括起来
--将子查询放在比较运算符的右边
--子查询只返回一行一列使用单行运算符
--重点where不能操作聚合函数（）
--练习练习
1.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select ename,hiredate
from emp
where hiredate<(select min(hiredate) from emp where deptno=20);
2.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
select emp.deptno,dname,count(emp.deptno)
from emp join dept on emp.deptno=dept.deptno
group by deptno
having count(ename)>(select count(*)/count(distinct emp.deptno) from emp);
----- 多行子查询(in any all)
--in使用 in操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同
练习练习
查询是经理的员工姓名，工资
select ename,sal
from emp
where empno
in (select mgr from emp);
--any使用
--any:表示和子查询的任意一行结果进行比较，有一个满足条件即可
<any:表示小于子查询结果集中的任意一个，即小于最大值就可以
>any:表示大于子查询结果集中的任意一个，即大于最小值就可以
=any:表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN
练习练习
查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资
select empno,ename,job,sal
from emp
where sal >any(select sal from emp where deptno=10)
and deptno<>10;
--all使用
--all:表示和子查询的所有行结果进行比较，每一行必须都满足条件
<all:表示小于子查询结果集中的所有行，即小于最小值
>all:表示大于子查询结果集中的所有行，即大于最大值
=all:表示等于子查询结果集中的所有行，即等于所有值，通常无意义。
练习练习
查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，姓名，职位，工资
select empno,ename,job,sal
from emp
where sal<all(select sal from emp where deptno =10)
and deptno<>10;
-- 在from子句中使用子查询
练习练习
查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
select a.ename,a.sal,a.deptno,b.salavg
from emp a ,(select deptno,avg(sal) salavg from emp group by deptno) b
where a.deptno=b.deptno
and a.sal>b.salavg;
image.png
