java8 多个字段分组_MySQL学习总结（五）——分组数据，使用子查询，联结表以及创建高级联结...

一.分组数据

1. 数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。如需要返回每个供应商提供的产品数目，或者返回只提供单项产品的供应商所提供的产品，或者返回提供10个以上产品的供应商。

2. 创建分组

分组是在select 语句的group by 子句中建立。

Select vend_id, count(*) as num_prods

From products

Group by vend_id;

Group by 子句指示mysql分组数据，然后对每个组而不是整个结果集进行聚集。

注意：

• Group by 子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• Group by 子句中列出的每个列都必须是检索列或者有效的表达式（但不能是聚集函数）。如果在select 中使用表达式，则必须在group by 子句中指定同样的表达式。不能使用别名。
• 除聚集计算语句外，select 语句中每个列都必须在group by 子句中给出。
• 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。
• Group by 子句必须出现在where 子句之后，order by 子句之前。

使用rollup 使用with rollup 关键字，可以得到每个分组以及每个分组汇总级别的值。

Select vend_id, count(*) as num_prods

From products

Group by vend_id with rollup;

3. 过滤分组

除了能用group by 分组数据外，mysql还允许过滤分组，规定包含哪些分组，排除哪些分组。Mysql为此目的提供了另外的子句，那就是having子句。Having 非常类似where。目前为止所学的所有类型的where子句都可以用having 来代替。唯一的差别是where 过滤行，而having过滤分组。

在一条语句中可以同时使用where 和having子句的需要。如需要列出具有2个（含）以上，价格为10（含）以上的产品的供应商。

Select vend_id, count(*) as num_prods

From products

Where prod_price >=10

Group by vend_id

Having count(*) >=2;

先用where子句过滤所有prod_price 至少为10的行。然后按vend_id 分组数据，having子句过滤计数为2或2以上的组。

• 注意：

Having和where的差别，where在数据分组前进行过滤，having在数据分组后进行过滤。

4. 分组和排序

虽然group by 和order by 经常完成相同的工作，它们之间有一些差别。

• 注意：不要忘记order by 一般在使用group by 子句时，应该也给出order by 子句。这是保证数据正确排序的唯一方法。

为按总计订单价格排序输出，需要添加order by 子句

Select order_num, sum(quantity*item_price) as ordertotal

From orderitems

Group by order_num

Having sum（quantity*item_price）>=50

Order by ordertotal;

在这个列子中，group by 子句用来按订单号（order_num列）分组数据，以便sum(*)函数能够返回总计订单价格。Having 子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用order by 子句排序输出。

5.select 子句顺序

二.使用子查询

1. 子查询

查询(query) :任何SQL语句都是查询。但术语一般指SELECT语句

SQL还允许创建子查询，即嵌套在其他查询中的查询。

2. 利用子查询进行过滤

现有三个表：orders表包含订单号，客户ID,订单日期的每个订单。Orderitems表包含各订单的物品储存。Customers表包含实际的客户信息。

（1） 检索包含物品TNT2的所有订单的编号

（2） 检索具有前一步骤列出的订单编号的所有客户的ID

（3） 检索前一步骤返回的所有客户ID的客户信息

三个问题可是说上一个问题的结果可以作为下一个问题的子查询，可以使用select 语句的where子句

(1) Select order_num

From orderitems

Where prod_id = ‘TNT2’;

(2) Select cust_id

From orders

Where order_num in (select order_num

From orderitems

Where prod_id = ‘TNT2’);

(3) 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 = ‘TNT2’);

• 注意：

列必须匹配 在where子句中使用子查询，应该保证select 语句具有与where子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

3. 作为计算字段使用子查询

使用子查询的另一方法是创建计算字段，假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID储存在orders表中

（1） 从customers表中检索客户列表

（2） 对于检索出的每个客户，统计其在orders表中的订单数目

这里即可以通过创建count(*)子查询。

Select cust_name,

Cust_state,

(select count(*)

From orders

Where orders.cust_id=customers.cust_id) as orders

From customers

Order by cust_name;

这里的orders.cust_id=customers.cust_id 这种类型的查询称为相关子查询。

任何时候只要列名可能有多义性，就必须使用这种语法。

三.联结表

1. 联结

联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

2. 关系表

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。如果由同一供应商生产的多个物品，那么在何处存储供应商信息（如：供应商名，地址，联系方法等）。这时候就可以使用关系表的设计。

关系表的设计：就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联。

在这个例子中可以建立两个表，一个存储供应商信息，另一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键，可以是供应商ID或任何其他唯一值。

Products表只存储产品信息，它除了存储供应商ID外不存储其他供应商的信息。Vendors表的主键又叫做products的外键。

外键：外键为某个表中一列，它包含另一个表的主键值，定义了两个表之间的关系。

关联表的好处：

• 供应商信息不重复，从而不浪费时间和空间
• 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动
• 由于数据无重复，显然数据是一致的，这使得处理数据更加简单

可伸缩性：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好。

3. 创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

Select vend_name , prod_name, prod_price

From vendors, products

Where vendors.vend_id = products.vend_id

Order by vend_name,prod_name;

• 注意：不要忘了where 子句

应该保证所有的联结都有where子句。

4. 内部联结

内部联结：目前为止所用的联结称为等值联结，它基于两个表之间的相等测试。

Select vend_name,prod_name,prod_price

From vendors inner join products

On vendors.vend_id =products.vend_id;

此语句中的select 与前面的select 语句相同，但from子句不同。两个表的关系是from 子句的组成部分，以inner join 指定。在使用这种语法时联结条件用特定的on子句而不是where 子句给出。传递给on的实际条件与传递给where 的相同。

5. 联结多个表

SQL对一条select 语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。

Select prod_name,vend_name,prod_price,quantity

From orderitems,products,vendors

Where products.vend_id=vendors.vend_id

And orderitems.prod_id=products.prod_id

And order_num = 20005;

性能考虑：MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的。应该仔细，不要联结不必要的表，联结的表越多，性能下降越厉害。

• 注意：一些子查询避免总是执行多个select操作可以使用联结的相同查询。

四.创建高级联结

1. 使用表别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。

原因:

• 缩短SQL语句
• 允许在单条select 语句中多次使用相同的表

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’

2. 自联结

使用表别名的主要原因之一是能在单条select 语句中不止一次引用相同的表。

如假如发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品供应商，然后找到这个供应商生产的其他物品。

Select p1.prod_id, p1.prod_name

From products as p1,products as p2

Where p1.vend_id=p2.vend_id

And p2.prod_id= ‘DTNTR’;

这里使用了表别名，products的第一次出现别名为p1,第二次出现的别名为p2。现在可以将这些别名用作表名。

注意：用自联结而不是子查询

用自联结的查询速度比子查询的速度要快很多。

3. 自然联结

自然联结排除多次出现，是每个列只返回一次。我们建立的每个内部联结都是自然联结，很可能用不到不是自然联结的内部联结。

4. 外部联结

外部联结：联结包含了那些在相关表中没有关联行的行。

如为了检索所有客户，包括那些没有订单的客户，可如下进行：

Select customers.cust_id,orders.order_num

From customers LEFT OUTER JOIN orders

On customers.cust_id = orders.cust_id;

这里使用了关键字outer join 来指定联结的类型。但与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JION 语法时必须使用right 或left 关键字指定包括其所有行的表（right 指的是outer join 右边的表，而left 指的是OUTER JOIN 左边的表）。

外部联结类型：左外部联结和右外部联结

5. 使用带聚集函数的联结

聚集函数也可以与联结一起使用

如要检索所有客户及每个客户所下的订单数

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;

此select 语句中使用inner join 将customers 和orders表互相关联。GROUP BY 子句按客户分组数据。函数调用count(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。