Toyota/Lexus: Driving in "S" mode for automatic transmission?

For any of the more recent, newer Toyota/Lexus (maybe Scion, too?) with the gated (zig-zag) gear-shifter thingy (specifically, for the 6-speed automatics) that read "PRND" and then to the left (or right?) of "D" with a separate "S" gate with "+"/"-" up/down for on-demand up-/down-shifting...
Can you leave the selector in "S-mode" and drive as-is? Does it hold gears while waiting for you to upshift or something and overrev bouncing the tachometer needle up and down like crazy to risk engine blowing up into pieces? Or, does it allow the computer to take over and override the low gears by upshifting on its own? From there, will it also downshift on its own at the appropriate times?
Basically, will the car drive on its own just fine by leaving it in S-mode? If so, will the driving experience be different from driving in "D" (normal mode) in terms of throttle response, acceleration, upshifting/downshifting, cruising, braking? How so?
And how do you turn the overdrive on/off on these things? Is it "automatically" taken care of for you?

FIRST go get the owners manual out of the glove box and read it carefully, it explains all your questions. As to driving in S mode, sport mode is for someone who is racing the car basically, it will have more rpm between shifts and the shift will be quicker, the idea is the more rpm, the more horsepower you will have, and the faster you will be going, so if you are not planing on blasting around corners or racing through the mountian roads somewhere with care free abandon, with your foot to the floor. Put the car in D and drive it. save the S mode for what its for. If there is no overdrive button, or switch, you cannot change anything. And you forgot to mention what year the vehicle is, this is important too. good luck have fun..