Oracle常用的三大类函数详解

一天一个关于测试知识点，5分钟内讲解你最关心的软件测试问题，今天就接着来谈谈关于软件测试中的“Oracle常用的三大类函数详解”。

　　函数

　　Oracle提供了很多函数用于数据的处理、统计和转换，这些函数增强了SQL语言的功能。

　　Oracle的函数分为单行函数、聚合函数和分组函数三大类。

　　单行函数

　　用于SQL语句中时，只能输入一个数据，返回一个结果，常用的单行函数包括字符串函数、数字函数、日期函数、转换函数。

　　(1)日期函数

• 　　oracle以内部数字格式存储日期年月日小时分钟秒

• 　　sysdate/current_date 以date类型返回当前的日期

• 　　add_months(d,x) 返回加上x月后的日期d的值

• 　　LAST_DAY(d) 返回的所在月份的最后一天

• 　　months_between(date1,date2) 返回date1和date2之间月的数目

• 　　next_day(sysdate,星期一) 下一个星期一

　　获取当前时间

　　select current_date from dual;

　　select sysdate from dual;

　　修改日期

　　-- 两天后的时刻

　　select sysdate+2 from dual;

　　修改月份

　　-- 当前5个月后的时间

　　select add_months(sysdate,5) from dual;

　　-- 雇佣日期 2个月的时间

　　select ename,hiredate, add_months(hiredate,2) after from emp;

　　月份差

　　-- 雇佣日期 距离现在的 月份数

　　select ename, months_between(sysdate , hiredate) from emp;

　　最后一天

　　-- 返回雇佣日期 当月最后一天的时间

　　select last_day(sysdate) from dual;

　　下一个星期的时间

　　-- 下一个星期二

　　select next_day(sysdate, '星期二') from dual;

　　(2)日期格式转换

　　● to_date(c,m) -> 字符串以指定格式转换为日期

　　● to_char(d,m) -> 日期以指定格式转换为字符串

　　select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss') time from dual;

　　select to_char(sysdate, 'yyyy-mm-dd') from dual;

　　select to_char(sysdate, 'yyyy/mm/dd') from dual;

　　select to_char(sysdate, 'yyyy\mm\dd') from dual;

　　-- 注意中文的问题

　　-- select to_char(sysdate,'yyyy年mm月dd日') from dual;

　　select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

　　多行 | 聚合 | 组函数

　　用于SQL语句中时，同时对多行数据进行操作，返回一个结果，例如求结果集的记录数、最小值、最大值、平均值、统计值等。

　　● count :统计记录数 count() -->* 或一个列名

　　● max min: 最大值 最小值

　　● sum：求和

　　● avg:平均值

　　注意:

　　1. 组函数仅在选择列表和Having子句中有效

　　2. 出现组函数，select 只能有组函数或分组字段

　　2.1 count 求个数

　　-- null不参与运算

　　select count(1) from emp;

　　2.2 max最大值，min最小值

　　-- 查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息

　　select max(sal) maxSal , min(sal) minSal , count(1) from emp;

　　2.3 sum求和

　　-- 查询10部门的所有员工的工资总和

　　select sum(sal) from emp where deptno=10;

　　2.4 avg 平均

　　-- 查询工资低于平均工资的员工编号，姓名及工资

　　select empno, ename,sal from emp where sal<(select avg(sal) from emp);

　　分组函数

　　用于计算基于分组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

• 　　group by : 分组

　　1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段

　　2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by 中

• 　　having : 过滤组

• 　　where : 过滤行记录，不能使用组函数

• 　　having : 过滤组 可以使用组函数

　　-- 按 部门 查询 平均工资

　　select avg(sal) from emp group by deptno;

　　-- 按 部门 查询 平均工资，且平均工资大于2000的部门编号

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

　　行转列

　　select name,

　　min(decode(course, '语文', score)) 语文,

　　min(decode(course, '数学', score)) 数学,

　　min(decode(course, '英语', score)) 英语

　　from tb_student

　　group by name;