Oracle笔记（二）
Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的伙伴参考。（@来自邵永波老师整理分享）
第五章：组函数
group by 在查询表中数据的时候进行分组的关键字
思考:为什么要对数据进行分组
having 分组之后的进行进一步数据筛选的关键字
having和where的功能类似
1.组函数(分组函数)
组函数指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理；
注意: 组函数一般要结合着分组关键字group by来使用。
具体组函数:
avg 求平均值
count 计算有多少条数据
max 最大值
min 最小值
sum 求和
stddev 标准差
variance 方差
如果使用组函数的时候没有出现group by子句，则将表中所有数据作为一个大组，进行处理。
2.具体实例
使用组函数，但是不结合group分组使用
注:如果【不使用group by分组】的话,那么默认当前查询到的【所有行数据是一组】。
例如:
查询s_emp表中所有员工的平均工资
select avg(salary)
from s_emp;
查询s_emp表中共有多少条数据
select count(*)
from s_emp;
查询s_emp表中所有员工中的最大工资
select max(salary)
from s_emp;
查询s_emp表中所有员工中的最小工资
select min(salary)
from s_emp;
查询s_emp表中所有员工的工资总和
select sum(salary)
from s_emp;
查询s_emp表中所有员工的工资的标准差
select stddev(salary)
from s_emp;
查询s_emp表中所有员工的工资的方差
select variance(salary)
from s_emp;
3.sql语句完整构成与执行步骤
select ...
from ...
where ...(遍历整个表，逐行筛选)
group by ...
having ...(组级别的筛选)
order by ...
注:除了select和from之外其他的都不是必须的。
sql语句执行顺序:
1).from
2).where
3).group by分组
4).执行组函数
5).having筛选
6).order by
注意：
分组查询时，select后面除了可以跟分组函数，还可以跟组级别的信息【group by后面的列】;
如果列没有出现在group by后面，则该列不可以放在select后面；
在select后面出现的内容，除了分组函数外，其他的列必须出现在group by后面。
4.具体实例2
使用组函数，同时结合group by分组使用。
例如:
查询s_emp表中每个部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
查询s_emp表中每个部门员工的最高工资
select dept_id,max(salary)
from s_emp
group by dept_id;
查询s_emp表中每个部门员工的工资总和
select dept_id,sum(salary)
from s_emp
group by dept_id;
查询s_emp表中每个部门员工的人数
select dept_id,count(*)
from s_emp
group by dept_id;
分组之后使用各种组函数
select dept_id,count(*),min(salary),max(salary),sum(salary),avg(salary)
from s_emp
group by dept_id;
5.组函数可以出现的位置
a.select后面
b.having后面
c.order by后面
d.where后面一定【不能】出现组函数(如果出现了，调整到having后面即可)
【特别注意:】
如果select/having语句后面出现了组函数,
那么select/having/order by后面那些【没有被组函数修饰的列】，
就【必须放在group by】后面。
求部门平均工资大于1000的信息,并按照部门|平均工资排序输出
select se.dept_id,sd.name,avg(salary)
from s_emp se,s_dept sd
where se.dept_id=sd.id
group by se.dept_id,sd.name
having avg(salary)>1000
order by sd.id;
6.where和having区别
a.where和having都是做条件筛选的
b.where执行的时间比having要早
c.where后面不能出现组函数(组函数执行次序比where晚)
d.having后面可以出现组函数
e.where语句要紧跟from后面
f.having语句要紧跟group by后面
group by和having的关系:
1.group by可以单独存在,后面可以不出现having语句
2.having不能单独存在,有需要的话,必须出现在group by后面
order by语句
1.如果sql语句中需要排序,那么就一定要写在sql语句的最后面
2.order by后也可以出现组函数
7.具体实例3
使用组函数，结合group分组以及having筛选使用
例如:
查询s_emp表中部门的平均工资大于等于1400的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;
思考:上面的sql语句修改为如下,是否可以?
select dept_id,avg(salary)
from s_emp
where avg(salary)>=1400
group by dept_id;
查询s_emp表中部门的总工资大于等于4000的部门
select dept_id
from s_emp
group by dept_id
having sum(salary)>4000;
8.综合实例
使用组函数:其他实例
例1: 查询s_emp表中部门的平均工资大于等于1400的部门,并且显示出这些部门的名字,同时按照部门编号进行排序
第一步:查询出基本需求
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400
order by dept_id;
第二步:加入多表查询,并且分别给表起别名
select se.dept_id,avg(se.salary)
from s_emp se,s_dept sd
group by se.dept_id
having avg(se.salary)>=1400
order by se.dept_id;
第三步:查询出s_dept表中的部门名称,并且进行等值连接
select se.dept_id,avg(se.salary),sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id
group by se.dept_id
having avg(se.salary)>=1400;
order by se.dept_id;
第四步:select语句后出现了组函数,那么没有被组函数修饰的列放到group by分组后面
select se.dept_id,avg(se.salary),sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id
group by se.dept_id,sd.name
having avg(se.salary)>=1400
order by se.dept_id;
思考:是否可以把where后的条件筛选 转移 到having语句后面?
答案是可以的(无非条件筛选时间延迟)，但需要注意语法规则。
（注意，having后面没有组函数修饰的列，需要放到group by后面）
select se.dept_id,avg(se.salary),sd.name
from s_emp se,s_dept sd
group by se.dept_id,sd.name,sd.id
having avg(se.salary)>=1400
and se.dept_id=sd.id
order by se.dept_id;
9.多表查询案例
查询所有员工信息，同时显示所在部门名称；
查看所有员工信息，同时显示所在区域名称；
查看区域id和名字，同时显示该区域所有员工总工资；
查看每个区域员工的人数；
查询南美地区的部门数；
查询南美地区工资大于1400的员工的信息；
select se.id,se.salary,sd.name,sr.name
from s_emp se,s_dept sd,s_region sr
where se.dept_id=sd.id
and sr.id=sd.region_id
and lower(sr.name)='south america'
and se.salary>1400;
第六章：子查询(嵌套查询)
子查询，即一个select语句中嵌套了另外的一个或者多个select语句。
子查询的第一种格式:
select ...
from ...
where 列 = (select ...)
group by ...
having 列 = (select ...)
order by ...;
1.基本应用
例1：查询工资比Smith工资高的员工信息
1).提取主句
select id,last_name,salary
from s_emp
where salary>(?);
2).提取子句（求Smith的工资）
select salary
from s_emp
where last_name='Smith';
3).组合
select id,last_name,salary
from s_emp
where salary>(
select salary
from s_emp
where last_name='Smith'
);
例2：查询以N开头，名字由四个字符组成，该员工所在部门员工的基本信息
1).主句 查询员工基本信息
select id,last_name,salary,dept_id
from s_emp
where dept_id in (?);
2).子句 获取到对应的部门
select dept_id
from s_emp
where last_name like 'N___';
3).组合
select id,last_name,salary,dept_id
from s_emp
where dept_id in (
select dept_id
from s_emp
where last_name like 'N___'
);
例3：查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
1).先分析子句 求41部门平均工资
select avg(salary)
from s_emp
where dept_id=41;
select avg(salary)
from s_emp
group by dept_id
having dept_id=41;
1247.5
2).找avg(salary)>1247.5的那些部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
3).提取上述部门中员工的信息
select id,last_name,dept_id
from s_emp
where dept_id in (?);
4).组合
select id,last_name,dept_id
from s_emp
where dept_id in (
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
);
例4：查询比Smith所在部门最高工资 高 的员工的信息
1).求部门
select dept_id
from s_emp
where last_name='Smith';
2).求最高工资
select max(salary)
from s_emp
where dept_id=(?);
3).提取员工信息
select id,last_name,salary
from s_emp
where salary > (?);
4).组合
select id,last_name,salary
from s_emp
where salary > (
select max(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Smith'
)
);
例5：查询比Ngao所在部门平均工资高的部门的员工信息
1).求Ngao所在部门
select dept_id
from s_emp
where last_name='Ngao';
41
2).求41部门平均工资
select avg(salary)
from s_emp
where dept_id=41;
1247.5
3).找部门(平均工资>1247.5)
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
4).提取员工信息
select id,last_name,dept_id
from s_emp
where dept_id in (
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
);
查看员工名字为Chang的员工所在部门的名称
查看所有和Chang不同部门的员工信息
查询和Chang相同职位的其他员工信息
查询公司里面工资比 平均工资 低的员工的信息
查看Chang员工所在部门其他员工薪资总和
查看部门平均工资大于32号部门平均工资的部门id
查看薪资大于Smith所在部门平均薪资的员工
查看薪资高于Chang员工经理薪资的员工信息
查看薪资高于 Chang员工经理所在区域最低工资 的员工信息
查看部门平均工资大于Chang所在部门平均工资的员工信息
查询工资大于41号部门平均工资的员工，且该员工所在部门的平均工资也要大于41号部门的平均工资
查看员工的基本信息(id,last_name,salary,部门名称,区域名称)，要求这些员工 薪资大于Chang所在部门的平均工资或者跟Chang不在同一个部门。
select se.id,se.last_name,se.salary,sd.name,sr.name
from s_emp se,s_dept sd,s_region sr
where se.dept_id=sd.id
and sd.region_id=sr.id
and (se.salary>1050
or
sd.id!=44);
Chang部门平均工资为 1050
select avg(salary)
from s_emp
group by dept_id
having dept_id=(?);
Chang所在部门 44
select dept_id
from s_emp
where last_name='Chang';
*************************************
以下是子查询第二种情况
select
from (select ...)
where 列>(select...)
group by
having 列<(select ...)
order by;
注意：
子查询其实可以分为两种情况，第一种就是上述用法：在查询过程中，需要另一个select查询到的结果,直接拿来用；第二种用法相比较更为复杂.
在查询过程中需要用来一张不存在的表，这张表需要用户通过select子句查询的结果进行构建，然后再利用这张构建的表实现最终的查询。
注意:(可以把子查询的结果作为一张表来使用)
01.查询员工信息,这些员工的工资要比自己所在部门的平均工资高
部门平均工资表s_avgs
id avgs
10 1400
31 1250
44 1050
41 1247.5
1 1200 10 10 1400
2 800 10 10 1400
3 1100 44 44 1050
4 970 41 41 1247.5
1).获取员工部门平均工资表
select dept_id id,avg(salary) avgs
from s_emp
group by dept_id;
2).写出完整功能
select se.id,se.salary,se.dept_id,sa.avgs
from s_emp se,(?) sa
where se.dept_id = sa.id
and se.salary>sa.avgs;
3).组合:
select se.id,se.salary,se.dept_id,sa.avgs
from s_emp se,(
select dept_id id,avg(salary) avgs
from s_emp
group by dept_id
) sa
where se.dept_id = sa.id
and se.salary>sa.avgs;
02.查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称以及所在地区
【在上题的基础上，添加s_dept和s_region多表连接查询】。
select se.id,se.salary,sa.avgs,sd.name,sr.name
from s_emp se,(
select dept_id id,avg(salary) avgs
from s_emp
group by dept_id
) sa,s_dept sd,s_region sr
where se.dept_id=sa.id
and se.dept_id=sd.id
and sd.region_id=sr.id
and se.salary>sa.avgs;
03.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资
a.41部门平均工资
select avg(salary)
from s_emp
where dept_id=41;
1247.5
b.找部门，平均工资高于1247.5
select dept_id
from s_emp
group by dept_id
having avg(salary) > 1247.5;
10,31,32,33,35,50
c.找以上部门 中员工信息
select se.last_name,se.dept_id
from s_emp se
where se.dept_id in (10,31,32,33,35,50);
d.准备部门平均工资表
select dept_id,avg(salary) avgs
from s_emp
group by dept_id;
e.同时显示 员工所在部门平均 工资
select se.last_name,se.dept_id,sa.avgs
from s_emp se,(
select dept_id,avg(salary) avgs
from s_emp
group by dept_id
) sa
where se.dept_id in (10,31,32,33,35,50)
and sa.dept_id = se.dept_id;
f.组合
select se.last_name,se.dept_id,sa.avgs
from s_emp se,(
select dept_id,avg(salary) avgs
from s_emp
group by dept_id
) sa
where se.dept_id in (
select dept_id
from s_emp
group by dept_id
having avg(salary) > (
select avg(salary)
from s_emp
where dept_id=41
)
)
and sa.dept_id = se.dept_id;
04.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资, 同时显示出部门的名字
求41部门平均工资 1247.5
select avg(salary)
from s_emp
where dept_id=41;
求部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
显示员工信息
select se.id,se.last_name,se.dept_id,sd.name,sa.avgs
from s_emp se,s_dept sd,(
select avg(salary) avgs,dept_id id
from s_emp
group by dept_id
) sa
where se.dept_id=sd.id
and se.dept_id in (10,31,32,33,35,50)
and sa.id=sd.id;
05.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高
1).找Ngao所在部门
select dept_id
from s_emp
where last_name='Ngao';
41
2).求Ngao所在部门平均工资
select avg(salary)
from s_emp
where dept_id=(?);
1247.5
3).提取员工信息(工资高于？)
select id,last_name,salary
from s_emp
where salary>(1247.5);
4).准备部门平均工资表
select dept_id id,avg(salary) avgs
from s_emp
group by dept_id;
5).添加 该员工部门平均工资高于1247.5的筛选条件
select se.id,se.last_name,se.salary,sa.avgs
from s_emp se,(
select dept_id id,avg(salary) avgs
from s_emp
group by dept_id
) sa
where se.dept_id=sa.id
and se.salary>(1247.5)
and sa.avgs>1247.5;
06.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区
select se.last_name,se.salary,se.dept_id,sa.avgs,sd.name,sr.name
from s_emp se,(
select dept_id,avg(salary) avgs
from s_emp
group by dept_id
)sa,s_dept sd,s_region sr
where sd.id = sa.dept_id
and sr.id = sd.region_id
and sa.dept_id = se.dept_id
and se.salary > 1247.5
and sa.avgs > 1247.5;
