本篇文章继续围绕SQL的语法重点为大家介绍子查询的使用。
01 子查询
使用子查询进行过滤
在SQL中SELECT语句用于查询,之前所使用的所有SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句。然而SQL还可以创建子查询,即嵌套在其他查询中的查询。
示例:
数据表:本次使用的数据库表都是关系表。订单存储在两个表中,每个订单包含订单编号、客户ID、订单日期,在Orders表中存储为一行。各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息,只存储顾客ID。顾客实际信息存储在Customers表中。
若现在需要检索出订购RGAN01的所有顾客,应怎样检索?步骤如下:
①检索包含物品RGAN01的所有订单的编号。
②检索具有前一步骤列出的订单编号的所有顾客的ID。
③检索前一步骤返回的所有顾客ID的顾客信息。
上述每个步骤都可单独作为一个查询来进行。
可将一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句,也可使用子查询来将3个查询组合成一条语句。
①第一个语句含义明确,是对prod_id为RGAN01的所有订单物品检索其order_num列。
示例:
SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’
分析:通过该语句知道了哪个订单包含要检索的物品。
②下一步查询与上述语句检索出的订单20007和20008相关的顾客ID。此处可利用IN子句。
示例:
SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008)
下面可结合上述两个查询,将第一个查询变为子查询。
示例:
SELECT cust_id
FROM Orders
WHERE order_num IN
(SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’)
分析:在SELECT语句中,子查询总是从内向外处理。在处理上述SELECT语句时,DBMS实际上执行了两个操作。
首先执行了圆括号()内的查询,此查询返回两个订单号:20007和20008.
接着这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变为:
SELECT cust_id
FROM orders
WHERE order_num IN (20007,20008)
该语句检索的结果和前面硬编码WHERE子句返回的结果相同。
③由上述语句得出订购物品RGAN01的所有顾客ID:100004和100005.下一步检索这些顾客ID的顾客信息。
示例:
SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN (100004,100005)
也可将其中的WHERE子句转换为子查询,就不用硬编码这些顾客ID了。
示例:
SELECT cust_name,
cust_contace
FROM Customers
WHERE cust_id IN
(SELECT cust_id
FROM Orders
WHERE order_num IN
(SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’))
分析:DBMS实际上必须执行三条SELECT语句才能完成上述语句。最里面的子查询返回订单号,此列用于外面的子查询的WHERE子句。外面的子查询返回顾客ID列,此顾客ID列用于最外层查询的WHERE子句。最外层查询返回最终所需的数据。
由此可见,在WHERE子句中使用子查询可编写出功能强大灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际应用中由于性能的限制,不宜嵌套太多子查询。
注意:作为子查询的SELECT语句只能查询单个列,检索多个列将返回错误。另外使用子查询并不总是执行该类数据检索的最有效方法。
不止SQL数据库学习,还有全面的大数据分析学习
TopBDA大数据分析师培训
作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
示例:需要显示Customers表中每个顾客的订单总数。订单与相应的顾客ID都存储在Orders表中。要执行这个操作,需要以下步骤:
①从Customers表中检索顾客列表。
②对于检索出的每个顾客,统计其在Orders表中的订单数目。
这里我们可以应用之前介绍的SELECT COUNT(*)对表中的行进行计数,并通过一条WHERE子句来过滤某个特定的顾客ID,仅对该顾客的订单进行计数。
如下对顾客1000001的订单进行计数:
SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id = 1000001
要对每个顾客执行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
分析:该SELECT语句对Customers表中的每个顾客返回三列:cust_name、cust_state和orders。orders是一个计算字段,它由圆括号中的子查询建立。该子查询对检索出的每个顾客执行一次。此例中,该子查询执行了5次,因为检索出了5个顾客。
子查询中的WHERE子句与之前使用的WHERE子句略有不同,因为它使用了完全限定列名,而不只是列名(cust_id)。它指定表名和列名(Orders.cust_id和Customers.cust_id)。下面的WHERE子句告诉SQL,比较Orders表中的cust_id和当前正从Customers表中检索出的cust_id:
WHERE Orders.cust_id = Customers.cust_id
在有可能混淆列名时必须用一个句点分隔表名和列名。此例中,有两个cust_id列:一个在Customers中,另一个在Orders中。若不采用完全限定名,DBMS会认为要对Orders表中的cust_id自身进行比较。因为:
SELECT COUNT(*)
FROM Orders
WHERE cust_id =cust_id
上述语句总返回Orders表中订单的总数,而该结果不是我们想要的,如下:
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE cust_id = cust_id) AS orders
FROM Customers
ORDER BY cust_name
由上可知,在构造语句时,若涉及到多个表,而不对同一列名加以区分则会引起DBMS抛出错误信息。
好的做法是,当在SELECT语句中操作多个表时,使用完全限定列名来避免歧义。
最后总结一下子查询的特点:
①子查询必须括在圆括号中。
②子查询的SELECT子句中只能有一个列,除非主查询中有多个列,用于与子查询选中的列相比较。
③子查询不能使用ORDER BY,不过主查询可以。在子查询中,GROUP BY可以起到同ORDER BY相同的作用。
④返回多行数据的子查询只能同多值操作符一起使用,比如IN操作符。
⑤SELECT 列表中不能包含任何对BLOB、ARRAY、CLOB或者NCLOB类型值的引用。
⑥子查询不能直接用在聚合函数中。
⑦BETWEEN操作符不能同子查询一起使用,但是BETWEEN操作符可以用在子查询中。
以上便是本次介绍的全部内容,下篇文章将为大家讲解连接和高级连接的使用。
我们下期再见!