【Oracle11gR2 | 学习】分组查询、多表查询、自连接、子查询、分页查询

学习资料来源于：【Oracle】黑马57期Oracle数据库基础教程_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili

今天开篇学习分组查询，涉及到group by、having和where的用法问题。

【代码展示】：

--分组查询
--查询出每个部门的平均工资
--分组查询中，出现在group by后面的原始列，才能出现在select后面，
--没有出现在group by后面的列，想在select后面，必须加上聚合函数。
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;

--查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000
--所有条件都不能使用别名来判断,即以下是错误表达
select ename,sal s from emp where s>1500;

select e.deptno,avg(e.sal) asal
from emp e
group by e.deptno
having asal>2000

--查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;
--然后再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;
--where是过滤分组前的数据，having是过滤分组后的数据
--表现形式：where必须在group by之前，having在group by之后

多表查询，涉及到笛卡尔积（不必掌握）、等值连接、内连接、左外连接、右外连接等概念。

【代码展示】：

--多表查询
--笛卡尔积：没有实际意义，大多数都是没有用的记录
select * 
from emp e,dept d;
--等值连接：即14个人带上他的部门信息，还是14条记录
select * 
from emp e,dept d
where e.deptno=d.deptno;
--内连接（和等值连接效果一样，推荐用等值连接）
select * 
from emp e inner join dept d
on e.deptno=d.deptno;
--发现40号部门(depno=40)没有显示，因为该部门没有员工，为了显示出所有的部门该怎么办？
--查询出所有部门以及部门下的所有员工信息
--用外连接（15条记录）
select *
from emp e right join dept d
on e.deptno=d.deptno;
--查询出所有员工以及所属部门信息
--用外连接（14条记录）
select *
from emp e left join dept d
on e.deptno=d.deptno;
--oracle中专用的外连接(15条记录，显示了右侧d的所有数据)
select * 
from emp e,dept d
where e.deptno(+)=d.deptno;
--oracle中专用的外连接(14条记录，显示了左侧e的所有数据)
select * 
from emp e,dept d
where e.deptno=d.deptno(+);

14条记录和15条记录的区别在于下图：

现在来观察一下emp这张表，可以发现，MGR一列代表的是领导，但是仅有领导编号，而这个编号在员工列EMPNO的编号重合

那么现在要来实现查询：找出员工的领导姓名，该如何操作呢？

【代码展示】：

select * from emp;
--查询出员工姓名，员工领导姓名
--自连接：是指站在不同的角度把一张表看成多张表
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;
--查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

在书写这段代码的时候，有一点值得注意：

并不是不报错就是对的，而是要揣摩表达式是否能够满足查询要求。

接下来学习子查询，其实子查询理解起来很简单（相比于上面的自连接），在本人理解其实就是嵌套（术语若不对，请指正）。

【代码展示】：

--子查询
--子查询返回一个值
--查询出工资和SCOTT一样的员工信息
select *
from emp
where sal =
      (select sal 
       from emp 
       where ename='SCOTT');
--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
select * 
from emp 
where sal in
      (select sal 
      from emp
      where deptno=10);
--子查询返回一张表
--查询出每个部门最低工资和最低工资员工姓名和该员工所在部门名称
--1、先查询出每个部门最低工资
select deptno,min(sal)
from emp
group by deptno;
--2、三表联查，得到最终结果
select t.deptno,e.ename,d.dname
from (select deptno,min(sal) msal
     from emp
     group by deptno) t,emp e,dept d
where t.deptno=e.deptno
and e.deptno=d.deptno
and t.msal=e.sal;

撰写习惯需要注意：

在写复杂语句表达时，应先搭好select-from-where框架，

然后在from后逐一对应用到的表或数据，如果表或数据能直接获取，那就直接写上，如果不能，可以先在旁处写好子查询语句，再嵌套进去；

select后也一样，逐一对应好需要展示的最终结果项；

而where后是较为困难的，需要逐一确定好限制条件。

分页查询就是要求从指定的第几个开始显示到指定的第几个结束，涉及到rownum的概念。

【代码展示】：

--oracle中的分页
--rownum行号：当做select操作时，每查询出一行记录，
--就会在该行加上一个行号，行号从1开始，依次递增，不能跳着走
-----emp表工资倒序排列后，每页五条记录，查询第二页
select rownum,e.* from emp e order by e.sal desc;--发现rownum是乱的
-----因此排序操作会影响rownum的顺序
-----因此，如果涉及到排序，同时需要用到rownum的时候，就在外面再加一个rownum
select rownum,t.* from(
       select rownum,e.* from emp e order by e.sal desc
) t;
-----分页查询：emp表工资倒序排列后，每页五条记录，查询第二页
select rownum,e.* from(
       select * from emp order by sal desc) e
where rownum<11
and rownum>5;--发现错误，一条也不显示，因为rownum不能写上大于一个正数
-----正确写法（固定格式）
select * from(
       select rownum rn,e.* from(
              select * from emp order by sal desc) e
       where rownum<11)
where rn>5;