One game that really really really scared me back then was Clock Tower for the SNES, the first one. IMO, the other entries in the series are garbage, even though I played the shit out of Clock Tower 3 for the PS2.
In the game, you play as Jennifer, a helpless girl who gets adopted by a family of dubious history. You don't have any weapons, and the only things you can do are hide or run. And, lemme tell ya, hiding was one of the most terrifying experiences for me, because you can hear the scissors of the main antagonist as he gets closer, and he interacts with the objects in the room to try and find out where you are.
There are several villains in the game, but the main one is Scissorman, a 9 year old deformed hunchback with a gigantic pair of gardening scissors.
As you get closer and closer to the end of the game, you uncover the dark history of the mansion, and why they do what they do.
By today's standards, the graphics are very outdated and for someone who has played it numerous times, the locations where Scissorman appears become very predictable, but it's still a valid option if you're looking for some old school horror.
There was a project in development which aimed to revive the old Clock Tower experience, called Remothered. Unfortunately, it was cancelled.