mysql增加列、修改列、删除列、修改数据（关联表）

#增加列
alter TABLE store_to_mifan_2019 add total_year int(11)  
select * from store_to_mifan_2019

#修改列的名称：
alter TABLE store_to_mifan_2019 change total_year  total_years int(11)  
# (把表名为store_to_mifan_2019的total_year列名修改为total_years)  

#填充数据
update store_to_mifan_2019 set 
total_years=month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12
 

#删除列
alter TABLE store_to_mifan_2019 drop column total_years  


#修改表中的数据
update store_to_mifan_2019 s1 LEFT JOIN (select b.sid,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-01' then b.date else null end)) as month1,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-02' then b.date else null end)) as month2,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-03' then b.date else null end)) as month3,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-04' then b.date else null end)) as month4,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-05' then b.date else null end)) as month5,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-06' then b.date else null end)) as month6,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-07' then b.date else null end)) as month7,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-08' then b.date else null end)) as month8,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-09' then b.date else null end)) as month9,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-10' then b.date else null end)) as month10,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-11' then b.date else null end)) as month11,
count(DISTINCT (case when DATE_FORMAT(b.date,'%Y-%m')='2019-12' then b.date else null end)) as month12
from (select * from store_to_mifan_2019 where cid=291) s 
join ide.buyrecord b on b.cid=s.cid and b.sid=s.sid where b.date>='2019-1-1'  and  b.date<='2019-12-31' GROUP BY b.sid
) t on t.sid=s1.sid set s1.month1=t.month1,
s1.month2=t.month2,s1.month3=t.month3,s1.month4=t.month4,s1.month5=t.month5,s1.month6=t.month6,s1.month7=t.month7,
s1.month8=t.month8,s1.month9=t.month9,s1.month10=t.month10,s1.month11=t.month11,s1.month12=t.month12 where s1.cid=291;


#查询更改后的数据
select * from store_to_mifan_2019 where cid=291;