Water cooling takes advantage of transferring heat to a fluid, rather than air; said fluid conducts heat better than air does.
Setup depends on what you're cooling. You'll have a radiate, to remove heat from the system; this radiator will have between 1 and 4 fans on it, depending on the model. 2 fan radiators are quite common. You'll also have a pump, to circulate the fluid. You'll have waterblocks, which conduct heat from the CPU, GPU, etc. into the circulating fluid. There's also miscellaneous things such as tubing, coolant, fittings, reservoirs, etc.
Compared to air cooling, water cooling is quite expensive. A basic setup will cost around $200-$300, depending on a multitude of things.
Is it worth it? That depends on you. If you want to do serious overclocking, then I'd say this would be your best bet. If you want your PC to look awesome, and impress friends, then yes, it's worth it. If you just want to run it on a stock system, then I'd say no, it's not worth it.
Parts needed are listed above, but I'll list them again.
- Misc. Fittings
beat me to it