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()作为⼀一个⼦子查询。
orders是⼀一个计算字段，它是由圆括号中的⼦子查询建⽴立的。该⼦子查询对检索出的每个客户执⾏行行⼀一次。在此例例⼦子中，该⼦子查询执⾏行行了了5次，因为检索出了了5个客户。

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

表联结

-- 联结的创建⾮非常简单，规定要联结的所有表以及它们如何关联即可。
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不不知道指的是哪⼀一个（它们有两个，每个表中⼀一个）。
--在引⽤用的列列可能出现⼆二义性时，必须使⽤用完全限定列列名（⽤用⼀一个点分隔的表名和列列名）。

联结多个表

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;

使用表别名 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)

组合查询 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可能会使处理理
更更简单。