How do you open / enable the console in Single Player mode?
The "console" isn't a part of Vanilla Minecraft, it's part of the Single Player Commands mod. By installing the mod, you can type in commands (listed in the thread linked) by hitting T, as if you were chatting in SMP.
Here's a video from the developer showcasing the commands.
As single player now has commands as well1 (albeit most depend on the "cheats" option on world creation): press the / (slash) key (or whatever key you have mapped to the "command" option in key mappings).
1 Since V1.3.1.