Oracle数据库笔记之子查询

1.子查询返回的结果是一个值  --单行子查询
使用 = > < <= >= <> between...and... 


查询与scott同一个职位的员工信息
select * 
from emp 
where job=(select job from emp where ename='SCOTT');
查询与smith同一个部门的员工信息
select *
from emp 
where deptno=(select deptno from emp where ename='SMITH');
查询比Ford工资高的员工信息
select *
from emp 
where sal>(select sal from emp where ename='FORD');
查询比ADAMS入职晚的员工信息
select * from emp
where hiredate>(select hiredate from emp where ename='ADAMS');


2.子查询返回的结果是多个值 --多行子查询
使用in 
not in 


查询与FORD同一部门或与CLARK同一部门的员工信息
select * from emp
where deptno in (select deptno from emp where ename='FORD' or ename='CLARK');
查询与CLARK和SMITH不同工作的员工信息
select * from emp
where job not in (select job from emp where ename='CLARK' or ename='SMITH');


any --任何一个
all --全部


查询比部门30中工资高的员工信息
select * from emp
where sal > any (select sal from emp where deptno=30);
select * from emp
where sal >  (select min(sal) from emp where deptno=30);
查询比部门30中工资都高的员工信息
select * from emp
where sal > all (select sal from emp where deptno=30);
select * from emp
where sal >  (select max(sal) from emp where deptno=30);
查询与Smith同一个部门，并且工作相同的员工信息
select * from emp
where deptno=(select deptno from emp where ename='SMITH') 
and job=(select job from emp where ename='SMITH');
select * from emp
where (deptno,job)=(select deptno,job from emp where ename='SMITH') ;


将子查询作为临时表和其他表连接
注意：要为当前子查询和子查询中的字段起别名


查询每个部门工资最高的员工信息
select * from emp e,(select max(sal) as max_sal,deptno dno from emp group by deptno) t 
where e.sal=t.max_sal  and e.deptno=t.dno;
查询每个部门最早入职的员工信息
select * from emp e,(select min(hiredate) min_hiredate,deptno dno from emp group by deptno) t
where t.min_hiredate=e.hiredate and t.dno=e.deptno;
查询每个部门的员工数，部门名称，部门地址
select num,dname,loc from dept d,(select count(*) num,deptno dno from emp group by deptno) t
where t.dno=d.deptno;


分页查询：
不同数据库的处理方式不同
mysql ------limit关键字
sqlserver ------top关键字
oracle ------rownum伪列
从m到n 
1.确定查询条件
2.设置n
3.设置m


分页查询的语法
select b.* 
from  (select t.*,rownum as num
from (select * from 表名 where 条件) t
where rownum <=n) b
where num>m;


查询员工表中前六条数据
select * from emp where rownum<=6;
查询员工表中第3—7条数据
select * from emp where rownum>= 3 and rownum<=7;--错误
select * from emp where rownum between 3 and 7;  --错误
查询员工表中名字带s的第2-4名员工信息
select e.* 
from  (select t.*,rownum as num
from (select * from emp where ename like '%S%') t
where rownum <=4) e
where num>=2;
select t.*
from (select rownum num,emp.* from emp where ename like '%S%' and rownum<=4) t
where num>=2;