`
nannan408
  • 浏览: 1783614 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

oracle复杂查询(转)

阅读更多
使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.
  
  1.1相关子查询
  
  可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询
  
  .where中可以包含一个select语句的子查询
  
  .where中可以包含in,exists语句
  
  .最多可以嵌套16层
  
  .层次过多会影响性能
  
  [例]简单子查询实例
  
  查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目
  
  (按规定只能以一个单位来申请)
  
  SQL> create table univ_subject
  2  (
  3    name         varchar2(12) not null,
  4    per_id        number   not null,
  5   dept_name    varchar2(20)
  6  );
  SQL> insert into univ_subject values('gaoqianjing',1001,'信息工程系');
  SQL> insert into univ_subject values('wangbing',1002,'物理系');
  SQL> insert into univ_subject values('liming',1003,'化学系');
  ===============
  SQL> create table colle_subject
  2   (
  3       colle_name  varchar2(20),
  4       per_id       number
  5   );
  SQL> insert into colle_subject values('电子研究所',1001);
  SQL> insert into colle_subject values('物理研究所',1005);
  ================
  SQL> select name,per_id,dept_name from univ_subject where per_id in
  2  (select per_id from colle_subject);
  
  NAME          PER_ID    DEPT_NAME
  ------------     ---------   --------------------
  gaoqianjing       1001    信息工程系
  
  1.2外连接
  
  [例]外连接实例
  
  招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生的信息。现在要全部列出所有学生,如果某个学生在表student_skill中就有其特长信息,并显示特长信息,如果某个学生没有特长就显示特长问空.
  
  SQL> create table students
  2  (
  3    st_id  varchar2(20),
  4    name varchar2(10),
  5    age   number(2),
  6    tol_score  number(3)
  7  ) ;
  SQL>  insert into students values('973231','wangbindu',22,501);
  SQL>  insert into students values('973232','zhuzhijing',21,538);
  SQL> insert into students values('973233','gaojing',21,576);
  ===================
  
  SQL> create table student_skill
  2  (
  3   st_id varchar2(20),
  4   skill  varchar2(20)
  5 );
  SQL> insert into student_skill values('973231','篮球');
  SQL> insert into student_skill(st_id) values('973232');
  SQL> insert into student_skill values('973233','足球');
  ===================
  
  SQL>  select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
  order by a.st_id;
  
  ST_ID        NAME     AGE   TOL_SCORE SKILL
  -------------------- ---------- --------- --------- ----------
  973231        wangbindu   22    501    篮球
  973232        zhuzhijing   21    538
  973233        gaojing    21    576    足球
  
  1.3自我连接
  
  自我连接是在同一个表或视图内进行条件连接.
  
  [例]自我连接实例
  
  查询每个雇员的名字和该雇员的经理的名字:
  
  SQL> select e1.ename||'  work for  '||e2.ename "Employees and their Managers"
  
  2 from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;
  
  Employees and their Managers
  -------------------------------------------------
  SMITH   work for  FORD
  ALLEN   work for  BLAKE
  WARD   work for  BLAKE
  JONES   work for  KING
  MARTIN  work for  BLAKE
  BLAKE   work for  KING
  CLARK   work for  KING
  SCOTT   work for  JONES
  TURNER work for  BLAKE
  ADAMS   work for  SCOTT
  JAMES   work for  BLAKE
  FORD    work for  JONES
  MILLER   work for  CLARK
  
  1.4UNION , INTERSECT及 MINUS
  
  UNION:      可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)
  
  具体语法:
  
  select ...
  union[all]
  select...
  ==========
  
  INTERSECT: 返回两个表中相同的信息
  具体语法:
  select ...
  intersect
  select...
  ==========
  
  MINUS     : 返回一个表中出现的信息
  具体语法:
  select ...
  minus
  select...
  [例1]UNION操作实例
  SQL> select st_id from students
  2 union
  3 select st_id from student_skill;
  
  ST_ID
  --------------------
  973231
  973232
  973233
  
  [例2]INTERSECT操作实例
  
  列出有特长的学生的学号
  
  SQL> select st_id from students
  2 intersect
  3 select st_id from student_skill;
  ST_ID
  --------------------
  973231
  973233
  
  [例3]MINUS操作实例
  
  列出没有特长学生的学号
  
  select st_id from students
  minus
  select st_id from student_skill;
  ST_ID
  --------------------
  973232
  
  2.创建复杂的视图
  
  许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.
  
  2.1分组视图
  
  [例1]简单的分组视图
  
  SQL> create or replace view dept_tot as
  2 select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
  3 where a.deptno=b.deptno group by a.dname;
  
  查看已建立。
  SQL> select * from dept_tot;
  
  DEPT       TOTAL_SAL
  --------------  ---------
  ACCOUNTING     8750
  RESEARCH      10875
  SALES       9400
  
  [例2]带复杂函数视图
  
  SQL> create or replace view itemtot as
  2 select persion,sum(amount) itemtot from ledger
  3 where actiondate between
  4 to_date('01-MAR-1901','dd-mon-yyyy') and
  5 to_date('31-MAR-1901','dd-mon-yyyy')
  6 and action in('bought','raid') group by persion;
  
  2.2合计视图
  
  [例]合计函数视图实例
  
  SQL> create or replace view emp_no1 as
  2 select deptno,sum(sal) 工资和,sum(comm) 总和
  3 from scott.emp group by deptno;
  SQL> select * from emp_no1;
  DEPTNO  工资和   总和
  -------- --------- ---------
  10     8750
  20     10875
  30     9400   2200
  
  2.3组合视图
  
  [例]带组合函数的视图
  
  SQL> create or replace view byitem as
  2 select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal
  3 from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
  4 and actiondate between
  5 to_date('01-MAR-1901','dd-mon-yyyy') and
  6 to_date('31-MAR-1901','dd-mon-yyyy')
  7  and action in('bought','raid') ;
  
  3.家族树
  
  语法:
  select column from table_name start with column=value
  connect by prior 父主键=子主键
  
  3.1排除单一性和分枝
  
  以ORACLE中的EMP表为例
  
  [例]从顶到底列出各雇员的信息
  SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
  2 connect by prior empno=mgr;
  
  NAME                 EMPNO    MGR
  ---------             ---------  ---------
  KING                 7839
  JONES                7566      7839
  SCOTT                7788      7566
  ADAMS                7876      7788
  
  3.2遍历至根
  
  [例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
  
  SQL> col ename for a30;
  SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
  2 start with mgr=7788 connect by prior mgr=empno;
  ENAME                MGR   EMPNO
  --------------------------    --------- ---------
  ADAMS                7788    7876
  SCOTT                7566    7788
  JONES                7839    7566
  KING                      7839
  
  [例2]列出所有雇员的层次结构
  
  SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
  2 start with mgr is not null
  3 connect by empno=prior mgr;
  
  ENAME               EMPNO    MGR
  ------------------        ---------  ---------
  SMITH                7369     7902
  FORD                7902     7566
  JONES                7566     7839
  KING                7839
  ALLEN                7499     7698
  BLAKE                7698     7839
  KING                7839
  WARD                7521     7698
  BLAKE                7698     7839
  KING                7839
  JONES                7566     7839
  KING                7839
  MARTIN               7654     7698
  BLAKE                7698     7839
  KING                7839
  BLAKE                7698     7839
  KING                7839
  CLARK                7782     7839
  KING                7839
  SCOTT                7788     7566
  JONES                7566     7839

文章转载自网管网:http://www.bitscn.com/pdb/oracle/200604/16924.html
分享到:
评论

相关推荐

    oracle 复杂查询语句的使用

    总的来说,Oracle的复杂查询功能强大,灵活多样,熟练运用这些技巧可以极大地提高数据查询的效率和准确性,同时降低数据库的维护成本。通过不断地学习和实践,开发者可以更好地理解和应用这些技术,以满足各种复杂的...

    Oracle复杂查询实验报告

    Oracle复杂查询实验报告, 用SQL语句实现下列查询。要求:能用嵌套查询的尽量使用嵌套查询。

    oracle复杂相关子查询

    复杂相关子查询 动画版

    oracle数据库复杂查询处理

    oracle数据库复杂查询处理,其中提供了详细的解决思路,由简入繁,对我们开发人员由其重要

    Oracle数据库sql转换mysql数据库工具

    总之,"Oracle数据库sql转换mysql数据库工具"是为了简化数据库迁移过程而设计的,通过自动化的方式,降低了数据库系统切换的复杂性和风险。在实际操作中,用户应根据自身需求,结合这些工具,遵循最佳实践,确保迁移...

    Mysql转oracle工具

    MySQL的权限管理相对简单,而Oracle有更复杂的用户、角色和权限体系。在转换时,需要考虑用户权限和角色的迁移。 4. **表空间和分区**: Oracle支持表空间和分区,这是MySQL不具有的特性。在转换过程中,可能需要...

    oracle通用查询系统

    Oracle通用查询系统是一款基于Delphi开发的图形化数据库查询工具,专为用户提供了方便快捷的方式来探索和操作Oracle数据库。这款工具允许用户直观地查看数据库的表结构,包括字段信息、索引、触发器和约束,使得...

    mysql 数据库转 oracle

    3. **触发器和存储过程**:如果MySQL数据库中包含触发器和存储过程,迁移工具需要能处理这些复杂逻辑,确保在Oracle中重建。 4. **权限和角色**:用户权限和角色在数据库迁移中同样重要。工具需要考虑如何将MySQL的...

    oracle姓名中文转全拼函数

    Oracle姓名中文转全拼函数是解决在Oracle数据库中处理中文姓名时的一个常见需求,特别是考虑到汉字的多样性和复杂性,包括生僻字和多音字的处理。此函数旨在将中文姓名转换为全拼,方便进一步的数据处理或搜索。下面...

    Oracle 数据查询器

    Oracle数据查询器的开发,体现了Delphi与Oracle数据库的完美结合,为数据库管理员和开发人员提供了一个直观、易用的界面,减少了对复杂命令行工具的依赖。通过这款工具,用户无需深入了解Oracle的底层细节,也能高效...

    oracle系列教程三+oracle复杂SQL语句.doc

    本文将深入探讨Oracle中的复杂SQL语句,包括查询优化、索引、函数的使用以及如何进行行列转换和去重操作。 首先,了解SQL语句的执行顺序至关重要。在一般的查询中,如`SELECT`、`FROM`、`WHERE`,执行顺序为:先...

    oracle金额转换

    如果需要更复杂的格式,例如国际化的货币格式,可以使用NLS参数,如`NLS_NUMERIC_CHARACTERS`和`NLS_CURRENCY`。这些参数可以在会话级别或系统级别设置,影响所有数值的显示方式。 此外,还可以使用自定义的PL/SQL...

    oracle拼音首字母查询

    Oracle拼音首字母查询是一...总的来说,Oracle拼音首字母查询是一个结合了字符编码、数据库索引和字符串处理技术的复杂过程。正确地实施可以极大地提升中文数据的检索效率,但也需要根据具体需求和资源来权衡解决方案。

    oracle开发复杂查询

    Oracle数据库是世界上最流行的数据库管理系统之一,尤其在处理复杂查询方面表现出强大的功能。本文将深入探讨Oracle9i中常用的复杂查询技术,帮助你提升SQL语言的运用水平。 首先,我们来了解查询的基本结构,它由...

    Oracle行转列(不定列)

    在数据库管理领域,Oracle数据库系统提供了丰富的功能来处理复杂的数据操作。其中,"行转列(不定列)"是数据库查询中的一种常见需求,尤其是在数据分析和报表生成时。本篇文章将详细探讨Oracle如何实现行转列为列的...

    oracle行转列

    "Oracle行转列"是数据库查询中常见的一种需求,尤其是在数据分析和报表展示时。这个主题涉及到Oracle数据库中的聚合函数、分析函数以及一些高级SQL技巧。 首先,"行转列"的基本思想是将数据库表中的一列数据转换为...

    Oracle查询优化改写技巧与案例

    , 如果您是开发人员,经常与Oracle打交道,那么《Oracle查询优化改写技巧与案例》可以帮助您处理复杂的需求,写出高性能的语句。如果您是运维人员,则《Oracle查询优化改写技巧与案例》可以帮助您更快地完成慢语句的...

    Oracle行转列之pivot

    在Oracle数据库中,行转列(也称为数据透视)和列转行(unpivot)是SQL查询中用于数据转换的高级功能。从Oracle 11g版本开始,引入了PIVOT和UNPIVOT关键字,以支持显式的查询转换,即从行数据转换为列数据,或从列...

    【oracle】oracle查询优化改写

    此外,子查询优化可能包括子查询消除、子查询合并或子查询物化,以减少查询的复杂性和提高执行速度。 优化器是Oracle处理SQL查询的核心组件,它负责选择执行查询的最佳执行计划。Oracle有多种优化器,如RBO(Rule-...

Global site tag (gtag.js) - Google Analytics