In my (admittedly) fairly short career I don't think I've been truly scared whilst flying. Just very very 'concerned' on more than one occasion...
Overcoming fear and doubt is par for the course in learning to do just about anything worthwhile. 4 personal milestones come to mind:
1st lesson on stalling approx 5hrs into PPL training. Seriously thought about giving it up after that lesson. Eventually managed to overcome the fear through learning aerobatics but lost a lot of sweat in the process. Have since used that particular experience to overcome fear in other areas of my life.
1st solo circuit. I was doing pretty well and looking like going solo after approx 3hrs of circuits until I had a brain melt for 4 or 5 lessons and couldn't land well consistently. Started to dislike the whole flying caper. Eventually clicked one day, the instructor got out and I finally got to experience that impressive 700ft/min max climb rate of the C150!.
1st solo cross-country. Sweaty palms all the way to destination. Mucked up the circuit and had to go around from short final, but have never been more proud of myself after shutting down. Was a bumpy ride on the way back home until I decided to change my flight plan and climb above the inversion. Absolute heaven thereafter. I couldn't get rid of the smile for days. Still brings a smile to my face just remembering it.
1st solo aerobatics. Very nervous and full of doubt. Just getting out of the control zone was difficult enough, couldn't seem to focus on anything. Preparing for the first aileron roll, I'm glad the harness was tight, I think my heart would have popped out of my chest otherwise. Got the first one out of the way and enjoyed the rest of the flight. Almost needed corrective surgery to remove the grin from my face.
And that's what learning to fly is all about - fear, doubt and elation all rolled into one neat package.