mysql：常用查询语句

1、在原有的时间字段上做加减

（1）减几天DATE_SUB

UPDATE  bg_outpatient_test set test_time=DATE_SUB(test_time,INTERVAL  30 DAY) where test_time>SYSDATE();

（2）加几天ADDDATE

UPDATE  bg_outpatient_test set test_time=ADDDATE(test_time,INTERVAL  30 DAY) where test_time>SYSDATE();

2、按日、周、月统计

select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  

select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  

select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

3、设置一个时间段的随机时间戳

set date= CONCAT(FLOOR(2016 + (RAND() * 1)),'-',LPAD(FLOOR(1 + (RAND() * 11)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))
set time= CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59))

update bg_outpatient_test  set test_time= CONCAT(@dates,' ',@time);

4、存储过程和游标

drop PROCEDURE if EXISTS out_patient_test;
create PROCEDURE out_patient_test()

begin
DECLARE num int DEFAULT 0; 
DECLARE IDS INT DEFAULT 1001;
DECLARE sn varchar(20);
DECLARE done int ;
DECLARE c_device_relation cursor for select DEVICE_SN from patient_device_relation;
/* 异常处理 */
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
truncate table bg_outpatient_test;
while num<280 do
open c_device_relation;
set done=0;
while done<>1 do
fetch c_device_relation into sn;
/*rand()取是是一个0和1之间的随机小数，FLOOR(X)返回不大于X的最大整数，floor（1.8）等于1*/
/*FLOOR(2016 + (RAND() * 1)取2016年，floor是取整函数*/
/*LPAD(FLOOR(8 + (RAND() * 3)),2,0)取9-11月之间的月份，并且lpad在月份之间填充0，如果大于两位，则不填充。如9月份则是09,10月是10*/
/*LPAD(FLOOR(1+(RAND() * 30)),2,0)取1-30号*/
set @dates= CONCAT(FLOOR(2016 + (RAND() * 1)),'-',LPAD(FLOOR(9 + (RAND() * 3)),2,0),'-',LPAD(FLOOR(1+(RAND() * 30)),2,0));
set @time= CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));


insert into BG_OUTPATIENT_TEST (ID, TEST_TIME, CODE, DEVICESN, RESERVEDCODE, USERCODE, RESULT, FOODSTATUS, CREATE_TIME, IS_REPEAT, TIME_ID, TIME_NAME, NORMAL_RESULT, NORMAL)
values (IDS, CONCAT(@dates,' ',@time), IDS, sn, '01', null, '11.41', '2', CONCAT(@dates,' ',@time), '1', 10037, '早餐后', '1', '0');

SET IDS=IDS+1;
end while;
set num=num+1;
CLOSE c_device_relation;
end while;
end