本文已参与【新人创作礼】活动，一起开启掘金传作之礼。
#更改金豆区 UPDATE ACTIVITY_TEMPLATE set AREA=1 WHERE ID=1;
#修改活动时间 ：根据id修改活动表的早、中、晚时间
UPDATE ACTIVITY_TEMPLATE SET MORNING_START = '12:11:00', MORNING_END = '12:59:00', NOON_START = '13:00:00', NOON_END = '13:59:00', EVENING_START = '14:00:00', EVENING_END = '14:30:00' WHERE ID = 1;
#设置某个活动结束
UPDATE ACTIVITY_INFO SET STATUS = '3' WHERE ID =362;
UPDATE activity_info SET GMT_START = '2021-08-26 04:47:00', GMT_END = '2021-08-26 04:59:00' WHERE ID = 226;
#设置用户的注册时间 UPDATE auction_member_info SET created_time = NOW() WHERE mobile_or_email = '402956610785251328' ; UPDATE auction_member_info SET created_time = '2021-07-01 09:24:41' WHERE user_id = '402956610785251328' ;
#修改登陆密码-所有用户
UPDATE auction_member_user SET password = MD5( CONCAT( salt, 'a1234567' ) ) WHERE email = '2@sina.com';
#修改登陆密码--实名过的用户
UPDATE auction_member_user u SET u.password = MD5( CONCAT( u.salt, 'a1234567' ) ) WHERE u.salt IS NOT NULL AND EXISTS ( SELECT 1 FROM auction_user_kyc k WHERE k.user_id = u.user_id ) AND u.email = '2@sina.com';
#设置实名 UPDATE auction_user_kyc SET verified_status = '1' WHERE updated_time > '2021-07-19 18:00';
#设置用户银豆资产 UPDATE auction_member_info SET silver_account = account+'10000'; UPDATE auction_member_info SET silver_account = account+'500000' WHERE user_id = '40295';\
#查询模板设置的活动时间 SELECT * FROM ACTIVITY_TEMPLATE;
#查询当前场次的活动时间 SELECT * FROM ACTIVITY_INFO ORDER BY id DESC;
#根据字段的枚举值查询对应的文字状态
SELECT id,(CASE TEMPLATE_ID WHEN 1 THEN '商品区' ELSE '通证' END) AS 'TEMPLATE_ID',SESSION_NO,NAME,(CASE STATUSWHEN 1 THEN '未开始' WHEN 2 THEN '进行中' WHEN 3 THEN '已结束' WHEN 4 THEN'已删除' END ) AS 'STATUS',GMT_START,GMT_END FROM ACTIVITY_INFO ORDER BY id DESC LIMIT 10; SELECT id,NAME,GMT_START,GMT_END,STATUS FROM ACTIVITY_INFO where status in (1,2) ORDER BY id DESC; SELECT id,NAME,GMT_START,GMT_END,STATUS FROM ACTIVITY_INFO WHERE NAME = '通证区通用模板' ORDER BY id DESC;
#可以抢购的用户查询 select * from auction_member_info t where t.account > 10000 and t.silver_account > 100 and exists (select 1 from auction_user_kyc a where a.user_id = t.user_id) and exists (select 1 from auction_member_asset b where b.user_id = t.user_id) and exists (select 1 from auction_payment_archive c where c.user_id = t.user_id) LIMIT 100;
#查找市场上的银豆资产-去除主席账户、测试账户 SELECT SUM(silver_account) FROM auction_member_info WHERE user_id NOT IN ( '402514206743597056', '402956054826061824' );
#查找用户的实名信息-手机号码 select k.first_name,m.USER_ID,m.phone_number,m.email,m.account,m.silver_account,m.created_time from auction_member_info m,auction_user_kyc k where m.user_id = k.user_id AND m.phone_number IN( 12853638518, 12845368875 );
#查找金豆用户的数量 SELECT COUNT(user_id) FROM auction_member_info WHERE account >10000;
#查询用户金豆的总收入、总支出-手机号码 select user_id ,账户,现有资产,总收入,总支出 , 总收入 - 总支出 as 余额 ,总收入 - 总支出-现有资产 as 价差 from (SELECT m.user_id ,m.phone_number '账户',m.account '现有资产' , ( SELECT sum(h.deal_quantity) FROM auction_account_history h where h.user_id = m.user_id and h.deal_type IN ( '1', '2', '3', '4', '8', '10', '13', '17', '0', '9', '16', '20' ) ) '总收入' , ( SELECT sum(h.deal_quantity) FROM auction_account_history h where h.user_id = m.user_id and h.deal_type IN ( '11', '5', '6', '7', '12', '14', '15', '18', '19' ) ) '总支出' FROM auction_member_info m WHERE m.phone_number = '12859025376' ) t;
#查看用户金豆流水记录 #转账状态（deal_type）:'0.分红 1.直推奖 2.团队奖 3.平级奖 4.购买 5.转出 6.消耗 7.解锁 8.补偿 9.退回10.赠送 11.分佣 12.新用户赠送消耗 13.兑换 14.兑换消耗 15.通证燃料费消耗 16.通证金豆收益 17.平台补贴 18.补贴消耗 19.买入支出 20.卖出收入' SELECT * FROM auction_account_history WHERE user_id = '403609772747657216' ORDER BY created_time DESC;
#查看金豆资金流水-手机 SELECT h.id, (SELECT phone_number FROM auction_member_info WHERE user_id=h.deal_object_id ) AS '操作补给账号', (SELECT user_id FROM auction_member_info WHERE user_id=h.user_id ) AS 'userId', (SELECT phone_number FROM auction_member_info WHERE user_id=h.user_id ) AS '用户账号', (CASE h.deal_type WHEN 0 THEN '分红' WHEN 1 THEN '直推奖' WHEN 2 THEN '团队奖' WHEN 3 THEN '平级奖' WHEN 4 THEN '购买' WHEN 5 THEN '转出' WHEN 6 THEN '消耗' WHEN 7 THEN '解锁' WHEN 8 THEN '补偿' WHEN 9 THEN '退回' WHEN 10 THEN '赠送' WHEN 11 THEN '分佣' WHEN 12 THEN '新用户赠送消耗' WHEN 13 THEN '兑换' WHEN 14 THEN '兑换消耗' WHEN 15 THEN '通证燃料费消耗' WHEN 16 THEN '通证金豆收益' WHEN 17 THEN '平台补贴' WHEN 18 THEN '补贴消耗' WHEN 19 THEN '买入支出' WHEN 20 THEN '卖出收入' ELSE '' END) AS '交易类型', h.deal_quantity '数量',h.created_time '操作时间',h.last_account '余额' FROM auction_account_history h,auction_member_info m WHERE m.user_id = h.user_id AND m.phone_number = '18959025376' ORDER BY h.created_time DESC;
