oracle基础知识4_多表查询

83ol7iee03

　　

   || 符号解释: 字符串连接，相当于 mssql中的 +
　　
　　笛卡尔集:
　　
　　联合两个表 emp, dept 查询数据，那么查询的结果:
　　1 列是两个表的列数相加
　　2 行数是这两个表的行数乘积
　　3 查询结果会在两个表形成笛卡尔集结果后在按照条件筛选得到。
　　
　　

 
　　
　　笛卡尔集产生条件:
　　
　　1 查询时省略连接条件
　　2 连接条件无效
　　3 所有表中的所有行相互连接
　　
　　为了提高查询效率，应该让where条件的连接条件更有效
　　学多表下查询就是学连接条件如何写。
　　
　　连接条件分类:
　　1 等值连接
　　查询员工信息： 员工号 姓名 月薪 部门名称
　　select e.empno,e.ename,e.sal,d.dname
    from emp e,dept d
    where e.deptno=d.deptno;
　　
　　2 不等值连接
　　select e.empno,e.ename,e.sal,s.grade
    from emp e,salgrade s
    where e.sal between s.losal and s.hisal;
　　
　　3 外连接(在结果集中，包含某些不成立的记录)
　　看如下写法：
　　
　　按部门统计员工人数
　　select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
   from emp e,dept d
   where e.deptno=d.deptno
   group by d.deptno,d.dname
　　


部门号
部门名称
人数


10
ACCOUNTING
3


20
RESEARCH
5


30
SALES
6
　　
　　结果: 部门号40无法显示出来,虽然这部门下没人，但是按照题目要求，显示所有部门人数，应该显示40号部门为0人。
　　未显示原因：因为用条件 e.deptno=d.deptno，表emp中没有deptno=40的记录，因此查询结果必然没有40这项。
　　
　　
　　
　　
　　左外连接: 当where e.deptno=d.deptno不成立的时候,等号左边所代表的表记录 任然被包含
　　tablea left join tableb on 条件
　　右外连接: 当where e.deptno=d.deptno不成立的时候,等号右边所代表的表记录 任然被包含
　　tablea rigth join tableb on 条件
　　
　　使用右外连接，达到需要的效果，语句如下：
　　select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
   from emp e right join dept d
   on e.deptno=d.deptno
   group by d.deptno,d.dname
   order by d.deptno ;
　　查询结果如下：


部门号
部门名称
人数


10
ACCOUNTING
4


20
RESEARCH
5


30
SALES
6


40
OPERATIONS
0
　　
　　
　　
　　4 自连接(通过表的别名，将同一张表视为多张表====>就是自己跟自己连接查询,工作中常涉及到 部门表， 帖子表 设计成自连接查询)
　　
　　select e.ename||'的领导是'||b.ename
   from emp e,emp b
   where e.mgr=b.empno;
　　

FORD的领导是JONES

SCOTT的领导是JONES

JAMES的领导是BLAKE

TURNER的领导是BLAKE

MARTIN的领导是BLAKE

　　
　　
　　自连接操作不太适合操作太大的表，否则查询的时候产生的笛卡尔集会随着表记录增长而成平方级别的增长
　　
　　
　　5 层次查询(遍历的是一棵树，利用递归)
　　
　　分析上面案例，得到领导和下属关系的树结构图如下：
　　

 
　　前一次操作的员工号是后一次操作的领导号
　　select level,empno,ename,sal,mgr
    from emp
    connect by prior empno=mgr
    start with mgr is null
    order by level;  --->  可以修改成 order by 1;   1表示查询条件的第一个字段，即level
　　
　　
　　执行的过程:
 1. KING: start with mgr is null ---> empno=7839
 2. where mgr = 7839; ---> 7566 7698 7782
 3. where mgr in (7566 7698 7782)
　　
　　结果:


LEVEL
EMPNO
ENAME
SAL
MGR


1
7839
KING
5000
 


2
7566
JONES
2975
7839


2
7698
BLAKE
2850
7839


2
7782
CLARK
2450
7839


3
7902
FORD
3000
7566


3
7521
WARD
1250
7698


3
7900
JAMES
950
7698


3
7934
MILLER
1300
7782
　　
　　
　　多表连接查询案例;
　　查询员工工资大于本部门平均工资的员工信息:(涉及的知识1 子查询 2 多表查询 3 函数 4 分组)
　　
　　select e.empno,e.deptno,e.ename,e.sal,d.avgsal
 from emp e,(select deptno,avg(sal)avgsal from emp group by deptno) d
  where  e.deptno= d.deptno and e.sal > d.avgsal;
　　


EMPNO
DEPTNO
ENAME
SAL
AVGSAL


7698
30
BLAKE
2850
1566.66667


7499
30
ALLEN
1600
1566.66667


7902
20
FORD
3000
2175


7788
20
SCOTT
3000
2175


7566
20
JONES
2975
2175


7839
10
KING
5000
3687.5


1001
10
Tom_ABCD
6000
3687.5