Oracle 数据库学习笔记03

Oracle 数据库 ( SQL 高级查询)

1. 子查询

子查询就是一条SQL语句,它是嵌套在其他SQL语句中的,目的是为实际的SQL语句提供数据
子查询可以应用在 DQL, DDL, DML 中

查看比 CLARK 工资高的员工信息

SELECT ename,sal FROM emp
WHERE sal > (SELECT sal FROM emp
             WHERE ename = 'CLARK')

查找和CLARK相同部门的其他员工

SELECT ename, deptno FROM emp
WHERE deptno = (SELECT deptno FROM emp
                WHERE ename='CLARK')
AND ename<>'CLARK'

• 子查询在 DDL 中的应用:
  可以根据查询结果集快速创建一张表

CREATE TABLE myemployee AS
SELECT e.empno, e.ename,e.sal,e.job,e.deptno,d.dname,d.loc
FROM emp e JOIN dept d
ON e.deptno= d.deptno;

• 子查询在 DML 中的应用:
  将CLARK所在部门所有员工的工资加500

UPDATE emp
SET sal= sal+500
WHERE deptno=(SELECT deptno FROM emp WHERE ename='CLARK')

子查询根据查询结果不同,分为:
1. 单行单列子查询: 常用作过滤条件
2. 多行单列子查询: 常用作过滤条件
3. 多行多列子查询: 常当做表看待

• 多行单列子查询
  查看与职位是 SALESMAN 同部门的其他员工

SELECT ename, job, deptno FROM emp
WHERE deptno IN (SELECT deptno FROM emp
                  WHERE job='SALESMAN')
AND job <> 'SALESMAN'

• 多行多列子查询
  查询比本部门平均工资高的员工信息

SELECT e.ename, e.sal, e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,deptno FROM emp
            GROUP BY deptno) s
WHERE e.deptno = s.deptno
AND e.sal > s.avg_sal

练习:

-- 查询4人以上(包含4人)的部门员工的工资是多少
-- 方法1
SELECT e.ename,e.sal,e.deptno
FROM emp e JOIN (SELECT deptno FROM emp
             GROUP BY deptno HAVING COUNT(*)>=4 ) d
ON e.deptno=d.deptno;
-- 方法2
SELECT ename, sal,deptno
FROM emp
WHERE deptno IN (SELECT deptno FROM emp
             GROUP BY deptno HAVING COUNT(*)>=4 );
-- 方法3
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT COUNT(*) per_num, deptno FROM emp
            GROUP BY deptno) d
WHERE e.deptno = d.deptno
AND d.per_num>=4;

EXISTS 关键字

该关键字后面跟一个子查询,用于在 WHERE 中作为过滤条件使用,是用来
判断其后的子查询是否可以查出数据,若可以则 EXISTS 返回true, 否则返回 false

查询有员工的部门

SELECT d.deptno , d.dname, d.loc FROM  dept d
WHERE EXISTS(SELECT *FROM emp e
            WHERE e.deptno = d.deptno);

查询没有员工的部门

SELECT d.deptno , d.dname, d.loc FROM  dept d
WHERE NOT EXISTS(SELECT *FROM emp e
            WHERE e.deptno = d.deptno)

查看有下属的员工的工号,名字,职位,工资

SELECT e.empno, e.ename, e.job,e.sal
FROM emp e
WHERE EXISTS(SElECT *FROM emp m WHERE e.empno=m.mgr )

---

2. 分页查询

• 分页查询是分段查询数据,在查询的数据量非常大的时候尤其重要.分页查询可以减少,系统资源消耗,响应速度快.
  不同的数据库分页的语法不一致
• Oracle中使用 ROWNUM 解决分页,
  ROWNUM 是一个伪列,不存在于任何一张表中,但是每张表都可以查询该字段.
  在查询某张表示,只要可以查询出一条记录,该字段的值就是该条记录的行号,从1开始生成的过程在查询过程中进行

举例一:

SELECT ename, sal, deptno FROM
WHERE ROWNUM BETWEEN 6 AND 10

以上查询不出任何数据
原因: 编号是在查询的过程中进行的,所以不能用 ROWNUM 在编号的过程中进行>1以上的数据的判断,否则查询不出数据

正确的做法:

SELECT ename, sal, deptno FROM
(SELECT ROWNUM rn,ename,sal,deptno FROM emp)
WHERE rn BETWEEN 6 AND 10

注意 : 子查询中 ROWNUM 必须要指定别名才能保证外层查询使用的是 子查询中的 ROWNUM

举例二 :
分页与 ORDER BY
查询 工资排名 6 到 10 的员工信息

SELECT * FROM
(SELECT ROWNUM rn ,t.*
 FROM (SELECT ename, sal, deptno
        FROM emp
        ORDER BY sal DESC) t
  WHERE ROWNUM <= 10)           -- 第10条数据之后不再编号,提高效率
WHERE rn BETWEEN 6 AND 10;

若有排序需求,一定要先排序,因为排序操作是在最后执行的.

计算分页公式

page : 页号
pageSize : 每页的条目数

start : ( page - 1 ) * pageSize + 1

end : pageSize * page

---

3 . 排序函数

排序函数允许按照指定字段分组, 再按照指定字段排序, 然后生成组内编号

• 
  
• ROW_NUMBER ()
  

生成组内连续且唯一的数字
查看每个部门的工资排名:

SELECT ename, sal, deptno, ROW_NUMBER() OVER(
                            PARTITION BY deptno
                            ORDER BY sal DESC   
                            )
FROM emp;

• 
  
• RANK ()
  

生成组内不连续也不唯一的数字
查看每个部门的工资排名(允许相同名次):

SELECT ename, sal, deptno, RANK() OVER(
                            PARTITION BY deptno
                            ORDER BY sal DESC   
                            )
FROM emp;

• 
  
• DENSE_RANK ()
  

生成组内连续但不唯一的数字
查看每个部门的工资排名(允许相同名次,但名次不跳跃):

SELECT ename, sal, deptno, DENSE_RANK() OVER(
                           PARTITION BY deptno
                           ORDER BY sal DESC   
                           )
FROM emp;

---

4. DECODE 函数

DECODE 函数用于比较的 参数 1
若匹配 参数 2 则 返回参数 3
若匹配 参数 4 则 返回参数 5
… 最后一个默认值

查询职员的职位来计算奖金

SELECT ename, job , sal, DECODE(job,
                                'MANAGER',sal*1.2,
                                'ANALYST',sal*1.1,
                                'SALESMAN',sal*1.05
                                ) bonus
FROM emp;

计算指定类别职位的人数
如:指定 MANAGER 和 ANALYST 为 VIP , 其他为 OPERATION, 分别统计这两类职位的人数

SELECT DECODE(job,
              'ANALYST','VIP',
              'MANAGER','VIP',
              'OPERATION'
              ) job,
        COUNT(*) job_cnt
FROM emp
GROUP BY  DECODE(job,
              'ANALYST','VIP',
              'MANAGER','VIP',
              'OPERATION'
              )      

---

5 . 集合操作

• UNION : 获取结果集并集,并去除重复
• UNION ALL : 获取结果集并集,但不去重
• INTERSECT : 获取结果集交集
• MINUS : 获取结果集的差集(结果集 1 中存在, 结果集 2 中不存在)

示例:(两个查询子句的查询字段必须相同)

SELECT ename,job,sal FROM emp
WHERE job = 'MANAGER'
UNION -- 这里替换其他集合操作
SELECT ename,job,sal FROM emp
WHERE sal>2500

---

6 . 高级分组函数

• 
  
• ROLLUP()
  

分组情况递减
GROUP BY ROLLUP(a , b , c) 等同于

SELECT... GROUP BY a, b ,c
UNION ALL
SELECT... GROUP BY a, b  
UNION ALL
SELECT... GROUP BY a
UNION ALL
全表

• 
  
• CUBE()
  

分组情况全排列
GROUP BY CUBE0(a , b , c) 等同于 (内部实现并非 UNION ALL, 效率更高但效果相同) :

SELECT... GROUP BY a, b , c
UNION ALL
SELECT... GROUP BY a, b  
UNION ALL
SELECT... GROUP BY a, c
UNION ALL
SELECT... GROUP BY c
UNION ALL
SELECT... GROUP BY b, c  
UNION ALL
SELECT... GROUP BY b
UNION ALL
SELECT... GROUP BY c
UNION ALL
全表

• 
  
• GROUPING SETS()
  

自定义分组
如:
SELECT... GROUP BY GROUPING SETS((year, month), (month))
指定

SELECT... GROUP BY year , month
UNION ALL
SELECT.. GROUP BY month