函数、组函数、分组group by、rowid和rownum

一、函数—单行函数

根据函数的返回结果，我们将函数分为单行函数和多行函数。

• 单行函数：一条记录返回一个结果
• 多行函数组函数聚合函数(重点)：多条记录返回一个结果(重点)

1.日期函数

日期函数: 注意区分db数据库时间，java应用服务器的时间。以一方为准oracle以内部数字格式存储日期:年,月,日,小时,分钟,秒。

• sysdate/current_date以date类型返回当前的日期
• add_months(d,x)返回加上x月后的日期d的值
• LAST_DAY(d)返回的所在月份的最后一天
• months_between(date1,date2)返回date1和date2之间月的数目
• next_day(sysdate,‘星期一’)下周星期一

1）当前时间

-- 查询当前时间（条件可写可不写）
select current_date from dual where 1 = 1;
select sysdate from dual;

2）修改日期（天数±）

--两天后
select sysdate+2 from dual;
--两天前
select current_date-2 from dual;

3）修改月份

-- 加月份
select add_months(sysdate,3) from dual;

--雇佣日期 2 个月的时间  查询员工入职日期
select ename, hiredate, add_months(hiredate,2) "转正日期" from emp;

4）月份之差

----雇佣日期 距离现在的 月份数
select ename, hiredate, months_between(sysdate,hiredate) "上班时间" from emp;

5）最后一天

--返回雇佣日期当月最后一天的时间
select ename, last_day(hiredate) d from emp;

6）下一个星期的时间

--下一个星期二
select next_day(sysdate, '星期二') from dual;

2.转换函数（重点***）

• to_date(c,m) 字符串以指定格式转换为日期
• to_char(d,m)日期以指定格式转换为字符串

-- to_date("时间字符串","格式字符串")   时间
select to_date('2017-3-21 6:12:12','yyyy-mm-dd hh24:mi:ss')  "生日" from dual;

select  to_char(sysdate,'yyyy"年"mm"月"dd hh24:mi:ss') "当前时间"   from dual;

3.其它函数

1）nvl（string1，string2）

nvl：nvl(string1,string2) 如果string1为null,则结果为string2的值。

select ename, nvl(null,0) from emp;
--把津贴为空的改成hello（伪列）
select ename, nvl(to_char(comm),'hello') from emp;

2）decode

decode：decode(condition,case1,express1,case2 , express2,…casen , expressn, expressionm

--把deptno为10的部门用十来显示（伪列）...
select ename, deptno,decode(deptno, 10,'十',20,'二十',30,'三十') from emp;

3)case when then else end

和上面效果一样，只是写法不一样。

--给所有的员工涨薪,10-->10% 20-->8% 30 -->15% 其他-->20%
--decode
select ename, sal, deptno, decode(deptno, 10, sal * 1.1, 20, sal * 1.08, 30, sal * 1.15, sal * 1.2) newsal from emp;
--case when then else end 
select ename, sal, deptno, (case deptno when 10 then sal * 1.1 when 20 then sal * 1.08 when 30 then sal * 1.15 else sal * 1.2 end) newsal from emp;

二、组函数

组函数|多行函数|聚合函数即多条记录返回一个结果。我们需要掌握如下几个组函数：avg 、sum、min、max、count。

• count：统计记录数count() -->* 或一个列名
• max min：大值最小值
• sum：求和
• avg：平均值

注意：

1、组函数仅在选择列表和Having子句中有效
2、出现组函数，select 只能有组函数或分组字段

1.count

--1、count统计所有的员工数
select count(*) from emp;
select count(1) from emp;--推荐
--2、null不参与运算
--存在佣金comm的员工数
select count(comm) from emp;
--统计部门编号30的员工数
select count(1) from emp where deptno=30;

--统计数量过程中，可能处理重复
--统计存在员工的部门数量
select count(distinct(deptno)) "有人的部门" from emp;
--统计10和20部门一共有多少人
select distinct(count(1)) from emp wheredeptno in(10,20);

2.max min: 最大值最小值

--查询所有员工的最高薪水，最低薪水，员工总数-->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;
--查询最高薪水的员工名称及薪水--组信息与单条记录不能同时查询
select max(sal), ename, sal from emp; ->错误
select ename, sal from emp where sal=(select max(sal) from emp );

3.sum：求和

--查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

4.avg:平均

--查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal)fromemp);
--查看高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno );

三、分组

分组：group by , 将符合条件的记录进一步的分组
过滤组：having , 过滤组信息，表达式同where 一致

过滤组having：

where：过滤行记录，不能使用组函数，having：过滤组可以使用组函数

现在的结构如下：

select distinct * | 字段| 表达式| 函数as 别名
from 表表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表asc | desc

解析步骤：

1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by

--按部门查询平均工资
select deptno,avg(sal) from emp group by deptno;
--按部门岗位查询平均工资
select deptno, job, avg(sal) from emp group by deptno, job;

--按部门查询平均工资，且平均工资大于2000的部门编号
--1、先分组后过滤(不推荐)
select deptno,avgsal
  from (select deptno, avg(sal) avgsal from emp group by deptno)
 where avgsal > 2000;
--2、过滤组,分组同时过滤
select avg(sal), deptno from emp group by deptno having avg(sal)>2000;

--查询最低平均工资的部门编号
--1)、按部门求出平均薪水
select avg(sal) from emp group by deptno;
--2)、找出最低的平均薪水
select min(avg(sal)) from emp group by deptno;
--3)、过滤组
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

--查看高于本部门平均薪水员工姓名
select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

四、rowid 和rownum

ROWID 它是一个伪列，它并不实际存在于表中，根据一行数据的ROWID 能找到一行数据的物理地址信息。从而快速地定位到数据行。使用ROWID 来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。

ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。 利 用ROWNUM，我们可以生产一些原先难以实现的结果输出。例如实现分页操作。ps: oracle 中索引从1 开始，java 程序从0 开始。

1.rowid

实现重复记录的删除：

--每条记录的唯一标识
select s.* , rowid from tb_student s;
--找出保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student
 where rowid not in
       (select min(rowid) fromtb_student group by name, course);

2.rownum

rownum :1、必须排序2、不能直接取大于1 的数

--对员工表中的所有数据，根据薪资降序排序进行分页，每页显示4个数据，显示第三页
--三层分页模板
--1.
select e1.* ,rownum from emp e1 order by sal desc;
--2.
select e2.*, rownum
  from (select e1.*, rownum from emp e1 order by sal desc) e2;
--3.  
select e3.*, rownum
  from (select e2.*, rownum r2
          from (select e1.*, rownum r1 from emp e1 order by sal desc) e2) e3
 where r2 > 9
   and r2 <= 12;