Each game is different. There's no one way to mod, it all depends on how the game engine is created.
A lot of Sins modding is working with values in text files, for example. Even creating abilities is just plugging in different effects into pre-set templates.
In Demigod and Supreme Commander it's LUA script, which is more or less actual programming.
Half-Life 2 has its own Source SDK (software development kit) that provides the tools and the backbone for mods.
Probably the only universal thing in all the games is that they need a 3D graphics app (like Maya or 3d Studio Max or XSI) to make the models and texture them. But after that, how you get those models into the game again fully depends on the game engine.
