开发者社区> leshami> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL 基础--> 子查询

简介: --========================= --SQL 基础--> 子查询 --=========================     /* 一、子查询     子查询就是位于SELECT、UPDATE、或DELETE语...
+关注继续查看

--=========================

--SQL 基础--> 子查询

--=========================  

 

/*

一、子查询

    子查询就是位于SELECTUPDATE、或DELETE语句中内部的查询

   

二、子查询的分类

    单行子查询

        返回零行或一行

    多行子查询

        返回一行或多行

    多列子查询

        返回多列

    相关子查询

        引用外部SQL语句中的一列或多列

    嵌套子查询

        位于其它子查询中的查询

 

三、子查询语法 */

    SELECT select_list

    FROM table

    WHERE expr operator

       (SELECT select_list

        FROM table);

        /*

    子查询(内部查询)在执行主查询之前执行一次

    然后主查询(外部查询)会使用该子查询的结果 

 

四、子查询的规则

    将子查询括在括号中

    将子查询放置在比较条件的右侧

    只有在执行排序Top-N分析时,子查询中才需要使用ORDER BY 子句

    单行运算符用于单行子查询,多行运算符用于多行子查询 

 

五、单行子查询

    仅返回一行

    使用单行的表较运算符:= ,>, >= ,< , <= ,<>

   

    WHERE 子句中使用子查询    */

    SQL> select ename,job from emp

      2  where empno = (

      3    select empno from emp

      4    where mgr = 7902 );

 

    ENAME      JOB

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

    SMITH      CLERK

   

    --使用分组函数的子查询

    SQL> select ename,job,sal

      2  from emp

      3  where sal >

      4    (select avg(sal) from emp);

 

    ENAME      JOB              SAL

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

    JONES      MANAGER         2975

    BLAKE      MANAGER         2850

    CLARK      MANAGER         2450

    SCOTT      ANALYST         3000

    KING       PRESIDENT       5000

    FORD       ANALYST         3000

   

    --HAVING子句中使用子查询

    SQL> select deptno,min(sal)

      2  from emp

      3  group by deptno

      4  having min(sal) >

      5      (select min(sal)

      6       from emp

      7       where deptno = 20);

 

       DEPTNO   MIN(SAL)

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

           30        950

           10       1300

          

    --FROM 子句中使用子查询

    SQL> select empno,ename

      2  from

      3      (select empno,ename

      4       from emp

      5       where deptno = 20);

 

        EMPNO ENAME

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

         7369 SMITH

         7566 JONES

         7788 SCOTT

         7876 ADAMS

         7902 FORD

   

    --单行子查询中的常见错误

        --子查询的结果返回多于一行

       SQL> select empno,ename

         2  from emp

         3  where sal =

         4      (select sal     

         5       from emp

         6       where deptno = 20);

           (select sal

            *

       ERROR at line 4:

       ORA-01427: single-row subquery returns more than one row

      

       --子查询中不能包含ORDER BY子句

       SQL> select empno,ename

         2  from emp

         3  where sal >

         4      (select avg(sal)

         5       from emp

         6       order by empno);

            order by empno)

            *

       ERROR at line 6:

       ORA-00907: missing right parenthesis

      

       --子查询内部没有返回行,如下语句可以正确执行,但没有数据返回

       SQL> select ename,job

         2  from emp

         3  where empno =

         4      (select empno

         5       from emp

         6       where mgr = 8000);

 

       no rows selected

 

    /* 

六、多行子查询

    返回多个行

    使用多行比较运算符IN ,ANY ,ALL

   

    在多行子查询中使用IN 操作符  */

    SQL> select empno,ename,job

      2  from emp

      3  where sal in

      4      (select max(sal)

      5       from emp

      6       group by deptno);

 

        EMPNO ENAME      JOB

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

         7698 BLAKE      MANAGER

         7902 FORD       ANALYST

         7788 SCOTT      ANALYST

         7839 KING       PRESIDENT

        

    --在多行子查询中使用ANY 操作符

    SQL> select empno,ename,job

      2  from emp

      3  where sal < any

      4      (select avg(sal)

      5       from emp

      6       group by deptno);

 

        EMPNO ENAME      JOB

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

          7369 SMITH      CLERK

         7900 JAMES      CLERK

         7876 ADAMS      CLERK

         7521 WARD       SALESMAN

         7654 MARTIN     SALESMAN

         7934 MILLER     CLERK

         7844 TURNER     SALESMAN

         7499 ALLEN      SALESMAN

         7782 CLARK      MANAGER

         7698 BLAKE      MANAGER

        

    --在多行子查询中使用ALL 操作符

    SQL> select empno,ename,job

      2  from emp

      3  where sal > all

      4      (select avg(sal)

      5       from emp

      6*      group by deptno)

   

        EMPNO ENAME      JOB

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

         7566 JONES      MANAGER

         7788 SCOTT      ANALYST

         7839 KING       PRESIDENT

         7902 FORD       ANALYST

   

     /*

七、相关子查询

    子查询中使用了主查询中的某些字段,主查询每扫描一行都要执行一次子查询 */

 

    --查询工资高于同一部门的员工的部门号,姓名,工资

    SQL> select deptno,ename,sal

      2  from emp outer

      3  where sal >

      4      (select avg(sal)

      5       from emp inner

      6       where inner.deptno = outer.deptno);

 

       DEPTNO ENAME             SAL

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

           30 ALLEN            1600

           20 JONES            2975

           30 BLAKE            2850

           20 SCOTT            3000

           10 KING             5000

           20 FORD             3000

          

    --查询负责管理其它员工的员工记录(使用exists)

    SQL> select empno,ename

      2  from emp outer

      3  where exists

      4     (select empno

      5      from emp inner

      6      where inner.mgr = outer.empno);

 

        EMPNO ENAME

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

         7566 JONES

         7698 BLAKE

         7782 CLARK

         7788 SCOTT

         7839 KING

         7902 FORD

    --查询不管理其它员工的职员(not exists)

    SQL> l3

      3* where exists

    SQL> c /where/where not

      3* where not exists

    SQL> l

      1  select empno,ename

      2  from emp outer

      3  where not exists

      4     (select empno

      5      from emp inner

      6*     where inner.mgr = outer.empno)

    SQL> /

 

        EMPNO ENAME

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

         7369 SMITH

         7499 ALLEN

         7521 WARD

         7654 MARTIN

         7844 TURNER

         7876 ADAMS

         7900 JAMES

         7934 MILLER

        

    EXISTS NOT EXISTS IN NOT IN 的比较

        EXISTSIN的不同:

            EXISTS只检查行的存在性,IN 要检查实际值的存在性(一般情况下EXISTS的性能高于IN)

        NOT EXISTS NOT IN

           当值列表中包含空值的情况下,NOT EXISTS 则返回true,NOT IN 则返回false.

          

    --看下面的查询,查询部门号不在emp表中出现的部门名称及位置

    SQL> select deptno,dname,loc

      2  from dept d

      3  where not exists

      4      (select 1

      5       from emp e

      6*      where e.deptno = d.deptno)

     

       DEPTNO DNAME          LOC

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

           40 OPERATIONS     BOSTON

      

    --IN与空值

           SQL> SELECT *

             2    FROM emp e

             3    WHERE e.empno NOT IN (

             4                           SELECT 7369 FROM dual

             5                           UNION ALL

             6                           SELECT NULL FROM dual

             7                          )

             8  ;

           

           EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

           

           SQL> SELECT *

             2    FROM emp e

             3    WHERE e.empno IN ('7369',NULL)

             4  ;

           

           EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

            7369 SMITH      CLERK      7902 1980-12-17     800.00               20

           

    

    /* 

    注:子查询要包含在括号内

        子查询一般放在比较条件的右侧

        除非进行TOPN 分析,否则不要在子查询中使用ORDER BY */

 

    /*

八、多列子查询

    1、成对比较

    查询工资为部门最高的记录 */

    SQL> select * from scott.emp

      2  where (sal,job) in

      3     (select max(sal),job from scott.emp group by job);

   

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

         7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

         7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

         7839 KING       PRESIDENT            17-NOV-81       5000                    10

         7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

         7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20   

      

       /*      

    2、非成对比较,实现了与上述类似的功能*/

    SQL> select * from scott.emp

      2  where sal in (select max(sal) from scott.emp group by job)

      3  and job in (select distinct job from scott.emp);

 

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

         7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

         7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

         7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

         7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

         7839 KING       PRESIDENT            17-NOV-81       5000                    10

        

     /*

九、嵌套子查询

    即位于子查询内部的子查询,嵌套层数最多可达层。然而应尽量避免使用嵌套子查询,使用表连接的查询性能会更高*/

    SQL> select deptno,Num_emp

      2  from (select deptno,count(empno) as Num_emp from emp group by deptno) d

      3  where Num_emp > 3;

 

       DEPTNO    NUM_EMP

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

           30          6

           20          5

      

    /*     

    注意:子查询对空值的处理

    除了count(*)外,都会忽略掉空值 */

 

    /*

十、更多*/  

Oracle 数据库实例启动关闭过程

 

Oracle 10g SGA 的自动化管理

 

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

 

Oracle实例和Oracle数据库(Oracle体系结构)

 

SQL 基础-->常用函数

 

SQL基础-->过滤和排序

 

SQL 基础-->SELECT 查询

 

 

 

   

 

 

   

 

  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL优化--关联子查询的前世今生
SQL优化--关联子查询的前世今生
0 0
SQL Sever 子查询与嵌套查询
作者主页:https://www.couragesteak.com/
0 0
SQL 连接查询、子查询、union
SQL 连接查询、子查询、union
0 0
【SQL刷题】DAY14----SQL使用子查询专项练习
【SQL刷题】DAY14----SQL使用子查询专项练习
0 0
SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
0 0
SQL查询模型和子查询再学习
SQL查询模型和子查询再学习
0 0
【如何成为SQL高手】第八关:子查询及联合查询
【如何成为SQL高手】第八关:子查询及联合查询
0 0
Mysql常用sql语句(20)- 子查询重点知识
Mysql常用sql语句(20)- 子查询重点知识
0 0
Mysql常用sql语句(19)- in / exists 子查询
Mysql常用sql语句(19)- in / exists 子查询
0 0
【教奶奶学SQL】(task3)复杂查询(视图 | 子查询 | 谓词 | Case)
单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的,但其实我们操作的是一个视图。所以从SQL的角度来说操作视图与操作表看起来是完全相同的,那么为什么还会有视图的存在呢?视图到底是什么?视图与表有什么不同呢?
0 0
+关注
leshami
传播知识,分享快乐!十年以上数据库,系统运维与管理,性能优化经验。全部文章,欢迎扩散,转载请注明出处!
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载