Mysql 总结（一）

存储过程的创建：

DELIMITER //  
CREATE DEFINER=`root`@`localhost` PROCEDURE `his_proc`()
BEGIN
  start transaction;
	set @CurYear = year(curdate());
	set @strDate = CURRENT_DATE();

  set @PlantTableName = CONCAT('plant',@CurYear);
  set @InvTableName = CONCAT('inverter',@CurYear);
	set @comboxTableName = CONCAT('combox',@CurYear);
	set @meterTableName = CONCAT('meter',@CurYear);
	set @sensorTableName = CONCAT('sensor',@CurYear);

	#电站
	set @PlantSql= concat('insert ignore into ',@PlantTableName,
	'(PlantTime,DailyYield,TotalYield,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,PlantPower,DailyCo2,TotalCo2,DCPower,TheoryPower,GridPower) ',
	'select PlantTime,DailyYield,TotalYield,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,PlantPower,DailyCo2,TotalCo2,DCPower,TheoryPower,GridPower ',
	'from plantpinstance where date(PlantTime)=\'',@strDate,'\'');
  #select @PlantSql;

	#逆变器
	set @inverterSql = concat('insert ignore into ',@InvTableName,
	'(InvTime,DevAddr,DailyYield,TotalYield,TotalRuntime,AirTemp,TransferTemp,FanTemp,DCVoltage,DCCurrent,InputPSum,Uab,Ubc,',
	'Uca,Ia,Ib,Ic,PA,PB,PC,PSum,QSum,PFSum,Frq,InvEfficiency,RunStatus,Upv2,Upv3,Upv4,Upv5,Upv6,Ipv2,Ipv3,Ipv4,Ipv5,Ipv6)',
	'select InvTime,DevAddr,DailyYield,TotalYield,TotalRuntime,AirTemp,TransferTemp,FanTemp,DCVoltage,DCCurrent,InputPSum,Uab,Ubc,',
	'Uca,Ia,Ib,Ic,PA,PB,PC,PSum,QSum,PFSum,Frq,InvEfficiency,RunStatus,Upv2,Upv3,Upv4,Upv5,Upv6,Ipv2,Ipv3,Ipv4,Ipv5,Ipv6 ',
	'from inverterpinstance where date(InvTime)=\'',@strDate,'\'');

	#汇流箱
	set @comboxSql = concat('insert ignore into ',@comboxTableName,
	'(CBTime,DevAddr,CBTemp,Voltage,I_1,I_2,I_3,I_4,I_5,I_6,I_7,I_8,I_9,I_10,I_11,I_12,I_13,I_14,',
	'I_15,I_16,I_17,I_18,I_19,I_20,I_21,I_22,I_23,I_24,I_25,I_26,I_27,I_28,I_29,I_30,I_31,I_32)',
	'select CBTime,DevAddr,CBTemp,Voltage,I_1,I_2,I_3,I_4,I_5,I_6,I_7,I_8,I_9,I_10,I_11,I_12,I_13,I_14,',
	'I_15,I_16,I_17,I_18,I_19,I_20,I_21,I_22,I_23,I_24,I_25,I_26,I_27,I_28,I_29,I_30,I_31,I_32 ',
	'from combinerboxpinstance where date(CBTime)=\'',@strDate,'\'');

	#电表
	set @meterSql = concat('insert ignore into ',@meterTableName,
	'(MDPTime,DevAddr,SystemFrq,Uab,Ubc,Uca,Ia,Ib,Ic,P1,P2,P3,PSum,Q1,Q2,Q3,QSum,S1,S2,S3,SSum,PF1,PF2,PF3,PFSum,ZY,FY,ZW,FW)',
	'select MDPTime,DevAddr,SystemFrq,Uab,Ubc,Uca,Ia,Ib,Ic,P1,P2,P3,PSum,Q1,Q2,Q3,QSum,S1,S2,S3,SSum,PF1,PF2,PF3,PFSum,ZY,FY,ZW,FW ',
	'from meterpinstance where date(MDPTime)=\'',@strDate,'\'');

	#环境检测仪
	set @sensorSql = concat('insert ignore into ',@sensorTableName,
	'(EMDTime,DevAddr,DailyAmplitude,TotalAmplitude,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,WindSpeed,WindDir)',
	'select EMDTime,DevAddr,DailyAmplitude,TotalAmplitude,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,WindSpeed,WindDir ',
	'from sensorpinstance where date(EMDTime)=\'',@strDate,'\'');

	prepare PlantStmt from @PlantSql;
	execute PlantStmt;
	prepare inverterStmt from @inverterSql;
	execute inverterStmt;	
	prepare comboxStmt from @comboxSql;
	execute comboxStmt;	
	prepare meterStmt from @meterSql;
	execute meterStmt;  
	prepare sensorStmt from @sensorSql;
	execute sensorStmt;  	

	commit;
END
//
DELIMITER; 

#查看存储过程
show procedure status;
#查看过程创建代码
show create procedure his;
#删除过程
DROP PROCEDURE his_proc;

定时器的创建：

CREATE DEFINER=`root`@`localhost` 
EVENT `his_timer`
ON SCHEDULE EVERY 5 MINUTE STARTS '2016-05-20 20:30:45'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '历史数据定时器'
DO
call his();

#查看事件
select * from  mysql.event;
#关闭事件
alter event hhis_timer on completion preserve disable;
#删除事件
DROP EVENT  IF EXISTS  hhis_timer;