oracle中常用函数学习

oracle中常用函数学习

1 nvl函数

   NVL函数的功能是实现空值的转换，根据第一个表达式的值是否为空值来返回响应的列名或表达式，主要用于对数据列上的空值进行处理，语法格式如：NVL( string1, replace_with)。
   NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以就有了NVL2函数。

拓展：NVL2函数:Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。
有时候在需求上的基础上,比如,没有值可以返回一个默认值0之类等

2 Union与Union all 用法区别

   UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意: UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
使用: 有时候需求上展示部门下所有人员的姓名和金额等,虽然说业务表中无数据,但是可以通过左关联+union进行查出所有的数据,当时没有金额的或为空的可以通过nvl(x1,0)转换成默认值为0

3 substr 函数：截取字符串

语法：SUBSTR(string，start， [length])
string：表示源字符串，即要截取的字符串。
start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。
length：可选项，表示截取字符串长度。
SELECT SUBSTR(‘Hello SQL!’, 1) FROM dual --截取所有字符串，返回’Hello SQL!’
SELECT SUBSTR(‘Hello SQL!’, 2) FROM dual --从第2个字符开始，截取到末尾。返回’ello SQL!’
SELECT SUBSTR(‘Hello SQL!’, -4) FROM dual --从倒数第4个字符开始，截取到末尾。返回’SQL!’
SELECT SUBSTR(‘Hello SQL!’, 3, 6) FROM dual --从第3个字符开始，截取6个字符。返回’llo SQ’
SELECT SUBSTR(‘Hello SQL!’, -4, 3) FROM dual --从倒数第4个字符开始，截取3个字符。返回’SQL’

4 instr 函数：返回子字符串在源字符串中的位置

语法：INSTR(string，child_string，[start]，[show_time])
string：表示源字符串。
child_string：子字符串，即要查找的字符串。
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错。

substr 和 instr两个函数可以结合使用满足需求

5 sql中 || 的作用

RTRIM (table1.string)
          || ' '
          || (SELECT stringNM
                FROM table2
               WHERE table2.string = table1.string)

其中的 || 是什么意思呢？
该SQL语句中||的作用就是字符串的连接符。
比如’111’||‘222’ 其结果就是’111222’

6 row_number() over(partition by 列名1 order by 列名2 desc)的使用

   表示根据 列名1 分组，然后在分组内部根据  列名2 排序，而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

—查询所有姓名，如果同名，则按年龄降序
SELECT name,age,detail,ROW_NUMBER() OVER(PARTITION BY name ORDER BY age DESC) FROM TEST_Y;

–通过上面的语句可知，是按照name字段分组，按age字段排序的。 如果只需查询出不重复的姓名即可，则可使用如下的语句, 由查询结果可知，姓名相同年龄小的数据被过滤掉了；
SELECT * FROM ( SELECT name ,age, detail ,ROW_NUMBER() OVER ( PARTITION BY name ORDER BY age DESC )RN FROM TEST_Y ) WHERE RN= 1 ;

–分页
–先做一个子查询,先按id1进行排序，排序完后，给每条记录进行了编号
–然后再将子查询做为一张表,就可以进行分页了
select *
from (select t.*,row_number() over(order by t.id1 asc) as rn from demo t) d
where d.rn between 1 and 2

7 round函数

Round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。除数值外，也可对日期进行舍入运算。
round函数的语法是：ROUND(number,num_digits)，即：Round（数值，保留的小数位数)
Number：需要进行四舍五入的数字。
Num_digits：指定的位数，按此位数进行四舍五入。
其中，如果 num_digits 大于 0，则四舍五入到指定的小数位。
如果 num_digits 等于 0，则四舍五入到最接近的整数。
如果 num_digits 小于 0，则在小数点左侧进行四舍五入。
=ROUND(3.19, 1) 将 3.19 四舍五入到一个小数位 (3.2)
=ROUND(2.649, 1) 将 2.649 四舍五入到一个小数位 (2.6)
=ROUND(-5.574, 2) 将 -5.574 四舍五入到两小数位 (-5.57)
=ROUND(18.8, -1) 将 18.8 四舍五入到小数点左侧一位 (20)。这个参数-1表示取整到十位数。