I don't think the Seifert-van Kampen theorem follows from these kinds of considerations. Rather, it is the statement that the fundamental groupoid functor $\tau_{\leq 1}$ preserves homotopy colimits. That's because it is left adjoint to the inclusion of 1-groupoids in $\infty$-groupoids (a generalization to any $\infty$-category is in Higher Topos Theory, Prop. 5.5.6.18).
[Removed the part on cohomology because it got me confused!]
Added:
I attempted to explain the long exact sequences in cohomology without using spectra, but that was wrong. Here's a correct explanation. The reduced cohomology of a pointed space $X$ depends only on its stabilization $\Sigma^\infty X$: it is given by $H^n(X;A)=[\Sigma^\infty X,\Sigma^n HA]$ where $HA$ is an infinite delooping of $K(A,0)$. The functor $\Sigma^\infty$ preserves cofiber sequences (being left adjoint). Now if you have a cofiber sequence in a stable category, you get long exact sequences of abelian groups when you apply functors like $[E,-]$ or $[-,E]$.
Added later:
My original answer was correct, but like I said I got confused… Here it is again. Let $A\to B\to C$ be a cofiber sequence of pointed spaces. As you say in your question, you get a fiber sequence of mapping spaces
$Map(C,X)\to Map(B,X)\to Map(A,X)$
for any $X$, because $Map$ transforms homotopy colimits in its first variable into homotopy limits. In its second variable it preserves homotopy limits, so $\Omega Map(A,X)=Map(A,\Omega X)$. Applying to $X=K(G,n)$ gives you the usual long exact sequence in cohomology, but only from $H^0$ to $H^n$.
Here are some basic remarks and examples:
(Caution. This answer refers to preorders; but many of the remarks also apply to partially ordered sets aka posets)
Many concepts of category theory have a nice illustration when applied to preorders; but also the other way round: Many concepts familiar from preorders carry over to categories (for example suprema motivate colimits; see also below).
This is partially justified by the following observation: An arbitrary category is a sort of a preorder but where you have to specify in addition a reason why $x \leq y$, in form of an arrow $x \to y$. The axioms for a category tell you: For every $x$ there is a distinguished reason for $x \leq x$, and whenever you have a reason for $x \leq y$ and for $y \leq z$, you also get a reason for $x \leq z$.
A preorder is a category such that every diagram commutes.
In a preorder, the limit of a diagram is the same as the infimum of the involved objects. Similarly, a colimit is just a supremum. The transition morphisms don't matter.
When $f^* : P \to Q$ is a cocontinuous functor between preorders, where $P$ is complete, then $f^*$ has a right adjoint $f_*$; you can write it down explicitly: $f_*(q)$ is the infimum of the $p$ with $f^*(p) \leq q$. This construction motivates the General Adjoint Functor Theorem. In this setting we only have to add the solution set condition, so that the a priori big limit can be replaced by a small one and therefore exists.
Let $f : X \to Y$ be a map of sets. Then the preimage functor $\mathcal{P}(Y) \to \mathcal{P}(X)$ between the power sets is right adjoint to image functor $\mathcal{P}(X) \to \mathcal{P}(Y)$. Every cocontinuous monoidal functor $\mathcal{P}(Y) \to \mathcal{P}(X)$ arises this way.
The inclusion functor $\mathrm{Pre} \to \mathrm{Cat}$ has a left adjoint: It sends every category to its set of objects with the order $x \leq y$ if there is a morphism $x \to y$. In particular, it preserves all limits. In fact, it creates all limits, and limits in $\mathrm{Cat}$ are constructed "pointwise". Thus, the same is true for limits in $\mathrm{Pre}$ (which one could equally well see directly). For example, the pullback of $f : P \to Q$ and $g : P' \to Q$ is the pullback of sets $P \times_Q P'$ equipped with the order $(a,b) \leq (c,d)$ iff $a \leq c$ and $b \leq d$. If we apply this to difference kernels, we see that $f : P \to Q$ is a monomorphism iff the underlying map of $f$ is injective.
The forgetful functor $\mathrm{Pre} \to \mathrm{Set}$ creates coproducts: Take the disjoint union $\coprod_i P_i$ and take the order $a \leq b$ iff $a,b$ lie in the same $P_i$, and with respect to that preorder we have $a \leq_i b$.
The construction of coequalizers seems to be more delicate; see this SE discussion.
I don't have a reference for all these observations, but they are easy. A general reference for basic category-theoretic constructions (and it surely says something about preorders and posets) is the book "Abstract and Concrete Categories - The Joy of Cats" by Adamek, Herrlich, Strecker which you can find online.
EDIT: Here is something not so basic: Sefi Ladkani studied the notion of derived equivalent posets. Two posets $X,Y$ are called (universally) derived equivalent if for some specific (every) abelian category $\mathcal{A}$ the diagram categories $\mathcal{A}^X$, $\mathcal{A}^Y$ are derived equivalent.
Best Answer
You can think of the pushout of two maps f : A → B, g : A → C in Set as computing the disjoint union of B and C with an identification f(a) = g(a) for each element a of A. We could imagine forming this as either the quotient by an equivalence relation, or by gluing in a segment joining f(a) to g(a) for each a, and taking π0 of the resulting space. If two elements a, a' of A satisfy f(a) = f(a') and g(a) = g(a'), the pushout is unaffected by removing a' from A. The homotopy pushout is formed by gluing in a segment joining f(a) to g(a) for each a and not forgetting the number of ways in which two elements of B ∐ C are identified; instead we take the entire space as the result. It is the "derived" version of the pushout.
In general you can think of the homotopy pushout of A → B, A → C as the "free" thing generated by B and C with "relations" coming from A. But it's important that the "relations" are imposed exactly once, since in the homotopical/derived setting we keep track of such things (and have "relations between relations" etc.)
Another, possibly more familiar example: In a derived category, the mapping cone of a morphism f : A → B is the homotopy pushout of f and the zero map A → 0. This certainly depends on A, even when B is the zero object: it is the suspension of A.