子查询:select语句当中嵌套select语句,被嵌套的select语句是子查询。
select…(select) from…(select) where…(select)
1。where子句中使用:
找出高于平均工资员工的信息:
select * from emp where sal > avg(sal);//错误写法,where后面不能直接使用分组函数。
第一步:找出平均工资 select avg(sal) from emp;//得到x
第二步:where过滤 select * from emp where sal > x;
合并:select * from emp where sal >(select avg(sal) from emp);
2。from后面嵌套子查询
找出每个部门平均工资的工资等级:
第一步找出每个部门平均工资(按照部门编号分组,求sal的平均值)
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步将以上查询结果当做临时表t,与salgrade s表连接 ,条件是 t.avgsal between s.losal and s.hisal;
select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
union(可以将查询结果相加)
找出工作岗位是xx和xxx的员工:
第一种:select name,job from emp where job = ‘xx’ or job ‘xxx’;
第二种:select name,job from emp where job in (‘xx’,‘xxx’);
第三种:select name,job from emp where job =‘xx’ union select name,job from emp where job =‘xxx’;
可以将两张不相干的表中数据拼接在一起显示:
select name from emp union select dname from dept;