The Doors-
Riders on the StormThat song gave me nightmares as a kid. I remember riding home one dark, rainy night in the back of the family car when I was little. This song came on the radio and it scared me, looking at the window at the rain and wind and lighting flashing through the trees, while Jim Morrison's deep, booming voice sang "there's a killer on the road."

I know it's not THAT scary of a song, but it WAS scary stuff for little kid.
EDIT: just noticed someone else mentioned Warren Zevon. I'm a big fan, he did a lot of creepyish songs, but more funny creepy, than scary creepy to me.
Roland the Headless Thompson Gunner is one of my favorites. Although
Mutineer is my favorite Zevon tune, not creepy just an awesome song.