Oracle入门之子查询

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle入门之子查询，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

为什么有子查询

对于一个问题,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

子查询的分类

单行子查询

查询员工信息,属于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 放置子查询 (必须要放单行子查询)

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
原文链接：https://www.f2er.com/oracle/210970.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问