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 如果不使用分组,则只能单独的使用组函数

  1. 使用分组函数的时候,不能出现分组函数和分组条件以外的字段。
二、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开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。img

既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!

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

如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)

img

最后

由于篇幅限制,小编在此截出几张知识讲解的图解

P8级大佬整理在Github上45K+star手册,吃透消化,面试跳槽不心慌

P8级大佬整理在Github上45K+star手册,吃透消化,面试跳槽不心慌

P8级大佬整理在Github上45K+star手册,吃透消化,面试跳槽不心慌

P8级大佬整理在Github上45K+star手册,吃透消化,面试跳槽不心慌

P8级大佬整理在Github上45K+star手册,吃透消化,面试跳槽不心慌

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!**

如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)

img

最后

由于篇幅限制,小编在此截出几张知识讲解的图解

[外链图片转存中…(img-xQtPSbU3-1713347535552)]

[外链图片转存中…(img-6ZQVkU5W-1713347535552)]

[外链图片转存中…(img-yznfobkT-1713347535552)]

[外链图片转存中…(img-Y0vDIn0o-1713347535553)]

[外链图片转存中…(img-XW3JSfBJ-1713347535553)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值