Oracle 函数、分组子查询

一、函数–单行函数

1.函数

1）理解：函数分为系统内置函数和自定义函数。了解系统内
置函数（方法），重点掌握 to_date 、 to_char （字符和日期的转换）

2）单行函数：一条记录返回一个结果

3）多行函数 、组函数、聚合函数 ：多条记录返回一个结果

2.日期函数

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

2）当前时间 sysdate/current_date 以date类型返回当前的日期

-- 当前时间
select  distinct sysdate from emp;
-- 虚表
select distinct sysdate from dual;
select current_date from dual;

3)修改日期

-- 加减日期
-- 2天以后是几号
select sysdate+2 from dual;
-- 所有员工入职的3天前是几号
select hiredate,hiredate-3 from emp;

4)修改月份 add_months(d,x) 返回加上x月后的日期d的值

-- add_months(日期对象,月份数)
-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select hiredate,add_months(hiredate,3) from emp;
-- 当前日期修改后三个月
select add_months(sysdate,3) from dual;

5）月份之差 months_between(date1,date2) 返回date1和date2之间月的数目

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

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

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

7）下一个星期的时间 next_day(sysdate,‘星期一’) 下周星期一

-- 下一个星期三是几号(即将要过的星期三)
select next_day(sysdate,'星期三') from dual;
select next_day(sysdate,5) from dual;

2.转换函数

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

-- to_date(数据,格式)
-- to_char(数据,格式)
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期）
-- 设定一个时间   就是今天 '2020-2-5 09:18:25'
select to_date('2020/2/5 09:18:25','yyyy/mm/dd hh24:mi:ss')+3 from dual;
select to_date('2018/9/5 16:18:25','yyyy/mm/dd hh24:mi:ss')+3 from dual;

**2)to_char(d,m)**日期以指定格式转换为字符串

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

3.其他函数

1） nvl (参数1,参数2) 如果参数1为null,函数最终的结果为参数2 ,如果参数1不为null,结果还是参数1

select ename,nvl(comm,0) from emp;
select ename, nvl(to_char(comm),'hello') from emp;

2. decode(判定字段,值1,结果1,值2,结果2,值3,结果3…(,默认结果))

-- 判定函数   decode(判定字段,值1,结果1,值2,结果2,值3,结果3....(,默认结果))
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,decode(deptno,10,'十',20,'二十'，30,'三十',40,'四十') 中文部门编号 from dept;

3)case when then else end

-- 部门号10 工资涨10% 部门号20 工资涨8% 部门号30 工资涨15% 部门号40 工资涨20%
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;

-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
-- case when then else end
select ename,sal,deptno,(case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*0.99 else sal*3 end) raisesal from emp;

二、组函数

组函数|聚合函数|多行函数 : 对结果集进行组函数计算，多行记录返回一个结果
count(条件) sum(条件) max() min() avg()
注意: 组函数不能和非分组字段一起使用

1.count 统计记录

-- 统计一下一共有多少个员工
select count(empno) from emp;
select count(deptno) from emp;
select deptno from emp;
select count(*) from emp;
-- 伪列
select count(1) from emp;
-- 统计一共有几个部门 
select count(1) from dept;
-- 统计有员工存在的部门总数
-- 查询有员工存在的部门编号的结果集,对这个结果集求个数
select count(distinct deptno) from emp;
select count(1) from dept where deptno in(select distinct deptno from emp);
-- 统计20部门一共有多少人
select count(deptno) from emp where deptno=20;
select count(*) from emp where deptno=20;

2.max min :最大值 最小值

-- 查询本公司的最高工资和最低工资
select max(sal) from emp;--5000
select min(sal) from emp; --800
-- 查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno=30;
-- 查询 最高薪水的员工姓名， 及薪水
select max(sal) from emp;
select ename,sal from emp where sal=(select max(sal) from emp);

3.sum：求和

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;
select sum(comm) from emp;
select sum(sal+nvl(comm,0)) from emp;
-- 计算出所有员工的奖金总和  null 不参与运算
select sum(comm) from emp where comm is not null;

4.avg：平均值

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal<(select avg(sal) from emp);
-- avg 平均工资
select avg(sal) from emp;
-- 请查询出 20部门的平均工资  组函数不能和非分组使用
select avg(sal) from emp where deptno=20;

三、分组

1.group by having

group by 分组字段
-- 查询公式:select 数据 from 数据来源 where 行过滤条件 group by 分组字段1,.. having 过滤组信息(以组为单位过滤) order by 排序字段..;
-- 执行流程: from -- where --group by --having --select  -- order by
-- 注意:
--  1)select 后如果出现了组函数|分了组,组函数不能与非分组字段,可以与其他组函数或分组字段一起使用
--  2)where 后不能使用组函数   因为还没有组,执行流程问题  

-- 求出所有有员工存在的部门编号
select deptno from emp group by deptno;
-- 找出20部门和30部门的最高工资 
	-- 20部门和30部门中的所有员工中的最高工资 
	select max(sal) from emp where deptno in(30,20);
	-- 找出20部门和30部门中每个部门的最高工资
 -- 先过滤 后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno;
-- 先分组再过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);
-- 求出每个部门的平均工资
-- 数据: 每组的平均薪资
-- 来源: 员工表 
-- 条件: 一个部门一个部门求平均薪资  ,一个部门一个值  以部门为单位 如果不分组组函数对所有满足条件的数据进行计算,如果分组了,以组为单位
select avg(sal),deptno from emp group by deptno;
-- 求出每个部门员工工资高于1000的的部门平均工资
-- 数据: 部门平均工资
-- 来源: 员工表 
-- 条件: sal>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 max(sal) from emp group by deptno;
-- 求出每个部门的平均工资高于2000的部门编号和平均工资
-- 先过滤后分组select 后如果出现了组函数|分了组,组函数不能与非分组字段,
-- 可以与其他组函数或分组字段一起使用
  -- 2)where 后不能使用组函数   因为还没有组,执行流程问题
-- select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
-- 先分组后过滤
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select * from emp;

四、子查询与行转列

1.一条sql语句实现需求

/*
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;
-- 查表
select * from tb_student;
-- 使用一条sql语句，查询每门课都大于80分的学生姓名
-- 数据: 学生姓名
-- 来源: tb_student
-- 条件: 1.学生考试科目数=一共有的科目数  and  2)这个人所有分数中最低分数>80
      -- 科目数
      select count(distinct course)from tb_student; 
      -- 每个人最低分
      select min(score) from tb_student group by name;
select name
  from tb_student
 group by name
having count(distinct course) = (select count(distinct course)
                                   from tb_student) and min(score) > 80;

2.行转列

-- 行专列
select name,max(decode(course,'语文',score)) 语文,min(decode(course,'数学',score)) 数学,max(decode(course,'英语',score)) 英语 from tb_student group by name;
-- decode() 是单行函数
select name,decode(course,'语文',score) 语文, decode(course,'数学'，score) 数学, decode(course,'英语'，score) 英语 from tb_student;

五、rowid 和 rownum

1.rowid

rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分

-- 当一个表中有多条一模一样的数据的时候,实现去重,重复数据只保留一条
-- 查到要保留的数据
select id,name,course,score,min(rowid) from tb_student group by id,name,course,score; 
-- 更加好
select * from tb_student where rowid in(select min(rowid) from tb_student group by id,name,course,score);

-- 查到要删除的数据
select *
  from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);

-- 删除这些数据
delete from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);

2.rownum

rownum 结果集的序号 有一个结果集就有一个rownum select查到的结果集
rownum 从1开始,每次+1

-- rownum 从1开始,每次+1
select deptno,dname,loc,rowid,rownum from dept;
select empno,ename,rownum from emp where rownum<=4;

-- 分页:在oracle中使用rownum.因为rownum规律可循,控制rownum序号从1开始,每次+1,方便判断
-- 查询
-- 一般如果根据主键字段进行排序,先排序后确定rownum
select deptno,dname,rownum from dept order by deptno desc;
-- 一般如果根据非主键字段进行排序,先确定rownum再排序
select deptno,dname,rownum from dept order by loc;

-- 保证一定先排序后确定rownum,在结果集的外面嵌套一层select,这个select的rownum肯定就是从1开始,根据这个有规律的,已确定的row进行判断操作就可以
select deptno,dname,rownum num from dept order by loc;

select rownum,deptno,dname,num from (select deptno,dname,rownum num from dept order by loc);

select empno,ename,sal,comm,rownum from (select empno,ename,sal,comm,rownum num from emp order by sal desc); 

-- 分页需求:
-- i页数  num每页显示几个
-- num= 3  i=4
-- 每一页要显示的数据的rownum   第一个:  rownum>=num*(i-1)+1                    最后一个为: row<=num*i
select *
  from (select empno, ename, sal, comm, rownum num2
          from (select empno, ename, sal, comm, rownum num
                  from emp
                 order by sal desc))
 where num2 >= 3 * (1 - 1) + 1
   and num2 <= 3 * 1;