The key to this is realising that different people think in different ways, and want to be able to achieve different things. The answers already given, tend to represent the the learning styles and playing ambitions of the people who wrote them.
Learning by rote versus learning by discovery
You can learn the position of every A on the fretboard by consulting a diagram. You can learn scales in every key by copying the diagrams in books. That may be your preferred learning style.
You can reason about the tuning of the guitar. On all but one string (in standard tuning) a note fretted on a particular string, is the same pitch as the next string up fretted five semitones lower. You can use that knowledge, and the knowledge of the intervals -- 2-2-1-2-2-2-1 -- to play a major scale in any key. You can hear the interval between your current note and your root note, and name the note that way. This may be the way you prefer to think about music.
Reading music via note names, or directly
It's possible you don't want to read music at all -- we'll come to that. However, if you do...
Some people like to start by going via the names of the notes. For each note, the mental process goes:
- A note at this position on a stave is a C
- In the current key signature, a C becomes a C#
- To play a C in that octave on my fretboard, I fret here
Of course, with practice, those three steps happen really quickly.
Other people take a more direct route from sheet music to note:
- In this key signature, that position on the stave corresponds to this position on the fretboard
The name of the note doesn't matter, for this kind of person. Why even think about its name? You just want to play it.
Realistically, I think most people are somewhere in between those two extremes. I think people who start as the first type, get to the point where they're doing it the second way and know the note name instantly. People who start as the second type, may always need to think a bit if they need to name the note they just played.
Improvising and playing by ear
Some really good musicians never learn to play by ear. Some really good musicians improvise by moving around scales they've learned by rote; reasoning about key signatures. Knowing the names of the notes that will fit, and playing those notes on the fretboard.
Other musicians simply know by (learned) instinct, what pitch they'll hear if they fret a certain position, and what pitch they're aiming for. I'm certain that most of the blues legends play in this way. They can probably tell you what key they're playing in (so they can growl "Rainbow, G" at the house band) but they're not thinking "F#" as they hit it.
Alternate tunings
You specifically asked about whether alternate tunings are difficult to learn.
Yes they are. To play guitar well, you need to get really, really familiar with the tuning. So it follows that another tuning is going to be really challenging.
I think people who use alternate tunings fall into two camps:
- Really dedicated virtuosos who go through the hard work of adapting to a tuning
- Guitarists who use alternate tunings to "trick" effects out of the instrument.
By the latter case, I mean -- open tunings that give you a chord wherever you barre it -- or the kind of tuning Keith Richards or Lou Reed would use to enable them to play a riff they wouldn't be able to achieve with standard tuning. Those guys are almost certainly not thinking of the names of the notes they're playing.