Home

hogen

Follow Excellence. Success will chase you.

Home About Github Email

oracle 学习之基础篇(三):单行函数和多行函数

单行函数:只有一个参数输入,只有一个结果输出:例如:upper('baidu.com')->BAIDU.COM

测试 lower/upper/initcap 函数,使用dual哑表

select lower('www.BAIdu.COM') from dual;
select upper('www.BAIdu.COM') from dual;
select initcap('www.BAIdu.COM') from dual;

测试 concat/substr 函数,从1开始,表示字符,不论中英文

select concat('hello','你好') from dual;正确
select concat('hello','你好','世界') from dual;错误
select 'hello' || '你好' || '世界' from dual;正确
select concat('hello',concat('你好','世界')) from dual;正确
select substr('hello你好',5,3) from dual;
5表示从第几个字符开始算,第一个字符为1,中英文统一处理
3表示连续取几个字符

测试 length/lengthb 函数,编码方式为 UTF8/GBK(赵君),一个中文占3/2个字节长度,一个英文一个字节

select length('hello你好') from dual; 
select lengthb('hello你好') from dual; 

测试 instr/lpad/rpad 函数,从左向右找第一次出现的位置,从1开始

select instr('helloworld','o') from dual;
select LPAD('hello',10,'#') from dual;
select RPAD('hello',10,'#') from dual;

注意:找不到返回0,大小写敏感

测试 trim/replace 函数

select trim(' ' from '  he  ll                ') from dual;
select replace('hello','l','L') from dual;

测试 round/trunc/mod 函数作用于数值型

select round(3.1415,3) from dual;
select trunc(3.1415,3) from dual;
select mod(10,3) from dual;

测试 round 作用于日期型(month)

select round(sysdate,'month') from dual;

测试round作用于日期型(year)

select round(sysdate,'year') from dual;

测试trunc作用于日期型(month)

select trunc(sysdate,'month') from dual;

测试 trunc 作用于日期型(year)

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;

注意:1、日期-日期=天数 2、日期+-天数=日期

多行函数或分组函数:可有多个参数输入,只有一个结果输出:例如:count(*)->14

统计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
having avg(sal) = (
                    select max(avg(sal)) 
                    from emp
                    group by deptno
                    );

group by 子句的细节:

  • 在select子句中出现的非多行函数的所有列,【必须】出现在group by子句中
  • 在group by子句中出现的所有列,【可出现可不现】在select子句中


hogen

2017-09-09
Home About Github Email