精华内容
下载资源
问答
  • Oracle虚拟

    千次阅读 2013-08-12 15:21:37
    Oracle11g增加了表的虚拟,这个的数据并没有存储在数据文件中,而是Oracle通过数据的生成放到了数据字典中。虚拟的数值是通过真实中的数据计算而来的。虚拟位置可以放在它参考的的前面,也可以包括...
    Oracle11g增加了表的虚拟列,这个列的数据并没有存储在数据文件中,而是Oracle通过列数据的生成放到了数据字典中。虚拟列的数值是通过真实列中的数据计算而来的。虚拟列的位置可以放在它参考的列的前面,也可以包括多个实际列的值,但是不能引用其他的虚拟列: 
    

    SQL> CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (c_vl_1+1)  );

    Table created

    SQL> CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (c_vl_1+1),   c_vl_3 AS (c_vl_2+1)  );

    CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (c_vl_1+1),   c_vl_3 AS (c_vl_2+1)  )

    ORA-54012: 在列表达式中引用了虚拟列


    以上只是一个简单的虚拟列的例子,实际上虚拟列的完整写法应该包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字,主要用于描述虚拟列的特性,写与不写没有本质区别。而列的数据类型如果忽略,那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型:
    SQL> CREATE TABLE yu_test1 ( v_cl_1 VARCHAR2(30),  v_cl_2 CHAR(50) GENERATED ALWAYS AS (LOWER(v_cl_1)) VIRTUAL );

    Table created

    SQL> desc yu_test1;
    Name   Type         Nullable Default         Comments
    ------ ------------ -------- --------------- --------
    V_CL_1 VARCHAR2(30) Y                                
    V_CL_2 CHAR(50)     Y        LOWER("V_CL_1") 


    虚拟列可以使用Oracle自带的函数或用户定义的函数,不过对于用户定义的函数要求必须声明函数的确定性(DETERMINISTIC),虚拟列必须是对实际列进行操作后的结果,不能使用没有实际列当做入参的函数,也就是说,虚拟列必须和表字段有关联:

    SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 RETURN NUMBER AS
      2  BEGIN
      3    RETURN 1;
      4  END;
      5  /
    Function created

    SQL> drop table yu_test1 purge;

    Table dropped

    SQL> CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (FUN_TEST1) );

    CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (FUN_TEST1) )

    ORA-54016: 指定了无效的列表达式

    SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS
      2  BEGIN
      3    RETURN 1;
      4  END;
      5  /

    Function created

    SQL> CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (FUN_TEST1(c_vl_1)) );

    CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (FUN_TEST1(c_vl_1)) )

    ORA-30553: 函数不能确定

    SQL>
    SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER DETERMINISTIC AS
      2  BEGIN
      3    RETURN 1;
      4  END;
      5  /

    Function created

    SQL> CREATE TABLE yu_test2   (c_vl_1 NUMBER,   c_vl_2 AS (FUN_TEST1(c_vl_1)) );

    Table created

    SQL>

    DETERMINISTIC是必须的。
    不过Oracle虽然在创建创建的时候会检查函数的确定性,在表建立之后,却可以将函数替换为非确定性函数:
    SQL> insert into yu_test2 (c_vl_1) values(1);

    1 row inserted

    SQL> commit;

    Commit complete

    SQL> DROP FUNCTION FUN_TEST1;

    Function dropped

    SQL> select * from yu_test2;

    select * from yu_test2

    ORA-00904: "YUZH"."FUN_TEST1": 标识符无效

    SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS
      2  BEGIN
      3    RETURN 2;
      4  END;
      5  /

    Function created

    SQL> select * from yu_test2;

        C_VL_1     C_VL_2
    ---------- ----------
             1          2
            
            
    建立了虚拟列可以有效的减少数据的存储,简化查询语句中对列进行的处理,而且还可以利用虚拟列进行分区。不过虚拟列还会带来其他问题。首先包含了虚拟列的表在INSERT INTO语句中不能省略COLUMN列表。由于虚拟列的值是由其他列的值计算得出的,且Oracle并不存储虚拟列的值,因此无论是INSERT还是UPDATE都不能对虚拟列进行修改:
    SQL> insert into yu_test2 values(1);

    insert into yu_test2 values(1)

    ORA-00947: 没有足够的值

    SQL> insert into yu_test2 values(1,1);

    insert into yu_test2 values(1,1)

    ORA-54013: 不允许对虚拟列执行 INSERT 操作

    SQL> insert into yu_test2 (c_vl_1,c_vl_2) values(1,1);

    insert into yu_test2 (c_vl_1,c_vl_2) values(1,1)

    ORA-54013: 不允许对虚拟列执行 INSERT 操作

    SQL> insert into yu_test2 (c_vl_1) values(1);

    1 row inserted

    SQL> commit;

    Commit complete

    SQL> update yu_test2 set c_vl_1=2;

    2 rows updated

    SQL> update yu_test2 set c_vl_2=2;

    update yu_test2 set c_vl_2=2

    ORA-54017: 不允许对虚拟列执行 UPDATE 操作

    SQL> commit
      2  ;

    Commit complete


    如果程序选择使用了一些工具来自动生成表的INSERT、UPDATE语句,那么遇到包含虚拟列的表就会报错。出于同样的原因,无法使用CREATE TABLE AS SELECT创建一个包含虚拟列的表。解决方法是CREATE TABLE AS SELECT结束后通过ALTER TABLE添加虚拟列。虚拟列还存在一个问题,当虚拟列的值一旦被实体化,那么虚拟列表达式发生变化会造成实体化结果与虚拟列不一致。简单的说就是虚拟列的结果是在查询的时候确定的,如果修改了虚拟列的表达式,下次执行查询时,虚拟列的值就会发生变化。但是一旦对虚拟列建立了索引,或者对包含虚拟列的表建立了物化视图,那么虚拟列的数值就被实际的存储下来,当虚拟列的表达式发生修改后,会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。这个问题的解决方法只有删除索引并重建,或者将物化视图完全刷新。

    SQL> create index YU_TEST2_IDX on YU_TEST2 (c_vl_2);

    Index created

    SQL> ALTER TABLE YU_TEST2 MODIFY c_vl_2 AS (UPPER(c_vl_1));

    ALTER TABLE YU_TEST2 MODIFY c_vl_2 AS (UPPER(c_vl_1))

    ORA-54022: 无法更改虚拟列表达式, 因为在列上定义了索引

    虽然建立了索引后Oracle会禁止虚拟列发生修改,但是Oracle并不禁止虚拟列参考的函数的修改,修改方式见前面。

    展开全文
  • oracle查询单列时去重,最常用的应该时distinct了 例如有如下的表(企业信息注册表),表中数据如下 查询单列并去重,如查询企业名称corporname,查看所有的企业名称 查询单列,用distinct就可以实现 2,...

    oracle查询单列时去重,最常用的应该时distinct了

    例如有如下的表(企业信息注册表),表中数据如下

    1. 查询单列并去重,如查询企业名称列corporname列,查看所有的企业名称

    查询单列,用distinct就可以实现

    2,如果想查询企业所有的信息 ,并根据企业名称去重,相同企业展示最新的注册时间registTime

    这时候如果在使用distinc就实现不了,如下是使用distinct的结果,相同企业B,应该展示最新的注册时间20200422,但使用distinct并没有达到效果

    select distinct t.corporname,t.corporid ,t.registtime from register t

    当 distinct 作用在多个字段的时候,它只会将所有字段值都相同的记录“去重”掉,显然这里的企业B数据不满足此条件,其corporid和registtime不一致。

    关键字 distinct 只能放在 SQL 语句中所有字段的最前面才能起作用,如果放错位置,SQL 执行时会报错,缺失表达式

    如下:

     

    3.使用rank() over()

    这时候就需要用到rank() over()了,查询多列,并对其中一列按指定条件去重

    语法 rank() over(partition by 列1 order by 列2  按列1分组,并在列1分组的基础上,对每个组按列b排名,默认升序(类似oralce分页中的rowid)

    如下就可以实现查询企业所有的信息 ,并根据企业名称去重,相同企业展示最新的注册时间registTime

      select corporid, corporname, registtime
      from (select t.*,
                   rank() over(partition by t.corporname order by t.registtime desc) rn
              from register t) k
     where rn = 1

    本文参考如下资源

    https://blog.csdn.net/DavieSmile/article/details/89216441

    https://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html

    展开全文
  • Oracle高级查询

    千次阅读 2016-11-06 23:49:10
    概述高级查询在数据库的开发过程中应用广泛,本博文将从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询,最后典型案例的应用。sql窗口修改已执行的sqled表示编辑上个sql / 表示执行上个sql分组查询分组...

    概述

    高级查询在数据库的开发过程中应用广泛,本博文将从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询,最后典型案例的应用。

    sql窗口修改已执行的sql

    ed表示编辑上个sql
    / 表示执行上个sql

    这里写图片描述


    分组查询

    分组函数的概念

    分组函数作用于一组数据,并对一组数据返回一个值。

    这里写图片描述


    分组函数的语法

    这里写图片描述


    常用分组函数

    • avg
    • sum
    • min
    • max
    • count
    • wm_contact 行转列

    更多及用法请参考oracle函数


    常见分组函数的使用

    avg()/sum()

    求出员工的平均工资和工资总和。

    SQL> select avg(sal) , sum(sal) from emp ;
     
      AVG(SAL)   SUM(SAL)
    ---------- ----------
    2073.21428      29025
     
    

    min()/max()

    SQL> select min(sal), max(sal) from emp;
     
      MIN(SAL)   MAX(SAL)
    ---------- ----------
           800       5000
     
    SQL> 
    

    count()

    SQL> select count(1) from emp;
     
      COUNT(1)
    ----------
            14
    

    distinct 关键字

     
    SQL> select distinct(deptno)  from emp;
     
    DEPTNO
    ------
        30
        20
        10
    

    wm_concat()行转列

    SQL> select deptno   部门 ,wm_concat(ename) 部门总的员工 from emp  group by deptno;
     
      部门 部门总的员工
    ---- --------------------------------------------------------------------------------
      10 CLARK,MILLER,KING
      20 SMITH,FORD,ADAMS,SCOTT,JONES
      30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
     
    

    这里写图片描述

    wm_concat不同版本的区别

    这里写图片描述

    10.2.0.4以前,wm_concat返回的是varchar2,10.2.0.5开始,是CLOB.


    nvl()/nvl2()

    分组函数会自动忽略空值, nvl()函数可以使分组函数不忽略空值

    NVL (expr1, expr2)
    【功能】若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。

    NVL2 (expr1, expr2, expr3)
    【功能】expr1不为NULL,返回expr2;expr2为NULL,返回expr3。
    expr2和expr3类型不同的话,expr3会转换为expr2的类型


    group by

    这里写图片描述


    语法

    这里写图片描述

    • 在select列表中所有未包含在函数中的列都应该包含在group by子句中,否则会抛出 ora-00937 not a singel-group group function。
    select  a, b, c ,avg(d)
    from table_name 
    group by a, b ,c ;
    
    • 包含在group by子句中的列,不必包含在select列表中。
    select avg(sal) 
    from emp 
    group by deptno;
    

    使用多个列分组

    这里写图片描述

    按照部门、不同的职位,统计员工的工资总和。

    select deptno, job, sum(sal) from emp group by deptno, job order by deptno;
    

    这里写图片描述

    先按照deptno分组,再按照job分组,如果都一样,则是同一组数据。


    过滤分组-having子句的使用以及和where的区别

    这里写图片描述


    having子句语法

    这里写图片描述


    having子句和where的区别

    • where子句中不能使用组函数,having后可以使用;
    • 当where和having可以通用的情况下,优先使用where,效率更高
      where 先过滤后分组
      having 先分组后过滤
      优先使用where

    举例:
    这里写图片描述


    在分组函数中使用order by

    这里写图片描述

    select deptno , avg(sal) 
    from emp 
    group by deptno
    order by avg(sal) ;--按表达式排序
    
    select deptno , avg(sal) 平均工资 
    from emp 
    group by deptno
    order by 平均工资  ;--按别名排序
    
    
    select deptno , avg(sal) 平均工资 
    from emp 
    group by deptno
    order by 2  ; --按序号排序,表示第二列。 如果只有2列,不能出现比2大的值
    

    分组函数的嵌套

    栗子: 求部门平均工资的最大值

    1. 先求出部门的平均工资
    2. 再求出平均工资中的最大值
    select max(avg(sal)) from emp group by deptno;
    

    包含在group by子句中的列,不必包含在select列表中。


    group by语句的增强

    这里写图片描述

    分析一下这个报表

    第一个红框内的是  按照部门和职位统计 工作总和
    select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
    
    第二个小篮筐是 部门工资的总和
    select deptno ,sum(sal) from  emp group by deptno order by deptno;
    
    第三个总计是 工资总和 
    
    select sum(sal) from emp ;
    
    
    

    整合一下:

    select  * from (
    select deptno , job, sum(sal) from emp group by deptno ,  job
    union all 
    select deptno ,null ,sum(sal) from  emp group by deptno
    union all 
    select  null ,null ,sum(sal) from emp  )  order by deptno, job ;
    

    这里写图片描述

    我们可以通过oracle提供的rollup函数来简化书写的过程。

    select deptno, job, sum(sal) from emp group by rollup(deptno, job);
    

    可以得到同样的结果

    这里写图片描述


    理解rollup

    rollup官方文档

    select a, b, c, sum( d )
    from t
    group by rollup(a, b, c);
    

    等效于

    select * from (
    select a, b, c, sum( d ) from t group by a, b, c 
    union all
    select a, b, null, sum( d ) from t group by a, b
    union all
    select a, null, null, sum( d ) from t group by a
    union all
    select null, null, null, sum( d ) from t
    )
    

    引申 GROUPING SETS 、 CUBE 、GROUPING

    数据

    create table students
    (id number(15,0),
    area varchar2(10),
    stu_type varchar2(2),
    score number(20,2));
    insert into students values(1, '111', 'g', 80 );
    insert into students values(1, '111', 'j', 80 );
    insert into students values(1, '222', 'g', 89 );
    insert into students values(1, '222', 'g', 68 );
    insert into students values(2, '111', 'g', 80 );
    insert into students values(2, '111', 'j', 70 );
    insert into students values(2, '222', 'g', 60 );
    insert into students values(2, '222', 'j', 65 );
    insert into students values(3, '111', 'g', 75 );
    insert into students values(3, '111', 'j', 58 );
    insert into students values(3, '222', 'g', 58 );
    insert into students values(3, '222', 'j', 90 );
    insert into students values(4, '111', 'g', 89 );
    insert into students values(4, '111', 'j', 90 );
    insert into students values(4, '222', 'g', 90 );
    insert into students values(4, '222', 'j', 89 );
    commit;
    
    
    

    GROUPING SETS

    GROUPING SETS官方文档

    select id,area,stu_type,sum(score) score 
    from students
    group by grouping sets((id,area,stu_type),(id,area),id)
    order by id,area,stu_type;
    
    

    理解grouping sets

    select a, b, c, sum( d ) from t
    group by grouping sets ( a, b, c )
    

    等效于

    select * from (
    select a, null, null, sum( d ) from t group by a
    union all
    select null, b, null, sum( d ) from t group by b 
    union all
    select null, null, c, sum( d ) from t group by c 
    )
    

    CUBE

    CUBE 官方文档

    select id,area,stu_type,sum(score) score 
    from students
    group by cube(id,area,stu_type)
    order by id,area,stu_type;
    
    

    理解cube

    select a, b, c, sum( d ) from t
    group by cube( a, b, c)
    

    等效于

    select a, b, c, sum( d ) from t
    group by grouping sets( 
    ( a, b, c ), 
    ( a, b ), ( a ), ( b, c ), 
    ( b ), ( a, c ), ( c ), 
    () )
    
    

    GROUPING

    GROUPING官方文档

    从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null。

    如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!

    select decode(grouping(id),1,'all id',id) id,
    decode(grouping(area),1,'all area',to_char(area)) area,
    decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
    sum(score) score
    from students
    group by cube(id,area,stu_type)
    order by id,area,stu_type; 
    

    多表连接

    多表查询

    这里写图片描述


    笛卡尔积

    这里写图片描述

    这里写图片描述

    • 列等于两个表列数的和
    • 行等于两个表行数的乘积

    笛卡尔积中并不是全部正确的数据,要根据连接条件进行筛选。

    比如刚才的dept和emp, 满足连接条件emp.deptno=dept.deptno才是正确的数据。

    在实际运行环境下,应避免使用笛卡儿积全集。
    连接条件至少有n-1个。


    ORACLE表的四种连接方式

    等值连接

    通过两个表具有相同意义的列,可以建立相等连接条件。
    只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。

    这里写图片描述

    select a.empno, a.ename, a.sal, b.dname
      from emp a, dept b
     where a.deptno = b.deptno;
    

    这里写图片描述


    不等值连接

    两个表中的相关的两列进行不等连接,
    比较符号一般为>,<,…,between… and…(小值在前 大值灾后)

    这里写图片描述

    select e.empno, e.ename, e.sal, s.grade
      from emp e, salgrade s
     where e.sal between s.losal and s.hisal;
    

    这里写图片描述


    外连接

    对于外连接,Oracle中可以使用“(+)”来表示,还可以使用LEFT/RIGHT/FULL OUTER JOIN 。

    外连接就是为了解决:通过外链接,把对于连接条件上不成立的记录,仍然包含在最后的结果中.


    右外连接

    A) 左条件(+) = 右条件;
      代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。

    此时也称为"右外连接".另一种表示方法是:

     SELECT ... FROM1 RIGHT OUTER JOIN2 ON 连接条件
    

    出现在表2中的字段,如果表1不存在该值,依然输出


    左外连接

    B) 左条件 = 右条件(+);
      代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。
     
      此时也称为"左外连接"

     SELECT ... FROM1 LEFT OUTER JOIN2 ON 连接条件
    

    存在表1的数据,如果表2不存在,依然输出


    这里写图片描述

    数据说明:
    部门表有个id为40的部门。而员工表中却没有deptno=40的员工。

    
    select   p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  ,dept p
    where e.deptno (+)= p.deptno --右外连接 , 显示右边的表 不能匹配的信息   注意等号两侧表的顺序
    group by   p.deptno , p.dname
    order by p.deptno;
    
    
    select   p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  ,dept p
    where  p.deptno = e.deptno (+) --左外连接 , 显示左边的表 不能匹配的信息  注意等号两侧表的顺序
    group by   p.deptno , p.dname
    order by p.deptno;
    
    
    
    select  p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  right  join dept p  on e.deptno = p.deptno 
     group by   p.deptno , p.dname
    order by p.deptno;
    
    ---不推荐这样写,因为dept不是主表。
    select  p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from dept p  left  join  emp e  on e.deptno = p.deptno 
     group by   p.deptno , p.dname
    order by p.deptno;
    
    

    这里写图片描述


    自连接

    自连接核心:通过别名,将同一张表视为多张表 ,多表做笛卡儿相等连接。

    这里写图片描述

    数据说明 mgr字段
    这里写图片描述

    select a.empno 员工工号,
           a.ename 员工姓名,
           a.mgr   领导工号,
           b.empno 领导工号,
           b.ename 领导姓名
      from emp a
      join emp b
        on a.mgr = b.empno
     order by a.empno;
    

    这里写图片描述


    自连接存在的问题和解决办法

    问题:不适合大量数据的表

    自连接不适合大量数据的表:因为查询同一个表看做多个表,他们的笛卡尔全集的记录数至少为 行数的平方 。如果看做3个表这是立方关系。假设emp有1亿条数据…做自连接的话 可想而知

    解决办法:层次查询connetct by
    层次查询概述

    oracle中的select语句可以用START WITH…CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的.

    基本语法:
    SELECT [LEVEL],column,expression, ...
    
    FROM table
    
    [WHERE conditions]
    
    [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
    
    
    • level是伪列,代表树的层级,根节点level为1,子节点为2等。

    • from后面只能是一个表或一个视图。

    • where条件可以限制查询返回的行,但不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响.注意,彻底剪枝条件应放在connect by(connect by之后也可跟过滤条件,它将该条件节点后的所有子孙后代一并去除不显示,如:connect by prior employee_id=manager_id and employee_id>10),单点剪掉条件应放在where子句(入层后不输出)。因为connect by的优先级要高于where,也就是sql引擎先执行connect by。

    • start_condition定义层次化查询的起点,如employee_id=1。

    • prior_condition定义父行和子行之间的关系,如父子关系定义为employee_id=manager_id,表示父节点employee_id和子节点manager_id之间存在关系。如果不加PRIOR关键字则不会进行递归,只是相当于一个过滤条件,只能得到根节点。

    另外,该关键字可放在前列前,也可放在后列前,放在哪列前哪列就是根节点

    如果connect by prior中的prior被省略,则查询将不进行深层递归,只能得到根节点。

    这里写图片描述

    select
     level,--是oracle中的伪列,其实在emp表中并没有该字段
     empno, 
     ename, 
     sal, 
     mgr
      from emp
     start with mgr is null  /**只有根节点可以用 is null 这种写法 ,或者 empno=7839 也表示是从根节点开始 .当然也可以从任意节点开始遍历,获取特定子树 .比如遍历  JONES下面的所有子节点 empno=7566**/
     connect by prior empno = mgr
     order by 1;--按照第一个字段排序 即按照levle排序
    

    connect by prior empno = mgr 等号左右两侧的字段顺序不要搞反了。表示父节点empno 和子节点mgr 之间存在关系。

    这里写图片描述

    connect by生成序列:

    如生成一个1到10的序列:

    SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
    

    其原理在于:省略start with则以所以点为根节点,而dual表只有一行所有只有一个节点,而connect by则对所有输入内容进行遍历。

    上面的方法受制于rownum伪列的限制,想得到指定始尾的序列我们也可以借助level伪列,如:select level from dual where level >= 5 connect by level <= 10;

    自连接和层次查询各有利弊,看使用场景。层次查询:不存在多表查询,但是查询结果没有自查询直观。


    子查询

    子查询概述

    这里写图片描述


    语法

    这里写图片描述


    分类

    子查询分为 单行子查询 和 多行子查询


    这里写图片描述

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

    子查询需要注意的10个问题

    1. 不要忘记子查询语法中的小括号
    2. 形成良好的子查询的书写风格
    3. 可以使用子查询的位置:Where,select,having,from
    4. 不可以使用子查询的位置:group by
    5. 强调:from后面的子查询
    6. 主查询和子查询可以不是一张表
    7. 一般不在自查询中,使用排序;但是在Top-N分析问题中,必须对子查询排序
    8. 一般先执行子查询,再执行主查询;但相关子查询例外
    9. 单行子查询只能使用单行操作符;多行子查询只能多行操作符
    10. 注意:子查询中是Null值的问题

    子查询语法中的小括号问题

    子查询必须有小括号,否则会抛出ora-00936 :missing expression

    这里写图片描述


    子查询的书写风格问题

    注意换行和缩进


    可以使用子查询的位置

    可以使用子查询的位置:Where,select,having,from

    where:

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

    select:

    这里写图片描述


    having:

    select   deptno , avg(sal)
    	from emp 
    		group by deptno
    			having avg(sal) > (select max(sal)
                       from emp
    	                   where deptno=30);
    

    having不能换成where, 因为where后面不能使用分组函数。


    from:

    select * 
    	from (
           select a.empno ,a.ename ,a.deptno 
    	       from emp a);
    

    不可以使用子查询的位置

    不可以在group by 后使用子查询

    这里写图片描述


    from后面的子查询

    这里写图片描述


    这里写图片描述

    select *
    from (select empno,ename,sal from emp);
    

    这里写图片描述

    select * 
    from (select empno,ename,sal,12*sal 年薪 from emp);
    

    主查询和子查询可以不是同一张表

    这里写图片描述

    select *
    from emp 
    where deptno=(select deptno
    from dept
    where dname='SALES');
    

    当然也可以使用多表查询的方式:

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

    理论上应该尽量使用多表查询,因为上面的子查询有两个from语句,所以要对数据库访问查询两次,而下面的多表查询只访问了一次!这是理论上的结论,并没有考虑实际比如多表查询中产生的笛卡尔积的大小,具体情况还是要具体对待。


    子查询的排序问题

    这里写图片描述

    select rownum,empno,ename,sal
    from (select * from emp order by sal desc)
    where rownum<=3;
    

    rownum 行号,oracle提供的伪列。

    将排序后的表作为一个集合放到from()中 生成一个新表
    重新再查询rownum 就可以让rownum也实现排序了


    行号需要注意的两个问题

    • 1、行号永远按照默认的顺序生成
    • 2、行号只能使用<,<=;不能使用>,>=

    针对1的情况 举个栗子:
    这里写图片描述

    我们按照工资排序下,在看下rownum的顺序

    这里写图片描述

    即使用order by排序,也不会打乱rownum默认生成行号的顺序 。


    针对2的情况 举个栗子:

    这里写图片描述

    可以看到 当使用rownum >号时,获取到的结果为空。


    主查询和子查询的执行顺序

    一般先执行子查询,再执行主查询;但相关子查询例外。

    那什么是相关子查询呢?

    相关子查询的典型结构如下:

    select columnlist
      from table1 t1
     where column2 in 
     (select column3 from table2 t2 where t2.column3 =     t1.column4)
    

    也就是说在子查询中使用到了外查询的表和相关的列。

    这样无法像嵌套子查询一样一次将子查询的结果计算出来然后再和外查询挨个比对,相关子查询对于外部查询的每一个值都会有一个结果与其对应,其计算的过程是这样的:

    • 1.扫描外查询的第一条记录
    • 2.扫描子查询,并将第一条记录的对应值传给子查询,由此计算出子查询的结果
    • 3.根据子查询的结果,返回外查询的结果。
    • 4.重复上述动作,开始扫描外查询的第二条记录,第三条记录,直至全部扫描完毕

    这里写图片描述

    select empno,
           ename,
           sal,
           (select avg(sal) from emp where deptno = e.deptno) avgsal
      from emp e
     where sal > (select avg(sal) from emp where deptno = e.deptno);
    
    

    这里写图片描述


    单行子查询和多行子查询

    单行子查询:插叙结果为一个
    多行查询:查询结果为两个或两个以上

    单行子查询可以使用单行操作符(也可以使用in啊)。
    多行子查询只能使用多行操作符。

    这里写图片描述

    单行操作符
    这里写图片描述


    多行子查询
    这里写图片描述

    多行操作符
    这里写图片描述


    单行子查询栗子

    这里写图片描述

    select * from emp e 
           where e.job = (select job from emp b where b.empno=7566)
                and 
                e.sal > (select sal from emp c where c.empno=7782)
    

    这里写图片描述


    这里写图片描述

    select deptno, min(sal)
      from emp
     group by deptno
    having min(sal) > (select min(sal) from emp b where b.deptno = 20)
    

    这里写图片描述


    非法使用单行操作符

    这里写图片描述


    多行子查询栗子

    多行操作符 in
    这里写图片描述

    这里写图片描述

    多行操作符 any

    这里写图片描述

    找出员工中,只要比部门号为30的员工中的任何一个员工的工资高的员工信息。也就是说只要比部门号为30的员工中的那个工资最少的员工的工资高就满足条件。

    any取的是集合的最小值。

    select *
      from emp
     where sal > any (select sal from emp b where b.deptno = 30);
    

    或者
    单行操作符表示

    select *
      from emp
     where sal > any (select sal from emp b where b.deptno = 30);
    

    这里写图片描述

    多行操作符 all

    这里写图片描述

    max取的是集合的最大值。

    
    select *
      from emp
     where sal >  all (select sal from emp b where b.deptno = 30);
    
    select *
      from emp
     where sal >  (select max(sal) from emp b where b.deptno = 30);
    

    这里写图片描述


    子查询中的空值问题 null

    单行子查询的null问题

    这里写图片描述


    多行子查询的null问题

    先看下emp的数据

    这里写图片描述

    in相当于 =ANY
    not in 相当于 <>ALL(其中如果子查询返回值有NULL,则<>NULL当然没有结果)

    ORACLE官方文档:
    这里写图片描述

    这里写图片描述

    select * from emp where empno not in (select mgr from emp where mgr is not null);
    

    这里写图片描述


    案例

    案例1

    这里写图片描述

    select  rn , empno, ename, sal
      from (select rownum rn, empno, ename, sal
              from (select empno, ename, sal from emp order by sal desc) t1
             where rownum <= 8)  t2
     where t2.rn >= 5;
    

    这里写图片描述


    案例2

    这里写图片描述

    最开始用的相关子查询做的,

    select empno,
           ename,
           sal,
           (select avg(sal) from emp where deptno = e.deptno) avgsal
      from emp e
     where sal > (select avg(sal) from emp where deptno = e.deptno);
    

    现在用多表查询的方式实现下

    select e.empno, e.ename, e.sal, s.deptno, s.avgsal
      from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) s
     where e.deptno = s.deptno
       and e.sal > s.avgsal
    

    在pl/sql中,选中sql,按F5查看执行计划
    这里写图片描述

    这里写图片描述

    可以看到 相关子查询的效果更好一些。


    案例3

    统计员工的入职年份

    使用函数方式

     select count(*) Total,
            sum(decode(to_char(hiredate, 'YYYY'), '1980', '1', '0')) "1980",
            sum(decode(to_char(hiredate, 'YYYY'), '1981', '1', '0')) "1981",
            sum(decode(to_char(hiredate, 'YYYY'), '1982', '1', '0')) "1982",
            sum(decode(to_char(hiredate, 'YYYY'), '1987', '1', '0')) "1987"
       from emp;
    

    使用子查询和dual伪表

     select (select count(*) from emp) Total,
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1980') "1980",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1981') "1981",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1982') "1982",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1987') "1987"
       from dual;
    
    

    这里写图片描述


    展开全文
  • Oracle rownun

    千次阅读 2008-12-06 13:17:00
    oracle的伪rownum小结2007-08-12 20:16本人最近在使用oracle的rownum实现分页显示的时候,对rownum做了进一步的分析和研究。现归纳如下,希望能给大家带来收获。 对于rownum来说它是oracle系统顺序分配为从查询...
    oracle的伪列rownum小结
    2007-08-12 20:16

    本人最近在使用oracle的rownum实现分页显示的时候,对rownum做了进一步的分析和研究。现归纳如下,希望能给大家带来收获。
            对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。
    举例说明:
    例如表:student(学生)表,表结构为:
    ID         char(6)      --学号
    name    VARCHAR2(10)   --姓名
    create table student (ID char(6), name VARCHAR2(100));
    insert into sale values('200001',‘张一’);
    insert into sale values('200002',‘王二’);
    insert into sale values('200003',‘李三’);
    insert into sale values('200004',‘赵四’);
    commit;
    (1) rownum 对于等于某值的查询条件
    如果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。
    SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
    SQL> select rownum,id,name from student where rownum=1;
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
               1 200001 张一
    SQL> select rownum,id,name from student where rownum =2;
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
    (2)rownum对于大于某值的查询条件
         如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录
    SQL> select rownum,id,name from student where rownum >2;
    ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
    那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。
    SQL>select * from(select rownum no ,id,name from student) where no>2;
              NO ID       NAME
    ---------- ------ ---------------------------------------------------
               3 200003 李三
               4 200004 赵四
    SQL> select * from(select rownum,id,name from student)where rownum>2;
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
    (3)rownum对于小于某值的查询条件
    如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。
    SQL> select rownum,id,name from student where rownum <3;
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
    1 200001 张一
              2 200002 王二
    综上几种情况,可能有时候需要查询rownum在某区间的数据,那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的,rownum对于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
    SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
              NO ID       NAME
    ---------- ------ ---------------------------------------------------
               2 200002 王二
               3 200003 李三
    (4)rownum和排序
    Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
    SQL> select rownum ,id,name from student order by name;
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
               3 200003 李三
               2 200002 王二
               1 200001 张一
               4 200004 赵四
    可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询
    SQL> select rownum ,id,name from (select * from student order by name);
          ROWNUM ID       NAME
    ---------- ------ ---------------------------------------------------
               1 200003 李三
               2 200002 王二
               3 200001 张一
               4 200004 赵四
    这样就成了按name排序,并且用rownum标出正确序号(有小到大)

    对于 Oracle 的 rownum 问题,很多资料都说不支持>,>=,=,between...and,只能用以上符号(<、<=、!=),并非说用>,>=,=,between..and 时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来,其实您只要理解好了这个 rownum 伪列的意义就不应该感到惊奇,同样是伪列,rownum 与 rowid 可有些不一样,下面以例子说明

    假设某个表 t1(c1) 有 20 条记录

    如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于号,查出来的结果很容易地与一般理解在概念上能达成一致,应该不会有任何疑问的。

    可如果用 select rownum,c1 from t1 where rownum > 10 (如果写下这样的查询语句,这时候在您的头脑中应该是想得到表中后面10条记录),你就会发现,显示出来的结果要让您失望了,也许您还会怀疑是不谁删了一些记录,然后查看记录数,仍然是 20 条啊?那问题是出在哪呢?

    先好好理解 rownum 的意义吧。因为ROWNUM是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。所以您没办法期望得到下面的结果集:

    11 aaaaaaaa
    12 bbbbbbb
    13 ccccccc
    .................

    rownum >10 没有记录,因为第一条不满足去掉的话,第二条的ROWNUM又成了1,所以永远没有满足条件的记录。或者可以这样理解:

    ROWNUM是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类推。如果你用>,>=,=,between...and这些条件,因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除,接着取下条,可是它的rownum还是1,又被删除,依次类推,便没有了数据。

    有了以上从不同方面建立起来的对 rownum 的概念,那我们可以来认识使用 rownum 的几种现像

    1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢?它与 select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢?
    因为是在查询到结果集后,显示完第 9 条记录后,之后的记录也都是 != 10,或者 >=10,所以只显示前面9条记录。也可以这样理解,rownum 为9后的记录的 rownum为10,因条件为 !=10,所以去掉,其后记录补上,rownum又是10,也去掉,如果下去也就只会显示前面9条记录了

    2. 为什么 rownum >1 时查不到一条记录,而 rownum >0 或 rownum >=1 却总显示所以的记录
    因为 rownum 是在查询到的结果集后加上去的,它总是从1开始

    3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果,而用 between 2 and 10 却得不到结果
    原因同上一样,因为 rownum 总是从 1 开始

    从上可以看出,任何时候想把 rownum = 1 这条记录抛弃是不对的,它在结果集中是不可或缺的,少了rownum=1 就像空中楼阁一般不能存在,所以你的 rownum 条件要包含到 1

    但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句,把 rownum 先生成,然后对他进行查询。
    select *
    from (selet rownum as rn,t1.* from a where ...)
    where rn >10

    一般代码中对结果集进行分页就是这么干的。

    另外:rowid 与 rownum 虽都被称为伪列,但它们的存在方式是不一样的,rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,在DB中唯一。只要记录没被搬动过,rowid是不变的。rowid 相对于表来说又像表中的一般列,所以以 rowid 为条件就不会有 rownum那些情况发生。
    另外还要注意:rownum不能以任何基表的名称作为前缀。

    展开全文
  • Oracle查询优化-01单表查询

    千次阅读 2017-01-31 22:39:57
    1.1 查询表中所有的行与1.2 从表中检索部分行1.3 查找空值1.4 将空值转换为实际值1.5 查找满足多个条件的行1.6 从表中检索部分1.7 为取有意义的名称1.8 在 WHERE 子句中引用取别名的1.9 拼接1.10 在 ...
  • Oracle查询优化-02给查询结果排序

    千次阅读 2017-02-02 23:27:49
    2.1以指定的次序返回查询结果2.2按多个字段排序2.3按子串排序2.4 TRANSLATE2.5 按数字和字母混合字符串中的字母排序2.6 处理排序空值2.7 根据条件取不同中的值来排序
  • ORACLE分级查询

    千次阅读 2016-11-27 14:08:50
    分级查询主要用于查询树形结构的记录。树形结构的数据存放在表中,数据之间的层次关系即父子关系,通过表中的间的关系来描述。
  • oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select * from tablename start with cond1 connect by c
  • oracle模糊查询

    千次阅读 2017-08-03 16:24:26
    oracle在Where子句中,可以对datetime、char、varchar字段类型的用Like子句配合通配符选取那些“很像...”的数据记录,以下是可使用的通配符: % 零或者多个字符 _ 单一任何字符(下划线) \ 特殊字符  ...
  • oracle查询练习

    千次阅读 2009-01-04 17:36:00
    oracle查询练习//巅峰科技(软件外包)-->初级oracle程序员//80%->入职使用scott/tiger用户下的emp表完成下列练习,表的结构说明如下emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal...
  • Oracle查询中获取已经根据条件排序好的数据(比如我要查询员工表,年龄根据从大往下排的顺序,然后再从中获取第几条数据到第几条的数据),思路其实已经很明显; 第一步:根据条件排序好; 例如: select * ...
  • Oracle的子查询 何为子查询:子查询时嵌入在其他SQL语句中的SELECT语句,也称嵌套查询 举例:查询和SMITH是同一个职位的员工 //这是在where子句中使用子查询 Select * From emp Where job = (select job ...
  • oracle 交换,移动位置

    千次阅读 2011-10-26 00:21:53
    要求:  移动一个分区表中的两个位置: desc tbln_testA;  BRCD NOT NULL CHAR(4)  DSBSID ...
  • oracle查询sql语句

    万次阅读 2018-07-08 00:01:48
    Oracle查询语句 select*from scott.emp ;1.--dense_rank()分析函数(查找每个部门工资最高前三名员工信息)select*from(selectdeptno,ename,sal,dense_rank()over(partitionby deptno orderby sal desc) a from...
  • Oracle 查询技巧与优化(一) 单表查询与排序

    千次阅读 多人点赞 2016-08-02 09:22:12
    关于Oracle单表查询与排序相关的技巧与优化~
  • oracle查询语句大全 oracle 基本命令大全 Oracle命令(一):Oracle登录命令 1、运行SQLPLUS工具  C:\Users\wd-pc>sqlplus 2、直接进入SQLPLUS命令提示符  C:\Users\wd-pc>sqlplus /nolog 3、以OS身份...
  • oracle rowid和rownum

    千次阅读 2011-09-28 18:51:27
    转载:... 整理ROWID一 一,什么是伪RowID? 1,首先是一种数据类型,唯一标识一条记录物理位置的一个id,基于64位编码的18个字符显示。 2,未存储在表中,可以从表中查询,但不支持插入,更新,删除
  • Oracle之rowmun和rowid

    千次阅读 2017-08-02 21:57:13
    rownum和rowid的共同点是什么? 都是伪劣 ... 从Rowid定义可知,只有数据行的物理位置改变才会导致rowid改变,所以我们只需要关心那些操作会产生数据的物理位置的改变即可。 1)exp&imp(导出原数据
  • Oracle查询慢的原因

    千次阅读 2010-11-25 17:36:00
    Oracle查询慢的原因
  • Oracle查询语句及运用语句整理

    万次阅读 2019-07-29 00:05:56
    基于很久没用到Oracle了最近有点怀念,看了很多博客至此没有看到满意的故此整理,当然这是基础的另外迪卡尔集及函数会后期码上,初学Oracle数据库的人都会经常在一些书籍中或者视频课程中提到几个常用的数据库表格,...
  • oracle 查询执行顺序

    万次阅读 2011-09-22 21:27:40
    oracle 语句提高查询效率的方法 1:.. where column in(select * from ... where ...); 2:... where exists (select 'X' from ...where ...); 第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有...
  • Oracle查询树形结构

    千次阅读 2013-08-08 10:17:36
    Oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的, 其基本语法是: ------------------------------------------------------------------------------...
  • 关于Oracle查询优化的相关字符串操作以及常用的字符串函数~
  • oracle 分组查询查询

    千次阅读 2017-10-27 12:02:44
    分组查询 例: 统计平均工资大于2000的部门信息 -- 1.确定要查询的字段及表 select e.sal,d.deptno, d.dname, d.loc from emp e,dept d where e.deptno= d.deptno -- 2.统计平均工资及部门信息 select ...
  • Oracle修改字段位置

    千次阅读 2020-04-23 15:12:11
    1.查询出该表的 object_id select object_id from all_objects where owner = 'SCOTT' and object_name = 'TESTS'; 查询结果为:74101 2.根据第一步查出的ID,查询表字段顺序的存储 select obj#, c...
  • oracle各种查询语句

    千次阅读 2016-09-08 11:19:19
    1. Oracle安装完成后的初始口令?   internal/oracle   sys/change_on_install   system/manager   scott/tiger   sysman/oem_temp 2. ORACLE9IAS WEB CACHE的初始默认用户和密码?  ...
  • Oracle中ROWNUM伪和ROWID伪的用法与区别

    万次阅读 多人点赞 2016-08-30 17:05:45
    做过Oracle分页的人都知道由于Oracle中没有像MySql中limit函数以及SQLServer中的top关键字等,所以只能通过...ROWNUM伪Oracle首先进行查询获取到结果集之后在加上去的一个伪,这个伪对符合条件的结果添加一个从
  • ORACLE查询树型关系

    千次阅读 2009-12-06 09:53:00
    oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select * from tablename start with cond1 connect by cond2 where cond3;...
  • Oracle 层次查询、递归

    千次阅读 2018-08-14 11:39:29
    Oracle 层次查询、递归 语法: select ... from tablename start with 条件1  connect by 条件2  where 条件3;     1. 树结构的描述  树结构的数据存放在表中,数据之间的层次关系即父子关系,通过表中...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 73,717
精华内容 29,486
关键字:

oracle查询列位置