Oracle数据库学习笔记 （四 —— select 从入门到放弃 【下】）(2)

– nvl(comm,0) 值为空的时候显示0 ，否则显示本身，它可以在任何函数中使用

1.2 分组查询

• group by 的含义和作用

group by 意为 “根据（by）” 一定的规则进行分组（group）。其作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对若干小区域进行统计汇总

• group by子句的功能和使用场景

1. 用于对查询结果的分组统计

2. 常与聚合函数联合使用。

语法：

select *| 列名

from 表名

where 条件表达式

group by 分组条件 （having 过滤条件）

order by 排序列 asc|desc

eg1：求出每个部门雇员的数量，先分组再统计

select deptno,count(empno) from emp group by deptno;

eg2：求出每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

注意：

1. 分组查询只能由两部份构成，一是 group by 中出现的列

2. 另外是分组函数，除此之外，其他内容不能放在 select 后

找错误：

一、非单组函数

select deptno,count(empno) from emp;

这个会报错：ORA-00937：不是单组分组函数

原因如下：

1. 如果程序使用了分组函数，则有两种可以使用的情况

1.1 程序中 存在 group by，并指定列分组条件，这样可以将分组条件一起查询

1.2 如果不使用分组，则只能单独的使用组函数

2. 使用分组函数的时候，不能出现分组函数和分组条件以外的字段。

二、where 后面不能跟 分组函数

统计 平均工资 2000 以上的部门

SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000

GROUP BY deptno;

• 原因

分组函数只能在分组中使用，不允许出现在 where 语句之中

• 解决方案如下

使用 having，对分组后的数据进行过滤

– 使用 having 统计平均工资 2000 以上的部门

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

– 求出每个部门雇员的数量，先分组在统计

select deptno,count(empno) from emp group by deptno

– 求每个部门的平均工资

select deptno,avg(sal) from emp group by deptno

– 显示部门名称，各部门员工数，平均工资

select d.dname,count(e.empno),avg(e.sal)

from emp e, dept d

where e.deptno = d.deptno

group by e.deptno

分组的原则

• 只要一列上存在重复的内容才能考虑分组

• select 后面出现查询列，要么是分组条件，要么是分组函数

分组函数只能出现在 select 语句的列里，或者 having、order by子句中，如果在 select 语句中同时包含有 group by、having、order by 子句，那么必须是 group by、再having 再 order by

比如下面的句子：

select avg(sal), max(sal), deptno

from emp

group by deptno

having avg(sal) < 6000

order by avg(sal);

• 多字段分组原则

使用 group by 可以根据多个字段进行分组

分组层次从左到右，即先按第一个字段分组，然后再第一个字段值相同的记录中，再根据第二个字段的值进行分组

eg：获取同一个部门下，同一个上司下的人数

select deptno, mgr, count(*) from emp group by deptno, mgr;

小测验

– 1 查询入职最早的员工日期

select min(hiredate) from emp;

– 2 求每个部门员工数量

select count(empno),deptno from emp group by deptno;

– 3 统计各个部门的员工数及平均工资

select d.dname,count(e.empno),avg(e.sal) from emp e,dept d

where e.deptno = d.deptno

group by d.deptno = d.dname

– 4 显示非销售人员工作名称雇员的月工资综合，并且要满足从事同一工作的雇员的月工资合计大于 5000，输出结果按月合计升序排列

select job,sum(sal) sal_num

from emp

where job <> ‘SALESMAN’

group by job

having sum(sal)>5000

order by sal_num;

– 5 统计每年入职的人数、鱼粉、人数

select to_char(hiredate,‘yyyy’) 年份,count(empno) 人数 from emp group by to_char(hiredate,‘yyyy’);

– 6 统计每年入职的人数：年份，人数（仅返回入职不少于2人的年份的数据）

select to_char(hiredate,‘yyyy’) 年份,count(empno) 人数

from emp

group by to_char(hiredate,‘yyyy’)

having count(empno)>=2

1.3 子查询

给一个场景，查询工资比 SCOTT 高的人员信息

select * from emp where sal>(select sal from emp where ename = ‘SCOTT’)

一般要将子查询放在括号内，将子查询放在比较条件的右侧

一、子查询语法

SELECT *｜列名 FROM 表名1 别名1，表名2 别名2，。。。

（

SELECT *｜列名

FROM 表名

WHERE 条件表达式

GROUP BY 分组条件

ORDER BY 排序列 ASC|DESC

）别名，…

WHERE 列 运算符

（

SELECT *｜列名

FROM 表名

WHERE 条件表达式

GROUP BY 分组条件

ORDER BY 排序列 ASC|DESC

）

GROUP BY 分组条件

ORDER BY 排序列 ASC|DESC

二、子查询类型

• 子查询可以分为三类

• 单列子查询：返回结果是一列中的一个内容，出现几率最高

• 单行子查询：返回多个列，有可能是一条完整的记录

• 多行子查询：返回多条记录

2.1 单行子查询

– 查询工资比7654高，同时与7788从事相同工作的全部雇员信息

SELECT *

FROM emp

WHERE sal > (SELECT sal FROM emp WHERE empno = 7654)

AND job = (SELECT job FROM emp WHERE empno=7788)

– 要求查询工资最低的雇员姓名，工作，工资

select ename,job,sal from emp where sal = (select min(sal) from emp);

– 查询部门名称，部门员工数，部门平均工资，部门的最低收入雇员的姓名

– 分析：程序关联的两张表 emp dept

– 1.如果要求各个部门的员工数及平均工资，一定要分组统计，对emp按deptno 分组。

SELECT deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno;

– 2.如果想查部门名称，则要与dept表关联，用子查询。

SELECT d.dname, em.c, em.a

from dept d,

(SELECT deptno, COUNT(*) c, AVG(sal) a FROM emp GROUP BY deptno) em

WHERE d.deptno = em. deptno;

– 查询最低收入的雇员姓名

select min(sal) from emp group by deptno

– 合并后

SELECT d.dname, em.c, em.a, e.ename

from dept d,

(SELECT deptno, COUNT(*) c, AVG(sal) a, MIN(sal) m

FROM emp

GROUP BY deptno) em,

emp e

WHERE d.deptno = em. deptno

and em.m = e.sal;

– in 操作符：指定一个查询范围的集合

– 求出各部门最低收入的员工信息

select * from emp where sal in

(select min(sal) from emp group by deptno)

– 查询工资UI 10 号部门中的任意一人相等即可

select * from emp where sal in (select sal from emp where deptno =10)

– any 操作符：任意一个

– = any：与 in 操作符的功能完全一样

– >ANY: 大于任意一个，即比最小值都要大

– <ANY: 小于任意一个，比最大的值小

– 查询工资小于 10号部门中的任意一个人即可

select * from emp where sal<any(seect sal from emp where deptno = 10);

– all 操作符：去阿奴

– >all：大于全部，比最大值还大

– <all，小于全部，比最小的值还要削

– 查询工资小于全部 10 好部门的员工信息

select * from emp where sal < all (select sal from emp where deptno = 10);

–

分页查询

– 查询工资最高的前五名员工

– 方法 1

select rownum e.* from emp e where rownum <= 5 order by sal desc

– 方法 2 使用子查询

select *

from (select rownum rn,e.* from employees e order by salary desc)

where rn <= 5;

– 查询工资最高的6 - 12 条员工

– 方法 1

SELECT *

FROM (SELECT rownum row_top, tt.*

FROM (SELECT e.* FROM emp e ORDER BY sal DESC) tt)

WHERE row_top BETWEEN 6 AND 12;

– 方法 2

SELECT *

FROM (SELECT rownum row_top, tt.*

FROM (SELECT e.* FROM emp e ORDER BY sal DESC) tt)

WHERE row_top <= 12

AND row_top >= 6;

– 方法 3

SELECT *

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。**

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

[外链图片转存中…(img-xn86gtsW-1715865709256)]

[外链图片转存中…(img-pqzL3cwi-1715865709257)]

[外链图片转存中…(img-RwA54HdV-1715865709257)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！