oracle基础（第四节）

--单行函数 通用函数
--通用函数
--(1)nvl
--nvl(expr1,expr2): 当表达式expr1的结果是null时，使用表达式expr2的结果
--(2)nvl2
--nvl2(expr1,expr2,expr3): 当表达式expr1的结果是null时，使用表达式expr3的结果，否则使用expr2的结果：

select ename, comm, nvl2(comm,'not null','haha')
from emp;

--练习：查询emp表中员工名字和奖金数，并多显示一行（如果有奖金，yes，否则显示no）:

select ename, comm, nvl2(nullif(comm,0),'yes','no')
from emp;

--(3)nullif
--nullif(expr1,expr2)如果expr1和expr2的结果相等，nullif返回null，不相等则返回expr1的结果:

select nullif(5+3,3+4)
from dual;

select 5/nullif(0,0)
from dual;

--(4)decode
--decode(expr,值1,显示1,值2,显示2,...值n,显示n,其他值的显示)
-- =3000和=5000 高 否则 低:

select ename, sal, decode(sal,5000,'高',3000,'高','低')
from emp

--sign(expr1) 如果expr1表达式的结果>0 返回 1 =0 返回0 <0 返回-1:

select sign(5-22)
from dual;

-- 1600以上为高， 否则为低

select ename, sal, decode(sign(sal-1600),1,'高','低')
from emp

--分组函数
--(1)平均 avg(expr)
--查询emp表中所有人的平均月薪:

select avg(sal)
from emp;

--(2)求和 sum(expr)
--查询emp表中所有人的月薪总和:

select sum(sal)
from emp;

--(3)最大 max(expr)
--查询emp表中最大的工资数:

select max(sal)
from emp;

--(4)最小 min(expr)
--查询emp表中最小的工资数:

select min(sal)
from emp;

--(5)计数 count(expr)
--查询emp表有多少位员工:

select count(*)
from emp;

--第一个列：一般是“主键” 建议使用count(主键列):

select count(empno)
from emp;

--练习：查询10号部门的员工人数:

select count(empno)
from emp
where deptno = 10

--分组的概念
--单行函数：一行记录会产生一个结果
--分组函数：多行记录（一组记录）会产生一个结果 统计

--group by 列或表达式 表示分组的依据 书写是在where之后，order by之前
--group by deptno 以部门编号作为分组依据
--自动把deptno相等的数据作为一组进行统计:

select *
from emp
order by deptno

select deptno,count(empno)
from emp
group by deptno;

--查询各个职位的平均月薪 :

select job,avg(sal)
from emp
group by job;

--使用了group by之后 select中能够书写的内容受限
--select语句中只能编写group by中出现的列和分组函数，不能写其他的内容

--分组group by之后可以写多个列
--group by A,B 先按A进行分组，在A分组的内部，再按B分组
--查询各个部门内部的各个岗位的平均月薪:

select deptno,job,avg(sal)
from emp
group by deptno,job
order by deptno,job;

select * from emp;

--练习
--1.查询在1981年期间入职员工的平均月薪:

select avg(sal)
from emp
where hiredate between to_date('1981-01-01','YYYY-MM-DD') and to_date('1981-12-31','YYYY-MM-DD');

--2.查询在月薪在1200以上的员工的人数:

select count(empno)
from emp
where sal > 1200;

--3.查询各部门的部门编号，人数，平均月薪和月薪总数:

select deptno, count(empno), avg(sal), sum(sal)
from emp
group by deptno;

--4.查询各部门的部门编号，平均年收入 :

select deptno, avg((sal+nvl(comm,0))*12)
from emp
group by deptno;

--5.查询各个年份入职员工的平均月薪
-- 显示内容为：年份，平均月薪
-- 比如 年份 平均月薪
-- 1980 900
-- 1981 1400

select to_char(hiredate, 'YYYY') as yr, avg(sal)
from emp
group by to_char(hiredate, 'YYYY')
order by yr asc;

--分组函数的筛选
--having: 配合group by句式，专门对分组函数的条件进行筛选
--练习：查询平均月薪高于1600的部门ID和其平均月薪:

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 1600;

--练习：查询人数不少于5人的部门ID和部门人数:

select deptno,count(empno)
from emp
group by deptno
having count(empno) >= 5;

--与distinct的使用:

select job from emp;
select count(job) from emp;--个数：14

select distinct job from emp;
select count(distinct job) from emp; --种类数：5

--分组函数的嵌套
--查询各个部门的平均人数:

select avg(count(empno))
from emp
group by deptno;

--总结：查询语句的书写顺序与执行顺序:
--书写顺序：select...from...where...group by...having...order by...
--执行顺序：from...where...group by...having...select...order by...

--多表连接查询(重要)
--dept表 部门信息表
--deptno 部门编号/部门ID
--dname 部门名称
--loc 部门所在地点（工作地点）

--笛卡尔积
select *
from emp, dept

--1.等值连接：在A，B两表之间，通过意义相同的两个列进行筛选，筛选出这个列中数值相同的数据:

select *
from emp, dept
where emp.deptno = dept.deptno

--表别名：只能用空格的方式声明 不能使用as

select *
from emp e, dept d
where e.deptno = d.deptno;

--查询员工编号，名字，所在部门名称，工作地点

select e.empno,e.ename,d.dname,d.loc
from emp e, dept d
where e.deptno = d.deptno;

--查询工作地点在DALLAS的员工编号，名字，月薪，所在部门名称

select e.empno,e.ename,e.sal,d.dname,d.loc
from emp e, dept d
where e.deptno = d.deptno and loc = 'DALLAS';

--hr用户
--查询用户的名字，职位名称，部门名称，所在城市:

select e.first_name, j.job_title, d.department_name, l.city
from employees e, jobs j, departments d, locations l
where e.job_id = j.job_id and e.department_id = d.department_id
and d.location_id = l.location_id;

--查询用户的名字，所在的大区名字:

select e.first_name, r.region_name
from employees e, departments d, locations l, countries c, regions r
where e.department_id = d.department_id and d.location_id = l.location_id
and l.country_id = c.country_id and c.region_id = r.region_id;

--2.不等值连接:

select *
from emp e, dept d
where e.deptno <> d.deptno

--查询员工姓名，月薪和月薪等级

select *
from emp e, salgrade s
where e.sal between s.losal and s.hisal