I think windows has moved away from executing autorun exes from discs by default a few versions ago. But back in the day it would prompt you what to do when you insert a USB storage drive, and just run whatever's set as the autorun if it's on a disc.
The common way to get USB malware to install automatically those days was to modify the USB drive to appear as a virtual disc drive, which worked.
I am currently sitting at my gaming PC, which does have a Blu Ray drive.
I use it about one or two times a year.
Just today I threw in a CD with the driver of my newly installed tp-link AXE5400 (WiFi PCIe adapter), because it wasn't detected on my PC and I didn't have internet without Wi-Fi.
I immediately got a prompt if I want to run the "autorun.exe" on the disc.
So that is still there (Windows 22635.3209, Windows-Insider Beta Chanel).
But back in the day, popping the disk in the drive would have just executed the autorun without even prompting you. Put the disk in the drive, suddenly new application running on your box as you (and generally, back in the day, as local admin). Not even a chance to say no.
The common way to get USB malware to install automatically those days was to modify the USB drive to appear as a virtual disc drive, which worked.