The Raspberry Pi is just another Linux computer, so your question should really be "Do Linux work with USB Modems ?".
The answer is "yes", but how well it works depends on the modem in question. Like you said there are hundreds of different models to choose from, and some need very special drivers and some might only work with Windows but there are many many devices that can work well with Linux and the Raspberry Pi.
Its very difficult to point to a single guide that would be helpful and not just confusing at this stage.
( As an example, I'll just post this https://www.thefanclub.co.za/how-to/how ... and-wvdial
which is basically a well written guide that would either be well suited or completely useless depending on what modem you buy in the end. )
Find a model that is easily available for you and works with your network provider, then google the name of that model + Linux + Raspberry pi. You will then probably find relevant information. Huawei have a few models that people can find for a nice price and usually works good.
And for choice of OS we always recommend the newest Raspbian. ( Which means that any guide meant for Debian Linux should also work. )
All model of Raspberry Pi will work, but its easier to work with one of the models with 4 USB ports. A PiZero works just as well but you would probably need an external USB-hub.