For the most part
CPU Fan header is for the fan that cools your CPU
CPU_OPT header is for the 2nd fan on a CPU air cooler
Sys / Aux fans are for case fans and other fans.
Dependoing on the MoBo, they may be all PW< or not...most common is PWM for CPU / CPU_OPT. Z87 CHA / SYS / AUX are almost all DCV and Z97 is mostly both.