where,insert,delete,update,select子查询，关联子查询，分页查询，集合查询

子查询

高级查询：子查询（出现多个select）

where子句中使用子查询

查询和SMITH在同一个部门的员工信息

select * from emp where deptno=

(

select deptno from emp where ename='SMITH'

);

查询比SCOTT工资高的

select * from emp where sal>

(

select sal from emp where ename=‘SCOTT’

);

查询跟10号部门工资一样的

select * from emp where sal=

(

select sal from emp where deptno=10

);此方法肯定不对,用了关系用算符，结果只能是一个

子查询返回结果不止一行

解决方法:（= —— in > —— any(some)、all）

select * from emp where sal in

(

select sal from emp where deptno=10

);

查询比10号部门工资高的

select * from emp where sal >

any(

select sal from emp where deptno=10

);查询出比10号部门任意（最低）工资高

select * from emp where sal >

all(

select sal from emp where deptno=10

);查询出比10号部门所有人工资高

查询和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’);

insert 子句中使用子查询

insert into exam values((select stuid from student where stuname=‘里斯’),‘JAVA’,88);

delete子句中使用子查询

delete from emp where deptno=(select deptno from dept where loc=‘NEW YORK’);

delete from emp where deptno in(select deptno from dept where loc=‘NEW YORK’);

update子句中使用子查询

update emp set sal=sal+100 where deptno in(select deptno from dept where loc=‘NEW YORK’);

select子句中使用子查询

select ename,job,(select dname from dept where deptno=emp.deptno) as 部门 from emp;

查平均工资

select ename,job,sal(select avg(sal) from emp) as 平均工资 from emp;

select ename,job,sal,(select avg(sal) from emp where deptno=e.deptno) as 部门平均工资 from emp e;

关联子查询

查询比本部门的平均工资高的员工工资

select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

一个查询结果可以当作一个临时表，从临时表中查询数据，可以将临时表和其他表做联合查询

分页查询⭐

rownum行号

每页5行，显示第一页

不能写大于2的条件

select * from emp where rownum<=5;

按工资排序再取前5个

select t.* ,rownum from(

select * from emp order by sal

)t where rownum<=5;

查看第二页

select* from(

select t.* ,rownum as rn from(

select * from emp order by sal

)t

)t2 where t2.rn between 6 and 10;

row_number行号

select emp.*,row_number() over(order by sal asc) as rn from emp;

分页

select* from(

select emp.*,row_number() over(order by sal asc) as rn from emp;

)t2 where t2.rn between 6 and 10;

查询每个部门中工资最高的员工的信息

方法1

select * from emp

inner join(

select deptno,max(sal) as maxsal from emp group by deptno)t

on emp.deptno=t.deptno and emp.sal=t.maxsal;

方法2

select * from emp

where (deptno,sal) in

(

select deptno,max(sal) as maxsal from emp group by deptno

)

集合查询

并集

union

union all

合并可以是两个表

select * from emp where sal>2000

union

select * from emp where ename like ‘S%’;

查员工的名字，工资，职位,另一个表dname,平均工资

select ename,sal,job from emp

union

select dname,’’,avg(sal) from dept inner join emp on emp.deptno=dept.deptno group by dname;

交集

intersect

两个条件都满足

select * from emp where job=‘CLERK’

intersect

select * from emp where sal>2000;

差集

minus

从第一个结果中剪掉第二个结果剩下的

select * from emp wher deptno=10

minus

select * from emp where sal<2000;