关系型数据库基础及应用（四）——SQL查询（以Oracle为主）

SQL（高级查询）

子查询

子查询在where中的应用

· 在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定值，而是来自于另一个查询的结果；

· 为了给查询提供数据而首先执行的查询语句叫做子查询；

· 子查询是嵌入在其他SQL语句中的SELECT语句，大部分时候出现在WHERE子句中；

· 子查询嵌入的语句称为主查询或者父查询；

· 主查询可以是SELECT语句，也可以是其他类型的语句，比如：DML或DDL语句；

（1）根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询；

--查询和SCOTT同职位的员工
--先执行子查询，得到结果后在进行主查询
SELECT e.ename,e.job FROM emp e WHERE e.job=(SELECT job FROM emp WHERE ename = 'SCOTT')

（2） 如果子查询返回多行，主查询中要使用多行比较操作符

· 多行比较操作符包括IN、ALL、ANY。其中ALL和ANY不能单独使用，必须配合比较操作符使用；

--查询出部门SALESMAN但职位不是SALESMAN的员工信息
--子查询的结果是多个值
SELECT empno,ename,job,sal,deptno FROM emp
WHERE deptno IN
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN'

（3）在子查询中需要引用到主查询的字段数据，使用EXISTS关键字;

· EXISTS后面的子查询至少返回一行数据，则整个条件返回TRUE;

--列出那些有员工的部门信息
SELECT deptno,dname FROM dept d
WHERE EXISTS
(SELECT * FROM emp e WHERE d.deptno=e.deptno)

子查询在HAVING子句中

-- 查询列出最低薪水高于部门30的最低薪水的部门信息
SELECT deptno,MIN(sal) min_sal FROM emp
GROUP BY deptno HAVING MIN(sal)>
(SELECT MIN(sal) FROM emp WHERE deptno=30);

子查询在FROM部门

· FROM子句用来指定要查询的表；

· 如果要在一个子查询的结果中继续查询，则子查询出现在FROM子句中，这个子查询也称作行内视图或者匿名视图；

· 把子查询当做视图对待，但视图没有名字，只能在当前SQL语句中有效；

-- 查询出薪水比本部门平均薪水高的员工信息
-- 子查询当表一样使用
SELECT e.deptno,e.ename,e.sal FROM emp e
(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno)x 
WHERE e.deptno = x.deptno and e.sal > x.avg_sal ORDER BY e.deptno;

子查询在SELECT部分

· 把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活；

SELECT e.ename,e.sal,
(SELECT d.deptno FROM dept d WHERE d.deptno = e.deptno) deptno
FROM emp e;

分页查询

ROWNUM

· 被称作伪列，用于返回标识行数据顺序的数字；

SELECT ROWNUM,empno,ename,sal FROM emp;

· 只能从1计数，不能从结果集中直接截取

-- 查询不到结果
SELECT ROWNUM ,empno,ename,sal FROM emp WHERE rownum > 3;

· 利用ROWNUM截取结果集中的部分数据，需要用到行内视图；

SELECT * FROM  (SELECT ROWNUM rn,e.* FROM emp e)
WHERE rn BETWEEN 8 AND 10;

使用子查询进行分页查询

· 分页策略：每次只取一页的数据，每次换页，取下一页的数据；

· 在ORACLE中利用ROWNUM的功能可用来进行分页（ORACLE特有；MySQL是用LIMIT）

· 假设结果集共有105条，每20条分为一页，则：

    page1：1至20；

    page2：21至40;

    ...

    PageN：（n-1）*pageSize+1 至 n*pageSize

    共6页；

分页与ORDER BY

select * from  
    (select rownum rn,m.* from 
       (select * from COST t order by unit_cost desc) m)
where rn between 8 and 10;

· 根据要查看的的页数，计算起点值，（（n-1）*pageSize + 1）和终点值（n*pageSize），替换掉between和and 的参数，即得到当前页的记录；

DECODE函数

DECODE函数基本语法

· DECODE(expr,search1,result1[,search2,result2...][,default])；

· DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果；

· 可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后的default的值；

· default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL；

· 举例：

-- 查询职员表，根据职员的职位计算奖励金额，当职位分别是‘MANAGER’、‘ANALYST’、‘SALESMAN’时，
-- 奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值
SELECT ename,job,sal
    DECODE (job,
            'MANAGER',sal*1.2,
            'ANALYST',sal*1.1,
            'SALESMAN',sal*1.05,
            sal
) bonus FROM emp;

· 和DECODE函数功能相似的有CASE...WHEN...ELSE语句，实现类似于if-else的操作；

SELECT ename,job,sal
    CASE job WHEN 'MANAGER' THEN sal*1.2
             WHEN 'ANALYST' THEN sal*1.1
             WHEN 'SALESMAN' THEN sal*1.05
             ELSE sal END 
bonus FROM emp;

DECODE函数在分组查询中的应用

（1）按字段内容分组

· 举例：计算职位的人数，analyst、manager属于vip，其余是普通员工operation，无法用GROUP BY简单实现；

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

（2）按字段内容排序

· 举例：Dept表中按“OPERATIONS”、“ACCOUNTION”、“SALES”排序，无法按照字面数据排序；

SELECT deptno,dname,loc
FROM dept 
ORDER BY
DECODE(dename,'OPERATION',1,'ACCOUNTING',2,'SALES',3);

高级分组函数

注：高级分组函数都是跟在GROUP BY 后面的

· ROLLUP、CUBE和GROUPING SETS 运算符是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效实现统计查询；

· GROUP BY ROLLUP(a,b,c);

· GROUP BY CUBE(a,b,c);

· GROUP BY FROUPING SETS((a),(b));

RollUp函数：从右到左逐级递减统计

· 假设有表test，有a,b,c,d四个列；

SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c

  等价于：

SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c 
    UNION ALL
SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
    UNION ALL
SELECT a,null,null,SUM(d) FROM test GROUP BY a
    UNION ALL
SELECT null,null,null,SUM(d) FROM test 

· 对ROLLUP的列从右到左一次少一列的方式进行分组直到所有列都去掉后的分组（也就是全表分组）；

· 对与n个参数的ROLLUP，有n+1次分组；

· 举例：

（1）准备数据：2010-2011年每月每天的销售额

创表语句：

create table sales_tab(
    year_id NUMBER NOT NULL,
    month_id NUMBER NOT NULL,
    day_id NUMBER NOT NULL,
    sales_value NUMBER(10,2) NOT NULL
)

（2）插入数据：给测试表插入随机数据，2010年-2011年，1-12月，1-31天，销售额是小于100的随机浮点数；

INSERT INTO sales_tab
    SELECT TRUNC(DBMS_RANDOM.value(low => 2010,high => 2012)) as year_id,
        TRUNC(DBMS_RANDOM.value(low => 1,high => 13)) as month_id,
        TRUNC(DBMS_RANDOM.value(low => 1,high => 32)) as day_id,
        ROUND(DBMS_RANDOM.value(low => 1,high => 100),2) as sales_value
FROM DUAL
CONNECT BY level <= 1000;

（3）使用ROLLUP分组函数，查询数据

SELECT year_id,month_id,SUM(sales_value)AS sales_value 
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id)
ORDER BY SALES_VALUE desc;

SELECT year_id,month_id,day_id,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY ROLLUP (year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id;

CUBE函数：所有子集组合统计

· GROUP BY CUBE(a,b,c)

· 对CUBE的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合；

· 对于n个参数的cube，有2^n次分组；

· GROUP BY CUBE（a,b,c）首先对（a,b,c）进行GROUP BY 然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后对全表进行GROUP BY操作，一共是2^3 = 8次分组；

SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c)

   等价于

SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
UNION ALL
SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
UNION ALL
SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
UNION ALL
SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
UNION ALL
SELECT NULL,NULL,NULL,SUM(d) FROM test;

· 注意：等价只是方便理解，其内部运行机制并不相同    ，其效率远高于UNION ALL

· 举例

SELECT year_id,month_id,SUM(sales_value) AS sales_value 
FROM sales_tab
GROUP BY CUBE(year_id,month_id)
ORDER BY year_id,month_id;

GROUPING SETS函数：每一个参数为一种分组方式

· 与CUBE 对比

SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY CUBE(year_id,month_id)
ORDER BY 1,2;

SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY GROUPING SETS((year_id),(month_id))
ORDER BY 1,2;

· 分组示例

  - 使用GROUP BY GROUPING SETS(a,b,c)，则对（a），（b），（c）进行GROUP BY；

  - 使用GROUP BY GROUPING SETS((a,b),c)，则对(a,b)，（c）进行GROUP BY；

  - 使用GROUP BY GROUPING SETS(a,a)，则对（a）进行2次GROUP BY，GROUPING SETS 的参数允许重复；

排序函数

ROW_NUMBER函数：生成组内连续且唯一的数字

· ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2)；

· 表示根据col1分组，在分组内部根据col2排序；

· 此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一；

· Rownum是伪列，ROW_NUMBER功能更强大，可以直接从结果集中取出子集；

-- 按照部门编码分租显示，每组内按职员编码排序，并赋予组内编码
SELECT deptno,ename,empno,
  ROW_NUMBER()
    OVER (PARTITION BY deptno ORDER BY empno) AS emp_id 
FROM emp

RANK函数：生成组内不连续但唯一的数字

· RANK()OVER(PARTITION BY col1 ORDER BY col2)；

· 表示根据col1分组，在分组内部根据col2给予等级标识；

· 等级标识即排名，相同的数据返回相同排名；

· 跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名；

· 和ROW_NUMBER的区别是有重复值，而ROW_NUMBER没有；

-- 按照部门编码分组，同组内按薪水倒序排列，相同薪水则按照奖金数正序排序，并给予组内等级，用Rank_ID表示
SELECT deptno,ename,sal,comm,
    RANK()OVER(PARTITION BY deptno 
    ORDER BY sal DESC,comm) "Rank_ID" 
FROM emp;

DENSE_RANK函数：生成组内连续但不唯一的数字

· DENSE_RANK()OVER(PARTITION BY col1 ORDER BY col2);

· 表示根据col1分组，在分组内部根据col2给予等级标识；

· 即排名，相同的数据返回相同排名；

· 连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序的；

集合操作

· 为了合并多个SELECT 语句的结果，可以使用集合操作符，实现集合的并、交、差;

· 集合操作包括UNION、UNOIN ALL、INTERSECT和MINUS;

· 多条件集合操作的SELECT 语句的列的个数和数据类型必须匹配；

· ORDER BY子句只能放在最后一个查询语句中

· 操作语法

SELECT table1
    UNION|UNION ALL|INTERSECT|MINUS
SELECT table2

UNION、UNION ALL函数

· 用来获取两个或两个以上结果集的并集；

· UNION操作符会自动去掉合并后的重复记录；即相交的只出现一次；

· UNION ALL 返回两个结果集中的所有行，包括重复的行；即相交的出现两次；

· UNION 操作符对查询结果排序，UNION ALL 不排序；

INTERSECT函数

· 获得两个结果集的交集，只有同时存在于两个结果集中的数据，才会被显示输出；

· 使用INTERSECT操作符后的结果集会以第一列的数据作升序排列；

MINUS函数

· 获取两个结果集的差集；

· 只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能被显示出来，也就是结果集一减去结果集二的结果；