Fatal Frame will is the game to get if you want to know just how bad ghosts are.
The original focuses around Miku, a college student who goes to a mansion to find her missing brother. What she discovers is that there are ghosts in the mansion, and the only thing which stands between her and them is Camera Obscura, a camera (no surprises there, then) which can seal ghosts in the photographs it takes.
The game itself is very, very atmospheric, and makes you jump endlessly. The story slowly unfolds through notes, diaries, newspaper clippings, etc. The history of the mansion holds a dark secret: Many secretive rituals took place there.
The game takes several influences from Asian horror films such as Ringu and Ju-On. You can't help but notice the similarties between some of these films and the game - creepy women in white kimonos with long hair, scenes to make you jump, but the game is all the better for it.
This game manages to stand out amoungst the other games in the genre by showing original combat and by being genuinely creepy. The main thing that may put players off is the unoriginal conrols and 'find a key, unlock the door, solve a puzzle' approach. But it is definitely a very scary game.
Apparently, there is also a film in the making. Unless it's directed by Uwe Boll, this could be extremely good.
Fatal Frame has a large amount of devout followers globally, who say that this is the best survival horror game of any kind.
Personally, I think this game is somewhat scarier than Resident Evil and Silent Hill put together, but maybe not as entertaining as the other main games of the genre. But that's just my personal opinion, and I'm sure there are plenty of people out there who would disagree with me.
But if you do want a game that will actually SCARE you, then try and find this or one of the sequels.