查看用户

select site_id,user_id,user_name,user_email,
  user_status,user_group,stat_main,stat_reply,stat_score,
  user_remark,entry_when,entry_ip
from ty_user

更换站点ID时用以下sql执行

update ty_main set site_id=141 where site_id=124;
update ty_forum set site_id=141 where site_id=124;
update ty_user set site_id=141 where site_id=124;
update ty_log set site_id=141 where site_id=124;
update ty_act set site_id=141 where site_id=124;
update ty_tag set site_id=141 where site_id=124;

用户名称修改：

update ty_main set user_name=NewUseName where user_id=UserID;
update ty_post set user_name=NewUseName where user_id=UserID;
update ty_user set user_name=NewUseName where user_id=UserID;
update ty_log set user_name=NewUseName where user_id=UserID;
update ty_act set user_name=NewUseName where user_id=UserID;
update ty_tag set user_name=NewUseName where user_id=UserID;

--网站统计

--显示每天统计条数
select stat_date,count(*) from ty_stat 
where site_id=141 
 and stat_date>'20230407'
group by stat_date

--显示某天统计条数
select count(*) from ty_stat 
where site_id=141 and stat_date='20230410'

--查询统计详细
select stat_id,site_id,stat_date,stat_time,from_ip,from_lang,from_os,to_url,main_title
from ty_stat 
where site_id=141
--and stat_id>=53368
and stat_date>'20230410'
order by stat_id desc limit 100;

--查看高频ip，可以加入ip库过滤掉
select from_ip,ipqty from (
select from_ip,count(*) as ipqty from ty_stat where stat_date>'20230409' group by from_ip
) dd
where from_ip not in (select ip_adress from ty_iplib where ip_flag in (1,7))
order by ipqty desc;

--tag更新

--更新主题表tag
update ty_main set main_tag=replace(main_tag, '读书无用论' , '大学无用论')
where main_tag like '%读书无用论%';
--查看tag是否有新tag
select tag_id,site_id,tag_name,data_flag from ty_tag
where tag_name='大学无用论';
--更新Tag表
update ty_tag set tag_name='大学无用论';
where tag_name='读书无用论';

--更新版块名

update ty_main m set forum_alias=
(select forum_alias from ty_forum f where f.site_id=m.site_id and f.forum_id=m.forum_id)
where m.site_id=101;

update ty_main m set forum_name=
(select forum_name from ty_forum f where f.site_id=m.site_id and f.forum_id=m.forum_id)
where m.site_id=101;

--更换版块

update ty_main set forum_id=106,forum_name='杂记',forum_alias='other'
where forum_id=144;

update ty_post set forum_id=106 where forum_id=144;

update ty_tag set forum_id=106,forum_name='杂记'
where forum_id=144;

--更新版块统计（主题和评论数）

update ty_forum f set stat_main=
(select count(*) from ty_main m where f.forum_id=m.forum_id)
where f.site_id=101;

update ty_forum f set stat_reply=
(select count(*) from ty_post m where f.forum_id=m.forum_id and post_flag='0')
where f.site_id=101;

--文章修改站点、版块和用户

update ty_main set site_id=xxx,user_id=xxx,user_name='xxx',forum_id=xxx,forum_name='xxx',forum_alias='xxx'
where main_code='xxx';

update ty_post set user_id=xxx,user_name='xxx',forum_id=xxx
where main_id=(select main_id from ty_main where main_code='xxx');

update ty_log set site_id=xxx,user_id=xxx,user_name='xxx' where main_id=(select main_id from ty_main where main_code='xxx');