Others can correct me, clarify, etc. as they see fit. The conventional wisdom looks about like this…
Leadoff - most importantly, this should be someone who can get on base. Secondarily, you want someone that can take a lot of pitches, either by having a good eye or by fouling them off. This is so that they can get an idea of what and how the pitcher is throwing, and also to help wear out the pitcher by the middle of the game, and get to the (hopefully) weaker bullpen pitchers.
2nd - typically a speedy player, and also one that can put the ball in play to advance the leadoff batter.
3rd - typically the best hitter on the team. If things have gone well, the leadoff guy is in scoring position and the 3rd batter will be able to drive him in. Being the best hitter team, the pitcher will often be more careful around this hitter, which could lead to a walk and multiple runners on the base for…
Clean-up - this is traditionally the best power hitter. You want him fourth on the chance that he comes up to bat with multiple runners on base. A home run will be (hopefully) for multiple runs.
5th - the style of this batter is similar to the clean-up hitter, but he usually doesn’t have quite the power, so he’s dropped a spot.
6th-9th - as far as I can tell, it’s just the remaining few batters who are worse/less powerful than the guy before, which limits their at bats (as you noted already, OP).
In the National League, the pitcher bats, but in the American League, there’s a designated hitter. I don’t know how long he did it, but Tony LaRussa was the Cardinals (NL team) manager and he would sometimes have the pitcher (weakest hitter in the line-up) hit 8th. The idea was that he wasn’t likely to get that many more at bats than in the 9th position, but by having a slightly better hitter in the 9th spot, when the line-up recycled itself, the lead-off through clean-up spots would have one more decent batter in front of them.
Example:
First inning - Hit, out (runner to second), hit (runners on 1st and 3rd), home run, single, out, out, out.
Second inning - Now the 9th batter is up first in the new inning. If it were the pitcher, you could pencil him in for an out, and now the top of the line-up has just two outs remaining. It’s harder to move runners without outs to play with. With a half-decent batter in the 9th spot, it’s not quite the throwaway position that you get with the antiquated NL rules of having pitchers bat. 