Mysql常用函数/方法

李指导、

已于 2022-03-22 10:22:05 修改

阅读量847

点赞数

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

于 2022-03-22 10:20:47 首次发布

本文链接：https://blog.csdn.net/LiHaoHang6/article/details/123653363

版权

数据库专栏收录该内容

10 篇文章 2 订阅

订阅专栏

Mysql根据开始时间和结束时间获取区间时间值,如果不加where则是每天的时间值。

SELECT 
a1.DATE
FROM (
SELECT
	CURDATE( ) - INTERVAL ( a.a + ( 10 * b.a ) + ( 100 * c.a ) +( 100 * d.a )) DAY AS DATE 
FROM
	(
	SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
	) AS a
	CROSS JOIN (
	SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
	) AS b
	CROSS JOIN (
	SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
	) AS c
	CROSS JOIN (
	SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
	) AS d
) AS a1 
-- WHERE a1.DATE >= '2022-03-01' AND a1.DATE <= '2022-03-10'
ORDER BY a1.DATE DESC

Mysql获得最近七天日期

        SELECT * FROM ( 
				SELECT curdate() as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 1 day) as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 2 day) as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 3 day) as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 4 day) as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 5 day) as mycreatedate
        union all
        SELECT date_sub(curdate(), interval 6 day) as mycreatedate
        ) as total