近期参与了某网站开发,设计到大量的数据导入,记录一下几个比较重要的数据操作语句。
1. case when
update changepatent set patendId =(case when patenName like '专利%' then 1 when patenName like '%软件著作权%' then 6 else 2 end)
2.导入的数据中有重复的项,让storeid取重复项的最小值
update changestore a set storeid = (select id from (SELECT cg_unitname,min(storeid) id from changestore group by cg_unitname ) b where b.cg_unitname=a.cg_unitname)
3.两张表中的相同名字的id相等
update t_user,changestore set t_user.store_id=changestore.storeid where t_user.userName=changestore.username and t_user.id >= 2 AND t_user.id <= 2386
4.全国省份的case when
UPDATE changearea SET areaid = (
CASE WHEN area LIKE '%北京%' THEN 4521984
WHEN area LIKE '%天津%' THEN 4522003
WHEN area LIKE '%河北%' THEN 4522022
WHEN area LIKE '%山西%' THEN 4522217
WHEN area LIKE '%内蒙%' THEN 4522359
WHEN area LIKE '%辽宁%' THEN 4522482
WHEN area LIKE '%吉林%' THEN 4522611
WHEN area LIKE '%黑龙江%' THEN 4522689
WHEN area LIKE '%上海%' THEN 4522847
WHEN area LIKE '%江苏%' THEN 4522868
WHEN area LIKE '%浙江%' THEN 4523000
WHEN area LIKE '%安徽%' THEN 4523113
WHEN area LIKE '%福建%' THEN 4523253
WHEN area LIKE '%江西%' THEN 4523357
WHEN area LIKE '%山东%' THEN 4523480
WHEN area LIKE '%河南%' THEN 4523655
WHEN area LIKE '%湖北%' THEN 4523850
WHEN area LIKE '%湖南%' THEN 4523980
WHEN area LIKE '%广东%' THEN 4524130
WHEN area LIKE '%广西%' THEN 4524292
WHEN area LIKE '%海南%' THEN 4524431
WHEN area LIKE '%重庆%' THEN 4524460
WHEN area LIKE '%四川%' THEN 4524503
WHEN area LIKE '%贵州%' THEN 4524724
WHEN area LIKE '%云南%' THEN 4524825
WHEN area LIKE '%西藏%' THEN 4524979
WHEN area LIKE '%陕西%' THEN 4525061
WHEN area LIKE '%甘肃%' THEN 4525189
WHEN area LIKE '%青海%' THEN 4525302
WHEN area LIKE '%宁夏%' THEN 4525355
WHEN area LIKE '%新疆%' THEN 4525388
end
)
5.让一张表的某字段与另一张表的字段相匹配concat(),用到数据库的关键字时,用``包住,如t_area.`level`=1
SELECT t_enterprise.qy_addr,t_area.id FROM t_enterprise,t_area
WHERE (t_enterprise.qy_addr LIKE concat('%',t_area.areaName,'%')) AND (t_area.`level`=1)
总结:经过这次数据的导入导出、数据库的操作,对navicat有了一定的了解,对mysql的操作进一步加深。