Oracle（三）

第五章 Group Function
第六章 Subqueries

第五章：组函数

group by 在查询表中数据的时候进行分组的关键字
思考:为什么要对数据进行分组
having 分组之后的进行进一步数据筛选的关键字
having和where的功能类似

组函数(分组函数),是指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理。所以组函数一般要结合着分组关键字group来使用

组函数:

avg 求平均值
count 计算有多少条数据
max 最大值
min 最小值
sum 求和
stddev 标准差
variance 方差

sql语句的各部分构成
select …
from …
where …
group by …
having …
order by …

注:除了select和from之外其他的都不是必须的。

假如select…from…后面的语句都出现了,那么他们的执行顺序为:
where–>group by分组–>执行组函数–>having筛选->order by

组函数出现的位置:

1.select后面
2.having后面
3.order by后面
4.where后面一定【不能】出现组函数

注意:如果select/having语句后面出现了组函数,那么select/having后面没有被组函数修饰的列，就必须出现在group by 后面

where和having对比:

1.where和having都是做条件筛选的
2.where执行的时间比having要早
3.where后面不能出现组函数
4.having后面可以出现组函数
5.where语句要紧跟from后面
6.having语句要紧跟group by后面

group by和having的关系:

1.group by可以单独存在,后面可以不出现having语句
2.having不能单独存在,有需要的话,必须出现在group by后面

order by语句
1.如果sql语句中需要排序,那么就一定要写在sql语句的最后面
2.order by后也可以出现组函数

使用组函数: 不结合group分组使用

注:如果不使用group分组的话,那么默认当前查询到的所有数据是一组
例如:
查询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;

使用组函数:结合group分组使用
例如:
查询s_emp表中每个部门的平均工资
select avg(salary),dept_id
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;

使用组函数:结合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,sum(salary)
from s_emp
group by dept_id
having sum(salary)>=4000;

select dept_id,avg(salary)
from s_emp
where dept_id in(41,32,35)
group by dept_id
having avg(salary)>1400
order by dept_id desc;

使用组函数:其他实例

例如:
查询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语句后面?
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;

查询s_emp表中最大的工资数,并且显示出这个最大工资的员工的名字

第一步:查出最大工资数
select max(salary)
from s_emp;

第二步:加上last_name的显示
select last_name,max(salary)
from s_emp;

第三步:select后面出现组函数,没有被组函数修饰的列放到group by后面,但是发现查询结果并不是想要结果
select last_name,max(salary)
from s_emp
group by last_name;

第四步:修改为多表查询(起别名),从s1表中查询出最大的工资数是多少,然后再和s2表连接起来,选出s2表中这个最大工资数的员工名字
select s2.last_name,max(s1.salary)
from s_emp s1,s_emp s2
where s2.salary = max(s1.salary)
group by s2.last_name;

第五步:where后面不能出现组函数,所以改为having
select s2.last_name,max(s1.salary)
from s_emp s1,s_emp s2
group by s2.last_name,s2.salary
having s2.salary = max(s1.salary);

第六章：子查询(嵌套查询)

子查询，即一个select语句中嵌套了另外的一个或者多个select语句

例如:
查询工资比Simth工资高的员工信息
第一步:查询Smith的工资数
select salary
from s_emp
where last_name=‘Smith’;
结果:
SALARY
----------
940

第二步:查询工资比940高的员工信息
select last_name,salary
from s_emp
where salary>940;

第三步:把第二步中的数字940替换成第一步中的sql语句即可(注意格式)
select last_name,salary
from s_emp
where salary>(
select salary
from s_emp
where last_name=‘Smith’
);

例如:
查询所有部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
结果:

   DEPT_ID AVG(SALARY)
	---------- -----------
			42  1081.66667
			43         900
			34        1160
			44        1050
			31        1400
			32        1490
			35        1450
			50        2025
			41      1247.5
			45        1089
			33        1515

 DEPT_ID AVG(SALARY)
	---------- -----------
			10        1450

查询平均工资比1247.5高的部门编号
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;

结果:
DEPT_ID
----------
31
32
35
50
33
10

查询平均工资比1247.5高的部门中员工信息
select last_name,salary,dept_id
from s_emp
where dept_id in(10,31,32,33,35,50);

等价于

select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
);

查询平均工资比 41号部门的平均工资 高 的部门中员工的信息

select avg(salary)
from s_emp
where dept_id=41;

结果:
AVG(SALARY)
-----------
1247.5

所以这个sql语句和1247.5是等价的,那么就可以将上面sql语句中的1247.5给替换了
select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
);
替换后为:
select last_name,salary,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
)
);