MYSQL必知必会读书笔记 第十五和十六章 联结表

为什么要使用联结？

如果数据存储在多个表中，怎样使用单条SELECT语句检索出数据？答案就是使用联结。简单地说，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

1.创建联结

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

注意：在引用列可能出现二义性时，必须使用完全限定列名。

2.WHERE 的重要性

利用WHERE子句建立联结关系似乎有点奇怪，但实际上，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MYSQL如何进行表联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件它只包含哪些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行进行配对，而不管它们的逻辑上是否可以配在一起。

3.笛卡尔积

由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数*第二个表中的行数。

SELECT  vend_name,prod_name,prod_price FROM vendors,products  ORDER BY vend_name,prod_name;

从结果上看：不要忘了WHERE子句  ，应该保证所有联结都有WHERE子句。

4.内部联结

目前为止所用的联结称为等值联结。它基于两个表之间的相等测试。这种联结也称为内部联结。事实上，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

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

5. 联结多个表

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在运行时关联指定的每个表以处理联结，这种处理可能非常耗费资源的。有时候可以通过不同的方式来调优。

比如返回订购产品TNT2的客户列表。

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='TNT12'

6.自联结

比如知道ID 为DTNTR的物品存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这种问题

SELECT prod_id ,prod_name  FROM products WHERE vend_id=(SELECT vend_id  FROM products  WHERE prod_id='DTNTR');

其等价于下面的语句

SELECT  p1.prod_id,p1.prod_name  FROM  products  AS p1products  AS p2 WHERE p1.vend_id =p2.vend.id   AND  p2.prod_id='DNTR';

7.自然联结

SELECT c.* ,o.reder_num ,o.order_date,oi.prod_id,oi.quantity,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';

8.外部联结

许多联结将一个表中的行与另一个表中的行相关联。但是有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

1.对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户。

2.列出所有产品以及订购数量，包括没有人订购的产品。

3.计算平均消费规模，包括那些至今尚未下订单的客户。

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

检索所有客户及其订单：

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

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户。

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

类似于上一章中所看到的内部联结，这一条SELECT语句使用了关键字OUTER JOIN 来指定联结的类型（而不是使用的WHERE子语句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联的行。在使用OUTER  JOIN语法时，必须使用RIGTH或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER  JOIN右边的表，而LEFT指出的是OUTER  JOIN左边的表），

外部联结的类型  ：存在两种基本的外部联结的形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可以通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。

9.内部联结和外部联结的区别

一般所说的左连接，外连接是指左外连接，右外连接。做个简单的测试你看吧。先说左外连接和右外连接：[TEST1@orcl#16-12月-11] SQL>select * from t1;        ID NAME---------- --------------------         1 aaa         2 bbb[TEST1@orcl#16-12月-11] SQL>select * from t2;        ID        AGE---------- ----------         1         20         3         30左外连接：[TEST1@orcl#16-12月-11] SQL>select * from t1 left join t2 on t1.id=t2.id;        ID NAME                         ID        AGE---------- -------------------- ---------- ----------         1 aaa                           1         20         2 bbb右外连接：[TEST1@orcl#16-12月-11] SQL>select * from t1 right join t2 on t1.id=t2.id;        ID NAME                         ID        AGE---------- -------------------- ---------- ----------         1 aaa                           1         20                                         3         30从上面的显示你可以看出：左外连接是以左边的表为基准。通俗的讲，先将左边的表全部显示出来，然后右边的表id与左边表id相同的记录就“拼接”上去，比如说id为1的记录。如果没有匹配的id，比如说t1中id为2的t2中就没有。那边就以null显示。右外连接过程正好相反。再看内连接：[TEST1@orcl#16-12月-11] SQL>select * from t1 inner join t2 on t1.id=t2.id;        ID NAME                         ID        AGE---------- -------------------- ---------- ----------         1 aaa                           1         20 只有一条记录。内连接就是只取出符合过滤条件的记录 也就是t1.id=t2.id  那么符合t1.id=t2.id的记录只有id=1这一条，所以只显示一条。 不像外连接，是将你作为基准的表（左外连接就是左边表为基准，右外连接就是右边表为基准）的所有行都显示出来。

10.使用带聚集函数的联结

聚集函数用来汇总数据的。虽然说聚集函数不仅可以用于单个表，而且也可以用于联结表。例如如果要检索所有的客户及每个客户所下的订单数，下面可使用COUNT()函数的代码完成此工作。

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;

聚集函数也可以很方便地与其他联结一起使用

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;

这个例子使用的是左外部联结来包含所有客户，甚至包含那些没有任何订单的客户。

11.使用联结和联结条件

下面是关于联结及其使用的某些要点。

*  注意所有的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

*  保证使用正确的联结条件否则将返回不正确的数据。

*  应该总是提供联结条件，否则会得出笛卡尔积

*  在一个联结中可以包含多个表，甚至对每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。