mysql语句

更新
update  bms_rule_faultwarning  set  equipment_uuid='263ad269e8ef44418bc75afefa3093ce'  , equipment_type='VAV'  where  id=4257 ;

update bms_rule_faultwarning as fault set equipment_name=(select `name` from bms_system_equipment  as  equipment where standard_type not in('CD', 'Temp', 'PS', 'SEN_TEMP', 'SEN_420MA', 'SEN_EP', 'OTH') and  fault.equipment_uuid=equipment.uuid);

删除
sql = 'delete from  bms_rule_faultwarning  WHERE id =%s ' % value

插入数据
insert  into  bms_system_equipment_module(equipment_id,module_id)  values('%s',%s)"%(uuid,3)

随机生成时间并更改数据库
time = "2019-03-20 %s:%s:%s" % (random.randint(0,8), random.randint(0,59), random.randint(0,59))
sql1 = 'UPDATE bms_rule_faultwarning set create_time ="{0}" ,update_time ="{0}" WHERE id= {1}'.format(time,i)

数据库只更新日期，时分秒不变
sql1 = 'UPDATE bms_rule_faultwarning set create_time = ADDTIME (date("{0}") + interval 0 hour,time(create_time)),update_time = ADDTIME (date("{0}") + interval 0 hour,time(update_time))WHERE id= {1}'.format('2019-03-23',id)

连表查询
select    bms_system_equipment.uuid,bms_system_equipment.name,bms_system_equipmenttype.name   from  bms_system_equipment    left join   bms_system_equipmenttype   on  bms_system_equipmenttype.id =bms_system_equipment.type_id  where bms_system_equipment.install_floor='["L59"]'

根据某一个字段分组
SELECT  level,count(*)   FROM   bms_rule_faultwarning   WHERE   create_time > "2019-03-{0} 00:00:00"  AND create_time < "2019-03-{0} 08:59:59"  AND type = "ERROR"    group by level

mysql数据库去重留一
DELETE
FROM
	bms_rule_faultwarning
WHERE
	equipment_uuid IN (
	SELECT
		dt.equipment_uuid
	FROM
		(
		SELECT
			equipment_uuid
		FROM
			bms_rule_faultwarning
		WHERE
			create_time > "2019-03-%s 00:00:00"
			AND create_time < "2019-03-%s 23:59:59" AND type = "ERROR" GROUP BY equipment_uuid HAVING count( * ) > 1
		) dt
	)

	AND  create_time > "2019-03-%s 00:00:00"
			AND create_time < "2019-03-%s 23:59:59" AND type = "ERROR"
			
			
	
			

数据库更改时间

UPDATE bms_rule_faultwarning  set   create_time  = ADDTIME (date('2019-09-10') + interval 0 hour,time(create_time)) WHERE create_time like "2019-04-26%"　　

改为当前时间：

UPDATE t_env_sensor set get_date = ADDTIME (CURDATE() + interval 0 hour,time(get_date)) WHERE get_date like "2016-04-11%"