一、概览
INITCAP 返回字符串并将字符串的第一个字母变为大写 INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 从哪个位置开始找,默认为1， J 第几次出现,默认为1 SCOTT@ORCL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING ---------- 9 LENGTH:返回字符串的长度； LOWER: 返回字符串,并将所有的字符小写； UPPER: 返回字符串,并将所有的字符大写； RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 SCOTT@ORCL> select lpad(rpad('gao',10,'*'),17,'*') string from dual; STRING ----------------- *******gao******* LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SCOTT@ORCL> select ltrim(rtrim(' gao qian jing ',' g'),' ') string from dual; STRING ------------ gao qian jin SUBSTR(string,start,count) 取子字符串,从start开始（包括开始）,取count个 SCOTT@ORCL> select substr(13088888888,3,8) string from dual; STRING -------- 08888888 REPLACE(string,s1,s2) string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串 SCOTT@ORCL> select replace('he love you','he','I') string from dual; STRING ---------- I love you ABS：返回指定值的绝对值 CEIL：返回大于或等于给出数字的最小整数 SCOTT@ORCL> select ceil(3.1415927) from dual; CEIL(3.1415927) --------------- 4 FLOOR:对给定的数字取整数 SCOTT@ORCL> select floor(2345.67) from dual; FLOOR(2345.67) -------------- 2345 MOD(n1,n2) 返回一个n1除以n2的余数 SCOTT@ORCL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) ---------- ---------- ---------- 1 0 2 ROUND和TRUNC 按照指定的精度进行舍入 round(55.5) = 56 round(55.4) = 55 trunc(sysdate,'yyyy') --返回当年第一天 trunc(sysdate,'mm') --返回当月第一天 trunc(sysdate,'day') --返回当前星期的第一天（周日） TRUNC（89.985，2）=89.98 TRUNC（89.985）=89 (即取整) TRUNC（89.985，-1）=80 日期函数 SQL> select to_char(add_months(to_date('202012','yyyymm'),2),'yyyymm') datum from dual; --增加或减去月份 DATUM ------ 202102 SQL> select last_day(sysdate) datum from dual; --返回本月的最后一天 DATUM ----------- 2020/12/31 SQL> select months_between(to_date('20000520','yyyymmdd'),to_date('20050520','yyyymmdd')) mon_betw from dual; --月份差异 MON_BETW ---------- -60 SQL> select next_day( sysdate, 'MONDAY') from dual; --当前日期的下个星期一 NEXT_DAY(SYSDATE,'MONDAY') -------------------------- 2020/12/21 6:52:42 GREATEST：返回一组表达式中的最大值,即比较字符的编码大小 LEAST：返回一组表达式中的最小值 STDDEV(distinct|all) 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL) ----------- 1182.503223 pl/sql中的case语句 select (case when DUMMY='X' then 0 else 1 end) as flag from dual; 1.case的第1种用法： case col when 'a' then 1 when 'b' then 2 else 0 end 这种用法跟decode一样没什么区别 2.case的第2种用法： case when score <60 then 'd' when score >=60 and score <70 then 'c' when score >=70 and score <80 then 'b' else 'a' end NVL(expr1, expr2) expr1为NULL，返回expr2，不为NULL，返回expr1，注意两者的类型要一致 NVL2 (expr1, expr2, expr3) expr1不为NULL，返回expr2,为NULL,返回expr3,expr2和expr3类型不同的话，expr3会转换为expr2的类型 NULLIF (expr1, expr2) 相等返回NULL，不等返回expr1 to_char 中fm 有9的地方如果有数字就显示如果没有数字就不显示，有0的地方在没有数字的时候也会有０来占位 select to_char(9999.09556,'fm99999.0900'),to_char(9999.09556,'fm00099.0900') from dual； 分析函数 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行,常用的分析函数如下所列: row_number() over(partition by ... order by ...) rank() over(partition by ... order by ...) dense_rank() over(partition by ... order by ...) count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...)
二、分析函数
2.1 rank（等级函数，也指示排名函数）
ROW_NUMBER() 定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序， 每一条数据加一个序号，一般多用于分页查询， 比如查询前10个 查询10-100个学生。 实例： 对学生成绩排序
这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序 获取第二个同学的成绩信息
这里用到的思想就是 分页查询的思想 在原sql外再套一层select where t.number>=1 and t.number<=10 是不是就是获取前十个学生的成绩信息纳。 RANK() 定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序， 当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()不一样出现相同的，他们的排名是一样的，看下面看例子:
注：当出现两个学生成绩相同是里面出现变化。 RANK()是 1 2 2，而ROW_NUMBER()则还是1 2 3，这就是RANK()和ROW_NUMBER()的区别了 DENSE_RANK() 定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？看例子：
DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就DENSE_RANK() NTILE() 定义：NTILE()函数是将有序分区中的行分发到指定数目的组中， 就是按序号分组的意思，各个组有编号，编号从1开始，就像我们说的分区一样 ，分为几个区，一个区会有多少个。
这里查询了3次,第一次分为1个区,所以查询结果number全是1， 第二次分为2个区，查询结果为 1 1 2，意思就是 第一个区为 1 1 （两个编号的数据），第二个区只有2这个数据。 总结： select row_number() over(order by a.deptno desc) rak,a.* from emp a --排序，用于分页查询 select rank() over(order by a.deptno desc) rak,a.* from emp a --跳跃的排名 select dense_rank() over(order by a.deptno desc) rak,a.* from emp a --排名连续 select NTILE(4) over(order by a.deptno desc) rak,a.* from emp a --分几个组
2.2 windowing函数
Order By 子句 select ename,sal,avg(sal) over() from emp; --所有值的平均值放在每行
select ename,sal,avg(sal) over(order by ename) from emp; --累积平均
注：在没有Order by子句时，在全部组上计算平均值，每一行给一个同样的值。 在用带有Order by的AVG（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数） select ename, deptno, sum(sal) over(order by ename, deptno) sum_ename_deptno, --但它仅仅在分区内对行进行排序，逻辑计算 sum(sal) over(order by deptno, ename) sum_deptno_ename from emp order by ename, deptno --只是排序，不影响逻辑计算
ROW 窗口 注：Windowing子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作， 在一组内基于任意变化或固定的窗口中，可以用该子句来让分析函数计算它的值， ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的列子作为ROW分区 select deptno,ename,sal, sum(sal) over( partition by deptno order by ename rows 2 preceding )sliding_total from emp order by deptno,ename 这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行SAL列的总和， 请注意，要使用窗口，必须使用ORDER BY 子句 Range 窗口 select ename, hiredate, sal, avg(sal) over(order by hiredate asc range 100 preceding) avg_sal_100_days_before, avg(sal) over(order by hiredate desc range 100 preceding) avg_sal_100_days_after from emp order by hiredate asc 如果有“range 5 preceding”，将产生一个滑动的窗口，它在组中拥有所有当前行以前5行的集合 range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内 rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关） 指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化 over（order by salary rows between 50 preceding and 150 following） --每行对应的数据窗口是之前50行，之后150行 FIRST_VALUE：返回组中的第一个值 LAST_VALUE：返回组中的最后一个值 LAG： 上一行 LEAD：下一行 KEEP函数 功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录 SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best" FROM employees WHERE department_id in (20,80) ORDER BY department_id, salary; LAST_NAME DEPARTMENT_ID SALARY Worst Best
三、常用日期函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') strDateTime from dual; --获取年-月-日 时:分:秒 --显示结果为：2018-09-20 12:35:21 select to_char(sysdate,'yyyy') strYear from dual; --获取年 --显示结果为：2018 select to_char(to_date('2018-09-20','yyyy-mm-dd'),'day') strDay from dual; --查询某天是星期几 --显示结果为：hursday select floor(sysdate - to_date('20201210','yyyymmdd')) strTime from dual; --两个日期间的天数 select months_between(date'2018-04-23',date'2017-04-23') days from dual; --月份差 select TO_CHAR(SYSDATE,'DDD'),sysdate from dual --查询某天是一年的第几天 select add_months(sysdate,1) from dual; --下个月的今天 select trunc(sysdate, 'mm') from dual --返回当月第一天 select trunc(sysdate,'yy') from dual --返回当年第一天 select trunc(sysdate,'d') from dual --返回当前星期的第一天（周日） select trunc(sysdate,'IW') from dual; --返回当前星期的第一天（周一） SELECT last_day(SYSDATE) FROM dual; --返回指定日期对应月份的最后一天 sysdate - 10 as "10天前", sysdate - 10 / 24 as "10小时前", sysdate - 10 / (24 * 60) as "10分钟前", sysdate - 10 / (24 * 3600) as "10秒钟前" select to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyiw') as week, --oracle求当年的第几周 to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyww') as week2, --oracle求当年的第几周 to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyy') as year, --oracle求第几年 to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyymm') as month, --oracle求当年的第几月 to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyddd') as day, --oracle求当年的第几天 to_char(TO_DATE('20190308', 'YYYYMMDD'), 'yyyyq') as quarter -- oracle求当年的第几季度 from dual 非ISO: 永远以新年的第一天为第一周的星期一，第一周一定有七天，而且最后一周不一定有七天。 ISO: 轮到星期几就是星期几，新年的第一天是星期几则为第一周的周期几，第一周不一定有七天，而且一定会要最后一周满七天，如果这年最后一周未满七天，则这一周会持续到下一年的前几天。每个日历星期从星期一开始，星期日为第7天。
