MDF is Medium Density Fiberboard and it's made by essentially combining resins with sawdust and compressing it into a fairly dense, extremely flat and smooth sheet. In general it can be worked like "regular" wood but it's heavier (because of the density) and tends to not hold screws as well as "real" wood. It isn't very pretty and has no grain but it can be painted well.
MDF isn't really a "mediocre" product, but it has it's advantages and limitations like any other product and doesn't exactly "shine" when used in a way that's outside its capabilities. MDF can not tolerate moisture at all (although they make "waterproof" versions that do fairly well.) I think the biggest reason why woodworkers don't like it is because it's really messy to work with. It makes a lot of fine dust when sawed or drilled, etc.
MDO is "real" plywood with a thin sheet of resin paper, plastic, or metal that is bonded to one or both sides to provide a smooth working face.
MDO is sometimes called signboard because sign painters (used to) use it for creating exterior, hand-painted signs, and so forth. It tends to handle moisture/water much better than MDF (which MDF is really, really, really bad at) so its good for exterior projects. It paints well, but its more expensive.
Both are well-suited to "shop" projects and so forth because neither is particularly "pretty". You wouldn't necessarily use either for show surfaces of "fine" furniture in most situations. But I usually cover the surfaces of my workbenches with MDF to take advantage of the density/weight.
There are also MDF-core plywood which feature an MDF core overlayed with veneer sheets which makes it appear like "regular" plywood. The advantage to this product is that it tends to be (and stay) flatter than other plywood while still taking stain, etc. like the "real stuff". It is heavier though.