After Effects is an amazing program. I tell people it's s basically Photoshop for video, but that doesn't completely cover it. You can do anything with it. It's used everywhere... commercials, film titles, some film/tv fx, you name it. And once you get the hang of it, it's a lot of fun. It makes motion graphics so easy. But the learning curve can be high.

I recommend "Creating Motion Graphics with After Effects" by Trish & Chris Meyers. It's now a 2 volume book. They start you off from the beginning, and quickly get into some very cool stuff. Very easy to follow, and after you get the basics, you can jump from chapter to chapter, just focusing on the effects you want to create.

If you want professional titles, After Effects is the way to go.

-T