The primary answer to your question is that although pitch defines the basic frequency of the note, there is—at least in common-practice tonal music, and many other styles too—an entire other trait called function. A C# and a Db are the same pitch (at least on the piano, these will often have slightly different tunings when played by unfretted string instruments or sung by vocalists etc.), but the C# is implying a need to resolve up to D while Db implies a need to resolve down to C. Not every composer or notater is as careful to maintain this distinction, but it's definitely the default.
As others have pointed out, different keys have different expectations for the diatonic pitches. One would expect to see C# in, say, A Major, but Db in Ab Major. In the rare case that a Db happens in A Major, it will be there specifically to imply a chromatic resolution to the (otherwise foreign to the key) C natural.
One of the great beauties of this particular notation system is that composers can indicate more than simply which buttons to press, or fingers to put down or notes to sing. Composers and arrangers can also show specific function of each and every note just what enharmonic choices they make. One sweet side effect of this is that a composer can imply one kind of movement or resolution, but then frustrate the tendency for dramatic effect while still easily informing the performer about how to interpret the notes.
Here's an example from the literature of an enharmonic change being used by the composer to signal a modulation. It's from m. 133 of the first movement of Beethoven's Grande Sonate Pathétique.
Please note that, although the key signature is C minor, this particular excerpt is firmly in G minor, as can be seen by all of the Bbs, F#s, and A naturals. Compare the second and third measures of this excerpt. In measure 2 Beethoven writes an F# fully diminished seventh chord (F#, A, C, and Eb). There's a voice exchange as it moves to the final 32nd note chord—that is, the Eb in the bass moves down to C while the C in the soprano moves up to Eb—but the harmony is the same. This is a viio7 chord in G minor, and it resolves normally to i with the G minor triad on beat 4 (well, technically, the resolution was on beat three, but the upper voices hold suspensions and retardations until beat 4). Now look at the third measure. It begins with the same F#dim7 as the previous measure, but the last 32nd note chord and the following quarter note are different. They are now a D#dim7 (D#, F#, A, and C), but—except for a slight rhythmic change in the bass—these two measures would still sound exactly the same to a listener until the final beat. That's because a D#dim7 and an F#dim7 are enharmonically equivalent, one just has a D# where the other had an Eb. However, the function of these two chords is completely different: whereas the F#dim7 wants to move to Gm including a downward resolution of the 7th, Eb, to D, the D#dim7 wants to move to Em including an upward resolution of the root, D#, to E. In fact, the entire next section of the piece is in the key of E minor, which is quite distant from both G minor and the original key of C minor.
The listener doesn't know about the change until the new unexpected resolution on beat 4, but the pianist is alerted to the change early via the enharmonic shift in the notation. More importantly, Beethoven would have learned about distant resolutions such as this from teachers (potentially Haydn?), method books, or other composer's music specifically in terms of the types of modulations made available by enharmonic respellings like this: using diminished seventh chords, German augmented sixths, pure melodic resolutions, etc.
