MySQL数据库update语句讲解

SELECT * FROM vip_config;
SELECT * FROM player;
SELECT * FROM player_vip_infoplayer;

-- 连接查询------------------------------------------------
SELECT player_id, player_vip_info.vip_lv, vip_end_time, vip_config.vip_auto_battle
FROM player_vip_info
JOIN player ON player_vip_info.player_id = player.id
JOIN vip_config ON player_vip_info.vip_lv = vip_config.vip_lv
WHERE vip_end_time > UNIX_TIMESTAMP(NOW());

UPDATE player SET remain_auto_times = 300
SELECT UNIX_TIMESTAMP(NOW()) 1331016064

-- 联合更新方式1
UPDATE test1,test2 SET test1.id = test2.id WHERE test1.oid = test2.id AND test2.ooid = "XXX"

-- 联合更新方式2

UPDATE test1 t1
JOIN test2 t2
    ON t1.id = t2.id AND t2.oId = 54
SET t1.ooid = 54

-- vip更新方式
UPDATE player_vip_info
JOIN player ON player_vip_info.player_id = player.id
JOIN vip_config ON player_vip_info.vip_lv = vip_config.vip_lv
SET player.remain_auto_times = player.remain_auto_times + vip_config.vip_auto_battle
WHERE vip_end_time > UNIX_TIMESTAMP(NOW());

DROP PROCEDURE reset_player_auto_battle_times_for_vip;
DELIMITER $$

CREATE
   
    PROCEDURE `reset_player_auto_battle_times_for_vip`()
   
    BEGIN
    SET @current_timestamp_a = UNIX_TIMESTAMP(NOW());
    UPDATE player_vip_info
    JOIN player ON player_vip_info.player_id = player.id
    JOIN vip_config ON player_vip_info.vip_lv = vip_config.vip_lv
    SET player.remain_auto_times = player.remain_auto_times + vip_config.vip_auto_battle
    WHERE vip_end_time > @current_timestamp_a ;
    END$$

DELIMITER ;