6. SQL 语言的深入解析

6.1. Introduction

在本章中，我们将介绍

SELECT 命令的其他语法，这些语法使其成为一个非常强大的查询命令，特别是用于同时查询多个表。
已学命令的扩展语法

为了说明各种命令，我们将使用以下用于图书分销 PME 中订单管理的表：

6.1.1. CLIENTS表

该表存储 PME 系统中的客户信息：

`ID`	客户唯一标识号 - 主键
`NOM`	客户名称
`STATUT`	I=个人，E=企业，A=政府
`PRENOM`	个人客户姓氏
`CONTACT`	客户方的联系人姓名（适用于企业或政府机构）
`RUE`	客户地址 - 街道
`VILLE`	城市
`CPOSTAL`	邮政编码
`TELEPH`	电话
`DEPUIS`	成为客户的时间？
`DEBITEUR`	如果客户欠公司钱，请选 O（是）；否则选 N（否）。

6.1.2. 表 ARTICLES

该表存储已售产品的信息，此处为书籍。其结构如下：

`ISBN`	唯一标识一本书的编号（ISBN=国际标准书号）——主键
`TITRE`	书籍标题
`CODEDITEUR`	唯一标识出版商的代码
`AUTEUR`	作者姓名
`RESUME`	书籍摘要
`QTEANCOUR`	年度销量
`QTEANPREC`	上一年度销量
`DERNVENTE`	最近一次销售日期
`QTERECUE`	最近一次交货数量
`DERNLIV`	最近一次交货日期
`PRIXVENTE`	销售价格
`COUT`	进货成本
`MINCDE`	最低订购量
`MINSTOCK`	最低库存阈值
`QTESTOCK`	库存数量

其内容可能如下：

6.1.3. 表 COMMANDES

该表记录客户下单的信息。其结构如下：

`NOCMD`	唯一标识订单的编号 - 主键
`IDCLI`	下单客户编号 - 外部密钥 - 产品编号 CLIENTS(ID)
`DATE_CMD`	此订单录入日期
`ANNULE`	若订单已被取消，则为 O（是）；否则为 N（否）。

6.1.4. 表 DETAILS

该表包含订单的详细信息，即所订购书籍的书号和数量。其结构如下：

`NOCMD`	订单号——外键，引用表NOCMD中的COMMANDES列
`ISBN`	订购图书编号 - 引用表 LIVRES 中列 ISBN 的外键
`QTE`	订购数量

其内容可能如下：

上文可见，订单编号 3（NOCMD）涉及三本书。这意味着客户同时订购了三本书。该客户的参考信息可在表[COMMANDES]中查到，其中显示订单编号3是由客户编号5下达的。 [CLIENTS]表显示，客户编号5是位于塞格雷的NetLogos公司。

6.2. 订单 SELECT

在此，我们将通过介绍该订单的新语法，进一步加深对订单 SELECT 的理解。

6.2.1. 多表查询的语法

`syntaxe`	SELECT 列1, 列2, ... FROM 表1, 表2, ..., 表p WHERE condition ORDER BY ...
`action`	此处的创新之处在于，列 colonne1、colonne2、... 来自多个表 table1、table2、...。如果两个表中有同名的列，则通过 tablei.colonnej 这种表示法来消除歧义。 condition 可以涉及不同表中的列。

工作原理

`1`	由 table1、table2、……、tablep 构成的笛卡尔积表已生成。若 ni 是 tablei 的行数，则生成的表包含 n1n2...*np 行，涵盖了各表的所有列。
`2`	将 WHERE 的 condition 应用于该表。由此生成一张新表
`3`	该表按 ORDER 中指定的方式排序。
`4`	显示了 SELECT 后面的所需列。

示例

使用前面介绍的表。我们想了解 9 月 25 日之后下达的订单详情：

SQL>select details.nocmd,isbn,qte from commandes,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd

需要注意的是，在 FROM 之后，需列出所有被引用的列所属的表名。在前面的示例中，所选列均属于表 DETAILS。然而，该条件所引用的却是表 COMMANDES。因此，有必要在 FROM 之后命名该表。测试两个不同表中列是否相等的操作通常称为等值连接。

查询 SELECT 也可以写成如下形式：

SQL> select details.nocmd,isbn,qte from commandes
    inner join details on details.nocmd=commandes.nocmd
    where commandes.datecmd>'25-sep-91'

让我们继续看几个例子。我们希望得到与之前相同的结果，但显示的是所订购书籍的书名，而不是其编号 ISBN：

SQL>select commandes.nocmd, articles.titre, details.qte
  from commandes,articles,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn

使用以下查询 SQL 也能得到相同的结果，但可读性较低：

SQL> select details.nocmd,articles.titre,details.qte from details
    inner join commandes on details.nocmd=commandes.nocmd
    inner join articles on  details.isbn=articles.isbn
    where commandes.datecmd>'25-sep-91'

上文中，与表 [DETAILS] 进行了两次内部连接：

其中一次与表 [COMMANDES] 进行连接，以获取某本书的订购日期
一个关联表[ARTICLES]，用于获取所订购书籍的书名

此外，我们还希望获取下单客户的姓名：

SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
  from commandes,details,articles,clients
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn
  and commandes.idcli=clients.id

此外，我们还需要订单日期，并按日期降序排列：

SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
    from commandes,details,articles,clients
    where commandes.datecmd>'25-sep-91'
    and details.nocmd=commandes.nocmd
    and details.isbn=articles.isbn
    and commandes.idcli=clients.id
    order by commandes.datecmd descending

以下是连接时应遵守的规则：

在 SELECT 之后，列出希望在显示中获取的列。如果该列存在于多个表中，则需在其前加上表名。
在 FROM 之后，列出所有将被 SELECT 查询的表，即拥有 SELECT 和 WHERE 之后列的表。

6.2.2. 自连接

我们想查询哪些书籍的售价高于《Using SQL》一书：

SQL>select a.titre from articles a, articles b
  where b.titre='Using SQL'
  and a.prixvente>b.prixvente

此处的两个连接表是相同的：即表 articles。为了区分它们，我们给它们起了别名：from articles a, articles b。第一个表的别名为 a，第二个表的别名为 b。即使表不同，也可以使用这种语法。使用别名时，在整个 SELECT 命令中，都必须使用该别名来代替它所指代的表。

6.2.3. 外部连接

我们希望查询9月份有购买记录的客户及其订单日期。其余客户则不显示该日期：

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where datecmd between '01-sep-91' and '30-sep-91'

令人惊讶的是，这里没有得到正确的结果。表[CLIENTS]中本应包含所有客户，但实际情况并非如此。仔细分析外连接的运作机制后，我们发现未购买的客户被关联到了表COMMANDES中的一条空行，因此其日期字段也为空（在SQL术语中为NULL）。该日期不符合设定的日期条件，因此对应的客户不会显示。让我们尝试另一种方法：

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where (commandes.datecmd between '01-sep-91' and '30-sep-91')
        or (commandes.datecmd is null)

这次我们得到了问题的正确答案。

6.2.4. 嵌套查询

syntaxe

SELECT 列[s] FROM 表[s]

WHERE 表达式运算符查询

ORDER BY ...

fonctionnement

requête 是一个 SELECT 命令，它返回 0、1 或多个值的集合。因此，我们得到一个类型为

表达式运算符 (val1, val2, ..., vali)

expression 和 vali 必须是同类型。如果查询返回单个值，则可简化为以下类型的条件

表达式运算符值

，这是我们所熟悉的。如果查询返回一组值，则可以使用以下运算符：

IN

expression IN (val1, val2, ..., vali)：若 expression 的值为列表 vali 中的某个元素，则为真。

NOT IN

IN 的逆运算

ANY

必须以 =、!=、>、>=、<、<= 开头

expression >= ANY (val1, val2, .., valn)：若 expression 大于或等于列表中任意一个 vali 值，则为真

ALL

表达式前必须带有 =,!=,>,>=,<,<=

expression >= ALL (val1, val2, .., valn)：若表达式大于等于列表中的所有 vali 值，则为真

EXISTS

查询：若 requête 返回至少一行，则为真。

示例

我们重新探讨一个已通过等值连接解决的问题：显示售价高于书籍《Using SQL》的书名。

SQL>select titre from ARTICLES
    where prixvente > (select prixvente from ARTICLES where titre='Using SQL')

该方案似乎比等值连接更直观。首先使用 SELECT 进行初次筛选，然后对所得结果进行二次筛选。可以以此方式进行多次串联筛选。

若要查询售价高于平均售价的股票：

SQL> select titre from ARTICLES
    where prixvente > (select avg(prixvente) from ARTICLES)

哪些客户订购了前一查询结果中的作品？

SQL>select distinct idcli from COMMANDES,DETAILS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd

说明

在表 DETAILS 中，筛选出价格高于书籍平均价格的书籍中包含的代码 ISBN。
在上一步骤选出的行中，没有客户代码 IDCLI。该代码位于表 COMMANDES 中。两张表通过订单号 NOCMD 建立关联，因此需要进行 COMMANDES.nocmd=DETAILS.nocmd 的等值连接。
同一客户可能多次购买过某本相关书籍，这种情况下其代码IDCLI会多次出现。为避免这种情况，需将关键字 DISTINCT 置于 SELECT 之后。通常情况下，DISTINCT 可消除 SELECT 结果行中的重复数据。
若要获取客户名称，我们需要在 COMMANDES 和 CLIENTS 表之间进行额外的等值连接，如下述查询所示。

SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd
    and COMMANDES.IDCLI=CLIENTS.ID

查找自9月24日以来未下单的客户：

SQL>select nom from CLIENTS
    where clients.id not in
    (select distinct commandes.idcli from commandes where datecmd>='24-sep-91')

我们已经看到，除了使用子句 WHERE 之外，还可以通过将子句 HAVING 与子句 GROUP、BY 结合使用来筛选行。子句 HAVING 可筛选行组。

与子句 WHERE 类似，语法


     HAVING expression opérateur requête

也是可行的，但需满足先前提出的约束条件：即 expression 必须是子句


     GROUP BY expr1, expr2, ...

示例

200F 以上的书籍销量是多少？

首先按书名显示销量：

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre

现在，我们来筛选这些股票：

SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre
    having titre in (select titre from ARTICLES where prixvente>200)

或许更直观的写法是：

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    and ARTICLES.prixvente>200
    group by titre

6.2.5. 相关查询

对于嵌套查询，存在一个父查询（最外层的查询）和一个子查询（最内层的查询）。只有当子查询完全求解完毕后，父查询才会被求解。

关联查询的语法基本相同，仅在细节上有所不同：子查询会对父查询的表进行连接。在这种情况下，父查询与子查询的组合会针对父表的每一行反复进行评估。

示例

我们继续使用之前那个示例，即查询自9月24日以来未下单的客户姓名：

SQL> 
select nom from clients
    where not exists
        (select idcli from commandes
            where datecmd>='24-sep-91'
                and commandes.idcli=clients.id)

父查询作用于表 clients。子查询对表 clients 和 commandes 进行连接。因此这是一个关联查询。对于表 clients 中的每一行，子查询都会执行：它在 9 月 24 日之后的订单中查找客户代码 id。如果未找到（not exists），则显示客户姓名。随后，系统将转至表 clients 的下一行。

6.2.6. 编写 SELECT 的选择标准

我们多次看到，通过不同的SELECT写入方式可以得到相同的结果。举个例子：显示下过单的客户：

连接

SQL>
select distinct nom from clients,commandes
    where clients.id=commandes.idcli

嵌套查询

SQL> 
select nom from clients
    where id in (select idcli from commandes)

结果相同。

相关查询

SQL>
select nom from clients
    where exists (select * from commandes where commandes.idcli=clients.id)

结果相同。

作者Christian MAREE和Guy LEDANT在其著作《SQL：入门、编程与精通》中提出了若干选择标准：

性能

用户并不知道SGBD是如何“运作”以找到其请求的结果的。因此，用户只能通过实践经验，才能发现某种写法比另一种更高效。MAREE和LEDANT根据经验指出，关联查询通常比嵌套查询或连接查询运行得更慢。

表达方式

与连接相比，嵌套查询的表达通常更易读且更直观。但这种方法并非总是适用。特别需要注意以下两点：

SELECT 查询中参数列所属的表（SELECT col1, col2, ...）必须在 FROM 关键字之后列出。此时将对这些表进行笛卡尔积运算，即所谓的连接。

当查询显示来自单个表的结果，且对该表行进行筛选时需要查询另一个表，则可以使用嵌套查询。

6.3. 语法扩展

出于方便起见，我们通常只介绍了各种命令的简化语法。在本节中，我们将介绍其扩展语法。这些语法不言自明，因为它们与我们深入探讨过的 SELECT 命令的语法类似。

INSERT

`syntaxe1`	INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...)
`syntaxe2`	INSERT INTO table (col1, col2, ..) (requête)
`explication`	已介绍了这两种语法

DELETE

`syntaxe1`	DELETE FROM table WHERE condition
`explication`	该语法已知。此外，条件中可以包含一个采用以下语法的查询：WHERE 表达式运算符 (查询)

UPDATE

`syntaxe1`	UPDATEtable SET col1=expr1, col2=expr2, ... WHERE condition
`explication`	该语法已介绍过。补充说明：条件中可以包含一个查询，其语法为 WHERE 表达式运算符（查询）

`syntaxe2`	UPDATE table SET (col1, col2, ..)= 查询1, (cola, colb, ..)= 查询2, ... WHERE condition
`explication`	分配给各列的值可以来自一个查询。