Will I be able to make this work in vanilla Minecraft, or do I need a mod to enable it?
What features (if any) do I gain by accessing the console?
Thanks to Tobias for the suggestion, from this thread.
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.