Oracle 高级查询

1. 多表查询

基本概念

• 从多个表中获取数据

• 笛卡尔积，列数=两表列数之和，行数=两表行数之积

  

连接条件至少=表数量-1

2. 连接类型

2.1 等值连接

• 查询员工信息，要求显示：员工号，姓名，月薪，部门名称

  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
        7839 KING                       5000 ACCOUNTING
        7844 TURNER                     1500 SALES
        7900 JAMES                       950 SALES
        7902 FORD                       3000 RESEARCH
        7934 MILLER                     1300 ACCOUNTING
  
  已选择 12 行。
  

不等值连接

• 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别

  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
        7521 WARD                       1250          2
        7654 MARTIN                     1250          2
        7934 MILLER                     1300          2
        7499 ALLEN                      1600          3
        7844 TURNER                     1500          3
        7566 JONES                      2975          4
        7698 BLAKE                      2850          4
        7782 CLARK                      2450          4
        7902 FORD                       3000          4
        7839 KING                       5000          5
  

外连接

核心：通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中
左外链接：当连接条件不成立的时候，等号左边的表仍然被包含
右外链接：当连接条件不成立的时候，等号右边的表仍然被包含

• 按部门统计员工人数，要求显示：部门号，部门名称，人数

  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                              3
          30 SALES    SQL> select * from dept;
  
      DEPTNO DNAME                        LOC
  ---------- ---------------------------- --------------------------
          10 ACCOUNTING                   NEW YORK
  
  
          20 RESEARCH                     DALLAS
  
  
          30 SALES                        CHICAGO
  
  
          40 OPERATIONS                   BOSTON
                               6
  

  SQL> select * from dept;
  
      DEPTNO DNAME                        LOC
  ---------- ---------------------------- --------------------------
          10 ACCOUNTING                   NEW YORK
  
  
          20 RESEARCH                     DALLAS
  
  
          30 SALES                        CHICAGO
  
  
          40 OPERATIONS                   BOSTON
  
  

  上面少了一条部门的记录，原因是什么？员工表中不存在40号部门的员工

  

• 右外连接

  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
    5  ;
  
      部门号 部门名称                           人数
  ---------- ---------------------------- ----------
          30 SALES                                 6
          10 ACCOUNTING                            3
          20 RESEARCH                              3
          40 OPERATIONS                            0
  

自连接

• 查询员工姓名和员工的老板姓名

  SQL> SELECT e.ENAME 员工姓名,b.ENAME 老板姓名
    2  FROM EMP e,EMP b
    3  where e.MGR=b.EMPNO
    4  ;
  
  员工姓名             老板姓名
  -------------------- --------------------
  FORD                 JONES
  ALLEN                BLAKE
  WARD                 BLAKE
  MARTIN               BLAKE
  TURNER               BLAKE
  JAMES                BLAKE
  MILLER               CLARK
  JONES                KING
  BLAKE                KING
  CLARK                KING
  SMITH                FORD
  

自连接存在的问题，本质上还是多表查询

• 不适合操作大表
• 解决办法：层次查询

上层员工号=下层员工的老板号

SQL> select level, empno,ename,sal,mgr
  2  from emp
  3  connect by prior empno=mgr
  4  start with mgr is null
  5  ORDER BY 1;

     LEVEL      EMPNO ENAME                       SAL        MGR
---------- ---------- -------------------- ---------- ----------
         1       7839 KING                       5000