联结查询。

1. 联结的基本用法

1.1 关系表

举个例子，我们建立两个表分别对应供应商vendors，和商品信息products。在供应商表中，以供应商的ID作为主键，商品表中以商品ID作为主键。

显然这两者是有联系的，所以我们把vendors表的主键叫做外键，最后形成关系表。

如果数据存储在多个表中，需要依靠联结来实现用单条SELECT语句检索出数据。联结可以从多个表返回一组输出。

1.2 创建联结

前面其实已经有过介绍，这里强化一下。利用WHERE vendors.vend_id=products.vend_id即可构建联结关系。此外，FROM子句也和前面不一样，它选择了两个表。

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM vendors,products
    -> WHERE vendors.vend_id = products.vend_id
    -> LIMIT 3;
+-------------+--------------+------------+
| vend_name   | prod_name    | prod_price |
+-------------+--------------+------------+
| Anvils R Us | .5 ton anvil |       5.99 |
| Anvils R Us | 1 ton anvil  |       9.99 |
| Anvils R Us | 2 ton anvil  |      14.99 |
+-------------+--------------+------------+
3 rows in set (0.22 sec)

这种联结称为等值联结（equijoin），它基于两个表之间的相等测试，这种联结也称为内部联结。

如果要联结多个表需要用AND将联结关系组合起来。

mysql> SELECT vend_name, prod_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;
+-------------+----------------+------------+----------+
| vend_name   | prod_name      | prod_price | quantity |
+-------------+----------------+------------+----------+
| Anvils R Us | .5 ton anvil   |       5.99 |       10 |
| Anvils R Us | 1 ton anvil    |       9.99 |        3 |
| ACME        | TNT (5 sticks) |      10.00 |        5 |
| ACME        | Bird seed      |      10.00 |        1 |
+-------------+----------------+------------+----------+
4 rows in set (0.21 sec)

2. 联结的高级用法

2.1 使用别名

前面介绍了别名用作被检索的表列，这里别名用于联结表的简称。

mysql> SELECT cust_name, cust_contact
    -> FROM customers AS c, orders AS o, orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num;
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Wascals        | Jim Jones    |
| Yosemite Place | Y Sam        |
| E Fudd         | E Fudd       |
+----------------+--------------+
11 rows in set (0.22 sec)

2.2 不同类型的联结

（1）自联结

举个例子，假如你发现某物品（其ID为DTNTR）存在问题，因此想知道负责生产的供应商的其他物品是否有问题。因此查询逻辑是：

找到生产ID为DTNTR的物品的供应商
找出这个供应商生产的其他物品。

前面我们提到了WHERE中的子查询可以解决这个办法

mysql> 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) |
+---------+----------------+
7 rows in set (0.23 sec)

现在使用联结进行相同的查询：

    -> 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) |
+---------+----------------+
7 rows in set (0.23 sec)

Products表用了两次，这是允许的，这也是为什么被称作自联结的原因。但这个使用具有二义性，为MySQL不知道你引用的是products表中的哪个实例。

这段代码的逻辑是：

WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表
按第二个表中的 prod_id找到所需要的vend_id，返回所需的数据。

（2）自联结

实现联结的必要条件是：至少有一个列出现在不止一个表中。在标准联结中，会返回所有数据，甚至相同的列会出现很多次，自然联结就是要排除多次出现。

这一般是通过对表使用通配符（SELECT *），你只能选择那些唯一的列，然后对其他需要明确的列写出来。

mysql> SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.item_price
    -> FROM customers AS c, orders AS o, orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num
    -> AND prod_id = 'FB';

上面这段程序的逻辑是：

在orderitem中查到prod_id='FB'，以此为依据知道了order_num
以order_num为依据在orders中查到了cust_id
在customer中找到这个cust_id。
将customer的所有信息连同上面提到的order_item等信息拼在一起，输出。由于cust_id10001下了两个订单，所以有两行。

（3）外部联结

假设我们需要对每个客户下了多少订单进行计数，包括那些至今还没有下单的客户。由于没有下单所以order表中没有记录，缺乏关联性，所以需要外部联结。与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。

mysql> SELECT c.cust_id,o.order_num
    -> FROM customers AS c LEFT OUTER JOIN orders AS o
    -> ON c.cust_id=o.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (1.82 sec)

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT 指出的是OUTER JOIN左边的表）。

如果使用RIGHT，则保证所有order_num都会出现。

mysql> SELECT c.cust_id,o.order_num
    -> FROM customers AS c RIGHT OUTER JOIN orders AS o
    -> ON c.cust_id=o.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.67 sec)

MySQL总结6-联结

2019-11-29
数据库

MySQL总结6-联结

1. 联结的基本用法

1.1 关系表

1.2 创建联结

2. 联结的高级用法

2.1 使用别名

2.2 不同类型的联结

MySQL总结6-联结

1. 联结的基本用法

1.1 关系表

1.2 创建联结

2. 联结的高级用法

2.1 使用别名

2.2 不同类型的联结

感谢支持！