Oracle数据库学习（四）--高级查询

     

1. SQL(高级查询)

1.1. 子查询

1.1.1. 子查询在WHERE子句中

在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

图-1子查询

例如查找和SCOTT同职位的员工：

    
    

     
     
SELECT e.ename, e.job
     
     
FROM emp e
     
     
WHERE e.job = 
     
     
  (SELECT job FROM emp WHERE ename = 'SCOTT')；
    
    

查找薪水比整个机构平均薪水高的员工：

    
    

     
     
SELECT deptno, ename, sal
     
     
FROM emp e
     
     
WHERE sal> (SELECT AVG(sal) FROM emp);
    
    

如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：

    
    

     
     
SELECT empno, ename, job, sal, deptno
     
     
FROM emp
     
     
WHERE deptno IN 
     
     
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
     
     
AND job <> 'SALESMAN';
    
    

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。例如列出来那些有员工的部门信息：

    
    

     
     
SELECT deptno, dname FROM dept d
     
     
WHERE EXISTS 
     
     
                (SELECT * FROM emp e
     
     
                 WHERE d.deptno = e.deptno);
    
    

1.1.2. 子查询在HAVING部分

子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息：

    
    

     
     
SELECT deptno, MIN(sal) min_sal
     
     
FROM emp
     
     
GROUP BY deptno
     
     
HAVING MIN(sal) >
     
     
(SELECT MIN(sal) FROM emp WHERE deptno = 30);
    
    

1.1.3. 子查询在FROM部分

在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。

查询出薪水比本部门平均薪水高的员工信息：

    
    

     
     
SELECT e.deptno, e.ename, e.sal
     
     
FROM emp e,
     
     
(SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
     
     
WHERE e.deptno = x.deptno
     
     
ande.sal>x.avg_sal
     
     
ORDER BY e.deptno;
    
    

1.1.4. 子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：

    
    

     
     
SELECT e.ename, e.sal, e.deptno,
     
     
           (SELECT d.deptno FROM dept d 
     
     
            WHERE d.deptno = e.deptno)  deptno
     
     
FROM emp e;
    
    

1.2. 分页查询

1.2.1. ROWNUM

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：

    
    

     
     
SELECT ROWNUM, empno, ename, sal
     
     
FROM emp;
    
    

ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：

    
    

     
     
SELECT ROWNUM, empno, ename, sal
     
     
FROM emp
     
     
WHERE rownum> 3;
    
    

如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：

    
    

     
     
SELECT  * FROM 
     
     
  (SELECT  ROWNUMrn , e.* FROM emp e )