MySQL ⼦查询与表连接

⼦查询与表连接

⼦查询(嵌套sql)

SELECT语句是SQL的查询。迄今为⽌我们所看到的所有SELECT语句都是简单查询，即从单个数据
库表中检索数据的单条语句。
SQL还允许创建⼦查询（subquery），即嵌套在其他查询中的查询。

利⽤⼦查询进⾏过滤

订单存储在两个表中。对于包含订单号、客户ID、订单⽇期的每个订单，orders表存储⼀⾏。 各订单的
物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。
实际的客户信息存储在customers表中。
现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

--(1) 检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
--(2) 检索具有前⼀步骤列出的订单编号的所有客户的ID
select cust_id from orders where order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
--(3) 检索前⼀步骤返回的所有客户ID的客户信息。
select cust_name,cust_contact from customers where cust_id in (10001,10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

可以把其中的WHERE⼦句转换为⼦查询⽽不是硬编码这些SQL返回的数据：

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 = 'TNT2'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。
--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。
--最外层查询确实返回所需的数据。

这⾥给出的代码有效并获得所需的结果。

但是，使⽤⼦查询并不总是执⾏这种类型的数据检索的最有效的⽅法。

作为计算字段使⽤⼦查询

使⽤⼦查询的另⼀⽅法是创建计算字段。

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列表。
select cust_id,cust_name from customers ;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。
select count(*) as orders from orders where cust_id = 10001;
+--------+
| orders |
+--------+
| 2 |
+--------+

为了对每个客户执⾏COUNT()计算，应该将COUNT()作为⼀个⼦查询。

select cust_id,cust_name,
 (select count(*)
 from orders
 where orders.cust_id = customers.cust_id) as orders
 )
from customers
order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name | orders |
+---------+----------------+--------+
| 10001 | Coyote Inc. | 2 |
| 10005 | E Fudd | 1 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 6 |
| 10004 | Yosemite Place | 1 |
+---------+----------------+--------+

orders是⼀个计算字段，它是由圆括号中的⼦查询建⽴的。该⼦查询对检索出的每个客户执⾏⼀
次。在此例⼦中，该⼦查询执⾏了5次，因为检索出了5个客户。

注意:⼦查询中的WHERE⼦句与前⾯使⽤的WHERE⼦句稍有不同，因为它使⽤了完全限定列名
这种类型的⼦查询称为相关⼦查询。任何时候只要列名可能有多义性，就必须使⽤这种语法
（表名和列名由⼀个句点分隔）。因为有两个cust_id列，⼀个在customers中，另⼀个在
orders中，需要⽐较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名，
MySQL将假定你是对orders表中的cust_id进⾏⾃身⽐较。

关系表

SQL最强⼤的功能之⼀就是能在数据检索查询的执⾏中联结（join）表。
在能够有效地使⽤联结前，必须了解关系表以及关系数据库设计的⼀些基础知识。

--假如有⼀个包含产品⽬录的数据库表，其中每种类别的物品占⼀⾏。
--对于每种物品要存储的信息包括产品描述和价格，以及⽣产该产品的供应商信息。
产品表：
 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
 A6 ... ... 奥迪 ... ....
 520li .. .... 宝⻢ ... ...
 ...

--现在，假如有由同⼀供应商⽣产的多种物品，那么在何处存储供应
--商信息（如，供应商名、地址、联系⽅法等）呢？
 产品，描述，价格，供应商名称，供应商地址,供应商联系⽅式
 A6 ... ... 奥迪 ... ....
 520li .. .... 宝⻢ ... ...
  A8 .. ... 奥迪 ... ...

相同数据出现多次决不是⼀件好事，此因素是关系数据库设计的基础。
关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。
各表通过某些常⽤的值（即关系设计中的关系（relational））互相关联。
在这个例⼦中，可建⽴两个表，⼀个存储供应商信息，另⼀个存储产品信息。

-- vendors表包含所有供应商信息
|vend_id | vend_name | vend_address| vend_city ....
-- products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。
prod_id | vend_id | prod_name | prod_price | prod_desc

vendors表的主键⼜叫作products的外键，它将vendors表与products表关联，利⽤供应商ID能 从vendors表中找出相应供应商的详细信息。 这样做的好处如下：

• 供应商信息不重复，从⽽不浪费时间和空间；
• 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
• 由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单

关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

表联结

如果数据存储在多个表中，怎样⽤单条SELECT语句检索出数据？
答案是使⽤联结。简单地说，联结是⼀种机制，⽤来在⼀条SELECT语句中关联表，因此称之为联
结。
使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。

例如：我们需要查询出所有的商品及对应的供应商信息怎么办？

-- 联结的创建⾮常简单，规定要联结的所有表以及它们如何关联即可。
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)
--这两个表⽤WHERE⼦句正确联结，WHERE⼦句指示MySQL匹配vendors表中的vend_id和products表
中的vend_id。
--可以看到要匹配的两个列以 vendors.vend_id 和 products. vend_id指定。这⾥需要这种完全限
定列名，因为如果只给出vend_id，则MySQL不知道指的是哪⼀个（它们有两个，每个表中⼀个）。
--在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）。

在联结两个表时，你实际上做的是将第⼀个表中的每⼀⾏与第⼆个表中的每⼀⾏配对。
WHERE⼦句作为过滤条件，它只包含那些匹配给定条件（这⾥是联结条件）的⾏。
你能想象上⾯的sql如果没有where条件时会怎样吗？

select vend_name,prod_name,prod_price from vendors,products

如果没有where条件，第⼀个表中的每个⾏将与第⼆个表中的每个⾏配对，⽽不管它们逻辑上是
否可以配在⼀起
由没有联结条件的表关系返回的结果为笛卡⼉积。检索出的⾏的数⽬将是第⼀个表中的⾏数乘以
第⼆个表中的⾏数。

不要忘了WHERE⼦句

应该保证所有联结都有WHERE⼦句，否则MySQL将返回⽐想要的数据多得多的数据。
同理，应该保证WHERE⼦句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据

其实，对于这种联结可以使⽤稍微不同的语法来明确指定联结的类型。
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

两个表之间的关系是FROM⼦句的组成部分，以INNER JOIN指定。
在使⽤这种语法时，联结条件⽤特定的ON⼦句⽽不是WHERE⼦句给出。
传递给ON的实际条件与传递给WHERE的相同。
SQL规范⾸选INNER JOIN语法。

联结多个表

SQL对⼀条SELECT语句中可以联结的表的数⽬没有限制。

创建联结的基本规则也相同。⾸先列出所有表，然后定义表之间的关系。

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
 where products.vend_id = vendors.vend_id
 and orderitems.prod_id = products.prod_id
 and order_num = 20005;

MySQL在运⾏时关联指定的每个表以处理联结。 这种处理可能是⾮常耗费资源的，因此应该仔
细，不要联结不必要的表。联结的表越多，性能下降越厉害。

使⽤表别名 AS

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。
这样做有两个主要理由：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使⽤相同的表

应该注意，表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机

⾃联结

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道⽣产该物品的供应商⽣产的其他物品
是否也存在这些问题。此查询要求⾸先找到⽣产ID为DTNTR的物品的供应商，然后找出这个供应
商⽣产的其他物品。

-- 使⽤⼦查询(嵌套查询)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 使⽤联结的相同查询：
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 此查询中需要的两个表实际上是相同的表，因此products表在FROM⼦句中出现了两次。虽然这是完全
合法的，但对products的引⽤具有⼆义性，因为MySQL不知道你引⽤的是products表中的哪个实例。
-- 为解决此问题，使⽤了表别名。products的第⼀次出现为别名p1，第⼆次出现为别名p2。现在可以将
这些别名⽤作表名。
--例如，SELECT语句使⽤p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存
在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪⼀个列（即使它们事实上是同⼀个
列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）⾸先联结两个表，然后按第⼆个表中的
prod_id过滤数据，返回所需的数据

⽤⾃联结⽽不⽤⼦查询 ⾃联结通常作为外部语句⽤来替代从相同表中检索数据时使⽤的⼦查询语
句。
虽然最终的结果是相同的，但有时候处理联结远⽐处理⼦查询快得多。

外部链接

许多联结将⼀个表中的⾏与另⼀个表中的⾏相关联。但有时候会需要包含没有关联⾏的那些⾏。
例如，可能需要使⽤联结来完成以下⼯作：

• 对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；
• 列出所有产品以及订购数量，包括没有⼈订购的产品；
  • 计算平均销售规模，包括那些⾄今尚未下订单的客户

在上述例⼦中，联结包含了那些在相关表中没有关联⾏的⾏。这种类型的联结称为外部联结。

-- 内部联结。它检索所有客户及其订单：
select customers.cust_id,orders.order_num from customers inner join orders on
customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)

--外部联结语法类似。检索所有客户，包括那些没有订单的客户
select customers.cust_id,orders.order_num from customers left join orders on
customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)

聚集函数也可以⽅便地与其他联结⼀起使⽤。
如果要检索所有客户及每个客户所下的订单数，下⾯使⽤了COUNT()函数的代码可完成此⼯作
包含那些没有任何下订单的客户。

select customers.cust_name,customers.cust_id,count(orders.order_num) as
num_ord from customers left join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+

• 保证使⽤正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡⼉积。
• 在⼀个联结中可以包含多个表，甚⾄对于每个联结可以采⽤不同的联结类型。虽然这样做是
  合法的，⼀般也很有⽤，但应该在⼀起测试它们前，分别测试每个联结。这将使故障排除更
  为简单

组合查询 UNION

MySQL也允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则
UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此，
如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次
序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，
不同的数值类型或不同的⽇期类型）。

--假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品。
-- 先查询第⼀个结果
select vend_id,prod_id,prod_price from products where prod_price <= 5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
4 rows in set (0.00 sec)
-- 再查询第⼆个结果
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
5 rows in set (0.00 sec)

--使⽤union将两个sql⼀并执⾏
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
8 rows in set (0.09 sec)

-- 这条语句由前⾯的两条SELECT语句组成，语句中⽤UNION关键字分隔。
-- UNION指示MySQL执⾏两条SELECT语句，并把输出组合成单个查询结果集
-- 以下是同样结果,使⽤where的多条件来实现
select vend_id,prod_id,prod_price from products where prod_price <= 5 or
vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)

--在这个简单的例⼦中，使⽤UNION可能⽐使⽤WHERE⼦句更为复杂。
--但对于更复杂的过滤条件，或者从多个表（⽽不是单个表）中检索数据的情形，使⽤UNION可能会使处理更简单。

• 现在思考⼀个问题,上⾯的语句分别返回了⼏条数据?

• 第⼀条sql返回4⾏,第⼆条sql返回5⾏,那么union返回了⼏⾏?

  UNION从查询结果集中⾃动去除了重复的⾏（换句话说，它的⾏为与单条SELECT语句中使
  ⽤多个WHERE⼦句条件⼀样）。

这是UNION的默认⾏为，但是如果需要，可以改变它。如果想返回所有匹配⾏，可使⽤UNION
ALL⽽不是UNION

• 对组合查询结果排序

  SELECT语句的输出⽤ORDER BY⼦句排序。在⽤UNION组合查询时，只能使⽤⼀条ORDER
  BY⼦句，它必须出现在最后⼀条SELECT语句之后。

对于结果集，不存在⽤⼀种⽅式排序⼀部分，⽽⼜⽤另⼀种⽅式排序另⼀部分的情况，因此不允
许使⽤多条ORDER BY⼦句。

select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
order by prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1003 | TNT1 | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1001 | ANV01 | 5.99 |
| 1002 | OL1 | 8.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)
--这条UNION在最后⼀条SELECT语句后使⽤了ORDER BY⼦句。
--虽然ORDER BY⼦句似乎只是最后⼀条SELECT语句的组成部分，但实际上MySQL将⽤它来排序所有
SELECT语句返回的所有结果。