How private key is generated from password in Bitcoin-clients
It's not standard across all clients. You mentioned wallet.dat, which I associate with the Bitcoin Core client; that does not use a password to generate private keys; it generates each set of private keys randomly when a new supply of them is needed for transactions.

Some other kinds of wallets, however, are known as HD, or hierarchical deterministic, wallets. When they are initialized they generate a random master key that they use as a basis for generating all of the wallet's subsequent private keys in a deterministic (non-random) way. And many HD wallets, the very first time they're used, display a sequence of natural language words known as a recovery seed. You write down the recovery seed word list on paper and keep it securely secret. If the wallet is lost or destroyed you can input the words into another one of the same type of wallet to re-generate your private keys and hence your coin balance. If you're interested in the technical details, look at the specifications for BIP32 (HD wallets) and BIP39 (recovery seeds).

An HD wallet which uses a recovery seed may allow the user to specify a passphrase, typically intended to be memorized rather than written down, which is used along with the recovery seed to (re-)generate the private keys. For the ones that I've encountered a passphrase is optional.