Are you looking at a fountain for cooling your pool, for aesthetic reasons, or both? I've got a triple tier fountain that I use in the summer time to help cool the pool when it gets too warm, and it works great. The lighted ones look good on the webpage, but I haven't seen any in person to be able to give a good opinion on those.
John's right on the money - my fountain has an adapter that mounts to my upper return, and then uses a length of clear plastic tubing to go to the fountain. I unscrew the and remove the ring and jet, and then screw the adapter on. The adapters, tubing, and a weight to hold the tubing down all should come with the fountain.
They are pretty to watch, and I'd say the lighted ones would be pretty to watch at night. Two things on the con side - they will cause your pool temp to cool down, and you will have to replentish your water more often. Oh, and if you've got salt in your water, then you're putting a salt spray in the air. If you can live with the cons, I'd say go for it.