Oracle学习笔记2-单行函数

一、字符函数：
upper(),lower() 函数
select upper('linux')from dual ;
select * from emp where ename=upper('smith');
initcap() 函数（首字母大写，其他字母小写）
select ename,initcap(ename) from emp;
replace()替换函数
select ename as 原始名字 , replace(ename,'A','_') 替换名字 from emp;
length() 长度计算函数
select * from emp where length(ename)=5;

从指定位置截取到结尾：substr(列|字符串,截取开始点);
截取部分的字符串：substr(列|字符串,截取开始点,截取个数);
select * from emp where substr(ename,0,3)='jam';
在oracle中开始下标是1，如果是0，则默认也是1

select ename ,substr(ename,3) from emp where  deptno=10;

截取姓名的后三位(-3就是后三位)
select ename,  substr(ename,length(ename)-2)from emp ;
select ename,substr(ename,-3) from emp;
ASCII 码
select ASCII('A'),ASCII('z') FROM dual;

TRIM()去掉左右空格，LTRIM()去掉做空格， RTRIM()去掉右空格

select '   DGQ ;laksdf skldf ' as 原始字符串,
ltrim('   DGQ ;laksdf skldf ')as 去掉左空格 from dual;

lpad(字符串|字段,位数,填充字符),Rpad(字符串|字段,位数,填充字符)字符填充

select Rpad(lpad(ename,10,'D'),16,'G') from emp;

字符串查找instr()如果找到就返回位置，如果找不到就返回0
select instr('aslkdjf','f') from dual;
select instr('aslkdjf','p') from dual;

二、数值函数

--round()函数四舍五入与保留小数

select round(123.23) from dual;

--保留小数2位

select round(123.234,2) from dual;

select ename,round(sal/12,2) from emp;

--四舍五入

select round(778,-2) from dual;

--trunc()函数截取小数，不进位

对比一下：

select round(123.78) from dual;

select trunc(123.78) from dual;

select trunc(123.78,-2) from dual;

 

--mod()取余数

--取10除以3的余数

select mod(10,3) from dual;

-三、日期函数
1.--取得当前日期
--可以通过利用sysdate伪列取得当前日期。所谓的伪列指的是不是表中的列，但又可以直接使用的列
select sysdate from dual;
2.--日期操作公式
若干天前的日期：日期-数字
select sysdate-7 from dual;
3.若干天后的日期：日期+数字
select sysdate+3 from dual;
4.两个日期之间的天数：日期-日期
select  trunc(sysdate-hiredate) as 雇佣天数 from emp;
5.--对于oracle而言提供相应的日期函数，之所以使用日期函数主要是避免闰年问题，
或者是一个月有28，29,30,31天的问题，通过日期函数可以取得最准确的时间。
6.--add_month()增加月份
select sysdate, add_months(sysdate,3) from dual;
select sysdate,add_months(sysdate,-3) from dual;
select sysdate,add_months(sysdate,50) from dual;
select hiredate,add_months(hiredate,3) from emp;
7.--next_day()函数，求出制定的日期数
--求出下一个星期日的具体日期
select  sysdate ,next_day(sysdate,'星期日') from dual;
--last_day(),求出制定日期所在月的最后一天的日期
select hiredate,last_day(hiredate) from emp;
8.--months_between()函数，返回指定日期之间的月数
--求出工龄几年
select empno,ename,hiredate,round( months_between(sysdate,hiredate)/12) as 工龄 from emp;
--求出雇佣的时间，具体到天，即求出雇佣了几年几月几天
select empno,ename,hiredate,
trunc( months_between(sysdate,hiredate)/12) as 工龄 ,
trunc(mod（months_between(sysdate,hiredate),12)） as 月数,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) as 天数
from emp;

9.--extract()函数，此函数的主要功能是可以从一个日期或者时间间隔中截取出特定的部分。
-从日期时间中去除年月日。(systimestamp取当前时间包含毫秒)
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; 
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; 
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; 
SELECT EXTRACT(HOUR FROM TIMESTAMP '2010-10-10 10:10:10') FROM DUAL;
SELECT EXTRACT(MINUTE  FROM TIMESTAMP '2010-10-10 10:10:10') FROM DUAL;
select extract(hour from systimestamp)from dual;--不知为何每次取出的小时总是1，不正确，没找到原因
--取两个时间点，相差的天数，小时，分钟。。
select 
        extract(day from time1-time2)as days,
        extract(hour from time1-time2)as hours,
        extract(minute from time1-time2)as minutes,
        extract(second from time1-time2)as seconds
from
(select 
to_timestamp('2017-09-04 10:11:00','yyyy-mm-dd hh24:mi:ss')as time1,
to_timestamp('2012-07-02 08:30:00','yyyy-mm-dd hh24:mi:ss') as time2
from dual);--(to_timestamp()日期格式化函数)

四、转换函数
常用的日期格式参数

No	格式	描述	示例 （2017-09-04 16:02:05为例）
1	YYYY	完整的年份数字表示，年有四位	2017
2	Y,YYY	带逗号的年份	2017
3	YYY	年的后三位	017
4	YY	年的后两位	17
5	Y	年的最后一位	7
6	YEAR	年份的文字表示，直接表示四位的年	twenty seventeen
7	MONTH	月份的文字表示，直接表示两位的月	9月
8	MM	两位的月份	09
9	DAY	用文字表示星期几	星期一
10	DDD	表示一年里的天数	247(001-366)
11	DD	表示一月里的天数	04(01-31)
12	D	表示一周里的天数	2（1-7）周日算第一天
13	DY	用文字表示星期几	星期一
14	WW	表示一年里的周数	36
15	W	表示一月里的周数	1
16	HH	表示12小时制	04
17	HH24	表示24小时制	16
18	MI	表示分钟	02
19	SS	表示秒	05
20	SSSSS	午夜之后的秒数表示	0-86399
21	AM|PM	表示上午下午	下午
22	FM	去掉查询后的前导0	2017-9-4

1.to_char()函数，用于将数据类型变为字符串
select sysdate as 原始日期 ,
to_char(sysdate,'yyyy-mm-dd') as 格式化日期,
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as 格式化日期时间,
to_char(sysdate,'FMyyyy-mm-dd HH24:MI:SS') as 去掉前导0
from dual;
找出雇员表里二月雇佣的雇员信息
select * from emp where to_char(hiredate,'MM')='02';
select * from emp where to_char(hiredate,'MM')=2;

to_char()函数最大的功能就是将数字格式化

NO	转化格式	描述
1	9	表示一位数字
2	0	显示前导0
3	$	将货币的符号显示为美元符号
4	L	根据不同语言环境不同，自动选择货币符号
5	.	显示小数点
6	,	显示千位符

例如：将金额2389042809，格式化为标准金额显示为：2,389,042,809.00
select to_char(2389042809,'L999,999,999,999.99')
        ,to_char(2389042809,'$000,000,000,000.00')
from dual;
在开发之中，to_char()函数很常用，建议掌握。
2.to_date()函数
这个函数主要是将字符串变为日期数据，而改变的过程里依然需要之前to_char()函数里出现的相关标记。
select to_date('2017-09-05','yyyy-mm-dd')from dual;
3.to_number()将字符串变为数字
select to_number('060')+to_number('100.00')
from dual;
select '60'+'100.00' from dual;--在oracle里支持数据类型的自动转换

五、通用函数(最基本的是nvl()和decode()函数)
1.nvl()函数，处理null,将null变为0
select ename,sal+comm ,sal,comm from emp;
注意：--null+任意数字还是null
select ename,sal+nvl(comm,0),sal,comm from emp;
2.nvl2()函数是在oracle9i之后增加的一个新的功能函数，相比较nvl()函数，nvl2()函数可以同时对
为null或者不为null进行分别判断并返回不同的结果。
select ename,nvl2(comm,sal+comm,sal) from dual;
3.nullif(表达式一，表达式二)函数的主要功能判断两个表达式的结果是否相等，
如果相等则返回null，如果不相等则返回表达式一
select nullif(1,1),nullif(2,3) from dual;
4.decode()函数是oracle中最有特色的函数，decode()函数类似于程序的if..elsse if...else,
但判断的内容都是一个具体的值。decode()函数的语法如下：
decode(列|表达式值1，输出结果，值2，输出结果...默认值)
Java语言之中，if..else判断都是逻辑条件，而在decode()里面判断的是数值。
select decode(2,1,'表达式一',2,'表达式二') from dual;
要求可以查询雇员的姓名，职位。但是要求将所有的职位信息都替换为中文显示
select ename,decode(job,'CLERK','业务员','SALESMAN','销售','MANAGER','经理',
'ANALYST','分析员','PRESIDENT','总裁')as 职业 ,sal from emp; 
5.case表达式。是oracle9i引入的，功能与decode（）函数有些类似，都是执行多条件判断。
不过严格来讲case表达式本身并不属于一种函数的范畴，它的主要功能是针对给定的列或者是
字段一次判断，在when 中编写判断语句，而在then 中编写处理语句，最后如果都不满足则使用
else进行处理。
要求显示每个雇员的姓名、工资、职位、同时显示新的工资
（新工资的标准为：办事员增长10%，销售增长20%，经理增长30%，其他人员增长50%）。
select ename,sal,
  case job when 'CLERK' then sal*1.1
          when 'SALESMAN' THEN sal*1.2
          when 'MANAGER' THEN sal* 1.3
  else sal*1.5
  end 新工资 from emp;      
 6. coalesce()函数
coalesce(表达式1，表达式2，..表达式3)。
主要功能是对null进行操作，采用依次判断表达式的方式完成。
如果表达式1为空，则显示表达式2的内容。如果表达式2为空，则显示表达式3的内容，依次类推
判断到最后还是空，则最终显示为空。
select  ename,sal,comm,
coalesce(comm,100,2000) ,coalesce(comm,null,null)from emp;