Not too exciting, right? Wrong! Cables are AMAZING. Nah, not really. But if you don't use the best connection you can, you're not getting the best audio and video quality out of your games. In some cases, the difference could be huge.
Let's start with the best and work our way down.
HDMI: This single connector transmits digital audio and video information. Make sure you read my HDMI Cable Article. All PS3s and newer 360s have HDMI. It's capable of 1080p and if your TV supports it, 1080p/24 with Blu-ray. The video signal is the same as DVI, and the two are interchangeable if you have an adaptor.
This is the red, green and blue video cable. Interestingly, the video information on these cables is not red, green and blue. The green cable (also called "Y") is black and white, with all the detail. The red and blue are called color difference channels (Pr and Pb). This means they carry whatever leftover data is needed to give the image red or blue color. What does this mean to you? Nothing, just thought you might want to impress your friends.
Component is capable of all the same resolutions as HDMI, though some TVs don't support 1080p over component. If all you can do is component, it's highly unlikely you're missing anything compared to HDMI.
You're better off with a cheap HDMI cable than a cheap component cable. Component is analog, so it's possible you'll pick up noise with a cheap cable. Seeing as you can get HDMI cables for $2, there's no reason not to go HDMI unless you don't have HDMI on your TV.
The Wii maxes out at Component, but seeing as it also maxes out at 480p, I wouldn't worry about it.
This is the one that looks like an old fashioned mouse connector. S-Video, despite all the pins, only has two channels of information, black and white on one, and color on the other. What component does with three, S-Video does with two. As you'd expect, the lower bandwidth means less quality. S-Video maxes out at 480i. Component will always look better than S-Video, but if your choice is composite or S-Video, everything is better than composite.
This is the single yellow cable that comes with every piece of A/V gear and gaming console. It sucks donkey balls. What component does with three, S-Video with two, composite does with one. It is noisy, has artifacts, and has severely limited bandwidth. So even though it's still 480i, it looks much worse than S-Video. Don't use composite unless you have no other choice.
HDMI: If you have a receiver and want to listen to the latest high-resolution audio formats like Dolby Digital Plus, Dolby True HD, and DTS-HD, HDMI is the only way to transmit these formats digitally. Though from an audiophile standpoint HDMI is a horrible way to transmit audio, there's no reason not to use it with 99% of all systems.
Optical: This is the badass looking fiber optic cable. It's been around for ages. It can do Dolby Digital, DTS and is found on pretty much everything. While there is a chance of jitter, it's not likely you'd hear that on most systems. If you're doing Blu-ray you should use HDMI, but otherwise Optical is just fine.
Coax: This is a single cable, usually colored orange on the back of equipment. The signal is basically the same as Optical (except for, you know, being optic). There's no appreciable audio difference between Optical and Coax. While you can get jitter with Optical, you could pick up interference with Coax. Over short runs, neither will be noticeable. Likely a decent Optical cable will be rather pricy, so I'd lean towards Coax for that reason alone.
Here's a neat secret. The spec for Coax is a 75 ohm cable, the same as video. So if you have a spare composite cable lying around, it will work as a coax digital cable. Perhaps not as perfect as a beefy Coax from a store, but it will due in a pinch.
Analog: I like analog. It just works. In most cases this is just the red and white (or black) connectors on the back of every piece of A/V gear. The main drawback is with just 2 channels, you can't get real surround sound. You can get Dolby Pro Logic (4.0) if your receiver decodes it, but if you have a receiver you should really invest in one of the cables above.
If you're just plugging into the TV, there's not going to be a big sound quality difference between analog and Optical/Coax.
I have a high-end Blu-ray player that has some amazing DACs, better than what's in my receiver. So I hook up with thick analog cables. But that's just me.
Generally speaking, a decent cable will sound/look better than a cheap cable (with the exception of HDMI). So paying a little extra is probably worth it. Not a lot extra, but a little.
Hope this all helps. If you have any questions, drop me a line.