I've seen choices either sRGB and AdobeRGB in my camera and a whole plethora more in Apple's Aperture? What exactly does this do? How do I pick the proper one?
While jrista's older answer that's been linked to gives a lot of background, for most people who just care about producing good results, things can be simplified a lot. There are basically two possibilities that (IMO) make much sense. For lack of a better term, I'd call them the "high end" and "low end" options1.
High end: ProPhoto RGB working space, 16 bits per channel.
Low end: AdobeRGB working space, 8 bits per channel.
In either case, you want to profile your monitor. IMO, if you care about color fidelity at all, it's worth getting and using a colorimeter. If you want to go for the really low-end version, Adobe includes a calibration program with Ps (and probably with Lightroom and such as well). I suppose if you really refuse to use a colorimeter, it's better than nothing -- though only a little bit, to be honest.
From there, you can convert to sRGB (for example) when you're publishing something to the web or getting a print from Walgreens (or whatever). In a fully color managed work flow (e.g., a pro-level printer or doing your own prints) should go directly from your working space to the profile for the printer/paper combo being used.
Just in case that wasn't clear: sRGB (somewhat like JPEG) should only be used as an output format, and even then only in situations where the destination is unknown and uncontrolled. It's never really a good choice -- but when/if the destination (probably) doesn't manage color at all, it's the least of the available evils.
1Trying to mix these to get a mid-way point doesn't really work well. AdobeRGB with 16 bits per channel doubles file sizes without improving quality. ProPhoto RGB with 8 bits per channel will frequently result in visible "banding".
Color Profiles allow you to select a color gamut (think palette or range or colors), that the photo will be rendered in. sRGB is the basic standard, and it used in most cases, however, there are others, such as AdobeRGB, that allow for a wider range of colors.
In general, you should select sRGB, unless you know a specific reason to choose otherwise. The downside with selecting a non-standard profile is that you have to use properly calibrated monitors to view it correctly, and you also have to make sure that your printing service and photo software supports the profile you select. (Note that proper monitor calibration is recommended even when using sRGB).