There are a lot of games that seem to not place much emphasis on introductory music, which I believe is to their detriment. Opening music as found in most main game music should set the tone for what's to come and/or get the player excited to play. I believe music to be crucial part of good game design as it can fundamentally alter the whole experience. While you may not catch me listening to game music outside of my play time, I know plenty of games whose fun is greatly bolstered through the use of music, like Rayman Origins and its whimsical tunes. Anyway, to get back to the main topic, I think these are some good examples of well done musical introductions to games:

The Raven - A great theme to set the tone for the following mystery/thriller.
The Book of Unwritten Tales 2 - I'm ready to go on an adventure. How about you?
Legend of Grimrock 2 - Let's get amped for exploration and monster-clobbering!
Ori and the Blind Forest - Be ready for melancholy to set in.
Metro 2033 - See above.
Gray Matter - It's actually not very common for games to use full songs as their opening, and this one is perfect in this case.

Shoutout: This is something fortune_p_dawg touched on in the RPG sales thread a few days ago, and I thought it was worth expanding on.

Feel free to share your own examples. The more, the merrier. ;)

Republic Commando
/thread

Dragon Quest VIII
Mega Man II


This is Bit Brigade playing the whole Mega Man II soundtrack while a band member plays through the whole game without death. It is pretty epic.

Bit Brigade Mega Man II MagFest

My two entries

<span class="bold">Aquaria</span>
This game has insanely good soundtrack, and the intro is only small sample of what can we expect later.
Very nostalgic and stirring, perfect for game in which we'll explore the vast beautiful underwater locations.

<span class="bold">Europa Universalis III</span>
For King and Go(l)d! Are you ready to explore and conquer the lands of heathens?

Two words: Buck Bumble.

EDIT: Child of Light also has really good menu music.

EDIT2: And Chrono Trigger's opening ticking/music is carved into my memory pretty much permanently. Same with the opening music for Chrono Cross. And Divinity: Original Sin has really great opening music. And what's more epic than Baldur Gate 2's menu theme? And of course, there's Ocarina of Time, which built its memorable menu theme from the short warp whistle song from the first Zelda.

Given my fondness for cheesy bond movies (and john barry in general), the effect on me of the NOLF and Evil Genius intro tunes was to be expected.

Here are a few more:

Sanitarium
Br&uuml;tal Legend (more about the concept than the music, but there's music, too)
Life Is Strange (and the different menus in motion *MILD SPOILERS*)
The Wolf Among Us (also the Intro music)
Shadowrun Returns
Remember Me
Max Payne 2
Fahrenheit / Indigo Prophecy
Call of Juarez / Gunslinger
Outlaws
Gothic
Victor Vran
Dragon Age: Origins
Contrast
The Saboteur
Brothers: A Tale of Two Sons
The Talos Principle

I have to ditto these two above. DAO is on my short list of favs.

I do like Fallout 3