MySQL，等值联结、内部联结、多表连接、自联结、自然联结、外部联结、带聚集函数的联结

• 1、表模型（概略）

2、联结表

使用一条select语句，检索出存储在多张表的数据。

2.1、等值联结

SELECT A.vend_id,A.vend_name,B.*
FROM vendors as A,products as B    # 规定要联结的表
WHERE A.vend_id=B.vend_id;    # 如何产生联结

2.1、内部联结

SELECT A.vend_id,A.vend_name,B.*
FROM vendors as A INNER JOIN products as B
ON A.vend_id=B.vend_id;

ps：与上一句select同效果，注意内联结的条件不用where，而用on。

2.3、多表联结

SELECT A.prod_id,C.vend_name,B.prod_name,B.prod_price,A.item_price
FROM orderitems as A, products as B, vendors as C
WHERE A.prod_id=B.prod_id
	AND B.vend_id=C.vend_id;

3、高级联结

3.1、自联结

在单条select语句中不止一次引用相同的表。

例：产品ID为2023207的商品存在问题，需要检查该产品供应商所生产的其它产品是否也有问题。

解决方案有：

• 方案一：select中嵌套子select查询
  • 检索id=2023207对应的供应商id；
  • 根据返回的供应商id，检索该供应商对应的所有商品id

    SELECT * FROM products
    WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id=2023207);

• 方案二：表联结
  • 产品表 联结 产品表；
  • where条件
    • 匹配共有字段产生联结关系
    • 指定产品id=2023207

      SELECT A1.*
      FROM products as A1,products as A2
      WHERE A1.vend_id = A2.vend_id
      	AND A2.prod_id=2023207;

3.2、自然联结

联结时，排除相同的列多次出现，情况如上文2.1节的结果图中有2个wend_id字段。

系统无法自动排除这种情况，需要手动指定需要的列、手动去除重复的列。

3.3、外部联结

对另一个表中没有关联的行进行联结。

例一：左外联结

检索所有客户的订单情况，包括未下单的客户。

SELECT A.cust_id,A.cust_name,B.*
FROM customers as A  LEFT OUTER JOIN orders as B	#从左边匹配，从customers表选中所有行
ON A.cust_id=B.cust_id;

例二：右外联结

检索所有订单对应的用户

SELECT A.cust_id,A.cust_name,B.*
FROM customers as A  RIGHT OUTER JOIN orders as B	#从右边匹配，从orders表选中所有行
ON A.cust_id=B.cust_id;

3.4、带聚集函数的联结

# 每个客户所下的订单数(仅包含已下单的客户)
## 聚集函数+内联结
SELECT customers.cust_id,customers.cust_name,COUNT(orders.order_num) as zongshu
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id
GROUP BY customers.cust_id;

# 每个客户所下的订单数（包含未下单的客户）
## 聚集函数+外联结
SELECT customers.cust_id,customers.cust_name,COUNT(orders.order_num) as zongshu
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id
GROUP BY customers.cust_id;

# 每个客户所下的订单数（包含未下单的客户）
## 聚集函数+嵌套查询
SELECT cust_id,cust_name,(SELECT COUNT(*) FROM  orders WHERE orders.cust_id=customers.cust_id) as order_sum
FROM customers
GROUP BY cust_id;

4、练习

## 分组
#  查询每个订单的总价
SELECT order_num,SUM(item_price) FROM orderitems GROUP BY order_num;

## 2表联结
# 查询每个供应商供应的产品信息
SELECT vendors.vend_name,products.prod_id,products.prod_name,products.prod_price
 FROM vendors,products
 WHERE vendors.vend_id = products.vend_id;

## 分组 + 表联结：通过用户id联结用户表、订单表
SELECT cust_id,cust_name,(SELECT COUNT(*) FROM  orders WHERE orders.cust_id=customers.cust_id) as order_sum
FROM customers
GROUP BY cust_id;

## 2张表的联结，通过共有字段prod_id联结商品表、订单详情表
# 查询订单详情里，具体的商品名、商品价格
SELECT orderitems.prod_id,prod_name,prod_price FROM products,orderitems WHERE products.prod_id = orderitems.prod_id;

## 三表联结：订单详情表、商品表、供应商表
# 查询订单详情表中每个商品的供应商名称及供应商价格
SELECT vendors.vend_id,vend_name,products.prod_id,products.prod_name,prod_price,item_price 
FROM products,orderitems,vendors
 WHERE orderitems.prod_id = products.prod_id AND vendors.vend_id = products.vend_id; 

## 表的联结
# 查询每个用户购买的产品名称、价格
SELECT cust_name,prod_name,prod_price FROM orderitems,orders,customers,products
WHERE orders.order_num = orderitems.order_num AND orders.cust_id = customers.cust_id AND products.prod_id = orderitems.prod_id;