Trust me - we've all been there...
It's worth it when it all comes together - the improvements over the vanilla game are EXTENSIVE and you'll love it!
In effect there is no difference in sp. You download and install the mods where they need to be. updater does this automatically for you, but you can do it manually.
You then just tell Arma2 what to load when you start it up. I.e.
Load arma Combined ops with the following mods @CBA_CO;@ace;@acex@ etc etc etc.
once in the game they most mods will appear as active by showing up on the right hand side of the load screen. it will literally say @ace @ CBA etc on the right hand side.
For major mods, they will have names and icons on the bottom of the screen. This means they are installed and working.
Most vanilla single player scenarios will not have been created with mods in mind, so they will make little distance. Sometimes mods like ACE can break the single player missions as the AI behave differently. Your mileage may vary. But at least this way you know the mod is in and running.
You can of course find scenarios designed to work with mods i.e.
http://www.armaholic.com/page.php?id=11570 This one clearly states which mods you will need to get it to work or play properly. These are a great way to experience what the mods will do before you go Multiplayer. Obviously the ACRE comms side is MP only
Most good mods become common and are often called for i.e. CBA, ACE, ACEX etc. Others are specific to that scenario. like for instance a particular map or weapon pack.
That guide I referenced earlier was pretty good to get me up and started, I just then picked and added mods where needed to improve game-play or give me different options.
Youtube is also a great way to figure stuff out as if you search for how to's there are normally loads - here's a quick search I did
http://www.youtube.com/results?search_q ... oPdxmSLQzoSJ