The story behind the constellation in Scorpio mythology has many possible origins, but the one that is most often referenced is the story of Scorpio versus Orion.
Orion was a giant hunter, some say the son of the Greek god of the sea, Poseidon. He was considered a great hero, but he had a way of pissing off the gods that both became his undoing, and created the constellation Scorpio.
The primary story goes that Orion was the lover of Eos (Greek mythology), the goddess of the dawn, and bragged about his conquests with her. This of course, made her angry. Then Orion bragged about how he was such a great hunter that we was going to kill every creature on earth.
Normally in Greek myths this is the point where Artemis, the Greek hunting goddess, would take offense to anyone claiming superiority in hunting skills, and cut them down to size. The only thing was that Artemis had a big time crush on Orion and actually found him charming. Her brother, Apollo, the sun god, did not find this claim quite so amusing, as he was also the god of the animal herds. Neither did Gaia, Greek mythology's great earth goddess, for obvious reasons.
So Apollo and Gaia talked and decided that it was time for Orion to go, so Gaia created a giant scorpion to hunt the hunter. This is where Scorpio mythology essentially begins and ends. Orion pretty much knew he was screwed, so he ran, and Scorpio chased him.
Depending on which myth you want to go with, either Scorpio killed Orion, or Artemis accidentally shot Orion in the head thinking she was shooting Scorpio. Either way, Orion died.
The leader of the gods Zeus, once again inserting himself at the tail end of a myth, had been watching the whole thing and put both Orion and Scorpio in the sky as constellations, but placing them 180 degrees apart so that they wouldn't keep fighting in the heavens.
This doesn't give us that great of a look at Scorpio himself. This myth pretty much is all about Orion, and Scorpio gets to be a bit player in his own mythology. Not exactly a tale befitting a major zodiac symbol.
Interestingly, the Scorpio zodiac symbol is often depicted in several different forms, including the shape of an eagle and a serpent, as well as a scorpion.
Unrelated to Greek mythology, these symbols immediately bring to mind the Mexican coat of arms, which depict an eagle devouring a snake, perched upon a cactus. This was the symbol that signified where the founding of Tenochtitlan would be. It is interesting also that if you consider the eagle devouring the snake as Scorpio devouring Scorpio, this coincides well with the fact that a scorpion, when cornered with no means of escape, will sting itself to death.
There is little reason to believe that ancient Greek and ancient Aztec myths share any sort of origins, but it does add a layer of interest to the otherwise short tale of Scorpio mythology.