The Steam Workshop is a utility used in the "community" section of the Steam software. It is used to share user-created game content. First, The Elder Scrolls V: Skyrim, then later Valve games such as (Portal 2, Left 4 Dead 2, etc...) and third-party games by various companies uses the Steam Workshop to get mods and other content. (ex. quests, weapons, textures, sounds, campaign, and much more.)
How it works
When in a workshop that allows you to download content, it will have a "subscribe" button. Using it makes it so you will be kept up-to-date with whatever you downloaded. For example: if I subscribed to "Portal 2 map name" every time I loaded Portal 2 (assuming that's what it's used for) it would be at the latest version, ready to play.
When at a page for a Steam workshop item, you can rate it: thumbs up or down. Though, when in Portal 2, at the end of a test, you are asked for a rating for the Steam workshop, without actually going to the page. You also have the choice to comment on an item on it's page.
Games like Team Fortress 2 have the Steam workshop to user's disposal, but not to use to download anything. In this case, they are items crated by users completly able to be used in the game to be suggested. For example, if someone made an item call "EPIC HAT" (assuming it's a hat you can wear) and a high amount of people liked it, it could be added to the game.
The Steam Workshop is now fully available for the games that supported it.