In all honesty, a lot of the work you like to do depends a lot on technique, though when it comes to macro work, it is definitely good to have top-notch equipment.
If you really want to do some fantastic macro work, I suggest any of the following (+ Canon DSLR):
- Canon Macro lenses (50mm, 100mm, etc.); if you want to get really close, the MPE5 is amazing -- up to 5x magnification, with a ridiculously close focusing distance.
- Tamron 180mm Macro (I have this one, love it to pieces), but they have others that are great too
- Sigma should have a good one too
- Extension tubes for existing lenses (to reduce focusing distance and increase magnification)
Next, you need to realize that a big sensor = tiny depth of field, which means that unless you can shut down to f/99 with a ton of light, you're only going to have a little bit in focus. The way around that is to do focus-stacking: this is a method where you take a series of images with different focus, and then you join them together to get an image with the subject entirely in focus. Not the easiest of work, but in a way, it's similar to HDR or other stacking techniques.
Second, a ring flash would be a great idea for macro work as it enables you to get light up-close to your subject that is nicely distributed. (Don't go for the really cheapies, you want a quality flash that won't harm your camera...)
As to some of your other likes:
- (Assuming in water) A circular polarizer helps here because you can turn it to a point that you will either see the reflection on the water, or you will see through the water. Very useful to have.
- Water Drops
- Macro lens is great for this
- Water (Flowing)
- a long exposure is the best option, something your camera can already do. If there is too much light, a polarizer or ND filter can be used to further darken the camera's view to allow for longer exposures without blowing out the scene.
- Clouds and Fog
- Remember, your camera exposes for the middle (so white often becomes gray). Try switching your metering mode (spot, evaluative, etc.) but also adjust exposure compensation. Better yet, go full-on manual and set the exposure yourself.
- Side note: unless you have something well-defined in the clouds/fog, any camera will have difficulty focusing. You may have to focus manually.
- Burning Tubelight / Running Fan
- Long exposure, again, something your camera can do. Although a DSLR has a bigger sensor, so it should have reduced noise vs. your existing camera.
Hope that helps. Can your current camera do a lot of what you're looking for, yes, to a certain degree, but it is most limited in the macro arena where you can't get a lot of magnification out of the lens. Noise is also going to be a limiting factor, in that it isn't long until the ISO causes unbearable grain. DSLRs get around this by having that big sensor, but they are also more flexible with regard to the interchangeable lens, letting you pick what really, really, really will work best for the scene.