Oracle课堂笔记4

●Oracle 复杂表查询
⏹数据分组：-max,min,avg,sum,count
？如何显示所有员工中最高工资和最低工资？
select max(sal) from emp ; (有多个结果也只显示一个)
select min(sal) from emp ;
select avg(sal) from emp ; (有空值null则不参与运算)
select sum(sal)/count(*) from emp ; (count计算行数)
？统计有多少员工？
select count(*) from emp ;（字段亦可，空值不参与运算）
？显示工资最高的员工的名字和工作岗位？
（我们可以使用子查询来完成1.先查询最大工资是多少2.查找谁的工资是最大工资）
select ename , job from emp where sal = (select max(sal) from emp) ; (SQL语句默认从右往左执行)
？显示工资高于平均工资的员工信息？
select ename , job from emp where sal > (select avg(sal) from emp) ;
⏹group by和having子句：
group by对结果进行分组统计，
having进行限制(过滤)分组显示结果，通常与group by 同时
出现。

？如何显示每个部门的平均工资和最高工资？
select avg(sal),max(sal),deptno from emp group by deptno ;
？先是每个部门每个岗位的平均工资和最低工资？
select avg(sal),max(sal) ,deptno ,job from emp group by
deptno,job order by deptno ;
？显示部门平均工资低于2000的部门和平均工资？
select avg(sal),deptno from emp group by deptno having
avg(sal)<2000 ;（having不支持别名）
●注意事项：
⏹ 1.分组函数（avg）只能出现在选择列表、having、order by
子句中。

⏹ 2.如果select中同时出现，顺序是：group by、having、
order by，顺序不能出错。

⏹ 3.在选择列中，如果有列，表达式，分组函数，那么这些
列和表达式必须有一个出现在group by中，否则会出错。

多表查询：在实际开发中不可避免存在对两张或多张表的复杂查询。

1.我们看看多表查询的原理：
select * from emp,dept ;
2.如何实现多表查询：
select emp.ename,emp.sal,dept.dname from emp,dept
where emp.deptno=dept.deptno ;
3.如何避免笛卡尔积：
多表查询的条件是至少不能少于表的个数-1。

4.如何显示部门号为10的部门名、员工名和工资：
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=10 ;
注意：我们建议大家在进行多表查询的时候使用别名。

select t1.ename,t1.sal,t2.dname from emp t1 ,dept t2 where t1.deptno=t2.deptno and t1.deptno=10;
自连接：
显示FORD的上级：select mgr from emp where ename='FORD' ;
显示FORD上级的信息：select * from emp where empno = (select mgr from emp where ename='FORD' );
显示各员工的姓名和他的上级领导的姓名：select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno ;
把worker的人员全部列出：select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno (+) ;
其中，加号是外连接。