What are the different types of solder used for?
A great question, and since a textbook could probably be written to answer it, there's probably not going to be any single answer.  I want to provide a general answer tailored to hobbyists, and hope that people more knowledgeable can come in and tie up specifics.

Summary
Solder is basically metal wire with a "low" melting point, where low for our purposes means low enough to be melted with a soldering iron.  For electronics, it is traditionally a mix of tin and lead.  Tin has a lower melting point than Lead, so more Tin means a lower melting point. Most common lead-based solder you'll find at the gadget store will be 60Sn/40Pb (for 60% tin, 40% lead).  There's some other minor variations you're likely to see, such as 63Sn/37Pb, but for general hobbyist purposes I have used 60/40 for years with no issue.

Science Content
Now, molten metal is a tricky beast, because it behaves a bit like water: Of particular interest is its surface tension.  Molten metal will ball up if it doesn't find something to "stick" to.  That's why solder masks work to keep jumpers from forming, and why you see surface-mount soldering tricks.  In general, metal likes to stick to metal, but doesn't like to stick to oils or oxidized metals.  By simply being exposed to air, our parts and boards start to oxidize, and through handling they get exposed to grime (such as oils from our skin).  The solution to this is to clean the parts and boards first.  That's where flux cores come in to solder.  Flux cores melt at a lower temperature than the solder, and coat the area to be soldered.  The flux cleans the surfaces, and if they're not too dirty the flux is sufficient to make a good strong solder joint (makes it "sticky" enough).

Flux Cores
There are two common types of flux cores: Acid and Rosin.  Acid is for plumbing, and should NOT be used in electronics (it is likely to eat your components or boards).  You do need to keep an eye out for that, but in general if it's in the electronics section of a gadget store it's good, if it's in the plumbing section of a home supply/home improvement store, it's bad.  In general, for hobbyist use, as long as you keep your parts clean and don't let them sit around too long, a flux core isn't necessary.  However, if you are looking for solder then you probably should pick up something with a rosin core.  The only reason you wouldn't use a flux core solder as a hobbyist is if you knew exactly why you didn't need the flux in the first place, but again, if you have some solder without flux you can probably use it for hobbyist purposes without issue.

Lead Free
That's pretty much all a hobbyist needs to know, but it doesn't hurt to know about lead-free solder since things are going that way.  The EU now requires pretty much all commercially-available electronics (with exceptions for the health and aerospace industries, as I recall) to use lead-free components, including solder.  This is catching on, and while you can still find lead-based solder it can lead to confusion.  The purpose of lead-free solder is exactly the same: It's an evolution in the product meant to be more environmentally friendly.  The issue is that lead (which is used to reduce melting point of the solder) is very toxic, so now different metals are used instead which aren't as effective at controlling melting point.  In general, you can use lead-free and lead-based solder interchangeably for hobbyist uses, but lead-free solder is a bit harder to work with because it doesn't flow as nicely or at as low a temperature as its lead-based equivalent.  It's nothing that will stop you from successfully soldering something, and in general lead-free and lead-based solders are pretty interchangeable to the hobbyist.

Tutorials
There are plenty of soldering videos on YouTube, just plugging in "soldering" to the search should turn up plenty.  NASA has some old instructional videos that are great, because they deal with a lot of through-hole components.  Some of these are relevant because they discuss the techniques and how the solder types relate.

In general, if you got it at the electronics hobby shop, it's good to use for hobbyist purposes.