Like always, it seems I have a big list for these kind of threads.
I'll just name a few that comes in my head.
Well I know one thing for sure, that there were quite a few creepy moments in Zelda: Ocarina of Time. Which were…
1. Some of the enemies in this game.
Dead Hand
The only way to get this monster appear is to have the hands (in the background from the image above) grab you. I just find Dead Hand's moving gesture to be a little disturbing.
Redeads
Do I really need to say anything about these enemies? These guys will creep the heck out of you. They make eerie moaning sounds, they will freeze you if they stare at you and will attack you in a really inappropriate manner. Luckily, there is a way to stop these guys, but no matter what, I still find them creepy.
WallMasters

They're not entirely that terrifying, but it's when they grab you from the ceiling is what makes them scary.
I could name a few more enemies, but that could be almost all the enemies from the game.
2. The Shadow Temple and From the Bottom of the Well Dungeon
From all the Temples that you need to go to, I find the Shadow Temple to be the creepiest temple/dungeon to go to. It has eerie music that gives you goosebumps, there are a lot of creepy enemies in the Temple (like the ones I've mentioned earlier) and just a creepy set up with blades and creepy statues. It's also my least favourite Temple from the game (not just because it's scary, but also annoying too).
As for the From the Bottom of the Well dungeon, it's basically the same as the Shadow Temple, but with a round about layout and you play as Young Link.
3. When Stalchilds appeared in Hyrule Fields at night as a Child

It's not the enemies that scares me, it's how they appeared is what startled me about them. They don't scare me that much these days compared to when I was a kid.
You could say that Majora's Mask was a creepier Zelda game than Ocarina of Time and I would agree on that argument too, but since I've began playing Majora's Mask a few years ago, I couldn't find moments that scared the heck out of me, except maybe a few. (Which I might mention those moments later).
Also, who ever played Super Mario 64, did any of you guys find this moment to be scary? Once you get close to the piano, it becomes a Man Eating Instrument! AHHHHHHH! 