Oracle sql 单行函数
2018-02-21 本文已影响7人
我爱矿泉水
一.字符
1.大小写转换
LOWER 字符转为小写
UPPER 字符转为大写
INITCAP 首字母大写，其他小写
SQL> select ename from emp where deptno=10;
ENAME
----------
CLARK
KING
MILLER
1).将ename转换为小写
SQL> select lower(ename) from emp where deptno=10;
LOWER(ENAM
----------
clark
king
miller
2).将king scott转换为大写
SQL> select upper('king scott') from dual;
UPPER('KIN
----------
KING SCOTT
3).将ename首字母转为大写，其余转为小写
SQL> select initcap(ename) from emp where deptno=10;
INITCAP(EN
----------
Clark
King
Miller
SQL> select initcap('king scott') from dual; => King Scott
SQL> select initcap('king0scott') from dual; => King0scott
SQL> select initcap('king_scott') from dual; => King_Scott
2.字符处理类
1).concat：类似“||”,连接函数
SQL> select ename||' is work '||job from emp where deptno=10;
ENAME||'ISWORK'||JOB
-----------------------------
CLARK is work MANAGER
KING is work PRESIDENT
MILLER is work CLERK
SQL> select concat(ename,' is work '), job from emp where deptno=10;
CONCAT(ENAME,'ISWOR JOB
------------------- ---------
CLARK is work MANAGER
KING is work PRESIDENT
MILLER is work CLERK
SQL> select concat(concat(ename,' is work '),job) from emp where deptno=10;
CONCAT(CONCAT(ENAME,'ISWORK'
----------------------------
CLARK is work MANAGER
KING is work PRESIDENT
MILLER is work CLERK
2).substr(expr字符串,m截取开始位置,n截取长度) //返回截取的字
注意：m为负数时，表示从右端开始截取; 必要条件：n>0。
# 截取左起第二个字符为A的ename
SQL> select ename from emp where substr(ename,2,1)='A';
ENAME
----------
WARD
MARTIN
JAMES
# substr(ename,1,1)和substr(ename,0,1)，0和1都表示从第一个字符开始截取
SQL> select ename from emp where substr(ename,1,1)='A';
SQL> select ename from emp where substr(ename,0,1)='A';
ENAME
----------
ALLEN
ADAMS
# (ename,-1,1) -1表示从右边第一个字符开始截取
SQL> select ename from emp where substr(ename,-1,1)='K';
ENAME
----------
CLARK
SQL> select ename from emp where substr(ename,-2,1)='K';
ENAME
----------
BLAKE
注意：
SQL> select ename from emp where substr(ename,1)='KING'; #无意义
SQL> select ename from emp where ename='KING'; #正确的查询方法
ENAME
----------
KING
3).length(str) 长度
# LENGTH(str) 返回以字符为单位的长度.
# LENGTHB(str) 返回以字节为单位的长度,unicode格式一个汉字占三个字节数
# LENGTHC(str) 返回以Unicode完全字符为单位的长度.
# LENGTH2(str) 返回以UCS2代码点为单位的长度.
# LENGTH4(str) 返回以UCS4代码点为单位的长度.
SQL> select ename,length(ename),lengthc(ename),
lengthb(ename) from emp where deptno=10;
ENAME LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME)
---------- ------------- -------------- --------------
CLARK 5 5 5
KING 4 4 4
MILLER 6 6 6
SQL> insert into emp(empno,ename) values(1,'长度');
SQL> select ename,length(ename),lengthc(ename),
lengthb(ename) from emp where empno=1;
ENAME LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME)
---------- ------------- -------------- --------------
长度 2 2 6 #用unicode格式存储汉字，三个字节一个汉字
4).instr(str,'A') 查找A在str里第一次出现的位置
SQL> select instr(ename,'A'),ename from emp;
# 查找A出现的位置
INSTR(ENAME,'A') ENAME
---------------- ----------
0 SMITH
1 ALLEN
2 WARD
0 JONES
2 MARTIN
3 BLAKE
3 CLARK
0 SCOTT
0 KING
0 TURNER
# 查找A开头的ename
SQL> select ename from emp where instr(ename,'A')=1;
ENAME
----------
ALLEN
ADAMS
SQL> insert into emp(empno,ename) values(2,'AADCS');
已创建 1 行。
SQL> select ename from emp where instr(ename,'A')=2;
ENAME
----------
WARD
MARTIN
JAMES #这里并没有查询到AADCS这个值，因为instr只查询A第一次出现的位置
SQL> select ename from emp where instr(substr(ename,2),'A')=1;
ENAME
----------
AADCS # 这时，便查到了AADCS
WARD
MARTIN
JAMES
SQL> select instr(ename,'LL'),ename from emp;
INSTR(ENAME,'LL') ENAME # LL 这里当作一个整体，ALLEN返回值2,MILLER返回值3.
----------------- ----------
0 AADCS
0 SMITH
2 ALLEN
3 MILLER
5).LPAD and RPAD 左填充和右填充
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select rpad(deptno,10,' ') deptno,dname,loc from dept;
# 右边填充左边对齐，最大10个字符长度，不足位数用空格填充。
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
# 左边填充右边对齐
SQL> col dname for a15
SQL> select deptno,lpad(dname,15,' ') dname,loc from dept;
DEPTNO DNAME LOC
---------- --------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
# 列的值居左left/右right/中center
SQL> col dname just right
SQL> select deptno,lpad(dname,15,' ') dname,loc from dept;
DEPTNO DNAME LOC
---------- --------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6).TRIM 删除字符函数
trim(both|leading|trailing 'char' from expr)
trim('A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDD #从两头删除字符A
trim('A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDD #同上，所以both可以省略
trim(leading 'A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDDAA #删除左边的字符A
trim(trailing 'A' from 'AAABBCDDCAAADDDAA') => AAABBCDDCAAADDD #删除右边的字符A
ltrim、rtrim，要求：oracle版本>=10g
ltrim('AAABBCDDCAAADDDAA','A') => BBCDDCAAADDDAA 类似于trim leading 删除左边的A
rtrim('AAABBCDDCAAADDDAA','A') => AAABBCDDCAAADDD 类似于trim trailing 删除右边的A
7).replace(str,old,new) 替换函数
replace('BLACK and BLUE','BL','J') => JACK and JUE
SQL> select replace(ename,'长度','forway') neweanme,ename from emp;
NEWEANME ENAME
------------------------------------------------------------ ----------
forway 长度
SMITH SMITH
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
二.number数字函数
1.round和trunc
example：456.789
4 5 6 . 7 8 9 # 注意位置对应关系
-3 -2 -1 0 1 2 3
round(456.789,2) => 8>5 456.79 #保留2位小数
trunc(456.789,2) => 只舍掉不进位 456.78 #保留2位小数
round(456.789,1) => 456.8 #保留1位小数
trunc(456.789,1) => 456.7 #保留1位小数
round(456.789,-1) => 460
trunc(456.789,-1) => 450
round(456.789,-2) => 500
trunc(456.789,-2) => 400
#不包含取舍位数要求的时候：
ROUND(456.789) => 457
TRUNC(456.789) => 456
2.mod(number,a) 取余
MOD(2002,5) => 2
MOD(2002,3) => 3
3.abs 绝对值
abs(-123) => 123
三.日期
1.日期显示格式
oracle里日期默认存储格式为DD-MON-RR
[oracle@wyzc ~]$ unset NLS_LANG
[oracle@wyzc ~]$ sqlplus scott/scott@wyzc11g
SQL> select ename,hiredate from emp;
ENAME HIREDATE
---------- ---------
JONES 02-APR-81
MARTIN 28-SEP-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
SCOTT 19-APR-87
KING 17-NOV-81
SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; => 2018-02-21
RR纪年法年份计算：
current year last 2 number --- <50
last 2 number --- 0-49 <50
last 2 number --- 50-99 世纪 -1
last 2 number --- >=50
last 2 number --- >=50
last 2 number --- < 50 0-49 世纪 +1
# 更改默认的日期显示格式
SQL> alter session set nls_date_format='yyyy-mm-dd';
SQL> select hiredate from emp where deptno=10;
HIREDATE
----------
1981-06-09
1981-11-17
1982-01-23
2.日期函数
1).日期的四舍五入 round/trunc
SQL> select ename,sysdate-hiredate hiredays from emp;# 计算雇佣天数
ENAME HIREDAYS
---------- ----------
SMITH 13580.5862
ALLEN 13515.5862
WARD 13513.5862
JONES 13474.5862
# today 2018-02-21
SQL> select sysdate-1 from dual; => 2018-02-20
SQL> select sysdate+1 from dual; => 2018-02-22
SQL> select round(sysdate) from dual; => 2018-02-22 四舍五入
select round(sysdate,'yyyy') from dual; => 2018-01-01
临时修改日期、时间
sudo date -s MM/DD/YY //修改日期
sudo date -s hh:mm:ss //修改时间
在修改时间以后，修改硬件CMOS的时间
sudo hwclock --systohc //非常重要，如果没有这一步的话，后面时间还是不准
# today 2013-12-31 12:02:03 星期二
SQL> select round(sysdate,'yyyy') ,trunc(sysdate,'yyyy') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-01-01
SQL> select round(sysdate,'mm') ,trunc(sysdate,'mm') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-12-01 # 27号超过了15号，round=>2014-01-01,trunc=>2013-12-01
SQL> select round(sysdate,'dd') ,trunc(sysdate,'dd') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-12-31 # 12:02:03 超过半天，所以round=>2014-01-01
# 29号是这个星期的第三天，不超一周的一半，结果也就是上个星期的周日，国外从周日开始算起
SQL> select round(sysdate,'day') ,trunc(sysdate,'day') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2013-12-29 2013-12-29
2).months_between、add_months、next_day、last_day
- months_between、add_months
# 员工入职公司多少月
SQL> select ename,months_between(sysdate,hiredate) months from emp;
# 员工入职公司多少年
SQL> select ename,months_between(sysdate,hiredate)/12 years from emp;
#员工入职、转正的日期
SQL> select ename,hiredate,add_months(hiredate,3) from emp;
ENAME HIREDATE ADD_MONTHS
---------- ---------- ----------
SMITH 1980-12-17 1981-03-17
ALLEN 1981-02-20 1981-05-20
WARD 1981-02-22 1981-05-22
JONES 1981-04-02 1981-07-02
MARTIN 1981-09-28 1981-12-28
SQL> update emp set hiredate=to_date('1980-11-30','yyyy-mm-dd'),ename='E3'
where empno=1;
# add_months 只对月份累加运算，下文这里2月份只有28天，只到28号，所以这里就是1981-02-28
SQL> select ename,hiredate,add_months(hiredate,3) from emp;
ENAME HIREDATE ADD_MONTHS
---------- ---------- ----------
E3 1980-11-30 1981-02-28
SMITH 1980-12-17 1981-03-17
# 本月转正的员工
SQL> select ename,hiredate from emp where hiredate=add_months(sysdate,-3);
- next_day、last_day
# 下个星期一，也可以写全monday
SQL> select next_day(sysdate,'mon') from dual; => 26-FEB-18
#换到中文环境下，./.nls,查询下个星期三
SQL> select next_day(sysdate,'星期三') from dual; => 28-2月 -18
# 查询当天是星期几
SQL> select to_char(sysdate,'day') from dual; => 星期三
SQL> select to_char(sysdate,'dy') from dual; # 同上
SQL> select last_day(sysdate) from dual; => 28-2月-18 #当月最后一天
