最全Oracle数据库学习笔记 （四 —— select 从入门到放弃 【下】，MySQL索引为何选择B+树

总结

在清楚了各个大厂的面试重点之后，就能很好的提高你刷题以及面试准备的效率，接下来小编也为大家准备了最新的互联网大厂资料。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

• 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);

–

分页查询

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

最后总结我的面试经验

2021年的金三银四一眨眼就到了，对于很多人来说是跳槽的好机会，大厂面试远没有我们想的那么困难，摆好心态，做好准备，你也可以的。

另外，面试中遇到不会的问题不妨尝试讲讲自己的思路，因为有些问题不是考察我们的编程能力，而是逻辑思维表达能力；最后平时要进行自我分析与评价，做好职业规划，不断摸索，提高自己的编程能力和抽象思维能力。

BAT面试经验

实战系列：Spring全家桶+Redis等

其他相关的电子书：源码+调优

面试真题：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

，做好职业规划，不断摸索，提高自己的编程能力和抽象思维能力。

[外链图片转存中…(img-k3CMHTcJ-1715579021848)]

BAT面试经验

实战系列：Spring全家桶+Redis等

[外链图片转存中…(img-364bmIC4-1715579021848)]

其他相关的电子书：源码+调优

[外链图片转存中…(img-UMAj7zXv-1715579021849)]

面试真题：

[外链图片转存中…(img-yPgkDhnT-1715579021849)]

[外链图片转存中…(img-z2E1VtVx-1715579021849)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取