You can't pick your settings until you know what the light is like.
Portrait photos, like character greetings. - I use aperture priority mode to control depth of field. I want the character and my child to be in focus but I'd like to throw as much of the background out of focus as possible. The exact aperture depends on the focal length and the distance between the character and my child. If I have time, I spot meter off of my childs face. Usually we're rushed, so I just leave the camera in evaluative and adjust in photoshop. I set the ISO to the lowest reasonable number for the lighting, usually 100 to 200.
Close action photos - on a rollercoaster - High shutter speed is a top priority unless I'm doing something tricky like panning. I usually bump my ISO to 800 for sunny photos or even higher if the light is low. If I'm on the ride with the kids, I often go to aperture priority mode to get a reasonable depth-of-field and then just hope the shutter speed works out. Because light levels change so quickly, I stay in evaluative metering mode.
Action photos of rollercoasters, or stunt shows, etc. - Too many variables. I use different settings depending on how much motion blur I want, how much depth-of-field I want, how much light I have to work with, etc.
Night time photos of fireworks, etc. - I haven't shot fireworks in a while. When I did, I shot in manual mode and set an aperture and shutter speed to match what I was trying to accomplish. I wanted a shutter speed that allowed some movement of the fireworks in the frame (probably 1/4 to 1/30). If I have something like the castle in the frame, I'll set my shutter speed, spot meter off the castle, and adjust my aperture to suit. I'll adjust my ISO to the lowest value that will work with my shutter speed and a reasonable aperture setting. I check the histogram between shots to see how things look and adjust accordingly.
there some of these just best left to 'auto' mode? - I never use full auto mode, but there are times when I use program exposure mode. If conditions are changing vary rapidly and I'm not at all sure what I'm going to do, I use program exposure and shift it when necessary. I also drop into program mode before handing the camera to my wife unless I think I can set it up with a single setting that will work for all of her shots.
Does anyone know a website that explains, in laymans terms ( ), what effect settings like ISO, aperture, shutter speed and exposure have in specific conditions?
I imagine that there are lots, but I don't know any. The effects of each of those is the same regardless of conditions. The appropriateness is what varies. I'll try to summarize them.
ISO is how sensitive your camera is to light. The higher the ISO, the less light it takes to make an image.
Aperture is how big the opening in your lens is that lets light through. The wider the opening (lower f-stop number), the more light it lets in. Wider openings also mean that you have less depth-of-field. That's a photo-nerd way of saying that less of your picture will be in focus. That can be a good thing because it draws the viewers eye to your subject.
Shutter speed is how long the camera lets light in. The longer the shutter speed, the brighter the picture. Long shutter speeds also give more time for the camera and the subject to move, either of which will cause blur.
Exposure is a combination of ISO, Aperture, and Shutter Speed. They all combine to determine whether your picture will be bright, dark, or something in between.