Oracle学习笔记 -- day04 单行函数字符、单行函数转换、多行函数

注意：oracle函数大全文档详见资源处

一、单行函数之字符函数

1、伪表

--dual  伪表  目的：配合查询select * from dual select 1+1 from dualselect * from emp

2、字符函数

replace--替换'jagflkascaas'   agf 替换成 XXXselect replace('jagflkascaas','agf','XXX') from dualsubstr--截取   --开始位置0和1是一样的'jagflkascaas'    取出gflkascaasselect substr('jagflkascaas',3) from dual --agflkascaas'jagflkascaas'    取出jagselect substr('jagflkascaas',1,3) from dual select substr('jagflkascaas',0,3) from dual concat--连接'aaaa','bbbb'select 'aaaa','bbbb' from dualselect 'aaaa'||'bbbb' from dualselect concat('aaaa','bbbb','CCC') from dual --只能有两个参数length--长度select length('qwer') from dual

3、数值函数

round --四舍五入select round(56.349) from dual  --56select round(56.549) from dual  --57select round(56.349,2) from dual--56.35select round(56.349,-1) from dual--60select round(53.349,-1) from dual--50trunc--截断select trunc(56.349) from dual  --56select trunc(56.549) from dual  --56select trunc(56.349,2) from dual--56.34select trunc(56.349,-1) from dual--50select trunc(53.349,-1) from dual--50mod  --取余select mod(10,3) from dualselect mod(10,2) from dualselect mod(10,0) from dual--10

4、日期函数

add_months(时间,数值)当前时间  sysdateselect sysdate from dual查询3个月以后的时间select add_months(sysdate,3) from dualselect add_months(sysdate,-3) from dualselect add_months(sysdate,1) from dual时间是可以相加减的时间-时间=数值    数值的单位是：天时间+数值=时间--计算每个员工的入职天数  -- sysdate：当前系统时间select empno,ename,hiredate , sysdate-hiredate from emp--计算每个员工的入职月数months_between  --  计算两个日期之间的月份差  参数1：当前时间   参数2：要计算的初始时间select empno,ename,hiredate , months_between(sysdate,hiredate) from emp

二、单行函数之转换函数

1、转换函数

to_date--查询1980-01-01到1985-12-31入职的员工select * from emp where hiredate between to_date('1980-01-01','yyyy-MM-dd') and to_date('1985-12-31','yyyy-MM-dd')to_charselect to_char(sysdate,'yyyy-MM-dd') from dualselect to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dualselect to_char(sysdate,'yyyy'),to_char(sysdate,'mm'),to_char(sysdate,'dd'),to_char(sysdate,'day') --星期from dual--查询1980和1987入职的员工select * from emp where to_char(hiredate,'yyyy')='1980' or  to_char(hiredate,'yyyy')='1987'to_number -- 不重要select 'abc'+'abc' from dual  -- 报错select '123'+'123' from dualselect * from emp where deptno='30'

2、通用函数

nvl -- 滤空函数select sal*12+nvl(comm,0) from empdecode --类似于条件表达式  只有Oracle有select job,decode(job,'CLERK','业务员','SALESMAN','销售员','其他') from emp--case表达式，重要语法：case 字段 when 值  then 显示的值.....end-- 使用表达式  select job,case job when 'CLERK' then '业务员'when 'SALESMAN' then '销售员'else  '其他'end,  -- 使用decode表达式decode(job,'CLERK','业务员','SALESMAN','销售员','其他') from emp

三、多行函数

1、多行函数

多行函数  聚合函数   组函数count sum avg max min分组：group by查询每个部门的平均工资select deptno ,avg(sal) from emp group by deptno查询每个部门的最高工资select deptno ,max(sal) from emp group by deptno查询每个部门的最低工资select deptno ,min(sal) from emp group by deptno查询每个部门的人数select deptno ,count(*) from emp group by deptno查询每个部门的总工资select deptno ,sum(sal) from emp group by deptno查询部门的最低工资大于800的部门编号select deptno ,min(sal) from emp group by deptno having min(sal)>800where  (前)group by  (中)having (后)