第七章 子查询

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/two2123427/article/details/47262637

子查询:

 

跟一个不确定的条件去比,使用子查询

 

使用子查询 

 

    SELECT      select_list 

    FROM        table 

    WHERE       expr operator 

                                (SELECT           select_list 

                                FROM              table); 

 

   • 括号内的查询叫做子查询,也叫内部查询,先 

      于主查询执行。 

   • 子查询的结果被主查询(外部查询)使用 

   • expr operator包括比较运算符。 

        –单行运算符:>=>=<<><= 

        –多行运算符: INANYALL 

 

 

使用子查询 

• 子查询可以嵌于以下SQL子句中: 

        –WHERE子句 

        –HAVING子句 

        –FROM子句 

使用子查询 

 

   • 子查询使用指导 

          –子查询要用括号括起来 

          –将子查询放在比较运算符的右边 

          –对于单行子查询要使用单行运算符 

          –对于多行子查询要使用多行运算符 

单行子查询:一行一列

多行子查询:多行(0,1,。。)一列

多行子查询 

     –子查询返回记录的条数 可以是一条或多条。 

     –和多行子查询进行比较时,需要使用多行操作符,多 

       行操作符包括: 

         •IN 

         •ANY 

         •ALL 

 

     –IN操作符和以前介绍的功能一致,判断是否与子查询 

        的任意一个返回值相同。 

IN: IN使用 

 

  SELECT empno,ename 

  FROM      emp 

  WHERE     sal (应该换成IN)

                         (SELECT       MIN(sal) 

                          FROM         emp 

                          GROUP BY deptno); 

 

     –返回结果 

         ERROR at line 4: 

         ORA-01427: single-row subquery returns more than 

           one row 

 

ANY

ANY:表示和子查询的任意一行结果进行比较,有一个满足 

       条件即可。 

         •< ANY:表示小于子查询结果集中的任意一个,即小于最 

           大值就可以。 

 

         •> ANY:表示大于子查询结果集中的任意一个,即大于最 

           小值就可以。 

 

         •= ANY:表示等于子查询结果中的任意一个,即等于谁都 

           可以,相当于IN。 

 

ALL

ALL的使用 

     –ALL:表示和子查询的所有行结果进行比较,每一行必须都 

       满足条件。 

         •< ALL:表示小于子查询结果集中的所有行,即小于最小 

           值。 

 

         •> ALL:表示大于子查询结果集中的所有行,即大于最大 

           值。 

 

         •= ALL :表示等于子查询结果集中的所有行,即等于所有 

           值,通常无意义。 

练习

 

• 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工 

-------1
SELECT ename,hiredate
FROM emp
WHERE hiredate >
ANY (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10

• 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工 

SELECT ename,hiredate
FROM emp
WHERE hiredate >
ALL (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10

• 3.查询职位和10部门任意一个员工职位相同的员 工姓名,职位,不包括10部门员工 

SELECT ename,job
FROM emp
WHERE job=
ANY (SELECT job FROM emp WHERE deptno=10)
AND deptno<>10

--------------------------
----------------------------------
------------------------------------
--------查询每个部门的最低工资的员工的信息
SELECT * FROM emp
WHERE (sal,deptno) IN
(SELECT MIN(sal),deptno FROM emp GROUP BY deptno)-----(要一一对应)

 

----------
查询比自己部门平均工资高的员工姓名,工资,部门编号,部门平均工资
思路:假设有一个表,保存部门的平均工资和部门编号(嵌套子查询)

SELECT ename,sal, emp.deptno,avgsal
FROM emp,(
          SELECT deptno,AVG(sal) avgsal 
          FROM emp 
          GROUP BY deptno) xb
WHERE  emp.deptno=xb.deptno
AND sal>avgsal

 

 

多列子查询:(多行,多列)

练习

 

• 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位,不包括10部门员工 

SELECT job,mgr FROM emp
WHERE job IN(
SELECT job FROM emp WHERE deptno=10)
AND mgr IN(
SELECT mgr FROM emp WHERE deptno=10)
AND deptno<>10

 

或者

 

SELECT mgr,job FROM emp
WHERE (mgr,job) =ANY (
SELECT mgr,job FROM emp WHERE deptno=10)
AND deptno<>10

 

• 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,不包括10部门员工 


SELECT job,mgr FROM emp
WHERE (job IN(
SELECT job FROM emp WHERE deptno=10)
OR  mgr IN(
SELECT mgr FROM emp WHERE deptno=10))
AND deptno<>10

 

子查询中的空值 :

查询不是经理的员工姓名。 

 

     SQL> SELECT      ename 

        2  FROM       emp 

        3  WHERE      empno NOT IN 

        4                              (SELECT mgr 

        5                               FROM    emp); 

     no rows selected. 

 

子查询返回的结果中含有空值 

    上面的SQL语句试图查找出没有下属的雇员,逻辑上,这个SQL语句应该会 

    返回8条记录,但是却一条也没返回,why? 

 

    因为子查询的结果中有一条空值,这条空值导致主查询没有记录返回。这 

    是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值 

    有可能成为子查询结果集合中的一部分,就不能使用NOT IN 运算符。 

改正:


SELECT ename FROM emp WHERE empno NOT IN(
SELECT   DISTINCT mgr FROM emp WHERE mgr IS NOT NULL

 

在 FROM 子句中使用子查询 

 

      • 查询比自己部门平均工资高的员工姓名,工资, 

         部门编号,部门平均工资 

 

      SQL> SELECT  a.ename, a.sal, a.deptno, b.salavg 

        2  FROM       emp a, (SELECT        deptno, avg(sal) salavg 

        3                       FROM        emp 

        4                       GROUP BY deptno) b 

        5  WHERE      a.deptno = b.deptno 

        6  AND        a.sal > b.salavg; 

 

      ENAME                SAL      DEPTNO        SALAVG 

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

      KING                 5000           10          2600 

      JONES                2975           20          2335 

      SCOTT                3000           20          2335 

      ... 

      6 rows selected. 

练习

 

• 1.查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资 

SELECT ename,e.job,dname ,avgsal
FROM emp e,dept d,(
                     SELECT  job,AVG(sal) avgsal
                     FROM emp
                     GROUP BY  job
                     )xb
WHERE e.deptno =d.deptno 
AND e.job= xb.job
AND e.sal>xb.avgsal 

• 2.查询职位和经理同员工SCOTTBLAKE完全相同的员工姓名、职位,不包括SCOOTBLAKE本人。

SELECT e.ename,e.job

FROM emp e,

     (SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') ) xinbiao

WHERE e.job =xinbiao.job

    AND e.mgr =xinbiao.mgr

    AND e.ename NOT IN('SCOTT','BLAKE')

 

 

 或者

 SELECT e.ename,e.job

FROM emp e

     

WHERE (e.job,e.mgr) IN  (SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') ) 

    AND e.ename NOT IN('SCOTT','BLAKE')    

• 3.查询不是经理的员工姓名。

SELECT ename 

FROM emp

WHERE empno NOT IN (SELECT  DISTINCT mgr FROM emp WHERE mgr IS NOT NULL) 

 

 

 

ROWNUM 

 

• ROWNUM 

     –ROWNUM是一个伪列,伪列是使用上类似于表中的列,而 

        实际并没有存储在表中的特殊列; 

     –ROWNUM的功能是在每次查询时,返回结果集的顺序号, 

        这个顺序号是在记录输出时才一步一步产生的,第一行 

        显示为1,第二行为2,以此类推。 

 • ROWNUM 

      –ROWNUM使用的注意点: 

          • 1.如下SQL语句,SELECT * FROM EMP WHERE ROWNUM>2;查询 

            不到任何记录,因为ROWNUM是在记录输出时才生成,且总是 

            从1开始,所以输出的第一条记录不满足>2的条件,被过滤掉, 

            第二条的ROWNUM又成了1,又不满足〉2的条件,又被过滤掉, 

            依此类推,所以永远没有满足条件的记录,返回为空。所以 

            对于ROWNUM只能执行<<=运算,不能执行>>=或一个区间 

            运算Between..And等 

          •2.ROWNUMORDER BY一起使用时,因为ROWNUM在记录输出时 

            生成,而ORDER BY子句在最后执行,所以当两者一起使用时, 

            需要注意ROWNUM实际是已经被排了序的ROWNUM。 

TOP-N查询 

 

• TOP-N查询 

     –Top-N查询主要是实现表中按照某个列排序,输出最大或 

       最小的N条记录功能。 

         •Top-N分析语法: 

 

   SELECT [列名], ROWNUM 

   FROM      (SELECT [列名

               FROM 表名 

               ORDER  BY Top-N操作的列 ASC|DESC) 

   WHERE  ROWNUM <=  N; 

 

         •ASC:查询最小的N条记录 

         •DESC:查询最大的N条记录 

练习

• 1.查询入职日期最早的前5名员工姓名,入职日  期。 

 

SELECT ROWNUM,hiredate
FROM (SELECT ename,hiredate FROM emp
             ORDER BY hiredate ASC)
WHERE ROWNUM <=5
• 2.查询工作在CHICAGO并且入职日期最早的前2名 员工姓名,入职日期。 

SELECT ROWNUM ,hiredate
FROM (SELECT ename,hiredate
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND loc='CHICAGO'
ORDER BY hiredate ASC)
WHERE ROWNUM <=2

分页 

 • 分页查询 

      –在Oracle中,利用ROWNUM的特性,可以实现数据库端的分 

        页查询,查询语法为: 

      –1.当未指定需要按照某列排序,语法为: 

 

SELECT b.* 

FROM (SELECT ROWNUM rn,[             1,      2,....      n] 

                                 列名 列名               列名 

        FROM        1,[     2,...       n] 

               表名  表名              表名 

        WHERE [               AND ] ROWNUM <=               *            ) b 

                  条件表达式                            目标页数每页记录数 

WHERE rn > (目标页数-1)*每页记录数 

或 

SELECT b.* 

FROM (SELECT ROWNUM rn,[             1,      2,....      n] 

                                 列名 列名               列名 

        FROM        1,[     2,...       n] 

               表名  表名              表名 

         [WHERE 条件表达式]) b 

WHERE rn <=             *             and rn > (             -1)* 

               目标页数每页记录数                            目标页数          每页记录数 

思考:哪种方式效率高? (第一种)

练习

 

• 1.按照每页显示5条记录,分别查询第1页,第页,第3页信息,要求显示员工姓名、入职日期 、部门名称。 

SELECT b.*
FROM (SELECT ROWNUM rn,ename,hiredate,dname
                    FROM emp e,dept d
                    WHERE e.deptno=d.deptno
                    AND
                    ROWNUM <=1*5)b
  WHERE rn>(1-1)*5

分页 

 • 分页查询 

      –2.当指定需要按照某列排序时,语法为: 

SELECT * 

FROM (SELECT ROWNUM  rn, b.* 

        FROM  (SELECT 列名1 [,列名2,....列名n] 

                  FROM       1,[     2,...      n] 

                        表名  表名              表名 

                  [WHERE 子句

                 ORDER BY 要排序的列 ASC|DESC ) b 

        WHERE ROWNUM <=              * 

                            目标页数每页记录数 

       ) 

WHERE rn > (目标页数-1)*每页记录数 

或: 

SELECT * 

FROM (SELECT ROWNUM  rn, b.* 

        FROM  (SELECT 列名1 [,列名2,....列名n] 

                  FROM       1,[     2,...      n] 

                        表名  表名              表名 

                  [WHERE 子句

                 ORDER BY 要排序的列 ASC|DESC ) b 

        ) 

WHERE rn <=             *             and rn > (            -1)*             ; 

               目标页数每页记录数                           目标页数          每页记录数 

练习

• 1.按照每页显示5条记录,分别查询工资最高的第1页,第2页,第3页信息,要求显示员工姓名 、入职日期、部门名称、工资。 

第一页:

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=1*5)
 WHERE rn >(1-1)*5

第二页:

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=2*5)
 WHERE rn >(2-1)*5

第三页:

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=3*5)
 WHERE rn >(3-1)*5

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页