Oracle查询入门（三）子查询、单行函数、聚合函数

一、子查询

子查询 : 查询语句嵌套查询语句

当条件与要查询的数据在不同的数据源,而两个数据源之间存在一定的关联方式,可以子查询中转查询

案例分析：
1、
–部门名称为 SALES 或 ACCOUNTING 的雇员信息
–数据 : 员工信息 *
–来源 : 员工表 emp
–条件 : dname in (‘SALES’,‘ACCOUNTING’)
–查询 SALES 或 ACCOUNTING的部门编号

--原代码
select deptno from dept where dname in ('SALES','ACCOUNTING');
select * from emp where deptno in(10,30);
--使用子查询之后
select * from emp where deptno in (select deptno from dept where dname in ('SALES','ACCOUNTING'));
--就是将原本的第一行查询的查询过程带入第二行得到的第一行的结果的地方

2、
– 查询工资等级为 2的员工信息
–数据 : 员工信息 *
–来源 : 员工表 emp
–条件 : 工资等级为 2
–查询2等级的最低薪与最高薪

--原代码
select losal from salgrade where grade = 2;
select hisal from salgrade where grade = 2;
select * from emp where sal between 1201 and 1400;
--1201和1400是执行上两行查询得到的结果
--使用子查询之后
select * from emp where sal between (select losal from salgrade where grade = 2) and (select hisal from salgrade where grade = 2);

二、单行函数

在SQL中函数主要分为内置函数和自定义函数
也可以把函数分为：单行函数和多行函数两类
单行函数：一条记录返回一个结果
例如：nvl（判断值，带入值）函数，判断判断值是否为空，把空修改为带入值。每一行的每一个记录都会返回一个结果就叫单行函数

多行函数|组函数|聚合函数： 多条记录返回一个结果
例如：求班上所有人的平均分，根据多条数据返回一个结果就是多行函数

常见的单行函数及应用：
表示当前时间

--都可以表示当前时间建议使用第一种
select sysdate from dual;
select current_date from dual;

输出结果：

表示当前时间的应用

--日期可以直接+——
-- 2天以后是几号
select sysdate+2 from dual;

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

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select ename,hiredate,hiredate+90 from emp;

对月份进行修改
add_months(修改的月份，修改多少)
months_between(当前日期，原本日期)

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

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

对结果取整
trunc（需要取整的内容）

--上述例子查询一共工作了几个月会得出非整数
select ename,months_between(sysdate,hiredate) from  emp;
--使用取整之后
select ename,trunc(months_between(sysdate,hiredate)) from emp;

查询当前月的最后一天
last_day(月份)

select ename,last_dat(sysdate) from emp;

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

--to_char
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
--to_date
select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;

nvl(值1，值2)对null值判断
判定函数 decode(判定字段,值1,结果1,值2,结果2…,默认值) 对判定字段的值进行判定,如果值为值1,函数的结果为结果1,与值2相等,函数的结果为结果2…最终如果以上都不相等,最终取默认值
给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十…

select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无') from  dept;

--case when then  else end
select deptno,dname,loc,(case deptno when 10 then '十' when 20 then '二十' else '无' end) from  dept;

– 查询82年入职员工的信息

select * from emp where hiredate like '%82%' ;
select * from emp where hiredate between to_date('1982-1-1','yyyy-mm-dd') and to_date('1982-12-31','yyyy-mm-dd');
select * from emp where to_char(hiredate,'yyyy') = '1982';

– 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水

select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) 涨薪后 from emp;

– 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍

select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;
select ename,sal,deptno,(case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*0.99 when 40 then sal*3 end) 涨薪后 from emp;

三、聚合函数

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

3.1count：求和

--1、count统计所有的员工数
select ename,1 from emp;
select count(1) from emp where 1=1;
--2、null不参与运算
--存在佣金的员工数
--不推荐/不需要
select count(comm) from emp where comm is not null; --推荐
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 where deptno in(10,20);

3.2max 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.3sum：求和

-- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;
-- 计算出所有员工的奖金总和
select sum(comm) from emp;
--null不参与运算

3.4avg：平均

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

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