加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle入门之子查询

发布时间：2020-12-12 15:28:56 所属栏目：百科 来源：网络整理
导读：为什么有子查询 对于一个问题,1步不能求解,需要多步 1 先求scott的工资 select sal from emp where ename=’SCOTT’; ===3000 2 求比3000大的工资 select * from emp where sal 3000; 通过子查询求解,子查询的本质是select语句的嵌套 select * from empwhere

为什么有子查询

对于一个问题,1步不能求解,需要多步

  • 1 先求scott的工资
    select sal from emp where ename=’SCOTT’; ===>3000

  • 2 求比3000大的工资
    select * from emp
    where sal > 3000;

通过子查询求解,子查询的本质是select语句的嵌套

select * from emp
where sal > (select sal from emp where ename='SCOTT'  )

基本语法

注意事项

  1. 合理的书写风格
  2. 子查询的() 不要丢掉

  3. 子查询和主查询可以不是同一张表,只要子查询返回的结果,主查询能用就行

    查询部门名称是SALES 的员工信息

    部门表-》部门编号-》利用编号在员工表中获取对应部门的员工信息：

    SQL> select * 
  2  from emp
  3  where deptno = 
  4  (select deptno
  5  from dept
  6  where dname = 'SALES')
  7  ;

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

6 rows selected.

    多表查询的方法：

    select e.* from emp e,dept d where e.deptno = d.deptno and d.dname='SALES'

    关于SQL语句的优化第三点：子查询检索数据库2次,多表查询只检索一次，优先用多表查询！利用了空间换时间，因为多表查询的列数增多，时间减少！

  4. 可以在主查询的什么地方放一个子查询

select ...可以放置子查询 (必须要放单行子查询)
    from .... 可以放置子查询
    where ... 可以放置子查询
    group by .... 不
    having .... 可以放置子查询 ppt例子
    order by ... 不
  1. 子查询中一般不使用order by,但是Top-N问题,子查询必须要用order by
    eg: 求工资的前三名 分页.... M<=x<=N

子查询的分类

  • 按照子查询返回的条目数,分为: 单行子查询和多行子查询

    • 单行子查询只能用单行比较操作符 (= < >)
    • 多行子查询只能用多行比较操作符 (in any all)

  • 按照子查询和主查询的执行顺序来分

    • 一般子子查询 子查询把结果返回给主查询….一般
    • 相关子查询 主查询把select列中的参数传递给 子查询

单行子查询

查询员工信息,属于141号,薪水比143号员工 工资高的 col1,col2,co3信息

在子查询中使用组函数

查询 工资最低的员工信息

SQL> select ename,empno,sal
  2  from emp
  3  where sal = 
  4  (select min(sal)
  5  from emp)
  6  ;

ENAME       EMPNO        SAL
---------- ---------- ----------
SMITH        7369        800

HAVING 子句使用子查询

求各个部门编号 和部门的最低工资 (这个最低工资要比20号部门的最低工资要高)

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)
  8  ;

    DEPTNO   MIN(SAL)
---------- ----------
    30    950
    10   1300

SQL>

select 放置子查询 (必须要放单行子查询)

  • 错误示例===select子句中使用了多行子查询
1  select empno,ename,sal,(select ename from emp where deptno = 10) "十号部门员工" 2* from emp SQL> / select empno,(select ename from emp where deptno = 10) "十号部门员工" * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
  • 正确实例
1  select empno,(select ename from emp where deptno=10 and ename='CLARK') "十号部" 2* from emp SQL> / EMPNO ENAME SAL 十号部 ---------- ---------- ---------- ---------- 1 tom_abc 8000 CLARK 7369 SMITH 800 CLARK 7499 ALLEN 1600 CLARK 7521 WARD 1250 CLARK 7566 JONES 2975 CLARK 7654 MARTIN 1250 CLARK 7698 BLAKE 2850 CLARK 7782 CLARK 2450 CLARK 7788 SCOTT 3000 CLARK 7839 KING 5000 CLARK 7844 TURNER 1500 CLARK 7876 ADAMS 1100 CLARK 7900 JAMES 950 CLARK 7902 FORD 3000 CLARK 7934 MILLER 1300 CLARK 15 rows selected.

from后面放置子查询–多行子查询

select * from (select a,b,c,d from emp where d='aaa'); 这种情况在oracle用的比较多 !

求员工编号和员工姓名,只能显示这2列，开头必须是select *。

SQL> select * 
  2  from 
  3  (select empno,ename
  4  from emp);

     EMPNO ENAME
---------- ----------
     1 tom_abc
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

15 rows selected.

where 放置子查询

查询部门名称是SALES 的员工信息 。

部门表-》部门编号-》利用编号在员工表中获取对应部门的员工信息：

SQL> select * 
  2  from emp
  3  where deptno = 
  4  (select deptno
  5  from dept
  6  where dname = 'SALES')
  7  ;

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

6 rows selected.

多行子查询

但是多行子查询中是可以使用>,<以及=操作符的，如果查询结果是多个，就要使用in，all或者any对结果进行处理，再和前面的符号进行比较操作。

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

  • 查询部门名称为 SALES 和 ACCOUNTING 的员工信息
select * from emp
  2  where deptno in
  3  (select deptno
  4  from dept
  5* where dname='SALES' or dname='ACCOUNTING')
  6  /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     1 tom_abc                     8000            10
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30

10 rows selected.

多表查询的方式：

SQL> select e.*
  2  from emp e,dept d
  3  where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     1 tom_abc                     8000            10
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30

10 rows selected.

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

  • any 和其中的任意一个元素做比较

  • 查询薪水 比30号部门 任意一个员工薪高的员工信息=====大于这个集合的最小值 就可以.

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

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     1 tom_abc                     8000            10
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

13 rows selected.

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

  • all 和集合中的所有元素做比较
  • 查询薪水 比30号部门 所有员工 高的员工信息=====大于这个集合的最大值.
select * 2 from emp 3 where sal > 4* all(select sal from emp where deptno=30) SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 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 7839 KING PRESIDENT 17-NOV-81 5000 10 1 tom_abc 8000 10

子查询中的空值问题

查询不是经理的员工信息.

*思路 先按照: 查询是经理的员工信息–把所有的经理id给查找出来,形成一个集合供in操作。

  • 检索所有的经理信息
SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from emp
  3* where empno in (select mgr from emp)
SQL> /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20

6 rows selected.
  • 检索所有的不是经理信息
SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from emp
  3* where empno not in (select mgr from emp where mgr is not null)
SQL> /

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     1 tom_abc                     8000            10
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

9 rows selected.

非法使用子查询

补充：

  • select是一个视图的概念，他只负责将数据库内容显示出来，和数据库的物理存储没有必然联系，可以抓取多张表的内容汇总显示！所以可以在其后面添加任意符合sql语句的表达式，使用逗号分离，比如加上时间等信息。
1  select ename,sysdate
  2  from
  3  (select empno,ename
  4* from emp)
SQL> /

ENAME      SYSDATE
---------- ---------
tom_abc    06-JAN-17
SMITH      06-JAN-17
ALLEN      06-JAN-17
WARD       06-JAN-17
JONES      06-JAN-17
MARTIN     06-JAN-17
BLAKE      06-JAN-17
CLARK      06-JAN-17
SCOTT      06-JAN-17
KING       06-JAN-17
TURNER     06-JAN-17
ADAMS      06-JAN-17
JAMES      06-JAN-17
FORD       06-JAN-17
MILLER     06-JAN-17

15 rows selected.
  • in作用于集合的时候，有空值不会受到影响，但是not in则会受到影响，无法执行正确的数据库操作！
The reason is that all conditions that compare a null value result in a null.


Deptno In(10,20);
解释为: Deptno =10 || deptno=20 ||deptno=null
Deptno not In(10,20,null);
解释为:Deptno!=10 && Deptno!=20 && deptno!=null

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同