Tilt shift lenses can serve a number of purposes...
It sounds as if you're aware of their use to "correct" perspective with architecture, counteracting "keystoning", for example.
You also can use them for multi-shot panoramas... first taking a shot shifted to one side, then to the other, then combining the two. There isn't a whole lot of movement in most T-S lenses, so this use may be somewhat limited.
Another use of the shift is to "dodge" reflections in shiny objects (mirrors, glass, chrome, etc.)... to keep from making a "self-portrait", for example.
The tilt can be used to increase OR decrease depth of field... the latter is often used to give a "miniature" effect to images.
And the tilt can be used to modify the plane of focus in ways that are impossible with a standard lens.
Typically it's the wide to normal lenses that are used for architecture and many of the other effects mentioned above. For example, Canon offers 17mm, 24mm and 45mm T-S lenses (the last is soon being replaced with a new 50mm model).
For macro and close-up work, a short telephoto T-S lens can be used. For example, Canon has long been offering a 90mm and is introducing a new TS-E 135mm soon. With APS-C crop sensor cameras, I've often used the 45mm for close-up work, too. That lens' minimum focus distance is fine for small products, but extension tubes may be needed for higher magnification. The same is true of the original 90mm, which might be wanted for similar work with a full frame camera. The new Canon TS-E 50mm, 90mm and 135mm - due in stores around December 1 - all are also now labelled Macro and are closer focusing than the older lenses, able to do 1:2 magnification on their own (half life size).
Canon's 24mm II was introduced several years ago, an upgrade to the original version of that lens that had been offered since the 1990s. At the same time the even wider 17mm was introduced too. Those two lenses and the three new models coming soon all have dual rotational controls. This allows you to orient the tilt and shift movements as needed, as well as set them up either in or out of phase with each other. The original 24mm, 45mm and 90mm (not labelled macro) all have a single rotational control that serves similar purpose to allow you to orient the tilt or shift movement as needed. However, those older lenses come set up with the tilt and shift movements 90 degrees out of phase with each other, so they may be less versatile. However, it's is possible to disassemble the lens and align the movements with each other (best done at the factory repair facility, so the lens can be checked will a collimeter and calibrated, if needed).
All of Canon's TS-E lenses are manual focus only, but all have the usual electronic control of their apertures via the camera body. I think this is true of most other brand tilt shift (also sometimes called "perspective control" or "PC") too, that most or all of them are also manual focus. Some of the third party tilt shift lenses might also use a fully manual aperture.
Most tilt shift lenses are built like "bricks".... lots of metal and glass! They also are often rather pricey. And, yes, they're typically considered to be fairly specialized lenses. Because of these factors, frequently pros and advanced amateurs buy them, use them for a specific project, then sell them off. So there tend to be a lot of them on the used market. Unless it's a newly introduced model, it's often a good idea to shop around for some possible savings.
You didn't mention what system you're working with.... Canon makes four TS-E lenses currently (17mm, 24mm, 45mm, 90mm) but that will soon be expanding to five (17mm, 24mm, 50mm Macro, 90mm Macro, 135mm macro). Nikon makes four PC lenses currently: 19mm, 24mm, 45mm and 85mm Micro. Schneider makes PC-TS 28mm, 50mm and 120mm for various mounts and formats. Samyang/Rokinon offer a 24mm Tilt-Shift (manual aperture, I think) lens in several mounts. Laowa Optics is offering a 15mm Macro lens that also has a shift feature (but no tilt), in Canon, Nikon and Sony mount. There also have been a number of TS and PC lenses made for medium format cameras over the years, some of which can be adapted for use on modern DSLRs pretty easily. And there are some "tilt" adapters available for systems, mostly for use with mirrorless cameras and adapted, vintage lenses I think.
Finally, the movements of T-S lenses for use on various formats of digital and roll film camera are somewhat limited compared to the movements possible with the sheet film camera movements they emulate. Large format "view" cameras commonly have a lot more range of movement, as well as additional types of movements. "Studio" cameras have the greatest range of movements, while "field" cameras are often limited, but still have greater range. So-called "press" sheet film cameras typically have the least amount of movements. With the field and especially studio sheet film cameras, tilt and shift movements are often available both at the lens plane and at the film plane... plus there are rise and fall (or rise and drop), and swings.... and complex combinations of the various movements.
More info about T-S and PC lenses can be found here:
https://en.wikipedia.org/wiki/Tilt%E2%80%93shift_photography#Perspective_control_lensesAnd here:
https://www.bhphotovideo.com/explora/photography/buying-guide/perspective-control-and-tilt-shift-lensesMore info about large format sheet film camera movements can be found here:
https://www.bhphotovideo.com/find/Product_Resources/largeformat2.jspCheck this out to see just how extreme movements can be with larger format, sheet film cameras!
http://www.rogerandfrances.com/subscription/camera%20movements.html