select DATE_FORMAT(a.Date,'%Y-%m-%d' ) as uDate
from(
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.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
) a
where a.Date>=startDate and a.Date<=endDate
-- 1.1 根据输入日期来返回各个日期 并保存在表中 查询earchars图表数据
BEGIN
SET @createSql = 'CREATE TABLE IF NOT EXISTS calendar (
`date` date NOT NULL,
UNIQUE KEY `unique_date` (`date`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8';
prepare stmt from @createSql;
execute stmt;
-- TRUNCATE calendar; -- 删除表原有数据 不需要 使用唯一约束了
WHILE s_date <= e_date DO
INSERT IGNORE INTO calendar VALUES (DATE(s_date)) ;
SET s_date = s_date + INTERVAL 1 DAY ;
END WHILE ;
-- SELECT a.date from calendar a;
END
-- 方法2 添加mydate 表 及此存储过程
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE beginDate datetime;
DECLARE endDate datetime;
DECLARE difDay int DEFAULT 0;
set beginDate =DATE_FORMAT(startTime,'%Y-%m-%d');
set endDate = DATE_FORMAT(endTime,'%Y-%m-%d');
select (DAYOFYEAR(endDate) - DAYOFYEAR(beginDate)) into difDay;
WHILE i<difDay
DO
insert into mydate (mydate) values (DATE_ADD(beginDate,INTERVAL i DAY));
SET i=i+1;
END WHILE ;
END
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`%`
SQL SECURITY DEFINER
VIEW `year_month_view` AS
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
-- SELECT DATE_FORMAT(NOW(),'%Y-12-01') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `year_month`
;
SELECT * from year_month_view ORDER BY year_month desc ;
-- drop view year_month_view ;
-- 查询全年数据1-12月份
select
case month(t.updateDate) when '1' then sum(1) else 0 end as 1月,
case month(t.updateDate) when '2' then sum(1) else 0 end as 2月,
case month(t.updateDate) when '3' then sum(1) else 0 end as 3月,
case month(t.updateDate) when '4' then sum(1) else 0 end as 4月,
case month(t.updateDate) when '5' then sum(1) else 0 end as 5月,
case month(t.updateDate) when '6' then sum(1) else 0 end as 6月,
case month(t.updateDate) when '7' then sum(1) else 0 end as 7月,
case month(t.updateDate) when '8' then sum(1) else 0 end as 8月,
case month(t.updateDate) when '9' then sum(1) else 0 end as 9月,
case month(t.updateDate) when '10' then sum(1) else 0 end as 10月,
case month(t.updateDate) when '11' then sum(1) else 0 end as 11月,
case month(t.updateDate) when '12' then sum(1) else 0 end as 12月
from t_slaughterout t
where date_format(t.updateDate, '%Y') = YEAR(CURDATE())
-- 常用sql 语法 及函数
set @data=new DATA()
SELECT LEFT('www.yuanrengu.com',3) -- 从左开始截取字符串
SELECT SUBSTRING('www.yuanrengu.com', 1, 1)
SELECT NOW() ,CURDATE() ,CURTIME() -- 都表示时间 cureate表示2017-01-05 格式 最后一个是当前时间时分秒
SELECT DATE(NOW()) AS OrderDate -- DATE() 函数返回日期或日期/时间表达式的日期部分。跟curedate差不多
SELECT FORMAT(200.1056,2) -- FORMAT函数在mysql中是数据内容格式化的，格式化后得到结果：＃＃＃，＃＃＃，＃＃＃＃＃。做四舍五入或者格式化数据为浮点或者整数
select DATE_FORMAT( NOW() ,'%Y-%m-%d') -- 格式化日期为指定格式
select month('2018-08-15') -- 返回8 MONTH函数返回一个整数，表示指定日期值的月份
select YEAR('2018-08-15') -- 返回2018
SELECT * FROM t_slaughterout a WHERE a.updateDate>DATE_SUB(CURDATE(), INTERVAL 1 YEAR) ; -- 距离今天近一年数据
select * from t_slaughterout a where YEAR(a.UPDATEDATE)=YEAR(NOW());
SELECT CONCAT('My', 'S', 'QL'); -- 连接字符串
SELECT DATE_SUB(DATE_FORMAT(CURDATE(),'%Y-%m-%01'), INTERVAL 1 YEAR)
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%01')