About Macro Lenses
A dedicated macro lens will get you high magnification, a good deal of control over your image, and excellent optical quality. If you can afford one and you know you want to do macro work, this is the way to go. But, macro lenses — at least new ones — are not cheap. If you can't afford one, or you don't plan to do a ton of macro, you can go for the one of the cheaper solutions listed below. Or, you can go for an older macro lens like the venerable 55mm f/3.5 Micro-Nikkor.
Macro lenses are usually in the range of 50-60mm, 90-105mm, and 150-200mm. The shorter lenses are usually the most affordable and the most portable. They'll all get you to 1:1 magnification, generally, but the longer lenses give you greater <i>working distance</i>, meaning you can shoot insects without having to put the front element right in the bugs' faces.
A note on autofocus
For the most part, when working at high magnifications, autofocus isn't much use. So even folks who normally prefer lenses that will autofocus on their cameras may not need to worry about this with a dedicated macro lens. <i>However,</i> macro lenses are often used in multiple roles; many make very good portrait or general-purpose lenses, and in those capacities, AF may be useful.
Some popular macro lenses
55mm f/3.5 Micro-Nikkor P
One of the best values among dedicated macro lenses is the 55mm f/3.5 Micro. It's an old, manual focus, non-metering lens. Used, it can be found for as little as around $40-60, depending on the version and the condition. It's designed to be paired with an extension tube (like an M2) to achieve 1:1 magnification; on its own, it goes to 1:2. It's generally not much more expensive than a set of modern extension tubes, so if you're considering getting into macro photography, the 55mm f/3.5 is one of the best inexpensive options.
60mm f/2.8 Micro-Nikkor AF-S
This is the great grandson of the 55mm f/3.5. It's a lens with fantastic optical perfomance, a semi-fast aperture (f/2.8), and it will autofocus on a D40/x/D60. It's a good choice for doing double-duty as a macro lens and a portrait lens.
105mm f/2.8 Micro-Nikkor AF-S VR
If you can afford this lens, and you may need a bit more reach than you get with the 60mm, go for it.
Tamron 90mm f/2.8
This is one of the most popular macro choices for folks who don't want to shell out the cash for a Nikon 105mm f/2.8. It's currently available in an AF version (no autofocus on D40/x/D60), but Tamron announced they'll be adding a focus motor.
Sigma 150mm and 180mm HSM
These lenses will AF, and they'll get you quite a bit more reach than the Tamron 90 or the Nikon 105.
Other macro solutions
An extension tube is just an empty tube of air which changes how the lens focuses, allowing you to focus on very close objects, but preventing focusing at infinity. They come in different lengths, and can be combined to achieve different degrees of close focus.
If you're using a lens with an aperture ring (meaning not a "G" type lens like the kit lens), your extension tube doesn't need to be anything other than a tube. But, unless it has something to couple with the diaphragm, you will have to stop the lens down manually before shooting. If you do want to use a G-type lens, your extension tubes will also need to have contacts to transmit information between the lens and the camera, and will therefore be somewhat more expensive. Kenko is one brand that makes tubes of this type.
One of the ways to achieve very high macro magnifications is to take a lens and mount it in reverse. You use a device like a BR2A that mounts a lens backwards against your camera body, or a coupling ring that connects the fronts of two lenses together. There are also ways of making the couplers yourself using body caps, filters, etc., hacked together.
This is easier to do if the reversed lens is one that has an aperture ring (AF-D or earlier), because without an aperture ring, you'll wind up shooting at the smallest aperture all the time. However, you can reverse such a lens in front of a G-type lens without an aperture ring without problems.
These are magnifying lenses that screw onto the filter threads of one of your existing lenses. The results are generally not as high in quality as with a dedicated macro lens, but these are comparatively inexpensive, easier to carry in an already-crowded camera bag, and can sometimes produce interesting effects. Raynox makes one that gives a sort of soft, impressionistic feeling. Canon and Nikon offer comparatively high-quality, multi-element close-up filters.
Light transmission decreases substantially as you increase magnification. Also, depth of field decreases, meaning you need to stop down to maintain DOF. This means that you either need to go to very long exposure times or introduce flash. Preferably off-camera flash, because it's easy for the lens itself to block light if you use flash on-camera.
It's best to have a very firm tripod, if practical given your subject and shooting conditions. It may also be worthwhile to get a macro plate/rail for the tripod, to allow the camera to move back and forth precisely without needing to reposition the tripod. This is important because macro focusing is done by moving the camera, rather than turning the focus ring, because the focus ring varies magnification.