Oracle数据库：单行函数、组函数、分组、rowid和rownum

学习目标

1、单行函数

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

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

1.1、日期函数

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

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

• **last_day(d)**返回所在月的最后一天

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

• next_day(sysdate,‘星期一’) 返回下周星期一的日期

1.1.1、当前时间

• sysdate
• current_date

-- 当前时间
select sysdate from dual;
select current_date from dual;

1.1.2、加减时间

-- 加减日期
-- 2天以后是几号
select sysdate+2 from dual;

-- 所有员工入职的3天后是几号
select ename 姓名,hiredate 入职日期, hiredate+3 入职三天后的日期 from emp ;

1.1.3、修改月份

• add_month( )

--add_months(hiredate,3) 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select ename,empno,hiredate 入职日期,add_months(hiredate,3) from emp;

1.1.4、月份之差

• months_between

-- 查询所有员工到目前为止一共工作了几个月
select months_between(sysdate,hiredate) 入职的月数 from emp;

1.1.5、月的最后一天

• last_day()

-- 查询当前月的最后一天
select last_day(sysdate) from dual;

1.5.6、下一个星期X的时间

• next-day

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

1.2、日期和字符串的转化

• to_char()
• to_date()

1.2.1、to_char()

-- 将日期转为特定格式的字符串 to_char
select to_char(sysdate,'yyyy"年"mm"月"dd"号"')from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss')from dual;
select to_char(sysdate,'yyyy')from dual;

1.2.2、to_date()

--to_date(日期字符串,日期转换模板)  'yyyy-mm-dd hh24:mi:ss'
select to_date('2020.08.12 12:25:56','yyyy-mm-dd hh24:mi:ss') 今天 from dual;

1.2.3、nvl()函数、decode()函数

--nvl(string1,string2) 如果string1为null,则结果为string2的值
select ename, nvl(null,0) from emp;

--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,decode(deptno,10,'十',20,'二十',30,'三十','四十') from dept;

--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) raisesal
  from emp;

2、组函数

组函数|多行函数|聚合函数即多条记录返回一个结果。

• count()；统计表中记录的个数
• max()|min()：最大值、最小值
• sum()：求和
• avg()：求平均值

注意：

• 组函数仅在选择列表和having语句中有效；
• 如果出现组函数，select后面只能跟组函数或分组字段；
• 组函数中可以使用distinct关键字去重；
• 组函数不能使用在where后面，但是可以使用在having后面；
• 计算组函数时null不参与运算；
• 组函数不能与非分组字段一起使用,组函数只能与组函数和分组字段（即group by后面的字段）一起使用。

-- 统计一下一共有多少个员工
select count(ename) from emp;
select count(1) from emp;--使用伪列也可以

-- 统计一共有几个部门
select count(deptno) from dept;

-- 统计有员工存在的部门总数，在组函数中可以去重
select count(distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(ename) from emp where deptno = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

-- 查询本公司的最高工资和最低工资
select max(sal) 最高工资,min(sal) 最低工资 from emp;

--查看30部门的最高工资和最低工资
select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno = 30;

-- 计算出所有员工的奖金总和，null不参与运算
select sum(comm) 奖金总和 from emp;

-- 统计有奖金的员工有几个
select count(comm) 有奖金的员工个数 from emp where comm is not null;

--查询 最高薪水的员工姓名， 及薪水
select ename 姓名,sal 薪水 from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
select deptno 员工编号,ename 姓名,sal 工资 from emp where sal < (select avg(sal) from emp);

3、分组

• 使用关键字group by对符合条件的记录进一步分组；
• 使用关键字having对每个组进行进一步筛选。

语法：

select distinct * | 字段 | 表达式 | 函数 as 别名

from 表 表别名

where 过滤行记录条件

group by 分组字段列表

having 过滤组

order by 字段列表 asc | desc

执行流程：from–>where–>group by -->having–>select–>order by

注意：

• select后出现分组函数就不可以使用非分组字段，但是可以使用group by后的分组字段；

• group by的分组字段可以不出现在select后，但是select后除组函数外其他字段必须出现在group by之后。

• where之后进行行过滤，且不可以使用组函数；having进行组过滤，having后只能使用组函数和分组字段。

• having后使用组函数时不可以嵌套使用，但是select后可以嵌套使用组函数。

不使用分组，但是可以达到分组的效果：

-- 查出比本部门平均工资高的员工信息
select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);
/*解析|执行流程：依次从e1中拿出一条语句和e2中所有的数据比较（e2.deptno = e1.deptno），比较完毕后得到了是当前部门的多有记录（即达到了分组的效果），然后对该组数据取平均值。
*/

-- 找出20部门和30部门的最高工资 
select max(sal),deptno from emp group by deptno having deptno in(20,30);-- 先分组，后过滤
select max(sal),deptno from emp where deptno in(20,30) group by deptno;-- 先过滤，后分组


-- 求出每个部门的平均工资
select avg(sal),deptno from emp group by deptno; 

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal), deptno from emp where sal > 1000 group by deptno

-- 求出10和20部门的哪些工资高于1000的员工的平均工资
select avg(sal), deptno
  from emp
 where sal > 1000
 group by deptno
having deptno in(10, 20); --先分组后判断


select avg(sal), deptno
  from emp
 where sal > 1000
   and deptno in (10, 20)
 group by deptno; --先判断后分组



-- 找出每个部门的最高工资
select max(sal),deptno from emp group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;


--按 岗位查询 平均工资，且平均工资大于2000的岗位
select job from emp group by job having avg(sal) >2000;

--先求出所有岗位的平均薪资和岗位,再判断平均薪资是否>2000
select job
  from (select avg(sal) avg_sal, job from emp group by job)
 where avg_sal > 2000;
 
 --或者
select avg(sal), deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

错误实例：

--注意：以下写法会报“分组函数嵌套太深错误”，无法使用
select avg(sal) from emp group by deptno having min(avg(sal));

4、行转列

准备工作：

/*
id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
create table tb_student(
 id number(4) ,
 name varchar2(20),
 course varchar2(20),
 score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;
*/

简单操作实例：

--查询每门课都大于80分的学生姓名
-- select name from tb_student where 学生课程数 = 3 and 最低分数 > 80

--1.查看最大课程数
select count(distinct course) from tb_student;
--2.查看每个学生的课程数（用到了分组）
select count(course) from tb_student group by name;
--3.查看每个学生的最低分数
select min(score) from tb_student group by name;

--合并得最终结果
select name
  from tb_student
 group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80

原生效果：

--1.查看表结构
select * from tb_student;

--2.使用decoude()函数组合成新的表头展示形式
select name,
       decode(course, '语文', score,null) 语文,
       decode(course, '数学', score) 数学,
       decode(course, '英语', score) 英语
  from tb_student ;

实现行转列效果：

-- 分组后select后只能使用分组字段和组函数
--在每个分组中，对decode函数生成的伪列取最大值，达到去重（合并）的效果
--3.去除无效的数字
select name,
       max(decode(course, '语文', score,null)) 语文,
       min(decode(course, '数学', score)) 数学,
       min(decode(course, '英语', score)) 英语
  from tb_student group by name;

5、rowid和rownum

rowid和rownum都是伪列（表中没有该字段，但是可以查询到）。

5.1、rowid

rowid 相当于对象的地址,区分表中的数据,是记录的唯一（和主键的地位一样）,在数据插入表中时就存在。

作用：

• 可以对相同数据做去重(没有主键的表)。
• 在没有主键的情况下仍可以唯一确定一条记录。
• 如果在select语句后没有给出rowid和rownum字段|伪列，仍可以正常使用。

select deptno,dname,loc,rowid from dept;

唯一确定一条数据：

 select *
   from emp
  where rowid = (select rowid from emp where empno = 7369);

使用rowid去重

注：表中没有主键

select * from tb_student；

去重：

--拿到rowid唯一值
select max(rowid) from tb_student group by id; 
--去重
delete from tb_student
 where rowid not in (select max(rowid) from tb_student group by id);

5.2、rownum

• rownum是伪列，是结果集的序号，只要有select就有结果集就有rownum,每一套结果集都有自己的rownum
• 规律: 把已确定了的结果集中的数据从第一个开始设置rownum,从1开始,依次+1；
• 优点: 有规律可循,结果集的序号是数字,可以进行判断；
• 作用：可以使用rownum对结果集进行分页并显。

select empno,ename,sal,rownum from emp;

5.2.1、使用rownum实现分页

-- 查询前五条数据
select empno,ename,sal,rownum from emp where rownum>= 1 and rownum <=5;
--或者
select empno,ename,sal,rownum from emp where rownum <=5;

以下方式（分页的数据不是从1开始显示的）没有语法错误但是查询不到任何数据：

select empno,ename,sal,rownum from emp where rownum > 5;

原因：

• rownum是对一个结果集中的数据进行编号，上述where后每次拿到rownum都为1，不能满足rownum>5的条件。

解决方案：

• 使用select语句嵌套后再使用
• 若where中使用内部（子查询）rownum时要使用别名，如果直接写rownum,被认为是外部select语句中的。

--查询6~10之间的数据
select empno, ename, sal, rn
  from (select empno, ename, sal, rownum rn from emp)
 where rn > 5
   and rn <= 10;

5.2.1、rownum排序汇总的乱序问题

• 如果存在排序，rownum的序号可能出现问题(乱号)

• 如果根据主键进行order by，则先排序再rownum，如果根据其他字段排序,一般会先rownum,再order by

--根据主键进行排序时rownum不会乱
select empno,ename,sal,rownum from emp order by empno;

--根据非主键排序时rownum会出现乱的情况
select empno,ename,sal,rownum from emp order by deptno;

解决方案：

• 在排序后外层再嵌套一个select，让系统对rownum伪列进行重新排序

select empno, ename, sal, rownum
  from (select empno, ename, sal, rownum from emp order by deptno);

--对emp表按工资进行升序排列，然后取rownum为6~10的数据
select empno, ename, sal from emp order by sal asc;--中间步骤1

select empno, ename, sal, rownum
  from (select empno, ename, sal from emp order by sal asc); --中间步骤2
  
--结果
select empno, ename, sal, rn
  from (select empno, ename, sal, rownum rn
          from (select empno, ename, sal from emp order by sal asc))
 where rn > 5
   and rn <= 10;

习题：

--按照工资降序排序 查询第三页的数据， 每一页显示4条记录 
select empno, ename, deptno, rownum from emp order by sal asc; --中间步骤1

select empno, ename, deptno, rownum rnum
  from (select empno, ename, deptno, rownum from emp order by sal asc); --中间步骤2

--结果
select empno, ename, deptno, rnum
  from (select empno, ename, deptno, rownum rnum
          from (select empno, ename, deptno, rownum from emp order by sal asc))
 where rnum > 4 * 2
   and rnum <= 12;