I highly recommend a DSLR but you don't need a macro lens. Most of my shots to date have been with kit lenses. That being said, a macro lens will definitely help especially with smaller subjects.
Whether using an overhead flash or just available aquarium lights, you want them at the front of your tank so the light source bounces of the front of your subject. Think of the fish like a mirror. If you shine the light behind it, it wont reflect back at you. I use an overhead flash with a wireless trigger. I leave the tank lights on so my camera can focus but the tank lights don't even show up with the settings I use.
For the black background. Set your camera settings to suit the front mid water section of your tank and only shoot when the fish are in that section. I generally keep the flash just off to the side of this area so the fish are not directly under it when it goes off. As you can see in the picture above, the light source is coming from the top right corner.
Settings for me are usually around 1/250th shutter speed to freeze the subject, ISO as low as possible for less noise, aperture stopped down to about f/13 for good depth of field and white balance set to suit your tank. If you are shooting without an overhead flash, you will have to decrease your shutter speed, increase your aperture and up the ISO. All of which will make it much harder to achieve an in focus shot. It can be done with a little patience though.
