联结表

联结

SQL强大的功能之一就是可以在数据检索查询的执行中联结。

关系表

我们设计两个表，一个供应商表，一个商品表，供应商表的主键标识就是商品表的外键。

这样关系数据可以有效的存储和方便的处理，他的可伸缩性是要远远好于非关系数据库。

可伸缩性

能够不断适应增加的工作量而不失败，设计良好的数据库或应用程序称之为可伸缩性好。

为什么使用联结

就像上面说的，你把数据分解到多个数据表这是有代价的，如果你想要的数据在多个数据表中你要怎么办呢？

:right_anger_bubble:答案就是使用联结。

创建联结

SELECT vend_name ,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

:panda_face:这里要完全限定列名，不然MySQL可处理不了这种充满二义性的WHERE语句。

WHERE子句的重要性

在联结两个表的时候。实际是将第一个表的每一行，与第二个表的每一行配对，WHERE子句作为过滤条件，他只包含哪些匹配给定条件的行。没有WHERE子句，返回的信息将是特别特别多的呀。

笛卡尔积

由于没有连接条件的表关系返回的结果为笛卡尔积。检索出的行的数目

内部联结

目前为止的所有连接都是等值连接，他是基于两个表之间的相等测试。这种连接称为内部联结。其实，这种连接是原有另外一种写法 的。（这种写法可以明确指定连接的类型）

SELECT vend_name ,prod_name ,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

连接多个表

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;

:closed_umbrella:mysql处理联结表是特别浪费性能的，所以我们要仔细，不要联结不必要的表。联结的表越多，性能的下降越厉害。

我们可以把之前的一个SELECT拿出来搞一个一题多解。

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') );

我们试试联结的方法

SELECT cust_name , cust_contact
FROM customers , orders, orderitems
WHERE customers.cust_id = orders.cust_id
        AND orderitems.order_num = orders.order_num
        AND prod_id = 'TNT2';

创建高级联结

使用表别名

:ice_cream: 这是我们之前使用到的表别名的例子

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

别名除了用于列名和计算字段之外，SQL还允许给表名起列名。主要两个原有、

1. 缩短SQL语句
2. 允许在单条SELECT语句中多次使用相同的表

那么我们就来试一试这个别名

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
        AND prod_id = 'TNT2';

使用不同类型的联结

直到现在，我们使用的都是内部联结或者等值联结的简单联结，下面我们要继续向前通关了呀。

自联结

例：你发现某物品存在问题，因此想知道生产该物品的供应商生产的其他物品是否也有这些问题。

SELECT  p1.prod_id , p2.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

:ice_skate:使用别名，我们就可以用自联结而不是子查询。有时候会快的多。

自然联结

迄今为止，我们建立的每一个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

外部联结

联结包含哪些在相关表中没有关联行的行，这种类型的联结称为外部联结。

:taco: 例：内部联结:

SELECT customers.cust_id ,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id  = orders.cust_id;

:eagle:例：外部联结：

SELECT customers.cust_id ,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id  = orders.cust_id;

那个10002对应的null就很能说明问题，因为你是left联结，所以左边表是要包含全部内容的，所以就会有10002对应的null这样一个信息，因为你左联结了，所以左边表的所有行都要出现。

:umbrella:mysql不支持一个*=的操作符，这在其他的dbms里是十分受欢迎的。

使用带聚集函数的联结

例： 检索所有客户以及每个客户所下的订单数

SELECT customers.cust_name,
        customers.cust_id,
        COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN  orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

:incoming_envelope:下面我们用一用这个左连接，把那些没有下单的客户也揪出来

SELECT customers.cust_name,
        customers.cust_id,
        COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN  orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

例：检索所有客户及每个客户所下的订单数

SELECT customers.cust_name,
        customers.cust_id,
        COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;