To start:
1. Mods can entail many things such as new races, planets, and almost everything ingame can be changed.
2. You can get them on the Sins Forum or Moddb, those are the places to get the good ones.
3. There are only 2 types with Sins, first there is TC( total conversion) which is a mod where all or most of the items ingame are removed and replaced. There are also addon mods which add extra things to the game to improve it
4. Downloads are really simple, just download it and place it in the mod folder, to find the mod folder, use locate mod folder while ingame and it will show where the folder is located.
5. If you install the mod incorrectly you can mess up the game, which can be fixed by reinstalling.
6. All mods are made for a specific version of Sins, whether it be for basic Sins, Entrenchment, Diplomacy. Most are made currently for Rebellion or are being upgrade to work with it, it all depends on the creator of the mod which version they make it for.