Why are there so many opcodes in Bitcoin Script, but they are not usable
Those opcodes can be used in non-standard scripts as part of a Pay-to-Script-Hash address. They can be in the redeemScript of the P2SH address and be standard. 

Furthermore, standardness is not something that is set in stone; it is not a consensus rule, rather it is node policy. So in the future, we may create new script types which become standard that use other opcodes. Such scripts may be non-standard now (as P2SH and Segwit were in the past) but can become standard in the future. Those opcodes exist so that we can make scripts which perform smart contract-like things without needing a fork.