ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 具有oracle 的sql特色整理 第一季

具有oracle 的sql特色整理 第一季

原创 Linux操作系统 作者:幕南风 时间:2012-06-09 22:00:18 0 删除 编辑

我们知道每个RDBMS在sql方面都会存在自己的特色。那么今天我们来看看oracle有啥特色值得我们来在

意。

特色1 :
Oracle分析函数与开窗函数:
语法:

    FUNCTION_NAME  (,...)
    OVER           ()
例如:
   sum(sal) over (partition by deptno order by ename rows between。。。)
   其中,sum是函数名,
         Over()是关键字,直接点就是给分析函数加条件,用于识别sum()是聚合函数还是分析函数
说明:
(1)通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须

跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦

(2)聚合函数用group by分组,每个分组返回一个统计值;分析函数用partition by分组,每组每行都

可以返回一个统计值。

(3)分析函数带有一个开窗函数over(),含三个分析字句:
           分组(partition by)排序(order by)窗口(rows)

(4)两个order by的区别:

        分析函数是在整个sql查询后(sql语句的执行比较特殊)再进行的操作,也就是说,sql语句的

order by也会影响分析函数的执行结果。

            A) 如果sql语句中的order by满足分析函数分析时要求的排序,那么sql语句的排序将先

执行,分析函数在分析时就 不必再排序了。

            B) 如果sql语句中的order by不满足分析函数分析时要求的排序,那么sql语句中的排序

将先执行。

(5)窗口就是分析函数分析时要处理的数据范围:

           第一行是:unbounded preceding

           当前行是:current row

           最后一行是:unbounded following

      窗口字句不能单独出现,必须有order by子句时才能出现。而出现order by子句时,不一定要有

窗口子句,此时的窗口缺省是第一行到最后一行;

           当省略窗口子句时:

           A)如果存在order by,则缺省的窗口是unbounded preceding
And current row;

           B)如果同时省略order by,则缺省的窗口是unbounded preceding and unbounded

following

例子:

1 统计每个部门工资最高的哪位?

select * from                                                                      

   (                                                                           

    select ename,sal,deptno,rank()over(partition by deptno order by sal desc) mm from emp

   )                                                                           

where mm=1

 

注意:
      1).在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,    

row_number()只返回一个结果         
      2).rank()和dense_rank()的区别是:
     --rank()是跳跃排序,有两个第二名时接下来就是第四名
     --dense_rank()l是连续排序,有两个第二名时仍然跟着第三名

 
2  显示各部门员工的工资,并附带该部门的最高工资。

select deptno,empno,ename,sal,last_value(sal)over(partition by deptno order by sal rows

between unbounded preceding and unbounded following)

max_sal from emp;

 
特色2:
灵活使用decode()函数:

语法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的

结果返回then1,...,如果不等于任何一个if值,则返回else。可以用函数或表达式来替代value,if,

then,else从而作出一些更有用的比较。

来看看具体的运用:
  1 假设我们想给百度职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加

15%
则:

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee

  2 表table_subject,有subject_name列。要求按照:语、数、外的顺序进行排序
则:

select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)


特色3:
oracle  update的特色:
    
来看个问题先:

       有一表a,列有id 和count;现在有表b,列也是id count,怎么用把表b中id相对应的count 更

新到表a中呢?

       不严格的解法如下:

       Update  a

       Set  count=count+nvl((select count from b where id=a.id),0)

如果a跟b表一对多的话,会有问题,因为,子查询跟运算符比如等于号连用,只能是单值。以后,子查询

和运算符联用需要特别在意这个问题:看看子查询返回的值的个数!!!

       遇到需要从另外一个表来更新本表的值的问题的时候,oracle有两种解决的办法:

       其一,使用子查询,使用子查询时一定要注意where条件(一般后面接exists子句),除非两个表

是一对一关系,否则where条件必不可少,遗漏掉where条件时可能会导致插入大量空值(如果不写where

子句,oracle将会默认的把所有的值全部更新,即使你这里使用了子查询并且某值并不能在子查询里找到

,你就会想当然的以为,oracle或许将会跳过这些值吧,你错了,oracle将会把该行的值更新为空)

update a

set a.count = a.count+(select nvl(sum(count),0) from b where b.id = a.id)

where exists (select 1 from b where b.id = a.id)

       其二,类视图的更新方法,这也是oracle所独有的。先把对应的数据全部抽取出来,然后更新表

一样更新数据,这里需要注意的是,必须保证表的数据唯一性(设主键来实现)

update (select a.count acount,b.count bcount from a,b where a.id=b.id)

set acount=acount+bcount


 

特色4:
oracle  ROWNUM浅谈

 (1) rownum是伪劣,会根据返回记录自动生成一个序列化的数字。

(2)作用:可以做一些原先难以实现的结果输出

常见的操作如下:

(3)TOP  N结果输出:

          Select  *  from  emp  where  rownum < 5

          [对排序结果去top  n  时要注意陷阱]

(4)分页查询:

          利用rownum对结果进行分页,下面返回结果的第6到10条记录:

           select * from (select e.*,rownum as rn from emp e where rownum<=10)b
           where b.rn>5;

(5)利用rownum作分组子排序

     如果我们希望在分组后对组中的成员的再进行编号,则:

       select decode(ROWNUM-min_sno,0,a.job,NULL)job,decode(ROWNUM-min_sno,0,1,rownum+1-   

                     min_sno) sno,a.ename
       from  (select * from emp order by job,ename) a,
             (select job,min(rownum) min_sno from  (select * from  emp order by            

              job,ename) group by job)              b
       where a.job=b.job

(6)确认某个表是否含数据:
       Select * from emp where rownum=1;

  常见的陷阱如下:由于rownum是伪劣,只有有结果记录时,rownum才有相应的值。

(7)对rownum使用>(大于1的值),>=(大于或等于1的值),=(大于1的值),这样子没有结果输出;

    因为:

A :rownum是伪列,必须要有返回结果后,每条返回记录就会对应产生一个rownum数值;

B :返回结果记录的rownum是从1开始排序的,因此第一条始终是1;

    这样当查询到第一条记录时,该记录的rownum为1,但条件要求rownum>1,因此不符合,继续查询下

一条;因为前面没有符合要求的记录,因此下一条记录过来后,其rownum还是为1,如此循环就不会有结

果。

    但可以通过实例化来实现对>,>=,=的使用:
  例如:

Select deptno,ename
From (select deptno,ename ,rownum as r From emp)
Where r>5

(8)rownum 和 order by

在使用rownum时,只有当order by 的字段是主键时,查询结果才会先排序再计算rownum
我们来做个测试:
环境:scott方案下的emp表,先把empno设置为主键:
alter table emp add constraint emp_pk_empno primary key (empno);
然后,
当order by的字段是主键时:
[code=SQL][/code]
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by empno;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  1 7369 SMITH
  2 7499 ALLEN
  3 7521 WARD
  4 7566 JONES
  5 7654 MARTIN
查询结果集先排序再计算rownum。情况很好很正常。
我们再来看一下,当order by 字段不是主键,会怎样呢?
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by ename;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  2 7499 ALLEN
  4 7566 JONES
  5 7654 MARTIN
  1 7369 SMITH
  3 7521 WARD
对ename排序,结果瞬间就凌乱了哈。

现在,我们来分析一下原因:
oracle会先按物理rowid顺序取出满足rownum条件的记录,即:物理位置上的前五条记录。接下来将有两

种情况:
其一,order by字段为非主键,则只对进行order by排序。
其二,order by字段为主键,则先将结果集进行order by排序,然后计算rownum。

但在工业环境中,不可能一直对主键进行排序,那么,我们可以通过实例化来进行非主键的排序。
[code=SQL][/code]

SQL> select empno,ename from 
  2 (select empno,ename from c order by ename)
  3 where rownum<=5;
 
EMPNO ENAME
----- ----------
 7876 ADAMS
 7499 ALLEN
 7698 BLAKE
 7782 CLARK
 7902 FORD

特色5:
oracle 常用函数rollup()和cube(),grouping()的使用:

        1  rollup()只作用于第一列:将第一列分成几个小组,先对各小组小计,再对全部小组总计

。而cube()先对第一列小计,再对第二列小计,。。。,每一列的处理和rollup一致,最后对全部总

计。所以,要有两个rollup语句才能顶上一个cube()。

       例如:

       SQL> select deptno,job,sum(sal) from c group by rollup(deptno,job);

+

       SQL> select deptno,job,sum(sal) from c group by rollup(job,deptno);

=

       SQL> select deptno,job,sum(sal) from c group by cube(deptno,job);

       2  GROUPING函数可以接受一列,返回0或者1。如果列值为空,那么GROUPING()返回1;如果列值

非空,那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个

值时,GROUPING()就非常有用。可以通过case…when..then..else来增加可读性。

       SQL> select case grouping(deptno)

            when 0 then '部门'

            when 1 then '部门汇总'

            end , sum(sal)

            from c group by rollup(deptno);

 

下面是几个比较有用的oracle sql:

1 oracle计算时间差

      Ceil(n):取比n大的最小整数;

      To_date() :时间格式可按需调整,’yyyy-mm-dd hh24:mi:ss’

       mm与mi区别:因为sql不区分大小写;

       To_date():两两相减后是天数

  毫秒级

select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30  23:59:59' , 'yyyy-    mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 

from   dual

         秒级,分钟级,时级,天级 只要调整24*60*60*1000便可。

2  阿拉伯的英汉对照

select to_char(to_date(n,'yyyy'),'year') from dual;

   输入:n=2

   输出:two

3  返回标量值可用dual表测试:

比如:

Select power(3,2) from dual

select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

【sign(n):取数字n的符号,大于0返回1,小于0返回-1,等于0返回0】

4   获得一个列的所有行的乘积:

Select power(10, Sum(Log(10, columnName))) From t

5   查询指定记录:

例如:

      显示第5到第10记录

select a.* from  (select rownum num,e.* from emp e) a 

where a.num >= 5 and a.num <= 10

6   查询当前用户某个表中创建了哪些索引:

select index_name from user_indexes where table_name='表名';

    查询当前用户的所有表:

select table_name from user_tables;

7   去掉字母保留数字:

   select regexp_replace(v,'[[:alpha:]]','') from b;

8   查看系统参数表:

       Select * from nls_session_parameters;

       若想对其修改:

       Alter session set ………….

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26515977/viewspace-732364/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
腾讯数据库工程师

注册时间:2012-05-10

  • 博文量
    544
  • 访问量
    1553110