oracle SQL简介（三）

函数，树结构查询，连接查询，子查询， 优化

3.   查询结果集合操作

实验表

create table t1 as select deptno,ename,sal from emp where deptno in (10,20) order by deptno;

create table t2 as select deptno,ename,sal from emp where deptno in (20,30) order by deptno;

union all:结果联合显示,不排序,不去重复行.

select * from t1 union all select * from t2;

union: 结果联合显示,排序,去重复行.

select * from t1 union select * from t2;

intersect:交集

select * from t1 intersect select * from t2;

minus:差集

select * from t1 minus select * from t2;

4.   连接查询

笛卡尔连接

select ename,dname from emp,dept;

select ename,dname from emp cross join dept;

等值连接

select ename,dname from emp,dept where emp.deptno=dept.deptno;

select ename,dname from emp e join dept d on e.deptno=d.deptno;

 

不等连接

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

select ename,sal,grade from emp a join salgrade b on sal between losal and hisal

 

外连接

右外

select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno;

select ename,dname,b.deptno from emp a right join dept b on a.deptno=b.deptno;

 

自连接

select a.ename subordinate, b.ename superior from emp a,emp b where a.mgr=b.empno(+);

5.   子查询

查询工资总和高于10号部门工资总和的部门

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

having sum(sal) > (select sum(sal) from emp where deptno=10);

 

查询每个部门最大工资是谁

select deptno,ename,sal from emp

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

select e.deptno,e.ename,e.sal from emp e,(select deptno,max(sal) sal from emp group by deptno) a

where e.deptno=a.deptno and e.sal=a.sal;

any和in

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

select ename,sal,deptno from emp where sal=any(select max(sal) from emp group by deptno);

any和all

select ename,sal from emp where sal<any(1000,2000);

select ename,sal from emp where sal<all(1000,2000);

小于all小于最小，大于all大于最大

select ename,sal,deptno from emp where sal<all(select avg(sal) from emp group by deptno);

select ename,sal,deptno from emp where sal>any(select avg(sal) from emp group by deptno);

from子句中的子查询

查询工资大于本部门平均工资的员工

select ename,e.deptno,sal asal from emp e,(select deptno,avg(sal) asal from emp group by deptno) a where e.deptno=a.deptno and sal>asal;

相互关联子查询

查询高于部门平均工资的员工(效率低，成本高 先运行主查询，传入子查询，求出平均，比较条件)

select ename,sal,deptno from emp a where sal>(select avg(sal) from emp where deptno=a.deptno);

 

exists

查询是领导的员工

select ename,empno,mgr from emp a where exists(select 1 from emp where mgr=a.empno);