Each slider adjusts the volume of its frequency band relative to the others, so you can reinforce or reduce some part or parts of the sound frequency spectrum relative to the others. This can be useful, for example if you want to emphasize, say drums versus piccolo, or bass singer versus soprano, since these will predominately produce sounds in differing frequency ranges. Moving the slider up increases the relative volume for that frequency band, moving it down, reduces it. It can also be useful to correct distortions caused by equipment that does not have an equal response to all audio frequencies (e.g. small speakers tend not to be able to reproduce low notes too well, so to compensate for that one might boost those using an equalizer). Similarly the effects of some environments can be corrected for (e.g. the reflective/absorbing properties of walls and furnishings may differ for different frequencies).
The 125, 250, etc. indicate the centre of the frequency band. Each filter is bell shaped around that frequency, but I don't know the width (quality or Q) used in the creative equalizer (probably set so that the filters don't overlap significantly).
Middle C on the piano is 256 cps, doubling every octave (higher frequency is higher note).
More background informatiion in, e.g.
http://stereos.about.com/cs/componen...equalizers.htm
http://www.dak.com/reviews/Tutorial_frequencies.cfm
Ultimately not using a flat equalizer (which gives equal weight to all frequencies in the sound file) is a matter of what sounds best for you, given your equipment and the environment in which you are situated.
The presets offer recommended settings for different kinds of music.