Metroid Prime- explore a cool frigate, escape, oh NOW YOU'RE ON AN EVEN COOLER PLANET
Resident Evil 4- hey sir you seen dis gurl 'Nah I'm gon try to kill ya (rough Spanish translation) and so will erryone else' oh a cabin OH A CHAIN SAW
Xenoblade Chronicles- sets up the world with both showing AND telling how it came to be as it is now, and then gets you right into the man vs machine war, which I find to be a beautiful contrast to what the rest of the game is like
Dark Souls- kickass metal-as-heck lore dump intro telling you who you gotta fight, and then the Undead Asylum will tell you how it's gonna be right away, with HARD AND FAST LESSONS, and you either accept it or you don't, if you can even make it out through these challenges.
Bayonetta 1&2- possibly the most gameplay centric intros I've ever seen in a cinematic action game, dumping you right into a random fight with little context other than a narration dump playing over probably the best music in both games, followed by a real prologue chapter that surpasses the intro in awesomeness (especially Bayo 2's).
I mean, a lot of games' intros aren't half bad cuz they gotta sell it to you but these stick out most to me.