oracle函数

/*
Oralce函数大致分为：
  一.单行函数（标量函数）
    1.日期函数
    2.字符函数
    3.数学函数
    4.转换函数
    5.其他函数
  二.分组函数（聚合函数）
  三.分析函数
*/

-----------------1.日期函数-----------------
--dual表：辅助查询
--使用dual表显示当前时间sysdate
select sysdate from dual;

--加月份add_months(时间,月份)
--时间格式 25-9月-2019或者 25/9月/2019
select add_months(sysdate,1) from dual;
select add_months('1-1月-2020',1) from dual;
select add_months(to_date('2000-10-1','yyyy-mm-dd'),1) from dual;

select * from dual;
--相差月份months_between(时间1，时间2)  结果是时间1减去时间2 
select months_between(sysdate,'1-1月-2021') from dual;

--extract(year/month/day from sysdate)获得完整日期的一个部分
select extract(month from sysdate) from dual;

--相差年份 --找到emp表中的员工距离今天一共入职多少年
--1.获取今年的年份
--2.获取入职的年份
--3.今年年份减去入职年份
select extract(year from sysdate) from emp;
select extract(year from hiredate) from emp;

select  emp.*,extract(year from sysdate)-extract(year from hiredate) as ys from emp;

select emp.*,extract(year from hiredate) from emp;
--查询emp表员工在公司工作了多少天
--用当前日期sysdate 减去 入职日期  得到得就是天数
select emp.*,sysdate-hiredate as days from emp;

--查询员工入职日是星期几
--得到星期to_char(时间,'day')
select to_char(sysdate,'day') from dual;

select emp.*,to_char(hiredate,'day') as week from emp;

--查询员工入职了多少个月 
--months_between(时间1,时间2) 
select emp.*,months_between(sysdate,hiredate) as ms from emp;

--varchar2类型做时间字符串,方便,但是不安全
--开发便捷,但是程序不安全
--java.util.date
--java.sql.date

--返回指定时间月的最后一天
--求月末last_day(时间) 
--'1-10月-1949' 自定义时间请注意格式
select last_day(sysdate) from dual;

-- 日期N后的第一个day of week(dow)
-- next_day(开始时间，N) 
-- N可以用 中文 '星期日' 
-- 或者用 1-7 的数字,星期日为1
select next_day(sysdate,6) from dual;

--查询2月入职的员工 -- 条件查询
--extract(month from 字段)
select * from emp where extract(month from hiredate) =2;

select extract(month from hiredate) from emp;

-----------------2.字符串函数-----------------
--字符串截
--substr('字符串',开始下标,截取长度)
--下标从1开始
--java,c#,js....oracle
select substr('长沙华瑞IT教育',5,2) from dual;

--员工姓名的小写形式
--lower小写(字段)
--upper大写
select emp.*,lower(ename) from emp;

select upper('zdlawp') from dual;

--姓名长度为5的员工信息
--length(字段) 
select emp.*,length(ename) from emp where length(ename)=5;

--查询所有员工的姓名的前三个字符
--substr(字段,开始位置,长度)
select emp.*,substr(ename,1,3) from emp;

--替换字符串replace(字符串，要替换的字符，[替换后的字符])
--如果没有指定替换后的字符，则表示删除所有要替换的字符。
--将姓名S替换为5 
--replace('原字符串','需要替换的字符串','替换后的字符串')
select emp.*,replace(ename,'S','A') from emp;

--求字符串'    asdf   asdfs   asdfsdf    '里面有多少空格
--1.先求出字符串总长度 length('字符串')
--2.再求出去掉空格后字符串的长度
select length('    asdf   asdfs   asdfsdf    ') from dual;
select length(replace('    asdf   asdfs   asdfsdf    ',' ')) from dual;
select length('    asdf   asdfs   asdfsdf    ')-length(replace('    asdf   asdfs   asdfsdf    ',' ')) from dual;

--3.相减
--字符串拼接concat(字符串1，字符串2)相当于||
--String str="abc";
--str=str.concat("def");
select concat('abc','def') from dual;
select 'abc' || 'def' from dual;

--select * from dual
--求字符对应的ascii码 ascii对应的字符
--ascii('字符')  转换成数字
--chr(数字) 转换成字符
--97 a
--65 A
select  ascii('A') from dual;
select chr(100) from dual;

--select from dual;

-----------------3.数学函数-----------------
--随机数 dbms_random.value(范围1,范围2)
--97~122 a-z
--产生a-z中的任意一个字母
select chr(dbms_random.value(97,122)) from dual;

--取整（四舍五入) round(N)
select round(10.5) from dual;

--取整(小于N的最大整数) floor(N)
select floor(10.9) from dual;

--取整(大于N的最小整数) ceil(N)
select ceil(10.1) from dual;

-- 银行家算法(银行稳赚不亏)
-- 1.001

--取余数 mod(被除数,除数) 取模
select mod(10,3) from dual;

--四舍五入保留M位小数 round(N,M)  
--N数字 M位数
select round(10.445,2) from dual;

-----------------4.转换函数-----------------
--to_char将数字或者是日期转变成指定格式的字符串
--to_char(d|n[,fmt]) d-日期，n-数字，fmt-指定日期或数字的格式
-- 将系统时间转变成 'xxxx年x月xx日'  格式输出 
--to_char(sysdate,'yyyy"年"mm"月"dd"日"')
--如果要家中文 需要""
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;

--年 yyyy"年"
--月 mm
--日 dd
--星期几 day
--时间 hh:mi:ss 12小时制
--时间 hh24:mi:ss 24小时制
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'day') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;

select extract(year from sysdate) from dual;
-- 印象笔记
-- 网易云笔记

--转金额to_char(数字,'$显示格式')
--$999.999
--$99,999.9
--$1,600 在工资前面加上 美元符号$
select to_char(sal,'$999,999.9') from emp;

-- to_date将日期"字符串" 转变为日期类型
-- to_date(时间,'yyyy-mm-dd')
-- to_date 将字符串 '2014年10-23' 转变成时间日期格式

select * from emp;
-----------------5.其他函数-----------------
--null值转成指定的值 
--nvl(参1,参2) 如果参1为空返回参2，否则返回参1。
--如果奖金为空，则发500块
select emp.*,nvl(comm,500) from emp;

--nvl2(参1，参2，参3),
--如果参1不为空，则取参2值，否则取参3值 
--员工奖金为为空的奖励500,其他的奖励200
select emp.*,nvl2(comm,100,500) from emp;

--计算所有员工的工资加奖金

--nullif（参1，参2）
--如果参1与参2相等，
--则返回null,否则返回 参数1
select emp.*,nullif(comm,300) from emp;

--分组函数(聚合函数)
--count()统计行
--avg()平均值
--sum()综合
--工资高于平均工资的人数
select count(*) from emp where sal>(select avg(sal) from emp)

--统计总共有多少职位distinct
select count(distinct(job)) from emp;

--avg()求平均值

--max()最大值

--min()最小值

--sum()求和

--求各部门平均工资和部门编号,要求显示工资大于2000
--部门分组
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;