They allow you to get the behavior of those types, while keeping it a RigidBody, allowing you the option to override its _integrate_forces()
Static and Kinematic are as you would expect, Character behaves mostly like a RigidBody, but stops angular rotations, so your character can stay upright when moving up slopes.