Well, there are a number of cheat codes that have always stuck in my mind for various reasons. The first was a password for the Sega Master System (SMS) game "Golvellius". In the game, you have to go through seven dungeons to defeat seven boses, after which you can purchase seven magical crystals that permit you to enter the last level.
But the password
SEGA SEGA SEGA SEGA
SEGA SEGA SEGA SEGQ
started the game from a debug file, where SOME of the bosses and crystals were taken care of, but SOME (like the second) had the crystal yet unbought, and SOME (like the first) had the boss completely undefeted. The rest of the file was also consistently inconsistent like that.
Next, we have the SMS game "Wonder Boy III: The Dragon's Trap", which had a notorious debug code
3YGU PYZ ZY7K NRR
Which allows the player to be play as Wonder Boy's human form (normally impossible past the first part of the game), and gives the player all weapons, armor, and shields, as well as an obscene amount of gold and infinite magic.
The best part of the code, though, is that you can FIND it in the game's instruction book, in a screenshot that shows how, in the game, you retrieve a password.
Then we have another SMS game, "Quartet". I don't remember the exact code, but my older brother was playing around, making up fake codes: "Then I push pause 3 times, reset twice, hold up-left and 2 on controller 2 and... Hey! That wasn't supposed to work!" The code opened up a little bonus stage that was played before the main game.
And finally, this last one is a code that I had heard of once as a li'l-un, and since have doubted that it was even real.
Until hunted it down just now:
http://www.defunctgames.com/shows.php?id=feuds-41(read down, it's not about Wind Waker).
...and those are all the most memorable codes to me.