oracle-多表查询

淩亂°似流年 2022-01-29 08:47 302阅读 0赞

范例：使用别名；

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e,dept d where e.deptno = d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="486" src="https://img-blog.csdnimg.cn/20190521161313993.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：要求查询出每个雇员的编号、姓名、职位、工资、部门名称、部分位置

|- 确定使用的数据表：

1.  emp表：雇员的编号，姓名，职位，工资
2.  dept表：部门名称，部门位置

|- 确立已知的关联条件

1.  雇员和部门：emp.deptno = dept.deptno;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select empno,ename,job,sal,dname,loc from emp e,dept d where e.deptno = d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="440" src="https://img-blog.csdnimg.cn/20190521161313978.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询出每个雇员的编号、姓名、工资、职位、雇用日期、工资等级

|- 确定使用的数据表：

1.  emp表：雇员的编号，姓名，工资，职位，雇佣日期
2.  salgrade表：工资等级

|- 确立已知的关联条件：

雇员与工资等级：emp.sal between salgrade.losal and salgrade.hisal;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select empno,ename,job,sal,hiredate,grade from emp e,salgrade s where e.sal between s.losal and s.hisal;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="420" src="https://img-blog.csdnimg.cn/201905211613147.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1169"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询每一个雇员的编号、姓名、职位、雇用日期、工资、工资等级、所在部门及位置

|- 确定要使用的数据表：

1.  emp表：雇员的编号，姓名，职位，雇佣日期，工资
2.  salgrade表：工资等级
3.  dept表：部门名称及位置

|- 确立已知的关联条件：只要是消除笛卡尔积的条件之间都是用AND连接操作

1.  雇员和工资等级：emp.sal between salgrade.losal and salgrade.hisal;
2.  雇员和部门：emp.deptno = dept.deptno;

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select empno,ename,job,hiredate,sal,grade,dname,loc from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and e.deptno = d.deptno;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="419" src="https://img-blog.csdnimg.cn/2019052116131430.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

右外连接

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e,dept d where e.deptno<span style="color:#FF0000;">(+)</span>=d.deptno;&nbsp;&nbsp; --右外连接加号在左边</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="462" src="https://img-blog.csdnimg.cn/2019052116131460.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

左外连接

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e,dept d where e.deptno=d.deptno(+);&nbsp;&nbsp; --左外连接加号在右边</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="411" src="https://img-blog.csdnimg.cn/2019052116131483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询每个雇员的编号、姓名、职位、领导姓名

1.  查询emp表
2.  查询领导姓名
3.  查询领导姓名è内连接，等值连接，有null的不显示

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select e.empno,e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno(+);</p> <p style="margin-left:0cm;">--此处需要注意的是因为是用的同一个表格，所以必须加.区分表格e和表格m</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="461" src="https://img-blog.csdnimg.cn/20190521161314110.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1072"></p> </td> 
  </tr> 
 </tbody> 
</table>

4、

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp cross join dept;&nbsp;&nbsp; --产生笛卡尔积 15*5</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp natural join dept;&nbsp;&nbsp; --自然连接，消除笛卡尔积</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="427" src="https://img-blog.csdnimg.cn/20190521161314151.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp join dept using(deptno);&nbsp;&nbsp; --用户指定关联字段</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="459" src="https://img-blog.csdnimg.cn/20190521161314583.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e join dept d on(e.deptno=d.deptno);&nbsp; --使用on子句，用户设置关联条件</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="457" src="https://img-blog.csdnimg.cn/2019052116131692.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

5、

外连接

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e left outer join dept d on(e.deptno=d.deptno);&nbsp;&nbsp; --左外连接</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="436" src="https://img-blog.csdnimg.cn/20190521161316108.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e right outer join dept d on(e.deptno=d.deptno);&nbsp;&nbsp; --右外连接</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="490" src="https://img-blog.csdnimg.cn/20190521161316107.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp e full outer join dept d on(e.deptno=d.deptno);&nbsp;&nbsp;&nbsp; --全外连接</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="483" src="https://img-blog.csdnimg.cn/20190521161316120.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>

6、

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp where empno=10 union select * from emp;&nbsp; ---union操作，不显示重复记录</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="450" src="https://img-blog.csdnimg.cn/20190521161315589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp where deptno=10 union all select * from emp;&nbsp;&nbsp; --union all显示重复记录</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="500" src="https://img-blog.csdnimg.cn/20190521161315634.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp where deptno=10 intersect select * from emp;&nbsp;&nbsp; --intersect表示交集操作</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="196" src="https://img-blog.csdnimg.cn/20190521161315686.png" width="1200"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">select * from emp minus select * from emp where deptno=10;&nbsp;&nbsp; --minus表示差集操作</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;"><img alt="" height="411" src="https://img-blog.csdnimg.cn/20190521161316108.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="1200"></p> </td> 
  </tr> 
 </tbody> 
</table>