Out of all the vidya's I have played (or atleast can remember) there are only two that I will forever hold in my brain as having the coolest intros. Those are:
The Darkness(this video isn't as cool because the dude cut the music out but the scene is 10x better with the music playing on a bigscreen television)
and
Deus ExThe Darkness' intro was just one big interactive action sequence and it did a good job throwing you into the game. I love it when games kick off with a boom. Deus Ex was cool because they basically recapped everything that was happening through the two main badguys and the soundtrack was pretty kickass.
Honorable mentions go to
Super Mario RPG: Alluding to older mario games by having to storm Bowser's castle and rescue Peach was a nice touch even though Bowser isn't the main villain.
BioShock: Simple, short, elegant. Loved it.
Final Fantasy 7: Didn't really care for the game but the intro was great. More games need to get to the fucking point instead of WHITE TEXT BLACK SCREEN
I should point out that the intro is one of the most important parts of a game for me. Too many games feature these long drawn out and boring opening sequences and I start thinking to myself. When I think to myself, I become disconnected with the game world, grow bored and then shut off the game. The intro should grab you by the balls, grip them hard, then ease off once you're reeling from pain. FF7 started off with a dungeon right off the back and shit blowing up then it proceeded into about an hour of backstory and talking to people. That was cool. I appreciated that.