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

2023-12-19 21:13:21
  • 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;

文章来源:https://blog.csdn.net/qq_40384309/article/details/135047432
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。