再次啃书，sql基础语法，基本概念

《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的组合排序有

  1. 函数名称（[参数，…]）over（partition by子句，order by子句，windowing子句）
  2. 函数名称（[参数，…]）over（partition by子句，order by子句）
  3. 函数名称（[参数，…]）over（partition by子句）
  4. 函数名称（[参数，…]）over（order by子句，windowing子句）
  5. 函数名称（[参数，…]）over（order by子句）
  6. 函数名称（[参数，…]）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))的值，他给出相对于总数的百分比

  • 行列转换

    • 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）
• some操作符：于any对比学习
• all操作符：表示匹配子查询中的数据数据
  • <>all:等价于not in（但是=all并不等价于in）
    • 使用=all没有记录返回
  • ">all"比子查询中最大的值还要大（>=all）
  • "<all"比子查询中最da的值还要xiao（<=all）
• 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"
• minus（差集）：返回查询结果的不同部分
• intersect(交集)：返回查询内容的相同的部分

语句书写顺序

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pqwj4SsD-1627555147172)(C:\Users\Sunlight-9Y9\AppData\Roaming\Typora\typora-user-images\image-20210716091204366.png)]

连接

• 

• 自然连接（natural join）:一种特殊的等价连接,它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。

  • 公式：
    $$关系E♾关系D$$

  • 悬浮元组：自然连接就是将所有的属性匹配后的元组数据进行显示，如果一个元组不能和另一个关系中的任何一个元组配对，则这个元组被称为悬浮元组

• 内连接：也称等值连接（不满足连接条件会缺失信息）

  • inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

• 外连接：（显示连接后的全部信息）

  • 左连接

    • left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

  • 右链接

    • right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

  • 全连接

    • full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

• 在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语句编写思路

1. 确定需要显示的字段
2. from子句获取数据源
3. 设置消除笛卡尔积的条件

• where子句和having子句作用不同（效果虽然都可以过滤数据）
  • where子句，在分组之前使用，对所有数据中筛选数据以便完成分组的要求，在where子句中不允许使用统计函数，没有group by函数依然可以使用
  • having子句，在分组之后使用，表示对分组统计后的数据再次过滤，可以使用统计函数，在group by 子句之后可以出现having子句

语句执行顺序

• 引入group by后
  1. from子句，确定数据源
  2. where子句，限定符对数据进行过滤
  3. group by 子句，根据指定字段分组
  4. select子句，查询检索字段以及编写的相应的统计函数
  5. order by子句，排序
• 引入having子句
  1. from子句，确定数据源
  2. where子句，限定符对数据进行过滤
  3. group by 子句，根据指定字段分组
  4. having子句：分组后的统计数据进行过滤
  5. select子句，查询检索字段以及编写的相应的统计函数
  6. order by子句，排序

by 子句之后可以出现having子句

语句执行顺序

• 引入group by后
  1. from子句，确定数据源
  2. where子句，限定符对数据进行过滤
  3. group by 子句，根据指定字段分组
  4. select子句，查询检索字段以及编写的相应的统计函数
  5. order by子句，排序
• 引入having子句
  1. from子句，确定数据源
  2. where子句，限定符对数据进行过滤
  3. group by 子句，根据指定字段分组
  4. having子句：分组后的统计数据进行过滤
  5. select子句，查询检索字段以及编写的相应的统计函数
  6. order by子句，排序