You could get as technical as you like with an explanation, but I always like to think about it like a built in warming or cooling filter. The filter (like any filter) would be attached to your lens and can either warm the image up (add a hint of red) or cool it down (add a hint of blue). WB is similar but without the extra glass.
So, the "overcast" setting is like your warming filter and will help reduce the amount of cold colours (like blue). And the "tungsten" setting will cool the scene and neutralize some of the red (named because indoor "tungsten" lights usually make the seen yellow or orange-ish). "Flourecent" will take care of some of the green that appears there.
"http://www.kenrockwell.com/tech/whitebalance.htm" helps to explain this (with regards to a D70 even).
I find most of the time you won't have to move this from auto. I also like to use the WB setting in the bracket menu. And finally, if you're shooting in RAW, you can usually change this during the edit stage too if you find you were way off.
Try shooting a bunch of shots under different light using the different settings and you'll understand better than anyone can expain it. It all comes down to how the camera interprets colour.