Mysql查询数据，方便前端使用Echart回显。

背景

最近经常要返回数据给前端，绘制图表，而且需要补0操作，为了自己方便，记录一下。

关于Mysql的一些知识点

1、UNION ALL

UNION All运算符用于组合两个SELECT语句(包括重复行)的结果。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

2、DATEFORMAT

适用于UNION子句的相同规则将适用于UNION All操作符。

DATE_FORMAT(date,format)

3、IFNULL

SELECT IFNULL(value1,value2) AS status; 当value1为null时,返回结果为value2 当value1非null时,返回结果为value1

IFNULL(value1,value2)

4、DATE_SUB()

DATE_SUB() 函数从日期减去指定的时间间隔。

5、DATE_ADD()

DATE_ADD() 函数向日期添加指定的时间间隔。

DATE_SUB(date,INTERVAL expr type)
DATE_ADD(date,INTERVAL expr type)

6、@符号的作用

@后接变量名，用以定义一个变量，该变量的有效期为语句级，即再一次执行中始终有效。
用select语句时，只能用”:=“方式，因为select语句中，”="号被看作是比较操作符

例子：

Mysql查询当日24小时的数据，当没有数据时，补零。

      SELECT a.times AS                    `date`,
               IFNULL(ac.downlinkTraffic, 0) downlinkTraffic,
               IFNULL(ac.upstreamTraffic, 0) upstreamTraffic
        from (
                 SELECT '00:00' as times

                 union all

                 SELECT '01:00' as times

                 union all

                 SELECT '02:00' as times

                 union all

                 SELECT ' 03:00' as times

                 union all

                 SELECT '04:00' as times

                 union all

                 SELECT '05:00' as times

                 union all

                 SELECT '06:00' as times

                 union all

                 SELECT '07:00' as times

                 union all

                 SELECT '08:00' as times

                 union all

                 SELECT '09:00' as times

                 union all

                 SELECT '10:00' as times

                 union all

                 SELECT '11:00' as times

                 union all

                 SELECT '12:00' as times

                 union all

                 SELECT '13:00' as times

                 union all

                 SELECT '14:00' as times

                 union all

                 SELECT '15:00' as times

                 union all

                 SELECT '16:00' as times

                 union all

                 SELECT '17:00' as times

                 union all

                 SELECT '18:00' as times

                 union all

                 SELECT '19:00' as times

                 union all

                 SELECT '20:00' as times

                 union all

                 SELECT '21:00' as times

                 union all

                 SELECT '22:00' as times

                 union all

                 SELECT '23:00' as times
             ) a
                 left join (
            select DATE_FORMAT(af.create_time, '%H:00') as date,
                   down as downlinkTraffic,
                   up as upstreamTraffic,
            from flow af
            where af.id = '123' and DATE_FORMAT(af.create_time, '%Y-%m-%d') = curdate()
            group by DATE_FORMAT(af.create_time, '%Y-%m-%d %H')) af ON a.times = af.date
        ORDER BY `date`;

Mysql查询近24小时的数据，当没有数据时，补零。

        SELECT a.click_date AS               `date`,
               IFNULL(ac.downlinkTraffic, 0) downlinkTraffic,
               IFNULL(ac.upstreamTraffic, 0) upstreamTraffic
        FROM (
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 1 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 2 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 3 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 4 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 5 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 6 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 7 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 8 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 9 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 10 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 11 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 12 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 13 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 14 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 15 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 16 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 17 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 18 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 19 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 20 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 21 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 22 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 23 HOUR), '%Y-%m-%d %H') as `click_date`
                 UNION ALL
                 SELECT DATE_FORMAT(date_sub(now(), INTERVAL 24 HOUR), '%Y-%m-%d %H') as `click_date`
             ) a
                 LEFT JOIN (
            select DATE_FORMAT(af.create_time, '%Y-%m-%d %H')       as date,
                   down as downlinkTraffic,
                   up as upstreamTraffic,
            from flow af
            where af.id = '123'
            group by DATE_FORMAT(af.create_time, '%Y-%m-%d %H')) ac ON a.click_date = ac.date
        ORDER BY `date`;

查询近30天的数据

SELECT
	date_format( create_time, '%Y-%m-%d' ) AS time,
	sum( entered ) entered,
	sum( exited ) exited 
FROM
	`student` 
WHERE
	id = '123' 
	AND DATE_FORMAT( create_time, '%Y-%m-%d' ) < DATE_SUB( CURRENT_DATE, INTERVAL 29 DAY ) 
GROUP BY
	date_format( create_time, '%Y-%m-%d' );

查询近30天的数据。没有时，补零。

SELECT
	date_table.dates AS dateValue,
	IFNULL( temp.entered, 0 ) AS entered,
	IFNULL( temp.exited, 0 ) AS exited 
FROM
	(
SELECT
	@s := @s + 1 AS indexs,
	DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
FROM
	mysql.help_topic,
	( SELECT @s := - 1 ) temp 
WHERE
	@s < 30 
ORDER BY
	dates 
	) date_table
	LEFT JOIN (
SELECT
	DATE_FORMAT( create_time, '%Y-%m-%d' ) AS dateValue,
	sum( entered ) AS entered,
	sum( exited ) AS exited 
FROM
	`student` t1 
WHERE
	t1.id = '123' 
GROUP BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) 
	) temp ON date_table.dates = temp.dateValue 
ORDER BY
	date_table.dates DESC;

查询近12个月的数据

SELECT
	date_format( create_time, '%Y-%m' ) AS time,
	sum( entered ) entered,
	sum( exited ) exited 
FROM
	`student` 
WHERE
	id = '123' 
	AND DATE_FORMAT( create_time, '%Y-%m' ) < DATE_SUB( CURRENT_DATE, INTERVAL 11 MONTH ) 
GROUP BY
	date_format( create_time, '%Y-%m' );

查询12个月数据，没有时补零。

SELECT
	date_table.date,
	IFNULL( hn.entered, 0 ) AS entered,
	IFNULL( hn.exited, 0 ) AS exited 
FROM
	(
SELECT
	@s := @s + 1 AS indexs,
	DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s MONTH ), '%Y-%m' ) AS date 
FROM
	mysql.help_topic,
	( SELECT @s := 0 ) temp 
WHERE
	@s < 12 
	) date_table
	LEFT JOIN (
SELECT
	DATE_FORMAT( create_time, '%Y-%m' ) AS event_time,
	sum( entered ) AS entered,
	sum( exited ) AS exited 
FROM
	student 
WHERE
	id = '123' 
GROUP BY
	DATE_FORMAT( create_time, '%Y-%m' ) 
	) hn ON date_table.date = hn.create_time;

) h1 ON date_table.date = h1.create_time;