Think of a inductor and capacitor in parallel. At DC, it's a short due to the inductor. At infinite frequency it's a short due to the capacitor. You'd therefore expect the impedance to be high in the middle.
It is, but there's a special case. Inductors and capacitors aren't just frequency-variable resistors. For one thing, the current is shifted by 90° phase from the voltage, although this phase shift is in opposite directions for inductors and capacitors.
Now think again what happens to a parallel inductor and capacitor as the frequency changes. We already said at DC the inductor is a short, so the combination is a short too. As the frequency starts to go up, the inductor still draws large current, and the capacitor starts to draw a small current. The net result is that the inductance dominates, and the current lags the voltage by nearly 90°.
At the other end, with high but finite frequency, the capacitor dominates. The current leads the voltage by nearly 90°.
There is a frequency somewhere such that the current magnitude thru the capacitor and thru the inductor are equal. At that special frequency, the inductor current with its 90° phase lag and the capacitor current with its 90° phase lead combine to cancel each other out. We have no current when a voltage of this frequency is applied. That's infinite impedance. A little off this special frequency, and the current phase is no longer 0°, and the above doesn't apply.
Real parts aren't ideal. The capacitor and especially the inductor each have a little inevitable resistance in series with it. This prevents the overall impedance to become truly infinite at the special frequency. With good quality parts, it can come fairly close.
The result is a peak in the graph of impedance magnitude as a function of frequency. The frequency of the peak is the resonant frequency. The more ideal the parts, the sharper the peak, meaning it is tighter in frequency.
This relatively sharp peak can be exploited to filter in or out just the resonant frequency (or frequencies close to it).
A series L-C combination works similarly, except that the impedance at 0 and infinite frequency is infinite. It has lower impedance to in-between frequencies. At resonance, it's impedance is ideally 0.