It is well known that given a short exact sequence $$0\rightarrow A \rightarrow B \rightarrow C \rightarrow 0,$$ we can form a long exact sequence in cohomology. (Example: the proof of the Mayer-Vietoris sequence.)
This is perhaps not too surprising. After all, we are given a nice algebraic relation between $A$, $B$, and $C$, so it seems sensible that we can get good information about their cohomology groups. And, if we're willing to get metaphorical, I suppose we can think of short exact sequences as some kind of algebraic version of a fibration and and the long exact sequence here as an analogue of the long exact sequence for a fibration from algebraic topology. I have two questions along these lines.
Is there any way to make this intuition more precise? Why should short exact sequences lead to long exact sequences in cohomology? Is comparing this result to examples from algebraic topology the right way to think about it, or is another approach more philosophically enlightening? (I have heard that in general the right way to understand results from homological algebra is to go back to their roots in algebraic topology to get intuition. Comments on this would also be appreciated.)
What's so special about exact sequences with three terms? Can we get any information about cohomology from exact sequences like
$$0\rightarrow A \rightarrow B \rightarrow C \rightarrow D \rightarrow 0\, ?$$
I have glanced at nLab, and this page suggests there might be some satisfying general explanation in terms of category theory, but I can't make much sense of it. (I confess I always just assume abelian categories are categories of modules and try not to worry about the details...)