I was a police officer at the airport. Speaking from experience in law enforcement, I will tell you that when "it" happens, you don't have time to stress out over it. Your training kicks in, and you do what's necessary to get through the moment, also relying on your team - co-pilot, and other flight crew. When it's all over, especially after your first incident or two, the severity of the situation sinks in and you just sort of sit there in disbelief. Those first few incidents will add to your self-confidence over the years, so, not only do you rely on training, but also, experience.
In your flight training, you will use a flight simulator, and face emergencies. So, you will have handled them before, just, not for real. As to feeling safe, you have to have confidence in the other components of the program. That includes your flight crew, but also your aircraft mechanics, the ground handlers, and Air Traffic Control.
If it's something you want to do, go for it! And, possibly consider going into the military first. The military will instill self-confidence in you, and hopefully give you some aircraft related training.
From what I've seen, I'd say in terms of the actual flying, probably commercial pilots don't really find moments scary. There's a lot of training and preparedness goes into commercial aviation, and statistically it's about the safest method of transport there is. There are so many safety features and procedures that have been set up to keep everyone safe and make everything rather smooth and routine - a lot would have to go wrong before a commercial pilot was in a situation where they're likely to feel scared. I'd guess a commercial pilot is more likely going to be scared by other aspects of the job - things like worries about failing their medicals are going to be bigger scares. I hear one of the scariest moments in piloting is what happens if you mess up significantly, even if everyone is fine - getting told a phone number to call to investigate your potential pilot deviation - that strikes fear into any pilot.
It's a quite different world in light aircraft general aviation, however - although pilots are still trained to include a large safety margin and not to fly outside of their "minimums", I think a lot of general aviation pilots experience moments when things aren't going to plan and it can get a bit scary.