子查询包括单行子查询和多行子查询
问题:查询所有比雇员“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;