数据库2
函数
内置函数和自定义函数
单行函数 : 一条记录返回一个结果的
多行函数|组函数|聚合函数 : 多条记录返回一条结果的
当前时间
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(数据,格式)
设定一个特定的时间（用一个特定的时间字符串转换为日期）
设定一个时间 就是今天 '2020-02-05 16:18:25'
select to_date('2020/02/05 16:18:25','yyyy/mm//dd/ hh24:mi:ss')+3 from dual;
将日期转为特定格式的字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss')from dual;
判定函数 decode(判定字段,值1,结果1,值2,结果2,值3,结果3....(,默认结果))
给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','四十') from dept;
给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
select ename,sal,deptno,decode(deptno,20,sal*1.1,sal)from emp;
10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
select sal,deptno, decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*(1-0.01),40,sal*3) 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) 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);
查询比SMITH薪资高并且与SMITH同部门的员工信息
select ename ,sal from emp where sal>(select sal from emp where ename='SMITH');
查看高于本部门平均薪水员工姓名
select avg(sal) from emp where deptno=20;
select ename from emp e1 where sal >(select avg(sal) from emp e2 where e2.deptno = e1.deptno);
分组: 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),depton 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;
--按 部门岗位(job) 查询 平均工资和工种
select avg(sal),job from emp group by job;
--按 岗位查询 平均工资，且平均工资大于2000的岗位
select avg(sal),job from emp where sal>2000 group by job;
select avg(sal),job from emp group by job having avg(sal)>2000;
--查询出所有部门的平均薪资和部门编号
select deptno,avg(sal) from emp group by deptno;
--所有部门的平均薪资中最低薪资
select min(avg(sal)) from emp group by deptno;
--查询 最低平均工资的部门编号
select deptno
from (select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal = (select min(avg(sal)) from emp group by deptno);
-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select count(1),deptno from emp group by deptno order by count(1);
-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select avg(sal),deptno from emp group by deptno having avg(sal) between 1500 and 2000;
--查询20部门的平均工资的员工
select avg(sal),deptno from emp group by deptno having deptno=20;
-- 查询工资高于20部门平均工资的员工
select * from emp where sal>(select avg(sal) from emp group by deptno having deptno=20);
--rowid 和 rownum 都是伪列
--rowid理解为记录在插入到数据库的表中时候就存在的数据的地址(对象的地址),其实不是地址,根据地址得到的值
--如果一个表中没有主键,没有不可重复的字段,可能会出现多条一模一样的数据,无法区分重复数据,可以根据rowid进行区分
每一页要显示的数据的rownum 第一个: rownum>=num*(i-1)+1 最后一个为: row<=num*i
