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;