MySQL-16创建高级级联知必会

第十章介绍了使用别名引用被检索的表列https://blog.csdn.net/CodeDabaicai/article/details/111954842，如下所示

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title 
	FROM vendors ORDER BY vend_name;

SQL 还允许给表名起别名，可以缩短SQL语句，允许在单条SELECT语句中多次使用相同的表

#同第15章最后的代码示例,三个表分别起别名
SELECT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi
	WHERE c.cust_id = o.cust.id
		AND oi.order_num = o.order_num
		AND prod_id = 'TNT2';

• 自联结

  某物品ID为DTNTR存在问题，需要检查该物品所属的供应商的其他物品是否也存在问题。首先要查到该ID所属的供应商是谁，之后根据供应商查所有的供应商物品。

  #使用子查询
  SELECT prod_id,prod_name FROM products 
  	WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
  

  #使用联结
  SELECT prod_id,prod_name FROM products AS p1,products AS p2
  	WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
  

  此查询的两个表实际是两个相同的表，使用两个别名是为了避免二义性。很多时候处理联结比子查询要快的多。

• 自然联结

  标准的联结返回所有的数据，甚至有的列出现多次。自然联结将每个列有且只返回一次，使用通配符**SELECT **

  SELCR c.*,o.order_num,o.order_data,oi.prod_id,oi.quantity,oi.item_price
  	FROM customers AS c,orders AS o,orderitems AS oi
  		WHERE c.cust_id = o.cust.id
  			AND oi.order_num = o.order_num
  			AND prod_id = 'TNT2';
  

• 外部联结

  很多时候联结需要包含没有关联行的那些行，这种类型的联结就是外部联结

#使用简单外部联结时
SELECT customers.cust_id,orders.order_num
	FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;
	
#使用外部联结
SELECT customers.cust_id,orders.order_num
	FROM customers LEFT OUTER JOIN orders ON cusLEFTtomers.cust_id = orders.cust_id;

关键字OUTRE JOIN用来指定联结的类型，且必须使用RIGHT或LEFT来指定包括其所有行的表，从FROM的左边/右边的表中选择所有行。

• 带聚集函数的联结

  #要检索所有客户及每个客户所下的订单数
  SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_order
  	FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id
  		GROUP BY customers.cust_id;
  

  使用INNNER JOIN将customer和orders表互相关联，GROUP BY子句将客户分组数据，COUNT(orders.order_num)对每个客户的订单进行计数求和并返回

  SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_order
  	FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
  		GROUP BY customers.cust_id;
  

  上边的示例使用左外部联结来包含所有的用户，即使是没有下过任何订单的客户也会被显示（订单默认值0）