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

风萧

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

风萧

1. SELECT
   
2.   a1.`uid` AS '用户ID',
   
3.   a1.`loginname` AS '登录名',
   
4.   a1.`username` AS '用户名',
   
5.   CASE
   
6.     WHEN a5.`gender` = '1' THEN '男'
   
7.     WHEN a5.`gender` = '2' THEN '女'
   
8.     ELSE '保密'
   
9.   END AS '性别',
   
10.   a1.`email` AS '注册邮箱',
    
11.   CASE
    
12.     WHEN a1.`emailstatus` = '1' THEN '已验证'
    
13.     ELSE '未验证'
    
14.   END AS '是否验证',
    
15.   a2.`grouptitle` AS '用户组',
    
16.   CASE
    
17.     WHEN a1.`adminid` NOT IN (1, 2, 3) THEN '空'
    
18.     ELSE a3.`grouptitle`
    
19.   END AS '管理权限组',
    
20.   FROM_UNIXTIME(a1.regdate, '%Y-%m-%d %H:%i:%s') AS '注册时间',
    
21.   a4.`regip` AS '注册IP',
    
22.   a4.`lastip` AS '最后登录IP',
    
23.   FROM_UNIXTIME(a4.`lastvisit`, '%Y-%m-%d %H:%i:%s') AS '最后登录时间'
    
24. FROM
    
25.   pre_common_member a1
    
26. LEFT JOIN pre_common_usergroup a2
    
27.   ON a1.`groupid` = a2.`groupid`
    
28. LEFT JOIN pre_common_usergroup a3
    
29.   ON a1.`adminid` = a3.`groupid`
    
30. LEFT JOIN pre_common_member_status a4
    
31.   ON a1.`uid` = a4.`uid`
    
32. LEFT JOIN pre_common_member_profile a5
    
33.   ON a1.`uid` = a5.`uid`;

复制代码

风萧

1. SELECT
   
2.   aa.`displayorder` AS '列序',
   
3.   aa.`magicid` AS '道具ID',
   
4.   aa.`name` AS '道具名',
   
5.   aa.`description` AS '道具说明',
   
6.   CASE
   
7.     WHEN aa.`credit` = '1' THEN '威望'
   
8.     WHEN aa.`credit` = '2' THEN '灵石'
   
9.     WHEN aa.`credit` = '3' THEN '灵晶'
   
10.     ELSE '其他'
    
11.   END AS '积分类型',
    
12.   aa.`price` AS '价格',
    
13.   CASE
    
14.     WHEN aa.`supplytype` = '1' THEN '每天补货'
    
15.     WHEN aa.`supplytype` = '2' THEN '每周补货'
    
16.     WHEN aa.`supplytype` = '3' THEN '每月补货'
    
17.     ELSE '不补货'
    
18.   END AS '补货周期',
    
19.   aa.`num` AS '库存数',
    
20.   aa.`supplynum` AS '补货数',
    
21.   aa.`weight` AS '重量',
    
22.   CASE
    
23.     WHEN aa.`available` = '1' THEN '启用'
    
24.     ELSE '未启用'
    
25.   END AS '是否启用'
    
26. FROM pre_common_magic aa
    
27. ORDER BY aa.`displayorder` LIMIT 5000

复制代码

风萧

1. /*批量设置所有道具库存为999*/
   
2.     UPDATE pre_common_magic SET num = 999;
   
3. /*批量设置所有道具自动补货999个*/
   
4.     UPDATE pre_common_magic SET supplynum = 999;
   
5. /*批量设置所有道具自动补货周期为每天（0不补货，1每天，2每周，3每月）*/
   
6.     UPDATE pre_common_magic SET supplytype = 3;
   
7. /*批量设置所有道具重量为1*/
   
8.     UPDATE pre_common_magic SET weight = 1;
   
9. /*批量设置所有道具排列数值为100*/   
   
10.     UPDATE pre_common_magic SET displayorder = 100;
    
11. /*批量设置所有道具积分类型（1-8 对应积分规则）*/
    
12.     UPDATE pre_common_magic SET credit = 2

复制代码

1. /*批量设置已启用道具自动补货周期为每天（0不补货，1每天，2每周，3每月）*/
   
2. UPDATE pre_common_magic SET supplytype = 0 WHERE available IN ('1')

复制代码

萧凌月

1. SELECT
   
2.   a1.`fid` AS '版块ID',
   
3.   a1.`name` AS '版块名称',
   
4.   CASE
   
5.     WHEN a1.`fup` = '0' THEN '无'
   
6.     ELSE a2.`name`
   
7.   END AS '上级版块',
   
8.   CASE
   
9.     WHEN a1.`type` = 'group' THEN '分类版块'
   
10.     WHEN a1.`type` = 'forum' THEN '普通版块'
    
11.     WHEN a1.`type` = 'sub' THEN '子版块'
    
12.     ELSE '其他'
    
13.   END AS '版块类型',
    
14.   CASE
    
15.     WHEN a1.`status` = '0' THEN '隐藏'
    
16.     WHEN a1.`status` = '1' THEN '正常'
    
17.     WHEN a1.`status` = '3' THEN '群组'
    
18.     ELSE '其他'
    
19.   END AS '版块状态'
    
20. FROM
    
21.   pre_forum_forum a1
    
22.   LEFT JOIN pre_forum_forum a2
    
23.     ON a1.`fup` = a2.`fid`

复制代码