Sheaf cohomology is the right derived functor of the global section functor, regarded as a left-exact functor from abelian sheaves on a topological space (more generally, on a site) to the category of abelian groups. In fact, one can regard this functor as $\mathcal{F} \mapsto \hom_{\mathrm{sheaves}}(\ast, \mathcal{F})$ where $\ast$ is the constant sheaf with one element (the terminal object in the category of all -- not necessarily abelian -- sheaves, so sheaf cohomology can be recovered from the full category of sheaves, or the "topos:" it is a fairly natural functor.
de Rham cohomology can be made to work for arbitrary algebraic varieties: there is something called algebraic de Rham cohomology (which is the hyper-sheaf cohomology of the analog of the usual de Rham complex with algebraic coefficients) and it is a theorem of Grothendieck that this gives the usual singular cohomology over the complex numbers. Incidentally, sheaf cohomology provides a very simple proof that de Rham cohomology agrees with ordinary cohomology (at least when you agree that ordinary cohomology is cohomology of the constant sheaf, here $\mathbb{R}$) because the de Rham resolution is a soft resolution of the constant sheaf $\mathbb{R}$, and you can thus use it to compute cohomology.
Sheaf cohomology is quite natural if you want to consider questions like the following: say you have a surjection of vector bundles $M_1 \to M_2$: then when does a global section of $M_2$ lift to one of $M_1$? The obstruction is in $H^1$ of the kernel. So, for instance, this means that on an affine, there is no obstruction. On a projective scheme, there is no obstruction after you make a large Serre twist (because it is a theorem that twisting a lot gets rid of cohomology). Sheaf cohomology arises when you want to show that something that can be done locally (i.e., lifting a section under a surjection of sheaves) can be done globally.
$H^1$ is also particularly useful because it classifies torsors over a group: for instance, $H^1$ of a Lie group on a manifold $G$ classifies principal $G$-bundles, $H^1$ of $GL_n$ classifies principal $GL_n$-bundles (which are the same thing as $n$-dimensional vector bundles), etc.
Also, sheaf cohomology does show up in algebraic topology. In fact, the singular cohomology of a space with coefficients in a fixed group is just sheaf cohomology with coefficients in the appropriate constant sheaf (for nice spaces, anyway, say locally contractible ones; this includes the CW complexes algebraic topologists tend to care about). For instance, Poincare duality in algebraic topology can be phrased in terms of sheaves. Recall that this gives an isomorphism
$H^p(X; k) \simeq H^{n-p}(X; k)$ for a field $k$ and an oriented $n$-dimensional manifold $X$, say compact. This does not look very sheaf-ish, but in fact, since these cohomologies are really $\mathrm{Ext}$ groups of sheaves (sheaf cohomology is a special case of $\mathrm{Ext}$), so we get a perfect pairing
$$ \mathrm{Ext}^p(k, k) \times \mathrm{Ext}^{n-p}(k, k)\to \mathrm{Ext}^n(k,k)$$
where the $\mathrm{Ext}$ groups are in the category of $k$-sheaves. This can be generalized to singular spaces, but to do so requires sheaf cohomology (and derived categories): the reason, I think, that for manifolds those ideas don't enter is that the "dualizing complex" that arises in this theory is very simple for a manifold. You might find useful these notes on Verdier duality, which explains the connection (and which mostly follow the book by Iversen).
This doesn't answer your question, but: why don't you just start reading papers in algebraic geometry? You will quickly be forced to come to grips with "reality" in this way. My basic point is that, if you have read (a lot of) Hartshorne and Liu, you don't need more textbooks; you just need to start reading some research mathematics.
But regarding your actual question, you already have the answer at hand, namely: Hartshorne! Chapters IV and V are entirely about curves and surfaces, and have lots of concrete discussion of both of them. If you succeed in mastering this material, you will know a lot of concrete algebraic geometry.
A typical problem that it is hard to think about if you don't know anything is: "how do I describe a typical curve of genus $3$, or $4$"? After you read Hartshorne Chapter IV, you will know that the answers are "a smooth plane quartic", and "the intersection of a quadratic and cubic hypersurface in $\mathbb P^3$", respectively. You can't get much more concrete than that. (And there are many exercises in the spirit of such concrete questions.)
One thing is that you will need cohomology of coherent sheaves, but that
it not so hard to learn, and the beautiful applications in Chapters IV and V
should give ample motivation.
Best Answer
Since Serre's GAGA is already listed, let me add the book "Algebraic and Analytic Geometry" by Neeman. The book starts very gently (even redefines schemes etc.) and has full chapters on the analytic topology, analytification, algebraic and analytic coherent sheaves etc. so that one is comfortable with all things necessary for understanding GAGA. Moreover, I think the book is well written and therefore definitely a good reference for the analytic world.