If you download a single table, you will get a CSV. This CSV can be imported back into Salesforce using their Apex Data Loader, Import Wizards, or other fancier tools like DemandTools. (FYI: the Apex Data Loader only runs on Windows.)
If you download your Metadata, you will get a .zip file which contains xml files that describe your metadata and a package file that lists all the metadata in the .zip. You can open this with the Force.com IDE (or equivalent) to manage it.
If you perform a full Salesforce export, you will receive an email with a link to download a .zip file. You will need to extract that .zip file to get the exported data. Please note that full exports are currently disabled
If you download a file, you will receive that file in its native format (.pdf, .xls, .doc, .gif, etc.).