mysql数据库操作

来源：互联网发布：淘宝买家退货卖家拒收编辑：程序博客网时间：2024/05/29 18:54

近期参与了某网站开发，设计到大量的数据导入，记录一下几个比较重要的数据操作语句。

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的操作进一步加深。

0 0