Oracle基础-函数总结

Oracle自学笔记整理（四）
八、函数

8.1 distinct

当关键字DISTINCT后面只有一个列1时，表示的是单个字段查询结果的不重复数据，当后面跟着多个列值时，表示的是多个字段组成的查询结果的所有唯一值，进行的是多个字段的分组消除。

select` `distinct` `b.coursename, t.score
 ``from` `score t, course b
 ``where` `t.courseid = b.courseid
  ``and` `t.courseid = ``'R20180101'``;

8.2 单行函数

对每一个函数应用在表的记录中时，只能输入一行中的列值作为输入参数(或常数)，并且返回一个结果。

8.2.1 字符型函数

字符串函数：

TO_CHAR(要转换的字段，转换格式) to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

DECODE()

**函数 **	**说明 **	**案例 **	**结果 **
ASCII(X)	求字符X的ASCII码	select ASCII(‘A’) FROM DUAL;	65
CHR(X)	求ASCII码对应的字符	select CHR(65) FROM DUAL;	‘A’
LENGTH(X)	求字符串X的长度	select LENGTH(‘ORACLE技术圈’)from DUAL;	9
CONCATA(X,Y)	返回连接两个字符串X和Y的结果	select CONCAT(‘ORACLE’,‘技术圈’) from DUAL;	ORACLE技术圈
INSTR(X,Y[,START])	查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始	SELECT INSTR(‘ORACLE技术圈’,‘技术’) FROM DUAL;	7
LOWER(X)	把字符串X中大写字母转换为小写	SELECT LOWER(‘ORACLE技术圈’) FROM DUAL;	oracle技术圈
UPPER(X)	把字符串X中小写字母转换为大写	SELECT UPPER(‘Oracle技术圈’) FROM DUAL;	ORACLE技术圈
INITCAP(X)	把字符串X中所有单词首字母转换为大写，其余小写。	SELECT INITCAP('ORACLE is good ') FROM DUAL;	Oracle Is Good
LTRIM(X[,Y])	去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格	SELECT LTRIM(’–ORACLE技术圈’,’-’) FROM DUAL;	ORACLE技术圈
RTRIM(X[,Y])	去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格	SELECT RTRIM(‘ORACLE技术圈–’,’-’) FROM DUAL;	ORACLE技术圈
TRIM(X[,Y])	去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格	SELECT TRIM(’–ORACLE技术圈–’,’-’) FROM DUAL;	ORACLE技术圈
REPLACE(X,old,new）	查找字符串X中old字符，并利用new字符替换	SELECT REPLACE(‘ORACLE技术圈’,‘技术圈’,‘技术交流’) FROM DUAL;	ORACLE技术交流
SUBSTR(X,start[,length])	截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾	SELECT SUBSTR(‘ORACLE技术圈’,1,6) FROM DUAL;	ORACLE
RPAD(X,length[,Y])	对字符串X进行右补字符Y使字符串长度达到length长度	SELECT RPAD(‘ORACLE’,9,’-’) from DUAL;	ORACLE—
LPAD(X,length[,Y])	对字符串X进行左补字符Y使字符串长度达到length长度	SELECT LPAD(‘ORACLE’,9,’-’) from DUAL;	—ORACLE

8.2.2 日期型函数

1、系统日期、时间函数

**SYSDATE函数：**该函数没有参数，可以得到系统的当前时间

**SYSTIMESTAMP函数：**该函数没有参数，可以得到系统的当前时间，该时间包含时区信息，精确到微秒

2、给日期加指定月份函数：

**ADD_MONTHS（r,n）函数：**该函数返回在指定日期r上加上一个月份数n后的日期

r：指定的日期。

n：要增加的月份数，如果N为负数，则表示减去的月份数。

select` `to_char(add_months(to_date(``'2018-11-12'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``),
    ``to_char(add_months(to_date(``'2018-10-31'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``),
    ``to_char(add_months(to_date(``'2018-09-30'``,``'yyyy-mm-dd'``),1),``'yyyy-mm-dd'``)    
 ``from` `dual;

3、月份最后一天函数：

**LAST_DAY®函数：**返回指定r日期的当前月份的最后一天日期

 ``select` `last_day(sysdate) ``from` `dual;

4、指定日期后一周的日期函数：

**NEXT_DAY(r,c)函数：**返回指定R日期的后一周的与r日期字符（c：表示星期几）对应的日期

 ``select` `next_day(to_date(``'2018-11-12'``,``'yyyy-mm-dd'``),``'星期四'``) ``from` `dual;

5、返回指定日期中特定部分的函数：

**EXTRACT（time）函数：**返回指定time时间当中的年、月、日、分等日期部分。

select` `extract( ``year` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `year``,
    ``extract( ``month` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `month``,    
    ``extract( ``day` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `day``, 
    ``extract( ``minute` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `minute``,
    ``extract( ``second` `from` `timestamp` `'2018-11-12 15:36:01'``) ``as` `second
 ``from` `dual;

6、返回两日期间的月份数

**MONTHS_BETWEEN(r1,r2)函数：**该函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1<r2时，返回的是负数。

select` `months_between(to_date(``'2018-11-12'``, ``'yyyy-mm-dd'``),
           ``to_date(``'2017-11-12'``, ``'yyyy-mm-dd'``)) ``as` `zs, ``--整数
    ``months_between(to_date(``'2018-11-12'``, ``'yyyy-mm-dd'``),
           ``to_date(``'2017-10-11'``, ``'yyyy-mm-dd'``)) ``as` `xs, ``--小数
    ``months_between(to_date(``'2017-11-12'``, ``'yyyy-mm-dd'``),
           ``to_date(``'2018-10-12'``, ``'yyyy-mm-dd'``)) ``as` `fs ``--负数
 ``from` `dual;

7、日期截取函数

**ROUND（r[,f]）函数：**将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天

select` `sysdate, ``--当前时间
    ``round(sysdate, ``'yyyy'``) ``as` `year``, ``--按年
    ``round(sysdate, ``'mm'``) ``as` `month``, ``--按月
    ``round(sysdate, ``'dd'``) ``as` `day``, ``--按天
    ``round(sysdate) ``as` `mr_day, ``--默认不填按天
    ``round(sysdate, ``'hh24'``) ``as` `hour` `--按小时
 ``from` `dual;

**TRUNC（r[,f]）函数：**将日期r按f的格式进行截取。如果f不填，则截取到当前的日期。

select` `sysdate, ``--当前时间
    ``trunc(sysdate, ``'yyyy'``) ``as` `year``, ``--按年
    ``trunc(sysdate, ``'mm'``) ``as` `month``, ``--按月
    ``trunc(sysdate, ``'dd'``) ``as` `day``, ``--按天
    ``trunc(sysdate) ``as` `mr_day, ``--默认不填按天
    ``trunc(sysdate, ``'hh24'``) ``as` `hour` `--按小时
 ``from` `dual;

8.2.3 数值型函数

**数 **	**解释 **	**案例 **	**结果 **
ABS(X)	求数值X的绝对值	select abs(-9) from dual;	9
COS(X)	求数值X的余弦	select cos(1) from dual;	0.54030230586814
ACOS(X)	求数值X的反余弦	select acos(1) from dual;	0
CEIL(X)	求大于或等于数值X的最小值	select ceil(7.8) from dual;	8
FLOOR(X)	求小于或等于数值X的最大值	select floor(7.8) from dual;	7
log(x,y)	求x为底y的对数	select log(2,8) from dual;	3
mod(x,y)	求x除以y的余数	select mod(13,4) from dual;	1
power(x,y)	求x的y次幂	select power(2,4) from dual;	16
sqrt(x)	求x的平方根	select sqrt(16) from dual;	4
round(x[,y])	求数值x在y位进行四舍五入。y不填时，默认为y=0;当y>0时，是四舍五入到小数点右边y位。当y<0时，是四舍五入到小数点左边|y|位。	select round(7.816, 2), round(7.816), round(76.816, -1) from dual;	7.82 / 8 / 80
trunc(x[,y])	求数值x在y位进行直接截取y不填时，默认为y=0;当y>0时，是截取到小数点右边y位。当y<0时，是截取到小数点左边|y|位。	select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual;	7.81 / 7 / 70

8.2.4 转换函数

**函数 **	**解释 **	**案例 **	**结果 **
asciistr(x)	把字符串x转换为数据库字符集对应的ASCII值	select asciistr(‘Oracle技术圈’) from dual;	Oracle\6280\672F\5708
bin_to_num(x1[x2…])	把二进制数值转换为对应的十进制数值	select bin_to_num(1,0,0) from dual;	4
cast(x as type)	数据类型转换函数，该函数可以把x转换为对应的type的数据类型，基本上用于数字，字符，时间类型安装数据库规则进行互转，	select cast(‘123’ as number) num,cast(123 as varchar2(3)) as ch,cast(to_date(‘20181112’,‘yyyymmdd’) as varchar2(12)) as time from dual;	123/‘123’/12-11月-18(三列值，用"/"隔开)
convert(x,d_chset[,r_chset])	字符串在字符集间的转换函数，对字符串x按照原字符集r_chset转换为目标字符集d_chset，当r_chset不填时，默认选择数据库服务器字符集。	select CONVERT(‘oracle技术圈’,‘US7ASCII’,‘ZHS16GBK’) from dual;	oracle???
to_char(x[,f])	把字符串或时间类型x按格式f进行格式化转换为字符串。	select to_char(123.46,‘999.9’) from dual; select to_char(sysdate,‘yyyy-mm-dd’) from dual;	123.52018-11-13
to_date(x[,f])	可以把字符串x按照格式f进行格式化转换为时间类型结果。	select to_date(‘2018-11-13’,‘yyyy-mm-dd’) from dual;	2018/11/13
to_number(x[,f])	可以把字符串x按照格式f进行格式化转换为数值类型结果。	select to_number(‘123.74’,‘999.99’) from dual	123.74

8.2.5 聚合函数

AVG()、SUM()、MIN()、MAX()、COUNT()等等

8.3 开窗函数

select` `table``.``column``, 
Analysis_function() OVER(
[partition ``by` `字段]
[``order` `by` `字段 [windos]]
) ``as` `统计值
from` `table

语法解析：

1、Analysis_function：指定分析函数名，常用的分析函数有sum、max、first_value、last_value、rank、row_number等等。

2、over()：开窗函数名，partition by指定进行数据分组的字段，order by指定进行排序的字段，windos指定数据窗口（即指定分析函数要操作的行数）

eg:
select` `c.stuname,
    ``b.coursename,
    ``t.score,
    ``--获取组中成绩最大值
    ``max``(t.score) over(partition ``by` `t.courseid) ``as` `score_max,
    ``--获取组中成绩最小值
    ``min``(t.score) over(partition ``by` `t.courseid) ``as` `score_min,
    ``--分组窗口的第一个值 (指定窗口为组中第一行到末尾行)
    ``first_value(t.score) over(partition ``by` `t.courseid 
    ``order` `by` `t.score ``desc` `ROWS` `BETWEEN` `UNBOUNDED PRECEDING ``AND` `UNBOUNDED FOLLOWING) ``as` `score_first,
    ``--分组窗口的最后一个值(指定窗口为组中第一行到末尾行)
    ``last_value(t.score) over(partition ``by` `t.courseid 
    ``order` `by` `t.score ``desc` `ROWS` `BETWEEN` `UNBOUNDED PRECEDING ``AND` `UNBOUNDED FOLLOWING) ``as` `score_last,
    --第一行是 unbounded preceding，当前行是 current row，最后一行是 unbounded following
    
    ``--分组窗口的第一个值 （不指定窗口）
    ``first_value(t.score) over(partition ``by` `t.courseid ``order` `by` `t.score ``desc` `) ``as` `score_first_1,
    ``--分组窗口的最后一个值（不指定窗口）
    ``last_value(t.score) over(partition ``by` `t.courseid ``order` `by` `t.score ``desc` `) ``as` `score_last_1
    
 ``from` `STUDENT.SCORE t, student.course b, student.stuinfo c
 ``where` `t.courseid = b.courseid
  ``and` `t.stuid = c.stuid

8.3.1 ROW_NUMBER/RANK

ROW_NUMBER/RANK：根据开窗函数中排序的字段返回在组内的有序的偏移量，即可得到在组内的位置。

select` `c.stuname,
    ``b.coursename,
    ``t.score,
    ``--组内排名
    ``row_number() over(partition ``by` `t.courseid ``order` `by` `t.score ``desc``) ``as` `"row_number排名"``,--相同分数排名不重复，依次递增
    ``--组内排名
    ``rank() over(partition ``by` `t.courseid ``order` `by` `t.score ``desc``) ``as` `"rank排名"--相同分数排名相同，跳过
``from` `STUDENT.SCORE t, student.course b, student.stuinfo c
 ``where` `t.courseid = b.courseid
  ``and` `t.stuid = c.stuid

1、ROW_NUMBER函数排名是返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

2、rank函数返回一个唯一的值，但是当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。比如数学成绩都是84分的两个人并列第二名，但是“张三丰”同学就是直接是第四名。

3、我们经常会利用row_number函数的排名机制（排名的唯一性）来过滤重复数据，即按照某一个特定的排序条件，通过获取排名为1的数据来获取重复数据当中最新的数据值。