Pin headers in general are just what the name suggests: pins. Those pins can be connected to various things, such as power lines, data lines and signal lines.
As already explained, the one on the Pi is documented and it is known what they all do. If you check out a different device, you might see headers with pins in them as well, but unless they are documented or you traced out their connections, you won't actually know what they are for.
Some pin headers have power connections and nothing else. Some might only have audio, or only a few GPIO connections. GPIO stands for General Purpose Input/Output, which are basically programmable connections. GPIO is very flexible, but in general is not capable of high speeds (as in: not as fast as PCI Express or even USB) or high voltages. This also means that you probably won't have any luck trying to program SATA-over-GPIO as it's most likely not going to work at all due to the speed being too low and unpredictable from the software side of things to actually communicate with a SATA device.
Pin headers can also be explained as simply being "plugs". They have no standard use or universal system and are commonly pretty device-specific.