This is my understanding of this yoga. It may not be exactly what you seek and may differ from another person's point of view. Also I apologize for my bad english.
For Grothendieck, many things should have a relative version. So instead of considering just a space $X_0$, consider a morphism $f:X\rightarrow S$ thought as a family of spaces $s\mapsto X_s:=f^{-1}(s)$.
Here is an easy example of relative thinking : Say you want to attach numerical invariants to your first space $X_0$, for example its dimension, you should instead replace it by a function on $S$, and you get the relative dimension. Under some assumptions, this function behave quite nicely.
If now you want to attach invariants that are sets, or abelian groups, to your first space $X_0$. Well there is a very nice tool that do exactly what you want for the relative version : presheaves on $S$. And under some assumptions, these are closely related to sheaves.
The six operations arise this way if you want to study cohomology.
There are many cohomology theories out there, and many of them have their 6 operations. But they do not behave exactly of the same way, so let's say we work with ordinary cohomology of (nice) topological spaces.
It is also better to work with derived categories. Indeed, the cohomology of space is in fact a complex up to quasi-isomorphism, more than just the groups $H^i$. Also, for example, the functor $f^!$ exists only at the level of derived categories. If $X$ is a space, let $D(X)$ be the derived category of sheaves on $X$ and let $\mathbb{Z}_X$ be the constant sheaf with value $\mathbb{Z}$ on $X$.
Let start with the first functor $f_*$ or rather $Rf_*:D(X)\rightarrow D(S)$. Well, this is exactly the functor that computes cohomology. Indeed, if $f$ is proper, $(R^if_*\mathbb{Z}_X)_s=H^i(X_s,\mathbb{Z})$ (this is the so-called proper base change theorem). So this is exactly the relative version of cohomology. In fact, if $f$ is proper and smooth (=submersion), then $R^if_*\mathbb{Z}$ are local systems on $S$.
There is also $f_!$ or rather $Rf_!:D(X)\rightarrow D(S)$. It does the same thing, but with cohomology with compact support : $(R^if_!\mathbb{Z}_X)_s=H^i_c(X_s,\mathbb{Z})$. This times, one does not need to assume $f$ proper.
And of course the tensor product $\otimes$ gives the multiplicative structure on cohomology. One can then speak of derived analogue of the Kunneth formula...
Now, if $g:T\rightarrow S$, there is the functor $g^*:D(S)\rightarrow D(T)$. In some sense this is the one that justify the whole thing : this is the functor which correspond to changing the base from $S$ to $T$. For example, if $j:U\rightarrow S$ is the inclusion of an open subset, one can form $X_U=f^{-1}(U)$ and $j^*Rf_*\mathbb{Z}_X=R(f_{|X_U})_*\mathbb{Z}_{X_U}$. This is a (very easy) special case of a very deep one : the smooth base-change theorem. Changing bases is VERY useful. For example, base change to the universal cover, so that local systems become constant. Or in algebraic geometry, base change to the algebraic closure. And of course, taking stalks are already special cases of base change...
And finally, the functor $f^!:D(S)\rightarrow D(X)$ and the internal Hom are there to deal with duality. (Note that $f^!$ is right adjoint to $f_!$, not left). Instead of a global duality between $H^i_c$ and $H_{d-i}$, we now have local versions, allowing local computations and so on... Just for completeness, if $S$ is a point, $f_*f^!\mathbb{Z}$ is the Borel-Moore homology and $f_!f^!\mathbb{Z}$ is the ordinary homology (at least if $X$ is nice enough).
Best Answer
The answer to this question is the same as the answer to every question of this genre ("why should I care about groups," "why should I care about rings"): they show up everywhere and are an extremely useful organizing principle.
There is a meta-principle that any time you're trying to understand something about categories, it's a good idea to restrict to the special case of posets first, regarded as categories where $a \le b$ means there is a single arrow $a \to b$. For example:
So what's a pair of adjoint functors in this context? Well, if $P, Q$ are two posets, then a functor $f : P \to Q$ is just an order-preserving function. So a pair of adjoint functors is first of all a pair $f : P \to Q, g : Q \to P$ of order-preserving functions. The definition I think is most convenient when studying posets is that $f, g$ must satisfy
$$\text{Hom}_Q(fa, b) \cong \text{Hom}_P(a, gb)$$
for all $a \in P, b \in Q$. But this is the same thing as requiring that
$$fa \le b \Leftrightarrow a \le gb.$$
This relationship is called a Galois connection. Important examples of Galois connections include:
Galois connections exist in extreme generality and are, by themselves, already an important organizing principle in mathematics. So adjoint functors are even more important than that!
Edit: It's probably worth explaining what's going on in the above examples abstractly. Let $A, B$ be two sets, and let $r : A \times B \to \{ 0, 1 \}$ be a relation. Then $r$ induces an order-reversing Galois connection (a pair of contravariant adjoint functors) between the poset $\mathcal{P}(A)$ of subsets of $A$ and the poset $\mathcal{P}(B)$ of subsets of $B$ as follows: if $S \subset A$ then $f(S) = \{ b \in B : r(a, b) = 1 \forall a \in S \}$ and if $S \subset B$ then $g(S) = \{ a \in A : r(a, b) = 1 \forall b \in S \}$. I'll leave it as an exercise to figure out what $A, B, r$ are in the above examples.
Note also that the fact that left adjoints preserve colimits and right adjoints preserve limits continues to hold for Galois connections, and shows that some of the properties of the Galois connections above are purely formal (in the sense that they follow from this "abstract nonsense"). Unfortunately it is generally not emphasized which properties those are.
The Wikipedia article does a nice job of explaining some broad general motivation (and has a lot of good discussion on this question besides): very roughly, an adjoint is the best substitute for an inverse that exists in a lot of cases that we care about. You can sort of see how this works in the above examples.
An important property of adjoint pairs is that they restrict to equivalences on subcategories, and this is what we get in the Galois theory and algebraic geometry examples above: the first adjoint pair is an equivalence by the fundamental theorem of Galois theory, and the second adjoint pair restricts to an equivalence between reduced ideals and varieties by the Nullstellensatz.
Since your question is tagged [algebraic-geometry], here is an important non-example related to the second half of Arturo's answer. There is a functor $F : \text{Aff} \to \text{Set}$ sending an affine scheme to its set of points (the prime ideals of the corresponding ring), and it does not have a left adjoint: there is no "free affine scheme" on a set. The reason is that $F$ does not preserve limits. (Note that a functor has a left adjoint if and only if it is a right adjoint.) In fact, it does not even preserve products. The product of two affine schemes $\text{Spec } R, \text{Spec } S$ is $\text{Spec } R \otimes_{\mathbb{Z}} S$, and it is a basic property of schemes that this is not the set-theoretic product.
It follows that the functor $F : \text{Aff} \to \text{Top}$ sending an affine scheme to its set of points in the Zariski topology also does not have a left adjoint. If you've ever wondered why the Zariski topology on $\mathbb{A}^2$ isn't the product topology on $\mathbb{A}^1 \times \mathbb{A}^1$, now you know.