The batting lineup (the order in which the hitters appear) is very deliberately chosen by a team, as the order in which the players with different skills appear is key in optimising the chances of scoring runs. As a general rule of thumb the batting lineup consists of the "table setters", the "sluggers" and the "bottom of the lineup".
Runs and RBI's
In baseball who actually makes the hit that scores the run is just as important as who scores the run (often more valuable). If a player on second base scores a run because of the hitter smashing a double, the hitter gets credited with what is called a "run batted in" (RBI) which doesn't have any value within the game, but is probably more valuable when negotiating his next contract!
The Table Setters
The main job of the no.1 hitter (the "leadoff man") is to get on base. He needs to be patient (for him, a base-on-balls is as good as a hit), quick (once he gets on base, he needs to be a threat to steal) and good at hitting (not necessarily powerfully). Once he's on base, he distracts the pitcher and the fielding side (who have to worry about a base runner, not just concentrate on the hitter).
The no.2 hitter tends to have similar skills and requirements, though he generally needs to be able to bunt as well (to advance the lead off man into scoring position) and speed isn't quite as important.
By the time the no.3 hitter comes to the plate, the hope is that at least one (hopefully two) of the table setters are on base. Usually a team puts its best hitter at no.3, and his job is to "drive in" the runners ahead of him. Ideally he's a powerful hitter (a home run will score himself and the runners) but also able to "hit for average" (and if not drive them in, advance them).
The no.4 hitter is often known as the "clean up hitter", and does a similar job. Often he's even more likely to find the bases loaded, not least because the pitching team may have walked the best hitter (rather than risk pitching to him).
The no.5 hitter is also usually a "slugger" who protects the no.4 hitter (in case the pitching team walk him as well).
The sluggers will generally have poorer hitting averages than the table setters. For the table setters the priority is getting on base, even it's just singles. For the sluggers it's about driving in runs, so power is more important than average.
The Bottom of the Lineup
Ideally, the batting team hope to get a contribution from the no.6 to no.9 hitters, but this is variable. Often the no.9 hitter is the pitcher (who isn't there for his hitting, and often will bunt) and there may be other hitters who are there more for their fielding than their hitting (traditionally any hitting ability for catchers and shortstops is considered a bonus, rather than a pre-requisite).
The mark of a good hitting team is not so much how good they are from 1-5, but how good their 6-9 hitters are. If the bottom of the lineup is poor, then once every three innings the pitching team can expect to have an "easy inning" and they always know that someone not very good is around the corner, which reduces the chances of a "big inning". Remember that the pitching team can often allow a good batter to "intentionally" walk if they prefer their chances with the next guy.
In some leagues (such as the American League in Major League Baseball) teams are allowed to use a Designated Hitter ("DH"). This is a hitter who doesn't field, but hits when the pitchers turn comes up. The Designated Hitter radically shifts the balance of the game - first of all the pitching team don't have an "easy out" once every nine at-bats, and secondly the hitting team can play a player who may still be a great hitter, but whose fielding isn't so great. It's not uncommon for great power hitters nearing the end of their career to DH.
The DH is probably the most debated aspect of baseball. Its supporters argue that it nonsensical watching one in three innings "ruined" because a pitcher is due up and has to do something for which he has no ability, whilst its detractors argue that it takes much of the skill out of managing a game of baseball. Managing your substitutions and getting production out of a pitcher (like getting him to bunt) is much more difficult than simply letting some slugger swing away.
Some teams will have two players "platoon" at a position, a left handed hitter who plays when the opposition starting pitcher is right handed, and a right handed hitter who plays when the opposition starting pitcher is left handed. Equally, a team will find a switch hitter (who can hit left or right handed) particularly valuable for the same reason.
Pinch Hitters & Pinch Runners
A team may use a substitute at any time (whether or not the player replaced is due to hit, fielding, or even a base runner), who comes "off the bench" and plays the rest of the game instead of the player he replaces (unless he is also replaced).
If the substitute comes in to replace a hitter he's known as a "pinch hitter", if he comes in to replace a runner he's known as a "pinch runner".
The most common reasons for using a substitute are to hit instead of a pitcher (late in the game, when losing, and the pinch hitter is then replaced by a new pitcher when the team's turn to pitch comes again), as a tactical change to take advantage of a pitching match-up (a left handed pinch hitter comes in to replace a right handed hitter, because the pitcher is right handed), as a defensive measure (if a team is leading in the late innings, it brings in a substitute who is a better fielder) or as a "pinch runner" (a tactic usually seen when a team is down by one run or tied, because he's quicker than the base-runner, and that one run is critical to tying or winning the game).