MySQL联结
总结
1. 联结方式有等值联结、自联结、自然联结和外联结。注意使用哪种联结。
2. 一定要标注联结条件,否则返回值不正确,得到笛卡尔积。
3. 注意联结的性能。
联结的通俗理解:把几个不同的表通过相同的列连接起了得到一个表,或者把其中几列取出来。联结不是物理实体,在数据库中不存在这个表,只存在于查询的执行中。
一、了解联结前需掌握一些基础知识。
主键(Primary Key)&外键(Foreign Key)
主键是唯一的标识,不会变化,如供应商的ID。当信息有所改变时,主键也不会改变。
外键是其他表中的一列,包含了另一个表的主键。
外键可避免储存重复信息,且相关表中的信息更新不影响外键。
联结通过主键和外键实现!
可伸缩性:能够适应不断增加的工作量而不失败。
必须使用完全限定列名!如果不使用会出现笛卡尔积(Cartesian Product)的情况,或称叉联结(Cross Join)
二、联结方法:
- 等值联结(又称内部联结)
- 自联结
- 自然联结
- 外部联结
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;
-
- 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.csdn.net/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;