2.联表根据a表字段值修改b表字段updatecommunitytempasaINNERJOINhouserentasbona.hId=b.idseta.dbName='sofang20_oldhouse',a.tbName='houserent'WHEREa.saleRentType='rent';
4.嵌套子查询SELECTc.idfromsofang20_newhouse.communityascWHEREc.idnotin(SELECTa.idfromsofang20_newhouse.communityasaINNERJOINcommunityasbona.id=b.idwherea.name=b.name)
5.查询一个字段中是否包含另一个字段中的值SELECT*FROMbdcityinfoWHERELOCATE(cityName,bdcityName)>0orLOCATE(cityName,bddistrict)>0
6.mysql字符串截取查询selectc.id,c.name,c.fullname,c0.nameasn2fromcitycrightjoin(select*fromcity0wheredisabled=0and(SUBSTR(`code`FROM5FOR2)='00'andSUBSTR(`code`FROM3FOR4)<>'0000'orsubstring(`code`,1,4)in(selectsubstring(`code`,1,4)fromcity0where(substring(`code`,3,4)='9000')orcode='500200'))andsubstring(`code`,3,4)<>'9000'and`code`<>'500200')c0onc.fullname=c0.name;
7.同一个表查询指定字符串是否存在，存在修改UPDATEsofang20_house_2017.houserent_201703SETstate=0WHEREidIN(SELECTa.idfrom(SELECTidFROMsofang20_house_2017.houserent_201703WHEREstate=1AND(locate('升值潜力无限',title)>0ORlocate('商住两用',title)>0ORlocate('学区房',title)>0ORlocate('商住两用',title)>0ORlocate('买一层送一层',title)>0ORlocate('全款收房',title)>0ORlocate('升值潜力无限',`describe`)>0ORlocate('商住两用',`describe`)>0ORlocate('学区房',`describe`)>0ORlocate('商住两用',`describe`)>0ORlocate('买一层送一层',`describe`)>0))ASa);
7.找到指定字符串后替换成相应字符UPDATEhouserentSETtitle=REPLACE(title,'学区房','***'),`describe`=REPLACE(`describe`,'学区房','***')WHEREstate=1AND(locate('学区房',title)>0ORlocate('学区房',`describe`)>0)
8.批量修改楼盘信息sql#SELECTenterpriseshopIdfromsofang20_user.enterprisesWHEREid=(SELECTidFROMsofang20_user.usersWHEREmobile='15717502687');UPDATEsofang20_newhouse0.communityASaSETa.uId=(SELECTidFROMsofang20_user.usersWHEREmobile='15717502687'),a.enterpriseshopId=(SELECTenterpriseshopIdFROMsofang20_user.enterprisesWHEREid=(SELECTidFROMsofang20_user.usersWHEREmobile='15717502687'))WHEREa.idIN(SELECTb.idFROM(SELECTidFROMsofang20_newhouse.communityWHERE`name`IN('弘德西街好莱城','惠天然梅岭国际','佳兆业云顶梅溪湖','天祥水晶湾','奥克斯缔壹城','恩瑞御西湖','天麓尚层','金科世界城','阳光100凤凰街','洋湖时代广场','和顺洋湖壹号','正旺U公馆','裕天国际商汇中心','东能华府','湘江雅颂居','五江天街','万科魅力之城','新世界广场','金谷豪庭','香江红星家具建材博览中心','高岭国际商贸城','福晟钱隆国际','富兴世界金融中心','泊富国际广场''中茂城','新城国际花都','润和紫郡','澳海澜庭','澳海谷山府','润和又一城','悦禧国际山庄','金地三千府','富基世纪公园','荣盛岳麓峰景','金桥国际市场集群','澳海望洲府'))ASb);
UPDATEsofang20_newhouse.communityASaSETa.state=1,a.uId=(SELECTidFROMsofang20_user.usersWHEREmobile='15717502687'),a.enterpriseshopId=(SELECTenterpriseshopIdFROMsofang20_user.enterprisesWHEREid=(SELECTidFROMsofang20_user.usersWHEREmobile='15717502687'))WHEREa.idIN(SELECTb.idFROM(SELECTidFROMsofang20_newhouse.communityWHERE`name`IN('弘德西街好莱城','惠天然梅岭国际','佳兆业云顶梅溪湖','天祥水晶湾','奥克斯缔壹城','恩瑞御西湖','天麓尚层','金科世界城','阳光100凤凰街','洋湖时代广场','和顺洋湖壹号','正旺U公馆','裕天国际商汇中心','东能华府','湘江雅颂居','五江天街','万科魅力之城','新世界广场','金谷豪庭','香江红星家具建材博览中心','高岭国际商贸城','福晟钱隆国际','富兴世界金融中心','泊富国际广场''中茂城','新城国际花都','润和紫郡','澳海澜庭','澳海谷山府','润和又一城','悦禧国际山庄','金地三千府','富基世纪公园','荣盛岳麓峰景','金桥国际市场集群','澳海望洲府'))ASb);
11.按城市批量修改楼盘用户id和分销商idUPDATEsofang20_newhouse0.communityASaSETa.uId=(SELECTidFROMsofang20_user.usersWHEREmobile='18595253788'),a.enterpriseshopId=(SELECTenterpriseshopIdFROMsofang20_user.enterprisesWHEREid=(SELECTidFROMsofang20_user.usersWHEREmobile='18595253788')),a.auditState=100WHEREa.idIN(SELECTb.idFROM(SELECTidFROMsofang20_newhouse.communityWHEREcityId=49)ASb);
UPDATEsofang20_newhouse.communityASaSETa.uId=(SELECTidFROMsofang20_user.usersWHEREmobile='18688615858'),a.enterpriseshopId=(SELECTenterpriseshopIdFROMsofang20_user.enterprisesWHEREid=(SELECTidFROMsofang20_user.usersWHEREmobile='18688615858')),a.state=2WHEREa.idIN(SELECTb.idFROM(SELECTidFROMsofang20_newhouse.communityWHEREcityId=144)ASb);
ALTERTABLE`houserentimage_201708`ADDINDEX`ix_rentImage_filename`(`fileName`)USINGHASH;
UPDATEsofang20_newhouse.communitysetstate=1WHEREidin(SELECTa.idfrom(SELECTidfromsofang20_newhouse.communityWHEREstate=3)asa);
//修改楼盘户型特色解析UPDATEcommunityroomSETfeature=''WHEREidin(SELECTc.idfrom(SELECTa.idFROM`communityroom`asaLEFTJOINcommunityasbONa.communityId=b.idWHEREb.cityId=49anda.feature<>'')asc);//修改楼盘点评UPDATEcommunitycommentSET`comment`=''WHEREidin(SELECTc.idfrom(SELECTa.idFROM`communitycomment`asaLEFTJOINcommunityasbONa.communityId=b.idWHEREb.cityId=49anda.`comment`<>'')asc);
//聚合查询SELECTa.*,(SELECTcount(b.id)FROMbusinessareaasbWHEREb.cityAreaId=a.idandb.deleteState=1)asnumFROM(SELECTc.NAMEAScname,ca.NAME,ca.id,c.idAScidFROM`city`AS`c`LEFTJOIN`cityarea`AS`ca`ON`ca`.`cityId`=`c`.`id`WHERE`c`.`id`IN(0,1,101)AND`ca`.`deleteState`=1GROUPBY`ca`.`name`ORDERBY`ca`.`timeLocUpdate`DESC)ASa
/**批量更新字段**/1、.replaceinto批量更新replaceintotest_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y');（慎用，必须带主键）2.insertintotest_tbl(id,dr)values(1,'2'),(2,'3'),...(x,'y')onduplicatekeyupdatedr=values(dr);3.创建临时表，先更新临时表，然后从临时表中updatecreatetemporarytabletmp(idint(4)primarykey,drvarchar(50));insertintotmpvalues(0,'gone'),(1,'xx'),...(m,'yy');updatetest_tbl,tmpsettest_tbl.dr=tmp.drwheretest_tbl.id=tmp.id;4、使用mysql自带的语句构建批量更新UPDATEcategoriesSETdingdan=CASEidWHEN1THEN3WHEN2THEN4WHEN3THEN5END,title=CASEidWHEN1THEN'NewTitle1'WHEN2THEN'NewTitle2'WHEN3THEN'NewTitle3'ENDWHEREidIN(1,2,3)
//例子$display_order=array(1=>4,2=>1,3=>2,4=>3,5=>9,6=>5,7=>8,8=>9);
$ids=implode(',',array_keys($display_order));$sql="UPDATEcategoriesSETdisplay_order=CASEid";foreach($display_orderas$id=>$ordinal){$sql.=sprintf("WHEN%dTHEN%d",$id,$ordinal);}$sql.="ENDWHEREidIN($ids)";echo$sql;
//if语句SELECTid,IF(LENGTH(roomStr)=7,CONCAT(substring(roomStr,1,3),substring(roomStr,6,2),substring(roomStr,4,2)),CONCAT(substring(roomStr,1,3),substring(roomStr,6,2),substring(roomStr,4,2),substring(roomStr,8)))FROMsofang20_house_2018.housesale_201803WHEREtpId=0ANDLENGTH(roomStr)>=7ANDUNIX_TIMESTAMP(timeCreate) UPDATEhousesale_201803SETroomStr=IF(LENGTH(roomStr)=7,CONCAT(substring(roomStr,1,3),substring(roomStr,6,2),substring(roomStr,4,2)),CONCAT(substring(roomStr,1,3),substring(roomStr,6,2),substring(roomStr,4,2),substring(roomStr,8)))WHEREtpId=0ANDLENGTH(roomStr)>=7ANDUNIX_TIMESTAMP(timeCreate) 说明：给主机为192.168.10.1的用户john分配可对数据库test所有表进行所有操作的权限，并设定口令为123。grantallprivilegeson*.*tojohn@192.168.10.1identifiedby'123'; 说明：给主机为192.168.10.1的用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。grantallprivilegeson*.*tossoUser@localhostidentifiedby'MxdpiLEjdfk2VsrG'; #sql语句导出csv文件 mysql-hphpad_db_mysql_offline_s01-P3306--default-character-set=gbk-ulinkstars_user-plinkstarsdb-e"selecta.goods_id,a.goods_name,sum(a.goods_count)ascount,sum(a.goods_count*a.goods_price),sum(a.commission_real),a.commission_type,a.commission_type_level_2,a.commission_type_level_3fromcps_order_detail_2018_1asainnerjoincps_order_2018_1asbona.order_id=b.idanda.adv_uid=600233anda.uid=600223anda.goods_namelike'%床垫%'anda.order_time>=UNIX_TIMESTAMP('2018-01-0100:00:00')anda.order_time