oracle相关函数

--SQL执行顺序
from where group by having select rownum order by
--数据库起始值从1算起、Java起始值从0开始
--1、数值函数-----开始--------------------
--四舍五入(n:代表要操作的数字;m:代表小数点向左向右四舍五入的方向[+:小数点向右;-小数点向左;默认为0，可写可不写]；当成数学的线段图)
round(n[, m])
select round(24.35, 0), round(24.35, -1), round(24.35, 1) from dual;
--直接截取，不四舍五入
trunc(n[,m])
select trunc (3.56) from dual;
--向上取整、向下取整(形参只有一位)
select ceil(24.56), floor(24.5) from dual
--取绝对值
select abs(24.5), abs(-24.5) from dual
--取余
select mod(5,3), mod(5,null) from dual
--数值函数-----结束--------------------

--2、字符函数-----开始--------------------
--字符小写、大写、首字母大写
select lower('ABC'), upper('abc'), initcap('abc') from dual
--字符串截取(+:从左边算起始值;-:从右边算起始值)
substr('字符', '起始值'， '长度')    substr('char', n[,m])
select substr('AaBCbCd', 1), substr('AaBCbCd', 4,3), substr('AaBCbCd', 2, 3), substr('AaBCbCd', -2, 3), substr('AaBCbCd', 4, 3) from dual
--拼接字符串
CONCAT('A', 'B')    ||
--判断字段里面是否包含指定字符
select instr('address', 'beijing'), instr('shanghai,beijing', 'beijing'), instr('beijing', 'beijing') from dual
--返回字符串长度
select length('Hello') from dual;
--删除字符左端的字符、删除字符右端的字符、删除字符左右两端的字符
SELECT ltrim('=Hello=','='), rtrim('=hello=', '='), trim('='FROM'=Hello=') FROM dual;
--新字符替换旧字符replace(原字段，'原字段旧内容', '原字段新内容')
SELECT REPLACE('ABCDEcdBCD','CD','AAA')FROM dual;
--字符函数-----结束--------------------

--3、日期函数-----开始--------------------
--在日期d上加n个月
add_months(d,n)
select sysdate, add_months(sysdate,5) from dual;
--返回指定日期当月的最后一天
last_day(d)
select sysdate, last_day(sysdate) from dual;
--四舍五入到天、周、月、年
select sysdate, round(sysdate), round(sysdate,'day'), round(sysdate,'month'), round(sysdate,'year') from dual;
--截取到到天、周、月、年的第一天、周、月、年
select sysdate, trunc(sysdate), trunc(sysdate,'day'), trunc(sysdate,'month'), trunc(sysdate,'year') from dual;
--EXTRACT(fmt FROM d)，提取日期中的特定部分
SELECT SYSDATE "date", EXTRACT(YEAR FROM SYSDATE)"year", EXTRACT(MONTH FROM SYSDATE)"month", EXTRACT(DAY FROM SYSDATE)"day", EXTRACT(HOUR FROM SYSTIMESTAMP)"hour", EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute", EXTRACT(SECOND FROM SYSTIMESTAMP)"second" FROM dual;
--日期函数-----结束--------------------

--4、转换函数-----开始--------------------
TO_CHAR(d|n[,fmt])
TO_DATE(X,[,fmt])
--转换函数-----结束--------------------

--5、空值判断函数-----开始--------------------
NVL(X,VALUE)--如果X为空，返回value，否则返回X
NVL2(x,value1,value2)--如果x非空，返回value1，否则返回value2
coalesce(exp1, exp2, exp3)--从左到右，依次参考各参数表达式，遇到非null值即停止并返回该值
--其它单行函数-----结束--------------------

--6、聚合函数-----开始--------------------
AVG(col)
SUM(col)
MIN(col)
MAX(col)
COUNT(col)
--聚合函数-----结束--------------------

--7、分组函数-----开始--------------------
group by having
--分组函数-----结束--------------------

--8、逻辑函数-----开始--------------------
decode()
case when exp1 then
case when exp2 then
else 
end
exists()
--逻辑函数-----结束--------------------

--9、集合运算-----开始--------------------
union --去除重复并排序
union all    --不会去除重复的
--集合运算-----结束--------------------

--10、连接函数-----开始--------------------
--左外、右外关联
--放在where过滤，会得到中间表满足关联条件的数据，不会影响记录条数
--放在on过滤，会影响记录条数，但未满足记录的条件，字段值会为null
--内连接没这个特殊性，放在where和on中过滤，效果一样
left join table on
right join table on
inner join table on 
--连接函数-----结束--------------------

--11、开窗函数-----开始--------------------
row_number() over(partition by)    --当分组排序后，若存在并列的排序结果，只返回一个结果(不能求有并列的情况)
rank() over(partition by)    --当分组排序后，若存在并列的排序结果，可以将并列的结果都查找出来，但是会出现跳序现象
dense_rank() over(partition by)    --当分组排序后，若存在并列的排序结果，可以将并列的结果都查找出来，但是会出现连序现象
sum() over(partition by)    --累加函数
avg() over(partition by)    --累计求平均值函数
count() over(partition by)    --累计求统计值函数
first_value() over(partition by)    --累计求首个值的函数
last_value() over(partition by)    --累计求最后一个值的函数
--开窗函数-----结束--------------------

--12、递归函数-----开始--------------------
--向下查找(connect by prior t.son_id_ = t.parent_id_)
select * from test t start with t.level = 3 connect by prior t.son_id_ = t.parent_id_
--向上查找(connect by prior t.parent_id_ = t.son_id_)
select * from test t start with t.level = 3 connect by prior t.parent_id_ = t.son_id_
--递归函数-----结束--------------------

--上个月1号00:00:00

select last_day(trunc(add_months(sysdate, -1), 'mm') + 0 / 86400) from dual;

select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') || ' 00:00:00' firstDay from dual;

--获取上个月最后一天23:59:59
select last_day(trunc(add_months(sysdate, -1), 'mm') + 86399 / 86400) from dual;
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') || ' 23:59:59' firstDay from dual;