SQLite学习笔记（四）

概述

本篇文章将继续介绍关于 SELECT 中的一些用法。文章结构如下：

• 子查询
• 多表连接/联结表
• 名称和别名
• 组合/复合查询
• 条件结果

---

一、子查询

子查询是指 SELECT 语句中又嵌套 SELECT 语句。子查询最常应用的地方是 WHERE 子句，特别是在 IN 操作符中。下面通过一个例子来了解下子查询的用法和好处。

例子

列出订购商品 RGAN01 的所有顾客。这个需求的检索思路如下：

1. 检索包含物品 RGAN01 的所有订单的编号。
2. 检索具有前一步步骤列出的订单编号的所有顾客的 ID。
3. 检索前一步骤返回的所有顾客 ID 的顾客信息。

可以看到每一步查询的条件都是上一步查询得到的结果，我们首先进行普通的查询：

输入：

SELECT order_num 
FROM OrderItems 
WHERE prod_id = 'RGAN01';

输出：

order_num
----------
20007
20008

可以看到包含物品 RGAN01 的订单编号为 20007 和 20008，接下来进行第二步：

输入：

SELECT cust_id 
FROM Orders  
WHERE order_num IN(20007, 20008);

输出：

cust_id
----------
1000000004
1000000005

可以得知我们需要的顾客 ID 为 1000000004 和 100000005，接下来进行第三步：

输入：

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id IN('1000000004', '1000000005');

输出：

cust_name   cust_contact
----------  ------------------
Fun4All     Denise L. Stephens
The Toy St  Kim Howard

经过这三步查询之后，我们最终得到了我们想要的顾客信息。

接下来我们用子查询实现同样的效果:

输入：

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id IN (SELECT cust_id 
		FROM Orders 
		WHERE order_num IN (SELECT order_num 
				FROM OrderItems 
				WHERE prod_id = 'RGAN01'));

输出：

cust_name   cust_contact
----------  ------------------
Fun4All     Denise L. Stephens
The Toy St  Kim Howard

可以看到使用子查询我们达到了同样的效果。实际上 DBMS 仍然执行了三条 SELECT 语句。对于子查询来说，有以下三点需要注意：

作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

SQL 语句对于能嵌套的子查询数目没有限制，不过在使用时由于性能的限制，不能嵌套太多子查询。

子查询可以用在关系表达式的任何地方，并非只能用于 WHERE 子句中。

---

二、多表连接/联结表

为了方便描述下面对于该名词统称为联结表。下面先来了解一下什么是联结。

1. 联结

SQL 最强大的功能之一就是能在数据查询的执行中联结(join)表，联结的这些表我们称之为关系表。什么是关系表，我们可以通过下面的一个例子来理解。

2. 关系表

例如，有一个包含产品目录的数据库表，其中每类物品占一行，对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。

而供应商可能不止生产这一类产品，它还有可能生产多种产品，那么供应商名、地址、联系方法等信息又该如何存储？

答案是最好将供应商信息和产品信息分开存储，理由如下：

• 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
• 如果供应商信息发生变化，例如供应商迁址或者电话号码变动，只需修改一次即可；
• 如果有重复数据(即每种产品都存储供应商信息)，很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值相互关联（所以才叫关系数据库）。

例如在上述例子中，我们可以使用一个 Products 表只存储产品信息，除了存储供应商 ID（Vnedors 表主键）外，它不存储其他有关供应商的信息。Vendors 表的主键将 Vendors 表和 Products 表相关联。接下来我们看看如何利用它们之间的联结来查询相关信息。

3. 内联结

内联结就是通过表中的两个字段进行联结，它使用关系代数的一种集合操作，叫做交叉，即找出同时存在于两个集合的元素，如下图所示：

现在我们将 Vendors、Products 这两个表通过 vend_id 字段进行内联结，我们可以使用 WHERE 子句：

输入：

SELECT vend_name, prod_name, prod_price 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;

输出：

vend_name   prod_name          prod_price
----------  -----------------  ----------
Bears R Us  8 inch teddy bear  5.99
Bears R Us  12 inch teddy bea  8.99
Bears R Us  18 inch teddy bea  11.99
Doll House  Fish bean bag toy  3.49
Doll House  Bird bean bag toy  3.49
Doll House  Rabbit bean bag t  3.49
Doll House  Raggedy Ann        4.99
Fun and Ga  King doll          9.49
Fun and Ga  Queen doll         9.49

注意：在本例中我们使用到了完全限定列名，这是因为 vend_id 在两个表中都存在，为了避免引用的列可能出现歧义，必须使用完全限定列名。

WHERE 的作用：
在联结两个表时，实际上要做的就是将第一表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，只包含那些匹配给定条件的行。没有 WHERE 子句，第一个表中的每一行将于第二个表中的每一行配对，而不管它们逻辑上能否配在一起，也就是所谓的笛卡儿积，这在后面会做介绍。

根据前面关于内联结的解释图，我们也可以画出 Vendors 和 Products 的内联结图，如下所示：

两个表的内联结除了使用 WHERE 子句外，还可以使用 INNER JOIN，如下所示：

输入：

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id;

输出：

vend_name   prod_name          prod_price
-----