a higher megapixel camera is important for maximum resolution - the higher the megapixel the larger the shot which means you can crop it down nicely and make it look 'zoomed in' - but its not everything. make sure the camera has fast processor and preferably can be manually preset (eg: for ISO etc to account for fast movement) also check it has a macro setting for those close up shots. i use a sony dsc-p100 5mp camera which seems to do ok. while its a good point and snap camera, it's still not the best (even though it has all the goodies on it)
i think mastering photo's of fish etc is more user than camera. IMO, turn the (camera's) flash off and use natural lighting of the tank instead otherwise the shot can look "washed out" (excuse the pun ) others may disagree, if they do, I can give you many reasons why not to use the cameras flash however, you should use an 'external' flash where possible (more expense ). a tripod (or at least table to put the camera on) is ESSENTIAL. even the steadiest of hands can blur your shots! i dont use the optical zoom (where possible) and NEVER use digital zoom (and never buy a camera based on digital zoom - it's pointless) make sure the glass is EXTREMELY clean on both sides obviously. Also, it depends if you are shooting fish or just the background. If fish, then their movement is unpredictable which makes it difficult to get focus. If you can manually preset the focus then have patience to wait til a fish swims in the path of focus, you should get a nice shot. Set auto-focus mode to single AF and use the lowest aperture setting your camera offers (remember a lower aperture number represents a larger opening = more light)
i have always been impressed with the high-end canon camera's. of course, going to the 10D or 20D means a second mortgage but their middle of the range camera's are very good.