子查询与表连接
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 表中找出相应供应商的详细信息。这样做的好处如下：
关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
表联结
如果数据存储在多个表中，怎样用单条 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可能会使处理更简单。
复制代码
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语句返回的所有结果。
复制代码
评论