One game that doesn't seem to be mentioned yet: Pixeljunk Shooter

While its still 'only' 2D, the water physics are done pretty good and it's definitely a big part of the gameplay.

I guess the hardest part in using water/fluid as a major game mechanic is getting the simulation stable (just like every other physics based game, but with water it's a little harder). You really want to get the same results every time you re-run the simulation under the same conditions.

If you want to go 3D with this you might want to read the GPU Gems article about fluids.