【Oracle基础】分组统计，多表查询，子查询，Rownum与分页查询_oracle 分组行号

数据使用的是scott用户下自带的表。

1. 分组统计

• 语法

SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名 1 ASC|DESC，列名 2...ASC|DESC
1

• 示例

--分组查询
--查询出每个部门的平均工资
--分组查询中，出现在group by后面的原始列，才能出现在select后面
--没有出现在group by后面的列，想在select后面，必须加上聚合函数。
--聚合函数有一个特性，可以把多行记录变成一个值
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;
1
2
3
4
5
6
7
8

--查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;
--所有条件都不能使用别名来判断

--查询出每个部门工资高于800的员工的平均工资
select e.deptno,avg(e.sal) asal
from emp e
where e.sal > 800
group by e.deptno
--where是过滤分组前的数据，having是过滤分组后的数据。
--查询出每个部门工资高于800的员工的平均工资，然后再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal) sal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 注意：

1. 如果使用分组函数， SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其他字段。
2. 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值
   
   

2. 多表查询

2.1 多表连接基本查询

• 语法

SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名 1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}
1

• 查询员工表和部门表

select * 
from emp e,dept d;
1
2

我们发现产生的记录数是 56 条，我们还会发现 emp 表是 14 条，dept 表是 4 条， 56 正是 emp表和 dept 表的记录数的乘积，我们称其为笛卡尔积。

如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。

在两张表中我们发现有一个共同的字段是 depno， depno 就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

--等值连接
select * 
from emp e,dept d
where e.deptno=d.deptno;
1
2
3
4

关联之后我们发现数据条数是 14 条，不在是 56 条。

2.2 内连接

--内连接
select * 
from emp e inner join dept d
on e.deptno = d.deptno;
1
2
3
4

2.3 外连接

当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的

--查询出所有部门，以及部门下的员工信息。[外连接]
select * 
from emp e right join dept d
on e.deptno = d.deptno;
1
2
3
4

当然，上面是普通写法，Oracle有专用外连接写法

select * 
from emp e,dept d
where e.deptno(+) = d.deptno;
1
2
3

• 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

--查询出员工姓名，员工领导姓名
--自连接：自连接其实就是站在不同的角度把一张表看成多张表。
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
1
2
3
4
5
6
7
8
9
10
11
12

3. 子查询

在一个查询的内部还包括另一个查询，则此查询称为子查询。

Sql的任何位置都可以加入子查询。

--子查询返回一个值
--查询出工资和SCOTT一样的员工信息
select * from emp where sal in (select sal from emp where ename = 'SCOTT')
1
2
3

--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in (select sal from emp where deptno = 10);
1
2
3

--子查询返回一张表
--查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
select t.deptno,t.msal,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 t.msal = e.sal
and e.deptno = d.deptno;
1
2
3
4
5
6
7
8
9

4. Rownum与分页查询

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

--查询 emp 表带有 rownum 列
select ROWNUM,t.* from emp t
1
2

我们可以根据 rownum 来取结果集的前几行，比如前 5 行

--查询前 5 行
select ROWNUM,t.* from emp t where ROWNUM<6
1
2

但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号。如果要实现这种需求，我们可以使用子查询

--emp表工资倒叙排列后，每页五条记录，查询第二页。
--rownum行号不能写上大于一个正数
select * from(
  select rownum rn,e.* 
  from
  (select * from emp order by sal desc) e
  where rownum<11
) where rn>5
1
2
3
4
5
6
7
8