Oracle子查询与伪列

1.   子查询

子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：

1.       单行子查询：不向外部返回结果，或者只返回一行结果。

2.       多行子查询：向外部返回零行、一行或者多行结果。

 

案例1：查询出销售部（SALES）下面的员工姓名，工作，工资。

案例分析

该问题可以用联接查询实现，由于所需的结果信息都在Emp表中，可以先从Dept表中查询出销售部对应的部门号，然后根据当前部门号再到Emp表中查询出符合该部门的员工记录即可。从销售表中查询出的结果可以作为Emp表中查询的条件，SQL语句实现如下：

 

代码演示：单行子查询

SQL> SELECT ENAME,JOB,SAL FROM EMP

  2  WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') ①

  3  /

 ENAME

JOB

SAL

ALLEN

SALESMAN

1600

WARD

SALESMAN

1250

MARTIN

SALESMAN

1250

BLAKE

MANAGER

2850

TURNER

SALESMAN

1500

JAMES

CLERK

950

 6 rows selected

代码解析：

①  内部查询的结果作为外部查询的条件。

 

需要注意：

Ø  如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返还任何结果。

Ø  在单行子查询中外部查询可以使用=、>、<、>=、<=、<>等比较运算符。

Ø  内部查询返回的结果必须与外部查询条件中的字段（DEPTNO）匹配。

Ø  如果内部查询返回多行结果则出现错误。

 

案例2：查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。

案例分析

销售员在Emp表中有很多条记录，每个人工资不相等，如果返回“比任意员工的工资还低”的条件，返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、<等）运行就会出错。这时候需要用关键字ANY。ANY放在比较运算符后面，表示“任意”的意思。

 

代码演示：ANY子查询

SQL> SELECT ENAME,JOB,SAL FROM EMP

  2  WHERE SAL<ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN')  ①

  3  /

ENAME

JOB

SAL

SMITH

CLERK

800

JAMES

CLERK

950

ADAMS

CLERK

1100

WARD

SALESMAN

1250

MARTIN

SALESMAN

1250

MILLER

CLERK

1300

TURNER

SALESMAN

1500

 7 rows selected

代码解析：

①  <any:比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么同理>any表示比子查询结果中最小的还大。

 

案例3：查询出比所有销售员的工资都高的员工姓名，工作，工资。

 

案例分析

ANY可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个关键字ALL。ALL与关系操作符一起使用，表示与子查询中所有元素比较。

 

代码演示：ALL子查询

SQL> SELECT ENAME,JOB,SAL FROM EMP

  2  WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')  ①

  3  /

 ENAME

JOB

SAL

JONES

MANAGER

2975

BLAKE

MANAGER

2850

CLARK

MANAGER

2450

SCOTT

ANALYST

3000

KING

PRESIDENT

5000

FORD

ANALYST

3000

6 rows selected

代码解析：

①  >ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。<ALL表示比最小值还要小。

 

对于子查询还可以使用IN和NOT IN操作符进行操作。

 

2.   Oracle中的伪列

在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID和ROWNUM。

 

²  ROWID

 

表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。

 

代码演示：ROWID

SQL> SELECT ROWID,ENAME FROM EMP WHERE SAL>2000;

ROWID

ENAME

AAAMgzAAEAAAAAgAAD

JONES

AAAMgzAAEAAAAAgAAF

BLAKE

AAAMgzAAEAAAAAgAAG

CLARK

AAAMgzAAEAAAAAgAAH

SCOTT

AAAMgzAAEAAAAAgAAI

KING

AAAMgzAAEAAAAAgAAM

FORD

6 rows selected

 

²  ROWNUM

 

在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。

 

ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。

 

案例4：查询出员工表中前5名员工的姓名，工作，工资。

 

代码演示：ROWNUM

SQL> SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;

 ROWNUM

ENAME

JOB

SAL

1

SMITH

CLERK

800

2

ALLEN

SALESMAN

1600

3

WARD

SALESMAN

1250

4

JONES

MANAGER

2975

5

MARTIN

SALESMAN

1250

 

案例5：查询出工资最高的前5名员工的姓名、工资和工资。

案例分析

“工资最高的前5名”需要先降序排序，再取前5名，但是生成ROWNUM操作比排序要早，排序时已经连同ROWNUM一起排序了，因此不能直接在案例1的语句中直接加上Order by就行，而是需要对排序的结果重新做二次查询，产生新的ROWNUM才能作为查询的条件依据。

 

代码演示：ROWNUM应用

SQL> SELECT ROWNUM,T.* FROM  ①

  2      (SELECT ENAME,JOB,SAL

  3       FROM EMP ORDER BY SAL DESC) T  ②

  4  WHERE ROWNUM<=5

  5  /

 

ROWNUM

ENAME

JOB

SAL

1

KING

PRESIDENT

5000

2

SCOTT

ANALYST

3000

3

FORD

ANALYST

3000

4

JONES

MANAGER

2975

5

BLAKE

MANAGER

2850

代码解析：

①  T是子查询②的别名，这里的ROWNUM是第二次查询后的ROWNUM。

 

案例6：查询出表EMP中第5条到第10条之间的记录。

案例分析

这是分页的应用。在查询条件中，如果查询条件中ROWNUM大于某一正整数，则不返还任何结果。

 

代码演示：ROWNUM分页

SQL> SELECT * FROM

  2     (SELECT ROWNUM R,ENAME,JOB,SAL  ①

  3      FROM EMP WHERE ROWNUM<=10)  ②

  4  WHERE R>5  ③

  5  /

 R

ENAME

JOB

SAL

6

BLAKE

MANAGER

2850

7

CLARK

MANAGER

2450

8

SCOTT

ANALYST

3000

9

KING

PRESIDENT

5000

10

TURNER

SALESMAN

1500

代码解析：

①  内部查询中得到ROWNUM 并且用别名R记录，供外层条件③使用。

②  内部查询的ROWNUM，与外出的ROWNUM列是平等的两列。

③  使用的R是内层产生的ROWNUM，在外层看来，内层查询的ROWNUM是正常的一列。

 

转自：http://blog.csdn.net/self_realian/article/details/72724307