MySQL联结

总结

1. 联结方式有等值联结、自联结、自然联结和外联结。注意使用哪种联结。

2. 一定要标注联结条件，否则返回值不正确，得到笛卡尔积。

3. 注意联结的性能。

 

联结的通俗理解：把几个不同的表通过相同的列连接起了得到一个表，或者把其中几列取出来。联结不是物理实体，在数据库中不存在这个表，只存在于查询的执行中。

 

一、了解联结前需掌握一些基础知识。

主键(Primary Key)&外键(Foreign Key)

主键是唯一的标识，不会变化，如供应商的ID。当信息有所改变时，主键也不会改变。

外键是其他表中的一列，包含了另一个表的主键。

外键可避免储存重复信息，且相关表中的信息更新不影响外键。

 

联结通过主键和外键实现！

可伸缩性：能够适应不断增加的工作量而不失败。

必须使用完全限定列名！如果不使用会出现笛卡尔积(Cartesian Product)的情况，或称叉联结(Cross Join)

 

二、联结方法：

1. 等值联结(又称内部联结)
2. 自联结
3. 自然联结
4. 外部联结

 

1. 等值联结(又称内部联结)：基于两个表的相等测试。

有两种语法来明确指定联结的类型。

1. 1. WHERE

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

1. 1. INNER JOIN IN

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

两种语法是等价的，但是基于性能考虑，一般使用INNER JOIN ON的语法

注意，使用完全限定列名！

另外上篇博客中，子查询的语句可通过联结代替。用联结查询得到的结果与子查询一致。

地址：https://blog.youkuaiyun.com/m0_38061639/article/details/82872705

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

联结时，还可联结多个表，语句同上。

联结时注意性能，不要联结不必要的表。

2. 自联结

首先引进表别名。可缩短SQL语句，允许在单条SQL语句中多次使用相同的表。

表别名只在查询执行中使用，与列别名不一样，不返回至客户机。

SELECT c.cust_name FROM customers AS c;

选出产品ID为’DTNTR’的供应商生产的其他商品

产品表：

用自联结查询产品表：

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

上述查询还可用子查询，实践过程中可以两种都试一下，看哪种方法比较快。

3. 自然联结

相对于等值联结，自然联结中不包括重复的列。

4. 外部联结

下图即以左侧表—customers中的顾客id为准，看其对应有哪几个订单order_num。

顾客表：保存所有顾客信息

订单表：保存订单id和对应下单顾客id

进行外部联结操作，以每个顾客为基准，看顾客下的订单。可以看出用户10002并没有下过订单。

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

也可使用RIGHT OUTER JOIN，用法和LEFT OUTER JOIN一致，主要看想要以哪个表为基准。

 

三、使用带聚合函数的联结

如：检索每个顾客的订单数

SELECT customers.cust_name, customers.cust_id, COUNT(customers.cust_id) AS num_ord

FROM customers

INNER JOIN orders

ON customers.cust_id=orders.cust_id

GROUP BY customers.cust_id;

联结三个表：看每个顾客下的订单中的产品数量总和，则涉及三个表：customers，orders，和orderitems。

SELECT customers.cust_name, customers.cust_id, SUM(orderitems.order_item) AS num_ord

FROM customers, orders,orderitems

WHERE customers.cust_id=orders.cust_id

AND orders.order_num=orderitems.order_num

GROUP BY customers.cust_id;