Gasoline Engine oil

Gasoline Engine oil is designed to be as slippery as possible, with a very low coefficient of friction using friction modifiers, some detergent, and anti-wear additives. It is designed to absorb moisture and later evaporate it when it gets hot. It should not really be used in any transmission. It is available in various viscosities, typically from 0W-20 to 10W-60. Most common at the moment is 5W-30.

Diesel engine oil

Diesel engine oil has a higher level of detergents and anti-wear additives for the longer drains and higher compression of heavy duty Diesel engines. It is also designed to absorb moisture and release it when hot. It is acceptable, and even recommended in several manual transmissions (Isuzu, and some Isuzu made Chevrolets), both synchronized and non-synchronized. It is available in various viscosities, typically from 5W-30 to 5W-50 (Isuzu used 15W-40).

Motorcycle engine oil

Motorcycle engine oil (4 stroke) is a blend of Gasoline engine oil without the slippery friction modifiers, but with friction modifiers to make it grab the clutches instead of letting them slip, since most motorcycles have wet-clutches (exception is dry-clutch high powered motorcycles that basically use gasoline engine oil). This is also used in some gasoline car engines where the same oil is used in the automatic transmission, such as my Morris Mini. It is available in various viscosities, typically from 10W-30 to 10W-60.

Two-Cycle motor oil

Two-Cycle motor oil is a light oil with a blend of anti-wear additives and one to keep it in suspension in the gasoline when stored. It should not go in anything else.

Automatic transmission oil

Automatic transmission oil is a thin oil that can withstand high transmission temperatures, lubricate, carry heat to the radiator where it cools, etc. It has to last a long time (100,000 km, typically). It also has additives to make it grab clutches to not allow them to slip. There are three basic formulations (although probably 2 dozen specs). You must use the correct one for your car. There are two basic viscosities, the most common (two of the three) being less viscous than a 0W-20 motor oil. The thinner (8 and 9 speed transmissions) is about half that viscosity. It should never get water in it, as the fiber discs can be damaged.

CVT oil:

While this may look like a regular ATF, it has very different properties. In addition to all the attributes of the ATF, it must be able to eliminate slippage on the metal to metal contact of the steel belt on the steel pulleys. This is done with a very special synthetic oil mixed in small quantities in the base fluid.

Manual transmission oil for synchronized transmissions (often called MTF)

MTF is an oil that has additives for extreme pressure to care for the gear faces, high lubrication of bearings, but also an additive to make the synchronizers grab so you can shift gears. The newer versions do not contain sulfur/phosphorous additives, so they are friendly to brass parts. The older (cheaper) versions still use sulfur/phos additives, but in 50% of the level used in differentials or non synchronized truck transmissions. It is typically available in viscosities of 75W-80, 75W-85, 75W-90, and 80W-90 (although that is really for a few older cars that did not use needle bearings). These will generally try to decant, or separate small amounts of moisture.

Synchromesh Fluid:

(MTF) As its name implies, this is for synchronized manual transmissions. Its formulation is very similar to a 5W-30 Diesel engine oil with some friction modifiers to help the synchronizers grab when shifting. It was basically designed with this different name so people would stop putting sulfur/phosforous products in synchronized manual transmissions. In general it is being replaced in formulation, if not in name, by the new versions of the MTF I referred to above, as they have stronger base oils and additive combinations.

Non-Synchronized manual transmission oil, and differential oil, has a high amount of EP additives for the high stress on the rubbing/sliding gear teeth, since the differential has the full torque applied to it.

One person mentioned grease. Please do not confuse oil and grease. Grease is oil with a solid thickener that acts like a sponge, holding the oil in place to it can be proportioned out to as it is needed. It does not circulate or cool. The oil contains the additives needed and the thickener provides the sponge.

Just to add to my original answer, due to questions on viscosities, I graphed some common viscosities so you can see there relationship.