The scariest movie I saw when I was a kid was
Psycho. The shower scene didn't scare me nearly as much as the reveal of Mother. When Lila Crane went into that basement and swung that chair around and saw the dessicated corpse, I just freaked.
A few years later, I went to San Antonio with my family. We visited the Alamo and right across the street was a Ripley's Believe It or Not Museum and a wax museum right next door, Tussaud's Plaza Wax Works. They had a scary section of the museum, featuring characters from classic horror stories (the Wolf Man, the Phantom of the Opera, etc.) as well as real life killers. I only got twenty feet into this room before becoming rooted to the spot by Mother. They had set this wax figure on a turntable to precisely mimic the reveal in the movie, and unluckily for me it was just starting to turn slowly to the right. I ran right out of there and never looked back.
Other than that, the only scary film I can remember making as great an impression on me was
The Omen. I still remember Keith Jennings (David Warner's character) being decapitated by the pane of glass and the head bouncing in the frame. Yikes!
The Exorcist didn't scare me at all, nor did its re-release, with all the 'shocking' language intact, do any more for its reputation. I'm sorry, but any little girl, demon-possessed or not, shouting out "your mother sucks c----s in hell!" is instantly risible.
The only other movie I can say was seriously traumatizing to me was
The Great Santini. It's not a horror film at all, but I grew up in an abusive family situation not at all dissimilar to the one in that movie, so it really bothered me. I never did finish watching it and I still haven't tried to see it again.