Oracle-SQL系列知识点(二)

Oracle-SQL

这是对Oracle-SQL知识点详细介绍的文章系列，其他文章如下：

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(三)
• PL/SQL基础知识详解

---

多表连接

• 1、什么是连接

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据

1477117179356.png

在 WHERE子句中书写连接条件。
如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
N个表相连时，至少需要N－1个连接条件

• 2、笛卡尔积
  第一个表中的所有行和第二个表中的所有行都发生连接。

笛卡尔积在下列情况产生：

• 连接条件被省略
• 连接条件是无效的

为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件；

• 3、 限制歧义列名

在用到多个表时可以使用表名作前缀来限定列；

通过使用表前缀可以提高性能；

通过使用列的别名可以区分来自不同表但是名字相同的列；

4、用等值连接检索数据

1477120390478.png

5、非等值连接的数据检索

1477120416865.png

6、外部连接

在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。

外部连接的符号是 (+)，语法包括：

1477120464641.png 1477120472009.png

（+）所在的一边数据缺失；

• 7、自连接

自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

1477120575678.png

• 8、SQL:1999语法的连接

• 交叉连接

交叉连接会产生连个表的交叉乘积，和两个表之间的笛卡尔积是一样的；

使用CROSS JOIN 子句完成。

1477120745841.png

• 自然连接

自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；

如果两个表之间相同名称的列的数据类型不同，则会产生错误；

使用NATURAL JOIN子句来完成；

1477120758631.png

• USING子句

自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件

1477120815300.png

使用USING子句创建连接时，应注意以下几点：

• 如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。

• 如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。

• USING子句中的用到的列不能使用表名和别名作为前缀。

• NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用

• ON子句

自然连接条件基本上是具有相同列名的表之间的等值连接；

如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；

用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；

ON子句可以提高代码的可读性。

1477120972480.png 1477120988643.png

• 左外连接

左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来

1477121046649.png

• 右外连接

右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来。

1477121086219.png

• 全外连接

全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录

1477121109510.png

分组函数

• 为什么使用分组函数

分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件。

单行函数和分组函数区别

1477121627533.png

• 分组函数

分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。

分组函数常用到以下五个函数：
MIN
MAX
SUM
AVG
COUNT

1477121709556.png

• MIN函数和MAX函数

MIN和MAX函数主要是返回每组的最小值和最大值。

• MIN([DISTINCT|ALL] column|expression)
• MAX([DISTINCT|ALL] column|expression)

MIN和MAX可以用于任何数据类型

• SUM函数和AVG函数

SUM和AVG函数分别返回每组的总和及平均值。

• SUM([DISTINCT|ALL] column|expression)
• AVG([DISTINCT|ALL] column|expression)

SUM和AVG函数都是只能够对数值类型的列或表达式操作。

• COUNT函数

COUNT函数的主要功能是返回满足条件的每组记录条数。

• COUNT( *|{[DISTINCT|ALL] column|expression})

**DISTINCT会消除重复记录后再使用组函数 **

• 分组函数中空值处理

除了COUNT（）*之外，其它所有分组函数都会忽略列中的空值，然后再进行计算；

在分组函数中使用NVL函数，NVL 函数可以使分组函数强制包含含有空值的记录

• 用GROUP BY子句创建数据组

1477121955949.png

通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的列

在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中

• 用 HAVING Clause子句排除组结果

使用 HAVING 子句限制组

• 记录已经分组.

• 使用过组函数.

• 与 HAVING 子句匹配的结果才输出

• SELECT语句执行过程

1477122124139.png 1477122182122.png

------>>>

1477122207610.png

------>>>

1477122218458.png
------>>>
1477122230438.png
------>>>
1477122240872.png

------>>>

1477122249240.png

1.通过FROM子句中找到需要查询的表；
2.通过WHERE子句进行非分组函数筛选判断；
3.通过GROUP BY子句完成分组操作；
4.通过HAVING子句完成组函数筛选判断；
5.通过SELECT子句选择显示的列或表达式及组函数；
6.通过ORDER BY子句进行排序操作。

子查询

• 使用子查询

1477122409227.png

• 括号内的查询叫做子查询，也叫内部查询，先于主查询执行。
• 子查询的结果被主查询（外部查询）使用
• expr operator包括比较运算符。
  • 单行运算符：>、=、>=、<、<>、<=
  • 多行运算符： IN、ANY、ALL

子查询可以嵌于以下SQL子句中：

• WHERE子句

• HAVING子句

• FROM子句

• 子查询使用指导

  • 子查询要用括号括起来
  • 将子查询放在比较运算符的右边
  • 对于单行子查询要使用单行运算符
  • 对于多行子查询要使用多行运算符

• 子查询的类型

单行子查询；
多行子查询；
多列子查询；

• 多行子查询

子查询返回记录的条数 可以是一条或多条。

和多行子查询进行比较时，需要使用多行操作符，多行操作符包括：

• IN
• ANY

ANY的使用

ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。

• < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。

• ‘> ANY’：表示大于子查询结果集中的任意一个，即大于最小值就可以。

• = ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

• ALL

ALL的使用

ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。

• < ALL:表示小于子查询结果集中的所有行，即小于最小值。

• ‘> ALL:表示大于子查询结果集中的所有行，即大于最大值。

• = ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

--

• 多列子查询

之前讲的子查询都是在一个条件表达式内和子查询的一个列进行比较，多列子查询可以在一个条件表达式内同时和子查询的多个列进行比较。

多列子查询通常用IN操作符完成。

• ROWNUM

ROWNUM是一个伪列，伪列是使用上类似于表中的列，而实际并没有存储在表中的特殊列；

ROWNUM的功能是在每次查询时，返回结果集的顺序号，这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推

• 分页查询

1477122929348.png

集合运算

集合运算：是用来把两个或多个查询的结果集做并、交、差的集合运算，包含集合运算的查询称为复合查询

1477123042522.png

• 联合(UNION)运算

1477123142755.png

返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列升序排序。

使用原则：

1、多个被联合的查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。

2、如果多个查询结果都有NULL值，整个结果中只包含一个NULL值。

3、每个查询不能包含自己的Order by子句，只能在联合之后使用Order by子句。

• 完全联合 (UNION ALL) 运算

1477123214134.png

返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排序 。

使用原则：

1、多个被联合的查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
2、如果多个查询结果都有NULL值，不被去掉。
3、每个查询不能包含自己的Order by子句，只能在联合之后使用Order by子句。

注意：使用UNION ALL会比UNION的速度快，因为省去了去掉重复记录和排序的时间。

• 相交运算

返回多个查询结果集的公有行。

使用原则

1、多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
2、相交运算不忽略空值。

• 相减运算

1477123354054.png

返回第一个查询中存在 而第二个查询中不存在的行记录。

使用原则

1、多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
2、相减运算不忽略空值。

• 相关子查询

1477123533067.png

父查询中的行每被处理一次，子查询就执行一次

• EXISTS和NOT EXISTS操作符

相关子查询还可使用EXISTS和 NOT EXISTS操作符来进行操作

EXISTS判断是否“存在”，具体操作如下：

子查询中如果有记录找到，子查询语句不会继续执行，返回值为TRUE；

子查询中如果到表的末尾也没有记录找到，返回值为FALSE。

EXISTS 子查询并没有确切记录返回，只判断是否有记录存在，而且只要找到相关记录，子查询就不需要再执行，然后再进行下面的操作。这样大大提高了语句的执行效率。

NOT EXISTS正好相反，判断子查询是否没有返回值。如果没有返回值，表达式为真，如果找到一条返回值，则为假。

数据操作

Data Manipulation Language ,简称DML，主要用来实现对数据库表中的数据进行操作。

数据操作语言主要包括如下几种：

增加行数据：使用INSERT语句实现
修改行数据：使用UPDATE语句实现
删除行数据：使用DELETE语句实现
合并行数据：按照指定条件合并两个表的数据，使用MERGE语句实现

• 插入数据

插入单行数据

使用INSERT语句向表中插入数据，语法结构如下

1477124378123.png

采用这种语法一次只能追加一条记录；
列名列表和值列表必须在个数及数据类型上保持一致；
列名列表部分可以省略，如果省略，默认包括该表的所有列；
注意值列表必须和列名列表对应；
字符和日期型数据必须要用单引号括起来。

插入多行数据

1477124486871.png

不必书写values子句；
INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量；

• 修改数据

修改数据使用UPDATE子句完成，语法结构如下

1477124562024.png

WHERE子句用来限定修改哪些行。
SET子句用来限定修改哪些列

相关UPDATE

用一个相关子查询来更新在一个表中的行，该表中的行基于另一个表中的行

1477124653362.png

• 删除数据

使用 DELETE 语句删除表中满足条件的行记录

1477124706136.png

相关DELETE

1477124764861.png

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(三)
• PL/SQL基础知识详解