What are color profiles (in Photoshop) and how do they actually affect the printing?
Color profiles have everything to do with how images are displayed and printed. They are not specific to photoshop, but that is one way they can change.
Color profiles include CMYK, RGB, LAB, etc with more specific version depending on the output. Each one represents a color gamut which is a range of colors supported.
Adobe RGB 1998 and srgb are common colorspaces (gamuts) for images used for display (digital format, web, monitors, projectors, etc).
CMYK is much different. It really depends on the printing process and printer that determines the right color profile to use. In general, though, the differences between the printer-specific gamut and a general CMYK profile are minor.
LAB you'll never use unless a printer specifies it, but it's arguably the better format.
Here's a good visual of the differences in colorspaces:
Edit: Also see this post: What is the difference between CMYK and RGB? Are there other color spaces I should know?