【Oracle学习】Oracle之多表查询_oracle根据a表统计b表

1.内连接

笛卡尔积： 两张表进行关联查询，得到的结果是两张表的乘积
如何去除笛卡尔积： 通过条件【外键】

主表：
从表：外键在这里，外键的值来源于主表的主键

–内连接：完全符合条件的数据才会显示

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

–案例：查询员工与他领导的信息

select *
from emp e,emp m
where e.mgr = m.empno;
1
2
3

–案例：在上面的基础上，查询员工的部门信息

select e.empno,e.ename,e.deptno,d.dname 员工部门名称, m.empno,m.ename 领导名称
from emp e,emp m,dept d
where e.mgr = m.empno
      and e.deptno=d.deptno;
1
2
3
4

–案例：在上面的基础上，查询领导的部门信息

select e.empno,e.ename,e.deptno,d.dname 员工部门名称, m.empno,m.ename 领导名称,m.deptno ,d2.dname 领导部门名称
from emp e,emp m,dept d,dept d2
where e.mgr = m.empno
      and e.deptno=d.deptno
      and m.deptno=d2.deptno;
1
2
3
4
5

–案例：在上面的基础上，显示员工的薪资级别

select e.empno,e.ename,e.deptno,d.dname 员工部门名称,e.sal,s.grade 员工薪资等级,
       m.empno,m.ename 领导名称,m.deptno ,d2.dname 领导部门名称
from emp e,emp m,dept d,dept d2,salgrade s
where e.mgr = m.empno
      and e.deptno=d.deptno
      and m.deptno=d2.deptno
      and e.sal between s.losal and s.hisal;
1
2
3
4
5
6
7

–案例：在上面的基础上，把薪资等级显示为中文 一级,二级…

select e.empno,e.ename,e.deptno,d.dname 员工部门名称,e.sal,s.grade 员工薪资等级,
       decode(s.grade,1,'一级',2,'二级',3,'三级','大boss级') as 中文等级,
       m.empno,m.ename 领导名称,m.deptno ,d2.dname 领导部门名称
from emp e,emp m,dept d,dept d2,salgrade s
where e.mgr = m.empno
      and e.deptno=d.deptno
      and m.deptno=d2.deptno
      and e.sal between s.losal and s.hisal;
     
1
2
3
4
5
6
7
8
9

2.外连接

外连接查询中，最关键是找基准表，如果是基准表，那么他的数据全部显示

外连接：
左外连接： select * from tab1 left join tab2 on 条件 ： tab1是基准表
右外连接： select * from tab1 right join tab2 on 条件 ： tab2是基准表

2-1 左外连接

– 案例： 查询员工与部门表的信息

select * from emp e left join dept d on e.deptno = d.deptno;
1

– 案例： 查询员工与他的薪资级别

select * from emp e left join salgrade s on e.sal between s.losal and s.hisal;
1

– 案例： 查询员工与领导的信息

select * from emp e left join emp m on e.mgr = m.empno;
1

2-2 右外连接

select * from emp m right join emp e on e.mgr = m.empno;
1

–oracle中独有外链接： 在内连接的基础上，使用 + 号
– 口诀： + 号在那边，对面的一边就是基准表

select * 
from emp e,emp m
where e.mgr = m.empno(+);
1
2
3

3.子查询

–【范例1】： 查询比SCOTT 用户工资高的员工
步骤：
–1)查询scott用户的工资

select * from emp where ename = 'SCOTT';
--2)把上述的查询作为一个条件
select * 
from emp
where sal > (select sal from emp where ename = 'SCOTT');
1
2
3
4
5

–【范例2】：查询出比雇员 7654 的工资高，同时从事和 7788 的工作一样的员工
步骤：
–1）查询7654的工资

select sal from emp where empno = 7654;
1

–2）查询7788的工作

select job from emp where empno = 7788;
1

–3）把上述的查询作为条件

select * 
from emp
where sal > (select sal from emp where empno = 7654)
      and
      job = (select job from emp where empno = 7788);
1
2
3
4
5

3-1 单行子查询

–查询编号为8888的员工

select *
from emp
where job = (select job from emp where empno = 8888);
1
2
3

3-2 多行子查询

–查询是领导的员工

select * 
from emp
where empno in (select distinct mgr from emp); -- 查询：相当于用  empno = 7902 or empno = 7698 
1
2
3

–查询不是领导的员工: 使用not in 一定要去空!!!

select * 
from emp
where empno not in (select distinct mgr from emp where mgr is not null);-- 相当于：  empno=7902  and  empno=7698 and empno = null
1
2
3

–非法使用子查询的问题 ： 使用等于号的时候，返回的结果应该只有一条，下面的查询 会报错!!!

select * 
from emp
where job = (select job from emp);
1
2
3

3-3 exists的用法

exists(子查询) ：
1）当子查询有返回值，那么就返回true
2）当子查询没有返回值，返回false

select * from emp where 1=1;
select * from emp where 1=2;

–exists 使用

select * from emp where exists(select * from dept where deptno=10);--返回true
select * from emp where exists(select * from dept where deptno=100);--返回false
1
2

–案例: 查询有员工的部门的信息
–使用in

select * 
from dept d
where d.deptno in(select distinct deptno from emp);
1
2
3

–使用exists

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

–in exists区别: in会进行全表扫描； exists不会
–在查询的数据量很大【百万级】的时候，exists的效率高

4.分页

伪列：
ROWNUM:表示行号，实际上只是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
ROWID:表中每行数据指向磁盘上的物理地址。

      for(int i=1;i<10;i++){
              System.out.println(i);
      }
      1 2 3 4 ......9
1
2
3
4

–【案例1】： 找出员工表中的第一页数据： pageSize = 3

select rownum,e.*
from emp e
where rownum <4;
1
2
3

–【案例2】： 找出员工表中的第二页数据： pageSize = 3 : rownum不能使用大于号 ： 没查询一条数据就分配一个行号
–下面的查询是没有数据的

select rownum,e.*
from emp e
where rownum >=4  and rownum <7;
1
2
3

–1、先找到小于7的数据

select rownum,e.*
from emp e
where rownum <7;
1
2
3

–2、把上述的查询当成一张表

select *
from (
      select rownum as rnum,e.*
      from emp e
      where rownum <7
      ) t
where t.rnum >=4;
1
2
3
4
5
6
7

【案例3】：对员工表的工资排序，由大到小，取出第二页，pageSize=3
–1、先排序

select *
from emp
order by sal desc;
1
2
3

-2、-把上述的查询当成一张表

select rownum,t.* 
from (
     select *
     from emp
     order by sal desc
     ) t
where rownum < 7 ;
1
2
3
4
5
6
7

–3、把上面的查询当成一张表

select *
from (
      select rownum as rnum,t.* 
      from (
           select *
           from emp
           order by sal desc
           ) t
      where rownum < 7 
    ) t2
where t2.rnum >=4;
1
2
3
4
5
6
7
8
9
10
11

5.集合运算

/*
要求:
两个集合返回的列数、列的类型、列名要一致
*/

5-1 并集

–案例： 查询工资大于1500 或者 在20号部门下的员工

–先找到大于1500
– 把两个集合联合起来
–再找到20号部门的员工

select * from emp where sal > 1500
union all  -- 有可能有重复的数据
select * from emp where deptno = 20;
1
2
3

select * from emp where sal > 1500
union  -- 把两个集合联合起来，还会去重
select * from emp where deptno = 20;
1
2
3

5-2 交集

– 案例： 找出工资大于1500 并且是 20号部门的员工

select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
1
2
3

5-3 差集

—范例： 1981 年入职的普通员工（不包括总裁和经理）（差集）
–1）查询1981年入职的所有员工

select * from emp where to_char(hiredate,'yyyy') = '1981'; 
1

–2）查询是总裁和经理的员工

select * from emp where job = 'MANAGER' or job = 'PRESIDENT';
1

–3）第一步减去第二步的集合

select * from emp where to_char(hiredate,'yyyy') = '1981'
minus
select * from emp where job = 'MANAGER' or job = 'PRESIDENT';
1
2
3