SQL select 语法

来源：互联网发布：网页排版软件编辑：程序博客网时间：2024/05/17 05:08

SQL 里面最常用的命令是 SELECT 语句，用于检索数据。语法是：

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF class_name [, ...] ] ]
    [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

现在我们将通过不同的例子演示 SELECT 语句复杂的语法。用于这些例子的表在 供应商和部件数据库 里定义。

1.4.1.1. 简单的 Select

这里是一些使用 SELECT 语句的简单例子：

Example 1-4. 带有条件的简单查询

要从表 PART 里面把字段 PRICE 大于 10 的所有记录找出来，我们写出下面查询：

SELECT * FROM PART
    WHERE PRICE > 10;

然后得到表：

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25

在 SELECT语句里使用 "*" 将检索出表中的所有属性。如果我们只希望从表 PART 中检索出属性 PNAME 和 PRICE，我们使用下面的语句：

SELECT PNAME, PRICE 
    FROM PART
    WHERE PRICE > 10;

这回我们的结果是：

                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25

请注意 SQL 的 SELECT 语句对应关系演算里面的 "projection" （映射），而不是 "selection"（选择）（参阅 关系演算 获取详细信息）。

WHERE 子句里的条件也可以用关键字 OR，AND，和 NOT 逻辑地连接起来：

SELECT PNAME, PRICE 
    FROM PART
    WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE <= 15);

这样将生成下面的结果：

 PNAME  |  PRICE
--------+--------
 Bolt   |   15

目标列表和 WHERE 子句里可以使用算术操作。例如，如果我们想知道如果我们买两个部件的话要多少钱，我们可以用下面的查询：

SELECT PNAME, PRICE * 2 AS DOUBLE
    FROM PART
    WHERE PRICE * 2 < 50;

这样我们得到：

 PNAME  |  DOUBLE
--------+---------
 Screw  |    20
 Nut    |    16
 Bolt   |    30

请注意在关键字 AS 后面的 DOUBLE 是第二个列的新名字。这个技巧可以用于目标列表里的每个元素，给它们赋予一个在结果列中显示的新的标题。这个新的标题通常称为别名。这个别名不能在该查询的其他地方使用。

1.4.1.2. Joins（连接）

下面的例子显示了 SQL 里是如何实现连接的。

要在共同的属性上连接三个表 SUPPLIER，PART 和 SELLS，我们通常使用下面的语句：

SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND
          P.PNO = SE.PNO;

而我们得到的结果是：

 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam

在 FROM 子句里，我们为每个关系使用了一个别名，因为在这些关系间有着公共的命名属性（SNO 和 PNO）。现在我们可以区分不同表的公共命名属性，只需要简单的用每个关系的别名加上个点做前缀就行了。联合是用与 一个内部联接里显示的同样的方法计算的。首先算出笛卡儿积 SUPPLIER × PART × SELLS 。然后选出那些满足 WHERE子句里给出的条件的记录（也就是说，公共命名属性的值必须相等）。最后我们映射出除 S.SNAME 和 P.PNAME 外的所有属性。

另外一个进行连接的方法是使用下面这样的 SQL JOIN 语法：

select sname, pname from supplier
JOIN sells USING (sno)
JOIN part USING (pno);

giving again:

 sname | pname
-------+-------
 Smith | Screw
 Adams | Screw
 Smith | Nut
 Blake | Nut
 Adams | Bolt
 Blake | Bolt
 Jones | Cam
 Blake | Cam
(8 rows)

一个用 JOIN 语法创建的连接表，是一个出现在 FROM 子句里的，在任何 WHERE，GROUP BY 或 HAVING子句之前的表引用列表项．其它表引用，包括表名字或者其它 JOIN 子句，如果用逗号分隔的话，可以包含在 FROM 子句里．连接生成的表逻辑上和任何其它在 FROM 子句里列出的表都一样．

SQL JOIN 有两种主要类型，CROSS JOIN (无条件连接) 和条件连接．条件连接还可以根据声明的 连接条件(ON，USING，或 NATURAL)和它应用的方式(INNER 或 OUTER 连接)进一步细分．

连接类型

CROSS JOIN

{ T1 } CROSS JOIN { T2 }

一个交叉连接（cross join）接收两个分别有 N 行和 M 行的表 T1 和 T2，然后返回一个包含交叉乘积 NxM 条记录的连接表．对于 T1 的每行 R1，T2 的每行 R2 都与 R1 连接生成连接的表行 JR，JR 包含所有 R1 和 R2 的字段． CROSSJOIN 实际上就是一个 INNER JOIN ON TRUE．

条件 JOIN

{ T1 } [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ] JOIN { T2 } { ON search condition | USING ( join column list ) }

一个条件 JOIN 必须通过提供一个(并且只能有一个) NATURAL，ON，或者 USING 这样的关键字来声明它的连接条件． ON 子句接受一个 search condition，它与一个 WHERE 子句相同．USING 子句接受一个用逗号分隔的字段名列表，连接表中必须都有这些字段，并且用那些字段连接这些表，生成的连接表包含每个共有字段和两个表的所有其它字段． NATURAL 是 USING子句的缩写，它列出两个表中所有公共的字段名字．使用 USING 和 NATURAL 的副作用是每个连接的字段都只有一份拷贝出现在结果表中(与前面定义的关系演算的 JOIN 相比较)．

[ INNER ] JOIN

对于 T1 的每行 R1，连接成的表在 T2 里都有一行满足与 R1 一起的连接条件．

对于所有 JOIN 而言，INNER 和 OUTER 都是可选的．INNER 是缺省． LEFT，RIGHT，和 FULL 只用于 OUTER JOIN．

LEFT [ OUTER ] JOIN

首先，执行一次 INNER JOIN．然后，如果 T1 里有一行对任何 T2 的行都不满足连接条件，那么返回一个连接行，该行的 T2 的字段为 null．

小技巧: 连接成的表无条件地包含 T1 里的所有行．

RIGHT [ OUTER ] JOIN

首先，执行一次 INNER JOIN．然后，如果 T2 里有一行对任何 T1 的行都不满足连接条件，那么返回一个连接行，该行的 T1 的字段为 null．

小技巧: 连接成的表无条件地包含 T2 里的所有行．

FULL [ OUTER ] JOIN

首先，执行一次 INNER JOIN．然后，如果 T1 里有一行对任何 T2 的行都不满足连接条件，那么返回一个连接行，该行的 T1 的字段为 null．同样，如果 T2 里有一行对任何 T1 的行都不满足连接条件，那么返回一个连接行，该行的 T2 的字段为 null．

小技巧: 连接成的表无条件地拥有来自 T1 的每一行和来自 T2 的每一行．

所有类型的 JOIN 都可以链接在一起或者嵌套在一起，这时 T1 和 T2 都可以是连接生成的表．我们可以使用圆括弧控制 JOIN 的顺序，如果我们不主动控制，那么连接顺序是从左到右．

1.4.1.3. 聚集操作符

SQL提供以一些聚集操作符（如， AVG，COUNT，SUM，MIN，MAX），这些聚集操作符以一个表达式为参数。只要是满足 WHERE子句的行，就会计算这个表达式，然后聚集操作符对这个输入数值的集合进行计算．通常，一个聚集对整个 SELECT 语句计算的结果是生成一个结果．但如果在一个查询里面声明了分组，那么数据库将对每个组进行一次独立的计算，并且聚集结果是按照各个组出现的(见下节)．

Example 1-5. 聚集

果我们想知道表 PART 里面所有部件的平均价格，我们可以使用下面查询：

SELECT AVG(PRICE) AS AVG_PRICE
    FROM PART;

结果是：

 AVG_PRICE
-----------
   14.5

如果我们想知道在表 PART 里面存储了多少部件，我们可以使用语句：

SELECT COUNT(PNO)
    FROM PART;

得到：

 COUNT
-------
   4

1.4.1.4. 分组聚集

SQL 允许我们把一个表里面的记录分成组。然后上面描述的聚集操作符可以应用于这些组上（也就是说，聚集操作符的值不再是对所有声明的列的值进行操作，而是对一个组的所有值进行操作。这样聚集函数是为每个组独立地进行计算的。）

对记录的分组是通过关键字 GROUP BY 实现的，GROUP BY 后面跟着一个定义组的构成的属性列表。如果我们使用语句 GROUP BY A₁, &tdot;, A_k 我们就把关系分成了组，这样当且仅当两条记录在所有属性 A₁, &tdot;, A_k 上达成一致，它们才是同一组的。

Example 1-6. 聚集

如果我们想知道每个供应商销售多少个部件，我们可以这样写查询：

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME;

得到：

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  2  | Jones |   1
  3  | Adams |   2
  4  | Blake |   3

然后我们看一看发生了什么事情。首先生成表 SUPPLIER 和 SELLS 的连接：

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
   1   |  Smith  |   2
   2   |  Jones  |   4
   3   |  Adams  |   1
   3   |  Adams  |   3
   4   |  Blake  |   2
   4   |  Blake  |   3
   4   |  Blake  |   4

然后我们把那些属性 S.SNO 和 S.SNAME 相同的记录放在组中：

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
                 |   2
--------------------------
   2   |  Jones  |   4
--------------------------
   3   |  Adams  |   1
                 |   3
--------------------------
   4   |  Blake  |   2
                 |   3
                 |   4

在我们的例子里，我们有四个组并且现在我们可以对每个组应用聚集操作符 COUNT，生成上面给出的查询的最终结果。

请注意如果要让一个使用 GROUP BY 和聚集操作符的查询的结果有意义，那么用于分组的属性也必须出现在目标列表中。所有没有在 GROUP BY 子句里面出现的属性都只能通过使用聚集函数来选择。否则就不会有唯一的数值与其它字段关联．

还要注意的是在聚集上聚集是没有意义的，比如，AVG(MAX(sno))，因为 SELECT 只做一个回合的分组和聚集．你可以获得这样的结果，方法是使用临时表或者在 FROM 子句中使用一个子 SELECT 做第一个层次的聚集．

1.4.1.5. Having

HAVING子句运做起来非常象 WHERE 子句，只用于对那些满足 HAVING 子句里面给出的条件的组进行计算。其实，WHERE在分组和聚集之前过滤掉我们不需要的输入行，而 HAVING 在 GROUP 之后那些不需要的组．因此，WHERE无法使用一个聚集函数的结果．而另一方面，我们也没有理由写一个不涉及聚集函数的 HAVING．如果你的条件不包含聚集，那么你也可以把它写在WHERE 里面，这样就可以避免对那些你准备抛弃的行进行的聚集运算．

Example 1-7. Having

如果我们想知道那些销售超过一个部件的供应商，使用下面查询：

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME
    HAVING COUNT(SE.PNO) > 1;

and get:

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  3  | Adams |   2
  4  | Blake |   3

1.4.1.6. 子查询

在 WHERE 和 HAVING 子句里，允许在任何要产生数值的地方使用子查询（子选择）。这种情况下，该值必须首先来自对子查询的计算。子查询的使用扩展了 SQL 的表达能力。

Example 1-8. 子查询

如果我们想知道所有比名为 'Screw' 的部件贵的部件，我们可以用下面的查询：

SELECT * 
    FROM PART 
    WHERE PRICE > (SELECT PRICE FROM PART
                   WHERE PNAME='Screw');

结果是：

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25

当我们检查上面的查询时会发现出现了两次 SELECT 关键字。第一个在查询的开头 - 我们将称之为外层 SELECT - 而另一个在WHERE 子句里面，成为一个嵌入的查询 - 我们将称之为内层 SELECT。对外层 SELECT 的每条记录都必须先计算内层SELECT。在完成所有计算之后，我们得知名为 'Screw' 部件的记录的价格，然后我们就可以检查那些价格更贵的记录了。(实际上，在本例中，内层查询只需要执行一次，因为它不依赖于外层查询高等状态．)

如果我们想知道那些不销售任何部件的供应商（比如说，我们想把这些供应商从数据库中删除），我们用：

SELECT * 
    FROM SUPPLIER S
    WHERE NOT EXISTS
        (SELECT * FROM SELLS SE
         WHERE SE.SNO = S.SNO);

在我们的例子里，结果列将是空的，因为每个供应商至少销售一个部件。请注意我们在 WHERE 子句的内层 SELECT 里使用了来自外层SELECT 的 S.SNO。正如前面所说的，子查询为每个外层查询计算一次，也就是说， S.SNO 的值总是从外层 SELECT的实际记录中取得的。

1.4.1.7. 在 FROM 里面的子查询

一种有些特别的子查询的用法是把它们放在 FROM 子句里．这个特性很有用，因为这样的子查询可以输出多列和多行，而在表达式里使用的子查询必须生成一个结果． FROM 里的子查询还可以让我们获得多于一个回合的分组/聚集特性，而不需要求助于临时表．

Example 1-9. FROM 里面的子查询

如果我们想知道在所有我们的供应商中的最高平均部件价格的那家，我们不能用 MAX(AVG(PRICE))，但我们可以这么写：

SELECT MAX(subtable.avgprice)
    FROM (SELECT AVG(P.PRICE) AS avgprice
          FROM SUPPLIER S, PART P, SELLS SE
          WHERE S.SNO = SE.SNO AND
                P.PNO = SE.PNO
          GROUP BY S.SNO) subtable;

这个子查询为每个供应商返回一行（因为它的 GROUP BY）然后我们在外层查询对所有行进行聚集．

1.4.1.8. Union, Intersect, Except（联合，相交，相异）

这些操作符分别计算两个子查询产生的元组的联合，相交和集合理论里的相异。

Example 1-10. Union, Intersect, Except

下面的例子是 UNION 的例子：

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Jones'
UNION
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Adams';

产生结果：

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna

下面是相交（ INTERSECT）的例子：

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
INTERSECT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO < 3;

产生结果：

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris

两个查询都会返回的元组是那条 SNO=2 的

最后是一个 EXCEPT 的例子：

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
EXCEPT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 3;

结果是：

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna

作者Blog：http://blog.csdn.net/meskgron/

相关文章

基于ASP.NET实现全球化 SQL select 语法 ADO 与ADO.NET JDBC连接数据库格式 JavaBean实现多文件上传的两种方法