《Oracle开发实战经典》第6-8章
聚合函数
- count(*|[distinct]列):统计数量
- 表中没有数据时回返回0,其他的统计函数返回额是null
- count(1)与count(*)得到的结果一致,包含null值。
- count(字段)不计算null值
- count(null)结果恒为0
- sum(列):求总和,操作列为数字
- avg(列):平均值
- max(列):最大值
- min(列):最小值
- median(列):返回中间值
- variance(列):返回方差
- stddev(列):返回标准差
分析函数
- 计算运行总量
- 查找当前行数据占总数居的百分比
- 分区显示
- 计算流动数据行的平均值
- …
-
基本语法
函数名称([参数,....])over(
partition by 子句 字段,...
[order by 子句 字段,...[asc|desc] [nulls frist|nulls last]]
[windowing 子句]
);
-
函数名称:函数的标识
-
over子句:为分析函数指明一个查询结果集
-
parttition by子句:将一个简单的结果集分为N组,而后按照不同的的组对数据进行统计
-
order by子句:明确指明数据在每个组的排序
- nulls frist| nulls last:表示返回数据行中包括null值是出现在排序序列的头还是尾
-
windowing子句:给出在定义变化的固定的数据窗口方法,分析函数正是对这个数据进行统计
-
分窗子句主要用于定义一个变化和固定的数据窗口方法,以及分析函数在操作行的集合
-
实现方式一:值域窗(range window),逻辑偏移。当前分区中当前行的前n行到的记录集
-
实现方式二:行窗(rows window),物理偏移。以排序的结果顺序计算偏移当前行的起始行记录集
-
如果指定range或rows的偏移量,可以考虑一下集中排列顺序
- range|rows 数字 preceding
- range|rows between unbounded preceding and current row
- range|rows between current row and unbounded following
- 排列概念
- preceding:主要设置一个偏移量,偏移量可以是用户设置的数字或是其他标记
- between…and:设置一个操作范围
- unbounded preceding:不限制偏移量大小
- current row:表示当前行
- following:如不写此语句,表示使用上N行与当前行指定数据比较。编写此语句表示当前行与下N行数据进行比较。
-
-
-
分析函数的三种子句partition by,order by,windowing的组合排序有
- 函数名称([参数,…])over(partition by子句,order by子句,windowing子句)
- 函数名称([参数,…])over(partition by子句,order by子句)
- 函数名称([参数,…])over(partition by子句)
- 函数名称([参数,…])over(order by子句,windowing子句)
- 函数名称([参数,…])over(order by子句)
- 函数名称([参数,…])over()
-
分析函数范例
-
数据统计
- sum([distinct|all]表达式) 计算分区的数据累加和
- min([distinct|all]表达式) 查找分区的最小值
- max([distinct|all]表达式) 查找分区的最大值
- avg([distinct|all]表达式) 计算分区的数据平均值
- count(*|[distinct|all]表达式) 计算分区的数据量
-
等级函数
- rank() 根据order by子句的排序字段,从分区查询的每一行数据,安排序生成序号,可能会有相同的序号
- dense_rank() 根据order by子句的排序字段,从分区查询的每一行数据,安排序生成序号,不会有相同的序号
- rank()和dense_rank()函数为记录标记函数,更具order by子句表达式的值自动为每一行设置一个数字序号
- frist 取出dense_rank()返回的集合中第一行数据
- last 取出dense_rank()返回的集合中最后一行数据
- frist_value(列) 返回分组的第一个值
- last_value(列) 返回分组的最后一个值
- lag(列名称[,行数字] [,默认值]) 访问分区中指定前n行的记录,如果没有则返回默认值
- lead(列名称[,行数字] [,默认值]) 访问分区中指定前n行的记录,如果没有则返回默认值
- row_number() 返回每组中的行号
- 行标记函数,自动为每一行生成一个记录号
-
报表函数
- cume_dist() 计算一行在分区中的相对位置
- 会取得整个数据的相对位置,如果假设分区中有5条数据,那么函数会将这5条数据按照1,0.8,0.6,0.4,0.2进行划分,当出现相同数据时,位置号就不会连续了。
- ntile(数字) 将一个分区分成表达式的散列表示
- ratio_to_report(表达式) 该函数计算expression/(sum(expression))的值,他给出相对于总数的百分比
- cume_dist() 计算一行在分区中的相对位置
-
行列转换
-
pivot()函数
select *|列[别名]…
from 子查询
pivot(
统计函数(列)s for 转换列名称 in(
内容1 [[as]别名],
内容2 [[as]别名],
…
内容n [[as]别名],
)
)
[where 条件(s)]
[group by 分组字段1,分组字段2,…]
[having 过滤条件(s)]
[order by 排序字段 asc|desc];
- 子查询:规定的是pivot()函数操作过程,所需使用的数据(设置子查询确定行和列)
- 统计函数:在转化过程中,设置进行统计的数据列及统计函数,可以设置多个统计函数
- for转换列名称:将子查询中返回的指定数据变为显示的列
-
unpivot()函数
select *|列[别名]…
from 子查询
unpivot[include nulls|exclude nulls](
统计函数(列)s for 转换列名称 in(
内容1 [[as]别名],
内容2 [[as]别名],
…
内容n [[as]别名],
)
)
[where 条件(s)]
[group by 分组字段1,分组字段2,…]
[having 过滤条件(s)]
[order by 排序字段 asc|desc];
- include nulls:列变为行住哪话过程中保留null
- exclude nulls(默认):列变为行过程中不保留null数据
-
-
设置数据层次
level ...
connect by [nocycle] prior 连接条件
[start with 开始条件]
- level:可以根据数据所处的层次结构实现自动的层次编号
- connect by;指的是数据之间的连接,其中nocycle需要结合connect_by_iscycle伪列确定出负责节点循环关系
- start with:根节点数据开始的条件
- connect_by_isleaf伪列
- 在树状结构中分为两种,根节点和叶子节点,用户可以利用伪列判断某一结点是根还是叶子。返回数字0则为根,叶子节点为1
- connect_by_root列语句
- 主要作用是取得某一字段在本次分层中的根节点数据名称
- sys_connect_by_path(列,char)函数
- 该函数按照给定的节点关系,自动将当前根节点中的所有相关路
- order sibings by 字段语句
- 直接使用order by子句进行指定字段的排序,有可能会破坏数据的组成结构。用sibings则可以保留层次关系
- connect_by_iscycle伪列
- 可以判断循环关系的出现,出现循环显示为1,没有循环关系返回0.如果判断是否为循环节点,还需要nocycle的支持。
-
关键字
- using子句:可以通过using子句完成笛卡尔积的消除
- 用来指定进行等值连接的同名字段, 针对自然连接提供的
- 如果某列在using子句中使用,那么在引用该列时不要使用表名或是别名
- natural join和using子句是相互排斥的
- 当有多个列匹配的时候,用using子句匹配唯一的列
- 同名字段依然不能使用表前缀.
- on子句(实现θ连接,θ表示任意条件)
- group by
- 在select子句中运用统计函数,如果没有group by子句,则显示字段只能是统计函数
- 在统计查询中,select子句只允许出现分组字段和统计字段,而其他的非分组字段不能使用
- 统计函数允许嵌套调用,但是嵌套统计函数之后的select子句不允许再出现任何字段,包括分组字段
- having子句(通过条件过滤数据)
- in,not in操作符:用于指定一个查询范围,如果在指定的查询范围中存在一个null,则不会有任何的查询结果返回
- any操作符:
- “=any”:表示与子查询中返回的每个元素进行比较,功能与in类似(然而<>any不能等价于not in)
- 使用<>any返回的是全部记录
- “>any":比子查询中返回的最小结果大(>=any)
- “<any":比子查询中返回的最大结果小(<=any)
- “=any”:表示与子查询中返回的每个元素进行比较,功能与in类似(然而<>any不能等价于not in)
- some操作符:于any对比学习
- all操作符:表示匹配子查询中的数据数据
- <>all:等价于not in(但是=all并不等价于in)
- 使用=all没有记录返回
- ">all"比子查询中最大的值还要大(>=all)
- "<all"比子查询中最da的值还要xiao(<=all)
- <>all:等价于not in(但是=all并不等价于in)
- exists:用于判断子查询是否有数据返回,有数据返回为true,没有则为false
- having子句在子查询中:主要是针对分组后的数据进行过滤,如果在子查询中,返回的结果往往返回的是单行单列(一个空格)的数据
- from子句在子查询中:
- 多字段分组的数据量:如emp表1400条数据,dept数据量为400条,则笛卡尔积的数据量是1400*400=560000
- 子查询的数据量:dept表中400*子查询中的部分数据(如300)+ 统计雇员所使用的1400记录 = 121400
- 通常情况使用子查询的效率比多表查询的性能高
- select子句可以在子查询中
- with子句:可以提供一种定义临时表的操作方法(如果在一个查询中反复使用到的一些数据,那么就可以将这些数据定义在with中)
- range子句:可设置一个查询范围的偏移量,排序列的数值必须大于等于(或小于等于)”当前行列值+/-偏移量“
- row子句:可以设置一个当前行的起始物理偏移行,并依据此偏移量进行统计操作。
集合运算
- 笛卡尔积(简单笛卡尔积会影响效率,不建议常用。可用外键消除一些暴力笛卡尔积问题)
- 业务经验:消除笛卡尔积的条件个数 = 表的个数-1(参考经验,具体问题具体分析)
- union(并集):显示查询结果的全部内容(重复元素不显示)
- union all(并集):显示查询结果的全部内容(重复元素显示)
- 可以使用union,union all代替or:or操作表示多个条件有一个条件满足即可。从性能上谈,使用union,union all要比直接使用or的性能快很多
- 例句:
select * from tableA where A="mark" union select * from tableA where A="json"
- 例句:
- 可以使用union,union all代替or:or操作表示多个条件有一个条件满足即可。从性能上谈,使用union,union all要比直接使用or的性能快很多
- minus(差集):返回查询结果的不同部分
- intersect(交集):返回查询内容的相同的部分
语句书写顺序
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pqwj4SsD-1627555147172)(C:\Users\Sunlight-9Y9\AppData\Roaming\Typora\typora-user-images\image-20210716091204366.png)]
连接
-
-
自然连接(natural join):一种特殊的等价连接,它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。
-
公式:
关 系 E ♾ 关 系 D 关系E♾关系D 关系E♾关系D -
悬浮元组:自然连接就是将所有的属性匹配后的元组数据进行显示,如果一个元组不能和另一个关系中的任何一个元组配对,则这个元组被称为悬浮元组
-
-
内连接:也称等值连接(不满足连接条件会缺失信息)
-
外连接:(显示连接后的全部信息)
-
在Oracle中利用其提供的“(+)”进行左外连接和右外连接
- 左关系属性 = 右关系属性(+):把符号放在等号右边表示左连接
- 左关系属性(+) = 右关系属性 :把符号放在等号左边表示右连接
-
语句书写和实例参考
-
学生表
-
成绩表
-
左外连接
- 语句:select *from Student LEFT JOIN Score ON Student.s_id=Score.s_id
- 效果
-
右外连接
- 语句:select *from Student right JOIN Score ON Student.s_id=Score.s_id
- 效果:
-
全外连接(FULL JOIN 或 FULL OUTER JOIN)
-
语句:select *from Student full JOIN Score ON Student.s_id=Score.s_id
select *from Student full outer JOIN Score ON Student.s_id=Score.s_id
-
效果:
-
-
-
-
区分左表,右表
- from子句:from tableA,tableB,其中tableA为左表,B为右表
- where子句:where A.字段=B.字段(+),其中A为左表
sql语句编写思路
- 确定需要显示的字段
- from子句获取数据源
- 设置消除笛卡尔积的条件
- where子句和having子句作用不同(效果虽然都可以过滤数据)
- where子句,在分组之前使用,对所有数据中筛选数据以便完成分组的要求,在where子句中不允许使用统计函数,没有group by函数依然可以使用
- having子句,在分组之后使用,表示对分组统计后的数据再次过滤,可以使用统计函数,在group by 子句之后可以出现having子句
语句执行顺序
- 引入group by后
- from子句,确定数据源
- where子句,限定符对数据进行过滤
- group by 子句,根据指定字段分组
- select子句,查询检索字段以及编写的相应的统计函数
- order by子句,排序
- 引入having子句
- from子句,确定数据源
- where子句,限定符对数据进行过滤
- group by 子句,根据指定字段分组
- having子句:分组后的统计数据进行过滤
- select子句,查询检索字段以及编写的相应的统计函数
- order by子句,排序
by 子句之后可以出现having子句
语句执行顺序
- 引入group by后
- from子句,确定数据源
- where子句,限定符对数据进行过滤
- group by 子句,根据指定字段分组
- select子句,查询检索字段以及编写的相应的统计函数
- order by子句,排序
- 引入having子句
- from子句,确定数据源
- where子句,限定符对数据进行过滤
- group by 子句,根据指定字段分组
- having子句:分组后的统计数据进行过滤
- select子句,查询检索字段以及编写的相应的统计函数
- order by子句,排序