多表联查

select a.typeid, a.reporterid, b.typename, c.name from `fsosu_article` as a inner join `fsosu_type` as b on(a.typeid=b.typeid) inner join `fsosu_reporter` as c on(c.id=a.reporterid) where c.status=1 

复制一个字段内容到另一字段

update `fsosu_article` set title2=title

随机更新字段中的值

update `fsosu_article` set rank=FLOOR(RAND()*100)

inner join(等值连接)：只返回两个表中联结字段相等的行；
left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录；
right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录。

select a.title,a.`pubdate`,a.flag,b.body,c.typename from `zhnanke_archives` as a inner join `zhnanke_addonarticle` as b on a.id=b.aid  inner join `zhnanke_arctype` as c on a.typeid=c.id where a.ismake='0' 

设置表的字段自动增长初始值

alter table massage AUTO_INCREMENT=1

快速在织梦的数据库中加入一个管理员，密码只有我知道

INSERT INTO `#@_admin` (`id`, `usertype`, `userid`, `pwd`, `uname`, `tname`, `email`, `typeid`, `logintime`, `loginip`) VALUES (1, 10, 'xiaoming', '47da5275fa7228e7f27b', 'xiaoming', 'xiaoming', '332719434@qq.com', '', 1330652268, '61.145.121.154');

批量替换字符串：

update `yue39_com_arctype` set `typedir`=REPLACE(typedir,'{cmspath}','') where `typedir` like "%{cmspath}%"

在原有内容上更新：

update `fuke020_com_arctype` set `sitepath`= concat("{cmspath}",sitepath) where `sitepath` not Like "%{cmspath}%"

update `fuke999cn_channeltype` set `maintable`=REPLACE(`maintable`,'fuke999com_','fuke999cn_'), `addtable`=REPLACE(`addtable`,'fuke999com_','fuke999cn_') 

/*两个表之间不关联数据*/

select * from fuke999net_archives where not exists(select * from fuke999net_addonarticle where fuke999net_addonarticle.aid=fuke999net_agk_fuke999_netrchives.id);

1。表结构相同的表，且在同一数据库（如，table1,table2)

Sql ：insert into table1 select * from table2 (完全复制)

      insert into table1 select distinct * from table2(不复制重复纪录）

       insert into table1 select top 5 * from table2 (前五条纪录)

2。   不在同一数据库中（如，db1 table1,db2 table2)

sql:    insert into db1..table1 select * from db2..table2 (完全复制)

        insert into db1..table1 select distinct * from db2table2(不复制重复纪录）

         insert into tdb1..able1 select top 5 * from   db2table2 (前五条纪录)

打赏

微信扫一扫，打赏作者吧～

本文固定链接: https://www.cxy163.net/archives/222 | 绝缘体