Oracle进阶sql语句

一. 系统函数

  dual:Oracle 数据库提供的一个临时的系统表

  1. 字符类函数:主要用来操作字符串

  (1)ascii():将字符转换成ASCII码值

    例子:select ascii('A') from dual

  (2)chr():将ASCII码值转换成字符

    例子:select chr(65) from dual

  (3)concat(s1,s2):字符串拼接

    例子:select concat('hello','world') from dual

  (4)initcap():将字符串首字母变成大写,其他字母变成小写

    例子:select initcap('hello WORld')

  (5)instr(s,s1,[i,j]):返回字符串s1在字符串s中第j次出现的位置,i代表从哪个位置开始查找。

             i不设置代表从最开始查找,j不设置代表第一次出现的位置

  (6)length(s):返回字符串的长度

  (7)lower(s):将字符串全部转换成小写

  (8)upper(s):将字符串全部转换成大写

  (9)trim(s from s1):去除字符串s1两边的字符s

    例子:select trim(' ' from '    sysys    ') from dual

    (9.1)ltrim():去除字符串s1左边的字符s

      例子:select trim(' ' from '    sysys    ') from dual

    (9.1)rtrim():去除字符串s1右边的字符s

  (10)replace(s1,s2,s3):将字符串s1中的字符s2替换成s3,如果s3省略,就代表去除s1中的s2

    例子:select ('hello world','l','a') from dual

       select ('hello world','l') from dual

  (11)substr(s,i[,j]):截取字符串,j省略时,截取字符从i到结尾的字符串,j不省略时,截取字符从i开始长度为j的字符串

  2. 算数类函数:

  (1)round(n,m):m不写时四舍五入到整数,m写时表示保留的小数位数

  (2)trunc(n,m):直接截取,不会四舍五入

  (3)ceil(n):向上取整,返回比n大的最小整数

  (4)floor(n):向下取整,返回比n小的最大整数

  (5)sqrt(n):返回n的平方根,只返回正数

  (6)mod(n,m):取n除m的余数

  (7)power(x,y):计算x的y次幂

  3. 时间类函数:

  (1)sysdate:返回当前的系统时间

  (2)last_day(d):返回指定时间当月的最后一天

  (3)add_months(d,n):在指定时间的月份增加n个月

   注意:如果要加多少天的话直接使用+/-运算符进行操作

  (4)months_between(d1,d2):返回两个时间之间相差的月份

   注意:如果要计算相差多少天的话直接使用-运算符进行操作

  (5)trunc(d,['mm','q','day','iw','ww']):mm,将时间定位到d的月份的第一天,

                   q,将时间定位到d当前季度的第一天,

                   day,定位到d当周的第一天(第一天为周日)

                   iw,定位到d当周周一

                   ww,定位到d上周周六

  (6)extract(year/month/day from d):截取时间d的年份,月份,天

    注意:如果需要截取时、分、秒,则需要将时间转换成时间戳再截取

    语法:extract(hour/minute/second from cast(d as timestamp))

  4. 转换类函数:

  (1)to_date(s,'格式'):将一个时间字符串转换成时间对象

  (2)to_char(d,[格式']'):将一个时间对象或数据转换成字符串

    注意:时间转换成字符串时如果需要自定义格式则要将字符串用" "包起来

    例子:select to_char(sysdate,' yyyy"年"mm"月"dd"日" ') from dual

  (3)to_timestamp(s,'格式'):将一个时间字符串转换成时间戳对象

  (4)cast(数据 as 类型):将数据转换成指定类型

  5. 分支类函数:

  (1)case...when:

    简单函数:

    语法:case 字段 when 值 then 值

             when 值 then 值

             ...

             else 值 end

    搜索函数:

    语法:case when 条件1 then 语句1

           when 条件2 then 语句2

           ...

           else 语句n

           end

  (2)nvl(字段值,值1):如果字段值为null则返回值1,否则返回字段的值

      nvl2(字段值,值1,值2):如果字段值不为空则返回值1,否则返回值2

  (3)decode(字段,值1,值2,值3):如果字段的值等于值1则返回值2,否则返回值3

      decode(字段,值1,语句1,值2,语句2,值3,语句3):如果字段的值等于值1,则返回语句1;如果字段的值等与值2,则语句2;否则返回语句3,执行过程与case的简单函数类似

二. 表集合操作

  1. 并集操作:union [all]

   将两个查询结果合并在一起

   语法:select 语句 union [all] select 语句

   注意:合并的表的列数必须一样且对应的数据类型也要一致,列名不要求相同

  2. 交集操作:intersect

   显示两张表结果中重复的数据

   语法:select 语句 intersect select 语句

  3.差集操作:minus

   显示第一张表去除与第二张表重复的数据后的结果

   语法:select 语句 minus select 语句

三. 开窗函数

  语法:函数名() over(partition by 分组字段 order by 排序字段 [desc|asc] [定位框架]) 

  1.函数名支持的函数有:

    (1)聚合函数

      row_number():按照连续的数字进行排序,不关注相关名次

      rank():不会进行连续排名,如果遇到相同的名称则会跳过后续的名次,可以保证总名次等于总人数

      dense_rank():会进行连续排序,如果遇到相同的值会显示相同的名次,但是不能保证总名次等于总人数

    (2)移动函数

      lag(字段,n):将指定的字段的值下移n行

      lead(字段,n):将指定的字段的值上移n行

  2. over():窗体函数,主要就是将表中的数据进行分组排序

    group by 分组之后会将原始数据按照指定的分组进行汇总统计成一行数据,并不会显示每一组的详细信息

    但是使用over分组之后,只是在结束后面新增几列来显示汇总后的数据不会影响原来的数据

    partition by:开窗函数中的分组

    order by:进行排序,如果要对某一列的值进行累加则需要对它进行排序

  3. 开窗函数的定位框架

    在使用了order by的时候可以设置定位框架

    语法:order by 排序字段 [range|rows] between 边界规则1 and 边界规则2

    边界规则:unbounded preceding:向上一直到第一行

         n preceding:向上n行

         unbounded following:向下一直到最后一行

         n following:向下n行

         current row :当前行

    注意:如果不设置边界定位框架,则默认从第一行到当前行,并且按照range进行累计操作

       range是按照值的范围进行累加,rows按照行进行累加

    

四. 行列转换

  1. listagg():将指定的列中的数据拼接成一行数据

  注意:必须要配置group by分组使用

  语法:listagg(字段,'分隔符') within group (order by 排序字段)

  行转列:
    sname  course  score
    小明   语文   78
    小明   数学   90
    小白   英语   90
    小红   语文   88

  行专列之后:
    sname  语文  数学  英语
    小明   78   90   0
    小白   0     0     90
    小红   88   0     0

  2. pivot():行转列

  语法:select 列名,列名,列名 from 表名 pivot(sum(需要计算的列名) for 需要转换的列 in('值1' [as] 列名1,'值2' [as] 列名2,'值3' [as] 列名3))

  行转列:

   (1)使用 case when 进行行转列  

    select sname,sum(case when course='语文' then score else 0 end) 语文,
          sum(case when course='数学' then score else 0 end) 数学,
          sum(case when course='英语' then score else 0 end) 英语 from stu_cou_t group by sname;

   (2)使用 decode 进行行转列 

    select sname,sum(decode(course,'语文' ,score,0)) 语文,
          sum(decode(course,'数学' ,score,0)) 数学,
          sum(decode(course,'英语' ,score,0)) 英语 from stu_cou_t group by sname;

   (3)使用 pivot() 函数进行行转列

    select sno,course,score from stu_cou_t pivot(sum(score) for course in('语文' 语文,'数学' 数学,'英语' 英语))

  3.unpivot():列转行

   (1)使用union

    select * from (
      select sname,'语文' course, 语文 score from stu_cou_t1
      union
      select sname,'数学' course, 数学 score from stu_cou_t1
      union
      select sname,'英语' course, 英语 score from stu_cou_t1) a where a.score!=0;

   (2)使用 unpivot() 函数

     语法:select * from 表名 unpivot(自定义列名 from 自定义列名 in('列名','列名','列名'))

    select * from stu_cou_t1 unpivot(score for course in(语文,数学,英语))

五. 分页查询

  rownum:用来显示表中的数据的行号,通常用来进行分页查询数据

  分页查询的语法:

  select a.* from (select b.*,rownum rn from 表名 b [order by ]) a where a.rn between 开始位置 and 结束位置

六. 给表中的数据去重

  rowid:伪列类型,代表表中每一行数据的物理存储地址,该值是唯一的,在添加一条数据的时候系统就会生成一个rowid保存起来

    rownum 与 rowid 的区别

    rownum 是根据数据库的列而显示的行号,rowid 是数据存储的物理地址,是在添加数据的时候生成的,是唯一的

  将表中重复的数据删除

  delete from 表名 where rowid not in(select min(rowid) from 表名 group by 列名[,列名,列名])

posted @ 2022-06-06 16:35  Luo_YB  阅读(305)  评论(0编辑  收藏  举报