澳门金沙vip 52

【澳门金沙vip】Oracle-SQL系列知识点(二)

在SQL语句中,子查询需要放在圆括号中,在执行子查询时,其执行的过程是先查询出来子查询的结果,然后将子查询返回的结果作为其外层查询的查询条件。子查询根据返回的结果,可以分为单行子查询,多行子查询和多列子查询;根据返回的数据与外层查询之间的关系,可以分为相关子查询和不相关子查询。应该说,子查询在实际开发过程中有着非常广泛的应用。

Oracle-SQL

前面的话

  查询数据库,当查询条件比较复杂时,常常需要用到子查询。子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。本文将详细介绍子查询

 

单行子查询

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

定义

  子查询(Subquery)是指出现在其他SQL语句内的SELECT子句

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

  其中,SELECT * FROM t1,称为外层查询(Outer Query/Outer
Statement),SELECT col2 FROM t2,称为子查询(SubQuery)

  在使用子查询时,需要注意的是

  1、子查询指嵌套在查询内部,且必须始终出现在圆括号内

  2、子查询语句中可以包含多个关键字或条件,如DISTINCT、GROUP BY、ORDER
BY、LIMIT、函数等

  3、子查询的外层查询可以是:SELECT、INSERT、UPDATE、SET或DO

  4、子查询返回值可以是:标量、一行、一列或者子查询

 

在实际应用中,如果开发人员或者是用户明确知道其SQL语句中使用的子查询返回的结果是一行数据时,即时,就可以使用算术比较运算符进行子查询的操作。其中,比较运算符包括=(等于)、>=(大于等于)、<=(小于等于)、>(大于)、<(小于)、!=(不等于)、
<>(不等于)、!>(不大于)、!<(不小于)。

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

比较运算符

  使用比较符是其中一类子查询

operand comparison_operator subquery

  比较运算符包括=、!=、<>、<=> 、>、<、>=、<=

数据准备

  下载数据文件,建立数据库,数据表,并存入相应记录

澳门金沙vip 1

澳门金沙vip 2

  求所有电脑产品的平均价格,并且保留两位小数,AVG、MAX、MIN、COUNT、SUM为聚合函数

  [注意]AVG()是一个用来求平均值的函数

澳门金沙vip 3

  查询所有价格大于平均价格的商品

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5391.30;

澳门金沙vip 4

  通过子查询来实现相同的需求

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

澳门金沙vip 5

  查询类型为“超记本”的商品价格

澳门金沙vip 6

澳门金沙vip 7

  查询价格大于或等于”超级本”价格的商品

澳门金沙vip 8

  系统提示错误,子查询返回的多于一行,因为子查询有3条结果,SELECT无法知道要大于子查询中3条结果中的哪一个。所以,这时就需要用到接下来要介绍的修饰关键字

 

多行子查询


修饰关键字

  修饰关键字包括ANY、SOME、ALL三个,如果子查询返回多个值时,可以使用它们

operand comparison_operator  ANY(子查询)
operand comparison_operator  SOME(子查询)
operand comparison_operator  ALL(子查询)

澳门金沙vip 9

情况处理

  1、运算符为>或>=,使用ANY关键字时,表示大于子查询结果中的最小值

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >  ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

  由结果可知,返回的都大于4299的值,即最小值

澳门金沙vip 10

澳门金沙vip 11

  2、运算符为>或>=,使用ALL关键字时,表示大于子查询结果中的最大值

澳门金沙vip 12

  3、运算符为<或<=,使用ANY或SOME关键字时,表示小于子查询结果中的最大值;使用ALL关键字时,表示小于子查询结果中的最小值

澳门金沙vip 13

澳门金沙vip 14

  4、运算符为=,使用ANY或SOME关键字时,表示等于子查询结果中的任意值;使用ALL关键字时,则返回空

澳门金沙vip 15

 

所谓多行子查询是指子查询中返回的结果集中含有多行数据。当子查询返回的是多行数据时,需要使用多行运算符。多行运算符包括等运算符。
使用IN运算符,可以将满足列表中满足指定表达式的任何一个值都查询出来。在子查询中使用IN
运算符,则与子查询中查询出来的结果集中的任何一个值匹配的结果都会被查询出来。
ANY运算符也用于多行子查询中。ANY运算符的含义是只要与子查询中的任何一个结果值匹配,其值都会被返回。ANY运算符在使用时需要和比较运算符(=(等于)、>=(大于等于)、<=(小于等于)、>(大于)、<(小于)、!=(不等于)、
<>(不等于))放在一起使用。SOME是ANY的一个同义词。
多列子查询

多表连接

  • 1、什么是连接

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

澳门金沙vip 16

1477117179356.png

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

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

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

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

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

  • 3、 限制歧义列名

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

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

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

4、用等值连接检索数据

澳门金沙vip 17

1477120390478.png

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

澳门金沙vip 18

1477120416865.png

6、外部连接

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

外部连接的符号是 (+),语法包括:

澳门金沙vip 19

1477120464641.png

澳门金沙vip 20

1477120472009.png

(+)所在的一边数据缺失;

  • 7、自连接

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

澳门金沙vip 21

1477120575678.png

  • 8、SQL:1999语法的连接

  • 交叉连接

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

使用CROSS JOIN 子句完成。

澳门金沙vip 22

1477120745841.png

  • 自然连接

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

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

使用NATURAL JOIN子句来完成;

澳门金沙vip 23

1477120758631.png

  • USING子句

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

澳门金沙vip 24

1477120815300.png

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

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

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

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

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

  • ON子句

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

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

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

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

澳门金沙vip 25

1477120972480.png

澳门金沙vip 26

1477120988643.png

  • 左外连接

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

澳门金沙vip 27

1477121046649.png

  • 右外连接

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

澳门金沙vip 28

1477121086219.png

  • 全外连接

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

澳门金沙vip 29

1477121109510.png

[NOT]IN

operand comparison_operator [NOT] IN (subquery)

  第二种子查询是由IN 或 NOT
IN引发的子查询,与比较运算符使用的方法基本相同 

其中,= ANY 运算符与 IN 等效,!= ALL或 <> ALL运算符与 NOT IN 等效

澳门金沙vip 30

[NOT] EXISTS

  第三种子查询是由EXISTS 或 NOT
EXISTS引发的子查询。如果子查询返回任何行,EXISTS将返回TRUE;否则返回FALSE

 

所谓多列子查询,是指子查询的语句会返回多个数据列的子查询语句。在WHERE子句中也可以使用将多个属性值用括号括起来的方式实现多列子查询。在多列子查询中,WHERE子句中需要使用括号将多个属性括在一起,多个属性之间需要用逗号分开。
外层查询的WHERE子句中根据多列子查询返回的行数不同,可以选择使用不同的运算符。如果多列子查询中返回的数据行是单行的,即返回的结果值只有一个,则可以使用算术比较运算符;如果子查询中返回的数据行是多行的,即返回的结果值不只一个,则可以使用IN、ANY、ALL运算符。

分组函数

  • 为什么使用分组函数

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

单行函数和分组函数区别

澳门金沙vip 31

1477121627533.png

  • 分组函数

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

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

澳门金沙vip 32

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子句创建数据组

澳门金沙vip 33

1477121955949.png

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

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

  • 用 HAVING Clause子句排除组结果

使用 HAVING 子句限制组

  • 记录已经分组.

  • 使用过组函数.

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

  • SELECT语句执行过程

澳门金沙vip 34

1477122124139.png

澳门金沙vip 35

1477122182122.png

——>>>

澳门金沙vip 36

1477122207610.png

——>>>

澳门金沙vip 37

1477122218458.png

——>>>

澳门金沙vip 38

1477122230438.png

——>>>

澳门金沙vip 39

1477122240872.png

——>>>

澳门金沙vip 40

1477122249240.png

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

存储查询结果

  我们可以把查询结果统一存储到一个新的数据表中,而不需要一条一条地录入

  下面,先创建一个“商品分类”空表

澳门金沙vip 41

   然后,查询tdb_goods表的所有记录,并且按”类别”分组

澳门金沙vip 42

  将分组结果写入到“商品分类”数据表中

INSERT [INTO] tbl_name [(col_name),...)] SELECT...;

澳门金沙vip 43

澳门金沙vip 44

澳门金沙vip 45

 (dept,profession)  teaID  )

子查询

  • 使用子查询

澳门金沙vip 46

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,以此类推

  • 分页查询

澳门金沙vip 47

1477122929348.png

相关子查询

集合运算

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

澳门金沙vip 48

1477123042522.png

  • 联合(UNION)运算

澳门金沙vip 49

1477123142755.png

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

使用原则

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

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

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

  • 完全联合 (UNION ALL) 运算

澳门金沙vip 50

1477123214134.png

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

使用原则

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

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

  • 相交运算

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

使用原则

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

  • 相减运算

澳门金沙vip 51

1477123354054.png

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

使用原则

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

  • 相关子查询

澳门金沙vip 52

1477123533067.png

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

  • EXISTS和NOT EXISTS操作符

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

EXISTS判断是否“存在”,具体操作如下:

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

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

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

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