sql函数

--字符替换
select translate(‘abcdefgabcdef‘,‘abc‘,‘123‘) from dual;
select translate(‘agbfceddeafbgc‘,‘abc‘,‘123‘) from dual;

--字符串替换
select replace(‘abcdaefgdefadf‘,‘de‘,‘34‘) from dual;

--查找字符串所在位置：索引从1开始
SELECT instr(‘abcdefg‘,‘f‘) from dual;

--字符串截取：从3开始截取2位
SELECT Substr(‘abcdefg‘,3,2) from dual;

--字符串拼接：
SELECT CONCAT(‘abc‘,‘def‘),‘abc‘||‘def‘ from dual;

--获取字符串长度
SELECT LENGTH(‘abcdefg‘) from dual;

--数字函数
--取绝对值
SELECT ABS(12),ABS(-23) from dual;

--CEIL向上取整，floor向下取整
SELECT
CEIL(123.111),CEIL(-123.111),
FLOOR(123.999),FLOOR(-123.999) FROM dual;

--四舍五入
SELECT ROUND(153.454,2),ROUND(153.454,-2) FROM dual;

--截断
SELECT TRUNC(123.567,2) from dual;

--开平方根
SELECT SQRT(16) FROM dual;

--求冥
SELECT POWER(2,3) FROM dual;

--日期函数
--获取当前系统时间
SELECT SYSDATE from dual;

--两个日期之间的月份
SELECT months_between(to_date(‘2014-10-3‘,‘yyyy-mm-dd‘),SYSDATE) FROM dual;

--日期加减法
SELECT add_months(SYSDATE,-1) FROM dual;

--指定日期后的星期几是几月几号
SELECT next_day(SYSDATE,‘星期二‘) FROM dual;

--获取指定月的最后一天。
SELECT last_day(SYSDATE) FROM dual;

--round四舍五入
to_date(‘2014-5-20‘,‘yyyy-mm-dd‘) FROM dual;
SELECT
ROUND(to_date(‘2014-8-20‘,‘yyyy-mm-dd‘),‘YEAR‘),
ROUND(to_date(‘2014-5-20‘,‘yyyy-mm-dd‘),‘month‘),
ROUND(to_date(‘2014-5-20‘,‘yyyy-mm-dd‘),‘day‘)
from dual;

--trunc:日期截断
SELECT
TRUNC(to_date(‘2014-8-20‘,‘yyyy-mm-dd‘),‘YEAR‘),
TRUNC(to_date(‘2014-5-20‘,‘yyyy-mm-dd‘),‘month‘),
TRUNC(to_date(‘2014-5-20‘,‘yyyy-mm-dd‘),‘day‘)
from dual;

--转换函数
--tochar:其他类型转换为字符串
SELECT to_char(SYSDATE,‘yyyy-mm-dd‘) FROM dual;
SELECT to_char(123456.123,‘999,999.999‘) from dual;

--to_date：将其他类型转换为日期
SELECT to_date(‘2014-10-20‘,‘yyyy-mm-dd‘) FROM dual;

--to_number:将其他类型转换为数字
SELECT to_number(‘123456‘) from dual;

--其他函数
--nvl(exp1,exp2)：判断exp1是否为null，如果为null则反悔exp2，不为空则返回本身
SELECT ename, nvl(comm,0) FROM scott.emp;

--nvl(exp1,exp2,exmp3)：判断exp1是否为null，如果为null则反悔exp2，不为空则返回exp3
SELECT ename,NVL2(comm,0,100) from scott.emp;

--sql中的switch结构
SELECT ename,job,DECODE(job,‘CLERK‘,‘业务员‘,‘SALESMAN‘,‘销售员‘,‘MANAGER‘,‘经理‘) FROM scott.emp;

--聚合函数:对列中的进行汇总或计算
--聚合函数不能和普通列一起出现，除非这个列是分组列
SELECT deptno,
MAX(sal) AS 最高,
MIN(sal) AS 最低,
SUM(sal) AS 总金额，
AVG(sal) AS 平均,
COUNT(*) AS 总人数
FROM scott.emp
WHERE sal > 1000
GROUP BY deptno
HAVING COUNT(*) > 3

--分析函数
SELECT ename,deptno,sal,
row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) AS row_numer,
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS RANK,
dense_rank() OVER(PARTITION BY deptno ORDER BY sal DESC) AS dense_rank
FROM scott.emp;

--对查询结果进行一个自动编号
SELECT ROWNUM AS rnum,ename,deptno,sal FROM scott.emp;