SQL 使用子查询
一、子查询：
- SELECT 语句是SQL的查询。目前所看到的所有SELECT语句都是简单查询，单个数据库表中检索数据的单条语句。
- 查询(query)：
任何SQL语句都是查询。但此术语一般指SELECT语句。
- SQL还允许创建子查询，嵌套再其他查询中的查询。下面举几个栗子来理解一下：
- 注意：MySQL支持
- 如果使用MySQL，应该知道对子查询的支持从4.1版本引入的。MySQL的早期版本不支持子查询。
- 注意：MySQL支持
二、利用子查询进行过滤：
-
首先我们使用的数据库表都是关系表：
-
下面是关于每个表的描述：
-
通过该表，分析一下接下来要举得栗子：
1.订单存储再两个表中。
2.每个订单包含订单编号、客户ID、订单日期，再Orders表中存储为一行。
3.各订单的物品存储再相关的OrderItems表中。
4.Orders表不存储顾客信息，只存储顾客ID。
5.顾客的实际信息存储再Customers表中。
-
需求：
-
需要列出订购物品RGAN001的所有顾客，应该怎样检索？
1.检索物品RGAN001的所有订单的编号。
SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01';+-----------+ | order_num | +-----------+ | 20007 | | 20008 | +-----------+ 2 rows in set (0.01 sec)2.检索具有前一步骤列出的订单编号的所有顾客的ID（20007 , 20008）。
SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);+------------+ | cust_id | +------------+ | 1000000004 | | 1000000005 | +------------+ 2 rows in set (0.00 sec)3.检索前一步骤返回的所有顾客的ID顾客信息。
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ('1000000004', '1000000005');+---------------+--------------------+ | cust_name | cust_contact | +---------------+--------------------+ | Fun4All | Denise L. Stephens | | The Toy Store | Kim Howard | +---------------+--------------------+ 2 rows in set (0.00 sec)
-
-
现在结合1；2这两个查询，把第一个查询(返回订单号的那一个)变为
子查询：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='RGAN01');+------------+ | cust_id | +------------+ | 1000000004 | | 1000000005 | +------------+ 2 rows in set (0.00 sec)-
首先上面的子查询语句，先执行：
SELECT order_num FROM orderitems WHERE prod_id='RGAN01';+-----------+ | order_num | +-----------+ | 20007 | | 20008 | +-----------+ 2 rows in set (0.00 sec) -
此查询返回两个订单号：20007和20008。外部查询就变成了：
SELECT cust_id FROM orders WHERE order_num IN (20007, 20008);+------------+ | cust_id | +------------+ | 1000000004 | | 1000000005 | +------------+ 2 rows in set (0.00 sec) -
可以看到，输出是正确的，与前面硬编码WHERE子句返回的值相同。
-
-
现在结合1；2；3这三个查询，更改为子查询：
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 Store | Kim Howard | +---------------+--------------------+ 2 rows in set (0.01 sec)-
DBMS实际上必须执行三条SELECT语句。
1.最里面的子查询返回订单号列表，此列表用于其外面的子查询WHERE子句。
2.外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的WHERE子句。
3.最外层查询返回所需的数据。
-
-
注意：
对于能嵌套的子查询的数目没有限制，不过再实际使用时由于性能的限制，不能嵌套太多的子查询。 -
注意：只能是单列
- 作为子查询的SELECT语句只能
查询单个列。企图检索多个列将返回错误。
- 作为子查询的SELECT语句只能
-
三、作为计算字段使用子查询：
-
使用子查询的另一方法是
创建计算字段。 -
需要：需要显示customers表中每个顾客的订单总数。订单与相应的顾客ID存储再orders表中。
-
分析上面需要要遵循的步骤：
1.从customers表中检索顾客列表；对于检索出的每个顾客，统计其再orders表中的订单数目。
2.使用SELECT COUNT（*）对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。
-
例如：下面的代码对顾客1000000001的订单进行计数：
-
首先看一下orders表中的数据：
mysql> SELECT * FROM orders; +-----------+---------------------+------------+ | order_num | order_date | cust_id | +-----------+---------------------+------------+ | 20005 | 2012-05-01 00:00:00 | 1000000001 | | 20006 | 2012-01-12 00:00:00 | 1000000003 | | 20007 | 2012-01-30 00:00:00 | 1000000004 | | 20008 | 2012-02-03 00:00:00 | 1000000005 | | 20009 | 2012-02-08 00:00:00 | 1000000001 | +-----------+---------------------+------------+ 5 rows in set (0.00 sec) -
对顾客1000000001的订单进行计数：
SELECT COUNT(*) AS orders FROM orders WHERE cust_id= '1000000001'; -
要对每个顾客执行COUNT（*），应该将它作为一个子查询。
SELECT cust_name, cust_state, ( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;+---------------+------------+--------+ | cust_name | cust_state | orders | +---------------+------------+--------+ | Fun4All | IN | 1 | | Fun4All | AZ | 1 | | Kids Place | OH | 0 | | The Toy Store | IL | 1 | | Village Toys | MI | 2 | +---------------+------------+--------+ 5 rows in set (0.00 sec) -
这条SELECT 语句对customers表中每个顾客返回三列：cust_name、cust_state和orders。
-
orders是一个
计算字段，它是圆括号中的子查询建立的。 -
子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是l列名(cust_id)。
-
它指定表名和列名(orders.cust_id 和 customers.cust_id)。下面的WHERE子句告诉SQL，比较Orders表中的cust_id和当前正从Customers表中检索的cust_id：
WHERE Orders.cust_id = Customers.cust_id- 用一个句点分隔表名和列名，这种语法必须在有可能混淆列名时使用。
-
如果不采用`完全限定列名，DBMS会认为要对Orders表中的cust_id自身进行比较。
SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id；- 而这个结果并不是我们想要
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;+---------------+------------+--------+ | cust_name | cust_state | orders | +---------------+------------+--------+ | Fun4All | IN | 5 | | Fun4All | AZ | 5 | | Kids Place | OH | 5 | | The Toy Store | IL | 5 | | Village Toys | MI | 5 | +---------------+------------+--------+ 5 rows in set (0.00 sec) -
虽然子查询再构造这种SELECT语句时极有用，但必须注意限制有歧义的列。
-
-
-
注意：完全限定列名
- 已经看到为什么要用完全限定列名，如果没有具体指定就会返回错误结果，因为DBMS会误解你的意思。
- 由于出现冲突列名而导致的歧义性，会引起DBMS抛出错误信息。
1463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
