Oracle函数

函数： y=f（x） x叫参数 y叫返回值

输入x 经过一系列计算 返回结果 y=x*x+2

to_date to_date(19810601,'yyyymmdd')两个参数

Insert Into date_test Select to_date ('2018/01/01','yyyy/mm/dd') From dual

1.--nvl( ) 括号里两个参数 把空值转换为某个值

空值 NULL is null

查询出king的年工资（sal+comm）

Select (sal+nvl(comm,0))*12 From emp Where ename = 'KING'

king comm是空 空与任何数字进行计算都为空

nvl(comm,0)

--计算每个员工的年收入

Select ename, (sal+ nvl(comm,0))*12 As 年收入 from emp

列的别名 As **

nvl2（）

nvl2(value1,value2,value3)

如果value1的值为null 函数返回value3，否则函数返回value2，也就是说函数永远不会返回value1

注意的是参数value2 value3可以是除了LONG类型之外的任意数据类型。

----Coalesce（a,b,c....g）、遇到非null值停止并返回该值，如果所有都为null则返回null。

Select coalesce(Null,Null,1) From dual; ----返回1

2. 字符函数

一、截取函数

Substr()  截取字符串里面的字符 substr(a,b,c)

 a 传入的字符串/字段

 b 开始截取的位置

 c 截取的长度

Select Substr('abc',1,2) From dual ---- ab

--找出EMP表中 第一个字母是A的员工信息

Select * From emp Where Substr(ename,1,1) = 'A'

Instr() 返回  想查的字符在字符串中的位置

Select Instr('pingtouge','g',2,2) From dual ---返回 8

--从第2号（i）位置开始查找第二次出现g的位置

length（）用于获取对应字段的字符长度

lengthb（）用于获取对应字段的字节长度

Select Length(ename) From emp;

lpad() , rpad() 左填充，右填充函数

例如：将部门名称左填充为10位

Select Lpad(dname,10,' ') From dept;

trim，ltrim，rtrim函数用法

1.去空格

select trim(' dd df ') from dual; --去除指定字符的前后空格

select ltrim(' dd df ') from dual; --去除指定字符的前空格

select rtrim(' dd df ') from dual; --去除指定字符的后空格

2.去除指定字符，trim只能去除单个字符，ltrim、rtrim可以去除多个字符

SELECT TRIM(leading|trailing|both 字符串1 FROM 字符串2) FROM dual;

--表示字符串1去除 前/后前后面 的字符串2

SELECT trim(both '1' from '1213sfd111') FROM dual;

ltrim、rtrim

由于从右边算起，第一个字母是b没有与'main'匹配的字符，因此返回结果仍是'aaaaminb'

SELECT rtrim('aaaaminb','main') FROM dual;

SELECT ltrim('ccbcminb','cb') FROM dual; ---返回minb

二、替换函数

replace() 作用是替换作用 整体替换

Select  Replace ('abc','a',1)  From dual ---返回1bc

Select  Replace ('abc','abc',1)  From dual ---返回1

Select  Replace ('abc','abc',123)  From dual--返回123

Select Replace ('abc', 'bac' , 123) From dual--返回abc

--整体对应a-1，abc-1，abc-123，对应abc对应不上bac

Translate()  作用也是替换  一对一替换

Select Translate('abc','acb',123) From dual ---返回132

--一一对应关系，a-1，c-2，b-3

Select Translate('abc','ab',3) From dual

--这个是ab-3

Select Translate('abc','abc','') From dual

2.数字函数

round（）把数值字段舍入为指定的小数位数，四舍五入

例：Select Round(123.163,1) From dual --返回 123.2

trunc（a,b）类似截取函数，按指定的格式截取输入的数据

截取日期：select trunc(sysdate) from dual;--2017/2/13,返回当前时间

select trunc(sysdate,'mm') from dual;--2017/2/1,返回当月的第一天

截取数字：截取时并不对数据进行四舍五入

select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数

后面的截去。

select trunc(123.567,-2) from dual;--100,第二个参数可以为负数， 表示将小数点左边指定位数后面的部分截去，即均以0记;

 select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

ceil(n) ceiling（天花板）取大于等于数值n的最小整数；

select ceil(1.5) a from dual; 返回2

floor(n)取小于等于数值n的最大整数；

select floor(1.5) a from dual; 返回1

3，日期函数

to_date( )

add_months() 有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)

例： select add_months(sysdate,3) from dual; ---3个月以后的时间

用to_date修改月份

select add_months(to_date('2012-12-12','yyyy-mm-dd'),2) from dual;

months_between() 用于计算date1和date2之间有几个月

例：Select floor(months_between(Sysdate,to_date('2012-12-12','yyyy-mm-dd'))) From dual;

next_day( ) 函数

格式：next_day(date, char)

指定日期date之后下一个周char指定的日期，char可以可以表示为星期或者天数，星期从星期日开始算。

select next_day(sysdate,'星期一') from dual;

Select next_day(Sysdate,1) From dual; --1代表周日，2代表周一

4，多行函数，分组、Group By

Select deptno, Max(sal) from emp Group By deptno

跟聚合函数联用 5个聚合函数 sum(), Min(),Max（）， Avg(), Count()

Group By 后面是什么字段 前面就只能跟什么字段不能是别的字段

那前面能写什么呢？只有分组字段和聚合函数

小点 ：To _Char (hiredate,'yyyy') 把非字符类型转化成字符类型

Select To_Char(hiredate,'yyyy'), Count(*) from emp group by

To_Char(hiredate,'yyyy');

sum（），avg（），max（），min（），count（）

1.Count() 参数可以是常量，可以是字段 求表中数据条数

Select Count(*),Count(1),Count(empno),Count(comm) From emp

因为空值在函数中 不参与比较和计算

count（1）--14，count（comm）---4

count(*) 和count(1) 谁快 因为很多人认为1代表第一个字段 XXX--错误的

原理是一样的，都是常量，所以他俩的效率是一样的

2.分组前过滤 group by

--求出在1981年到1982年每个岗位人的平均工资

Select job ,avg(sal) from emp where hiredate >= to_date (19810101,'yyyymmdd')

and hiredate > to_date (19820101,'yyyymmdd') group by job

3、--对group by 后的结果进行过滤用 having

--求每个部门平均工资 高于1600的部门

distinct和group by去重

select distinct name, id from A

distinct语句中select显示的字段只能是distinct指定的字段，其他字段是不可能出现的,distinct必须放在开头

select name,id from A group by name,id;

--- order by

Asc 升序 从小到大 Desc 降序 从大到小

Select * From emp Order By 8 desc

Select deptno,Avg(sal) from emp group by deptno Having Avg(sal) > 1600

Case When 判断字段 条件判断语句

Select sal,Case When sal <=800 Then '贫穷'

When sal >800 And sal < 1500 Then '温饱'

When sal >=1500 And sal <=4000 Then '小康'

Else '富裕'

End

From emp

Decode() 和 Case When 可以等价改写，只有在等于号的情况下可以改写

Case When sal = 2000 Then '小康'

Decode(a,a1,b1,a2,b2,a3,b3,d)

等价于 Case When a = a1 Then b1

When a = a2 Then b2

When a = b3 Then b3

Else d

End

5，分析函数、开窗函数

开窗函数

--开窗函数的调用格式为：函数名(列) OVER(选项)

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。它用于为行定一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

1、聚合开窗函数

==》聚合函数(列) OVER (选项)，这里的选项可以是 PARTITION By 子句，但不可是 ORDER By 子句

Select ename,empno,sal,deptno,

Count(ename) Over (Partition By deptno) As em,

Count(ename) Over (Partition By sal) As sa

From emp;

--与GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，

--创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

2、排序开窗函数

==》排序函数(列) OVER(选项)，这里的选项可以是 ORDER By 子句，也可以是OVER（PARTITION By 子句 ORDER By 子句），但不可以是 PARTITION By 子句。

--支持的开窗函数分别为：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）

和NTILE（分组排名）。

--普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

一、排序编号的分析函数

1.row_number() OVER()

每一组里面编号，不管重复的

select Row_number() Over(Order By sal Desc) As Rn , a.* From emp a;

Select Row_number() Over(Partition by deptno Order By sal Desc)

As Rn , a.* From emp a;

2.rank() OVER()

每一组里面编号，重复的同号。重复的后面会跳号

例：三个100分并列第一名，所有99分则变成并列第四名

Select Rank() Over(Order By sal Desc) As Rn, a.* From Emp a；

Select Rank() Over(Partition By deptno Order By sal Desc) As Rn,

a.* From Emp a

3.dense_rank() OVER()

每一组里面编号，重复的同号，重复的后面不会跳号

例：三个100分并列第一名，所有99分则是并列第二名

Select dense_Rank() Over(Order By sal Desc) As Rn, a.* From Emp a

Select dense_Rank() Over(Partition By deptno Order By sal Desc) As Rn,

a.* From Emp a

二、统计分析函数

SUM()、

sum(sal) OVER(Partition by deptno)

MAX()、MIN()、AVG()、COUNT()

按部门分组，求各部门内部工资总和

Select Sum(sal) Over(Partition By deptno) ,a.* From emp a

三、求占百分比的分析函数ratio_to_report() over()

Ratio_to_report() 括号中就是分子，over() 括号中就是分母，分母缺省就是整个占比。

Ratio_to_report 一般结合partition by 使用。

Ratio_to_report(sal) over() *100 ---占整个公司的百分比

Ratio_to_report(sal) over(partition by deptno) *100 --占部门的百分比

四、lag()/lead() over() 分析函数

lag()函数，把上一行记录显示在当前行

lead()函数，把下一行数据显示在当前行

Select hiredate,

Lead(hiredate) Over(Order By hiredate)

From emp;

删除重复数据

一，rowid 去重

1，利用主键进行分组---性能差

Delete test_1

Where Rowid Not In (Select max(ROWID) FROM test_1 GROUP BY object_id) --每个重复的组里 取一条数据

2，利用分析函数----建议写这种

Delete test_1

Where Rowid In(Select Rowid From (

Select Rowid,row_number() Over(Partition By object_id Order By Rowid) rn From test_1)Where rn>1);

3，半连接方法exists，in

DELETE test_1 a WHERE EXISTS (SELECT NULL

FROM test_1 b WHERE a.object_id = b.object_id AND a.rowid < b.rowid);

等价改写为in

DELETE test_1 a

WHERE ROWID IN (SELECT b.rowid FROM test_1 b WHERE b.rowid > a.rowid);

4，distinct查询去重

5.Oracle的11g引入 regexp_count 函数 计算字符在字符串出现的次数。

SELECT regexp_count('1235545658', '5') FROM dual; ---返回4

三、Concat（）函数

将两个字符串连接起来，形成一个单一的字符串

select concat(ename,deptno) as data from emp;

四、cast（）函数

将某种数据类型转换为另一种数据类型，

SELECT CAST('12.4' AS int) From dual;---返回12，改为整数数据类型。

（1）两个表达式的数据类型完全相同。

（2）两个表达式可隐性转换。

（3）必须显式转换数据类型。

其中类型可以为：

CHAR[(N)] 字符型 

DATE  日期型

DATETIME  日期和时间型

DECIMAL  float型

SIGNED  int

TIME  时间型

五、listagg( ) Within Group（）over（）

将多行记录在一行显示

Select listagg(ename, ',') Within Group (Order By ename)From emp