You need Adobe After Effects, but I'll tell you know that if you don't know Photoshop to start off with, it won't be easy.
That brings me to the 2nd point, you need to know Photoshop.
Also, learning a 3D package won't hurt. 3D Studio Max, Cinema4D or Maya. I use Maya personally.
Check out www.videocopilot.net