oracle 数据库3——关联查询

桃扇骨 2022-06-17 00:12 221阅读 0赞

**1. 关联查询概述**

1.  关联查询就是从多张表中关联查询数据，重点是多张表之间的连接条件，根据该条件数据库才知道这些表之间数据的对应关系，从而分别获取这些对应的记录中查询的字段并构成结果集。
2.  关联查询要有连接条件，不添加连接条件会产生笛卡尔积，这通常是一个无意义的结果集。
3.  原则上 N 张表关联至少需要有 N-1 个连接条件。

**2. 关联查询的分类：**

*  关联查询有内连接、外连接、自连接三种情况。

**3. 等值连接**

*  是连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将**关联条件设定为有关系的列，使用“=”连接有关系的表**。等值连接是关联查询的基础。

--没有关联条件会出现笛卡尔积
    select ename,dname from emp,dept; 
    
    --不会出现笛卡尔积
    select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;
    
    --表也可以使用别名，方便操作
    select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
    
    --where 中关联条件和过滤条件必须同时成立，否则出现笛卡尔积
    select e.name,e.job,d.loc from emp e,dept d where e.deptno = d.deptno AND e.job='SALESMAN';
    
    --查看每个地区的平均工资
    select d.loc,AVG(e.salary) from emp e,dept d where e.deptno = d.deptno group by d.loc;

**4. 关联查询分类**

<table> 
 <thead> 
  <tr> 
   <th>关联查询</th> 
   <th>关键字</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>内连接</td> 
   <td>JOIN ON</td> 
   <td>关联查询中不满足连接条件的数据不会被查询出来</td> 
  </tr> 
  <tr> 
   <td>外连接</td> 
   <td>左外连接（LEFT OUTER JOIN ON）</td> 
   <td>以 JOIN 左侧表为驱动表，该表中所有记录都要显示，那么右侧表的字段若不满足连接条件则为 NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>右外连接（RIGHT OUTER JOIN ON）</td> 
   <td>以 JOIN 右侧表为驱动表，右侧表的所有记录都要显示，左侧表中不满足连接条件的字段则为 NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>全外连接(FULL OUTER JOIN ON)</td> 
   <td>除了满足返回连接条件的记录，还将不满足连接条件的所有行返回，是左外连接和右外连接查询结果的总和</td> 
  </tr> 
  <tr> 
   <td>自连接</td> 
   <td>JOIN ON</td> 
   <td>数据来源是一个表，通过别名将一张表虚拟成两张表来实现，关联关系是来自单表中的多个列</td> 
  </tr> 
 </tbody> 
</table>

**内连接**  
只显示满足连接条件的记录

--查看每个员工以及所在部门名
    select e.ename,d.dname from emp e JOIN dept d ON e.deptno = d.deptno;
    
    --也可以用等值连接来表示：
    select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

**外连接**  
不仅将满足连接条件的记录查询出来，还将驱动表中的记录都显示出来，不满足条件的字段为 NULL

--左外连接（JOIN 左边为驱动表）
    --列出所有员工及其所在部门名（所有员工为驱动表）
    select e.ename,d.dname from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

等值连接（普通连接）也可以作外连接操作，但是不可以作全外连接操作。  
\+ 在连接条件的左边，左边为 NULL ，是右外连接；  
\+ 在连接条件的右边，右边为 NULL，是左外连接；  
\+ 不能连接条件两边都写，所以等值连接不可以作全外连接；

--左外连接
    select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno(+);

**自连接**  
自连接是一种特殊的连接查询，数据来源是一个表，即关联关系来自于单表中的多个列；  
表中的列参照同一个表中的其他列的情况，称作自参照表；  
自连接是当前表的一条数据对应当前表的多条数据；  
自连接用于解决相同属性的一组数据，但是又存在上下级关系的树状结构组织时使用；

--查看每个员工及其上司的名字
    select e.enmae,m.ename from emp e JOIN emp m ON e.mgr = m.mepno;