Oracle-连接、分组函数、子查询

1.连接

①等连接

select e.empno, e.ename, e.deptno, d.deptno, d.loc
from emp e,dept d
where e.deptno=d.deptno;

②非等连接

select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

③.外连接（哪边少哪边加）

列出公司所有部门的员工信息，即使没有员工的部门也要列出来。
select e.ename, e.deptno, d.loc
from emp e, dept d
where e.deptno(+)=d.deptno;
解释：“没有员工”，所以员工这侧+，而且查出的结果也是员工表这边少数据。

列出公司所有员工的部门信息，即使没有部门的员工也要列出来。
select e.ename, e.deptno, d.loc
from emp e, dept d
where e.deptno=d.deptno(+);
解释：“没有部门”，所以部门这侧+，而且查出的结果也是部门表这边少数据。

④自连接

查询员工的员工号、经理号、经理名字。
select work.ename, work.empno, mgr.empno, mgr.ename
from emp work, emp mgr
where work.mgr=mgr.empno;

2.分组函数

avg、count、max、min、sum、stddev、variance
①select max(hiredate), min(hiredate) from emp where deptno=30;
③select count(*) from emp where deptno=30;
④select count(comm) from emp where deptno=30;

group by用法：

①select中出现的列必须出现在group by中
查询每个部门的平均工资：
select deptno, avg(sal) from emp group by deptno;

②select中不出现的列可以出现在group by中
select avg(sal) from emp group by deptno;

③多个列进行分组
select deptno, job , avg(sal) from emp group by deptno,job;

④where中不能出现分组函数，分组函数的列必须写在having中.
求平均工资大于2000的部门号。
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;
或者
select deptno,avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) where avgsal>2000;

分组函数的嵌套

查询平均工资最大的部门及部门号：
select deptno, max(avg(sal)) from emp group by deptno; ×

select max(avg(sal)) from emp group by deptno;√

或者

select a.deptno ,b.salary from 
(select deptno,avg(sal) avgsal from emp group by deptno) a,
(select max(avg(sal)) salary from emp group by deptno) b
where a.avgsal=b.salary;√

注意：
分组函数嵌套必须有group by
having子句后面不能有好几个组函数嵌套,只能使用一个组函数

子查询

①子查询要包括在括弧里面。
②子查询放在比较符的右边。
③子查询中不要包含order by。
④单行子查询使用单行比较操作符。
⑤多行子查询使用多行比较操作符。（in、any、all）
⑥子查询如果是空值，则整个查询返回空。

查询工资比7566高的员工名字：
select ename from emp where sal>
(select sal from emp where empno=7566);

i.单行子查询例子：

查询和7369一样工作，并且工资比7876高的 员工姓名和工作：
select ename, job from emp where job =
(select job from emp where empno=7369)
and sal>
(select sal from emp where empno=7876);

查询公司中工资最少的员工信息：
select ename,job,sal from emp where sal=
(select min(sal) from emp);

ii.子查询中使用having子句：
查询 最小工资 比20号部门的最小工资 的部门号及该部门的最小工资：

select deptno, min(sal) from emp group by deptno having min(sal)<
		(select min(sal) from emp where deptno = 20 group by deptno );

或者

select deptno , min(sal) from emp where sal<
(select min(sal) from emp where deptno=20)
group by deptno;

iii.多行子查询：

in：等于列表中的每一个值。
any：与列表中的任意一个值比较。
all：与列表中的所有值进行比较。（大于 比最大值还大的，小于 比最小值还小的）

select ename,job, sal from emp where sal<any
(select sal from emp where job=‘CLERK’)
and job<>‘CLERK’;

select ename,job, sal from emp where sal>all
(select sal from emp where job=‘CLERK’)
and job<>‘CLERK’;

iv.使用多列子查询：
找出与605号订单的产品号prodid、产品数量qty相匹配的订单orderid、prodid和qty。

成对比较：
select orderid，prodid， qty from item
where （prodid， qty） in
（select proid，qty from item where orderid=605）
and ordid<>605;

非成对比较：
select orderid，prodid， qty from item
where prodid in
（select prodded from item where ordid=605)
and qty in
(select qty from item where ordid=605)
and ordid<>605;

v.子查询为空例子：
select employee.ename from emp employee
where employee.empno not in
(select manager.mgr from emp manager);

vi.在from从句中使用子查询：
查询比部门平均工资高的人的信息及部门的平均工资：
select a.ename, a.sal, a.deptno , b.salavg
from emp a,(select deptno, avg(sal) salavg from emp group by deptno) b
where a.deptno=b.deptno
and a.sal>b.salavg;