Nintendo's NES (aka Famicom) used a custom CPU that was based on the 6502, called the 2A03. It was manufacted by Ricoh. One notable difference from a 6502 is that there is no decimal mode (though the decimal flag can still be set or cleared). Also, sound synthesis and DMA capabilities (used for accessing sprite RAM and Delta-PCM sample playing) were added to the CPU. It runs at 1.789Mhz.
Here is a pinout:
http://nesdev.parodius.com/2A03_pinout.txt
A whole bunch more NES information is on my site here:
http://nesdev.parodius.com/
The Super NES used a 65c816. I don't know if it's customized in any way, but I wouldn't be surprised if it was. It can run at 2 speeds depending on the access time of the ROMs used, either 2.68Mhz or 3.49Mhz(? I'm not really certain about those speeds).
I believe the Nintendo 64 used some kind of MIPS RISC CPU, but I don't know much about that stuff.
Also, Nintendo's arcade games (like Donkey Kong and Popeye) usually used a Z80, and later ones used a 2A03 (or 2) in addition to the Z80.
Finally, I should mention the Gameboy too. It used a custom version of the Z80, but I've never tried programming it so I know practically nothing about it. The Gameboy Advance uses one of those ARM CPUs (I don't know the details), but it also must have the old Gameboy CPU in there since it still runs the older games.