mysql资源

1.赋随机日期

update basic set `成立日期`=concat(CONCAT(FLOOR(2000 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0)),' ',CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)));

 

2.alter table basic MODIFY `成立日期` datetime; 

3.select DATE_FORMAT(NOW(),'%Y-%m-%d');   #函数用于以不同的格式显示日期/时间数据

4.分组统计

#按年分组统计
SELECT  YEAR(a.`成立日期`) years, count(*) cnt FROM basic AS a GROUP BY years ORDER BY years asc;

#按季分组统计
SELECT CONCAT(YEAR(a.`成立日期`),'Q',QUARTERs(a.`成立日期`)) quarters, count(*) cnt FROM basic AS a GROUP BY quarters ORDER BY quarters asc;

#按月分组统计
SELECT date_format(a.`成立日期`, '%Y%m') months, count(*) cnt FROM basic AS a GROUP BY months ORDER BY months asc;

#按周分组统计，以周一为一周起始
SELECT date_format(a.`成立日期`, '%xw%v') weeks, count(*) cnt FROM basic AS a GROUP BY weeks ORDER BY weeks asc;

#按天统计
select date_format(a.`成立日期`, '%Y/%m/%d') days,count(*) cnt from basic AS a GROUP BY days ORDER BY days asc;