Photoshop's Curve tool is no mystery to me, and if you are a frequent user of Photoshop then it shouldn't be to you either. That being said, I have found over the time I have been running my Digital-Darkroom workshop that many participants have a very basic understanding of the tool. Indeed, due to the non-intuitive nature of Photoshop I find that most think that the Curve tool is a mysterious thing.
The Curve tool is really a tone re-plotting tool. You can transpose a range of tones to being another range of tones by way of an input/output graph. The X axis (highlighted in green) conveys what the tone is before the transposition while the Y axis (highlighted in red) conveys what the tone will become.
In the curve example here, you can see that the anchor point in the middle of the graph is transposing mid-tones to upper-mid tones. In fact, any tone that is of value 128 (black = 0, white = 256) will be transposed to an upper tone of 192.
The curve (as the name suggests) is non-linear: meaning that although I have transposed tones in the middle region, I have altered tones elsewhere but to a lesser degree. More specifically, tones near the anchor point in the middle of the graph are transposed the most, while tones towards the black and white points (far left, far right) are transposed the least. This is illustrated by the blue area underneath the curve: the diagonal line in the graph is the 'no-change' waterline and the further away the curve moves from it, the larger the tonal-transposition. Where the wedge is thickest we get the most alteration in tone, and where the wedge is the thinnest we get the least change in tone. But ultimately tones throughout the entire image are being altered.
The Curve tool is really quite simple. It's just that we expect it to be quite difficult because it looks complicated.