PLSQL数据库select语句Day02

1.6、聚合函数

-- 组函数 |聚合函数 | 多行函数 :多条记录返回一个结果

-- 确定要计算的数据然后才能使用组函数,对结果集,分组

-- count() sum() max() min() avg()

--注意: 如果select后面一旦出现组函数的使用,不能与非分组字段一起使用

-- 统计一下一共有多少个员工

select count(*) from emp;

select count(empno) from emp;

select count(deptno) from emp;

select count(1) from emp;

-- 统计一共有几个部门

select count(1) from dept;

-- 统计有员工存在的部门总数

select count(distinct deptno) from emp;

-- 统计20部门一共有多少人

select count(1) from emp where deptno = 20;

-- 计算本公司每个月一共要在工资上花费多少钱

select sum(sal) from emp;

-- 查询本公司的最高工资和最低工资

select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资

select max(sal),min(sal) from emp where deptno = 30;

-- 请查询出 20部门的平均工资

select avg(sal) from emp where deptno = 20;

-- 计算出所有员工的奖金总和

select sum(comm) from emp;

--null值不参与组函数计算

-- 统计有奖金的员工有几个

select count(comm) from emp where comm is not null;

--查询最高薪水的员工姓名,及薪水

select max(sal) from emp;

select ename,sal from emp where sal = (最高薪水);

select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于整个公司平均工资的员工编号，姓名及工资

select empno,ename,sal from emp where sal<(select avg(sal) from emp);

--思考

--查看高于本部门平均薪水员工姓名

select avg(sal) from emp where deptno = 10;

select *

from emp e1

where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

1.7、分组

-- 分组 : 根据指定的规则对数据进行分组

-- group by 分组字段

-- 语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段;

-- 执行流程 : from -> where -> group by -> having -> select -> order by

-- 如果 select后面一旦出现组函数,不能使用非分组字段

-- 如果一旦分组,只能使用分组字段或者组函数

-- where中不能使用字段别名,不能使用组函数,因为执行流程的问题

-- 求出每个部门的平均工资

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

-- 找出20部门和30部门的最高工资

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

--先过滤后分组

select deptno,max(sal) from emp where deptno in(20,30) group by deptno;

--先分组后过滤

select deptno,max(sal) from emp group by deptno having deptno in(20,30);

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资

select avg(sal) from emp where sal>1000 and deptno in(10,20) group by deptno;

select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);

-- 找出每个部门的最高工资

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

-- 求出平均工资高于2000的部门编号和平均工资

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

-- 查询最低平均工资的部门编号

--1)找到每个部门的部门编号与平均工资

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

--2)最低平均工资

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

--3)工资与最低平均工资值相等的部门的部门编号

select deptno,avg(sal) from emp group by deptno having avg(sal)=(最低平均工资);

select deptno

from emp

group by deptno

having avg(sal) = (select min(avg(sal)) from emp group by deptno);

-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序

select deptno,count(1) n from emp group by deptno order by n ;

-- 查询每个工种的最高工资以及工种

select job,max(sal) from emp group by job;

-- 查询平均工资在1500到2000之间的部门平均工资和部门编号

select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000;

1.8、行转列

/*

id name course score

1 张三 语文 81

2 张三 数学 75

3 李四 语文 81

4 李四 数学 90

5 王五 语文 81

6 王五 数学 100

7 王五 英语 90

create table tb_student(

id number(4) ,

name varchar2(20),

course varchar2(20),

score number(5,2)

);

insert into tb_student values(1,'张三','语文',81);

insert into tb_student values(2,'张三','数学',75);

insert into tb_student values(3,'李四','语文',81);

insert into tb_student values(4,'李四','数学',90);

insert into tb_student values(5,'王五',&