【数据库】Mysql、Oracle的年、月、日sql脚本

汝之观览，吾之幸也！本文主要讲解Mysql、Oracle的年、月、日sql脚本

一、Mysql

1、当月数据

select a1.dayDate,convert(SUBSTRING(a1.dayDate,12,13),DECIMAL(10,0)) dayHour from(
select
DATE_FORMAT(DATE_ADD(curdate(),interval -day(curdate())+ac day), '%Y-%m-%d') as dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=-1) xc0
) ad)a1
where DATE_FORMAT(a1.dayDate,'%Y-%m-%d')
between DATE_FORMAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),'%Y-%m-%d') and DATE_FORMAT( last_day(curdate()),'%Y-%m-%d');

2、当天24小时数据

select DATE_FORMAT(DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL -ac+1 HOUR ) ,'%Y-%m-%d %H') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=0) xc0
) ad;

3、当年12个月数据

select DATE_FORMAT(DATE_SUB(CONCAT(YEAR(NOW()), '-01-01'), INTERVAL -ac MONTH ) ,'%Y-%m') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 ) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac2,
(SELECT @ai:=-1) xc0
) ad;

4、近七天

select
DATE_FORMAT(DATE_ADD(curdate(),interval -ac day), '%Y-%m-%d') as dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 ) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) ac2,
(SELECT @ai:=-1) xc0
) ad limit 7;

5、字符集问题

# 将traff_biz库中的表的字符集与字符规则更新为 utf8mb4、utf8mb4_general_ci
SELECT
CONCAT("ALTER TABLE `",TABLE_NAME,
"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")  AS target_tables 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="traff_biz" AND TABLE_TYPE="BASE TABLE";

# 将traff_biz库中的表的全部字段字符集与字符规则更新为 utf8mb4、utf8mb4_general_ci
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, 
        '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', 
        (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
        (case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
        ';') as `sql`
FROM information_schema.COLUMNS
WHERE 1=1
    and TABLE_SCHEMA = 'traff_biz' #要修改的数据库名称
		#and TABLE_NAME='table_name'  # 要修改的指定数据表名称
    #and DATA_TYPE = 'varchar' # 要修改的指定列类型
    and COLLATION_NAME ='utf8mb4_0900_ai_ci'; # 要修改的字符集
		
# 查看traff_biz库中的表所占空间大小
SELECT a.TABLE_NAME, concat(round(sum(a.DATA_LENGTH/1024/1024/1024),2),'GB') as data 
FROM information_schema.`TABLES` a 
WHERE a.TABLE_SCHEMA = 'traff_biz'
GROUP BY a.TABLE_NAME;

6、近一年的年份和季度

select year(DATE_SUB(now(), INTERVAL 4*ac MONTH )) dateYear,
quarter(DATE_SUB(now(), INTERVAL 4*ac MONTH )) dateQuarter
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 ) ac1,
(SELECT 1 UNION SELECT 2 ) ac2,
(SELECT @ai:=-1) xc0
) ad;

7、近12个月

select DATE_FORMAT(DATE_SUB(NOW(), INTERVAL ac MONTH ) ,'%Y-%m') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 ) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac2,
(SELECT @ai:=-1) xc0
) ad;

8、其他整理

# 1年12月
select DATE_FORMAT(DATE_SUB(CONCAT('2023', '-01-01'), INTERVAL -ac MONTH ) ,'%Y-%m') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 ) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac2,
(SELECT @ai:=-1) xc0
) ad;

# 1月30天
select * from(
select
DATE_FORMAT(DATE_ADD(concat('2023-01','-01'),interval ac day), '%Y-%m-%d') as dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=-1) xc0
) ad)a1
where DATE_FORMAT(a1.dayDate,'%Y-%m') ='2023-01';

# 时间段天数数据
select * from(
select
DATE_FORMAT(DATE_ADD('2023-01-01',interval ac day), '%Y-%m-%d') as dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
union SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
union SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
union SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
union SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18) ac2,
(SELECT @ai:=-1) xc0
) ad)a1
where DATE_FORMAT(a1.dayDate,'%Y-%m-%d')
between '2023-01-01' and '2023-02-11';

# 季度,向前推
select year(DATE_SUB('2023-01-01', INTERVAL 4*ac MONTH )) date_year,
quarter(DATE_SUB('2023-01-01', INTERVAL 4*ac MONTH )) date_quarter
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 ) ac1,
(SELECT 1 UNION SELECT 2 ) ac2,
(SELECT @ai:=-1) xc0
) ad;

# 一天24小时
select DATE_FORMAT(DATE_SUB('2023-01-01',INTERVAL -ac+1 HOUR ) ,'%Y-%m-%d %H') dayDate
FROM (
SELECT @ai:=@ai+1 as ac from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ac1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ac2,
(SELECT @ai:=0) xc0
) ad;

二、Oracle

1、当年12个月

select '2022' dateYear,to_char(add_months(trunc(to_date('2022','yyyy'),'yyyy'),level-1),'mm') dateMonth 
from dual connect by level<=12 ;

2、前12个月

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(to_char(SYSDATE,'yyyy-mm'),'yyyy-MM'), ROWNUM - 12),'yyyy-MM')
as dateTime FROM DUAL
CONNECT BY ROWNUM <= 12;

3、前30天

SELECT to_char(day,'yyyy-mm-dd') as "days"
          from (
        SELECT to_date(to_char(trunc(sysdate, 'dd'), 'yyyy-mm-dd'), 'yyyy-mm-dd') - rownum + 1 as day
          FROM dual
                  CONNECT BY ROWNUM <= 30
        )
ORDER BY day asc;

4、近5年

SELECT to_char(SYSDATE,'YYYY') - LEVEL + 1 as dateyear
FROM dual
CONNECT BY LEVEL <= 5
ORDER BY to_char(SYSDATE,'YYYY') - LEVEL + 1 ASC;

5、分钟 1-12 5分钟一个时间片

select trunc(to_number(to_char(sysdate,'mi'))/5)+1 from dual;

6、前一个小时

select to_char(sysdate-1/24,'yyyy-MM-dd hh24') from dual;