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