ORACLE--子查询

oacle基础知识

1、创建表空间

2、创建用户

3、创建一个表

4、数据库标准语言（DDL、DML、TCL、DQL、DCL）

5、数据库相关运算符（特别注意||运算符）

6、ORACLE相关函数及语法使用（group by、 order by、 having等等相关）

http://www.w3school.com.cn/sql/sql_delete.asp

oracle进阶1

1、非关联子查询（子查询中不会涉及到主查询中的列）

非关联子查询的执行过程：

先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。

子查询只执行一遍。

若子查询返回的结果为多个值，ORACLE会自动去掉重复值之后，再将结果返回给主查询。

子查询可以返回单行结果，可以返回多行结果，也可以不返回结果

1）如果子查询未返回任何行，则主查询也不会返回任何结果（空值）

SQL> select * from emp where sal>(select sal from emp where empno=8000);

Warning: connection was lost and re-established

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

2）如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符（正常）

SQL> select * from emp where sal>(select sal from emp where empno=7934);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7499 ALLEN      SALESMAN   7698 1981/2/20     1760.00    300.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

3）如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符（多值，非法）

SQL> select * from emp where deptno=(select deptno from dept );

select * from emp where deptno=(select deptno from dept )

ORA-01427:单行子查询返回多个行

子查询如果返回多行结果时，可以使用一下的运算符处理结果：

1。any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的

SQL> select * from emp where deptno>any(select deptno from dept );

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7499 ALLEN      SALESMAN   7698 1981/2/20     1760.00    300.00     30

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

2。some即一些。和any的用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同。

SQL> select * from emp where deptno=some(select deptno from dept );

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1760.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

3。all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的。

SQL> select * from emp where sal>all(select sal from emp where deptno=30);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

4。In

SQL> select * from emp t where t.deptno in (10,20);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

注释：不管在什么情况下子查询能够保证放回值是1<=1行，则可采用单行比较符：=、<、>、<=、>=等；

否则要采用多行比较符：IN、> ANY 、> ALL、< ANY、< ALL等。

2、关联子查询（子查询会引用外部查询中的一列或多列）

1）、关联子查询执行过程

关联子查询会引用外部查询中的一列或多列.在执行时,外部查询的每一行都被一次一行地传递给子查询.子查询依次读取外部查询传递来的每一值,并将其用到子查询上,直到外部查询所有的行都处理完为止.然后返回查询结果.

 下面例子中,外部查询从emp表中检索出所有的行,并将其传递给内部查询.内部查询依次读取外部查询传递来的每一行数据,并对内部查询中job等于外部查询的中job值的每种产品计算平均价格.如:查询第一行job为1时,把这行数据传递到内部查询,内部查询根据这个编号到产品表中找到同类型的所有行,然后求如平均价格,外部查询再根据这个结果来判断查询条件(外部查询是在执行where语句时用到子查询,子查询执行完后还返回到where子句中).

SQL> select t.ename,t.job,t.sal from emp t where t.sal>(select avg(sal) from emp a where a.job=t.job);

ENAME      JOB             SAL

---------- --------- ---------

ALLEN      SALESMAN    1760.00

JONES      MANAGER     2975.00

BLAKE      MANAGER     2850.00

TURNER     SALESMAN    1500.00

ADAMS      CLERK       1100.00

MILLER     CLERK       1300.00

总结：

子查询又称内部查询，而包含子查询的语句称之外部查询（又称主查询）。

所有的子查询可以分为两类，即关联子查询和非关联子查询。

非关联子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

关联子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。