oracle单行函数（字符函数、）、组函数

1、单行函数：

（1）字符函数：

--------------转小写lower：

--------------转大写upper：

--------------首字母大写initcap：

select lower('hellO WORld') ,upper('hellO WORld') ,initcap('hello world') 
 from dual;

--------------------------substr(a,b) 从a中，第b位开始取，取右边所有的字符

select substr ('hello world',4) from dual;

 ----------------------------substr(a,b,c) 从a中，第b位开始取，取c位

 select substr ('hello world',4,3) from dual;

 

---------------------------length 字符数 lengthb 字节数

select length('Hello World') , lengthb('Hello World') from dual;

 

------------------------instr 在母串中查找子串，找到返回下标（从1开始），否则返回0
 select instr('Hello World','ll') from dual;

 ---------------------------lpad 左填充  rpad 右填充

 select lpad('abcd',10,'*') ,rpad('abcd',10,'*')  from dual;

 

-------------------------trim 去掉前后指定的字符

 select trim('H' from 'Hello WorldH') from dual;

 

 --------------replace 替换

（2）数字函数

 -----------------round和trunc  取小数位

 

 

----------------------日期函数sysdate

 

改变日期函数输出的格式：

select  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select systimestamp from dual;                         systimestamp时间戳

select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') from dual;                              systimestamp时间戳

  select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual;

员工的工龄

select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年from emp;

 

这个月最后一天：

 计算工龄： months_between

 

 114个月后：

select add_months(sysdate,114) from dual;

--从今天开始算（2012.10.22星期一）,下一个星期一
 select next_day(sysdate,'星期一') from dual;

 

 --从今天开始算（2012.10.22星期一）,下一个星期二
 select next_day(sysdate,'星期二') from dual;

显式转换

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

 

查询员工的薪水： 货币符号，两位小数，千位符

 

通用函数
 --nvl2(a,b,c) 当a=null时，返回c，否则返回b
 select sal*12+nvl2(comm,comm,0) from emp;

 

 --nullif(a,b) 当a=b时，返回null，否则返回a

 

 -----------------------COALESCE 从左至右找到第一个不为null的值

select comm,sal,COALESCE(comm,sal) from emp; 

 

根据职位涨工资 总裁1000 经理800 其他400

 

select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
 when 'MANAGER' then sal+800
 else sal+400
 end 涨后薪水

from emp;

select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,'MANAGER', sal+800,sal+400)涨后薪

水 from emp

2、组函数

 sum\count\avg\

 查询平均工资大于2000的部门

 

select deptno,avg(sal)
 from emp
 group by deptno
 having avg(sal)>2000

 /

 

 having 过滤分组

 

having和where的区别
 --求10号部门的平均工资
 select deptno,avg(sal)
 from emp
 group by deptno
 having deptno=10;