Oracle高级查询语句学习



/*
一、伪列：

  CURRVAL AND NEXTVAL 使用序列号的保留字

  LEVEL 查询数据所对应的层级

  ROWID 记录的唯一标识

  ROWNUM 限制查询结果集的数量
二、伪表

DUAL 表

该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。

一般用于验证函数。例如：

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual

*/

--select * from emp;
--select * from DEPT
--select * from SALGRADE
--select max(sal) from emp
//WM_CONCAT：行转列函数
--select deptno,WM_CONCAT(ename) from emp group by DEPTNO
--select deptno,replace(WM_CONCAT(ename),',','|') 职位 from emp group by deptno
//分组函数和空值(分组函数会略掉空值)
select sum(sal) / count(*) sal1,sum(sal) / count(sal) sal2,avg(sal) sal3 from emp;
select sum(comm)/ count(*) sal1,sum(comm)/ count(comm) sal2,avg(comm) sal3 from emp;
--NVL函数使分组函数无法忽略空值
select sum(nvl(comm,0))/ count(*) sal1,sum(nvl(comm,0))/ count(nvl(comm,0)) sal2,avg(nvl(comm,0)) sal3 from emp;

/*
GROUP by子句：情况一：在select列表中所有未包含在组函数中的列都必须包含在group by 子句中
示例：求每个部门的平均工资，要求显示部门号及部门的平均工资
              情况二：在group by子句中包含的列不一定要包含在select列表中
示例：求每个部门的平均工资，要求显示部门的平均工资
//where 和having都是过滤结果，区别在于：
不能在where子句中使用组函数而可以在having子句中使用
在sql优化角度考虑，尽量使用where【因为having是先分组后过滤的，where反之】
//排序
order by:asc升序默认情况 desc 降序 
order by sal  [或加序列号order by 1]
*/
select deptno,avg(sal) from emp group by DEPTNO having avg(sal)>2000 order by 1;

/*分组函数的嵌套*/
select  max(avg(sal))  from emp group by DEPTNO;
--group by 语句的增强
/*
sqlplus:break on deptno skip 2【格式设置】
           set pagesize 30;
*/
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
--上面的sql语句等价于以下三条语句相加的结果
-- select deptno,job,sum(sal) from emp group by deptno,job;
-- +
-- select deptno,sum(sal) from emp group by deptno;
-- +
-- select sum(sal) from emp ;

/**多表查询知识点：
笛卡尔积：
等值连接：连接条件为“=”
不等值连接：>、<、betweeen ... and ...
*/
select ename,emp.deptno,sal FROM emp,dept where EMP.DEPTNO=DEPT.DEPTNO ;

/*外连接核心：通过外连接将对于连接条件不成立的记录任然包含在最后的结果中
 左外连接：当连接条件不成立的时候，等号左边的表任然被包含 e.DEPTNO=d.DEPTNO(+)
 右外连接：当连接条件不成立的时候，等号右边的表任然被包含 e.DEPTNO(+)=d.DEPTNO
*/
select d.deptno 部门号 ,dname 部门名称,count(e.empno) 职工人数 from emp e,DEPT d
where e.DEPTNO(+)=d.DEPTNO group by d.DEPTNO,dname; --右外连接【40号部门员工数为0】
/*自连接：通过别名，将同一张表视为多张表
自连接存在的缺陷：不适合操作大表（解决方法：使用层次查询【是一个单表查询】）
*/
--查询员工信息和员工老板的信息
select * from emp;
select e.ename 员工姓名 ,e1.ename 老板姓名 from emp e,emp e1 where e.MGR=E1.empno(+);--自连接+左连接
--层次查询(为单表查询，不会产生笛卡尔积，但是不直观)
--LEVEL:伪列   start with：表示从...开始
select LEVEL,empno,ename,sal,mgr from emp
CONNECT by PRIOR empno=mgr start with mgr is null order by 1;

/********************************oracle子查询**********************************************/
/*子查询可以返回单行结果，可以返回多行结果，也可以不返回结果。如果子查询未返回任何行，
则主查询也不会返回任何结果
子查询注意的10个问题：
1、子查询语法中的小括号
2、子查询书写风格
3、可用子查询的位置：where select having  from
4、不可用子查询位置：group by
5、from后面的子查询（强调）
6、主子查询可以不是操作同一张表
7、一般不在子查询中使用排序，但在TOP-N分析问题时必须使用排序
8、一般先执行子查询，再执行主查询。相关子查询例外
9、单行子查询只能使用单方操作符；多行子查询只能使用多方操作符
10、注意子查询为null值问题
*/
--示例：查询工资比scott高的员工信息
select * from emp where sal>(select sal from emp where ename='SCOTT');
--可用子查询的位置：where select having  from
select ename,empno,(select JOB from emp where empno=7839) JOB from emp;

select deptno,avg(sal) from emp group by deptno
having avg(sal)>(select max(sal) from emp where deptno=30);
--FROM 后面的子查询
select * from (select ename,empno,sal from  emp) ;
--示例：查询员工姓名，部门号，月薪（年薪）
select * from (select ename,empno,sal,sal*12 annsal from  emp) ;

--不可用子查询位置：group by
select sum(sal) from emp group by (select DISTINCT deptno from emp );
--[Err] ORA-22818: 这里不允许出现子查询表达式

--一般不在子查询中使用排序，但在TOP-N分析问题时必须使用排序【适用于分页查询】
--Top-N分析问题：按照某个规律排序后取出前几条记录
--ROWNUM 伪列行号的使用要注意两个问题：行号必须永远按照默认顺序生成；行号只能使用<,<=不能使用>,>=
select ROWNUM,ename,empno ,sal from (select ename,empno,sal from emp order by sal desc) where ROWNUM<=5;

--相关子查询(可以将主查询的值作为参数传递给子查询)
SELECT empno,deptno,ename,sal,(select avg(sal) from emp where EMP.deptno=e.deptno)avgsal
from emp e where sal>(select avg(sal) from emp where EMP.deptno=e.deptno);

/*单行子查询只能使用单方操作符；多行子查询只能使用多方操作符
单行操作符：=、>、>=、<、<=、<>
多行操作符：in(等于任何一个) any(和子查询返回的任意一个值比较) all(和所有值比较)
*/
select * from emp where job=(select job from emp where empno='7566')
and sal>(select sal from emp where empno='7782');
--示例：查询工资最低的员工信息
select * from emp where sal=(select min(sal) from emp);
--示例：查询最低工资大于20号部门最低工资的部门号和部门的最低工资
select deptno,min(sal) from emp group by deptno HAVING
min(sal) >(select min(sal) from emp where deptno=20);

--示例多行操作符：查询工资比30号部门任意一个员工高的员工信息
select * from emp where sal > any (select sal from emp where deptno=30);
select * from emp where sal >(select min(sal) from emp where deptno=30);--多行改单行
--查询工资比30号部门（任何一个）所有员工高的员工信息
select * from emp where sal >all (select sal from emp where deptno=30);
select * from emp where sal >(select max(sal) from emp where deptno=30);