What you are referring to is sometimes known as catastrophic failure, when a frame fails under load. While this does happen from time to time, it's relatively rare. There are tales of defective carbon frames failing spontaneously while riding, but these seem to always be friend-of-a-friend stories. Most frames that do "fail" do so because of traffic accidents or other damage. And, as Gary points out, the bottom bracket (and mounting eyelets) are likely areas to wear.
Frame material also plays a part in this. Conventional wisdom in cycling circles is that steel is the most durable - it can be bent back into shape and even re-welded. Aluminum frames can't. And carbon fiber frames have a reputation for being delicate; if one of those breaks, you throw it out and get a new one, since any fixes will be unreliable.
While there is some truth to this, steel can be damaged to the point where it would be unsafe to fix and re-use. And carbon fiber is more durable than folks assume. But evaluating a damaged frame is often more trouble than simply replacing it. There are pro wrenchers who can do this kind of evaluation based on experience, but without putting the frame through an x-ray machine can you really, definitively say that a frame is safe to put your weight on after it's been bent and re-welded?
In general, yes, the frame is the most durable part of the bike. But it, too can be damaged beyond repair.