高级SELECT语句

原创 2007年09月14日 21:59:00

 

 

高级SELECT语句

本章概览

使用GROUP BYHAVING子句

使用GROUP BY子句

使用HAVING子句

创建高级连接

自连接

外连接

简单连接

两个表上的简单外连接

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

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

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

SELECT语句中的子查询

使用ALL

使用ANY

单值子查询

相关子查询

使用EXISTS

集合运算符

并集

交集

差集

总结

 

本章概览

上一章介绍了用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 BYHAVING子句

可选的GROUP BYHAVING子句增强了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值。(也就是说,每个订单的项目被编组在一起。)编组后,聚类函数COUNTSUM在每个组内起作用。

这个查询对每组返回一行。它用标签来对COUNTSUM表达式的结果命名,显示如下:

  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子句中包括TEXTBYTE数据类型的列。要分组,首先要能排序,对TEXTBYTE数据而言,没有自然的次序关系。

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 BYSELECT对每一个组只返回一行。列在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

这个查询连接ordersitems表,为表指定别名,返回以下两行:

  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语句连接ordersitems表;指定显示标签、表的别名和用作列指示器的整数;对数据分组并排序;并把以下结果放入一个临时表中:

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子句中把一个表写两次,每次各指定一个不同的别名。在SELECTWHERE子句用别名来引用表,就象着写表是两个不同的表一样。(关于在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的意思是“xy是不同的两行。”

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表中返回两次数据,分别给它们指定表的别名xy。它搜索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函数

为了获得关于表的其它信息,可以结合使用rowidUSER函数,在INFORMIX_ONLINE上,还可以结合使用SITENAME关键字。USERSITENAME在本手册的第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语句查找服务器名字和用户名字,以及rowidtabid,其中tabid是系统目录表中使用的顺序内部表标识号码。它为DBSERVERNAMEUSER表达式指定显示标签,返回从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

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

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

简单连接

这是一个简单连接的例子,它把customercust_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          Phils 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语句首先执行一个在ordersitems表上的简单连接,检索由manu_codeKARSHM生产的项目的所有订单的信息。然后,它执行一个外连接,把这些信息和主表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

这个查询首先执行一个在ordersitems表上的外连接,检索由manu_codeKARSHM生产的项目的所有订单的信息。然后,它执行一个外连接,把这些信息和主表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

这个查询分别把从表orderscust_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语句试图创建一个关于两个从表ox间的连接条件,如下面的例子所示,将会得到一个错误信息,指出不能创建双向外连接:

WHERE o.customer_num = x.customer_num

SELECT语句中的子查询

套在另一个SELECT语句(INSERTDELETEUPDATE语句)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

可以与ALLANY一起使用任意的关系运算符,以把某个值和子查询所产生的每个值(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

单值子查询

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

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

相关子查询

下面相关子查询的例子返回orders表中10个最晚(这里原文有误——译者注)的装船日期。在子查询后它包括一个ORDER BY子句,用于对结果进行排序,因为不能在子查询中使用ORDER BY子句。

SELECT  po_num, ship_date FROM orders main

     WHERE  10 >

         ( SELECT  COUNT  (DISTINCT  ship_date )

                FROM  orders sub

                WHERE  sub.ship_date > main.ship_date )

                     AND  ship_date IS  NOT  NULL

         ORDER  BY  ship_date, po_num

子查询是相关的,因为它所产生的数字取决于main.ship_date,而这是外层SELECT所产生的一个值。因此,对外层查询所考察的每一行,子查询都被重新执行一次。

子查询使用COUNT函数来向主查询返回一个值。ORDER BY子句排序数据。查询定位并返回包含了10个最晚的装船日期。

po_num     ship_date

 

4745       06/21/1991

278701     06/29/1991

429Q       06/29/1991

8052       07/03/1991

B77897     07/03/1991

LZ230      07/06/1991

B77930     07/10/1991

PC6782     07/12/1991

DM354331   07/13/1991

S22942     07/13/1991

MA003      07/16/1991

W2286      07/16/1991

Z55709     07/16/1991

C3288      07/25/1991

KF2961     07/30/1991

W9925      07/30/1991

如果你在一个非常大的表上使用象前面例子的相关子查询,应对ship_date做索引,以提高性能。否则,这个SELECT语句的效率很低,因为它对表的每行都执行一次子查询。关于索引和性能问题在本手册的第10章讨论。

使用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

注意,对包含TEXTBYTE数据类型的列不能使用谓词IN

INEXISTS加上关键字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

注意,在这里使用了关键字EXESTSIN来完成集合运算交集,使用了关键字NOT EXISTSNOT 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语句在stockitems表的stock_nummanu_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_nummanu_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

        5 ANZ

        5 ANZ

      302 HRO

      302 KAR

      301 KAR

      201 NKL

        5 NRG

        5 NRG

      103 PRC

      106 PRC

        5 SMT

        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列。它返回120125号客户所在州的简写代码或者snameAa结尾的州。

state

 

AK

AL

AZ

CA

DE

FL

GA

IA

IN

LA

MA

MN

MT

NC

ND

NE

NJ

NV

OK

PA

SC

SD

VA

WV

在复合查询中,第一个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_num10025,或者是items表中的quantity10,然后根据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-4GLINFORMIX-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   Phils 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

. 

. 

. 

 

交集

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

下面的嵌套SELECT语句的例子说明了stockitems表的交集:

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 EXISTSNOT IN来引入子查询,显示两个集合的差集。图3-3说明了集合运算差集。

下面的嵌套SELECT语句的例子说明了stockitems表间的差集:

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 BYHAVING子句,可以和聚类一起使用,返回行的组并对那些组施加条件。

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

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

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

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

l             说明了在创建子查询时关键字ALLANYEXISTSINSOME的用法,以及加入关键字NOT或关系运算符的效果。

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

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

 

 

SELECT语句 联结表 高级联结 组合查询 全文本搜索

http://blog.sina.com.cn/s/blog_4acbd39c0100qp79.html   Sql最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用sql...

MySQL必知必会笔记(四)SELECT语句 联结表 高级联结 组合查询 全文本搜索

第十五章 联结表     Sql最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用sql的select能执行的最重要的操作,能很好的理解联结及其语法是学习sql的一个极为...
  • YMS_KK
  • YMS_KK
  • 2015年09月24日 15:45
  • 391

select语句的高级应用及实例

本文介绍的select高级应用主要包括联合查询、分组查询、嵌套(子查询)、关联子查询和限定查询数目等,与实例对照演示,所使用数据库为sqlite3。...

基本select 语句总结

  • 2012年09月26日 16:17
  • 57KB
  • 下载

select语句使用大全

  • 2014年03月19日 20:46
  • 27KB
  • 下载

Unix网络编程(六)高级I/O技术之复用技术 select

本文将讨论网络编程中的高级I/O复用技术,将从下面几个方面进行展开: a. 什么是复用技术呢? b. 什么情况下需要使用复用技术呢? c. I/O的复用技术的工作原理是什么? d. select, p...

SQL语句select用法详解

  • 2014年07月21日 10:56
  • 18KB
  • 下载

SQL Select语句完整的执行顺序

  • 2013年11月22日 13:48
  • 656B
  • 下载

高级I/O之多路转接select

一.关于I/O要提高网络服务服务器,提高I/O性能,本质上是在提高“等”的比重,“等”的比重趋于零,性能越好,而I/O中为了减少等的比重,可以让I/O一次等多个文件描述符,即I/O模型中的多路复用模型...

Select语句大全,适合初学者

  • 2008年09月18日 07:59
  • 15KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:高级SELECT语句
举报原因:
原因补充:

(最多只允许输入30个字)