Oracle查询知识点

数据查询语言DQL

数据查询语言DQL是SQL中的重点和难点。
1.大部分的面试都会涉及。
2.非常常用，必定涉及查询语句
3.部门内容挺复杂的

因此，这部分需要强化学习，我也会尽可能介绍的详细一些。

语句

SELECT * FROM A WHERE id=1

在上面的语句中，* 表示查询所有列，如果要查询具体的列，则列出具体列的字段名即可。WHERE 后跟过滤条件。

别名

在查询列的后面是可以添加别名的。
1.如果查询项是表达式或函数，那么查询的结果集就是以表达式或函数作为字段的名字，可读性很差。
2.别名可以根据自己的想法设置，没有限制。除了不能是Oracle的关键字
3.别名本身不区分大小写，也不能含有括号，如果需要区分大小写或者有空格，需在别名4.上加上双引号。
4.有些时候必须使用别名。

符号

有=、>、<、>=、<=、!=、<>
其中Oracle中的赋值用=
!=和<>都表示不等于

AND/OR关键字

AND表示同时满足多个条件，与Java中的&&一样
OR表示多个条件只需要满足一个，与Java中的||一样

AND的优先级高于OR，可以用括号提高OR的优先级

LIKE关键字

LIKE用于模糊匹配字符串，支持两个通配符：
_：单一的一个字符
%：任意个字符

IN/NOT IN

判断在列表中/判断不在列表中
WHERE NAME IN (‘zs’,‘ls’)
WHERE NAME NOT IN (‘zs’,‘ls’)

通常用于子查询中

BETWEEN…AND…

判断在一个区间范围内
示例：

SELECT * FROM sal BETWEEN 5000 AND 10000

注意：BETWEEN后面跟小的，AND后面跟大的
不仅可以判断数字，也可以比较其他，比如日期。

ANY/ALL

ANY/ALL不能单独使用，需要配合单行比较操作符>,<,>=,<=一起用。
‘>ANY’ 大于最小
‘<ANY’ 小于最大
’>ALL‘ 大于最大
‘<ALL’ 小于最小

通常用于子查询

DISTINCT

对结果集中指定的字段重复的记录进行去重,
也可以对多个字段去重，是多个字段的组合没有重复的
DISTINCT 修饰的字段必须在第一个，否时会报错

SELECT DISTINCT age FROM A

ORDER BY

根据指定字段进行数据排序，不写或ASC为升序，DESC为降序
ORDER BY 子句必须在SQL的最后
示例：

SELECT * FROM A WHERE age is not null ORDER BY age DESC

表示查询年龄补位空的信息，以年龄的降序排列。即年龄大的在上面。

可以按照多个字段排序
此时有优先级
先按照第一个字段排序，第一个字段有重复时，按照第二个字段排序

当排序的字段中有NULL值时，NULL被认作为最大值

聚合函数

聚合函数又被称为多行函数，分组函数
是对结果集某些字段的值进行统计

聚合函数都是忽略NULL的
可以配合NVL使用

MAX/MIN

指定字段的最大值和最小值

AVG/SUM

指定字段的平均值和总和

COUNT

统计给定字段补位NULL的记录数

SELECT COUNT(*) FROM A

表示查出A表中的记录总数

分组函数

GROUP BY 可以将结果集按照指定的字段值相同的记录看作一组，然后配合聚合函数进行更细分的统计工作。
一旦查询字段项中有聚合函数出现，就不能有其他字段，如果非要有，就必须将这些字段项放入GROUP BY 中

GROUP BY 可以根据多个字段进行分组
是将这几个字段都相同的看作一组

HAVING

WHERE 中不能使用聚合函数，因为过滤时机不对。
聚合函数最为过滤条件必须要在HAVING子句中，并且HAVING必须跟在GROUP BY 子句后，
HAVING 用来过滤分组函数

查询语句执行顺序

1. FROM 子句：执行顺序从后往前，从右往左（数据量小的表尽量放在后面）
2. WHERE 子句：执行顺序自下而上，从右往左（将能过滤掉大量数据的条件放在最右边）
3. GROUP BY 子句：从左往右分组（最好在GROUP BY 之前用WHERE将不需要的记录先过滤掉）
4. HAVING 子句：消耗资源（尽量避免使用，非常耗资源，HAVING会在检索出所有记录之后才对结果集进行过滤，需要排序等操作）
5. SELECT 子句：少用*号，尽量使用字段名称（Oracle解析时会通过查询数据字典将星号转为所有的字段名，消耗时间）
6. ORDER BY 子句：执行顺序从左往右，消耗资源

关联查询

从多张表中查询对应记录的信息。
关联查询的重点在于这些表中的记录的对应关系，这个关系也称为连接条件

当两张表有同名字段时，SELECT子句中必须明确指定该字段来自哪张表，在关联查询中，表名也可以添加别名。

关联查询时要添加查询条件，否在会出现笛卡儿积
笛卡尔积通常是一个毫无意义的结果集，他的记录数是所有参与查询的表的记录数乘积的结果。
要避免出现。数据量大时极易出现内存溢出等现象。
N张表关联查询要有至少N-1个查询条件。
不满足连接条件的记录是不会在关联查询中被查询出来的

内连接

JOIN … ON …
内连接也是用来关联查询的

外连接

外连接除了会把满足连接条件的记录查出来外，还可以把不满足连接条件的查出来。
外连接分为左外连接、右外连接和全外连接。
左外连接 LEFT OUTER JOIN：以JOIN左侧表作为驱动表（所有记录都会查出来），那么当该表中某条记录不满足链接条件时，来自右侧表中的字段全部填NULL.
右外连接 RIGHT OUTER JOIN:以JOIN右侧表作为驱动表（所有记录都会查出来），那么当该表中某条记录不满足链接条件时，来自左侧表中的字段全部填NULL.
全外连接 FULL OUTER JOIN:就是左右表不满足的都会查出来。

自连接

自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单张表的多个列
自连接是为了解决同类型数据但又存在上下级关系的树状结构数据时使用。

子查询

子查询是一条SELECT语句，但它是嵌套在其他SQL中，为的是给该SQL提供数据以支持其执行操作。

此处是说的其他SQL，而不是查询语句中，子查询是可以嵌套在其它各种SQL的，比如DDL，就可以通过子查询创建一张查询出来数据的表。

子查询可分为单行子查询、多行子查询及多列子查询。
单行单列子查询：常用于过滤条件，可以配合>、=、<、>=、<=使用
多行单列子查询：常用于过滤条件，判断=时用in，判断>、>=等时用ANY、ALL
多行多列子查询：常当作一张表看待

EXISTS关键字

EXISTS后面跟一个子查询，当该子查询可以查询出一条记录时，则EXISTS表达式成立并返回true。

分页查询

是将查询表中数据时分段查询，而不是一次性将所有数据查询出来，有时查询的数据量非常庞大，这会导致系统资源消耗大，相应速度长，数据冗余严重。

数据库基本都支持分页，但是不同数据库语法不同

ROWNUM

Oracle中的分页是基于伪列rownum实现的
rownum不存在与任何一张表中，但是所有表都可以查该字段，该字段的值是随着查询自动生成的，方式是：每当可以从表中查询出一条记录时，该字段的值即为该条记录的行号，从1开始逐次递增。
在使用rownum对结果集进行编号的查询过程中不能使用rownum做>1以上的数字判断，否在将查询不出任何数据。

计算区间公式：
pageSize:每页显示的条数
page:页数

star:(page-1)pageSize+1
end:pageSizepage

DECODE函数

decode(a,b,c[,d,e……],m)
如果a=b,返回c
如果d=e,返回e
……
都不匹配则返回m（因为编辑器键位冲突，上面的表达式没有表达清楚，m可以写也可以不写，不写如果没有匹配上就返回null）

decode在group by分组中的应用可以将字段值不同的记录看作一组

CASE函数

case a when b then c
when d then e
else f end

排序函数

排序函数允许对结果集按照指定的字段分组，再在组内按照指定的字段排序，最终生成组内编号。

ROW_NUMBER()

生成组内连续且唯一的数字,PARTITION BY 按什么分组，ORDER BY 按什么排序
ROW_NUMBER() OVER(
PARTITION BY a，
ORDER BY b
)

RANK()

生成组内不连续也不唯一的数字，组内排序字段值一样的，生成的数字也一样。
RANK() OVER(
PARTITION BY a，
ORDER BY b
)
除了函数名不一样 其他都一样

DENSE_RANK()

生成组内连续，但不唯一的数字
DENSE_RANK() OVER(
PARTITION BY a，
ORDER BY b
)

高级分组函数

UNION 、UNION ALL

并集/全并集
并集：两个结果集的总记录，去除重复记录
全并集：两个结果集的总记录，包括重复记录

INTERSECT

交集：两个结果集共有的记录

MINUS

差集：前面的结果集有，后面的结果集没有的记录

ROLLUP CUBE GROUPING SETS是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。每一个高级分组函数都有一套分组策略。

GROUP BY ROLLUP(a,b,c)
分组原则：参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果并且并在一个结果集中。

GROUP BY CUBE(a,b,c)：每种组合分一次组
分组次数：2的参数个数次方

GROUP BY GROUPING SETS((a),(b))
每一个参数是一种分组方式，然后将这些分组统计后并在一个结果集显示