There are a few "types" of "manual" mode auto gears.
1. Auto Transmission with Manual Mode.
The ones implemented in the regular BMWs, Porsche Tiptronic, Mazda 3, Mazda 6 and Mercs are still regular automatic transmissions, aka those with torque converters.
In a regular automatic transmission, you still have different gear ratios (usually 4 speed like the Mazda and earlier Porsches, but can also be 5/6 even 7 speeders in the higher end Mercs), but you don't use a clutch to switch between different gears. The power is transmitted through a torque converter.
Even for those without a manual mode, you can still use these gears manually by switching to by using the 1, 2, D with OD off and D to switch to 1st, 2nd, 3rd and 4th gears respectively.
The Tiptronic (and other variants) merely provide a more convenient way of switching between the gears.
2. CVT with Manual Mode

CVT does not have a standard gear ratio but uses a variable gear ratio using the system above.
But most CVT transmissions do have a preset series of ratios to mimic a 4 or 5 or 6 speed gearbox like with the PI Honda Fit/Odyssey or the Audi CVT boxes.
3. Manual gearboxes with automatic clutch
The most complex ones are those used in Formula One/Rally cars. These use the same clutch based manual gearbox as in regular manual cars, but there is a mechanism that engages and disengages the clutch automatically, instead of using yr left foot.
Used in some low end cars (to improve fuel economy) like Ford Fiesta, Opel Meriva, Corsa. More advanced versions with faster switching and blipping of throttle in downshifting are used in BMW SMG/SMGII, Alfa Selespeed, Ferrari 360 CS etc. Volkswagen even has a dual clutch system called DSG which is even more impressive.
If you want all the techie bits.. head on over to
http://autozine.kyul.net/technical_school/tech_index.htm#Transmission