How does a constant-speed propeller work?
Before we get started, I should mention that the most basic constant-speed propellers were driven by balancing air pressure and centrifugal force. The blades were shaped in such a way, or equipped with a counterweight that would cause the blade to intrinsically move to a shallow pitch. However, when air was moved over the blade, it would begin to overpower the counterweight and push the blade to a higher pitch. It's a pretty ingenious design that meant that the prop would automatically be at a high RPM when the plane was moving slowly (takeoff, landing, climbs),  and would automatically move to a higher pitch when the plane sped up for cruise. There is even a single-bladed propeller that utilized this design very efficiently for light aircraft. The problem is that this type of propeller is not pilot-adjustable, and I am assuming you are wondering about adjustable props, so that's what we'll talk about from here on.
In the first picture, observe that the pilot valve is resting directly on the flyweights (separated by a bearing). The second picture better depicts the valve system. So we see that the speeder spring is pushing down on the top of the pilot valve's shaft, which is in turn resting on the flyweights.
First off, the flyweights are turned by the crankshaft, so any change in engine RPM is immediately reacted to.
There are a few ways that the propeller is attached to the piston, and a rack and pinon type gear system is common and easy to imagine. The rack is fixed to the piston so that when it moves, the prop blade's pinion gear is rotated and thus the blade is rotated. Another common type uses a peg that is offset from the center of the blade's circular base. When the piston moves, it pushes or pulls on the peg and because the peg is offset it causes the blade to twist.
Overspeed: When your airspeed increases.
When the RPMs increase, the centrifugal force causes the flyweights to want to spread apart in order to conserve momentum. Because they're in an L shape and have a hinge in the corner, this causes the lower part to rise up, pulling the pilot valve up and compressing the speeder spring. When the pilot valve is raised, pressurized oil from the pump is allowed to flow into the prop hub through a tube that passes through the center of the hub piston. The oil fills a cavity in front of the piston, pushing it backwards. The prop blade is rotated to a higher pitch (closer to feather) which slows the engine RPM. When the RPM is reduced, the flyweights will be under less centrifugal force, and the speeder spring will push them back to equilibrium (on-speed), pushing the pilot valve back down to block the hub oil tube from gaining or losing oil.
Underspeed: When your airspeed decreases.
When the RPMs decrease, there is less centrifugal force acting on the flyweights. Due to their shape as described earlier, they are no longer able to push up on the speeder spring with the same force as before, so the compressed spring will begin to expand. This brings the top of the flyweights in, and their bottoms down. When their bottoms move down, the pilot valve moves down as well and unblocks the hub oil tube. Positive oil pressure in the hub and the blades' built-in tendency to want to move to a low pitch pushes the oil out of the hub and back into the oil system. The piston moves forward and the blades rotate to a low-pitch condition. The flyweights do not necessarily return to equilibrium, especially if you manually set the spring to maximum compression with the prop lever: you want the prop to stay at minimum pitch no matter what.
Loss of oil pressure:
Oil pressure is constantly pushing against the bottom of the pilot valve, trying to force it upwards. The speeder spring is resisting, and while the flyweights are on-speed, the whole system is in equilibrium with the valve blocking the hub tube. When you lose oil pressure, that pressure is no longer holding the valve up and the speeder spring will win, pushing the valve down and allowing the oil to flow out of the hub. This resets the prop to the low-pitch climb setting, giving us maximum power because that's more useful than maximum speed in the emergency situation that is probably about to occur.
So why do we do this?
All propellers are set at a pitch. Theoretically, a propeller is pitched to move forward a certain distance each revolution, just like a screw moving through wood. But unlike wood, air is fluid and difficult to grip. If you imagine screwing a screw into a board, the only way to make it go in faster is to turn it faster. This is because the screw's pitch is fixed. If you could space out the rungs on the screw (increase the pitch) the screw would move through the wood faster but it would become much harder to turn.
An airplane's propeller works much the same way. When we first take off, we need to impart maximum power into the air and it doesn't matter if we don't move forward very far for each revolution, because we're not going very fast anyway. We just want to move as much air as possible. But we will reach a certain point where the only way to speed to is to increase the number of prop revolutions, and that's impractical with our engines. The only alternative is to change the pitch of the blade. We change it to a pitch which allows us to move forward a greater distance with each revolution, in exchange for less power being imparted into the air each revolution. Luckily, we don't need to pump out so much energy, just enough to keep us from slowing down.
If you like numbers, imagine we have an engine and propeller that theoretically (at perfect efficiency) moves forward 1 foot for each revolution, and it can make 1 revolution per second, giving a maximum forward speed of 1 foot per second. That's perfectly fine when we're taking off: we might be moving forward at 0.5 fps, and we have some air being blown backwards a 0.5 fps. When when we level off at cruise, we're stuck at 1 fps. But we want to go 3 fps and we know our airframe can handle it. The easiest option is to turn the engine three times as fast, but unfortunately that would send some pistons flying out of the cowling. So we have to change the prop pitch to one that moves forward 3 feet for each revolution, that way it's still turning at the same rate, but it will move us forward three times as far. Or more likely we're still going 1 fps but only 1/3 RPM and burning 1/3 the fuel! It's here that you can see the issue: if we're burning 1/3 the fuel, we're only imparting 1/3 the energy into the air through the prop, and there's no way we could climb or accelerate quickly (if at all) at that setting.
A final way to think about it is just like the gears on a car. Your transmission allows you to, at 1500RPM, go 10mph or 60mph or any speed in between. The problem is that if you try to accelerate from 10mph to 60mph while maintaining 1500RPM, it will take a much longer time than if you were to bump it up to 2500RPM. High gears are great for cruising and maintaining speed, but are not great for accelerating. They allow you to keep your RPMs low, your torque low, and your fuel burn low, because you don't need that extra energy. A high prop pitch is exactly the same.
A note about multi-engine aircraft:
They are just the opposite of singles. Where singles require positive oil pressure to increase blade pitch, multis require positive pressure to decrease it. This is because in a multi, you want the propeller to feather automatically if you lose oil pressure (because of an engine failure). This removes a significant amount of drag from that side of the aircraft.
They are equipped with either a spring or a nitrogen charge in the hub that is pressurized, pushing the hub piston back to the feathered position. The prop governor works the same way but the pilot valve directs oil to push against the back of the piston, moving it forward and compressing the spring/nitrogen, moving the prop to a lower pitch.
Some are also equipped with an unfeathering accumulator. This is a bottle with a piton in the top (also charged with nitrogen) that fills up with oil when the engine is running. When you bring the prop out of high RPM, the bottle is sealed off from the rest of the system. If you lose oil pressure, the prop will feather like it's supposed to, but there will still be pressurized oil in the accumulator. That way if you manage to get your engine issues taken care of, you can push the prop lever full forward, open the valve to the accumulator, and the pressurized nitrogen will force the oil out of the bottle to put the whole system under positive pressure for a few seconds. This positive pressure moves the hub piston forward and unfeathers the prop.
Now you might think &quot;If multis feather when they lose oil pressure, why are all those twins out on the ramp sitting there with low-pitched props?&quot; Well, they have centrifugal locking pins that disappear when the engine RPM is high enough. If it gets low, they will fall back down into a groove around the hub piston or its shaft and lock it into a low-pitch state. This is not a fool-proof system, however, because the pins have been known to stick, and people have been known to shut the engine off with the prop lever less than full-forward. In that case you either need to get some strong guys out there to manually twist the prop back, or you need to waste a chunk of your starter's precious life turning the engine over to create oil pressure to unfeather the prop. Most engines can not start in a feathered state due to the huge drag from the blades being perpendicular to their direction of rotation.
On turboprops:
Although it can vary engine to engine, turboprops use similar systems to multi-engine aircraft but at much higher pressures and with the ability to enter beta mode (reverse pitch). That introduces problems mainly in that the engine will very quickly overspeed with the blade pitch is 0 and the prop is generating no thrust/load. I'm not going to attempt to explain it because I've never really worked with one. This guy seems to do a good job, though.