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"日"');