高级SELECT语句

最新推荐文章于 2023-07-31 20:00:33 发布

jian704

最新推荐文章于 2023-07-31 20:00:33 发布

阅读量3.9k

点赞数 1

文章标签： informix date each 数据库数据库服务器 user

本文链接：https://blog.csdn.net/jian704/article/details/1785681

版权

高级SELECT语句

本章概览

使用GROUP BY和HAVING子句

使用GROUP BY子句

使用HAVING子句

创建高级连接

自连接

外连接

简单连接

两个表上的简单外连接

一个简单连接对第三个表的外连接

一个外连接对第三个表的外连接

两个表对第三个表的外连接

SELECT语句中的子查询

使用ALL

使用ANY

单值子查询

本章概览

上一章介绍了用SELECT语句从关系数据库中检索数据的一些基本方法。本章将进一步增加使用强大的SQL语句的工作范围，使你能执行更复杂的数据库查询和数据操作。

上一章集中在SELECT语句的五个子句的语法上，本章将增加两个子句。你可以用GROUP BY子句及聚类函数来组织FROM子句所返回的行。你可以包括一个HAVING子句来放置关于GROUP BY子句所返回的值的条件。

本章对连接进行深入的讨论。本章将介绍自连接，它使得你能把一个表和自身连接。本章还介绍四种外连接，你可以用关键字OUTER来不对等地处理两个或多个连接的表。本章还介绍相关和非相关的子查询以及它们的运算关键字，说明如何用UNION运算符组合查询，定义并集、交集和差集等集合运算。

本章的例子说明了在查询中如何使用SELECT语句的部分或全部子句。这些子句必须以下面的次序出现：

1. SELECT子句

2. FROM子句

3. WHERE子句

4. GROUP BY子句

5. HAVING子句

6. ORDER BY子句

7. INTO TEMP子句

此外，还有一个SELECT语句的子句，INTO子句，用于在INFORMIX-4GL和嵌入语言产品中指定程序和宿主变量。这将在本手册的第6章和有关的产品手册中讨论。

使用GROUP BY和HAVING子句

可选的GROUP BY和HAVING子句增强了SELECT语句的功能。你可以在基本SELECT语句中加入这些子句或其中之一，以增强处理聚类的能力。

在选项列表中指定的列上具有相同值的行成为一组，GROUP BY子句把这些行组合起来，产生一行结果行。HAVING子句设置关于形成后的行的条件。你可以使用GROUP BY子句而不使用HAVING子句，反之亦然。

使用GROUP BY子句

GROUP BY子句把一个表分为若干集合。常把这个子句和聚类函数一起使用，以产生这些集合的汇总值。第2章中的一些例子说明了对整个表使用聚类函数的用法。本章介绍对一组的行使用聚类函数。

使用GROUP BY子句而不用聚类函数的结果与在SELECT子句中使用DISTINCT(或UNIQUE)关键字非常相似。在第2章中有以下例子：

SELECT DISTINCT customer_num FROM orders

你还可以这样来写这个查询：

SELECT customer_num

FROM orders

GROUP BY customer_num

两个语句都返回这些行：

customer_num

101

104

106

110

111

112

115

116

117

119

120

121

122

123

GROUP BY子句搜集行，放入集合中，使得每个集合中的所有行的客户代码相等。没有选定其它列时，结果就是唯一的customer_num的值的列表。

当GROUP BY子句与聚类函数一起使用时，它的作用就很明显了。

SELECT order_num, COUNT(*) number, SUM(total_price)

price

FROM items

GROUP BY order_num

这个SELECT语句检索每张订单中项目的数量和总价。GROUP BY子句使得items表中的行分成若干组，同一个组的行具有相同的order_num值。(也就是说，每个订单的项目被编组在一起。)编组后，聚类函数COUNT和SUM在每个组内起作用。

这个查询对每组返回一行。它用标签来对COUNT和SUM表达式的结果命名，显示如下：

order_num number price

1001 1 $250.00

1002 2 $1200.00

1003 3 $959.00

1004 4 $1416.00

1005 4 $562.00

1006 5 $448.00

1007 5 $1696.00

1008 2 $940.00

1015 1 $450.00

1016 4 $654.00

1017 3 $584.00

1018 5 $1131.00

1019 1 $1499.97

1020 2 $438.00

1021 4 $1614.00

1022 3 $232.00

1023 6 $824.00

这个SELECT语句把items表的行分组，订单号码相同的为一组，然后计算每组中的行的COUNT值和价格总和。

注意，不能在GROUP BY子句中包括TEXT或BYTE数据类型的列。要分组，首先要能排序，对TEXT或BYTE数据而言，没有自然的次序关系。

与ORDER BY子句不同，GROUP BY子句不对数据进行排序。如果你要以特定次序排序数据，或者要按选项列表中的聚类项排序，则必须在GROUP BY子句后面包括一个ORDER BY子句。

SELECT order_num, COUNT(*) number, SUM(total_price)

price

FROM items

GROUP BY order_num

ORDER BY price

这个查询与上一个相同，但包括了一个ORDER BY子句，以price的升序排列检索而得的各行：

order_num number price

1010 2 $84.00

1011 1 $99.00

1013 4 $143.80

1022 3 $232.00

1001 1 $250.00

1020 2 $438.00

1006 5 $448.00

1015 1 $450.00

1009 1 $450.00

1018 5 $1131.00

1002 2 $1200.00

1004 4 $1416.00

1014 2 $1440.00

1019 1 $1499.97

1021 4 $1614.00

1007 5 $1696.00

上一章曾经讲过，可以在ORDER BY子句中使用一个整数来指定在选项表中列的位置。还可以在GROUP BY子句中用一个整数来指定在组列表中列的名字或显示标签的位置。

这个SELECT语句与上面一个查询返回相同的结果：

SELECT order_num, COUNT(*) number, SUM(total_price)

price

FROM items

GROUP BY 1

ORDER BY 3

当你写一个SELECT语句时，切记在SELECT子句选项列表中的非聚类的列都必须包括在GROUP BY子句的组列表中。这是因为带GROUP BY的SELECT对每一个组只返回一行。列在GROUP BY后的列在组内唯一，这个值可以被返回。而没列在GROUP BY后的列可能在组内各行的值不一样。

可以在连接表的SELECT语句中使用GROUP BY子句。

SELECT o.order_num, SUM(i.total_price)

FROM orders o, items I

WHERE o.order_date > “01/01/90”

AND o.customer_num = 110

AND o.order_num = i.order_num

GROUP BY o.order_num

这个查询连接orders和items表，为表指定别名，返回以下两行：

order_num (sum)

1008 $940.00

1015 $450.00

使用HAVING子句

HAVING子句通常作为GROUP BY子句的补充，提供分组后关于组的一个或多个限定条件，这与WHERE子句对表中的行进行限定类似。使用HAVING子句的一个优点是你可以在搜索条件中包括聚类，而在WHERE子句的搜索条件中不能包括聚类。

每个HAVING条件把一个列或者关于一个组的聚类表达式和另一个聚类表达式或常数作比较。可以用HAVING来设置关于组列表中的列的值和聚类值的条件。

SELECT ordre_num, COUNT(*) number, AVG(total_price)

average

FROM items

GROUP BY order_num

HAVING COUNT(*) > 2

这个SELECT语句对所有包含多于两个项目的订单返回该订单的平均总价。HAVING子句在分组时测试每个组，选取那些包括多于两行的组。

order_num number average

1003 3 $319.67

1004 4 $354.00

1005 4 $140.50

1006 5 $89.60

1007 5 $339.20

1013 4 $35.95

1016 4 $163.50

1017 3 $194.67

1018 5 $226.20

1021 4 $403.50

1022 3 $77.33

1023 6 $137.33

如果只使用HAVING子句而不使用GROUP BY子句，HAVING子句的条件作用于所有满足搜索条件的行。换句话说，所有满足搜索条件的行组成一个分组。

SELECT AVG(total_price) average

FROM items

HAVING count(*) > 2

这个语句是上一个例子的修订版，它只返回一行，是表中所有total_price值的平均值：

average

$270.97

如果这个查询象上一个一样，在选项列表中包括了非聚类列order_num，则必须包括GROUP BY子句，子句的组列表中包括那一列。此外，如果HAVING子句的条件不满足，输出中只显示各列的头，然后有一个信息表明找不到任何行。

下面这个例子包括了在Informix交互式SQL中可以使用的SELECT语句的所有七种子句(用于指定程序或宿主变量的INTO子句仅在INFORMIX-4GL或嵌入语言程序中可用)：

SELECT o.order_num, SUM (i.total_price) price,

paid_date - order_date span

FROM orders o, items I

WHERE o. order_date > “01/01/90”

AND o.customer_num > 110

AND o.order_num = i.order_num

GROUP BY 1, 3

HAVING COUNT ( * ) < 5

ORDER BY 3

INTO TEMP temptabl

这个SELECT语句连接orders和items表；指定显示标签、表的别名和用作列指示器的整数；对数据分组并排序；并把以下结果放入一个临时表中：

order_num price span

1017 $584.00

1016 $654.00

1012 $1040.00

1019 $1499.97 26

1005 $562.00 28

1021 $1614.00 30

1022 $232.00 40

1010 $84.00 66

1009 $450.00 68

1020 $438.00 71

创建高级连接

上一章中说明了在SELECT语句中如何包括一个WHERE子句，在一个或多个列上连接两个或多个表。还说明了自然连接和等值连接。

本章讨论两种更复杂的连接：自连接和外连接。如第2章中简单连接所述，可以对表指定别名，对表达式指定显示标签，以缩短多表查询。还可以用ORDER BY子句来排序数据，把查询结果放入临时表中。

自连接

连接并非一定涉及两个不同的表。可以把一个表和它自身连接，创建一个自连接。当要比较同一列的不同的值时，这就很有用。

要创建自连接，应在FROM子句中把一个表写两次，每次各指定一个不同的别名。在SELECT和WHERE子句用别名来引用表，就象着写表是两个不同的表一样。(关于在SELECT语句中的别名在本手册的第2章和《INFORMIX SQL指南：参考手册》第7章中讨论。)

象在表间连接一样，自连接中也能使用算术表达式。可以测试NULL值，可以对指定的列按升序或降序进行ORDER BY操作。

SELECT x.order_num, x.ship_weight, x.ship_date,

y.order_num, y.ship_weight, y.ship_date

FROM orders x, orders y

WHERE x.ship_weight >= 5 * y.ship_weight

AND x.ship_date IS NOT NULL

AND y.ship_date IS NOT NULL

ORDER BY x.ship_date

这个SELECT语句查找这样的订单对，它们的ship_weight相差至少五倍，并且ship_date不是NULL，然后按ship_date排列数据。

order_num ship_weight ship_date order_num ship_weight ship_date

1004 95.80 05/30/1991 1022 15.00 07/30/1991

1004 95.80 05/30/1991 1020 14.00 07/16/1991

1004 95.80 05/30/1991 1011 10.40 07/03/1991

1007 125.90 06/05/1991 1001 20.40 06/01/1991

1007 125.90 06/05/1991 1015 20.60 07/16/1991

1007 125.90 06/05/1991 1011 10.40 07/03/1991

1007 125.90 06/05/1991 1020 14.00 07/16/1991

1007 125.90 06/05/1991 1022 15.00 07/30/1991

1007 125.90 06/05/1991 1009 20.40 06/21/1991

1005 80.80 06/09/1991 1011 10.40 07/03/1991

1005 80.80 06/09/1991 1020 14.00 07/16/1991

1005 80.80 06/09/1991 1022 15.00 07/30/1991

1012 70.80 06/29/1991 1020 14.00 07/16/1991

1012 70.80 06/29/1991 1011 10.40 07/03/1991

1013 60.80 07/10/1991 1011 10.40 07/03/1991

1018 70.50 07/13/1991 1020 14.00 07/16/1991

假定你要把自连接的结果放在一个临时表中。你当然可以在SELECT语句中加入一个INTO TEMP语句。然而，因为你实际上是创建了一个新的表，你得通过指定显示标签来修改至少一部分的列的名字。否则的话，将会得到一个错误信息，指出重复的列名，不能创建临时表。

SELECT x.order_num orders1, x.po_num purch1,

x.ship_date ship1, y.order_num orders2,

y.po_num purch2, y.ship_date ship2

FROM orders x. orders y

WHERE x. ship_weight >= 5 * y.ship_weight

AND x.ship_date IS NOT NULL

AND y.ship_date IS NOT NULL

INTO TEMP shipping

这个SELECT语句与上一个相似，把从orders表中选出的所有列重新命名，然后存放到一个临时表shipping中。如果你对该表执行SELECT *操作，可以看到以下各行：

orders1 purch1 ship1 orders2 purch2 ship2

1004 8006 05/30/1991 1011 B77897 07/03/1991

1007 278693 06/05/1991 1011 B77897 07/03/1991

1007 278693 06/05/1991 1015 MA003 07/16/1991

1007 278693 06/05/1991 1001 B77836 06/01/1991

1007 278693 06/05/1991 1009 4745 06/21/1991

1017 DM3543 07/13/1991 1011 B77897 07/03/1991

1023 KF2961 07/30/1991 1011 B77897 07/03/1991

1013 B77930 07/10/1991 1011 B77897 07/03/1991

1018 S22942 07/13/1991 1011 B77897 07/03/1991

1012 278701 06/29/1991 1011 B77897 07/03/1991

1005 2865 06/09/1991 1011 B77897 07/03/1991

1019 Z55709 07/16/1991 1011 B77897 07/03/1991

可以不止一次地把一个表和自身连接。自连接的最大次数取决于你可以获得的资源。

SELECT s1. manu_code, s2.manu_code, s3.manu_code,

s1.stock_num, s1.description,

FROM stock s1, stock s2, stock s3

WHERE s1.stock_num = s2.stock_num

AND s2.stock_num = s3.stock_num

AND s1.manu_code < s2.manu_code

AND s2.manu_code < s3.manu_code

ORDER BY stock_num

这个自连接建立了一个在stock表中，关于由三个制造商供应的项目的清单。通过WHERE子句的最后两个条件，它消除了检索而得的行中重复的制造商代码。

manu_code manu_code manu_code stock_num description

HRO HSK SMT 1 baseball gloves

ANZ NRG SMT 5 tennis racquet

ANZ HRO HSK 110 helmet

ANZ HRO PRC 110 helmet

ANZ HRO SHM 110 helmet

ANZ HSK PRC 110 helmet

ANZ HSK SHM 110 helmet

ANZ PRC SHM 110 helmet

HRO HSK PRC 110 helmet

HRO HSK SHM 110 helmet

HRO PRC SHM 110 helmet

HSK PRC SHM 110 helmet

ANZ KAR NKL 201 golf shoes

ANZ HRO NKL 205 3 golf balls

KAR NKL PRC 301 running shoes

KAR NKL SHM

如果你要从一个工资表中选取数据，看看哪个雇员的工资高于他的主管。你可以构造以下自连接：

SELECT emp.employee_num, emp.gross_pay, emp.level,

emp.dept_num, mgr.employee_num, mgr.gross_pay,

mgr.level

FROM payroll emp, payroll mgr

WHERE emp.gross_pay > mgr.gross_pay

AND emp.level < mgr.level

ORDER BY 4

下面这个自连接的例子用一个相关子查询来检索并列出订货的项目中价格最高的头10项：

SELECT order_num, total_price

FROM items a

WHERE 10 >

( SELECT COUNT ( * )

FROM items b

WHERE b.total_price < a.total_price ) ORDER BY total_price

它返回以下10行：

order_num total_price

1018 $15.00

1013 $19.80

1003 $20.00

1005 $36.00

1006 $36.00

1013 $36.00

1010 $36.00

1013 $40.00

1022 $40.00

1023 $40.00

你可以创建一个类似的查询来查找并列出公司中级别最高的头10名雇员。

相关及不相关的子查询在本章稍后讨论。

使用Rowid值

可以在自连接中使用隐藏的rowid列来定位表中重复的值。在下面的例子中，条件x.rowid != y.rowid的意思是“x和y是不同的两行。”

SELECT x.rowid, x.customer_num

FROM cust_calls x, cust_calls y

WHERE x.customer_num = y.customer_num

AND x.rowid != y.rowid

这个SELECT语句从cust_calls表中返回两次数据，分别给它们指定表的别名x和y。它搜索customer_num列中的重复值以及它们的rowid，查找下面结果：

rowid customer_num

1283 116

1537 116

上一个SELECT语句中的最后一个条件既可以写成：

AND x.rowid != y.rowid

也可以写成：

AND NOT x.rowid = y.rowid

对重复值定位的另一种方法是用一个相关子查询，如下所示：

SELECT x.customer_num, x.call_dtime

FROM cust_calls x

WHERE 1 <

(SELECT COUNT ( * ) FROM cust_calls y

WHERE x.customer_num = y.customer_num )

这个SELECT语句与上面的查询同样定位了相同的两个重复的customer_num值，返回下面这些行：

customer_num call_dtime

116 1990-11-28 13:34

116 1990-12-21 11:24

可以用先前在自连接中所讲的rowid来定位数据库表中与一行相联系的内部记录号。rowid的顺序号没有什么特别的，其取值取决于在簇(chunk)中的物理数据位置。

本手册的第4章讨论了性能与rowid值的关系。

SELECT rowid, * FROM manufact

这个SELECT语句在SELECT子句中使用rowid和匹配符*来检索manufact表中的每一行及其相应的rowid。

rowid manu_code manu_name lead_time

513 SMT Smith 3

514 ANZ Anza 5

515 NRG Norge 7

516 HSK Husky 5

517 HRO Hero 4

518 SHM Shimara 30

519 KAR Karsten 21

520 NKL Nikolus 8

521 PRC ProCycle 9

在选定一个特定的列时也可以同时使用rowid。

SELECT rowid, manu_code FROM manufact

这个SELECT语句产生下面的结果：

rowid manu_code

514 ANZ

517 HRO

516 HSK

519 KAR

520 NKL

515 NRG

521 PRC

518 SHM

513 SMT

还可以在WHERE子句中使用rowid来根据内部记录号检索行。当表中没有其它唯一的列时，这种方法显得很方便。

SELECT * FROM manufact WHERE rowid = 519

这个SELECT语句返回一行：

manu_code manu_name lead_time

KAR Karsten 21

使用USER函数

为了获得关于表的其它信息，可以结合使用rowid和USER函数，在INFORMIX_ONLINE上，还可以结合使用SITENAME关键字。USER和SITENAME在本手册的第2章中讨论。

SELECT USER username, rowid FROM cust_calls

这个查询为USER表达式的列指定标签username，返回关于cust_calls表的这些信息：

username rowid

informix 1025

informix 1026

informix 1027

informix 1281

informix 1282

informix 1283

informix 1537

当你选择rowid时，可以在WHERE子句中使用USER函数。

SELECT rowid FROM cust_calls WHERE userid = USER

这个查询返回那些有键入这个语句的用户所键入的行。例如，如果用户richc键入了这个语句，输出将是：

rowid

258

259

使用SITENAME函数

在查询中加入DBSERVERNAME关键字就可以查找出当前数据库的宿主位置。

SELECT DBSERVERNAME server, tabid, rowid,

USER username

FROM systables

WHERE tabid >= 105 OR rowid <= 260

ORDER BY rowid

这个SELECT语句查找服务器名字和用户名字，以及rowid和tabid，其中tabid是系统目录表中使用的顺序内部表标识号码。它为DBSERVERNAME和USER表达式指定显示标签，返回从systables系统目录表中找到的以下10行：

server tabid rowid username

manatee 1 257 informix

manatee 2 258 informix

manatee 3 259 informix

manatee 4 260 informix

manatee 105 524 informix

manatee 106 525 informix

manatee 107 526 informix

manatee 108 527 informix

manatee 109 528 informix

manatee 110 529 informix

注意，切勿把一个rowid存放在永久表中，也不要把rowid用作外部键，因为rowid是会变的。例如，如果删除一个表，然后重新装载外部数据，rwoid将不同。

外连接

第2章说明了如何创建和使用一些简单的连接。在简单连接中，平等地对待两个或多个连接表，外连接不对称地对待两个或多个连接表。它使得其中一个表成为主表(也叫做保留表)，其它表叫做从表。

外连接有四种基本类型：

l 两个表上的简单外连接

l 对第三表的简单外连接

l 简单连接对第三表的外连接

l 外连接对第三表的外连接

这四种类型的外连接在本节中讲述。关于外连接的语法、用法和逻辑的完整信息，请参阅《INFORMIX SQL指南：参考手册》第7章。

在简单连接中，结果仅包括了从表中选出的行的组合，它们满足连接条件。不满足连接条件的行被舍去。

在外连接中，结果包括了从满足连接条件的，从表中选出的行的组合。但主表中的行被保留，尽管在从表中没有匹配的行，这些主表的行不舍去。在选定的列被投影前，没有匹配的从表行的那些主表行将连接一行空值。

外连接在顺序地对主表中的行使用连接条件是对从表使用条件。条件在WHERE子句中给出。

一个外连接必须有一个SELECT子句、一个FROM子句和一个WHERE子句。你可以在FROM子句中的从表的名字前直接插入关键字OUTER来把一个简单连接转换成一个外连接。本节下面将要讲到，在你的查询中，可以多次使用关键字OUTER。

在你决定使用外连接前，应考虑一个或多个简单连接能完成任务。当你不需要其它表的补充信息时，简单连接常常能够奏效。

为简便起见，本节使用了表的别名。表的别名在上一章讲述。

简单连接

这是一个简单连接的例子，它把customer和cust_calls表连接起来。这个例子在本手册的第2章中出现过：

SELECT c.customer_num, c.1name, c.company,

c.phone, u.call_dtime, u.call_descr

FROM customer c, cust_calls u

WHERE c.customer_num = u.customer_num

这个查询返回关于客户曾请求过客户服务的行：

customer_num 106

lname Watson

company Watson & Son

phone 415-389-8789

call_dtime 1991-06-12 08:20

call_descr Order was received, but two of the cans of ANZ tennis

balls within the case were empty

customer_num 110

lname Jaeger

company AA Athletics

phone 415-743-3611

call_dtime 1991-07-07 10:24

call_descr Order placed one month ago (6/7) not received.

customer_num 119

lname Shorter

company The Triathletes Club

phone 609-663-6079

call_dtime 1991-07-01 15:00

call_descr Bill does not reflect credit from previous order

customer_num 119

lname Shorter

company The Triathletes Club

phone 609-663-6079

call_dtime 1991-07-01 15:00

call_descr Bill does not reflect credit from previous orde

customer_num 121

lname Wallack

company City Sports

phone 302-366-7511

call_dtime 1991-07-10 14:05

call_descr Customer likes our merchandise. Requests that we stock

more types of infant joggers. Will call back to place

order.

两个表上的简单外连接

下面的例子和上一个例子使用同样的选项列表、表和比较条件，但这次是一个简单的外连接。

SELECT c.customer_num, c.1name, c.company,

c.phone, u.call_dtime, u.call_descr

FROM customer c, OUTER cust_calls u

WHERE c.customer_num = u.customer _num

cust_calls表前面的关键字OUTER表明这个表是从表。外连接使得查询返回关于所有客户的信息，无论他们是否曾经请求过客户服务。主表customer表中的所有行都检索出来，当从表cust_calls表中没有相应的行时就指定为空值。

customer_num 101

1name Pauli

company All Sports Supplies

phone 408-789-8075

call_dtime

call_descr

customer_num 102

1name Sadler

company Sports Spot

phone 415-822-1289

call_dtime

call_descr

customer_num 103

1name Currie

company Phil’s Sports

phone 415-328-4543

call_dtime

call_descr

customer_num 104

1name Higgins

company Play Ball !

phone 415-368-1100

call_dtime

call_descr

一个简单连接对第三个表的外连接

下面的例子说明了一个简单连接对第三表所得的外连接。这是外连接的第二种类型，也叫做分组简单连接：

SELECT c.customer_num, c. 1name, o.order_num,

i.stock_num, i.manu_code, i.quantity

FROM customer c, OUTER (orders o, items i )

WHERE c.customer_num = o.customer_num

AND o.order_num = i.order_num

AND manu_code IN ( “KAR”, “SHM” )

ORDER BY 1name

这个SELECT语句首先执行一个在orders和items表上的简单连接，检索由manu_code为KAR或SHM生产的项目的所有订单的信息。然后，它执行一个外连接，把这些信息和主表customer表的数据组合。可选的ORDER BY子句对数据进行重新组织，形成以下的列表：

dcustomer_num lname order_num stock_num manu_code quantity

114 Albertson

118 Baxter

113 Beatty

103 Currie

115 Grant

123 Hanlon 1020 301 KAR 4

123 Hanlon 1020 204 KAR 2

125 Henry

104 Higgins

110 Jaeger

120 Jewell 1017 202 KAR 1

120 Jewell 1017 301 SHM 2

111 Keyes

112 Lawson

128 Lessor

109 Miller

126 Neelie

122 O'Brian 1019 111 SHM 3

116 Parmelee

101 Pauli

124 Putnum 1021 202 KAR 3

108 Quinn

107 Ream

102 Sadler

127 Satifer 1023 306 SHM 1

127 Satifer 1023 105 SHM 1

127 Satifer 1023 110 SHM 1

一个外连接对第三个表的外连接

这个SELECT语句创建了一个外连接，这个外连接是一个外连接与第三表连接而成。这是外连接的第三种类型，又称分组外连接：

SELECT c.customer_num, 1name, o.order_num,

stock_num, manu_code, quantity

FROM customer c, OUTER (orders o, OUTER items i )

WHERE c.customer_num = o.customer_num

AND o.order_num = i.order_num

AND manu_code IN ( “KAR” “SHM” )

ORDER BY 1name

这个查询首先执行一个在orders和items表上的外连接，检索由manu_code为KAR或SHM生产的项目的所有订单的信息。然后，它执行一个外连接，把这些信息和主表customer表的数据组合。这个查询保护了在上个例子中舍去的部分订单号码，返回没有对应制造商项目的订单的行。一个可选的ORDER BY子句重新组织了数据。

Lcustomer_num lname order_num stock_num manu_code quantity

114 Albertson

118 Baxter

113 Beatty

103 Currie

115 Grant 1010

123 Hanlon 1020 204 KAR 2

123 Hanlon 1020 301 KAR 4

125 Henry

104 Higgins 1011

104 Higgins 1001

104 Higgins 1013

104 Higgins 1003

110 Jaeger 1008

110 Jaeger 1015

120 Jewell 1017 301 SHM 2

120 Jewell 1017 202 KAR 1

111 Keyes 1009

112 Lawson 1006

128 Lessor

109 Miller

126 Neelie 1022

122 O'Brian 1019 111 SHM 3

116 Parmele 1005

101 Pauli 1002

124 Putnum 1021 202 KAR 3

108 Quinn

107 Ream

102 Sadler

127 Satifer 1023 110 SHM 1

127 Satifer 1023 105 SHM 1

127 Satifer 1023 306 SHM 1

119 Shorter 1016 101 SHM 2

117 Sipes 1012

117 Sipes 1007

当你要对一个外连接与第三表进行外连接时，有两种方式说明连接条件。两个从表连接后，如果主表和从表共享一个公共的列，那么主表可以与任意一个从表连接，结果相同。

两个表对第三个表的外连接

这个SELECT语句说明了一个外连接，它是两个表分别和第三表外连接后的结果。在这种外连接的第四种类型中，连接条件只能在主表和从表之间：

SELECT c.customer_num, 1name, o.order_num,

o.order_date, call_dtime

FROM customer c, OUTER orders o, OUTER cust_calls x

WHERE c.customer_num = o.customer_num

AND c.customer_num = x.customer_num

INTO TEMP service

这个查询分别把从表orders和cust_calls和主表customer连接；它不把两个从表连接起来。一个INTO TEMP子句把选取的结果存放在临时表中，供进一步的操作和查询使用。

customer_num lname order_num order_date call_dtime

101 Pauli 1002 05/21/1991

102 Sadler

103 Currie

104 Higgins 1001 05/20/1991

104 Higgins 1003 05/22/1991

104 Higgins 1011 06/18/1991

104 Higgins 1013 06/22/1991

105 Vector

106 Watson 1004 05/22/1991 1991-06-12 08:20

106 Watson 1014 06/25/1991 1991-06-12 08:20

107 Ream

108 Quinn

109 Miller

110 Jaeger 1008 06/07/1991 1991-07-07 10:24

110 Jaeger 1015 06/27/1991 1991-07-07 10:24

111 Keyes 1009 06/14/1991

112 Lawson 1006 05/30/1991

113 Beatty

114 Albertson

115 Grant 1010 06/17/1991

116 Parmele 1005 05/24/1991 1990-11-28 13:34

116 Parmelee 1005 05/24/1991 1990-12-21 11:24

117 Sipes 1007 05/31/1991

117 Sipes 1012 06/18/1991

118 Baxter

119 Shorter 1016 06/29/1991 1991-07-01 15:00

120 Jewell 1017 07/09/1991

121 Wallack 1018 07/10/1991 1991-07-10 14:0

122 O'Brian 1019 07/11/1991

123 Hanlon 1020 07/11/1991

124 Putnum 1021 07/23/1991

125 Henry

126 Neelie 1022 07/24/1991

127 Satifer 1023 07/24/1991 1991-07-31 14:30

128 Lessor

注意，如果前面一个SELECT语句试图创建一个关于两个从表o和x间的连接条件，如下面的例子所示，将会得到一个错误信息，指出不能创建双向外连接：

WHERE o.customer_num = x.customer_num

SELECT语句中的子查询

套在另一个SELECT语句(或INSERT、DELETE、UPDATE语句)的WHERE子句中的SELECT语句称为子查询。每个子查询必须包含一个SELECT子句和一个FROM子句，必须用括号括起来，告诉数据库服务器应优先执行这个操作。

子查询可以是相关的和非相关的。一个子查询(或内部SELECT语句)是相关的，如果该子查询所产生的值取决于由包含这个子查询的外层SELECT语句所产生的某个值。其它的子查询是非相关的。

相关子查询依赖于外层SELECT，因此，外层SELECT每产生一个值，相关子查询就得重复执行一次，这是相关子查询的重要特征。一个非相关子查询只执行一次。

经常可以通过构造一个包含子查询的SELECT语句来替代两个单独的SELECT语句。

SELECT语句中的子查询可以用于

l 比较一个表达式和另一个SELECT语句结果。

l 确定一个表达式是否包括在另一个SELECT语句的结果当中。

l 确定任意行是否被另一个SELECT语句选中。

子查询中的可选的WHERE子句常用于缩小搜索条件。

子查询选取并向第一个或外层的SELECT语句返回值。一个子查询可以不返回值、返回一个值或一个值的集合。

l 如果它不返回值，那么查询不返回行。这样一个子查询等价于一个NULL值。

l 如果它返回一个值，那么子查询返回或是一个聚类表达式，或是选取一行和一列。这样一个子查询等价与一个数字或字符值。

l 如果它返回值的一个列表或一个集合，子查询返回一行或一列。

以下的关键字在SELECT语句的WHERE子句中引入子查询：

l ALL

l ANY

l IN

l EXISTS

可以与ALL和ANY一起使用任意的关系运算符，以把某个值和子查询所产生的每个值(用ALL)或任意一个值(用ANY)做比较。可以用关键字SOME取代ANY。运算符IN等价于=ANY。要创建相反的搜索条件，应使用关键字NOT或不同的关系运算符。

EXISTS运算符测试子查询，看它是否搜索到了值；也就是说，它测试子查询的结果是否为空。

关于创建查询和子查询的完整语法，请参阅《INFORMIX SQL指南：参考手册》第7章。

关于相关的和非相关的子查询与性能的关系，请参阅本手册第4章。

使用ALL

在子查询前使用关键字ALL，用于确定对每个返回值比较都为真。如果子查询不返回值，搜索条件为true。(如果它根本不返回值，条件对所有的零值为真。)

SELECT order_num, stock_num, manu_code, total_price

FROM items

WHERE total_price < ALL

( SELECT total_price FROM items

WHERE order_num = 1023 )

这个SELECT语句列出以下信息，是所有这样的订单，它包含了一个项目，其总价小于订单号码为1023中每个项目的总价：

order_num stock_num manu_code total_price

1003 9 ANZ $20.00

1005 6 SMT $36.00

1006 6 SMT $36.00

1010 6 SMT $36.00

1013 5 ANZ $19.80

1013 6 SMT $36.00

1018 302 KAR $15.00

使用ANY

在子查询前使用关键字ANY(或它的同义词SOME)，用于确定对返回值的比较中是否至少有一个为真。如果子查询不返回值，搜索条件为false。(因为没有值，条件不可能对值当中的任何一个为真。)

SELECT DISTINCT order_num

FROM items

WHERE total_price > ANY

( SELECT total_price

FROM items

WHERE order_num = 1005 )

这个查询搜索所有这样的订单号码，订单中包含了一个项目，其总价大于号码为1005的订单中任何一个项目的总价。查询返回以下行：

order_num

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

101

1015

1016

1017

1018

1019

1020

1021

1022

102

单值子查询

如果你知道子查询将向外层查询返回一个值时，不需要使用关键字ALL或ANY。只返回一个值的子查询可以视同一个函数。因为聚类函数总是返回一个值，所以经常在这类子查询中使用聚类函数：

SELECT order_num FROM items

WHERE stock_num = 9

AND quantity =

( SELECT MAX ( quantity )

FROM items

WHERE stock_num = 9 )

这个SELECT语句在子查询中使用聚类函数MAX，查找包括排球网数量最多的订单的order_num。它返回这一行：

order_num

1012

下面的例子在子查询中使用了聚类函数MIN，选取总价高于最低价格10倍的项目：

SELECT order_num, stock_num, manu_code, total_price

FROM items x

WHERE total_price >

( SELECT 10 * MIN (total_price)

FROM items

WHERE order_num = x.order_num )

这个查询检索这些行：

order_num stock_num manu_code total_price

1003 8 ANZ $840.00

1018 307 PRC $500.00

1018 110 PRC $236.00

1018 304 HRO $280.00

使用EXISTS

关键字EXISTS又称为存在判断符，因为只有当外层的SELECT语句找到至少一行时子查询才为真。

SELECT UNIQUE manu_name, lead_time

FROM manufact

WHERE EXISTS

(SELECT * FROM stock

WHERE description MATCHES “ *shoe* ”

AND manufact. manu_code = stock.manu_code )

通常可以构造一个使用EXISTS的查询，其等价于一个使用IN的查询。可以用=ANY来替代IN。

SELECT UNIQUE manu_name , lead-time

FROM stock, manufact

WHERE manufact.manu_code IN

(SELECT manu_code FROM stock

WHERE description MATCHES “*shoe*”)

AND stock.manu_code = manufact.manu_code

上面两个查询返回两列(这里原文有误)：生产一种鞋的制造商和这种产品从订货到交货的时间。

manu_name lead_time

Anza 5

Hero 4

Karsten 21

Nikolus 8

ProCycle 9

Shimara 30

注意，对包含TEXT或BYTE数据类型的列不能使用谓词IN。

对IN或EXISTS加上关键字NOT就可以创建一个搜索条件，意义与上面的查询刚好相反。还可以用!=ALL来代替NOT IN。

做同样的事有两种不同的方法。其中一种可能能使数据库服务器的工作量更少，这取决于数据库的设计和表的大小。为了发现哪个查询更好些，可以使用SET EXPLAIN命令来获得查询规划的列表。SET EXPLAIN在本手册的第4章和《INFORMIX SQL指南：参考手册》第7章中讨论。

SELECT customer_num, company FROM customer

WHERE customer_num NOT IN

( SELECT customer_num FROM orders

WHERE customer. customer_num

= orders. customer_num )

SELECT customer_num, company FROM customer

WHERE NOT EXISTS

( SELECT * FROM orders

WHERE customer. customer_num

= orders.customer_num )

这两个语句都返回以下11行，查出没有下订单的客户：

customer_num company

102 Sports Spot

103 Phil's Sports

105 Los Altos Sports

107 Athletic Supplies

108 Quinn's Sports

109 Sport Stuff

113 Sportstown

114 Sporting Place

118 Blue Ribbon Sports

125 Total Fitness Sports

128 Phoenix Universit

注意，在这里使用了关键字EXESTS和IN来完成集合运算交集，使用了关键字NOT EXISTS和NOT IN来完成集合运算差集。这些概念在本章稍后讨论。

下面这个SELECT语句通过执行一个在items表上的子查询来找出stock表中所有还没被订货的项目：

SELECT stock. * FROM stock

WHERE NOT EXISTS

( SELECT * FROM items

WHERE stock.stock_num = items.stock_num

AND stock.manu_code = items.manu_code )

它返回这些行：

stock_num manu_code description unit_price unit unit_descr

3 SHM baseball bat $280.00 case 12/case

101 PRC bicycle tires $88.00 box 4/box

102 SHM bicycle brakes $220.00 case 4 sets/case

102 PRC bicycle brakes $480.00 case 4 sets/case

105 PRC bicycle wheels $53.00 pair pair

106 PRC bicycle stem $23.00 each each

107 PRC bicycle saddle $70.00 pair pair

108 SHM crankset $45.00 each each

109 SHM pedal binding $200.00 case 4 pairs/case

110 ANZ helmet $244.00 case 4/case

110 HRO helmet $260.00 case 4/case

112 SHM 12-spd, assmbld $549.00 each each

113 SHM 18-spd, assmbld $685.90 each each

201 KAR golf shoes $90.00 each each

202 NKL metal woods $174.00 case 2 sets/case

203 NKL irons/wedge $670.00 case 2 sets/case

205 NKL 3 golf balls $312.00 case 24/case

205 HRO 3 golf balls $312.00 case 24/case

301 NKL running shoes $97.00 each each

301 HRO running shoes $42.50 each each

301 PRC running shoes $75.00 each each

301 ANZ running shoes $95.00 each each

302 HRO ice pack $4.50 each each

303 KAR socks $36.00 box 24 pairs/box

305 HRO first-aid kit $48.00 case 4/case

306 PRC tandem adapter $160.00 each each

308 PRC twin jogger $280.00 each each

309 SHM ear drops $40.00 case 20/case

310 SHM kick board $80.00 case 10/case

310 ANZ kick board $84.00 case 12/case

311 SHM water gloves $48.00 box 4 pairs/box

312 SHM racer goggles $96.00 box 12/box

312 HRO racer goggles $72.00 box 12/box

313 SHM swim cap $72.00 box 12/box

313 ANZ swim cap $60.00 box 12/box

注意，这里对SELECT语句可以有的子查询的数目没有逻辑限制，但当把语句考虑成字符串时，对其长度有物理限制。然而，这个限制足够大，以至于大于你可能实际编制的语句。

也许你要检查信息是否正确地进入了数据库。找出数据库中错误的一种方法是写一个查询，仅当存在错误时返回输出。一个这种类型的子查询就象一种审计查询：

SELECT * FROM items

WHERE total_price != quantity *

( SELECT unit_price FROM stock

WHERE stock.stock_num = items.stock_num

AND stock.manu_code = items.manu_code )

这个SELECT语句只返回这样的行，其中一个订单上的一个项目的总价不等于货物单价乘以订单数量。假定这里没有折让，这样的行一定是错误地进入了数据库。例如：

item_num order_num stock_num manu_code quantity total_price

1 1004 1 HRO 1 $960.00

2 1006 5 NRG 5 $190.00

集合运算符

标准的集合运算并集、交集和差集使你可以操纵数据库中的信息。这三个功能使你在执行更新、插入或删除操作后能检查数据库的完整性。例如，当你要往一个历史表中传送数据，而后在原来的表中删除数据前要检查历史表中是否包含了正确的数据时，这些功能就有用了。

并集

并集函数使用UNION关键字或运算符来把两个查询组合成一个复合查询。你可以用UNION关键字把两个或多个SELECT语句统一起来，产生一个临时表，包含了原来表中的任意的或所有的行。(注意，在子查询或视图定义中不能使用UNION运算符。)图3-1说明了并集操作。

UNION关键字从两个查询中选取所有的行，删掉重复的行，返回所剩的行。因为查询结果被组合成一个单一的结果，每个查询的选项列表中应包含相同数目的列。而且，从每个表中选取的相应的列必须具有相同的数据类型(CHARACTER类型的列长度必须相同)，这些相应的列必须或者都允许空值，或者都不允许空值。

这个复合的SELECT语句在stock和items表的stock_num和manu_code列上执行了一个并集操作。

SELECT DISTINCT stock_num, manu_code

FROM stock

WHERE unit_price < 25.00

UNION

SELECT stock_num, manu_code

FROM items

WHERE quantity > 3

这个SELECT语句选取这样的项目，它们的单价小于$25.00，或者订货量多于三件，列出这样的项目的stock_num和manu_code。

stock_num manu_code

5 ANZ

5 NRG

5 SMT

9 ANZ

103 PRC

106 PRC

201 NKL

301 KAR

302 HRO

302 KAR

如果要包括一个ORDER BY子句，它必须跟在最后一个SELECT语句之后，并且要使用整数而不是标识符来引用排序的列。排序在集合运算完成后进行。

SELECT DISTINCT stock_num, manu_code

FROM stock

WHERE unit_price < 25.00

UNION

SELECT stock_num, manu_code

FROM items

WHERE quantity > 3

ORDER BY 2

这个复合查询与上一个SELECT语句选取同样的行，但根据制造商代码排列显示：

stock_num manu_code

5 ANZ

9 ANZ

302 HRO

301 KAR

302 KAR

201 NKL

5 NRG

103 PRC

106 PRC

5 SMT

在缺省情况下，UNION关键字排除重复的行。可以加上可选的关键字ALL来保留那些重复的行。

SELECT stock_num, manu_code

FROM stock

WHERE unit_price < 25.00

UNION ALL

SELECT stock_num, manu_code

FROM items

WHERE quantity >3

ORDER BY 2

INTO TEMP stockitem

这个复合SELECT语句使用UNION ALL关键字来联合两个SELECT语句，通过在最后的SELECT语句后接INTO TEMP子句来把结果放在一个临时表中。它与上面的例子返回同样的行，但还包括了重复值。

stock_num manu_code

9 ANZ

5 ANZ

9 ANZ

5 ANZ

9 ANZ

5 ANZ

302 HRO

302 KAR

301 KAR

201 NKL

5 NRG

103 PRC

106 PRC

5 SMT

组合查询选项列表中的对应的列必须具有相同的数据类型，但这些列不一定使用同一个标识符。

SELECT DISTINCT state

FROM customer

WHERE customer_num BETWEEN 120 AND 125

UNION

SELECT DISTINCT code

FROM state

WHERE sname MATCHES “*a”

这个SELECT语句从customer表中选取state列，对应地选取state表中的code列。它返回120到125号客户所在州的简写代码或者sname以A或a结尾的州。

state

在复合查询中，第一个SELECT语句中的列名和显示标签也就是出现在结果中的列名和显示标签。因此，结果中出现了第一个SELECT语句中的列名state，而不是第二个SELECT语句中的列名code。

这个SELECT执行了一个三个表的并集操作。并集的最大数目取决于应用的实际限制和存储限制。

SELECT stock_num, manu_code

FROM stock

WHERE unit_price > 600.00

UNION ALL

SELECT stock_num, manu_code

FROM catalog

WHERE catalog_num = 10025

UNION ALL

SELECT stock_num, manu_code

FROM items

WHERE quantity = 10

ORDER BY 2

这个复合查询选取这样的项目，或者是stock表中的unit_price大于$600，或者是catalog表中的catalog_num为10025，或者是items表中的quantity为10，然后根据manu_code排序：

stock_num manu_code5

5 ANZ

9 ANZ

8 ANZ

4 HSK

1 HSK

203 NKL

5 NRG

106 PRC

113 SHM

关于SELECT语句和UNION运算符的完整语法，请参阅《INFORMIX SQL指南：参考手册》第7章。关于INFORMIX-4GL和INFORMIX-ESQL/C产品的特别信息，以及涉及INTO子句和复合查询的限制，请参阅本手册第6章和第7章。

下面的例子使用了一个组合查询来把数据选取到一个临时表中，然后用一个简单的查询来排序并显示。必须用分号来把组合查询和简单查询分开。

这个组合查询在选项列表中使用了一个字符来在输出中标记并集的各个部分，以便以后区分。这个标记的标签是sortkey。这个简单查询使用那个标记来排序检索到的行。

SELECT “1” sortkey, 1name, fname, company,

city, state, phone

FROM customer x

WHERE state = “CA”

UNION

SELECT “2” sortkey, 1name, fname, company,

city, state, phone

FROM customer y

WHERE state <> “CA”

INTO TEMP calcust;

SELECT * FROM calcust

ORDER BY 1

这个查询创建一个列表，其中加利福尼亚的客户排在前面，他们的请求最多：

sortkey 1

1name Albertson

fname Frank

company Sporting Place

city Redwood City

state CA

phone 415-886-6677

sortkey 1

1name Baxter

fname Dick

company Blue Ribbon Sports

city Oakland

state CA

phone 415-655-0011

sortkey 1

1name Beatty

fname Lana

company Sportstown

city Menlo Park

state CA

phone 415-356-9982

sortkey 1

1name Currie

fname Philip

company Phil’s Sports

city Palo Alto

state CA

phone 415-328-4543

sortkey 1

1name Grant

fname Alfred

company Gold Medal Sports

city Menlo Park

state CA

phone 415-356-1123

交集

两个行的集合的交集产生了一个表，包含了那些在原来两个表中都有的行。可以使用关键字EXISTS或IN来引入子查询，显示两个集合的交集。图3-2说明了集合运算交集。

下面的嵌套SELECT语句的例子说明了stock和items表的交集：

SELECT stock_num, manu_code, unit_price

FROM stock

WHERE stock_num IN

( SELECT stock_num FROM items )

ORDER BY stock_num

结果包含了所有两个集合都有的元素，返回下面57行：

stock_num manu_code unit_price

1 HRO $250.00

1 HSK $800.00

1 SMT $450.00

2 HRO $126.00

3 HSK $240.00

3 SHM $280.00

4 HRO $480.00

4 HSK $960.00

5 ANZ $19.80

5 NRG $28.00

5 SMT $25.00

6 ANZ $48.00

6 SMT $36.00

7 HRO $600.00

8 ANZ $840.00

9 ANZ $20.00

101 PRC $88.00

101 SHM $68.00

103 PRC $20.00

104 PRC $58.00

105 PRC $53.00

105 SHM $80.00

差集

两个行的集合的差集产生一个表，包含了在第一个集合中但不在第二个集合中的行。可以使用关键字NOT EXISTS或NOT IN来引入子查询，显示两个集合的差集。图3-3说明了集合运算差集。

下面的嵌套SELECT语句的例子说明了stock和items表间的差集：

SELECT stock_num, manu_code, unit_price

FROM stock

WHERE stock_num NOT IN

( SELECT stock_num FROM items )

ORDER BY stock_num

结果包含了所有只在第一个集合的元素，返回以下17行：

stock_num manu_code unit_price

102 PRC $480.00

102 SHM $220.00

106 PRC $23.00

107 PRC $70.00

108 SHM $45.00

112 SHM $549.00

113 SHM $685.90

203 NKL $670.00

305 HRO $48.00

308 PRC $280.00

310 ANZ $84.00

310 SHM $80.00

311 SHM $48.00

312 HRO $72.00

312 SHM $96.00

313 ANZ $60.00

313 SHM $72.00

总结

本章是基于第2章所引入的概念的。它提供了更高级的SELECT语句的语法和结果范例，这些复杂的SELECT语句用于执行对关系数据库的查询。在本章中，

l 介绍了GROUP BY和HAVING子句，可以和聚类一起使用，返回行的组并对那些组施加条件。

l 描述了如何使用rowid从表和系统目录表中检索内部记录号，并讨论了顺序内部表标识符，即tabid。

l 说明了如何通过自连接把一个表和自身相连接，以比较同一个列中的不同的两个值，以及如何标识重复。

l 介绍了关键字OUTER，解释了如何用外连接来不对称地处理两个或多个表，给出了四种类型的外连接的例子。

l 描述了如何通过在另一个SELECT语句的WHERE子句中嵌套SELECT语句来创建相关的和不相关的子查询，说明了在子查询中聚类的用法。

l 说明了在创建子查询时关键字ALL、ANY、EXISTS、IN和SOME的用法，以及加入关键字NOT或关系运算符的效果。

l 讨论了集合运算并集、交集和差集。

l 说明了如何使用UNION和UNION ALL关键字来创建包含两个或多个SELECT语句的复合查询。

jian704

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫