学习第二十一天

一：
–函数
根据函数的返回结果，我们将函数分为单行函数和多行函数
–内置函数和自定义函数
–单行函数 : 一条记录返回一个结果的
–多行函数|组函数|聚合函数 : 多条记录返回一条结果的

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

– 当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

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

– 所有员工入职的3天前是几号
select hiredate,hiredate-3 from emp;

–add_months(日期对象,月份数)
– 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select hiredate,add_months(hiredate,3) from emp;

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

–last_day()
– 查询当前月的最后一天
select hiredate,last_day(hiredate) from emp;

–next_day(‘星期三’)
– 下一个星期三是几号(即将要过的星期三)
select next_day(sysdate,‘星期日’) from dual;

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

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

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

其他函数
–1)、nvl nvl(string1,string2)  如果string1为null,则结果为string2的值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),‘hello’) from emp;
–2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm)
select ename,decode(deptno, 10,‘十’,20,‘二十’) from emp;
–3)、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;

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

– 统计一下一共有多少个员工
select count(empno) from emp;
select count(deptno) from emp;
select count(*) from emp;
select count(1) from emp; --伪列 相当于为每条数据的后面添加一个伪列字段 1

– 统计一共有几个部门
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|1|*…) from emp where deptno =20;

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

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

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

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

– avg 平均工资
select avg(sal) from emp;

– 请查询出 20部门的平均工资
select avg(sal) from emp where deptno = 20;

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

– 统计一共有多少个员工 null 不参与运算
– 统计有奖金的员工有几个
select count(comm) from emp;

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

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

三：分组

–分组: 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 (30,20) group by deptno; --先过滤 后分组
select max(sal),deptno from emp group by deptno having deptno in (30,20); --先分组再过滤

– 求出每个部门的平均工资
–数据: 每组的平均薪资
–来源: 员工表
–条件: 一个部门一个部门求平均薪资 ,一个部门一个值 以部门为单位 如果不分组组函数对所有满足条件的数据进行计算,如果分组了,以组为单位
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 * from (select avg(sal),deptno from emp where sal>1000 group by deptno) where deptno in(10,20);

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

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

四：子查询与行转列
–使用一条sql语句，查询每门课都大于80分的学生姓名
–数据: 学生姓名
–来源: tb_student
–条件: 1.学生考试科目数=一共有的科目数 and 2)这个人所有分数中最低分数>80
select name
from tb_student
group by name
having count(distinct course) = (select count(distinct course)
from tb_student) and min(score) > 80;

select name,count(distinct course) from tb_student group by name;

–一共有几门课程
select count(distinct course) from tb_student; --先去重再求个数

–求每个人的最低分
select min(score),name from tb_student group by name ;

–行专列
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

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

select deptno,dname,loc,rowid from dept;
select empno,ename,rowid from emp;

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);

select * from tb_student;

–当一个表中有多条一模一样的数据的时候,实现去重,重复数据只保留一条
–查到要保留的数据
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);

–rownum 结果集的序号 有一个结果集就有一个rownum select查到的结果集
–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;

–select 数据 from 数据来源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序
–执行流程: from where group by having select order by
–decode() |case判定函数 nvl()
–单行函数
–组函数
–子查询
–in 和 exists
–rowid 和 rownum
–集合函数
–like