I use two different cameras for aquatic photos, dependant on what type of fish I'm taking photos of.
For slow paced fish in a well lit tank I will use my little compact digital camera coupled with it's Macro shooting feature.
My preference, though, goes to my Nikon D300 DSLR. I agree that a Macro prime lens is the way to go (the Nikkor 50mm 1.4 is a very nice, cheap lens), but for the moment I'm just using the bog standard, basic kit lens: 18 - 55mm.
Lighting and timing is the key, especially with fast moving little critters! A clean tank front, free of smudges and large marks also helps.
Anyho, best of luck!