Counter-Strike is the original game. Multi-player terrorists versus counter-terrorists. Some variety in missions with the counter-terrorists trying rescue the hostages, stop the bomb, or kill all of the terrorists. Half-Life (original) era engine so runs nicely on lower end computers. Strictly multi-player only (no single-player, no story line) and only player-versus-player so no computer controlled participants ('bots) involved. You can edit maps with the original WorldCraft level editor. May be able to use the Hammer editor - not sure.
Counter-Strike : Condition Zero updates the original with a few enhancements. Condition Zero has 'bots (computer controlled participants) so a basic single-player mode is possible and you can play multi-player with the 'bots as well. Multi-play with 'bots lets you have all the players on the same team (humans versus 'bots) or lets you play mix of human and 'bots on both sides. Still original Half-Life engine so good for lower end computers. You can edit maps with the original WorldCraft level editor. It is supposedly possible to use the Hammer editor as well but I've never done it.
Valve doesn't sell CS and CS:CZ separately any longer - you get both when you buy the basic Counter Strike package ($10) via Steam.
Counter-Strike : Deleted Scenes was an attempt to create a more involved single-player game that had not just a single-player off-line mode but a storyline as other popular games do - e.g. the original Half-Life etc. This game never really worked out and Valve pulled it as an official title. Included in some of the retail (boxed) versions of Counter-Strike and also included in most of the CS-related packages via Steam.
Counter-Strike : Source is an overhaul of the game to use the new Source engine that was used for Half-Life 2. Includes a 'bot for off-line and on-line games. Graphics are significantly updated. Includes use of the Havok physics engine though not a great use of that engine - some of the mods explore this area better than the base game. Because of the better graphics and physics engine this game requires a higher-end PC than the older versions - you can turn down some of the effects but it still is generally a more demanding computing environment. You must use the newer Hammer editor to make levels for this game - can't use WorldCraft.
Steam offers a variety of packages available that include CS:Source ranging from stand-alone purchase ($20) to part of the Valve Complete Pack ($80)
Valve migrated their developer website from Valve ERC to the Valve Collective to the Valve Developer Community. The newest Developer Community site is wiki-based which has some benefits for finding the information you want but if you working with the older games you may find the older sites good as it doesn't appear that all of the old information migrated forward to the newer developer sites.