MySQL按年/月/周/日/小时分组查询、排序、limit及判空用法实例

目录
  • 一、按年/月/周/日/小时分组查询（日期时间类型为date或者datetime）
  • 二、MySQL排序
    • 1.排序规则
    • 2.单列排序
  • 三.limit 详细用法
    • 1、用于强制返回指定的记录行数
    • 2、分页
  • 四.ifnull()函数的使用
    • 1、ifnull()的语法
    • 2、ifnull()的使用
  • 总结

一、按年/月/周/日/小时分组查询（日期时间类型为date或者datetime）

在DATE_FORMAT(create_time,'%Y%m%d')中设置需要分组的类型和格式，如下：

'%Y%m%d'表示：20221122

'%Y-%m-%d'表示：2022-11-22

可根据自己的需要设置，年月日都是如此

如下图user表

id name age birthday gender create_time
1 张三 20 2000-01-01 男 2022-11-22 11:11:11
2 李四 21 2001-02-02 男 2022-11-19 00:00:00
3 王五 23 1999-05-06 女 2022-11-20 23:59:59

按日分组查询并排序

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') days,COUNT(id)
FROM user
WHERE create_time > "2022-11-11 00:00:00"
  AND create_time < "2022-12-01 00:00:00"
GROUP BY days
ORDER BY days DESC;

按周分组查询

SELECT DATE_FORMAT(create_time,'%Y-%u') weeks,COUNT(id) FROM user GROUP BY weeks;

按月分组查询

SELECT DATE_FORMAT(create_time,'%Y-%m') months,COUNT(id) FROM user GROUP BY months

按年分组查询

SELECT DATE_FORMAT(create_time,'%Y') months,COUNT(id) FROM user GROUP BY years

按小时分组查询

SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H') hours,COUNT(id)
FROM user
WHERE create_time > "2022-11-11 00:00:00"
  AND create_time < "2022-12-01 00:00:00"
GROUP BY hours;

DATE_FORMAT()需要传入一个特定的占位符，mysql常用的占位符可参考下表:

占位符 说明
%Y 年(4位)
%y 年(2位)
%M 月（英文名，如January）
%m 月（数字，如01）
%D 日（英文名，如1st）
%d 日（数字，如01）
%e 日（数字，如1）
%U 一年中的第几周，从0开始 ，周日是第一天
%u 一年中的第几周，从0开始，周一是第一天
%H 时，24小时制，例如15
%h 时，12小时制，例如01
%i 分
%s 秒

二、MySQL排序

1.排序规则

使用ORDER BY 字句排序,在其后面加所需字段

  • ASC(ascend): 升序
  • DESC(descend): 降序

ORDER BY 字句在SELECT语句的结尾

注意:

数据库中默认按照先后添加顺序存储数据,在查询时,也按照添加顺序遍历显示结果.因此当我们需要一些特定的数据排列时就要使用到排序

2.单列排序

根据上述用户user表查询：

#练习1：根据年龄降序（从大到小）排序

SELECT * FROM user ORDER BY age DESC;

#练习2：根据出生日期升序（从小到大）排序

SELECT * FROM user ORDER BY birthday ASC;

注意:

如果在ORDER BY 后面没有显示指明排序方式的话,则默认按照升序排列排序，有WHERE 和 ORDER BY 同时出现的地方,一定要把WHERE放在FRON语句后面,ORDER BY 往后放

SELECT *
FROM user
WHERE age > 20
ORDER BY age DESC;

三.limit 详细用法

1、用于强制返回指定的记录行数

在查询中，经常要返回前几条或者中间某几行数据时，用到limit

语法如下：

select * from table_name limit [offset，] rows

参数说明：

  • offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
  • rows：返回具体行数。

总结：如果limit后面是一个参数，就是检索前多少行。如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。
举例：

select * from table_name limit 10;//检索前10行记录
select * from table_name limit 5 ，10;//从第6行开始，检索10行记录，即：检索记录行 6-15

2、分页

客户端通过传递page(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：
比如：

  • 查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;
  • 查询第10条到第20条的数据的sql是：select * from table limit 10,10; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;
  • 查询第20条到第30条的数据的sql是：select * from table limit 20,10; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

总结：

通过上面的分析，可以得出符合我们需求的分页sql格式是：

select * from table limit (page-1)*pageSize,pageSize;

四.ifnull()函数的使用

1、ifnull()的语法

ifnull(a,b)函数解释：

  • 如果value1不是空，结果返回a
  • 如果value1是空，结果返回b

2、ifnull()的使用

如下表：分数表score

id Chinese English Physics
1 99 98 null
2 88 99 null

1、 在统计数据时如果没有值返回，给结果置为0

举例：统计物理科目分数总和时，如果该列为null，返回0，如果不使用ifnull(),正常返回值为null

SELECT ifnull(SUM(Physics),0) FROM score;

2、在查询数据需要计算遇到null值时

举例：查询id为1的学生的语文和物理的总分

错误写法（在数据库没有指定为空时的默认值时）

SELECT Chinese+Physics FROM score;

数据为null不能参与运算，否则会直接报错。

正确写法

SELECT ifnull(Chinese,0)+ifnull(Physics,0) FROM score;

还有就是使用SUM()等函数，可以忽略计算中的空值。

SELECT SUM(Chinese，Physics) FROM score;

总结

到此这篇关于MySQL按年/月/周/日/小时分组查询、排序、limit及判空用法的文章就介绍到这了,更多相关MySQL分组查询、排序、limit及判空内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mysql按照时间分组查询的语句

    mysql按照时间分组查询的语句

    mysql 按年.月.周.日分组查询 1.按照年份分组查询 SELECT DATE_FORMAT(t.bill_time,'%Y') month_time,sum(t.pay_price) total FROM f_bill t GROUP BY month_time; 查询结果 2.按照月份分组查询 SELECT DATE_FORMAT(t.bill_time,'%Y-%m') month_time,sum(t.pay_price) total FROM f_bill t GROUP BY m

  • MySQL 分组查询的优化方法

    MySQL 在处理 GROUP BY 和 DISTINCT 查询的方式在大多数情况下类似,事实上,在优化过程中有时候会把在这两种方式中转换.两类查询都能够从索引中受益,通常,这也是优化这两种查询最为重要的方式. 在无法使用索引时,MySQL 对于 GROUP BY 查询有两种策略:使用临时表或者 filesort 执行分组.对于给定的查询,两种方式都没法更高效.我们可以通过配置 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来指定优化器选择其中一个方式. 通常,对查询表的i

  • MySQL学习之分组查询的用法详解

    MySQL学习之分组查询的用法详解

    目录 为什么要分组 逐级分组 逐级分组对 SELECT 子句的要求 对分组结果集再次做汇总计算 GROUP_CONCAT 函数 GROUP BY 子句的执行顺序 该章节来开始学习分组查询,上一章节我们学习了聚合函数,默认统计的是全表范围内的数据,配合上 WHERE 就能够缩小统计的范围了.但是这并不能满足我们的要求,比如说我们按照之前的数据表查询每个部门的平均底薪是多少?这样的记录就需要针对部门编号进行分组了.根据分组的情况统计分组内的最大值.最小值.平均值等等.如此就能够满足刚刚提到的 “查询

  • MySQL连表查询分组去重的实现示例

    目录 业务逻辑 数据表结构 查询逻辑 SQL脚本 脚本说明 业务逻辑 通过多种渠道将小程序的活动页链接发布出去,比如通过多多种短信附带链接( channel 就记为 sms1,sms2,sms3 ),或者海报上面贴微信小程序的二维码( channel 记为 qrcode1,qrcode2,qrcode3 ),线下会员通过扫描二维码也能进入小程序指定的活动页,亦或者是通过其他会员分享的小程序链接也可以进入小程序( channel 记为 share).这些不同的进入方式在我这篇文章统称为不同的渠道,

  • Mysql区间分组查询的实现方式

    Mysql区间分组查询的实现方式

    目录 Mysql区间分组查询 场景 第一想法 一番百度之后 另一种解决办法 按区间分组查询.获取各区间的总数 数据表如下 需求 Mysql区间分组查询 场景 一张用户表(user),有用户id(id).余额(balance)等字段,要求展示 余额在某个区间内的人数 ​ 区间有0-1万,1-10万,10-50万,50-100万,100万+, 下面是模拟数据: 用户id        余额 1            100     2            200     3            

  • 详解MySQL中的分组查询与连接查询语句

    分组查询 group by group by 属性名 [having 条件表达式][ with rollup] "属性名 "指按照该字段值进行分组:"having 条件表达式 "用来限制分组后的显示,满足条件的结果将被显示:with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和. 1)单独使用 group by 单独使用,查询结果只显示一个分组的一条记录. 实例: select * from employee group by sex;

  • MySQL 子查询和分组查询

    概述 子查询是SQL查询中的重要一块,是我们基于多表之间进行数据聚合和判断的一种手段,使得我们的处理复杂数据更加的便捷,这一节我们主要来了解一下子查询. 先做一下数据准备,这边建立三张表:班级.学生.毕业成绩表,用于后面的操作: drop database if exists `Helenlyn_Class`; create database `Helenlyn_Class`; /*班级表*/ DROP TABLE IF EXISTS `classes`; CREATE TABLE `class

  • MySQL按年/月/周/日/小时分组查询、排序、limit及判空用法实例

    目录 一.按年/月/周/日/小时分组查询(日期时间类型为date或者datetime) 二.MySQL排序 1.排序规则 2.单列排序 三.limit 详细用法 1.用于强制返回指定的记录行数 2.分页 四.ifnull()函数的使用 1.ifnull()的语法 2.ifnull()的使用 总结 一.按年/月/周/日/小时分组查询(日期时间类型为date或者datetime) 在DATE_FORMAT(create_time,'%Y%m%d')中设置需要分组的类型和格式,如下: '%Y%m%d'

  • oracle实现按天，周，月，季度，年查询排序方法

    oracle按天,周,月,季度,年查询排序 天--to_char(t.start_time,'YYYY-MM-DD') 周 --to_char(t.start_time,'YYYY'),to_char(t.start_time,'IW') 月度--to_char(t.start_time,'YYYY-MM') 季度--to_char(t.start_time,'YYYY'),to_char(t.start_time,'Q') 年度--to_char(t.start_time,'YYYY') 按天

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • PHP实现针对日期,月数,天数,周数,小时,分,秒等的加减运算示例【基于strtotime】

    本文实例讲述了PHP实现针对日期,月数,天数,周数,小时,分,秒等的加减运算方法.分享给大家供大家参考,具体如下: 其实就是strtotime这个内置函数 //PHP 日期 加减 周 date("Y-m-d",strtotime("2013-11-12 +1 week")) //PHP 日期 加减 天数 date("Y-m-d",strtotime("2013-11-12 12:12:12 +1 day")) //PHP 日期

  • js 计算月/周的第一天和最后一天代码

    因为项目开发中遇到需要向后台传本周的开始和结束时间,以及上一周的起止时间,就琢磨了半天,总算写出来一套,写篇文章是为了方便自己记忆,也是分享给需要的人,水平有限,写的不好请见谅: 1.getDateStr3函数是为了把时间对象转变为yy-mm-dd的字符串,方便传值: 2.getWeekStartAndEnd函数是获取周的起止时间,并且用getDateStr3转换成字符串放到数组中,其中参数0代表当前周,-1代表前一周,-2代表上上周,以此类推,反过来也可以1代表下一周: 3.getMonthS

  • 解决MySQL时区日期时差8个小时的问题

    目录 场景: 解决: mysql查出时间相差14小时 问题重现 问题排查 场景: 我们在mysql客户端查询 now() 系统当前日期时间,会发现得到的结果比系统实际日期时间慢8个小时左右,这是由于不同的时区导致的 解决: 1.在 mysql 的服务端的 my.conf 文件中 [mysqld] 节点下设置时区参数 default-time-zone=Asia/Shanghai 2.如果 mysql 的版本是 5.7 配置如下参数 default-time-zone = '+8:00' 3.如果

  • 清明节是几月几日 清明节的由来

    2012年是4月5日 清明节是根据二下四节气来推算的.所以不一定每年的清明节都是4月4日.清明是二十四节气之一,清明节就在清明那一天,每年的清明大致都在阳历的4月4日至4月6日那天.节气的划分是根据太阳跟赤道的角度来定的,因此农历也没有一定的日子. 2010的清明节是4月5日 2010年清明节放假时间:4月4日至5日放假,共3天.其中,4月3日(星期六),4月4日(星期日)照常公休.4月5日(星期一,农历清明当日)为法定节假日. 清明,农历二十四节气之一.中国传统的清明节大约始于周代,距今已有二

  • 植树节是几月几日，植树节的的由来和意义整理

    中国: 每年3月12日为植树节 ; 朝鲜: 每年3月2日为植树节; 印度: 每年7月第一周开展全国植树节活动; 斯里兰卡:9月17日为植树节; 泰国:国庆节定为植树节; 菲律宾: 每年9月第二个星期六为植树节; 约旦:1月15日是植树节; 巴勒斯坦:1月6日是植树节; 埃及: 每年9月至11月为植树节; 巴西: 每年9月21日为植树节; 墨西哥: 每年6至9月的雨季里开展植树节活动; 哥伦比亚: 每年10月12日为植树节; 萨尔瓦多: 每年的6月21日举行; 法国: 每年3月为法定的绿化月,3月

随机推荐