10-Oracle入门之子查询

最新推荐文章于 2022-03-22 10:00:04 发布
最新推荐文章于 2022-03-22 10:00:04 发布
阅读量451 收藏 1
点赞数
分类专栏： 数据库 文章标签： oracle select
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lzjsqn/article/details/54138336
版权

为什么有子查询

这里写图片描述

对于一个问题,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, ename, sal, (select ename from emp where deptno = 10) "十号部门员工"
                           *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
  • 正确实例
1  select empno, ename, sal, (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
小帅比simon
关注
专栏目录
Oracle查询入门（三）子查询、单行函数、聚合函数
Arthur_Altria的博客
03-21 363
Oracle查询入门（三）子查询，单行函数，聚合函数
10-Oracle数据库(学习4天).txt
08-19
- **复杂查询**：通过连接（JOIN）、子查询（SUBQUERY）等方式进行复杂的SQL查询操作，提高查询效率和灵活性。 - **PL/SQL编程**：介绍Oracle特有的过程化SQL语言——PL/SQL，学习如何编写存储过程、函数和触发器等...
oracle求最大值
weixin_43343423的博客
06-02 6062
oracle中查询最大值用max函数。 如emp表中有如下数据： 现要查询sal的最大值，用如下语句： select max(sal) from emp;
oracle怎么查找最大值,oracle – 在每个分区的列中查找最大值
weixin_33037051的博客
04-03 669
我有表结构,如：CREATE TABLE new_test( col1 NUMBER(2) NOT NULL,col2 VARCHAR2(50) NOT NULL,col3 VARCHAR2(50) NOT NULL,col4 VARCHAR2(50) NOT NULL);它有数据：col1 col2 col3 col40 A B X1 ...
oracle 分组查询后获取某列最大值或最小值的记录
lianzhang861的博客
06-03 1万+
需求： 获取分组后数据的某一部分。例如登录日志表中查询 所有用户最后一次登录时间，需要对用户id进行分组，并获取登录时间最大的那一条数据 语法： SELECT* FROM(SELECT分组的字段名, ROW_NUMBER()OVER(PARTITIONBY分组的字段名ORDERBY排序的字段名...
Oracle数据库之子查询
好好学java
01-06 216
Oracle数据库总结： Oracle数据库之基本查询 Oracle数据库之过滤和排序 Oracle数据库之单行函数 Oracle数据库之多行函数 Oracle数据库之多表查询 SQL&gt; 注意的问题： SQL&gt; 1. 括号 SQL&gt; 2. 合理的书写风格 SQL&gt; 3. 可以在主查询的where select having from后面都可以放置子查询 ...
13-Oracle入门之插入操作
lzjsqn的专栏
01-06 644
SQL语句类型 DML语句 (Data Manipulation Language) 数据库操作语言 insert update delete select DDL语言 data definition Lanaguage create table create view index sequence synonym同义词 truncate tabl
15-Oracle入门之delete删除操作
热门推荐
lzjsqn的专栏
01-08 2万+
综述 使用 DELETE 语句从表中删除数据。 基本套路 DELETE [FROM] table [WHERE condition]; 基本应用 使用WHERE 子句指定删除的记录。 DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. SQL> ed Wrote file afiedt
Oracle19C入门到熟练007-简单查询
langfeiyes的博客
03-22 2416
快速上手Oracle SQL查询语法， 看这篇就够了，中国人不骗中国人。
三.数据库基础-Oracle入门到精通学习这一篇就够了（详细）
ThatsThePrice的博客
03-07 5092
oracle入门到精通
oracle学习笔记--快速入门
qq_43433860的博客
09-11 1000
【注】本笔记是看完黑马视频后的学习笔记 1. 创建 1.1.创建表空间 Create tablespace mytable –创建一个名为mytable的表空间 Datafile ‘c:\myraclefiel.dbf’ –数据存放位置 Size 100m –表空间大小为100m Autoextend on –自动扩展大小 Next 10m; –每次拓展10m 1.2. 删除表空间 Deop tablespace mytable 1.3.创建用户 Create user mjn –
oracle基本查询用法入门示例
09-08
Oracle数据库是世界上最广泛使用的数据库系统之一，其强大的查询能力是数据管理的核心。本文将深入探讨Oracle的基本查询用法，帮助初学者快速入门。 一、基本`SELECT`语句 Oracle的`SELECT`语句用于从数据库中检索...
SQL-e-PL-SQL---ORACLE:开始培训ORACLE数据库
04-10
本培训将带你入门Oracle数据库，主要关注SQL和PL/SQL的学习。 SQL，全称Structured Query Language，是一种用于管理和处理关系数据库的标准语言。它包括数据查询、数据更新、数据插入和数据删除等操作，是数据操作...
Proc *C/C++入门--C/C++操作Oracle数据库
lzjsqn的专栏
01-10 8146
Proc *C/C++入门有点累啊 整理笔记！！！基本概念 SQL 构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。 1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。 P
数据库入门之常用模板汇总
lzjsqn的专栏
01-13 5885
Oracle脚本案例-- 01 创建表空间 -- 注意表空间的路径 根据实际安装环境进行调整 CREATE TABLESPACE ts_myscott01 LOGGING DATAFILE '/home/oracle/ts_myscott01.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL;CREATE TABLESPACE ts_mysco
Proc *C/C++入门之动态SQL
lzjsqn的专栏
01-11 5545
基本概念在有些情况下， 在编码时 SQL 语句还不能完整地写出来， 而是在程序执行时才能 构造出来，这种在程序执行临时生成的 SQL 语句叫动态 SQL 语句. 利用动态 SQL 来 编写 Pro*C 程序的方法叫动态 SQL 技术！ 目的：加强应用程序的功能和灵活 静态SQL —- 在编写应用程序时，使用EXEC SQL关键字直接嵌入的SQL语句；在proc编译应用程序生成c语言的时，都已经确
MySQL入门之预处理语句的使用
lzjsqn的专栏
01-13 4835
MySQL客户端／服务器协议提供了预处理语句。该功能采用了由mysql_stmt_init()初始化函数返回的MYSQL_STMT语句处理程序数据结构。对于多次执行的语句，预处理执行是一种有效的方式。首先对语句进行解析，为执行作好准备。接下来，在以后使用初始化函数返回的语句句柄执行一次或多次。　　对于多次执行的语句，预处理执行比直接执行快，主要原因在于，仅对查询执行一次解析操作。在直接执行的情况下，
MySQL入门之C语言操作MySQL
lzjsqn的专栏
01-12 4826
基本概念C APIs包含在mysqlclient库文件当中，与MySQL的源代码一块发行，用于连接到数据库和执行数据库查询。 #include <stdio.h> #include <stdlib.h> #include <string.h> #include <dlfcn.h> #include <mysql/mysql.h>int main() { int ret = 0;
Proc *C/C++入门之常用嵌入式SQL语句
lzjsqn的专栏
01-11 2943
事务控制语句Commit, rollback and savepoint. EXEC SQL commit; EXEC SQL commit work release; EXEC SQL rollback work release; 数据定义语句（DDL)create, alter, dropEXEC SQL CREAT TABLE t1(cola int); EXEC SQL DROP TABLE
Oracle SQL入门：子查询全面解析
这份ORCAL学习资料的初级入门篇对于初次接触子查询的学习者来说是一份全面的指南，涵盖了子查询的基本概念、类型、语法以及实际应用，有助于初学者逐步建立起对子查询的深入理解和熟练运用。通过学习和实践，初学者...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值