不常用数据sql库命令操作

//添加表字段
alter table $table add $field int null default 0 comment "";

//修改表字段信息
alter table $table modify column $field bigint(15) default null comment "";

//修改数据库表字段编码
alter database <数据库名> character set utf8;
alter table <表名> character set utf8;
alter table $table change $field $field varchar(20) character set utf8 not null;

能用change重命名，而modify不能

.查看数据表的编码格式

mysql> show create table <表名>;

导出：
mysqldump -uroot -prs2019newcms rscms>/rs2019220.sql

导入
常用source 命令
进入mysql数据库控制台，如
mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

字段内容替换
#select * from akr_infolist_content where content like '%&tp=webp%';
update akr_infolist_content set content=replace(content,'&tp=webp','&tp=jpg');
update akr_infolist set turnurl=REPLACE(turnurl,'http://sjtzb.pzhgd.com/home.php','http://sjtzb.pzhgd.com:89/home.php') where turnurl!='';

 
//两张表中得其中一个字段值  赋值给另一个表其中一个字段  
update akr_agent_record,akr_user set akr_agent_record.agent_cod = akr_user.agent where akr_agent_record.userid = akr_user.uid;

#查询存在‘泥猴’相关标题 修改 akr_info 表existkey 字段值
update akr_info set existkey=1 where infoid in (select infoid from(select infoid from akr_info where title like "%泥猴%") as A);

//修改字段值为另一张表查询统计结果   
update akr_infolist as A,(select count(id) as counts,targetid from akr_cms_viewrecord where type=0 and `from`='app' group by targetid) as B set A.waphits=B.counts where A.infoid=B.targetid

//查询数据库中去掉重复得数据信息

select username,nickname,mobile,is3rdlogin,locked,regtime from akr_user where username!="" || uid in(select maxuid from (select max(uid) as maxuid,nickname from akr_user where nickname!="" group by nickname order by uid desc) as tmp);

//更新字段值为其他表统计

update akr_special_contenttype set rnum=(select count(id) from akr_special_r where ctypeid=akr_special_contenttype.ctypeid);