oracle9i学习笔记之十六 高级子查询

2023-12-16 21:32

本文主要是介绍oracle9i学习笔记之十六 高级子查询,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 1.子查询回顾
 
  SELECT   select_list
  FROM     table
  WHERE    expr operator(SELECT  select_list
                         FROM    table);

例1:查询emp表,显示薪水大于平均薪水的雇员信息

     SELECT *
     FROM   emp
     WHERE  sal>(SELECT AVG(sal)
                 FROM   emp);

例2:显示雇员的细节信息,要求这些雇员的经理和部门与雇员号为7521或7698是相同的,但不包括empno为7521或7698的雇员信息
   
   SELECT *
   FROM   emp
   WHERE  (mgr,deptno) IN(SELECT mgr,deptno
                          FROM   emp
                          WHERE  empno IN(7521,7698))
   AND    empno NOT IN(7521,7698);

例3:显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息
     
     SELECT *
     FROM   emp
     WHERE  (deptno,job) IN(SELECT deptno,job
                            FROM   emp
                            WHERE  sal>2900)
     AND    sal<=2900;

例4:显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息(在FROM子句中使用子查询)

    SELECT *
    FROM   emp e,(SELECT deptno,job,sal
                  FROM   emp
                  WHERE  sal>2900) s
    WHERE e.deptno=s.deptno
    AND   e.job=s.job
    AND   e.sal!=s.sal;

2.相关子查询
1)相关子查询是一种读表中每一行并且依靠相关数据比较每行的值的方法,相关子查询被用于row-by-row处理。对外查询的每一行,每个子查询被执行一次。
2)嵌套子查询与相关子查询
  (1)嵌套子查询的执行
     -内查询首先执行并且查找值
     -外查询用内查询的值执行一次
  (2)相关子查询的执行
     -用外查询取得候选行
     -用候选行的值执行内查询
     -用来自内查询的值确认或取消候选行
     -重复直到无剩余的候选行  

   SELECT column1,column2,...
   FROM   table1 outer
   WHERE  column1 operator(SELECT column1,column2
                           FROM   table2
                           WHERE  expr1=outer.expr2)                    

例:找出所有的雇员,他们挣的薪水高于该部门的平均薪水

    SELECT ename,deptno,sal
    FROM   emp outer
    WHERE  sal>(SELECT AVG(sal)
                FROM   emp
                WHERE  deptno=outer.deptno);
结果:
      ENAME  DEPTNO  SAL 
      ALLEN    30    1600 
      JONES    20    2975 
      BLAKE    30    2850 
      SCOTT    20    3000 
      KING     10    5000 
      FORD     20    3000 
      小王     10    3000 


3.EXISTS操作
  EXISTS操作对在子查询的结果集中存在的行进行检验:
  1)如果一个子查询行值被找到:
    -在内查询中的搜索不再继续
    -条件被标记为TRUE
  2)如果一个子查询行值未找到
    -条件被标记为FALSE
    -在内查询中的搜索继续

例:查找至少有一个雇员的经理信息
   
    SELECT *
    FROM   emp outer
    WHERE  EXISTS(SELECT 'X'
                  FROM   emp
                  WHERE  mgr=outer.empno);
结果:
     EMPNO  ENAME    JOB     MGR  HIREDATE      SAL  COMM  DEPTNO 
     7566   JONES  MANAGER   7839  02-4月 -81  2975         20 
     7698   BLAKE  MANAGER   7839  01-5月 -81  2850         30 
     7782   CLARK  MANAGER   7839  09-6月 -81  2450         10  
     7788   SCOTT  ANALYST   7566  19-4月 -87  3000         20 
     7839   KING   PRESIDENT       17-11月-81  5000         10 
     7902   FORD   ANALYST   7566  03-12月-81  3000         20 


4.NOT EXISTS操作
例:查找部门编号不在雇员表中的部门信息

   SELECT *
   FROM   dept
   WHERE  NOT EXISTS(SELECT *
                     FROM   emp
                     WHERE  emp.deptno=dept.deptno);
  

5.相关UPDATE
  -用一个相关子查询来更新一个表中的行,该表中的行基于另一个表中的行

  UPDATE table1 alias1
  SET    column=(SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column=alias2.column);

例:1.在雇员表中增加部门名称列
  
     ALTER TABLE emp
     ADD   (dname VARCHAR2(14));

   2.使用部门表中的部门名称更新雇员表中的部门名称

     UPDATE emp e
     SET    dname=(SELECT dname
                   FROM   dept d
                   WHERE  e.deptno=d.deptno);

6.相关delete
  -用一个相关子查询来删除一个表中的行,该表中的行基于另一个表中的行
 
  DELETE FROM table1 alias1
  WHERE  column operator(SELECT expression
                         FROM   table2 alias2
                         WHERE  alias1.column=alias2.column);

例:用一个相关子查询删除emp表中的数据,被删除的数据是emp表和job_history表中有相同empno列值的数据
   
    DELETE FROM emp e
    WHERE  empno IN (SELECT empno
                     FROM   job_history
                     WHERE  empno=e.empno);

7.WITH子句
1)当一个查询块在一个复杂的查询中出现多次时,使用WITH子句,能够用在SELECT语句中使用相同查询块
2)WITH子句取回查询块的结果,并且将它存在用户的临时表空间中
3)WITH子句可以改善性能 

例:用WITH子句,写一个查询来显示部门名称和该部门的合计薪水,那些人的合计薪水高于各部门的平均薪水

   WITH
   dept_costs AS(
       SELECT   d.dname,SUM(e.sal) AS dept_total
       FROM     emp e,dept d
       WHERE    e.deptno=d.deptno
       GROUP BY d.dname),
   avg_costs AS(
       SELECT SUM(dept_total)/COUNT(*) AS dept_avg
       FROM   dept_costs)
   SELECT *
   FROM   dept_costs
   WHERE  dept_total>(SELECT dept_avg
                      FROM   avg_costs)
   ORDER BY dname;
结果:
      DNAME        DEPT_TOTAL 
      ACCOUNTING   11750 
      RESEARCH     14075 

练习
1.显示变换过至少两次工作的雇员的详细情况(emp和job_history)

 

2.写一个子查询显示任何一个其部门号和薪水都与任何挣佣金的雇员的部门号和薪水相匹配的雇员的名字、部门号和薪水

  SELECT ename,deptno,sal
  FROM   emp
  WHERE  (deptno,sal) IN(SELECT deptno,sal
                         FROM   emp
                         WHERE  comm IS NOT NULL);

3.显示任何其薪水和佣金与任何位于loc为CHICAGO的雇员薪水和佣金相匹配的雇员的名字、部门名和薪水
 
  SELECT ename, dname, sal
  FROM emp e, dept d
  WHERE e.deptno = d.deptno
  AND (sal, NVL(comm,0)) IN (SELECT sal, NVL(comm,0)
        FROM emp e, dept d
        WHERE e.deptno = d.deptno
        AND d.loc = 'CHICAGO');

4.创建一个查询来显示所有其薪水和佣金与SCOTT相同的雇员的名字、受雇日期和薪水(不显示SCOTT的信息)
 
  SELECT ename,hiredate,sal
  FROM   emp
  WHERE  (sal,NVL(comm,0)) IN (SELECT sal,NVL(comm,0)
                                 FROM emp
                                WHERE ename='SCOTT')
  AND   ename!='SCOTT';


5.创建一个查询来显示那些所挣薪水高于所有经理(job='MANAGER')的雇员的名字、工作岗位和薪水。依据薪水从最高到最低排序结果集

  SELECT ename,job,sal
  FROM   emp
  WHERE  sal>ALL (SELECT sal
                  FROM   emp
                  WHERE  job='MANAGER')
  ORDER BY sal DESC;

6.显示那些住在城市名字以N开头的城市的雇员的ID、名字和部门号

  SELECT empno,ename,deptno
  FROM   emp
  WHERE  deptno IN(SELECT deptno
                   FROM   dept
                   WHERE  loc LIKE 'N%');


7.写一个查询来查找所有其薪水多于他所在部门的平均薪水的雇员,显示名字、部门号和部门的平均薪水,按平均薪水排序

  SELECT ename,e.deptno,sal,a.avg
  FROM   emp e,(SELECT   deptno,AVG(sal) avg
                  FROM    emp
                 GROUP BY deptno) a
  WHERE e.deptno=a.deptno
  AND   e.sal>a.avg
  ORDER BY a.avg;

8.写一个查询显示所挣薪水低于他们所在的部门平均薪水的雇员名

  SELECT ename
 FROM emp outer
 WHERE sal< (SELECT AVG(sal)
      FROM emp inner
             WHERE deptno = outer.deptno);
  或:
  SELECT ename
         FROM emp outer
  WHERE outer.sal< (SELECT AVG(inner.sal)
      FROM   emp inner
             WHERE  inner.deptno = outer.deptno);

9.查找所有不是管理人员的雇员(雇员编号不在经理编号列的雇员)

  SELECT *
  FROM   emp
  WHERE  empno NOT IN(SELECT mgr
                      FROM   emp
                      WHERE  mgr IS NOT NULL);
  或:
  SELECT *
  FROM   emp outer
  WHERE  NOT EXISTS(SELECT 'X'
                    FROM   emp
                    WHERE  mgr=outer.empno);
  或:
  SELECT *
  FROM   emp
  WHERE  empno NOT IN (SELECT NVL(mgr,0)
                       FROM   emp);

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/luyangever/archive/2008/12/13/3510764.aspx

这篇关于oracle9i学习笔记之十六 高级子查询的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/501957

相关文章

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》:本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI

数据库oracle用户密码过期查询及解决方案

《数据库oracle用户密码过期查询及解决方案》:本文主要介绍如何处理ORACLE数据库用户密码过期和修改密码期限的问题,包括创建用户、赋予权限、修改密码、解锁用户和设置密码期限,文中通过代码介绍... 目录前言一、创建用户、赋予权限、修改密码、解锁用户和设置期限二、查询用户密码期限和过期后的修改1.查询用

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3

MySQL不使用子查询的原因及优化案例

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家... 目录不推荐使用子查询和JOIN的原因解决方案优化案例案例1:查询所有有库存的商品信息案例2:使用EX

SpringBoot基于MyBatis-Plus实现Lambda Query查询的示例代码

《SpringBoot基于MyBatis-Plus实现LambdaQuery查询的示例代码》MyBatis-Plus是MyBatis的增强工具,简化了数据库操作,并提高了开发效率,它提供了多种查询方... 目录引言基础环境配置依赖配置(Maven)application.yml 配置表结构设计demo_st

Python中列表的高级索引技巧分享

《Python中列表的高级索引技巧分享》列表是Python中最常用的数据结构之一,它允许你存储多个元素,并且可以通过索引来访问这些元素,本文将带你深入了解Python列表的高级索引技巧,希望对... 目录1.基本索引2.切片3.负数索引切片4.步长5.多维列表6.列表解析7.切片赋值8.删除元素9.反转列表

正则表达式高级应用与性能优化记录

《正则表达式高级应用与性能优化记录》本文介绍了正则表达式的高级应用和性能优化技巧,包括文本拆分、合并、XML/HTML解析、数据分析、以及性能优化方法,通过这些技巧,可以更高效地利用正则表达式进行复杂... 目录第6章:正则表达式的高级应用6.1 模式匹配与文本处理6.1.1 文本拆分6.1.2 文本合并6

Redis KEYS查询大批量数据替代方案

《RedisKEYS查询大批量数据替代方案》在使用Redis时,KEYS命令虽然简单直接,但其全表扫描的特性在处理大规模数据时会导致性能问题,甚至可能阻塞Redis服务,本文将介绍SCAN命令、有序... 目录前言KEYS命令问题背景替代方案1.使用 SCAN 命令2. 使用有序集合(Sorted Set)

MyBatis框架实现一个简单的数据查询操作

《MyBatis框架实现一个简单的数据查询操作》本文介绍了MyBatis框架下进行数据查询操作的详细步骤,括创建实体类、编写SQL标签、配置Mapper、开启驼峰命名映射以及执行SQL语句等,感兴趣的... 基于在前面几章我们已经学习了对MyBATis进行环境配置,并利用SqlSessionFactory核

PostgreSQL如何查询表结构和索引信息

《PostgreSQL如何查询表结构和索引信息》文章介绍了在PostgreSQL中查询表结构和索引信息的几种方法,包括使用`d`元命令、系统数据字典查询以及使用可视化工具DBeaver... 目录前言使用\d元命令查看表字段信息和索引信息通过系统数据字典查询表结构通过系统数据字典查询索引信息查询所有的表名可