一、按年/月/周/日/小時(shí)分組查詢（日期時(shí)間類型為date或者datetime）

在DATE_FORMAT(create_time,'%Y%m%d')中設(shè)置需要分組的類型和格式，如下：

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

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

可根據(jù)自己的需要設(shè)置，年月日都是如此

 如下圖user表

按日分組查詢并排序

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

按小時(shí)分組查詢

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()需要傳入一個(gè)特定的占位符，mysql常用的占位符可參考下表:

二、MySQL排序

1.排序規(guī)則

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

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

ORDER BY 字句在SELECT語(yǔ)句的結(jié)尾

注意:

數(shù)據(jù)庫(kù)中默認(rèn)按照先后添加順序存儲(chǔ)數(shù)據(jù),在查詢時(shí),也按照添加順序遍歷顯示結(jié)果.因此當(dāng)我們需要一些特定的數(shù)據(jù)排列時(shí)就要使用到排序

2.單列排序

根據(jù)上述用戶user表查詢：

#練習(xí)1：根據(jù)年齡降序（從大到?。┡判?/p>

SELECT * FROM user ORDER BY age DESC;

#練習(xí)2：根據(jù)出生日期升序（從小到大）排序

SELECT * FROM user ORDER BY birthday ASC;

注意:

如果在ORDER BY 后面沒(méi)有顯示指明排序方式的話,則默認(rèn)按照升序排列排序，有WHERE 和 ORDER BY 同時(shí)出現(xiàn)的地方,一定要把WHERE放在FRON語(yǔ)句后面,ORDER BY 往后放

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

三.limit 詳細(xì)用法

1、用于強(qiáng)制返回指定的記錄行數(shù)

在查詢中，經(jīng)常要返回前幾條或者中間某幾行數(shù)據(jù)時(shí)，用到limit

語(yǔ)法如下：

select * from table_name limit [offset，] rows

參數(shù)說(shuō)明：

• offset：指定第一個(gè)返回記錄行的偏移量（即從哪一行開(kāi)始返回），注意：初始行的偏移量為0。
• rows：返回具體行數(shù)。

總結(jié)：如果limit后面是一個(gè)參數(shù)，就是檢索前多少行。如果limit后面是2個(gè)參數(shù)，就是從offset+1行開(kāi)始，檢索rows行記錄。
舉例：

select * from table_name limit 10;//檢索前10行記錄
select * from table_name limit 5 ，10;//從第6行開(kāi)始，檢索10行記錄，即：檢索記錄行 6-15

2、分頁(yè)

客戶端通過(guò)傳遞page(頁(yè)碼)，pageSize(每頁(yè)顯示的條數(shù))兩個(gè)參數(shù)去分頁(yè)查詢數(shù)據(jù)庫(kù)表中的數(shù)據(jù)，那我們知道MySql數(shù)據(jù)庫(kù)提供了分頁(yè)的函數(shù)limit m,n，但是該函數(shù)的用法和我們的需求不一樣，所以就需要我們根據(jù)實(shí)際情況去改寫(xiě)適合我們自己的分頁(yè)語(yǔ)句，具體的分析如下：
比如：

• 查詢第1條到第10條的數(shù)據(jù)的sql是：select * from table limit 0,10; ->對(duì)應(yīng)我們的需求就是查詢第一頁(yè)的數(shù)據(jù)：select * from table limit (1-1)*10,10;
• 查詢第10條到第20條的數(shù)據(jù)的sql是：select * from table limit 10,10; ->對(duì)應(yīng)我們的需求就是查詢第二頁(yè)的數(shù)據(jù)：select * from table limit (2-1)*10,10;
• 查詢第20條到第30條的數(shù)據(jù)的sql是：select * from table limit 20,10; ->對(duì)應(yīng)我們的需求就是查詢第三頁(yè)的數(shù)據(jù)：select * from table limit (3-1)*10,10;

總結(jié)：

通過(guò)上面的分析，可以得出符合我們需求的分頁(yè)sql格式是：

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

四.ifnull()函數(shù)的使用

1、ifnull()的語(yǔ)法

ifnull(a,b)函數(shù)解釋：

• 如果value1不是空，結(jié)果返回a
• 如果value1是空，結(jié)果返回b

2、ifnull()的使用

如下表：分?jǐn)?shù)表score

1、 在統(tǒng)計(jì)數(shù)據(jù)時(shí)如果沒(méi)有值返回，給結(jié)果置為0

舉例：統(tǒng)計(jì)物理科目分?jǐn)?shù)總和時(shí)，如果該列為null，返回0，如果不使用ifnull(),正常返回值為null

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

2、在查詢數(shù)據(jù)需要計(jì)算遇到null值時(shí) 

舉例：查詢id為1的學(xué)生的語(yǔ)文和物理的總分

錯(cuò)誤寫(xiě)法（在數(shù)據(jù)庫(kù)沒(méi)有指定為空時(shí)的默認(rèn)值時(shí)）

SELECT Chinese+Physics FROM score;

數(shù)據(jù)為null不能參與運(yùn)算，否則會(huì)直接報(bào)錯(cuò)。

正確寫(xiě)法

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

還有就是使用SUM()等函數(shù)，可以忽略計(jì)算中的空值。

SELECT SUM(Chinese，Physics) FROM score;

總結(jié) 

到此這篇關(guān)于MySQL按年/月/周/日/小時(shí)分組查詢、排序、limit及判空用法的文章就介紹到這了,更多相關(guān)MySQL分組查詢、排序、limit及判空內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論