1.函数概念
函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。
函数根据处理的数据分为单行函数和聚合函数（组函数）。
单行函数对单个数值进行操作，并返回一个值。
组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句
2.字符相关函数
-- dual用于测试 select * from dual; -- 1.字符串连接 select concat('aa','12') from dual; select 'aa'||'12' from dual; -- 2.首字母大写 select initcap('abc') from dual; --- 把大写转化小写 select lower('ABc') from dual; select upper('abc') from dual; -- 把所有员工的姓名小写输出 select lower(e.ename),e.empno from emp e -- 3.填充字符lpad/rpad select lpad('sxt',5,'*') from dual; select rpad('sxt',5,'*') from dual; -- 4.去掉空白字符 select ' kallen' from dual; select ltrim(' kallen',' ') from dual; select rtrim(' kallen ',' ') from dual; -- trim 删除左右两边的字符 select trim('a' from 'abc') from dual; -- 5.求子串 substr(str,loc,len)-->loc从1开始 select substr('abcd',2,2) from dual; -- 6.查找字符串 /* 如果找到返回>=1的索引；如果没找到返回0 */ select instr('abcd','b') from dual; -- 7.求长度 select length('abcd') from dual;
3.数值型函数
-- 数值型函数 -- 四舍五入round(x,y)对x保留y为小数 select round(23.652) from dual; select round(23.652,1) from dual; select round(25.652,-1) from dual; -- 返回x按精度y截取后的值 select trunc(23.652) from dual; select trunc(23.652,2) from dual; select trunc(23.652,-1) from dual; -- mod（x，y）求余数 select mod(9,2) from dual; -- ceil 向上取整 select ceil(1.9) from dual; -- floor 向下取整 select floor(1.9) from dual;
4.日期时间函数
4.1 日期函数
-- 返回系统当前时间 select sysdate from dual; -- 返回当前会话时区中的当前日期 select current_date from dual; -- 添加月数 select add_months(sysdate,1) from dual; -- 返回两个时间相差的月数 select months_between(sysdate,add_months(sysdate,2)) from dual; -- 需求:查询工作年限在30年以上 select e.ename,e.hiredate from emp e where months_between(sysdate,e.hiredate)/12 > 30 -- 返回date所在月份最后的一天 select last_day(add_months(sysdate,1)) from dual; -- next_day(date1，week) 返回date1下周星期几的日期 select sysdate "当时日期",next_day(sysdate,'Monday') "下周星期一" from dual; -- 查询会话的环境参数 select * from nls_session_parameters;
4.2日期计算函数
两个时间进行四则运算的单位是天。
select sysdate+2 from dual; select sysdate-2 from dual;
5.转换函数
转换函数就是把字符、日期、数值型数据进行相互转换。类型转换分两种：隐式类型转换和显式类型转换。
5.1隐式类型转换
-- 字符隐式转换成数值 select '100' - 10 from dual; -- 字符隐式转化为日期 -- DD-MON-RR 默认的日期格式 select 1 from dual where sysdate > '13-May-19'; -- 查date format格式 select * from nls_session_parameters;
5.2显示类型转换
5.2.1 to_char
把日期转化成字符
把数值格式化成字符串
-- to_char -- 【1】把日期转化成字符 -- 按照默认格式DD-MON-RR select to_char(sysdate) from dual; -- 按指定格式 select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t from dual; -- 【2】把数值格式化成字符串 select to_char(12345,'99999.99') from dual; select to_char(12345,'99,999.99') from dual; select to_char(12345,'999,999.99') from dual; -- 不够位置0 select to_char(12345,'000,000.00') from dual; -- 格式化成美元显示 select to_char(12345,'$000,000.00') from dual;
5.2.2 to_number、to_date
-- to_number select to_number('$12,345','$99,999') from dual; select to_number('$12,345.12','$99,999.99') from dual; -- to_date select to_date('14-May-19','DD-MON-RR') from dual; select to_date('2004-09-19','YYYY-MM-DD') from dual;
-- 查询雇用期满6个月的下一个周一的日期。 select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'Monday') from emp e where months_between(sysdate,e.hiredate) > 6 -- 查询公司boss select e.ename || nvl(to_char(e.mgr),' is boss') from emp e where e.mgr is null
5.2.3 decode/case when
decode(条件,值1,“返回值1”, 值2,“返回值2”,,,“默认值”)
-- 需求:查询员工所在的部门名称 select e.ename, e.deptno, decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知') from emp e;
case when：
-- case when select e.ename, e.deptno, case e.deptno when 10 then '部门1' when 20 then '部门2' when 30 then '部门3' else '未知' end from emp e;
-- 需求:对各个部门进行涨薪，10->1.1 20->1.2 30->1.3 其他->1.0 select e.deptno, e.ename, e.sal "涨薪前", decode(e.deptno,10,e.sal*1.1,20,e.sal*1.2,30,e.sal*1.3,e.sal) "涨薪后" from emp e -- 需求:根据工资分布输出以下信息 /* <1000 真屌丝 (1001,2000] 屌丝 (2001,3000] 白领 (3001,5000] 高富帅 (5001,10000] 土豪 */ select e.ename "姓名", e.sal "工资", case when e.sal <= 1000 then '真屌丝' when e.sal <= 2000 then '屌丝' when e.sal <= 3000 then '白领' when e.sal <= 5000 then '高富帅' when e.sal <= 10000 then '土豪' else '未知' end "描述" from emp e;
总结：decode 多用于等值匹配；case when可以用于等值，多用于条件分支。
6.组函数
组函数把多行数据经过运算后返回单个值。也称聚合函数。
-- 求公司雇员的数量 select count(*) from emp e; select count(e.empno) from emp e; select count(1) from emp e; -- avg:对多个记录的某个字段求平均值 -- 需求:求底薪的平均值 select avg(e.sal) from emp e; -- 需求:求雇员的最高薪资/最低薪资 select max(e.sal),min(e.sal),avg(e.sal) from emp e; -- 需求:求公司一个月的员工基本开销 select sum(e.sal) from emp e;
注意:
[1] 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。
[2] 聚合函数对字段是null的值进行忽略。count(*)
-- 求有津贴的员工的数量 select count(e.comm) from emp e;
[3] max/min 适合任意数据类型，sum/avg 只适用于数值类型。
聚合函数的结果可以作为其他查询条件。
-- 最早入职的员工 select e.ename,e.hiredate from emp e where e.hiredate = (select min(e.hiredate) from emp e);