oracle number(4_3),Oracle基础（第三节）

--单行函数

--函数：是一种工具，提供材料(参数)后，根据材料产生结果(返回值)

--单行函数：一行记录在函数使用中就会产生一个结果

--分组函数：一组记录在函数使用中只能产生一个结果

--dual表: 本身只有一个一行一列的数据

select * from dual;

--dual表的主要作用是为了补全SQL语句使用的

--Oracle中 select语句执行时，必须有from才能执行

select 2+3

from dual;

--参数含义

--str 或 s 文本类型的参数

--num 或 n 数值类型的参数

--date 或 d 日期类型的参数

--expr 表达式

--1.文本/字符函数

--(1)大小写转换(重要)

-- 转大写upper(str)结果：str

-- 将传入的str转换成全部为大写形式

select upper('abCd'),upper('张'),upper('123')

from dual;

-- 转小写

-- 转大写lower(str)结果：str

-- 将传入的str转换成全部为小写形式

select lower('abCd'),lower('张'),lower('123')

from dual;

-- 转首字母大写

-- 转大写initcap(str) 结果：str

-- 将传入的str转换成每个单词都是首字母大写

select initcap('abCd'),initcap('abCd xYZx'), initcap('张'),initcap('123')

from dual;

--练习：显示emp表中所有的员工名字，首字母大写

select initcap(ename)

from emp;

select *

from emp

where lower(ename) like lower('%sCOTt%');

--(2)字符拼接

--字符串拼接||

select ename || '的职位是' || job

from emp;

--concat(str1,str2)将str1和str2拼接在一起

select concat(ename,'的职位是')

from emp;

--使用函数嵌套方式:

select concat(concat(ename,'的职位是'),job)

from emp;

--(3)字符截取(重要)

--substr(str,n1,n2)在文本str中，从第n1个字符起，截取n2个长度的字符

select substr('I LIKE GIRL',3,4)

from dual;

--练习：查询emp表中员工职位，只显示职位的前三个字符:

select substr(job,1,3)

from emp;

--文本长度length(str)得到文本的长度 num

--练习：查询emp表中员工职位，只显示职位的最后三个字符

select job, substr(job,length(job)-2,3)

from emp;

--查询员工名字中后三个字符中包含T的员工

select *

from emp

where substr(ename,length(ename)-2,3) like '%T%'

--(4)字符查找

--instr(str1,str2,n1,n2)

--在str1文本中从n1位置起，寻找str2文本第n2次出现的位置:

select 'I LIKE GIRL'

from dual;

--在文本'I LIKE GIRL'中第3个字符后，寻找文本'I'第2次出现的位置:

select instr('I LIKE GIRL','I',3,2)

from dual;

--(5)字符补充

-- 左补充

-- lpad(str1,n,str2): 使用文本str2在str1左侧进行补充，将str1的长度补充至n为止

-- 右补充

-- rpad(str1,n,str2): 使用文本str2在str1右侧进行补充，将str1的长度补充至n为止

select lpad('ABC',10,'*'),rpad('ABC',10,'*')

from dual;

--(6)字符替换

--replace(str1,str2,str3): 在str1中，使用str3替换其中的str2:

select replace('haha hehe xixi hehe','hehe','*****')

from dual;

--(7)字符裁剪

--trim(str): 去掉str两端多余的空格:

select ' A B CD ', trim(' A B CD '), length(' A B CD '),length(trim(' A B CD '))

from dual;

--2.数值函数

--(1)取余(掌握) 7 / 3 = 2...1 7 / 3 = 2 取余% 7%3=1

--基本算法 按照正数得出结果，符号取决于被除数 5%3=2 -5%3=-2 5%-3=2 -5%-3=-2

--mod(n1,n2) 计算n1与n2取余后的结果:

select mod(5,3),mod(5,-3),mod(-5,3),mod(-5,-3)

from dual;

select trunc(mod(345/10,10))

from dual;

--(2)四舍五入(重要)

--round(n1,n2): 对n1进行四舍五入，精确到小数点后n2位(n2可以是0或负数):

select 3.1415926, round(3.1415926,2), round(3.1415926,3)

from dual;

select round(3.1415926,0), round(3.1415926)

from dual

select round(3.1415926,-1),round(5.1415926,-1),round(67,-2),round(49,-2)

from dual

--(3)进一法

--ceil(n1):对n1进行取整操作，舍弃小数部分，整数部分加1:

select ceil(3.0001),ceil(3.9999),ceil(3.0000)

from dual;

--(4)截取法

--trunc(n1):对n1进行截取操作，舍弃小数部分

--trunc(n1,n2):对n1进行截取操作，舍弃小数点n2后的部分(n2可以是0或负数):

select trunc(3.0001),trunc(3.9999),trunc(3.0000)

from dual;

select trunc(3.12345,2),trunc(3.12345,0),trunc(3.12345,-1),trunc(19.12345,-1)

from dual;

--3.日期/时间函数

--基础操作：时间的加减法

--直接加减操作是以“天”为单位进行加减

-- today + 1 = 明天

-- today - 1 = 昨天

-- today + 1/24 = 今天的下一个小时

--(1)系统时间(重要)

--sysdate 系统(服务器)当前时间 关键字 mysql: now():

select sysdate from dual;

select sysdate+1, sysdate-1, sysdate+1/24

from dual;

--(2)计算两个时间的月份差(重要)

--months_between(date1,date2):计算date1与date2之间相隔的月份，

--date1大于date2结果得到正数，反之结果得到负数:

select months_between('31-12月-18', sysdate),months_between(sysdate, '31-12月-18')

from dual;

--练习：计算emp表中各个员工的司龄:

select ename, hiredate, trunc(months_between(sysdate, hiredate)/12)

from emp;

--(3)为某个时间添加月份(某个日期几个月之后的时间)

--add_months(date, num)获得date日期num个月之后的时间 num可以是负数:

select add_months(sysdate, 7),add_months(sysdate, -7)

from dual;

--比如试用期是6个月，查询emp表中，所有员工转正的日期:

select ename, hiredate, add_months(hiredate, 6)

from emp;

--(4)求得某个月的最后一天

--last_day(date)：求得date所在的月份的最后1天:

select last_day(sysdate)

from dual;

select last_day(sysdate)-2, last_day(sysdate)+3

from dual;

--求得下一个月的倒数第5天:

select add_months(last_day(sysdate),1)-4

from dual;

select last_day(last_day(sysdate)+1) - 4

from dual;

--(5)求得下一个星期几

--next_day(date, num) 求得date日期之后第一个星期几(num：1-7 日-六):

select next_day(sysdate, 5)

from dual;

--4.通用函数

--(1)nvl

--(2)nv2

--(3)nullif

--(4)decode

--5.转换函数

number(数字)

| |

to_number | | to_char

| |

char(文本)

| |

to_date | | to_char

| |

date(日期)

--(1)to_number 从文本向数字进行转换

-- fmt是一种文本，表示一种特定的格式

-- fmt格式转义字符

-- 0表示一位数字

-- 9 表示一位数字(通常使用9)

-- $表示美元符号

-- L 表示本地货币符号

-- , 表示三位分隔符

-- . 表示小数点

-- fmt规定的数字的范围至少应该大于实际数字的范围，否则语句会报错

-- to_number(str,fmt)：将str按照fmt规定格式进行识别，将识别的结果转换成数字

select

'$2,123,456,789',

to_number('$2,123,456,789', '$9,999,999,999'),

to_number('$2,123,456,789', '$9,999,999,999.99'),

to_number('$2,123,456,789', '$999,999,999,999.99'),

to_number('￥2,123,456,789', 'L9,999,999,999')

from dual;

--(2)to_char

--(2.1)to_char(num, fmt) 将num数字按照fmt规定的格式，显示成文本

select ename, sal, to_char(sal, '$9,999,999.99')

from emp;

--(2.2)to_char(date, fmt)将date日期按照fmt规定的格式，显示成文本

select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"')

from dual;

select to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD')

from dual;

--(3)to_date

-- fmt

-- YYYY:四位数年(使用)

-- YY：两位数无世纪转换的年

-- RR：两位数有世纪转换的年

-- MM：月

-- DD：日

-- HH24: 24进制小时

-- HH12：12进制小时

-- MI：分

-- SS：秒

--to_date(str,fmt)将str按照fmt规定格式进行识别，将识别的结果转换成日期

select

to_date('2018-04-08','YYYY-MM-DD'),

to_date('2018-05-03 22:12:35', 'YYYY-MM-DD HH24:MI:SS')

from dual;

--练习：查询emp表中在1982年期间入职的员工，要求使用to_date方式:

select *

from emp

where hiredate between '01-1月-82' and '31-12月-82';

select *

from emp

where hiredate between to_date('1982-01-01','YYYY-MM-DD') and to_date('1982-12-31','YYYY-MM-DD');

--比如日期文本中出现了特殊的文字 比如：年，月，日

--在fmt中 使用双引号将特殊文字括起来:

select *

from emp

where hiredate between to_date('1982年01月01日','YYYY"年"MM"月"DD"日"')

and to_date('1982年12月31日','YYYY"年"MM"月"DD"日"');