《SQL必知必会》第十一课 使用子查询 创建嵌套在其他查询中的查询

第十一课 使用子查询

子查询：

[1] 迄今为止，所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句

[2] SELECT语句是SQL的查询

[3]SQL允许创建子查询，即嵌套在其他查询中的查询

利用子查询进行过滤：

所有课中使用的数据库表都是关系表，具体说明如下：

Vendors表：

[1]Vendors 表存储销售产品的供应商
[2]每个供应商在这个表中有一个记录，供应商ID 列（vend_id）用于进行产品与供应商的匹配。
[3]所有表都应有主键。
[4]此表应用vend_id 作为其主键。

Products表：

[1]Products 表包含产品目录，每行一个产品
[2]每个产品有唯一的ID（prod_id 列）
[3]借助vend_id（供应商的唯一ID）与供应商表（Vendors 表）相关联

Customers 表：

[1]Customers 表存储所有顾客信息
[2]每个顾客有唯一的ID（cust_id 列）
[3]此表应该用cust_id 作为它的主键

Orders 表：

[1]Orders 表存储顾客订单（ 不是订单细节）
[2]每个订单唯一编号（order_num 列）
[3]Orders 表按cust_id 列（关联到Customers 表的顾客唯一ID）关联到相应的顾客。
[4]此表应该用order_num 作为其主键
[5]为实施引用完整性，应该在cust_id 上定义一个外键，关联到Customers表的cust_id 列。

OrderItems 表：

[1]OrderItems 表存储每个订单中的实际物品，每个订单的每个物品一行
[2]对于Orders 表的每一行，在OrderItems 表中有一行或多行
[3]每个订单物品由订单号加订单物品（第一个物品、第二个物品等）唯一标识
[4]订单物品用order_num 列（关联到Orders 表中订单的唯一ID）与其相应的订单相关联。
[5]每个订单物品包含该物品的产品ID（把物品关联到Products 表）。
[6]此表应该用order_num 和order_item 作为其主键
[7]为实施引用完整性，应该在order_num 和prod_id 上定义外键，关联order_num 到Orders 的order_num 列，关联prod_id 到Products的prod_id 列。

使用关系图来说明数据库表的关联方式：

#订单存储在两个表中（Orders 表和OrderItems 表）
#每个订单包含订单编号（order_num）、订单日期（order_date）、客户ID（cust_id），在Orders 表中存储为一行
#各订单的物品存储在相关的OrderItems 表中
#Orders 表不存储顾客信息，只存储顾客ID（cust_id）
#顾客的实际信息存储在Customers 表中

列出订购物品RGAN01 的所有顾客

下面列出具体的检索步骤：
[1]检索(OrderItems 表)包含物品（prod_id=）RGAN01 的所有订单的编号(order_num)

[2]检索(Orders 表)具有前一步骤列出的订单编号(order_num)的所有顾客的ID(cust_id)

[3]检索(Customers 表)前一步骤返回的所有顾客的ID(cust_id)的顾客信息

[4]上述每个步骤都可单独作为一个查询来执行，可以把一条SELECT 语句返回的结果用于另一条SELECT 语句的WHERE 子句。

[5]可以使用子查询来把3 个查询组合成一条语句

【1】SELECT order_num FROM orderitems WHERE prod_id='RGAN01';
#检索(OrderItems 表)包含物品（prod_id=）RGAN01 的所有订单的编号(order_num)
#对prod_id 为RGAN01 的所有订单物品，检索其order_num 列
#列出两个包含此物品的订单

【2】SELECT cust_id FROM orders WHERE order_num IN (20007,20008);
#检索(Orders 表)具有前一步骤列出的订单编号(order_num)的所有顾客的ID(cust_id)
#查询与订单20007和20008 相关的顾客ID

现结合这两个查询，把第一个查询（返回订单号的那一个）变为子查询

【3】SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='RGAN01');
#在SELECT 语句中，子查询总是从内向外处理

#DBMS 实际上执行了两个操作:
(1)执行SELECT order_num FROM orderitems WHERE prod_id='RGAN01'，查询返回两个订单号：20007 和20008
(2)这两个值以IN 操作符要求的逗号分隔的格式传递给外部查询的WHERE 子句
(3)外部查询:SELECT cust_id FROM orders WHERE order_num IN (20007,20008);

#得到订购物品RGAN01 的所有顾客的ID

注意1：
格式化SQL：
（1）包含子查询的SELECT 语句难以阅读和调试
（2）把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用
（3）DBMS 客户端正是出于这个原因使用了颜色代码SQL

【3】SELECT cust_name,cust_contact,cust_city FROM customers WHERE cust_id IN ('1000000004','1000000005');
#检索(Customers 表)前一步骤返回的所有顾客的ID(cust_id)的顾客信息
#检索这些顾客ID 的顾客信息

【4】SELECT cust_name,cust_contact,cust_city FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='RGAN01')
#把其中的WHERE 子句转换为子查询，而不是硬编码这些顾客ID

#DBMS 实际上必须执行三条SELECT 语句
（1）最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句
（2）外面的子查询返回顾客ID 列表，此顾客ID 列表用于最外层查询的WHERE 子句
（3）最外层查询返回所需的数据

在WHERE 子句中使用子查询能够编写出功能很强且很灵活的SQL语句

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

注意2：
[1]作为子查询的SELECT 语句只能查询单个列。企图检索多个列将返回错误。
[2]使用子查询并不总是执行这类数据检索的最有效方法

作为计算字段使用子查询：

#使用子查询的另一方法是创建计算字段

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译