Wells. My dwarves drink from a drinking zone next to a pond on the surface. That's convenient at the moment, but not classy at all. I haven't found any underwater rivers yet. I'm planning a potential disaster.
I want to drain the water from the pond into an underwater cistern. I'm not that concerned about flooding my base but rather about having it spread everywhere and evaporate since it's not all that much water. Any safe way to do this? I guess i have to channel a pool underground for this to work.
The safest way is to dig a web of single-tile pipes on the same level as your murky pools, but leave one tile intact at the edge of the pool. For bonus points, build floodgates in the pipes, connect them to levers, test your mechanisms, close the pipes off, and channel away the tiles immediately.
Next connect the surface pipes to a vertical shaft leading down to your cistern. For this part you can do up/down stairs, or use ramp-and-channel if you want to have an open pipe. On the bottom level of the vertical pipe (which will become the top level of your cistern) you may want to tunnel a single tile corridor horizontally for 10-30 tiles to leave room for a dining hall over the cistern. This is not mandatory, but it will allow you to have a well in the middle of the floor of your dining hall without having to build your dining room around a flooding pipe fixture.
Count up the number of pool tiles you're draining and dig a cistern that has approximately the same volume. For aesthetics I like to dig a circular cistern, but you can get away with 5x5 or 7x7 as long as you have enough Z-levels to contain all the water you're draining. If you did levers and floodgates, go with a 2- or 3-level cistern and as small a footprint as you can get away with (3x3x3, for example).
If you're obsessive, you can smooth the cistern and dump all the rock that has accumulated. If not, channel away the last tile at the edge of each pool and let 'er rip. The cistern should fill up, and you can channel a hole into its ceiling from above and build a well over it. The top-most layer of your cistern will likely evaporate but it will take a long time for your dwarves to deplete the rest. If you're using levers and gates, you can fill your cistern without depleting your murky pools, and if you get enough rain those pools will refill instead of evaporating, giving you a perpetual water supply.