Someone correct me if I am wrong, but let's say if you display an sRGB image in color managed and non color managed programs they should look more or less the same because the operating system is basically a sRGB color space. If you display an AdobeRGB image in the same programs, the image will only display correctly in color managed programs, provided they implement color management properly and are set up correctly.
To accurately see the differences you will need a high quality graphics card and a colorimeter calibrated good quality monitor, otherwise you are groping in the dark, using your printer as a monitor which is very ass backwards and you will run around in a vicious circle that will usually only get worse. In my experience, the graphic card is more important than the monitor. The only gunslinger in the chain is the graphics card. The monitor is a dummy that does what the card says. The profiles are rules and instructions on how towns people should to talk to the gunslinger if they expect him to shoot the baddies and not them(you).
Most cameras work in sRGB and some allow you to select Adobe RGB, so these will be the color spaces you will almost always be working in and there is little or no advantage in converting them to anything else on your computer.
Output to your printer will normally be managed by the printer driver for a certain range of papers so there is usually no need to assign a profile as the printer is taking care of that. If you are using papers that are not listed in the printer driver, then it is helpful to have a custom profile for your printer using that paper. Some paper manufacturers provide profiles for different printers and their papers, or recommended setttings in your printer driver for those papers, for example Photo Glossy, Fine, yellow +15. Of course the quality of your printer and even the local weather will determine how your prints come out.
If you are outputting to an outside lab, you can apply their profile to your output, but if they are competent it would be better to send them the image in it's native color space and let them do it. Same would apply if you are sending an image to press. Unless you are providing the whole document, let them do it.
If you are outputing to the web, the universal color space is sRGB.
There are a variety of color spaces and you can profile almost anything, however most people's needs are confined to very few. Most people will shoot their pic, do their PS thing and print with no hoohaa. However, you can only do your PS thing with any hope of success if your monitor is at least colorimeter calibrated and profiled.
...and of course, you have much more control if you are shooting RAW than JPEG. Color space is the same in both, though the implementation through RAW>TIF,PSD whathaveyou will be better than JPEG.
Hope this is sufficiently confusing...