摘要: 本篇博客仅作为笔记,如有侵权,请联系,立即删除(网上找博客学习,然后手记笔记,因纸质笔记不便保存,所以保存到网络笔记)。
一、子查询
注意:版本要求:MySQL4.1引入了对子查询的支持,所以要想使用本章描述的SQL,必须使用MySQL4.1或更高级的版本。
SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句。
查询(query) 任何SQL语句都是查询。但此术语一般指SELECT语句。
二、利用子查询进行过滤
有些数据库是以关系表。一些信息就存储在表中。
现在加入需要列出订购物品TNT2的所有客户,应该怎样检索?下面列出具体的步骤:
(1)检索包含物品TNT2的所有订单的编号;
(2)检索前一步列出的订单的所有客户的ID;
(3)检索前一步骤返回的所有客户ID的客户信息。
上述的每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
第一条SELECT语句的含义很明确,对于prod_id为TNT2的所有订单物品,它检索其order_num列:
SELECT order_num FROMorderitemsWHERE prod_id = 'TNT2';
下一步,查询具有订单20005和20007的客户ID:
SELECT cust_id FROMordersWHERE order_num IN (20005,20007);
现在,把第一个查询(返回订单号的那一个)变为子查询组合两个查询:
SELECT cust_id FROMordersWHERE order_num IN (SELECT order_num FROMorderitemsWHERE prod_id = 'TNT2');
分析:在SELECT语句中,子查询总是从内向外处理。在处理上面的SELECT语句时,MySQL实际上执行了两个操作。
注意:格式化SQL 包含子查询的SELECT语句难以阅读和调试,特别是它们较为复杂时更为如此。如上所示把子查询分解为多行并且适当地进行缩进,能极大地简化子查询的使用。
现在得到了订购物品TNT2的所有客户的ID。下一步就是检索这些客户的ID:
SELECT cust_name,cust_contact FROMcustomersWHERE cust_id IN (10001,10004);
可以把其中的WHERE子句转换为子查询而不是硬编码这些客户ID:
SELECT cust_name,cust_contact FROMcustomersWHERE cust_id IN(SELECT cust_id FROMordersWHERE order_num IN (SELECT order_num FROMorderitemsWHERE prod_id = 'TNT2'));
分析:为了执行上述SELECT语句,MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单列表,此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表,此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。
可见,在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
注意:列必须匹配 在WHERE子句中使用子查询,应该保证SELECT语句具有与WHERE子句中相同数目的列。通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列。虽然子查询一般与IN操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等。
子查询和性能 这里给出的代码有效并获得所需的结果。但是,使用子查询并不总是执行这种类型的数据检索的最有效的方法。(后序博文将再次给出这个例子)。
三、作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单数。订单与相应客户ID存储在orders表中:
为了执行这个操作,遵循下面的步骤:
(1)从customers表中检索客户列表。
(2)对于检索出的每个客户,统计其在orders表中的订单数目。
正如前博所说,可以使用SELECT COUNT(*)对表中的行进行计数,并且通过提供一条WHERE子句来过滤某个特定的客户ID(比如10001),可仅对该客户的订单进行计数。
SELECT COUNT(*) AS orders FROMordersWHERE cust_id = 10001;
为了对每个客户执行COUNT(*)计算,应该将COUNT(*)作为一个子查询。请看下面的代码:
SELECTcust_name,
cust_state,(SELECT COUNT(*) FROM orders WHEREorders.cust_id=customers.cust_id)AS orders FROMcustomersORDER BY cust_name;
分析:这条SELECT语句对customers表中每个客户返回3列:cust_name、cust_state和orders。orders是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。子查询中WHERE子句与前面使用的WHERE子句稍有不同,因为它使用了完全限定列名。下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id。
相关子查询 涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时候知道要列名可能有多义性,就必须使用这种语法(表名和列名由一个据点分隔)。虽然子查询在构造这种SELECT语句时极有用,但必须注意限制有歧义性的列名。
四、小结
讨论了子查询以及如何使用它们。子查询最常见的使用是在WHERE子句的IN操作符中,以及用来填充计算列(以上两个例子)。