浅析SQL Server数据库中的伪列以及伪列的含义

TABLE Test( id int identity(1,1), name varchar(50))GOINSERT INTO Test VALUES (NEWID())GO 100

SQL Server中有一个未公开的伪列“%%physloc%%”，也就是在查询的时候，对于任何一张表，可以加上这个字段，比如如下，就可以查到表中每一行的伪列。

这个伪列的类型是binary(8)，也就是有8个字节，参考上图的DATALENGTH(%%physloc%%) as Len，%%physloc%%返回的记录的物理地址，其中前四个字节表示页号，中间两个字节表示文件号，最后两个字节表示槽号为了更加方便地观察伪列的含义，sqlserver提供了一个未公开的系统函数sys.fn_PhysLocFormatter，下面借助sys.fn_PhysLocFormatter这个函数来继续观察这个伪列如下图，这里就可以清晰地看到伪列中的信息了。

比如第一行中的(1:73:0)，上面说了，其中前四个字节表示页号，中间两个字节表示文件号，最后两个字节表示槽号，　　(1:73:0)这种格式是经过sys.fn_PhysLocFormatter格式化显式之后的结果。　　把文件号1放在最前面，中间的73是页号（page number），最后一位0是槽号（sloc number）。　　下面粗略地说一下这几个字段的含义。这里要求对SQL Server的存储只是有一个基本的认识，否则看的云里雾里。

 

1，首先说什么是文件号

如截图，文件号就是数据库的数据文件编号，这里只有一个数据文件，文件编号为1，　　建表的时候默认（这里也只能建立）建立在fileid = 1 的文件上面，fileid=2的是日志文件，就不多说了。

2，其次是页号，页号就是分配给当前这张表的数据页面（8kb的最小分配单元）的页号，我们看一下Test这个表的页面情况

借助DBCC IND命令，查询分配给这个表的页面信息，其中77号页面是IMA也面，至于什么事IMA页面，不多解释。　　73号页面才是真正存储数据的页，与上面的1:73:0中的73一样，没毛病。

3，最后看一下槽号，槽号的概念要对SQL Server的数据页面有一个基本的认识，这里盗用一张网友的图。

所谓的槽号就是在数据页面中，每个页面存储多行数据，槽号用来标记每一行数据的偏移量，用大白话说就是“存储每一行数据的地址空间开始的位置”，　　因为每一行数据的总长度是不一样的（存在可变长度列的情况下），每一行的占用的存储空间也是不一样的，　　槽号或者行偏移量就是说明每一行数据在页内的开始位置。　　不过sys.fn_PhysLocFormatter格式化显式的槽号并不是如下截图的偏移量，而是第N个数据行的这个N的信息，　　因此第1行的槽号就是1，第2行的槽号就是2，以此类推，当第一个page存储满之后，从第二个page开始存储，槽号又从0开始编号且累加

 　　

至此，对SQL Server的伪列，也就说经常说的RowId有了一个简单的认识。　　这里可以认为，在SQL Server数据库中，伪列RowId就是数据行的物理地址，至于别的数据库中的伪列（RowId）是不是物理地址倒是不确定（很有可能也是的）

这里简单提一下一开始说的一个问题：为什么SQL Server的聚集表（有聚集索引的表）存储数据的时候存储的是“索引键值+聚集索引键值”,对于非聚集索引表，索引存储的是索引键值+RowId？或者反过来说，为什么聚集索引表的非聚集索引存储的是“索引键值+聚集索引键值”而不是“索引存储的是索引键值+RowId”作为一个常识，聚集索引要按照聚集索引的顺序存放，这就意味着聚集索引表的行数据物理位置有可能发生变化，比如在众所周知的“页拆分（page split）”中发生变化，在数据行的物理位置发生了变化的时候，如果非聚集索引存储的是索引键值+RowId，那么这个RowId也势必要发生变化，这个变化当然要耗费一定的性能，为了防止此种情况的发生，聚集表中的非聚集索引存储成相对不变的索引键值+聚集索引键值，因为在数据行的物理位置发生变化的时候，聚集索引键值是相对不变的，这一点也不难理解。当然有一种例外，当对聚集索引表做更新的时候，直接更新聚集索引的键值，这样的话，也有可能造成聚集索引表中当前数据行的物理位置发生变化，这一点也比较有意思，就不展开叙述了。这一点跟绕口令一样，这里要求对SQL Server中的聚集索引和非聚集索引，以及存储结构有一个

 

最后高能预警

高能预警，别说我瞎比比误导人，上述解析伪列的函数sys.fn_PhysLocFormatter是一个未公开的函数，　　未公开的函数就有可能潜在一些问题，事实上这个函数有一个非常严重的bug。　　该bug就是在解析物理存储位置的时候有一定的逻辑错误，这个问题早有细心的人分析过了　　参考：http://blog.itpub.net/81227/viewspace-751898/　　目前测试来看，在SQL Server中仍然存在bug，N前年啃书的时候就了解到有这么一个函数，　　但是一直不想提及sys.fn_PhysLocFormatter这个函数的原因，因此对于未公开的函数，请不要做验证性测试，　　再次声明：该函数有bug，请谨慎使用。

 

总结

本文简单阐述了SQL Server中的伪列，以及伪列的含义，通过伪列对非聚集索引以及数据行的存储结构有一个简单的了解。

 

浅析SQL Server数据库中的伪列以及伪列的含义

标签：height   target   存在   ora   浅析   不能   sys   ace   ide   

小编还为您整理了以下内容，可能对您也有帮助：

数据库中的伪列指的是什么

数据库术语，指的是在物理上这个列并不存在，只是在查询时才构造出来。伪列通常是自由分配的，用户无法执行修改等操作。比如Oracle中的RowID字段，使用RowID可以快速地定位到某个对应的数据，因为它标记了记录所对应的物理地址，是唯一的。

参考资料：http://ke.baidu.com/view/2990269.htm

数据库中的伪列指的是什么

数据库术语，指的是在物理上这个列并不存在，只是在查询时才构造出来。伪列通常是自由分配的，用户无法执行修改等操作。比如Oracle中的RowID字段，使用RowID可以快速地定位到某个对应的数据，因为它标记了记录所对应的物理地址，是唯一的。

参考资料：http://ke.baidu.com/view/2990269.htm

在ORACLE数据库中什么叫伪列

伪列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
所有的伪列要得到值必须要显式的指定。
最常用的两个伪列：rownum和rowid。
1、

ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
rownum永远按照默认的顺序生成。（不受order by的影响）
rownum只能使用 <、 <= ，不能使用 > 、>= 符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
2、
ROWID（记录编号）：是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。
主键:标识唯一的一条业务数据的标识。主键是给业务给用户用的。不是给数据库用的。
记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。

在ORACLE数据库中什么叫伪列

伪列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
所有的伪列要得到值必须要显式的指定。
最常用的两个伪列：rownum和rowid。
1、

ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
rownum永远按照默认的顺序生成。（不受order by的影响）
rownum只能使用 <、 <= ，不能使用 > 、>= 符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
2、
ROWID（记录编号）：是表的伪列，是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。
主键:标识唯一的一条业务数据的标识。主键是给业务给用户用的。不是给数据库用的。
记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。

Sql 语句中" a.rowid <> b.rowid"是什么意思？ a ,b 为表明

1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。

2、rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来。

3、利用rowid是访问表中一行的最快方式。

4、rowid需要10个字节来存储，显示为18位的字符串。

rowid的组成结构为：

data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，如：AAAADeAABAAAAZSAAA

Oracle数据库的子查询关键字该怎么使用?

为何要用子查询

SQL> --查询工资比SCOTT高的员工信息
SQL> --1. 知道SCOTT的工资
SQL> select sal from emp where ename=‘SCOTT‘;


       SAL                                                                      
----------                                                                      
      3000                                                                      


SQL> --2. 查询比3000高的员工
SQL> set linesize 120
SQL> col sal for 9999
SQL> select * from emp where sal>3000;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> --子查询解决问题：不能一步求解时，考虑使用子查询
SQL> select *
  2  from emp
  3  where sal> (select sal
  4              from emp
  5              where ename=‘SCOTT‘);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   

子查询注意的问题

SQL> /*
SQL> 注意的问题：
SQL> 1. 子查询应该在括号中
SQL> 2. 子查询相对主查询采用缩进
SQL> 3. 可以在主查询的where from select having后，都可以放子查询
SQL> 4. 在主查询的group by后面，不能放置子查询
SQL> 5. 强调在from后面放置子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
SQL> 7. 一般来讲，不在子查询中使用 order by;但在Top-N分析中，必须使用order by
SQL> 8. 单行子查询必须使用单行操作符；多行子查询必须使用多行操作符
SQL> 9. 注意：子查询中的空值

SQL> */

子查询 (内查询) 在主查询之前一次执行完成。子查询的结果被主查询使用 (外查询)。

子查询要包含在括号内。将子查询放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询放置的位置

SQL> -- 3. 可以在主查询的where from select having后，都可以放子查询
SQL> select *
  2  from (select ename,sal from emp);


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
SMITH        800                                                                                                        
ALLEN       1600                                                                                                        
WARD        1250                                                                                                        
JONES       2975                                                                                                        
MARTIN      1250                                                                                                        
BLAKE       2850                                                                                                        
CLARK       2450                                                                                                        
SCOTT       3000                                                                                                        
KING        5000                                                                                                        
TURNER      1500                                                                                                        
ADAMS       1100                                                                                                        


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
JAMES        950                                                                                                        
FORD        3000                                                                                                        
MILLER      1300                                                                                                        


已选择14行。


SQL> ed
已写入 file afiedt.buf


  1  select e.*
  2* from (select ename,sal from emp) e
SQL> /


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
SMITH        800                                                                                                        
ALLEN       1600                                                                                                        
WARD        1250                                                                                                        
JONES       2975                                                                                                        
MARTIN      1250                                                                                                        
BLAKE       2850                                                                                                        
CLARK       2450                                                                                                        
SCOTT       3000                                                                                                        
KING        5000                                                                                                        
TURNER      1500                                                                                                        
ADAMS       1100                                                                                                        


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
JAMES        950                                                                                                        
FORD        3000                                                                                                        
MILLER      1300                                                                                                        


已选择14行。



SQL> ---select后放子查询:必须是单行子查询
SQL> select ename,sal,(select job from emp where empno=7839)
  2  from emp;


ENAME        SAL (SELECTJO                                                                                              
---------- ----- ---------                                                                                              
SMITH        800 PRESIDENT                                                                                              
ALLEN       1600 PRESIDENT                                                                                              
WARD        1250 PRESIDENT                                                                                              
JONES       2975 PRESIDENT                                                                                              
MARTIN      1250 PRESIDENT                                                                                              
BLAKE       2850 PRESIDENT                                                                                              
CLARK       2450 PRESIDENT                                                                                              
SCOTT       3000 PRESIDENT                                                                                              
KING        5000 PRESIDENT                                                                                              
TURNER      1500 PRESIDENT                                                                                              
ADAMS       1100 PRESIDENT                                                                                              


ENAME        SAL (SELECTJO                                                                                              
---------- ----- ---------                                                                                              
JAMES        950 PRESIDENT                                                                                              
FORD        3000 PRESIDENT                                                                                              
MILLER      1300 PRESIDENT                                                                                              


已选择14行。

主查询和子查询是否同表

SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
SQL> select ename,sal,(select dname from dept where deptno=10)
  2  from emp;


ENAME        SAL (SELECTDNAMEFR                                                                                         
---------- ----- --------------                                                                                         
SMITH        800 ACCOUNTING                                                                                             
ALLEN       1600 ACCOUNTING                                                                                             
WARD        1250 ACCOUNTING                                                                                             
JONES       2975 ACCOUNTING                                                                                             
MARTIN      1250 ACCOUNTING                                                                                             
BLAKE       2850 ACCOUNTING                                                                                             
CLARK       2450 ACCOUNTING                                                                                             
SCOTT       3000 ACCOUNTING                                                                                             
KING        5000 ACCOUNTING                                                                                             
TURNER      1500 ACCOUNTING                                                                                             
ADAMS       1100 ACCOUNTING                                                                                             


ENAME        SAL (SELECTDNAMEFR                                                                                         
---------- ----- --------------                                                                                         
JAMES        950 ACCOUNTING                                                                                             
FORD        3000 ACCOUNTING                                                                                             
MILLER      1300 ACCOUNTING                                                                                             


已选择14行。

子查询与多表查询对比

SQL> host cls


SQL> --查询部门名称为SALES的员工信息
SQL> --子查询

 1  select *
  2  from emp
  3  where deptno=(select deptno
  4                from dept
  5*               where dname=‘SALES‘)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择6行。


SQL> --多表查询
SQL> select e.*
  2  from dept d,emp e
  3  where d.dname=‘SALES‘ and d.deptno=e.deptno;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择6行。

一个数据库优化问题（子查询与多表查询）

SQL> --如果子查询和多表查询均可，一般采用多表查询         （子查询要查询两次数据库，多表查询查询一次）

单行子查询

子查询结果只返回一行
使用单行比较操作符



查询示例：

SELECTlast_name,job_id,salary

FROM   employees

WHERE job_id= 

                (SELECTjob_id

                 FROM   employees

                 WHERE employee_id= 141)

AND    salary >

                (SELECT salary

                 FROM   employees

                 WHERE employee_id= 143);

多行子查询

in与not in

SQL> --多行子查询
SQL> --in:  在集合中
SQL> --查询部门名称为SALES和ACCOUNTING的员工信息
SQL> --练习：多表查询
SQL> select *
  2  from emp
  3  where deptno in   (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                                   


已选择9行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where deptno not in (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7369 SMITH      CLERK           7902 17-12月-80       800                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   


SQL> host cls

any与all查询

any

SQL> --any: 集合中的任意一个
SQL> --查询工资比20号部门任意员工工资高的员工信息
SQL> select *
  2  from emp
  3  where sal > any (select sal from emp where deptno=20);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择13行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where sal > any (select sal from emp where deptno=10)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   


已选择8行。

all

SQL> --all:和集合的所有值比
SQL>  --查询工资比20号部门所有员工工资高的员工信息
SQL> select *
  2  from emp
  3  ;
from emp
        *
第 2 行出现错误: 
ORA-00911: 无效字符 




SQL> select *
  2  from emp
  3  where sal > all (select sal from emp where deptno=20);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> select *
  2  from emp
  3  where sal > (select min(sal) from emp where deptno=10);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   


已选择8行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where sal > (select max(sal) from emp where deptno=20)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> host cls


SQL> --查询不是经理的员工
SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO           &

Oracle数据库的子查询关键字该怎么使用?

为何要用子查询

SQL> --查询工资比SCOTT高的员工信息
SQL> --1. 知道SCOTT的工资
SQL> select sal from emp where ename=‘SCOTT‘;


       SAL                                                                      
----------                                                                      
      3000                                                                      


SQL> --2. 查询比3000高的员工
SQL> set linesize 120
SQL> col sal for 9999
SQL> select * from emp where sal>3000;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> --子查询解决问题：不能一步求解时，考虑使用子查询
SQL> select *
  2  from emp
  3  where sal> (select sal
  4              from emp
  5              where ename=‘SCOTT‘);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   

子查询注意的问题

SQL> /*
SQL> 注意的问题：
SQL> 1. 子查询应该在括号中
SQL> 2. 子查询相对主查询采用缩进
SQL> 3. 可以在主查询的where from select having后，都可以放子查询
SQL> 4. 在主查询的group by后面，不能放置子查询
SQL> 5. 强调在from后面放置子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
SQL> 7. 一般来讲，不在子查询中使用 order by;但在Top-N分析中，必须使用order by
SQL> 8. 单行子查询必须使用单行操作符；多行子查询必须使用多行操作符
SQL> 9. 注意：子查询中的空值

SQL> */

子查询 (内查询) 在主查询之前一次执行完成。子查询的结果被主查询使用 (外查询)。

子查询要包含在括号内。将子查询放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询放置的位置

SQL> -- 3. 可以在主查询的where from select having后，都可以放子查询
SQL> select *
  2  from (select ename,sal from emp);


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
SMITH        800                                                                                                        
ALLEN       1600                                                                                                        
WARD        1250                                                                                                        
JONES       2975                                                                                                        
MARTIN      1250                                                                                                        
BLAKE       2850                                                                                                        
CLARK       2450                                                                                                        
SCOTT       3000                                                                                                        
KING        5000                                                                                                        
TURNER      1500                                                                                                        
ADAMS       1100                                                                                                        


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
JAMES        950                                                                                                        
FORD        3000                                                                                                        
MILLER      1300                                                                                                        


已选择14行。


SQL> ed
已写入 file afiedt.buf


  1  select e.*
  2* from (select ename,sal from emp) e
SQL> /


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
SMITH        800                                                                                                        
ALLEN       1600                                                                                                        
WARD        1250                                                                                                        
JONES       2975                                                                                                        
MARTIN      1250                                                                                                        
BLAKE       2850                                                                                                        
CLARK       2450                                                                                                        
SCOTT       3000                                                                                                        
KING        5000                                                                                                        
TURNER      1500                                                                                                        
ADAMS       1100                                                                                                        


ENAME        SAL                                                                                                        
---------- -----                                                                                                        
JAMES        950                                                                                                        
FORD        3000                                                                                                        
MILLER      1300                                                                                                        


已选择14行。



SQL> ---select后放子查询:必须是单行子查询
SQL> select ename,sal,(select job from emp where empno=7839)
  2  from emp;


ENAME        SAL (SELECTJO                                                                                              
---------- ----- ---------                                                                                              
SMITH        800 PRESIDENT                                                                                              
ALLEN       1600 PRESIDENT                                                                                              
WARD        1250 PRESIDENT                                                                                              
JONES       2975 PRESIDENT                                                                                              
MARTIN      1250 PRESIDENT                                                                                              
BLAKE       2850 PRESIDENT                                                                                              
CLARK       2450 PRESIDENT                                                                                              
SCOTT       3000 PRESIDENT                                                                                              
KING        5000 PRESIDENT                                                                                              
TURNER      1500 PRESIDENT                                                                                              
ADAMS       1100 PRESIDENT                                                                                              


ENAME        SAL (SELECTJO                                                                                              
---------- ----- ---------                                                                                              
JAMES        950 PRESIDENT                                                                                              
FORD        3000 PRESIDENT                                                                                              
MILLER      1300 PRESIDENT                                                                                              


已选择14行。

主查询和子查询是否同表

SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
SQL> select ename,sal,(select dname from dept where deptno=10)
  2  from emp;


ENAME        SAL (SELECTDNAMEFR                                                                                         
---------- ----- --------------                                                                                         
SMITH        800 ACCOUNTING                                                                                             
ALLEN       1600 ACCOUNTING                                                                                             
WARD        1250 ACCOUNTING                                                                                             
JONES       2975 ACCOUNTING                                                                                             
MARTIN      1250 ACCOUNTING                                                                                             
BLAKE       2850 ACCOUNTING                                                                                             
CLARK       2450 ACCOUNTING                                                                                             
SCOTT       3000 ACCOUNTING                                                                                             
KING        5000 ACCOUNTING                                                                                             
TURNER      1500 ACCOUNTING                                                                                             
ADAMS       1100 ACCOUNTING                                                                                             


ENAME        SAL (SELECTDNAMEFR                                                                                         
---------- ----- --------------                                                                                         
JAMES        950 ACCOUNTING                                                                                             
FORD        3000 ACCOUNTING                                                                                             
MILLER      1300 ACCOUNTING                                                                                             


已选择14行。

子查询与多表查询对比

SQL> host cls


SQL> --查询部门名称为SALES的员工信息
SQL> --子查询

 1  select *
  2  from emp
  3  where deptno=(select deptno
  4                from dept
  5*               where dname=‘SALES‘)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择6行。


SQL> --多表查询
SQL> select e.*
  2  from dept d,emp e
  3  where d.dname=‘SALES‘ and d.deptno=e.deptno;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择6行。

一个数据库优化问题（子查询与多表查询）

SQL> --如果子查询和多表查询均可，一般采用多表查询         （子查询要查询两次数据库，多表查询查询一次）

单行子查询

子查询结果只返回一行
使用单行比较操作符



查询示例：

SELECTlast_name,job_id,salary

FROM   employees

WHERE job_id= 

                (SELECTjob_id

                 FROM   employees

                 WHERE employee_id= 141)

AND    salary >

                (SELECT salary

                 FROM   employees

                 WHERE employee_id= 143);

多行子查询

in与not in

SQL> --多行子查询
SQL> --in:  在集合中
SQL> --查询部门名称为SALES和ACCOUNTING的员工信息
SQL> --练习：多表查询
SQL> select *
  2  from emp
  3  where deptno in   (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                                   


已选择9行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where deptno not in (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7369 SMITH      CLERK           7902 17-12月-80       800                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   


SQL> host cls

any与all查询

any

SQL> --any: 集合中的任意一个
SQL> --查询工资比20号部门任意员工工资高的员工信息
SQL> select *
  2  from emp
  3  where sal > any (select sal from emp where deptno=20);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10                                   
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30                                   
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30                                   


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20                                   
      7900 JAMES      CLERK           7698 03-12月-81       950                    30                                   


已选择13行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where sal > any (select sal from emp where deptno=10)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   


已选择8行。

all

SQL> --all:和集合的所有值比
SQL>  --查询工资比20号部门所有员工工资高的员工信息
SQL> select *
  2  from emp
  3  ;
from emp
        *
第 2 行出现错误: 
ORA-00911: 无效字符 




SQL> select *
  2  from emp
  3  where sal > all (select sal from emp where deptno=20);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> select *
  2  from emp
  3  where sal > (select min(sal) from emp where deptno=10);


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30                                   
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20                                   
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30                                   
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10                                   
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30                                   
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20                                   


已选择8行。


SQL> ed
已写入 file afiedt.buf


  1  select *
  2  from emp
  3* where sal > (select max(sal) from emp where deptno=20)
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- -------------- ----- ---------- ----------                                   
      7839 KING       PRESIDENT            17-11月-81      5000                    10                                   


SQL> host cls


SQL> --查询不是经理的员工
SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO           &

SQL语句中select dummy from dual;有什么意义?

FORM 后面就是 表名 说的明白一下 select 列名 from 表名 这句话的意思是 从这个表里选择制定的列！

SQL语句中select dummy from dual;有什么意义?

FORM 后面就是 表名 说的明白一下 select 列名 from 表名 这句话的意思是 从这个表里选择制定的列！

oracle emp,dept表中查询部门人数大于所有部门平均人数的部门编号、部门名称、部门人数

select s.deptno,s.dname,count(t.empno) from emp t,dept s where t.deptno=s.deptno group by s.deptno,s.dname having count(t.empno) > (select count(empno)/count(distinct deptno) from emp )

SQL Server里有没有类似Oracle里的rownum的伪列

rownum和Dual表，应该是Oracle所特有的东西。SQL Server和Sybase都没有这个，其它的数据库不好说。rownum关键字，是Oracle为查询返回的行，顺序分配的编号，当然也可以作为Where条件来使用。Dual表：也是Oracle方便查询而使用的特殊表。Oracle内部机制可以确保该表始终只有一行一列一个X值。下面是从网上找到的使用临时表的方法，因为没有Sql server环境，未测试，仅供参考：select rownum=identity(int,1,1),id,name into #t from table1select * from #tdrop table #t如果只想查询记录，可以参考下面的子查询代码：select * from 表 where id = ALL (select id from 表);select * from 表 where id <= ALL (select id from 表);