MySQL基础入门（4）

分组数据

1.创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

输出： vend_id   num_prods
		1001		3
		1002		2
		1003		7
-- 可以看到供应商1001有3个产品，1002有2个，1003有7个。

注意：GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

2.过滤分组

HAVING子句，目前为止学过的WHERE子句都可以用HAVING代替。唯一的差别是WHERE过滤行，而HAVING过滤分组。

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;-- 过滤出两个订单以上的分组

HAVING和WHERE的差别：一个过滤分组，一个过滤行。还有一种理解：HAVING在数据分组后进行过滤，WHERE在数据分组前进行过滤。

所以，HAVING和WHERE可以同时出现，如下：

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id;
HAVING COUNT(*) >= 2;-- 过滤出价格大于10，且具有2个订单以上的供应商

3.分组和排序

不要忘记之前学习过得分组子句ORDER BY子句：一般在使用GROUP BY子句的时候也应该给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅仅依赖GROUP BY排序数据

SELECT order_num,SUM(item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(item_price) >= 50   -- 过滤出总价格大于50的订单
ORDER BY ordertotal;  -- 将订单价格按从小到大的顺序排序

4.SELECT子句顺序

SELECT       -- 查询需要操作的列
FROM         -- 从哪个表中进行操作
WHERE        -- 过滤数据
GROUP BY     -- 分组
HAVING	     -- 过滤分组
ORDER BY     -- 分组排序
LIMIT        -- 输出限制

使用子查询

查询：任何SQL语句都是查询，但此术语一般指SELECT语句。
1.利用子查询进行过滤
现有两条SELECT语句：

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT' --输出：10001  10008
-- AND --
SELECT cust_id
FROM orders
WHERE order_num IN (10001,10008);
-- 现在把第一个查询变为子查询组合这两个查询如下 --
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id = 'TNT');
-- 在此语句中，MySQL实际上执行了两个操作。
-- 首先它执行了SELECT order_num FROM orderitems WHERE prod_id = 'TNT'返回了两个单号10001,10008.
-- 然后这两个值以IN操作符要求的逗号分隔的格式给外部查询的WHERE子句。
-- 外部查询变成SELECT cust_id FROM orders WHERE order_num IN(10001,10008);
-- 可以看到，与前面硬编码WHERE子句所返回的值是一样的。

2.作为计算字段使用子查询,看一个示例：

SELECT cust_name,
	   cust_state,
	   (SELECT COUNT(*)
	   FROM orders
	   WHERE order.cust_id = customers.cust_id) AS orders
FROM coustomers
ORDER BY cust_name;

联结表

1.创建联结：规定要联结的所有表以及它们如何关联即可。

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

-- SELECT语句与前面所有语句一样指定所要检索的列，这是最大的区别就是所指定的两个列prod_name,prod_price在一个表中，而vend_name在另一个表中
-- 再来看FROM语句，与之前的不一样，这里列出了两个表，分别是vendors,products，它们就是WHERE子句联结的两个表的名字。
-- 正确联结后，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id.
-- 可以看到匹配两个列以vendors.vend_id 和 products.vend_id指定，因为如果只给出vend_id，MySQL不知道指的是哪一个（两个表中都有vend_id)

不要忘了WHERE子句，应该保障职能所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据，同理，应该保证WHERE子句的正确性，不正确的过滤条件将导致MySQL返回不正确的数据。

2.内部联结

之前所使用的联结方式称为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结。

使用哪种语法呢？ANSI SQL规范首选INNER JOIN语法，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。下列SELECT语句返回与前面例子完全相同的数据：

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

-- 这里，两个表支架你的关系是FROM子句的组成部分，以INNER JOIN指定。
-- 在使用这种语法时，联结条件能用特定的ON子句而不是WHERE子句给出，传递给ON的实际条件与传递给WHERE的相同。

3.联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制，创建的基本规则也相同。首先列出所有表，然后定义表之间的关系即可。

性能考虑：子查询并不总是执行复杂的SELECT操作的最有效的方法，下面是使用联结的相同查询转化，用的是上述例子：

SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
				  FROM orders
				  WHERE order_num IN (SELECT order_num
									  FROM orderitems
								   	  WHERE prod_id = 'TNT'));
-- 转化成联结查询如下 --

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 = 'TNT';
-- 这个查询中返回数据需要使用3个表。但这里我们没有在嵌套子查询中使用它们，而是使用了两个联结。
-- 这里有3个WHERE条件，前两个用来关联联结的表，后一个过滤产品TNT的数据。

创建高级联结

1.使用表别名，先看一个例子：

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 = 'TNT';
	
-- 在此查询中，表别名只用于WHERE子句，但是，表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分

注意：表别名只在查询执行中使用。与列别名不一样，表别名不返回客户机。

2.使用不同类型的联结

●自联结，下面举一个例子：
假如你发现某物品（ID为TNT）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为TNT的物品的供应商，然后找出这个供应商生产的其他物品。

SELECT prod_id,prod_name 
FROM products
WHERE vend_id = (SELECT vend_id
				 FROM products
				 WHERE prod_id = 'TNT');
-- 现在来看使用联结的相同查询
SELECT prod_id,prod_name 
FROM products AS p1,products AS p2
WHERE p1.vend_id = P2.vend_id
AND p2.prod_id = 'TNT';
-- 此查询中需要的两个表实际上是相同的表，因此products表在WHERE子句中出现了两次。
-- 虽然是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products中的哪个表
-- 为了解决这个问题，使用了这里的表别名，给products表两个别名，WHERE（通过匹配p1和p2中的vend_id）
-- 首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需要的数据

●外部联结：许多联结将一个表中的行与另一个表中的行想关联。但有时候会需要包含没有关联行的那些行。联结包含了那些在相关表中没有关联的行，这种类型的联结称为外部联结。

-- 与外部联结语法类似
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id;
-- 使用的是OUTER JOIN来进行指定联结类型，而不是WHERE。

与内部联结不同的是：外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，LEFT反之）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers）中选择所有行。

左外部联结和右外部联结之间的唯一差别是所关联的表的顺序不同，所以，左外部联结可以通过颠倒FROM或WHERE子句中表的顺序转换成右外部联结，两种类型的外部联结可以互相转换使用，而究竟使用哪种是根据方便而定。

●使用带聚集函数的联结,看一个例子

-- 如果要检索出所有客户以及每个客户所下的订单数，下面使用了COUNT()函数
SELECT customers.cust_name,
	   customers.cust_id,
	   COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
-- 使用INNER JOIN将customers和orders表相互关联，GROUP BY按客户分组数据，函数调用COUNT对每个客户的订单计数并返回
-- 聚集函数也可以与其他联结一起使用，下面是使用外部联结的例子
SELECT customers.cust_name,
	   customers.cust_id,
	   COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
-- 这个例子使用左外部联结来包含所有客户，甚至包含了那些没有下任何订单的客户，而在上面的内部联结中则不会返回客户订单为0的结果。

3.使用联结和联结条件
●注意所使用的联结类型。一般使用内部联结，但使用外部联结也是有效的
●保证使用正确的联结条件，否则将返回不正确的数据
●应该总是提供联结条件。否则是得出笛卡尔积
●在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结，这样排除故障更为简单。