We bought this tilt-shift lens specifically for a customer who wanted to hire it. Yes, really.

The main use for the shift part of a tilt-shift lens is for perspective correction, most typically in architectural photography. But you can do other wacky things with them, such as photographing a mirror without seeing your reflection, or photographing objects when there's something in the way. The Wikipedia article entitled Perspective Correction Lens contains links to a number of good tutorials and articles on this subject.

But what about the tilt part? Well, that gets really weird. Tilting the lens allows you to bring any plane into sharp focus, not just a plane perpendicular to the sensor. If you know what you're doing, you can probably get huge Depth Of Field for landscape shots. But we're way out of our depth here. If you are too, you might want to read the Wikipedia article entitled View Camera/Tilt.