My favorite kind of answer to my favorite kind of question:
It depends
Some mods, like mods regarding high-res textures, are single-player mods that do not affect the communication with the server, and can therefore be used irrespective of server configuration.
Other mods, like aircraft mods, require server support in order to work.
In general, the description of any given mod will list if it's compatible with vanilla servers, if it only takes effect in SSP, or if it requires a modded server.
Edit: In response to comments; one piece of clarification:
Some mods function somewhat more like exploits based on the fact that the server allows a certain amount of client-side processing of things like falling. Mods that function by misrepresenting the effects of gravity, for instance, can function without server support, at least until the communication protocol is changed to handle all movement server-side, assuming that ever happens.
- What happens internally to the minecraft application?
Currently, the way Minecraft Mods work is by modifying the minecraft.jar that contains all the game code. This changes the code to allow extra features such as Single Player Commands or Zepplin Mod.
- What happens with the old saved games?
Nothing will happen to your old saved games.
- What is the compatibility of modded saved games versus a vanilla minecraft game?
A modded save game will work fine in vanilla minecraft. However, if you use a mod that adds new blocks (such as a nuke block) having those blocks in your inventory or placed in your world will cause them to not appear or worse.
- What else should I be aware of?
Do note that sometimes mods may not be compatible. This is usually because they modify the same source files, which means that when you install one, and then the other, the other will overwrite the first one, meaning that first mod will not be able to use that source file.
Also, sometimes when I install minecraft mods, minecraft does not start up. If that happens, I use the following command to start minecraft.
java -Xms512m -Xmx1024m -cp "%APPDATA%\.minecraft\bin\*" -Djava.library.path="%APPDATA%\.minecraft\bin\natives" net.minecraft.client.Minecraf
(it goes in the run box which you open by pressing WinKey + R)
Best Answer
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.