MySQL常用查询

# 常用时间查询

# 查询指定时间数据

# 查询某一年 某一月 某一天的数据(可组合)
SELECT  * FROM 表名 WHERE DATE_FORMAT(列名,'%Y-%m-%d') = '2016-10-10';

1
2

# 查询指定天数内的数据

# 查询最近7天的数据(左表为近7天)
  SELECT a.dateTime,IFNULL(b.count,0) AS `count`
  FROM (
    SELECT curdate() as dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 1 day) AS dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 2 day) AS dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 3 day) AS dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 4 day) AS dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 5 day) AS dateTime
    UNION ALL
    SELECT date_sub(curdate(), INTERVAL 6 day) AS dateTime
    ) a LEFT JOIN (
    SELECT DATE(列名) AS dateTime, COUNT(*) AS `count`
    from 表名
    GROUP BY date(列名)
    ) b ON a.dateTime = b.dateTime
  ORDER BY
    a.dateTime ASC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 查询指定月份内的数据

# 最近5个月统计数据
SELECT IFNULL(b.count,0) AS `count`,a.date
FROM(
    SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%m') AS `date`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%m') AS `date`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%m') AS `date`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%m') AS `date`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%m') AS `date`
    UNION SELECT DATE_FORMAT(CURDATE(), '%m') AS `date` ) AS a
LEFT JOIN
(SELECT DATE_FORMAT(列名,'%m') AS date,
    COUNT(*) AS `count`
    FROM 表名
    GROUP BY MONTH(列名))AS b
ON a.date = b.date

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 查询指定时间前的数据

# 查询3天前的数据
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(列名) >= 3 ORDER BY 列名 DESC; # 列名为时间相关字段

1
2

# 查询指定时间范围内的数据

# 查询前3到前7天内的数据
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(列名) >= 3
AND TO_DAYS(NOW()) - TO_DAYS(列名) < 7  ORDER BY START_DATE DESC; # 列名为时间相关字段

1
2
3

# 查询季度数据

# 查询4个季度数据
  SELECT t1.`quarter`, IFNULL(t2.`count`,0) AS `count` FROM
    (SELECT "first" as `quarter` UNION ALL SELECT "second" UNION ALL SELECT "third" UNION ALL SELECT "fourth") t1
      LEFT JOIN
    (SELECT
       CASE
         WHEN MONTH(alarm_time) BETWEEN 1 AND 3 THEN 'first'
          WHEN MONTH(alarm_time) BETWEEN 4 AND 6 THEN 'second'
          WHEN MONTH(alarm_time) BETWEEN 7 AND 9 THEN 'third'
          WHEN MONTH(alarm_time) BETWEEN 10 AND 12 THEN 'fourth'
       END AS `quarter`,
       COUNT(*) AS `count`
      FROM 表名
  GROUP BY quarter) t2 ON t1.`quarter` = t2.`quarter`

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 常用统计查询

# 根据指定字段的值统计

# 根据类型统计数量
  SELECT
    IFNULL(SUM(CASE `type` WHEN 1 THEN 1 ELSE 0 END) ,0) AS 列名1,
    IFNULL(SUM(CASE `type` WHEN 2 THEN 1 ELSE 0 END) ,0) AS 列名2
  FROM 表名

1
2
3
4
5

# 查询重复数据

SELECT 列名,count(*) as `count` FROM 表名 group by 列名 HAVING `count` > 1;
# 查询用户名重复(大于1)的数据
select user_name,count(*) as `count` from user group by user_name having `count` > 1;

1
2
3

# 更新生日格式

# 更新生日字段格式为: yyyy-MM-dd 
update table_name SET birthday = DATE_FORMAT(birthday,'%Y-%m-%d') WHERE birthday IS NOT NULL AND birthday != '';

1
2

#SQL教程

上次更新: 2024/01/16, 13:25:25

← MySQL视图 MyBatisPlus常用操作→