mysql语句总结

删除表中的所有行，而不记录单个行删除操作。并且自增id变为0
如果曾经的数据都不需要的话，可以直接清空所有数据，并将自增字段恢复从1开始计数：
truncate table table_name;

----规则和公式关联
INSERT INTO index_rule_formula (rule_id,formula_name,formula_source)
SELECT r.id rule_id,IFNULL(f.formula_name,'') formula_name,IFNULL(f.formula_source,'') formula_source FROM index_rule_entity r LEFT JOIN formula_entity f on r.name = f.formula_name
WHERE r.id not in (select rule_id from index_rule_formula) and f.formula_name is not null;

UPDATE index_rule_entity r , index_rule_formula f SET r.formula_id=f.id WHERE r.id = f.rule_id;

----规则和指标关联
INSERT INTO index_rule_relation (index_id,rule_id)
SELECT DISTINCT i.id index_id,r.id rule_id FROM index_rule_entity r LEFT JOIN index_entity i on r.name like CONCAT('%',i.index_name)
WHERE 1=1 and r.id not in (select rule_id from index_rule_relation) and i.id not in (select index_id from index_rule_relation)

---查询重复的关系
SELECT r.index_id,r.rule_id FROM index_rule_relation r WHERE 1=1 and rule_id=266;

select * from index_entity t where 1=1
and (id =222 or id = 355);

select * from index_rule_entity where 1=1
#and id =253
and name like '%社区居民电子健康档案建档率（%）%'

SELECT r.index_id,r.rule_id,count(1) FROM index_rule_relation r GROUP BY r.rule_id HAVING count(1)>1;

DELETE from index_rule_relation where 1=1 and index_id =355 and rule_id = 135;

SELECT e.id,e.index_name,r.index_id,r.rule_id FROM index_entity e LEFT JOIN index_rule_relation r on e.id=r.index_id WHERE 1=1 ORDER BY r.rule_id

INSERT INTO index_system_relation (system_id,parent_id,index_id,rule_id)
select 1 system_id,IFNULL((select r.id FROM index_entity e left JOIN index_system_relation r on e.id=r.index_id WHERE 1=1 and e.index_name='概况' limit 1),0) parent_id
,t.id index_id,IFNULL(r.rule_id,0) rule_id
from index_entity t LEFT JOIN index_rule_relation r on t.id=r.index_id where 1=1 and t.index_name in ('概况','经济','文化','民生','生态')