Java优学网MySQL更新数据教程：轻松掌握UPDATE语句与事务处理

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

UPDATE user_profile SET username = 'new_username',

bio = '热爱编程的开发者',
updated_at = CURRENT_TIMESTAMP 

WHERE user_id = 15034;

UPDATE employees e JOIN departments d ON e.department_id = d.id SET e.salary = e.salary * 1.1 WHERE d.budget_status = '充足' AND e.performance_rating >= 4;

-- 检查当前用户权限 SHOW GRANTS FOR CURRENT_USER;

public class GradeUpdateService {

private Connection connection;

public boolean updateStudentGrade(int studentId, int courseId, 
                                 double newScore, String operator) {
    String checkSql = "SELECT COUNT(*) FROM student_courses WHERE student_id = ? AND course_id = ?";
    String updateSql = "UPDATE student_courses SET score = ?, updated_at = NOW(), updated_by = ? WHERE student_id = ? AND course_id = ?";
    String logSql = "INSERT INTO grade_update_logs (student_id, course_id, old_score, new_score, operator, updated_at) VALUES (?, ?, ?, ?, ?, NOW())";
    
    try {
        connection.setAutoCommit(false);
        
        // 检查记录是否存在
        PreparedStatement checkStmt = connection.prepareStatement(checkSql);
        checkStmt.setInt(1, studentId);
        checkStmt.setInt(2, courseId);
        ResultSet rs = checkStmt.executeQuery();
        rs.next();
        if (rs.getInt(1) == 0) {
            throw new SQLException("选课记录不存在");
        }
        
        // 获取旧成绩用于日志记录
        double oldScore = getCurrentScore(studentId, courseId);
        
        // 执行成绩更新
        PreparedStatement updateStmt = connection.prepareStatement(updateSql);
        updateStmt.setDouble(1, newScore);
        updateStmt.setString(2, operator);
        updateStmt.setInt(3, studentId);
        updateStmt.setInt(4, courseId);
        int affectedRows = updateStmt.executeUpdate();
        
        if (affectedRows > 0) {
            // 记录操作日志
            PreparedStatement logStmt = connection.prepareStatement(logSql);
            logStmt.setInt(1, studentId);
            logStmt.setInt(2, courseId);
            logStmt.setDouble(3, oldScore);
            logStmt.setDouble(4, newScore);
            logStmt.setString(5, operator);
            logStmt.executeUpdate();
            
            connection.commit();
            return true;
        }
        
        connection.rollback();
        return false;
        
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException rollbackEx) {
            rollbackEx.printStackTrace();
        }
        throw new RuntimeException("成绩更新失败", e);
    }
}

private double getCurrentScore(int studentId, int courseId) throws SQLException {
    String sql = "SELECT score FROM student_courses WHERE student_id = ? AND course_id = ?";
    PreparedStatement stmt = connection.prepareStatement(sql);
    stmt.setInt(1, studentId);
    stmt.setInt(2, courseId);
    ResultSet rs = stmt.executeQuery();
    return rs.next() ? rs.getDouble("score") : 0.0;
}

}