oracle函数命令

单行函数：

只有一个参数输入，只有一个结果输出。多行函数或者分组函数：可以有多个参数输入，只有一个结果输出。

#使用哑表dual进行查询
lower(转小写)，upper(转大写)，initcap(每个单词的首字母大写)
select lower('www.BAIDU.com') 小写百度 from dual;
select upper('www.BAIDU.com') 大写网址 from dual;
select initcap('www.BAIDU.com') 首字母大写 from dual;
concat(拼接字符)
select concat('hello','你好') 结果 from dual;
select 'hello' || '你好' || '世界' 结果 from dual;
select concat('hello',concat('你好','世界')) from dual;
substr(截取字符)，中英文统一处理
#包含第五个字符
select substr('hello你好',5,3) from dual;
length(字符个数),lengthb(字节个数)  英文一个字节，中文两个字节
#结果为7
select length('hello你好') from dual;
#结果为9
select lengthb('hello你好') from dual;
instr/lpad/rpad函数，从左向右找第一次出现的位置，从1开始
#结果为5，找不到返回0，大小写敏感
select instr('helloworld','o') from dual;
#结果为#####hello  左填充
select lpad('hello','10','#') from dual;
#结果为hello#####  右填充
select rpad('hello','10','#') from dual;
trim/replace函数
#结果为he ll o   作用：忽略字符串开头的*号
select trim('*' from '**he ll o') from dual;
#结果为heLLo     作用：替代字符
select replace('hello','l','L') from dual;

【7】round/trunc/mod函数作用于数值型
#结果为3.142
select round(3.1415,3) from dual;
#结果为3.141 相当于java中的floor向下取整
select trunc(3.1415,3) from dual;
#结果为1
select mod(10,3) from dual;

-------------------------------------
#测试日期为：09-9月-16
-------------------------------------
#round作用于日期型（month） 1-15号舍去   16-30/31/28/29向前进一个月
#结果为：01-10月-16
select round(sysdate,'month') from dual;
#round作用于日期型（year）
#结果为：01-1月 -17
select round(sysdate,'year') from dual;
#trunc作用于日期型（month）
#结果为：01-9月-16
select trunc(sysdate,'month') from dual;
#trunc作用于日期型（year）
#结果为：01-1月 -16
select trunc(sysdate,'year') from dual;
显示昨天，今天，明天的日期，日期类型 +- 数值 = 日期类型
select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;
以年和月形式显示员工近似工龄，日期-日期=数值，假设：一年以365天计算，一月以30天计算
select ename "姓名",round(sysdate-hiredate,0)/365 "天数" from emp;
使用months_between函数，精确计算到年底还有多少个月
select months_between('31-12月-15',sysdate) from dual;
使用months_between函数，以精确月形式显示员工工龄
select ename "姓名",months_between(sysdate,hiredate) "精确月工龄" from emp;
测试add_months函数，下个月今天是多少号
select add_months(sysdate,1) from dual;
测试add_months函数，上个月今天是多少号
select add_months(sysdate,-1) from dual;
测试next_day函数，从今天开始算，下一个星期三是多少号【中文平台】
select next_day(sysdate,'星期三') from dual;
测试next_day函数，从今天开始算，下下一个星期三是多少号【中文平台】
select next_day(next_day(sysdate,'星期三'),'星期三') from dual;
测试next_day函数，从今天开始算，下一个星期三的下一个星期日是多少号【中文平台】
select next_day(next_day(sysdate,'星期三'),'星期日') from dual;
测试last_day函数，本月最后一天是多少号
select last_day(sysdate) from dual;
测试last_day函数，本月倒数第二天是多少号
select last_day(sysdate)-1 from dual;
测试last_day函数，下一个月最后一天是多少号
select last_day(add_months(sysdate,1)) from dual;
测试last_day函数，上一个月最后一天是多少号

select last_day(add_months(sysdate,-1)) from dual;

通用函数和条件判断函数

使用NVL(a,b)通用函数，统计员工年收入，NVL()作用于任何类型，即(number/varchar2/date)
通用函数：参数类型可以是number或varchar2或date类型
select ename,sal*12+NVL(comm,0) from emp;

使用NVL2(a,b,c)通用函数，如果a不为NULL，取b值，否则取c值，统计员工年收入
select ename,sal*12+NVL2(comm,comm,0) from emp;

使用NULLIF(a,b)通用函数，在类型一致的情况下，如果a与b相同，返回NULL，否则返回a，比较10和10.0是否相同
select NULLIF(10,10.0) from dual;

使用SQL99标准通用语法中的case表达式，将职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
case 字段
     when 条件1 then 表达式1
     when 条件2 then 表达式2
     else 表达式n
end

select ename "姓名",job "职位",sal "涨前工资",
       case job
        when 'ANALYST' then sal+1000
        when 'MANAGER' then sal+800
            else sal+400
       end "涨后工资"
from emp; 

使用oracle专用语法中的 decode()函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)
select ename "姓名",job "职位",sal "涨前工资",
       decode(job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "涨后工资"

from emp;

多行函数

函数：oracle服务器先事写好的一段具有一定功能的程序片段，内置于oracle服务器，供用户调用
单行函数：输入一个参数，输出一个结果，例如：upper('baidu.com')->BAIDU.COM
多行函数：输入多个参数，或者是内部扫描多次，输出一个结果，例如：count(*)->14

多行函数：count/sum/avg/max/min

统计emp表中员工总人数
select count(*) from emp;
*号适用于表字段较少的情况下，如果字段较多，扫描多间多，效率低，项目中提倡使用某一个非null唯一的字段，通常是主键

统计公司有多少个不重复的部门
select count(distinct deptno) from emp;

统计有佣金的员工人数
select count(comm) from emp;
注意：今天讲的这些多个行函数，不统计NULL值

员工总工资，平均工资，四舍五入，保留小数点后0位
select sum(sal) "总工资",round(avg(sal),0) "平均工资"
from emp;

查询员工表中最高工资，最低工资
select max(sal) "最高工资",min(sal) "最低工资"
from emp;

入职最早，入职最晚员工
select max(hiredate) "最晚入职时间",min(hiredate) "最早入职时间"
from emp;

按部门求出该部门平均工资，且平均工资取整数，采用截断
select deptno "部门编号",trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno;

(继续)查询部门平均工资大于2000元的部门
select deptno "部门编号",trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0) > 2000;

(继续)按部门平均工资降序排列
select deptno "部门编号",trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0) > 2000
order by 2 desc;

除10号部门外，查询部门平均工资大于2000元的部门，方式一【having deptno<>10】
select deptno,avg(sal)
from emp
group by deptno
having deptno<>10;

除10号部门外，查询部门平均工资大于2000元的部门，方式二【where deptno<>10】
select deptno,avg(sal)
from emp
where deptno<>10
group by deptno;
提倡

显示部门平均工资的最大值
select max(avg(sal)) "部门平均工资的最大值"
from emp
group by deptno;

思考：显示部门平均工资的最大值和该部门编号?
select max(avg(sal)) "部门平均工资的最大值",deptno "部门编号"
from emp
group by deptno;
错误

group by 子句的细节：
1）在select子句中出现的非多行函数的所有列，【必须】出现在group by子句中
2）在group by子句中出现的所有列，【可出现可不现】在select子句中

where和having的区别：
where：
1）行过滤器
2）针对原始的记录
3）跟在from后面
4）where可省
5）先执行

having：
1）组过滤器
2）针对分组后的记录
3）跟在group by后面
4）having可省
5）后执行

oracle中综合语法：
1）select子句-----必须
2）from子句-------必须，不知写什么表了，就写dual
3）where子句------可选
4）group by子句---可选
5）having子句-----可选
6）order by 子句--可选，如果出现列名，别名，表达式，字段