MYSQL的高级查询:Group By,Order by, having子句

1. Group by就是分组的意思,根据某个字段进行分组。Group By 与 Count()函数 

基本语法:group by 字段名;

使用前面的学生表,我们来进行练习

举例:根据性别分组


根据上面显示,我们使用了分组语句,结果出现了数据丢失的情况。分组之后男和女只显示一条数据。

在SQL中分组是为了统计数据,SQL提供了一系列统计函数供我们使用。

1)      Count(): 统计分组后的记录数,每一组有多少记录

2)      Max(): 统计每组中最大值

3)      Min(): 统计最小值

4)      Avg(): 统计平均值。

5)      Sum(): 统计和

举例:分组统计:身高高矮,年龄平均和总年龄


       上面表显示男和女各有2个,男里面最高175,女里面最高165,还有后面的平均年龄和最矮身高。所以,如果你在SQL语句中使用了group by,但是没有用到上面函数的一个或者几个,那么分组就没有意义,就想我们文章开头查询的结果。

Count函数 

Count函数里面可以使用两种参数:*代表统计所有的记录,使用字段名 代表统计对应字段(Null不统计)。

举例: 下面我们先准备一个字段为null,例如把其中一个学生age设置值为null。


然后分别用count(*)和count(age)分组查询进行对比。


由于我们之前把lily这个女性的age设置成null,所以count(age)在女性分组里的统计数显示为1.

 

分组会自动根据分组字段排序,默认升序。

基本语法:group by 字段[ase 或者 desc];-- 效果是对分组的结果合并后进行排序

 

默认female排序在前,升序这里当然是安装字母顺序,desc表示降序,效果就是男性在前,女性在后。

 

多字段分组 

为了演示效果,我们给当前学生表新增一个班级字段,效果如下。

 

下面先按照班级,然后按照性别进行分组


      上面可以看到java01班级有一个女,有一个男,java02班有一个女,也有一个男。这种情况可以实现,每个班级有多少个女和多少个男的统计。那么可不可以统计,每个班级女的和男的叫什么。

 

这里有一个函数,叫group_concat(字段),效果就像上面那样

having子句和order by字句,和where字句一样,是用来进行条件判断的。Where是针对磁盘数据进行判断,进入到内存之后,会进行分组操作,分组结果就需要Having来处理。有这么一个结论,having能做where能做的几乎所有事情,但是where却不能做having能做的事情。Order by主要就是用来排序操作。

分组统计的结果或者说统计函数 having能够使用

举例:求出所有班级人数大于等于2的学生人数

       为了方便举例,我们需要对前面的my_student表进行插入和修改数据,这里推荐用SQLyog图形化工具来操作,比命令行不容易出错,具体效果如下。


       上面有三个班级的学生信息。下面我们来看如何用having子句来查询人数大于等于2的学生人数,事件上这个结果应该是java02和java01,因为上面表java02班才有3个学生,java01有两个学生。


先是通过班级分组,然后对分组结果进行having字句判断。

 

Having能够使用字段别名,where不能

       因为where是从磁盘取数据,而名字只可能是字段名,别名是在字段进入到内存之后才会产生。下面就是利用字段别名举例,total就是count(*)的别名。


Where和having总结,能够使用where就使用where,因为会减少在内存里出错的概率,查询效率也更高。

 

Orderby


Order by就是排序操作,根据某个字段进行升序或者降序排序,依赖校对集。

基本语法:order by 字段名[asc 或者 desc];-- 默认是asc升序

 

我们先来看看分组和排序的区别


第一个查询是分组,三个班级,所以有三个分组显示;第二个查询就是全量的排序显示,可以看到java01排最前,java02在中间,java03在最后。

 

多字段排序

排序可以进行多字段排序,先根据某个字段进行排序,然后排序好的内容,将按照某个数据进行再次排序。

举例:先根据班级排序,然后根据身高排序。


       上面是先班级排序,然后班级内部再根据身高排序。排在在web的界面经常看得到,例如根据商品名称排序,根据商品价格排序,根据时间排序等。

3. Limit子句是一种限制结果的语句,通常是限制数量。

 只用来限制长度(数据量):limit数据量;

 举例:查询学生表中前两条记录

我们本来有6条学生记录,使用limit 2就只查询前面两条记录。

 

限制起始位置,限制数量:limit起始位置,长度;

举例:还是和上面一样,查询前面两个学生记录,看看有什么区别。

 

      上面第二种写法,起始位置是从0开始的,所以结果是一样的。下面我们来查询从第三个记录开始,长度限制为3.


       由于起始位置是从0开始,所以第三条记录的起始位置就是2,长度为3,就是查询结果限制为3条。这种场景,在实际项目中,最常见的使用场景就是列表分页显示。例如web系统有一个列表,默认一页显示20条记录,第21条就采取分页方式,这种方式,主要能为用户节省查询时间,提高服务器的响应效率,减少资源浪费。

      这里简单提下,前端页面分页在数据库层面查询的实现是这样的,用户只需要点击页码,例如有1,2,3,4这样的页面菜单,而对于服务器来讲,会根据用户选择的页码来获取不同的数据,这个limit的语法是这样的:limit offset,length;

Length:每页显示的数量,一般基本不变,一般页面有20,40,80等

0fferset:计算公式是这样的:offerset =(页码-1)*每页显示数量。

        这里再举例一个limit的例子,笔试中经常让写,成绩最高的前5名,或者工资最多的前5名。这里我们来查询出学生表里面身高最高的前3名。

 

      

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页