Oracle学习笔记（三）

一、group by 分组

1.计算员工分布在哪几个部门

select distinct deptno from emp

2.有几个部门有员工

select count(distinct deptno) from emp

3.按部门分组，计算每个部门的薪水总和

select deptno,sum(sal) from emp group by deptno

4.按职位分组，计算每个职位的平均薪水

select job,avg(sal) from emp group by job

5.获得每个部门每个职位的平均薪水--重点

select deptno,job,avg(sal) from emp group by deptno,job order by deptno,avg(sal)

--结论：多次分组，组更多，只对最小的组单位进行聚合

二、Having的使用

1.求平均薪水大于1500元的部门号

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

--having 相当于where，因为where只能放在group by前面,having在group by 后面进行筛选

2.求部门工资总和大于3000的部门

select deptno,sum(sal) from emp group by deptno having sum(sal)>3000

3.按本公司岗位平均薪水大于1500元的岗位信息

select job,avg(sal) from emp group by job having avg(sal)>1500

--结论：只要是对group by的结果进行行筛选则必用having

三、多表查询之连接查询

1.交叉--一般不使用

问题1：分别取一行emp，和一行dept信息，并列组成一个新的行，共有多少种取法 m*n

select * from emp cross join dept order by ename(14*4=56)

问题2：根据以上结果，如何查找叫Smith的人所在的部门

select * from emp cross join dept where emp.deptno=dept.deptno and ename='SMITH';

select emp.empno,emp.ename,emp.job,emp.hiredate,emp.sal,emp.comm,dept.dname from emp cross join dept where emp.deptno=dept.deptno and ename='SMITH'

新的问题：先直接算叉乘得到全部集合，然后再添加where，影响性能

2.内连接：inner join 等值连接/非等值连接/自连接

2.1等值连接

查找叫Smith的人所在的部门名称

select * from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH'

查找所有的manager的人员信息

select * from emp inner join dept on emp.deptno=dept.deptno and job='MANAGER';

select emp.empno,emp.ename,dept.deptno,dept.dname from emp inner join dept on emp.deptno=dept.deptno and job='MANAGER';

select e.empno,e.ename,d.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno where job='MANAGER';

select e.empno,e.ename,d.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno and job='MANAGER'

--先考虑需求，再考虑数据来源于哪些表

查找名称为research部门的所有人员信息

select * from emp e inner join dept d on e.deptno=d.deptno and d.dname=upper('research') ;

select * from emp e inner join dept d on e.deptno=d.deptno where d.dname=upper('research')

2.2非等值连接

显示每一个人的薪水及等级信息

1[700,1200] 2[1201,1400]

select * from emp join salgrade on sal between losal and hisal;

select * from emp join salgrade on sal>=losal and sal<=hisal

显示员工完整信息，包括部门名称

select * from emp join salgrade on sal between losal and hisal join dept on emp.deptno=dept.deptno

统计每一个工资等级的员工人数--重点

select count(emp.empno) from emp join salgrade on sal between losal and hisal group by salgrade.grade

2.3内连接

查找员工与其上级的编号，姓名

select * from emp e1 join emp e2 on e1.mgr=e2.empno--少了boss信息

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno

2.4外连接 left outer join

查找员工与其上级的编号，姓名(boss也要显示)

select * from emp e1 left outer join emp e2 on e1.mgr=e2.empno

--理解：1.先按内连接查出所有数据并显示（能满足on条件的都显示出来）2.左边的表如果不满足on条件，也要显示出来，右边找不到匹配记录全部写null

查询所有部门的人数清单，部门编号，名称，人数--重点

select d.deptno,d.dname,count(e.empno) from dept d left outer join emp e on d.deptno=e.deptno group by d.deptno,d.dname order by d.deptno

哪些员工没有下属（不是别人的领导）--重点

--外连接+匹配表pk is null表示否定问题，不是，不包括等等

select * from emp e1 join emp e2 on e1.mgr=e2.empno

select * from emp e1 right outer join emp e2 on e1.mgr=e2.empno

select * from emp e1 right outer join emp e2 on e1.mgr=e2.empno where e1.empno is null

select * from emp e1 left outer join emp e2 on e1.empno=e2.mgr

select * from emp e1 left outer join emp e2 on e1.empno=e2.mgr where e2.mgr is null

哪些部门没有叫Smith的员工

select * from emp

select d.deptno from dept d left outer join emp e on d.deptno=e.deptno and ename=upper('smith') where e.empno is null order by d.deptno

总结：

内连接：等值连接、非等值连接、自连接

外连接：左、右、全外连接

遗留问题：

求每一个部门的收入大户是谁，收入多少，不知道人

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

select emp.ename from emp join(select deptno,max(sal) maxsal from emp group by deptno) T on emp.deptno=T.deptno and emp.sal=maxsal order by emp.deptno

求平均薪水大于1600元的部门号，部门名称

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