Some gyms have a nice vibe (mine, no one talks, but there's a huge range of ages, sizes, plenty of women in modest Muslim dress getting their workouts just as much as there are "stereotypical gym users", and plenty of smiles-in-passing).
Mostly everyone's just focused on their own workouts, which is a good thing to remember if you're self-conscious! If I ever think anything about someone in the gym who looks new, it's pretty much just "good for them".
Some gyms have a competitive, testosterone-heavy "bro" vibe, or an Insta "look at me" vibe - I'd avoid those ones, so looking round before you buy a membership is helpful.
If you've got money for a personal trainer, that can help keep motivation and build confidence in using the kit, but if not, something like PureGym is cheap and their app has loads of different workouts you can follow to learn, or there are a million people on YouTube etc with workout suggestions.
Weights are great - you won't bulk up without really heavy focus on doing so, but it helps with bone density as we age, and muscle burns more calories and has better shape than fat, so you'll look more toned and feel better, plus it can create muscle which supports joints, so can e.g. reduce back pain - but it's important to do lifts with the right form to avoid injury.
Bit of cardio and flexibility and balance work as well covers the main areas you need to hit to keep healthy as you age.
If you want to lose weight though, that's pretty much all via diet, but I find gym has huge benefits for how I feel about myself, endorphins are good for boosting and maintaining better mood, and I know I'm setting myself up for a healthier future. Higher protein for feeling full longer and low carb works for me, but fine what suits you.
Lastly - you most likely won't feel "new & nervous" for long! Few weeks of sustained effort and it'll be another thing within your comfort zone, so you'll be able to congratulate yourself both for doing it, and for expanding your confidence and your world a bit.
Good luck!