Composite video is the familiar connection to red, white, and yellow cables with RCA-type jacks that carries left and right audio and standard definition video. They are used to connect DVDs and VCRs, but are not used with high-definition digital streams.
Component video is similar to composite, but it uses three cables to carry the video - one for luminance and two for color (or chrominance). This was fashionable for a while, mainly with large wide-screen TVs, but the advent of HDMI has largely removed the need for component video.
S-video is an improvement on composite inputs. S-Video input is desired as it's the best way to get a good signal from a standard DVD player. S-video contains one conductor for luminance and one for color.
Since S-video is a common connection found on standard DVD players, it's good to have at least one input and one output on an A/V receiver. (The receiver, of course, will provide quick switching between your standard and high definition sources.)