There are two aspects to your question. Let's take them in turn.
In the general case,
does a mechanical hard drive (not SSD) spinning ALWAYS when the computer is powered ON?
No. There are a number of situations that can cause a mechanical hard drive to spin down. For a trivial example, consider a system with two rotational HDDs, with one of those as the system startup drive and the other used for other purposes (backups, maybe? because backups are always good.)
If you open the computer case and remove the power cable from the second drive (which is allowed by modern interfaces; it's termed "hot removal" in the SATA standard), then pretty obviously the second drive will no longer be spinning, since there is no electrical power for the drive motor, yet the system is still running. Hence, we have shown that "a mechanical hard drive" does not necessarily "[always spin] when the computer is powered on".
On the other hand, the question you probably meant to ask,
in a system with a single mechanical hard drive, is it possible for the hard drive to spin down while the system is working?
which we can answer with yes. The hard drive only needs to be spinning while there is I/O activity occuring on it. Because spinning up takes some non-negligible amount of time (usually on the order of seconds), we generally want to keep the hard drive spinning for a while after the most recent disk activity. However, for reasons of power consumption (rotational hard drives need some 5-10 watts of power just to keep the platters spinning; the exact figure varies, and depends on several different factors), we may want to spin down the drive after a while and accept that the next I/O request is going to take longer to complete.
Different operating systems expose this functionality in different ways, but just about anything reasonably modern will have some way of setting these power savings settings. For example, in modern versions of Windows, you can go into Control Panel and then into Power Options where you can set the hard disk spindown time.
If the system doesn't need to access the hard drive for normal operations, then it's perfectly possible for it to boot from a HDD, take whatever preparatory steps may be necessary, and then allow the drive to spin down while working with data in RAM only, with the hard disk spinning up only very occasionally if indeed at all. An example of such a system could be one that is running number-crunching applications that don't require a large amount of storage.
Modern operating systems, including Windows, often use swap space to both allow applications to allocate and use more memory than the physically installed RAM, as well as to make more efficient use of the much faster RAM. This can cause disk accesses even when those would not be expected, and thus prevent the disk from spinning down. Completely disabling swap may however have unintended side effects, as also explained here. You should also keep in mind that many hard disk drive failures happen during spin-up, so having the disk spin up and down repeatedly may increase the probability of it failing during use.
It's even possible, though I'm not sure modern versions of Windows supports that at all, to build systems that have no local persistent storage capability whatsoever, getting everything they need from another host on the network. Compare for example PXE or Preboot Execution Environment.