Oracle数据库学习---子查询

sql子查询：

子查询包括单行子查询和多行子查询
问题：查询所有比雇员“CLARK”工资高的员工信息
分解1：
select sal from emp where ename='CLARK'
分解2：
select * from emp where sal>2450

1、使用单行子查询学习: 如果在问题中涉及到不能直接获取的数据，考虑使用子查询

特点1：可以将子查询放在where条件中使用单行比较符进行数据筛选
特点2：子查询的结果只有一个值
注意1：一般把子查询放在右边增强可读性
注意2：在单行子查询中不能使用order by,除非使用TOP-N
select * from emp where sal>(select sal from emp where ename='CLARK');
select * from emp where (select sal from emp where ename='CLARK')<sal;

查询工资高于平均工资的员工的名字和工资

select ename,sal from emp where sal>(select avg(sal) from emp)

查询和soctt属于同一部门且工资比他低的员工资料

select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT');

查询工资最高的员工资料

select * from emp where sal=(select max(sal) from emp)

查询职务和scott相同，雇佣时间早的员工信息

select * from emp where job=(select job from emp where ename='SCOTT') and hiredate<(select hiredate from emp where ename='SCOTT')

查询工资比scott高或者雇佣时间早的员工编号和名字

select empno,enamefrom emp where sal>(select sal from emp where ename='SCOTT') or hiredate<(select hiredate from emp where ename='SCOTT');

多行子查询

关键字1：any，表示任意一个
关键字2：all，表示所有
关键字3：in，表示等于任意一个

查询工资低于任意一个“CLERK”工资的雇员信息，使用any

select * from emp where sal<1300
select * from emp where sal<(select max(sal) from emp where job='CLERK') and job<>'CLERK'--单行写法 
select * from emp where sal< any (select sal from emp where job='CLERK') and job<>'CLERK'--多行写法

查询工资比所有的“salesman”高的雇员编号，名字，工资 使用all

select sal from emp where job='SALESMAN' order by sal；
select * from emp where sal>(select max(sal) from emp where job='SALESMAN') order by sal
select * from emp where sal> all (select sal from emp where job='SALESMAN') order by sal

查询部门20中同部门10的雇员工作一样的雇员信息

解读1：查看10部门雇员工作
select job from emp where deptno='10'
解读2:查询部门20中工作是MANAGER 或者 PRESIDENT 或者CLERK的雇员信息
select * from emp where deptno=20 and (job='MANAGER' or job ='PRESIDENT' or job='CLERK') 
select * from emp where deptno=20 and job in ('MANAGER','PRESIDENT','CLERK')
select * from emp where deptno=20 and job in (select job from emp where deptno=10)--使用in关键字
select * from emp where deptno=20 and job = any (select job from emp where deptno=10)

查询所有经理人的信息?

select * from emp 
select distinct mgr from emp where mgr is not null
select * from emp where empno in(select distinct mgr from emp where mgr is not null);

转换成等值链接

select distinct e.* from emp e,emp m where e.empno = m.mgr and m.mgr is not null;