If you're getting into the N64, I recommend reading this list I put together in another topic:
Top 50 and Bottom 25 Nintendo 64 games list
The N64 has many fantastic games. Here's a variety of good games to get you started:
ACTION/ADVENTURE:
Indiana Jones and the Infernal Machine
The Legend of Zelda: Majora's Mask
The Legend of Zelda: Ocarina of Time
FIGHTING:
Killer Instinct Gold
Mortal Kombat 4
Super Smash Bros.
FIRST-PERSON SHOOTERS:
007: The World Is Not Enough
Goldeneye 007
Perfect Dark
Quake II
Turok 3: Shadows of Oblivion
PLATFORMING:
Banjo-Kazooie
Conker's Bad Fur Day
Goemon's Great Adventure
Mischief Makers
Rayman 2: The Great Escape
Rocket: Robot on Wheels
Super Mario 64
PUZZLE:
Bust-a-Move '99
Pokemon Puzzle League
The New Tetris
Worms Armageddon
RACING:
Beetle Adventure Racing
F-1 World Grand Prix series
F-Zero X
Mario Kart 64
Ridge Racer 64
San Francisco Rush 2049
Snowboard Kids
Star Wars Episode I Racer
Top Gear Overdrive
Top Gear Rally
Wave Race 64
World Driver Championship
RPG/STRATEGY:
Command and Conquer 64
Harvest Moon 64
Ogre Battle 64: Person of Lordly Caliber
Paper Mario
StarCraft 64
SPACE SHOOTER:
Star Fox 64
Star Wars: Rogue Squadron
Star Wars Episode I: Battle for Naboo
SPORTS:
1080 Snowboarding
All-Star Baseball series
International Superstar Soccer series
Ken Griffey Jr.'s Slugfest
Mario Golf
Mario Tennis
NBA Courtside 2: Featuring Kobe Bryant
NFL Blitz series
WCW/NWO Revenge
WWF No Mercy
WWF Wrestlemania 2000
OTHER:
BattleTanx: Global Assault
Blast Corps
Mario Party series
Pokemon Snap
Nuclear Strike 64
Resident Evil 2
Sin and Punishment: Successor to the Earth (Japan and Wii Virtual Console only)
Space Station Silicon Valley
Have fun!
EDIT: And here are N64 games I felt were over-rated (not horrible, just over-rated):
Diddy Kong Racing (It's a good, polished game, but I prefer racing games like Mario Kart 64 that don't have overworlds/hubs.)
Donkey Kong 64 (Amazing graphics, and a solid game, but too much back-tracking. It gets a little tedious after a while.)
Excitebike 64 (Great graphics, but the controls could've been better.)
Jet Force Gemini (Great graphics, but too much back-tracking. And I got confused sometimes where I should go next.)
Shadow Man (Great graphics, but I never knew where to go next. Too confusing. And the sound of Michael's gun is extremely annoying.)
Turok: Dinosaur Hunter (Pretty good, but I thought Turok 3 was better in every way. And first-person shooters shouldn't require accurate jumps to proceed!)
Turok 2: Seeds of Evil (Amazing graphics, but I never knew where to go next. Too confusing. I thought Turok 3 was more streamlined.)
Slightly under-rated:
Castlevania: Legacy of Darkness (A bit more polished and fleshed-out than Castlevania 64, which is also a perfectly good game.)
Iggy's Reckin' Balls (A fun, quirky racing/platforming game.)
Mystical Ninja Starring Goemon (Could've used slightly more polish, but still a very good second-generation N64 game. And excellent music!)