MYSQL查询今天、昨天、本周、本月、上一月 、下一月、上一年、下一年、上季度、下季度

-- 今天
select * from 表名 where to_days(时间字段名) = to_days(now());
-- 昨天
select * from 表名 where to_days( now( ) ) - to_days(时间字段名) <= 1;
-- 本周
select * from 表名 where yearweek( date_format(时间字段名,'%y-%m-%d' ) ) = yearweek(now());
-- 上一周
select * from 表名 where yearweek(date_format(时间字段名, '%y-%m-%d')) = yearweek(now())-1;
-- 本月
select * from 表名 where date_format(时间字段名,'%y-%m')=date_format(now(),'%y-%m');
-- 上一月
select * from 表名 where period_diff(date_format(now(), '%y%m'), date_format(时间字段名, '%y%m')) = 1;
-- 本季度
select * from 表名 where quarter(时间字段名)=quarter(now());
-- 上一季度
select * from 表名 where quarter(时间字段名)=quarter(date_sub(now(),interval 1 quarter));
-- 本年
select * from 表名 where year(时间字段名)=year(now());
-- 上一年
select * from 表名 where year(时间字段名)=year(date_sub(now(),interval 1 year));
-- 查询距离当前现在6个月的数据
select * from 表名 where 时间字段名 between date_sub(now(),interval 6 month) and now();

-- 周 日期数据
-- 获取昨天日期
select DATE_SUB( curdate( ), INTERVAL 1 DAY );
-- 获取上周第一天
select date_sub(now(),interval dayofweek(now())-1+6 day);
-- 获取上周最后一天
select date_sub(now(),interval dayofweek(now())-1 day);
-- 获取本周第一天
select date_sub(now(),interval dayofweek(now())-2 day);
-- 获取本周最后一天
select date_add(now(),interval 7-dayofweek(now())+1 day);
-- 获取下周第一天
select date_add(now(),interval 7-dayofweek(now())+1+1 day);
-- 获取下周最后一天
select date_add(now(),interval 7-dayofweek(now())+1+7 day);

-- 月 日期数据
-- 本月天数
select day(last_day(curdate()));
-- 获取当前月已过了几天
select day(curdate());
-- 上月今天的当前日期
select date_sub(curdate(), interval 1 month);
-- 上月今天的当前时间（时间戳）
select unix_timestamp(date_sub(now(), interval 1 month));
-- 获取当前时间与上个月之间的天数
select datediff(curdate(), date_sub(curdate(), interval 1 month));
-- 获取本月的第一天
select date_add(curdate(), interval - day(curdate()) + 1 day);
-- 获取本月的最后一天
select last_day(curdate());
-- 获取上个月的第一天
select date_add(curdate() - day(curdate()) + 1, interval -1 month);
-- 上个月的最后一天
select last_day(date_sub(now(), interval 1 month));
-- 获取下个月的第一天
select date_add(curdate() - day(curdate()) + 1, interval 1 month);
-- 获取下个月最后一天
select last_day(date_sub(now(), interval -1 month));
-- 获取当前月的天数(先加一个月，再减今天是第几天，得到当前月的最后一天，最后求最后一天是几号)
select day(date_add(date_add(curdate(), interval 1 month), interval -day(curdate()) day));
-- 获取当前月的天数(先加一个月，再减今天是第几天，得到当前月的最后一天，最后求最后一天是几号)
select day(date_add(date_add(curdate(), interval 1 month), interval -day(curdate()) day));

-- 季度 日期数据
-- 返回上季度第一天
select concat(year(now()),'-',lpad((quarter(now())-1)*3-2,2,'0'),'-01');
-- 返回上季度最后一天
select date_sub(concat(year(now()),'-',lpad((quarter(now())-1)*3+1,2,'0'),'-01'),interval 1 day);
-- 返回本季度第一天
select concat(year(now()),'-',lpad((quarter(now())-1)*3+1,2,'0'),'-01');
-- 返回本季度最后一天
select last_day(concat(year(now()),'-',lpad(quarter(now())*3,2,0),'-01'));
-- 返回下季度第一天
select concat(year(now()),'-',lpad((quarter(now()))*3+1,2,'0'),'-01');
-- 返回下季度最后一天
select last_day(concat(year(now()),'-',lpad((quarter(now())+1)*3,2,0),'-01'));

-- 年 日期数据
-- 返回上年第一天
select concat(year(now())-1,'-01-01');
-- 返回上年最后一天
select concat(year(now())-1,'-12-31');
-- 返回本年第一天
select date_sub(now(),interval dayofyear(now())-1 day);
-- 返回本年最后一天
select concat(year(now()),'-12-31');
-- 返回下年第一天
select date_add(concat(year(now()),'-12-31'),interval 1 day);
-- 返回下年最后一天
select date_add(concat(year(now()),'-12-31'),interval 1 day);

-- 查询表结构包含注释
select * from information_schema.columns
where table_schema = 'brio' #表所在数据库
and table_name = 'user' ; #你要查的表

-- 数据库相差8小时解决
select now();
show variables like '%time_zone%'; -- 查询当前时区
set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间，即我们所在的东8区
set time_zone = '+8:00'; ##修改当前会话时区
flush privileges; #立即生效

-- 查询重复 记录
select order_id, count(*) as count
from od_order_item
group by order_id
having count > 1;

-- 查询数据大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='risk_fxjc' AND table_name='hft_machine_logs';
-- 查询表结构
desc hft_machine_logs;


-- 近七天账单统计--
SELECT SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND DATE_SUB(CURDATE(),INTERVAL 7 DAY) <=DATE(Creat_time)
-- 本周账单统计--
SELECT SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND YEARWEEK(date_format(Creat_time,'%Y-%m-%d')) = YEARWEEK(now())
-- 上月账单统计--
SELECT SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND period_diff(date_format(now(), '%y%m'), date_format(Creat_time, '%y%m')) = 1
-- 本月账单统计--
SELECT SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND date_format(Creat_time,'%y-%m')=date_format(now(),'%y-%m');
-- 近30天账单统计--
SELECT SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <=DATE(Creat_time)
-- 上季度账单统计--
select SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND quarter(Creat_time)=quarter(date_sub(now(),interval 1 quarter))
-- 本季度账单统计--
select SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND quarter(Creat_time)=quarter(now());
-- 本年账单统计--
select SUM(Money) FROM v_g_account WHERE creatuserID=100031 AND year(Creat_time)=year(now());
-- 月统计 --
select * FROM v_g_account WHERE creatuserID=100031 AND Creat_time LIKE '%2021-10%'
-- 类别账单统计--
select categoryName,SubcategoryName,SUM(Money) from v_g_account WHERE creatuserID=100031 and period_diff(date_format(now(), '%y%m'), date_format(Creat_time, '%y%m')) = 1 group by SubcategoryID,categoryID

每年的每个月

 select
     sum(case month(r_time) when \'1\'  then r_recharge else 0 end) as Jan,
     sum(case month(r_time) when \'2\'  then r_recharge else 0 end) as Feb,
     sum(case month(r_time) when \'3\'  then r_recharge else 0 end) as Mar,
     sum(case month(r_time) when \'4\'  then r_recharge else 0 end) as Apr,
     sum(case month(r_time) when \'5\'  then r_recharge else 0 end) as May,
     sum(case month(r_time) when \'6\'  then r_recharge else 0 end) as June,
     sum(case month(r_time) when \'7\'  then r_recharge else 0 end) as July,
     sum(case month(r_time) when \'8\'  then r_recharge else 0 end) as Aug,
     sum(case month(r_time) when \'9\'  then r_recharge else 0 end) as Sept,
     sum(case month(r_time) when \'10\' then r_recharge  else 0 end) as Oct,
     sum(case month(r_time) when \'11\' then r_recharge  else 0 end) as Nov,
     sum(case month(r_time) when \'12\' then r_recharge  else 0 end) as Dece
 from rechargeinfo
 where year(r_time)=\'2019\';

# 查询每年的数据
SELECT
    COUNT(1) AS countNumber,
    YEAR(create_time) AS dateTime
FROM project_case
GROUP BY dateTime;

查询重复数据统计
SELECT gateway_code, COUNT(*)
FROM gatewayServer
GROUP BY gateway_code
HAVING COUNT(*) > 1;