oracle--多表查询

多表查询

笛卡尔积:

笛卡尔积.PNG

笛卡尔积会在下面条件下产生:

省略连接条件
连接条件无效
所有表中的所有行互相连接

为了避免笛卡尔积,可以在where加入有效的连接条件
在实际运行环境下,应避免使用笛卡尔全集。

连接的类型:

oracle的连接

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

SQL99的连接

Cross joins
Natural joins
Using clause
Full or two sided outer joins

等值连接:

SQL> --等值连接
SQL> --查询员工信息: 员工号 姓名 月薪 部门名称
SQL> set linesize 80
SQL> desc dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> select e.empno,e.ename,e.sal,d.dname
  2  from emp e,dept d
  3  where e.deptno=d.deptno;

     EMPNO ENAME             SAL DNAME                                          
---------- ---------- ---------- --------------                                 
      7369 SMITH             800 RESEARCH                                       
      7499 ALLEN            1600 SALES                                          
      7521 WARD             1250 SALES                                          
      7566 JONES            2975 RESEARCH                                       
      7654 MARTIN           1250 SALES                                          
      7698 BLAKE            2850 SALES                                          
      7782 CLARK            2450 ACCOUNTING                                     
      7788 SCOTT            3000 RESEARCH                                       
      7839 KING             5000 ACCOUNTING                                     
      7844 TURNER           1500 SALES                                          
      7876 ADAMS            1100 RESEARCH                                       

     EMPNO ENAME             SAL DNAME                                          
---------- ---------- ---------- --------------                                 
      7900 JAMES             950 SALES                                          
      7902 FORD             3000 RESEARCH                                       
      7934 MILLER           1300 ACCOUNTING 

不等值连接:

SQL> --不等值连接
SQL> --查询员工信息: 员工号 姓名 月薪 工资级别
SQL> select * from salgrade;

     GRADE      LOSAL      HISAL                                                
---------- ---------- ----------                                                
         1        700       1200                                                
         2       1201       1400                                                
         3       1401       2000                                                
         4       2001       3000                                                
         5       3001       9999                                                

SQL> select e.empno,e.ename,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

     EMPNO ENAME             SAL      GRADE                                     
---------- ---------- ---------- ----------                                     
      7369 SMITH             800          1                                     
      7900 JAMES             950          1                                     
      7876 ADAMS            1100          1                                     
      7521 WARD             1250          2                                     
      7654 MARTIN           1250          2                                     
      7934 MILLER           1300          2                                     
      7844 TURNER           1500          3                                     
      7499 ALLEN            1600          3                                     
      7782 CLARK            2450          4                                     
      7698 BLAKE            2850          4                                     
      7566 JONES            2975          4                                     

     EMPNO ENAME             SAL      GRADE                                     
---------- ---------- ---------- ----------                                     
      7788 SCOTT            3000          4                                     
      7902 FORD             3000          4                                     
      7839 KING             5000          5   

外连接:

连接n个表,至少需要n-1个连接条件。

SQL> --外连接
SQL> --按部门统计员工人数:部门号 部门名称 人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno=d.deptno
  4  group by d.deptno,d.dname;

    部门号 部门名称             人数                                            
---------- -------------- ----------                                            
        10 ACCOUNTING              3                                            
        20 RESEARCH                5                                            
        30 SALES                   6                                            

SQL> select * from dept;

    DEPTNO DNAME          LOC                                                   
---------- -------------- -------------                                         
        10 ACCOUNTING     NEW YORK                                              
        20 RESEARCH       DALLAS                                                
        30 SALES          CHICAGO                                               
        40 OPERATIONS     BOSTON                                                

SQL> select * from emp where deptno=40;

未选定行

希望: 对于某些不成立的记录,任然希望包含在最后的结果中

使用外连接:

左外连接:当where e.deptno=d.deptno不成立的时候,等号左边的表任然被包含
写法:where e.deptno=d.deptno(+)

右外连接:当where e.deptno=d.deptno不成立的时候,等号右边的表任然被包含
写法: where e.deptno(+)=d.deptno

例:

SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno,d.dname;

    部门号 部门名称             人数                                            
---------- -------------- ----------                                            
        10 ACCOUNTING              3                                            
        40 OPERATIONS              0                                            
        20 RESEARCH                5                                            
        30 SALES                   6  

自连接:

SQL> --自连接
SQL> select e.ename 员工姓名,b.ename 老板姓名
  2  from emp e,emp b
  3  where e.mgr=b.empno;

员工姓名   老板姓名                                                                                                                                                                                     
---------- ----------                                                                                                                                                                                   
FORD       JONES                                                                                                                                                                                        
SCOTT      JONES                                                                                                                                                                                        
JAMES      BLAKE                                                                                                                                                                                        
TURNER     BLAKE                                                                                                                                                                                        
MARTIN     BLAKE                                                                                                                                                                                        
WARD       BLAKE                                                                                                                                                                                        
ALLEN      BLAKE                                                                                                                                                                                        
MILLER     CLARK                                                                                                                                                                                        
ADAMS      SCOTT                                                                                                                                                                                        
CLARK      KING                                                                                                                                                                                         
BLAKE      KING       

SQL> select count(*)
  2  from emp e, emp b;

  COUNT(*)                                                                                                                                                                                              
----------                                                                                                                                                                                              
       196   

自连接不适合操作大表:使用层次查询

层次查询.PNG
SQL> select level,empno,ename,mgr
  2  from emp
  3  connect by prior empno=mgr
  4  start with mgr is null
  5  order by 1;
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • SQL多表查询 等值和不等值连接查询 从多个表中获取数据:如果在查询的时候,直接从多个表中获取数据。没有添加条件判...
    MPPC阅读 5,674评论 0 16
  • 在探讨机会之前,先复习一下两点: 第一,感受经济的定义:生产力高度发展,商品经济发展到大多数产品达到买方市场,特别...
    Yeny爱生活阅读 2,325评论 0 0
  • 最近感触很多,说说我心目中的好老师形象! 小学的事我已经记不清了,所以这段只能跳过去了。 初中我一共换了两个班,所...
    飞云冉冉阅读 2,701评论 0 6
  • 相信世界上永远不抛弃我的男人,除了我爸,还有我的干弟弟。 我和他没有血缘关系,却沉淀了亲姐弟一样的感情。在家家户户...
    灵犀傻丫头阅读 5,127评论 5 2