风萧 发表于 2026-7-17 10:50:00

论坛后台数据常用查询语句汇总

【批量查询论坛会员邮箱、用户组等资料】
【批量查询论坛道具信息】
【批量设定道具属性】

风萧 发表于 2026-7-17 10:50:28

批量查询论坛会员邮箱、用户组等资料

SELECT
a1.`uid` AS '用户ID',
a1.`loginname` AS '登录名',
a1.`username` AS '用户名',
CASE
    WHEN a5.`gender` = '1' THEN '男'
    WHEN a5.`gender` = '2' THEN '女'
    ELSE '保密'
END AS '性别',
a1.`email` AS '注册邮箱',
CASE
    WHEN a1.`emailstatus` = '1' THEN '已验证'
    ELSE '未验证'
END AS '是否验证',
a2.`grouptitle` AS '用户组',
CASE
    WHEN a1.`adminid` NOT IN (1, 2, 3) THEN '空'
    ELSE a3.`grouptitle`
END AS '管理权限组',
FROM_UNIXTIME(a1.regdate, '%Y-%m-%d %H:%i:%s') AS '注册时间',
a4.`regip` AS '注册IP',
a4.`lastip` AS '最后登录IP',
FROM_UNIXTIME(a4.`lastvisit`, '%Y-%m-%d %H:%i:%s') AS '最后登录时间'
FROM
pre_common_member a1
LEFT JOIN pre_common_usergroup a2
ON a1.`groupid` = a2.`groupid`
LEFT JOIN pre_common_usergroup a3
ON a1.`adminid` = a3.`groupid`
LEFT JOIN pre_common_member_status a4
ON a1.`uid` = a4.`uid`
LEFT JOIN pre_common_member_profile a5
ON a1.`uid` = a5.`uid`;

风萧 发表于 2026-7-17 11:31:56

批量查询论坛道具信息

SELECT
aa.`displayorder` AS '列序',
aa.`magicid` AS '道具ID',
aa.`name` AS '道具名',
aa.`description` AS '道具说明',
CASE
    WHEN aa.`credit` = '1' THEN '威望'
    WHEN aa.`credit` = '2' THEN '灵石'
    WHEN aa.`credit` = '3' THEN '灵晶'
    ELSE '其他'
END AS '积分类型',
aa.`price` AS '价格',
CASE
    WHEN aa.`supplytype` = '1' THEN '每天补货'
    WHEN aa.`supplytype` = '2' THEN '每周补货'
    WHEN aa.`supplytype` = '3' THEN '每月补货'
    ELSE '不补货'
END AS '补货周期',
aa.`num` AS '库存数',
aa.`supplynum` AS '补货数',
aa.`weight` AS '重量',
CASE
    WHEN aa.`available` = '1' THEN '启用'
    ELSE '未启用'
END AS '是否启用'
FROM pre_common_magic aa
ORDER BY aa.`displayorder` LIMIT 5000

风萧 发表于 2026-7-17 11:38:39

批量设定道具属性的SQL语句

/*批量设置所有道具库存为999*/
    UPDATE pre_common_magic SET num = 999;
/*批量设置所有道具自动补货999个*/
    UPDATE pre_common_magic SET supplynum = 999;
/*批量设置所有道具自动补货周期为每天（0不补货，1每天，2每周，3每月）*/
    UPDATE pre_common_magic SET supplytype = 3;
/*批量设置所有道具重量为1*/
    UPDATE pre_common_magic SET weight = 1;
/*批量设置所有道具排列数值为100*/   
    UPDATE pre_common_magic SET displayorder = 100;
/*批量设置所有道具积分类型（1-8 对应积分规则）*/
    UPDATE pre_common_magic SET credit = 2
/*批量设置已启用道具自动补货周期为每天（0不补货，1每天，2每周，3每月）*/
UPDATE pre_common_magic SET supplytype = 0 WHERE available IN ('1')

萧凌月 发表于 2026-8-13 19:04:02

版块名称信息查询语句

SELECT
a1.`fid` AS '版块ID',
a1.`name` AS '版块名称',
CASE
    WHEN a1.`fup` = '0' THEN '无'
    ELSE a2.`name`
END AS '上级版块',
CASE
    WHEN a1.`type` = 'group' THEN '分类版块'
    WHEN a1.`type` = 'forum' THEN '普通版块'
    WHEN a1.`type` = 'sub' THEN '子版块'
    ELSE '其他'
END AS '版块类型',
CASE
    WHEN a1.`status` = '0' THEN '隐藏'
    WHEN a1.`status` = '1' THEN '正常'
    WHEN a1.`status` = '3' THEN '群组'
    ELSE '其他'
END AS '版块状态'
FROM
pre_forum_forum a1
LEFT JOIN pre_forum_forum a2
    ON a1.`fup` = a2.`fid`
页: [1]
查看完整版本: 论坛后台数据常用查询语句汇总