mysql 常用语句

最新推荐文章于 2024-07-10 19:12:38 发布

geniusbluesky

最新推荐文章于 2024-07-10 19:12:38 发布

阅读量187

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/geniusbluesky/article/details/127227399

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

NULL 值判断

select * from jspt_js where remark <=> null；
select * from jspt_js where ISNULL(remark);

select * from jspt_js where !(remark <=> null);
select * from jspt_js where !ISNULL(remark);

近几天的日期获取

SELECT 
	DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s:=@s+1 DAY)) AS 'date' 
FROM 
	mysql.help_topic,(SELECT @s := -1) temp 
WHERE 
	@s < 7 #八天
ORDER BY 'date' ;

当天二十四小时数据

SELECT
	@s := @s + 1 AS `index`,
	DATE_FORMAT( DATE_SUB( DATE_SUB( curdate( ), INTERVAL 1 DAY ), INTERVAL - @s HOUR ), '%Y-%m-%d %H' ) AS `date` 
FROM
	mysql.help_topic,
	( SELECT @s := - 1 ) temp 
WHERE
	@s < 23 
ORDER BY
	`date`

统计近几天的数据

SELECT d.date, IFNULL(r.num,0) AS num
FROM (
	SELECT 
	DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s:=@s+1 DAY)) AS 'date' 
FROM 
	mysql.help_topic,(SELECT @s := -1) temp 
WHERE 
	@s < 7 #八天
ORDER BY 'date' 
) d 
LEFT JOIN (
	SELECT DATE_FORMAT(js_date,'%Y-%m-%d') AS createTime, count(*) AS num
	FROM jspt_js
	GROUP BY createTime
	) r ON r.createTime = d.date
GROUP BY d.date