Mysql中的数据分组和子查询 _Mysql

文章插图
MySQL中的数据分组和子查询
一、数据分组
1、创建分组
分组是在SELECT语句的GROUP BY子句中建立的。
示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id
上述语句首先根据book_id进行分组，把所有book_id相同的条目放在一起，然后使用COUNT(*)统计每一组的数目。最有进行显示。
因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
注意：
1）GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2）GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
3）如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
4）GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
2、过滤分组
除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。可以通过 HAVING实现。HAVING非常类似于WHERE 。事实上，之前所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符。
示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id HAVING COUNT(*)>10
上述语句的最后增加了HAVING子句，它过滤COUNT(*) >=2（两个以上的订单）的那些分组。这里WHERE子句不起作用，因为这里需要的过滤是基于分组聚集值而不是特定行值的。
HAVING和WHERE的差别：
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
3、分组和排序的区别
虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是有很大区别的。
order by 是按表中某字段排列表中数据。group by 是按某些字段分类。
例如：
按年龄排序表中的记录
【Mysql中的数据分组和子查询】select * from users order by age
按年龄分类表中数据（就是求各个年龄的人数）
select age,count(*) as number1 from users group by age
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万
不要仅依赖GROUP BY排序数据。
4、SELECT子句的书写顺序和执行顺序
Select子句书写顺序：
Select … from tbl1, tbl2 where … group by … having ……order by (asc | desc )
当SELECT语句被DBMS执行时，其子句会按照固定的先后顺序执行：
（1）FROM 子句。
（2）WHERE 子句。
（3）GROUP BY 子句。
（4）HAVING 子句。
（5）SELECT 子句。
（6）ORDER BY 子句。
基本的工作原理：FROM子句先被执行，通过FROM子句获得一个虚拟表，然后通过WHERE子句从虚拟表中获取满足条件的记录，生成新的虚拟表。将新虚拟表中的记录通过GROUP BY子句分组后得到更新的虚拟表，而后HAVING子句在最新的虚拟表中筛选出满足条件的记录组成另外一个虚拟表中，SELECT子句会将指定的列提取出来组成更新的虚拟表，最后ORDER BY子句对其进行排序得出最终的虚拟表。通常这个最终的虚拟表被称为查询结果集。
二、子查询
1、利用子查询进行过滤
子查询（ subquery），即嵌套在其他查询中的查询。
现在假设一个订单存储的情境。orders表存储一行包含订单号、客户ID、订单日期。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID 。实际的客户信息存储在customers表中。
三个表的数据如下：
orders