PL/SQL学习五

18. 连接查询
连接查询是指基于多个表或视图的查询。
当使用多个表进行连接查询的时候：
1.需要在FROM后面指定两个或两个以上的表
2.在SELECT时，列名需要加上表名作为前缀
3.需要在WHERE内指定两表或多表的连接条件，如不指定则视为笛卡尔积(A表记录条数xB表记录条数)
示例:

SQL> SELECT e.ename,e.sal,d.dname
  2  FROM emp e,dept d
  3  WHERE e.deptno=d.deptno
  4  AND e.deptno=10;

ENAME             SAL DNAME
---------- ---------- ------------
CLARK            2450 ACCOUNTING
KING             5000 ACCOUNTING
MILLER           1300 ACCOUNTING

--使用BETWEEN...AND,emp表的sal在b的losal和hisal范围内

SQL> SELECT e.ename,e.sal,b.grade
  2  FROM emp e,salgrade b
  3  WHERE e.sal BETWEEN b.losal AND b.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
MARTIN           1250          2
WARD             1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
FORD             3000          4
SCOTT            3200          5
KING             5000          5

   --自连接(自己连接自己,注意表一定要用别名)，显示BLAKE的上级领导
SQL> SELECT manager.ename FROM emp manager,emp worker
  2  WHERE manager.empno=worker.mgr
  3  AND worker.ename='BLAKE';

ENAME
----------
KING

18.1 内、外连接查询
内连接查询只返回满足连接条件的记录;而外连接不仅会返回满足连接条件的所有记录，还返回不满足连接条件的
记录。在oracle 9i之前，连接语法都在WHERE中指定，9i之后，可以在FROM子句中指定连接语法。
INNER JOIN:内连接
内连接返回满足连接条件的所有记录。默认的=连接就是属于内连接。

SQL> SELECT a.dname, b.ename FROM dept a,emp b
  2  WHERE a.deptno=b.deptno AND a.deptno=30;
 
DNAME          ENAME
-------------- ----------
SALES          ALLEN
SALES          WARD
SALES          MARTIN
SALES          BLAKE
SALES          TURNER
SALES          JAMES
 
6 rows selected

-- 改写为INNER JOIN形式如下：
SQL> SELECT a.dname,b.ename FROM dept a
  2  INNER JOIN emp b
  3  ON a.deptno=b.deptno AND a.deptno=30;
 
DNAME          ENAME
-------------- ----------
SALES          ALLEN
SALES          WARD
SALES          MARTIN
SALES          BLAKE
SALES          TURNER
SALES          JAMES

LEFT JOIN: 左外连接
左外连接，不仅可以返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表
的其他行,如:

--请对比上面的内连接
SQL> SELECT a.dname,b.ename 
  2  FROM dept a
  3  LEFT JOIN emp b
  4  ON a.deptno=b.deptno AND a.deptno=30;
 
DNAME          ENAME
-------------- ----------
SALES          ALLEN
SALES          WARD
SALES          MARTIN
SALES          BLAKE
SALES          TURNER
SALES          JAMES
OPERATIONS     
RESEARCH       
ACCOUNTING     
 
9 rows selected

RIGHT JOIN:右外连接
右外连接不仅可以返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符右边表
的其他行，如：

SQL> SELECT a.dname,b.ename
  2  FROM dept a
  3  RIGHT JOIN emp b
  4  ON a.deptno=b.deptno AND a.deptno=30;
 
DNAME          ENAME
-------------- ----------
SALES          JAMES
SALES          TURNER
SALES          BLAKE
SALES          MARTIN
SALES          WARD
SALES          ALLEN
               MILLER
               KING
               CLARK
               JONES
               SMITH
               SCOTT
               ADAMS
               FORD
 
14 rows selected

FULL JOIN: 全连接
完全连接是指不仅会返回满足连接条件的所有行，还会返回不满足连接条件的所有其他行,如：

SQL> SELECT a.dname,b.ename
  2  FROM dept a
  3  FULL JOIN emp b
  4  ON a.deptno=b.deptno AND a.deptno=30;
 
DNAME          ENAME
-------------- ----------
               FORD
               ADAMS
               SCOTT
               SMITH
SALES          ALLEN
SALES          WARD
               JONES
SALES          MARTIN
SALES          BLAKE
               CLARK
               KING
SALES          TURNER
SALES          JAMES
               MILLER
OPERATIONS     
RESEARCH       
ACCOUNTING     
 
17 rows selected

18.2 使用(+)操作符
在oracle 9i之前，执行外连接的时候，都是使用(+)来完成的。虽然现在仍旧可以使用
操作符(+)执行外连接操作，但oracle开始建议使用OUTER JOIN执行外连接，以使得和标准SQL兼容。
注意1:(+)只能出现在WHERE子句中，并且不能与OUTER JOIN(也就是LEFT JOIN和RIGHT JOIN)同时使用。
注意2：如果在WHERE子句中包含有多个条件，则必须所有条件都包含(+)操作符号。
注意3：(+)只适用于列，而不能用在表达式。
注意4：(+)不能与OR、IN操作符一起使用。
注意5：(+)不能用于实现全连接(FULL JOIN).

--使用(+)执行左外连接，与LEFT JOIN完全一样
SQL> SELECT a.dname,b.ename
  2  FROM dept a,emp b
  3  WHERE a.deptno=b.deptno(+)
  4  AND b.deptno(+)=30;
 
DNAME          ENAME
-------------- ----------
ACCOUNTING     
RESEARCH       
SALES          ALLEN
SALES          WARD
SALES          JAMES
SALES          BLAKE
SALES          TURNER
SALES          MARTIN
OPERATIONS     
 
9 rows selected  
      
--使用(+)执行右外连接，与RIGHT JOIN完全一样
SQL> SELECT a.dname,b.ename
  2  FROM dept a,emp b
  3  WHERE a.deptno(+)=b.deptno
  4  AND a.deptno(+)=30;
 
DNAME          ENAME
-------------- ----------
SALES          JAMES
SALES          TURNER
SALES          BLAKE
SALES          MARTIN
SALES          WARD
SALES          ALLEN
               MILLER
               KING
               CLARK
               JONES
               SMITH
               SCOTT
               ADAMS
               FORD
 
14 rows selected

19. 子查询
子查询又称嵌套查询，是指嵌入在其他SQL语句中的SELECT语句。注意在DDL语句中引用子查询的时候，可以带ORDER BY；
但在WHERE中、SET中引用子查询时，不能带ORDER BY。
通过INSERT或CREATE TABLE使用子查询，可以将源表数据插入到目标表中。
在CREATE VIEW或CREATE MATERIALIZED VIEW(物化视图)中使用子查询，可以定义视图或实体化视图所对应的SELECT语句。
通过UPDATE语句中使用子查询可以修改一列或多列数据。
根据返回结果的不同，分为单行子查询、多行子查询、多列子查询。

19.1 单行子查询
单行子查询是指只返回一行数据的子查询,如：

SQL> SELECT ename,sal,deptno FROM emp
  2  WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');
 
ENAME            SAL DEPTNO
---------- --------- ------
FORD         3000.00     20
ADAMS        1100.00     20
SCOTT        3200.00     20
SMITH         800.00     20
JONES        2975.00     20

19.2 多行子查询
当在WHERE中使用多行子查询的时候，必须要使用多行比较符号，如IN、ALL、ANY等。
IN:匹配子查询结果集中任一个值

SQL> SELECT ename,job,sal,deptno 
  2  FROM emp WHERE job IN
  3  (SELECT job FROM emp WHERE deptno=10);
 
ENAME      JOB             SAL DEPTNO
---------- --------- --------- ------
CLARK      MANAGER     2450.00     10
BLAKE      MANAGER     2850.00     30
JONES      MANAGER     2975.00     20
KING       PRESIDENT   5000.00     10
MILLER     CLERK       1300.00     10
JAMES      CLERK        950.00     30
SMITH      CLERK        800.00     20
SCOTT      CLERK       3200.00     20
ADAMS      CLERK       1100.00     20
 
9 rows selected

ALL：必须要符合子查询结果集中的所有值

SQL> SELECT ename,sal,deptno 
  2  FROM emp WHERE sal>ALL
  3  (SELECT sal FROM emp WHERE deptno=30);
 
ENAME            SAL DEPTNO
---------- --------- ------
JONES        2975.00     20
FORD         3000.00     20
SCOTT        3200.00     20
KING         5000.00     10

ANY：只要符合子查询结果集中的任一个值即可

SQL> SELECT ename,sal,deptno
  2  FROM emp WHERE sal>ANY
  3  (SELECT sal FROM emp WHERE deptno=30);
 
ENAME            SAL DEPTNO
---------- --------- ------
KING         5000.00     10
SCOTT        3200.00     20
FORD         3000.00     20
JONES        2975.00     20
BLAKE        2850.00     30
CLARK        2450.00     10
ALLEN        1600.00     30
TURNER       1500.00     30
MILLER       1300.00     10
MARTIN       1250.00     30
WARD         1250.00     30
ADAMS        1100.00     20
 
12 rows selected

19.3 多列子查询
单行或多行子查询都是针对单列而言的，一个是返回单列单行数据，一个是返回单列多行数据。
而多列子查询是指返回多列数据的查询，如：

--获得与SMITH部门号和职位一样的员工的姓名、职位、工资、部门信息
SQL> SELECT ename,job,sal,deptno
  2  FROM emp WHERE (deptno,job)=
  3  (SELECT deptno,job FROM emp WHERE ename='SMITH' );
 
ENAME      JOB             SAL DEPTNO
---------- --------- --------- ------
ADAMS      CLERK       1100.00     20
SCOTT      CLERK       3200.00     20
SMITH      CLERK        800.00     20     

19.4 其他子查询
除了可以使用单行子查询、多行子查询以及多列子查询外，还可以使用相关子查询。

--使用EXIST谓词实现相关子查询,显示在'NEW YORK'工作的所有雇员
SQL> SELECT ename,job,sal,deptno FROM emp WHERE
  2  EXISTS
  3  (SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');
 
ENAME      JOB             SAL DEPTNO
---------- --------- --------- ------
MILLER     CLERK       1300.00     10
CLARK      MANAGER     2450.00     10
KING       PRESIDENT   5000.00     10

在FROM子句中使用子查询的时候(注意一定要给子查询起别名)，该子查询会被作为视图对待，因此也称为内嵌视图。

--FROM中使用子查询
SQL> SELECT ename,job,sal FROM emp,
  2  (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) dept
  3  WHERE emp.deptno=dept.deptno AND sal>dept.avgsal;
 
ENAME      JOB             SAL
---------- --------- ---------
FORD       ANALYST     3000.00
SCOTT      CLERK       3200.00
ALLEN      SALESMAN    1600.00
JONES      MANAGER     2975.00
BLAKE      MANAGER     2850.00
KING       PRESIDENT   5000.00
 
6 rows selected      

在DML中使用子查询

--INSERT中使用子查询
SQL> INSERT INTO employee(EMPNO,ENAME,JOB,SAL)
  2  SELECT empno,ename,job,sal FROM emp;
 
14 rows inserted

--UPDATE中使用子查询
SQL> UPDATE emp SET (sal,comm)=
  2  (SELECT sal,comm FROM emp WHERE ename='SMITH')
  3  WHERE job=(SELECT job FROM emp WHERE ename='SMITH');
 
5 rows updated

--DELETE中使用子查询
SQL> DELETE FROM emp WHERE deptno=
  2  (SELECT deptno FROM dept WHERE dname='SALES');
 
6 rows deleted

19.5 在DDL中使用子查询

--CREATE TABLE中使用子查询
SQL> CREATE TABLE EMP_TEST AS (SELECT * FROM emp WHERE ROWNUM<4);
 
Table created

--CREATE VIEW中使用子查询
SQL> CREATE OR REPLACE VIEW dept_test AS
  2  SELECT empno,ename,job,sal FROM emp
  3  WHERE deptno=30 ORDER BY empno;
  
--CREATE MATERIALIZED VIEW中使用子查询
SQL> CREATE MATERIALIZED VIEW sum_emp AS
  2  SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal
  3  FROM emp GROUP BY cube(deptno,job);
  
Materialized view created