FF8 is amazing of an intro.. I loved MGS agent-like movie intro, but most of that game was filled with exciting cinematics..
Uncharted 2 blew me away when it starts and flows right into gameplay. I was simply shocked when the camera zoomed out and suddenly I was able to interact with that scene! It set the whole mood for the game and graphics.
Infamous title screen explosion, MGS4 graphics, GTA4 movie and music intro... A lot of amazing intro cinematics that caught my attention as being brilliant in their own way.