I shoot mostly in Program AE (P mode on most SLRs). It works for me most of the time.
For dark rides, and fireworks, I shoot hand-held and use Shutter Speed Priority (Tv on Canon SLRs). I set the shutter speed to the slowest that I believe I can hold steady by hand (usually 1/15 sec) and let 'er rip. For fireworks, I typically pre-focus on the castle before the lights go down, then switch to manual focus, because the camera can't focus on black sky with streaks of light. I hit about .100 with these techniques.
I also use Tv mode for fast action capture, such as street performers and daylight parades, and of course for many fountain shots where I want to either freeze the water mid-air, or turn it silky with a longer exposure. Shooting the waterfalls in Epcot's Canada pavilion way back with my Canon Rebel G 35mm in about 1999 or 2000 was my first experience with Tv, and my first foray out of P mode, and it has served me well over the years.
Once in a while I switch to Auto Depth of Field mode (A-DEP on Canon SLRs). A-DEP uses all the focus points (if you choose a particular patter, the camera ignores that in A-DEP mode), and adjusts the aperture to give maximum DOF so that everything in the frame is in focus. It's not perfect, but in some situations where I have close foreground and far background, it has worked better than P mode. Eventually, I hope to have good enough instincts about aperture selection to set it manually, but for now, A-DEP makes a nice crutch.
I almost never use Aperture Priority mode (Av on Canon SLRs). And now that I think of it, that's a huge mistake, because using Tv mode has given me a much greater instinctive understanding of shutter speed, and since aperture is the mighty dragon I have yet to confront, maybe I need to start using Av more often.
Those "Creative Zone" modes on my Digital Rebel (Landscape, Portrait, Action, etc)? Never met 'em, never want to. They're nothing but green box modes with settings optimized for those situations, and using them gives me no greater understanding or feel for how the camera operates than the dreaded Green Monster.