Java优学网MySQL更新数据教程：轻松掌握高效数据更新技巧，告别慢查询烦恼

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2 WHERE 条件;

// 更新用户最后登录时间和IP String sql = "UPDATE users SET last_login = NOW(), login_ip = ?, login_count = login_count + 1 WHERE user_id = ?";

PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, ipAddress); pstmt.setInt(2, userId);

int affectedRows = pstmt.executeUpdate(); System.out.println("更新了 " + affectedRows + " 行数据");

String sql = "UPDATE users SET status = 'ACTIVE' WHERE user_id = ? AND status = 'PENDING'"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 12345);

int rows = pstmt.executeUpdate(); // 返回0，但用户确实存在

-- 没有索引时，更新10万用户需要8秒 UPDATE user_points SET points = points + 100 WHERE last_login_date > '2024-01-01';

-- 给last_login_date加上索引后，只需要0.3秒 CREATE INDEX idx_login_date ON user_points(last_login_date);

UPDATE products SET sale_price = CASE

WHEN category = 'ELECTRONICS' AND member_level = 'GOLD' THEN price * 0.8
WHEN category = 'ELECTRONICS' AND member_level = 'SILVER' THEN price * 0.85
WHEN category = 'CLOTHING' AND member_level = 'GOLD' THEN price * 0.7
WHEN category = 'CLOTHING' AND member_level = 'SILVER' THEN price * 0.75
ELSE price * 0.9

END, status = CASE

WHEN stock_quantity = 0 THEN 'OUT_OF_STOCK'
WHEN stock_quantity < 10 THEN 'LOW_STOCK' 
ELSE 'IN_STOCK'

END WHERE active = 1;