Miles Hecker has a very nice Curves 101 tutorial on Luminous Landscape. I urge you to read it before continuing if you're new to curves or rusty. In spite of Miles' and dozens of other Curves tutorials I keep reading that beginners and intermediates have endless trouble getting the hang of this exceedingly useful tool.

Here are two slightly more advanced pointers that can keep you out of lots of hot water:

1. The more aggressive the curve the more you need to be in 16-bit mode.
2. If my math extended past grade 3 I could express this more elegantly, but with multi-point curves no portion of the curve line should have a downward (negative?) slope reading from left to right. IOW: no portion of the curve should have a hump or kink in it (you could even say it frowns).

Let's look at this visually. Let's start with a basic three-point curve but drag the centre point a bit to the left:

Now let's drag the centre point upward:

The bad curve is bad because part of it goes back down hill. This means there are brightness values that are duplicated on both sides of the hump.

In fact, the closer any portion of the curve approaches being horizontal - let alone humping - the more of a miracle you are asking PS to pull off, and certainly the more you need to be in 16-bit mode:

Now let's apply theory to practice. Here's a crop from a typical contrasty scene:

The exposure was made to preserve some colour in the sky, which in turn caused the rest of the scene to appear dark, foreboding, and generally un-bourgeois. In order to open up the non-sky parts of the scene, we might apply a curve like so:

Don't be intimidated by all the control points in this curve. I started with the same three basic points shown in Miles' tutorial, then added two more – one close to the bottom and one close to the top.

A common reason to add another control point in any situation is just to tack down a portion of the curve you don't want to change when you change another part of the curve. But in this case I added the upper-most point to exaggerate the highlight contrast in order to make the clouds more visible. I added the bottom-most point by experiment when seeking to maximally lighten the shadows.

Which results in this:

However, if we push the Curves tool too hard, as in the following curve:

We get the following:

Notice the areas circled in red. Colours that should be shades of green in the tree tops are instead shades of light grey. Similarly, two of the boats are now light grey instead of yellow. The culprit, of course, is the plateau area circled in amber:

Given how much trouble a plateau or hump in a curve can cause, and all the grief novices must have needlessly suffered over the years with this, one has to wonder why the Photoshop dev team never added an optional limiter or warning mechanism to this tool ... Reduce speed – sharp curve ahead...