oracle 数据库4——高级查询

你的名字 2022-06-17 00:16 342阅读 0赞

## 1. 常见高级查询有哪些？ ##

<table> 
 <thead> 
  <tr> 
   <th>高级查询</th> 
   <th>细分</th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>子查询</td> 
   <td>子查询在 where 子句</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>子查询在 having 子句</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>子查询在 from 子句</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>子查询在 select 子句</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>分页查询</td> 
   <td>ROWNUM</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>使用子查询进行分页</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>分页与 order by</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>DECODE 函数</td> 
   <td>DECODE 函数基本语法</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>DECODE 函数在分组查询中的应用</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>排序函数</td> 
   <td>ROW_NUMBER /RANK /DENSE_RANK</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>高级分组函数</td> 
   <td>ROLLUP/ CUBE/ GROUPING SETS</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>集合操作</td> 
   <td>UNION</td> 
   <td>取并集，不会有重复</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>UNIONALL</td> 
   <td>取并集，重复的会出现</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>INTERSECT</td> 
   <td>取交集</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>MINUS</td> 
   <td>取差集</td> 
  </tr> 
 </tbody> 
</table>

## 2.常用的高级查询详解 ##

**子查询**

1.  子查询中如果某个字段是表达式，则该字段必须给别名，否则会报错。

**子查询在 where 子句中**

--查看谁的工资高于 CLARK？
    select sal,name from emp where sal>(select sal from emp where name='CLARK');
    
    --查看与 CLARK 职位相同的员工
    slect name,job
    from emp
    where job=(select job from emp where name='CLARK') and name <>'CLARK';

**子查询在 having 中**

--查看部门的平均工资，前提是该部门的平均工资要高于或等于20部门的平均工资
    select AVG(sal),deptno from emp group by deptno having AVG(sal)>=(select AVG(sal) from emp where deptno=20);

**子查询在 from子句 中**  
如果要在一个子查询的结果中继续查询，则子查询出现在 from 子句中，这个子查询页称作行内视图或者匿名视图。

--查询出薪水比本部门平均薪水高的员工信息
    select * from emp e,（select AVG(sal) avg_sal,deptno from emp group by deptno）t where e.deptno = t.deptno and e.sal>t.avg_sal order by e.deptno DESC;

**子查询在 select 子句中**  
可以认为是外连接的另一种表现形式，使用更灵活

SELECT e.ename,e.sal,(SELECT d.dname FROM dept_qhj d WHERE d.deptno=e.deptno) dname FROM emp_qhj e

**分页查询**

1.  当查询数据量过大时，会导致响应速度慢，资源消耗大等问题，所以常常采用分页查询的方式分段将数据查询出来。
2.  分页查询不同的数据库可能完全不同，是方言
3.  ROWNUM，伪列，不存在于任何一张表中，但所有表都可以查询该字段，该字段的值是可以动态生成的，是在查询表生成结果集的过程中动态产生值的。原则上从 1 开始，逐次递增，在查询过程中，只要能从表中查询出一条记录，ROWNUM 的值就是该记录的行号。
4.  在使用 ROWNUM为结果集编行号的查询过程中，不要做 ROWNUM>1 的过滤判断，否则将得不到任何结果。

select rownum ,name,sal from emp where rownum <3;
    
    
    select * from (select rownum rn,name,sal from emp) where rn between 5 and 10;
    
    
    --??????效率低
    SELECT * FROM (SELECT ROWNUM rn ,t.* FROM (SELECT ename,sal,job FROM emp_qhj ORDER BY sal DESC) t) WHERE rn BETWEEN 6 AND 10 --效率高 SELECT * FROM (SELECT ROWNUM rn,t.* FROM (SELECT ename,sal,job FROM emp_qhj ORDER BY sal DESC) t WHERE ROWNUM<=10) WHERE rn>=6 --分页查询(page：表示显示第几页 size:每页显示多少条数据) start ： (page - 1)*size + 1 end：page*size

**DECODE 函数**  
1. DECODE 函数可以实现简单的分支效果  
2. DECODE函数可以在SELECT 子句，GROUP BY 子句，ORDER BY 子句中使用  
3. 注意DECODE在SELECT子句中作为参数

SELECT ename,job,sal,
           DECODE(job,
                  'MANAGER',sal*1.2,
                  'ANALYST',sal*1.1,
                  'SALESMAN',sal*1.05,
                  sal
                  )bonus --别名
    FROM emp_qhj