If you're looking to shoot crisp, clear photos, you'll usually want to use the fastest shutter speed possible. The primary limitation of shutter speed is available light. Since a fast shutter doesn't let as much light through, it can lead to overly dark photos if lighting conditions are dim to begin with. You can compensate by opening up the aperture (the opening that allows light into the camera) and using a higher ISO setting (measuring the film's sensitivity to light), but these changes also have their own tradeoffs. If you're trying to capture fast-moving action, like a sporting event or a playful pet, fast shutter speed is vital. If the scene is too dark, your only option might be to alter the lighting or use a flash.
If you're shooting a stationary subject in low light conditions, you can use a slower shutter speed. This can allow you to capture a scene with shadows and subtle lighting that might be lost if you used a flash. Make sure to use a tripod or activate your camera's stabilization feature, if it has one. If your shutter speed is anything slower than 1/60, it will be almost impossible to hold the camera still and avoid a blurry photo.
If you're looking to shoot some special effects, shutter speed can help you out. Slow down your shutter just a little, and those crisp action shots show dynamic movement as some things become slightly blurred. Slow it down a little more, but this time pan the camera along with the movement of one object. Now that object will be crisp while the background shows as a blur. A photo of a waterfall with a slow shutter, using a tripod, will show a luminous sheet of water instead of clear individual splashes.
A more advanced way to use slow shutter speeds is to use an external flash to illuminate one particular object. Imagine that you're photographing four couples dancing in a ballroom, and you want one couple to appear crisp while the others remain blurred in the background. Set the camera on a tripod for a slow shutter speed, then set off a brief external flash. The flash will illuminate the "featured" couple, so a crisp image of them will be captured during that one instant. The other couples, who were positioned so the flash didn't illuminate them, will remain blurred by their motion. Some photographers specialize in light painting, in which the subject moves a brightly colored object through a dimly lit scene, "painting" blurs of light in various parts of the image.
As in all things in photography, the key to finding the right shutter speed for your situation is experience, and you gain experience through experimentation. Try different shutter speed/aperture/ISO combinations until you find one that works, and play around with special effects to see what happens.