Best lens for my Nikon D5000 SLR camera

I only have the standard lens that it came with the 18-55mm lens, and i'm trying to invest in a better one.i'm still an amateur at photography but i really enjoy it so i want one that can be used for lots of different purposes. I've been googling and i still don't know
i want one that has a decent zoom, preferably with a macro built in thats good for general photography
i don't want something too huge, anyone help please?

I shoot Canon dslr cameras and my main lens is the Canon 28-200. I hear good things about the
Nikon lens of the same size. The Canon lens retails for $400, so check out the Nikon. Mine is an excellent lens.

You've got it the wrong way round really. You don't buy a lens and then find a use for it, you identify an need and buy the right lens for the job.

True 1:1 macro is typically featured on fixed focal length or 'prime' lenses. Some common ones are 50mm, 60mm, 90mm, 105mm from the likes of Canon and Nikon as well as third parties such as Tamron and Sigma.

A lens which you might choose to compliment you kit lens - the 70-300mm Tamron for example - will give you more 'zoom' as well as 2:1 macro.

Rather than trying to identify a jack-of-all-trades though think hard about the type of photography you want to get involved in and then you will be better placed to decide on the type of lens you require.

If you simply want a carry-around-does-everything (apart from Macro) then something like the Tamron 18-270mm VC will offer better optics than your kit lens and 15x of optical zoom.

The 18-105 from nikon is a nice amount of zoom with decent quality and a friendly pricetag. Sigma has a 17-70 that also does a bit of 1:2.7 macro

Nikon's new 18-200mm but it has no macro.
Try Tamron's 70-200mm which is macro capable.

It's better to have the 18-200 first since you're just starting out. You still don't need those expensive lenses. You'll figure out sooner or later which type of lens you will need on your type of subjects. The 18-200 will help you figure that out.