mysql group by 实战-工具盒子

mysql group by 实战

实际操作命令演示：select+from+where+group by+聚合函数（统计函数）结合使用情况

在利用select语句查询数据信息，结合group by子句可以实现分组查询，并且还必须配合聚合函数对分组查询的数据做相应处理；

数据库服务中常用的聚合函数（统计函数）：

| 序号 | 函数信息 | 解释说明 | |----|----------------|--------------------------------| | 01 | count() | 表示求指定列的总记录数 | | 02 | sum() | 此函数表示求指定列的和 | | 03 | avg() | 此函数表示对数值信息进行求平均值 | | 04 | min() | 此函数表示对数值信息进行取最小值 | | 05 | max() | 此函数表示对数值信息进行取最大值 | | 06 | group_concat() | 此函数表示输出信息无法匹配分组和聚合函数时，进行拼接整合显示 |

利用group by进行分组查询的执行逻辑分析：

根据查询语句信息，取出表中关注的列字段信息；
根据查询分组信息，将特定列字段信息进行排序，从而将分组的一致信息整合在一起（形成结果集）；
根据分组合并信息，结合使用的聚合函数，进行数值信息运算或统计（生成最终结果）；
根据分组聚合要求，分组信息输出时必须和分组信息一一对应，但特殊列无法一一对应输出时，可使用group_concat()拼接输出

测试，导入数据：

数据在这里

mysql: https://url69.ctfile.com/d/253469-56755836-403176?p=2206 (访问密码: 2206)

/usr/local/mysql/bin/mysql -uroot -p123456 < world.sql

实际操作命令演示：获取分组数据信息进行聚合函数处理实践：

# 查询统计每个国家的人口总数

我们先看看表结构

首先

 查询统计每个国家的人口总数

select * from world.city  goupby  CountryCode ; #现根据 国家分组
#然后早出 人口总数

select CountryCode，  sum(population) from world.city group by CountryCode;

-- 根据国家信息分组聚合，在将分组后所有城市的人口数量进行sum求和运算，实现国家信息对应人口总数的1对1关系

查询统计每个省份的城市个数

# 查询统计每个省份的城市个数

select  district , count(district) from  city  group by district ;


中国的代码为  chn 

select  district , count(district) from  city where countrycode='chn'   group by district ;

# 查询统计每个省份的城市个数，以及城市名称信息（经常面试题考到）

select district , count(district),name from city where countrycode='chn' group by district ;

会出现报错

-- 由于数据库sql_mode配置了only_full_group_by，由于输出的name信息不能和district信息实现1对1关系，因此报错

解决方案：

-- 利用group_concat()就可以实现没有出现在分组和聚合函数中的字段，采取拼接整合方式显示，满足分组1对1关系

select district , count(district),group_concat(name) from city where countrycode='chn' group by district ;

实际操作命令演示：select+from+where+group by+聚合函数+having

在利用select语句查询数据信息，结合分组和聚合函数处理之后，可以将输出的信息再进行过滤处理（having）；

having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤。

实际操作命令演示：对分组聚合后数据进行过滤处理

# 查询统计每个国家的人口总数，只显示人口数量超过1个亿的信息

select sum(population) from city group by CountryCode having sum(population) > 100000000;

实际操作命令演示：select+from+where+group by+聚合函数+having+order by

在利用select语句查询数据信息，结合分组和聚合函数处理之后，并且再次经过筛选的数据，按照一定数值规律排序显示信息

实际操作命令演示：

# 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数排序显示

select countrycode, sum(population) from city group by CountryCode having sum(population) > 50000000 order by sum(population);

-- 实现了人口数量从小到大排序（升序/正序）

select countrycode, sum(population) from city group by CountryCode having sum(population) > 50000000 order by sum(population) desc;

-- 实现了人口数量从大到小排序（降序/逆序）

实际操作命令演示：select+from+where+group by+聚合函数+having+order by+limit

在利用select语句查询数据信息，结合分组和聚合函数处理之后经过筛选的数据，按照一定数值规律排序显示信息，并限制输出内容行数

实际操作命令演示：

# 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数从大到小排序，只显示前三名

mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;
或者
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 0,3;
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 0;

# 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数从大到小排序，只显示三~五名
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 2,3;
或者
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 2;
-- 跳过前2名，显示后面的三名数据信息