Oracle数据库之SQL(高级查询)详解

SQL(高级查询)

1、SQL(高级查询)
子查询 : 嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句
根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询

子查询在WHERE子句中

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

在DDL中使用子查询，快速创建表

其中，单列子查询通常作为过滤条件使用，而多列同时作为表使用
如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE

子查询在HAVING部分
子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分

子查询在FROM部分
如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效

子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式

2、分页查询
SQL标准未定义，不同数据库分页查询不一样
当一次查询的结果集中数据量非常大的时候会导致：网络传输速度慢，系统资源占用大等问题导致的拖慢情况，并且实际用户也很少能一次性需要这么大批量的数据，对此，常用的解决方法就是“分批分段”的将数据查询出来，这就是分页

ROWNUM

ROWNUM被称作伪列，用于 返回标识行数据顺序的数字
该字段不存在任何表中，但是所有表都可以查询该字段，编号的过程是伴随着查询的过程进行的
ROWNUM只能从1计数，不能从结果集中直接截取
如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图，也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件

在使用ROWNUM对结果集编行号的过程中不要使用ROWNUM做> 1以上数字的过滤判断，否则得不到任何结果，因为ROWNUM是在查询过程中生成的，只有查询到数据才会标号，才会自增

使用子查询进行分页
分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能来进行分页。
PageN: (n - 1) * pageSize + 1 至 n * pageSize

3、DECODE函数
DECODE函数基本语法
DECODE函数的语法如下：
DECODE (expr, search1, result1[, search2, result2…][, default])
它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

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

DECODE函数可以按字段内容分组

DECODE函数也可以按字段内容排序

4、排序函数
ROW_NUMBER
ROW_NUMBER语法如下：

根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一

ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集
表示结果集按照指定的字段分组，然后组内按照指定字段进行排序，之后为每组的记录生成行号

RANK

RANK函数的语法如下：

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。
ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

不连续，不唯一

DENSE_RANK

DENSE_RANK函数的语法如下：

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。

连续，不唯一

5、高级分组函数
OLLUP
ROLLUP、CUBE 和 GROUPING SETS 运算符是 GROUP BY 子句的扩展，可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法形式如下：
• GROUP BY ROLLUP(a, b, c)
• GROUP BY CUBE(a, b, c)
• GROUP BY GROUPING SETS ( (a), (b))

CUBE
对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次分组


GROUPING SETS

GROUPING SETS 运算符可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。
如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。

6、集合操作
UNION、UNION ALL
为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。
集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。
ORDER BY子句只能放在最后的一个查询语句中

INTERSECT
INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列

MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果