Given a topological space $X$, we can define the sheaf cohomology of $X$ in 

I. the Grothendieck style (as the right derived functor of the global sections functor $\Gamma(X,-)$)

or

II. the Čech style (first by defining the Čech cohomology groups subordinate to an open cover, and then taking the direct limit of these groups over all covers).

When exactly are these two definitions equivalent? I'm unhappy with the explanation given by Hartshorne. Are they the same for any paracompact Hausdorff space? Or a locally contractible space?

And what is the relationship between these two sheaf cohomologies and singular cohomology?

Any elaboration on this circle of ideas related to the relationship between all the different cohomology theories would be appreciated.

Dear Victoria: here is a summary of the main comparison results I know of between Grothendieck cohomology  (which is usually just called cohomology and written  $\newcommand{\F}{\mathcal F}H^i(X,\F)$  ) and other cohomologies.

1) If $X$ is locally contractible then the cohomology of a constant sheaf coincides with singular cohomology. [This is Eric's answer, but there is no need for his hypothesis that open subsets be acyclic]

2) Cartan's theorem: Given a topological  space $X$ and a sheaf $\F$, assume there exists a basis of open sets $\mathcal{U}$, stable under finite intersections, such that the CECH cohomology groups for the sheaf $\F$ are trivial (in positive dimension) for every open $U$ in the basis: $H^i(U,\F)=0$
Then the Cech cohomology of $\F$ on $X$ coincides with (Grothendieck) cohomology 

3) Leray's Theorem: Given a topological  space $X$ and a sheaf $\F$, assume that for some covering $(U_i)$ of $X$ we know  that the (Grothendieck!) cohomology in positive dimensions of $\F$ vanishes on every finite intersection of the $U_i$'s.
Then the cohomology of $\F$ is already calculated by the Cech cohomology OF THE COVERING $(U_i)$: no need to pass to the inductive limit on all covers.
This contains Dinakar's favourite example of a quasi-coherent sheaf on a separated scheme covered by affines.

4) If $X$ is paracompact and Hausdorff, Cech cohomology coincides with Grothendieck cohomology for ALL SHEAVES
If you think this is too nice to be true, you can check Théorème 5.10.1 in Godement's book cited below
[So Eric's remark that no matter how nice the  space is, Cech cohomology would probably not coincide with derived functor cohomology for arbitrary sheaves turns out to be too pessimistic]

5) Cohomology can be calculated by taking sections of  any acyclic resolution of the studied sheaf: you don't need to take an injective resolution. This contains De Rham's theorem that singular cohomology can be calculated with differential forms on manifolds.

6) If you study  sheaves of non-abelian groups, Cech cohomology is convenient: for example vector bundles on $X$ ( a topological space or manifold or scheme or...) are parametrized by $H^1(X, GL_r)$. I don't know if there is a description of sheaf cohomology for non-abelian sheaves in the derived functor style.

Good references are 

a) A classic:  Godement, Théorie des faisceaux (in French, alas)

b) S.Ramanan, Global Calculus,AMS graduate Studies in Mahematics, volume 65.
(An amazingly lucid book, in the best Indian tradition.)

c) Torsten Wedhorn's  quite detailed on-line notes, which prove 1) above (Theorem 9.16, p.92)  and much, much more.
By the way, @Wedhorn is one of the two authors of a great book on algebraic geometry.

d) Ciboratu, Proposition 2.1 and Voisin's Hodge Theory and Complex Algebraic Geometry I, Theorem 4.47, page 109 , which both also prove 1) above.
