01-29-记录下练习时长四天半的MySQL小白练习过程-子查询-内联结

信息记录：
逆序排列语句

ORDER BY [clause] desc;

ok

子查询

现在我们需要查询一个顾客列表，里面包含已订购的总金额（返回顾客ID，顾客订单总数，结果按照金额从大到小排序）

SELECT cust_id,
		 COUNT(*) AS total_order,
		(select SUM(quantity*item_price)
	    FROM orderitems
		WHERE orderitems.order_num = orders.order_num)
		AS total_value
FROM orders
GROUP BY cust_id
ORDER BY (SELECT  SUM(quantity*item_price)
						FROM orderitems
						WHERE orderitems.order_num = orders.order_num
						GROUP BY orderitems.order_num) desc;

这些语句实现了好几件事情

首先是从orders表中返回cust_id和每个id一共下单次数，同时还把每个id的订单总金额显示在最后一列。

排序按照订单总金额从大到小降序排列。

联结

内联结

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

尝试多表查询
很遗憾程序出错

网上声称出现这种问题，是没有具体声明参数在哪个表单中。

Σ(っ °Д °;)っ

检查发现select子句中的vend_id，因为其同时存在于在vendors和products两个表中，所以我们需要在前面加上表头。

再贴一次语句

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

好耶！！！这次一切正常！！！

如果我们想一次性联结多个表单有应该怎么做呢？

方法一：用where······and······and·····
方法二：用inner join···on····inner·····join···on···

SELECT   cust_name,
		 order_num,
		 (SELECT SUM(quantity*item_price)
		  FROM orderitems
		  WHERE orderitems.order_num = orders.order_num
		  )AS Ordertotal
FROM customers
inner JOIN orders ON orders.cust_id = customers.cust_id
ORDER BY cust_name,
			order_num
;

上述语句在一次查询中同时使用了子查询和内联结

返回顾客名称、订单号、以及每个订单的总金额

创建高级联结

自然联结 排除多次出现的列，让每列值出现一次，而这个排除工作由你自己来完成。

外联结，左外联结和右外联结