What the circle of fifths shows us is the harmonic relationship between notes. This is something that is not obvious when looking at the piano keyboard, as that arranges the notes in linear order.
Notes that are close together on the piano keyboard are in fact harmonically far apart. Try playing C and C# together - it will sound dissonant and unpleasant. These two notes are far apart on the circle of fifths, and they'll rarely be heard together in music.
But play C and G together and it will sound sweet. G is a fifth higher than C, it is the closest note to C harmonically, and they are neighbours on the circle of fifths.
The reasons for this can be analysed mathematically, in fact G is 1.5 times the frequency of C. So three cycles of G will fit into the same time as 2 cycles of C. But when playing music there's not much time to think this way, we just need a feeling for what sounds good.
Look at the circle of fifths and you'll see that G is one place clockwise from C. Now, go one place anticlockwise and you'll find F. In other words, C is the fifth note of the scale of F.
Say we are playing a tune in the scale of C. The two notes most closely related to the home key are G and F. And you'll find that the chords C, G and F are the ones that will pop up most often when trying to harmonise an accompaniment with a tune in C. The most commonly needed chords are the two nearest neighbours on the circle of fifths.