Oracle--多表查询、层次查询详解

男娘i 2021-08-13 23:28 661阅读 0赞

#### 什么是笛卡尔积？ ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

#### Oracle的连接条件的类型 ####

*  等值连接
 *  不等值连接
 *  外连接
 *  自连接

#### Oracle多表连接示例 ####

**1.多表连接基本查询**  
使用一张以上的表做查询就是多表查询  
语法：

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

示例：查询员工表和部门表

select * from emp,dept;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]  
我们发线产生的记录数是56条，我们还会发现emp表是14条，dept表是4条，56正是emp表和dept表的记录数的乘积，我们称其为笛卡尔积。  
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。  
再两张表中我们发现有一个共同的字段是depno，depno就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

select * from emp,dept where emp.deptno = dept.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]关联之后我们发线数据条数是14条，不再是56条。  
多表查询我们可以为每一张表起一个别名

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

示例：查询出雇员的编号，姓名，部门的编号和名称，地址

select e.empno, e.ename, d.deptno, d.dname, d.loc
    from emp e, dept d
    where e.deptno = d.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]  
示例：查询出每个员工的上级领导  
分析：emp表中的mgr字段是当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，剋使用mgr字段和empno来关联

select e.empno, e.ename, e2.empno,e2.ename
    from emp e, emp e2
    where e.mgr = e2.empno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]  
示例：再上一个例子的基础上查询该员工的部门名称  
分析：只要再上一个例子基础上再加一张表的关联，使用deptno来做关联字段即可

select e.empno, e.ename, e1.empno, e1.ename, d.dname
    from emp e, emp e1, dept d
    where e.mgr = e1.empno and e.deptno=d.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]  
示例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资，等级

select e.empno,e.ename, decode(s.grade,
                                1,'一级',
                                2,'二级',
                                3,'三级',
                                4,'四级',
                                5,'五级') grade,
                          d.dname,e1.empno, e1.ename,
                          decode(s1.grade,
                                    1,'一级',
                                    2,'二级',
                                    3,'三级',
                                    4,'四级',
                                    5,'五级'）grade
    from emp e, emp e1, dept d, salgrade s, salgrade s1
    where e.mgr = e1.empno 
    and e.deptno = d.deptno
    and e.sal between s.losal and s.hisal
    and e1.sal between s1.losal and s1.hisal;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]

#### 外连接（左右连接） ####

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

select e.empno, e.ename, d.deptno, d.dname
    from emp e, dept d
    where e.deptno(+) = d.deptno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]使用（+）表示左连接或者右连接。

示例：查询出所有员工的上级领导  
分析：我们使用以前的做法发现KING的没有上级领导，需要使用外连接把他查询出来

select e.empno, e.ename, m.empno, m.ename
    from emp e, emp m
    where e.mgr = m.empno(+);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 8]  
使用left join实现：

select e.deptno, e.ename, m.deptno, m.ename
    from emp e left join emp m
    on e.mgr = m.empno;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 9]  
（+）这种形式时Oracle数据库独有的，所以left join或right join 也需要了解。

### 层次查询（树型结构查询） ###

层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:

SELECT [LEVEL] ,column,expression,...
    
           FROM table_name
    
           [WHERE where_clause]
    
           [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

*  LEVEL:  
    为伪列,用于表示树的层次
 *  start\_condition：  
    层次化查询的起始条件
 *  prior\_condition：  
    定义父节点和子节点之间的关系

示例：查询所有员工的上级领导  
分析：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 10]起始条件为King没有上级，子节点与父节点的关系为：子节点的mgr=父节点的empno

select level, empno, ename, mgr
    from emp
    start with mgr is null
    connect by prior empno = mgr
    order by 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 11]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/c0d3038ffb9b4948ae4a216d3b2564e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/53d13b8af15a42b5a79d23cd812a4806.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/309e3a26403a4195af2a4d3c7f3d4327.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/059f7954ea5d45faa6fcd42f721e87a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]: /images/20210813/ccda933929844f7aa7cc1126d6596608.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]: /images/20210813/7340a4af94b9475fa38bd27359d1a7b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]: /images/20210813/260d138be5944d8398f9b2a41396327a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]: /images/20210813/b1d125d14ca24869b15158dea94b091a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 8]: /images/20210813/692d6c7419b54a8f84d18d687ee7b9d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 9]: /images/20210813/a7a18e49dfa9466882458717f76e991a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 10]: /images/20210813/6384828ef1514cb38d2aa40b5e8c632d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 11]: /images/20210813/34a8a0fe506a4bfb92f52f6a6410edc7.png