Probably the clearest explanation would involve doing a quick test: go out at night, and take two photos: 1 using a very short shutter speed, and one using a long. If using, say, shutter priority to manually select the shutter speed, and letting the camera adjust everything else automatically, you'll find the short shutter speed will require: aperture to be as big and wide as humanly possible, and ISO to be cranked up as high as possible...which means you get super-shallow depth of field (from the big aperture) and you get lots of noise/grain (from the high ISO). Often at higher ISOs, you lose some color fidelity too. The alternative would be to try a smaller aperture or lower ISO, which will result in a massively underexposed photo, because there wasn't enough light for the camera to expose. Now, 2. take a long exposure. In this case, the camera can use a much smaller aperture, since in order to get the light needed to expose the scene, it just keeps the shutter open longer, sucking in as much light as it needs. Similarly, no need to crank up the ISO since the camera pulls in the light by staying open a long time. End result is a bright, nicely colored, vibrant, full-of-detail shot with no noise or grain and plenty of depth of field to keep a landscape or wide scene in focus. Of course, exposing a long time requires a tripod, and also won't work well when you have movement in your scene - anything that moves will blur or streak. Sometimes that's desirable (lights from a car, etc), sometimes not (blowing leaves and branches turn to blurry mess).
So why use either mode? One would use the shorter shutter speeds when needing to photograph a scene at night which involves motion, either by the photographer or the subject. High ISO as needed, wide open apertures as needed, and a shutter speed which can be sufficiently handheld without motion blur, or to freeze a moving subject. One would use the longer shutter speeds when exposing a nice still scene, wanting maximum detail and color, well exposed, with no noise or grain. With no movement to worry about, the shutter can be left open to pull in plenty of light even on a dark night...if things do move through the frame, they will streak or blur which might be a fun effect if used intentionally.
Examples: I wanted to photograph a ship's worker on a quiet deck at night - he was moving, I was moving, no tripod around...so in order to expose this shot, I needed to crank up to ISO1600, use a fairly big aperture of F2.2, so that I could get a shutter speed of 1/125, which was fast enough to freeze the movement:
The side effects, which in this case were desirable, were that the depth fo field is very shallow - note how the areas in front of the man and behind him are both out of focus and blurred...also, color is much more muted, and there is some graininess in the photo.
In this case, I wanted good color, maximum details, and didn't have any worries about motion (building wasn't going anywhere, and I had a tripod)...so I used the lowest ISO to avoid grain and used a 10 second shutter to let the camera suck in all the light it needed:
In digging through my gallery, I found two examples of a very similar shot, one done handheld with a short shutter speed, and one done on tripod with a long shutter...it gives some idea of the difference in the look, the detail, the lighting, and the color that alters with each method...
Handheld, ISO800, 1/4 second exposure:
Tripod, ISO200, 5 second exposure:
Both methods have their purpose - longer exposures will always result in the nicer color fidelity and saturation, and ability to pull in more light than visible with the eye if desired...and also provide the most details and least noise when paired with the lowest ISO...while the short exposures are the only way to freeze a moving subject or shoot while handheld in low light conditions.
When it comes to longer exposures...whether to use 5 seconds or 15 seconds...that depends on how light you want to make the scene, or whether you want to use a smaller aperture to control blowing highlights or to increase depth of field - which would require longer exposures, or whether you want to go a little shorter to avoid everything looking too light and losing the nighttime feel.
Sony DSLR-A68 / Sony 18-250mm / Minolta 50mm F1.7 / Tamron 150-600mm / Minolta 300mm F4 APO
Sony A6300 / 18-55mm F3.5-5.6 / 55-210mm F4-6.3 / 10-18mm F4 / 35mm F1.8 / 16mm F2.8 / FE70-200mm F4 G OSS / FE70-300mm F4.5-5.6 G OSS / via manual adapter, lots of Pentax K mount, Konica K/AR mount, and Leica M mount manual lenses