Good question. See here and here for more.
The most common name in the Jewish Bible for God is spelled in Hebrew letters that would roughly correspond with YHVH in English (Hebrew doesn't always use vowels); this is known as the "Tetragrammaton", i.e. the four-letter name of God. Jews don't pronounce that as written, instead they pronounce it "Adonai", which means "my lord." The conventional English translations of the Bible (e.g. King James) translate this as "The Lord."
Rabbi Aryeh Kaplan's traditional Jewish translation translates this name simply as "God."
You are correct about its first appearance being in Genesis Ch. 2. Genesis Ch. 1 is taking a cosmic-scale look at the universe, and thus uses the name "Elohim", i.e. "All-mighty." (The point is that one, all-mighty God, is responsible for everything in creation, rather than a pantheon.)
The Tetragrammaton is generally used vis-a-vis a more personal relationship with God; it also is usually used when referencing God's mercy, whereas "Almighty" is usually used in the context of harsh judgment. Still one God, just different perspectives.
Or as Rabbi Kaplan puts it:
For the rest of this chapter, the Torah uses two names, Adonoy Elohim, usually translated as 'the Lord God.' In the earlier chapters, only the name Elohim (usually translated 'God') was used. According to tradition, Elohim denoted a creation with unmitigated justice, whereas the name Adonoy denotes an admixture of mercy. Since there is no simple, contemporary way to translate Adonoy Elohim, we translate it as 'God'.
From when the structure of the world is in place (Genesis 2:4) until Adam is figuring out his post-Eden existence (Genesis 3:24), the scripture uses the combined name "Adonai Elohim." The first time "Adonai" is used on its own actually comes out of Adam's mouth in Genesis 4:1, when he becomes a father and says, "I now have a son, thanks to [Tetragrammaton, pronounced "Adonai."] This name is only used vis-a-vis God.
Now if this wasn't complicated enough, sometimes a name is spelled out literally as "Adonai" (i.e. the Hebrew letters spell ADNY, not YHVH). Most English translations don't have a good way to distinguish between ADNY and YHVH-which-you-should-pronounce-ADNY. Sorry. ADNY, spelled as such, can be a person referring to God, his master; or could be a form of respectful address from one person to another.
You'll find such an instance in Genesis 18:3. Abraham is having a prophetic experience, when he pauses to invite some guests. He starts by addressing ADNY. It's unclear (i.e. the traditional Jewish commentaries suggest both explanations) if he's asking God to be excused so he help the visitors; or if he's addressing the visitors respectfully.
So far so good?
You're probably thinking about Psalms 110:1. Here's how it's spelled:
The saying of YHVH to ADNE
(Clearly two different Hebrew words.)
Here's how it's pronounced:
Ne'um Adonoi Ladonee
- YHVH-pronounced-ADNY is always God.
- ADNY could be God or "my master", addressing someone of importance.
- ADNE is always "my master", addressing someone of importance. (Jacob says it to his brother Esau approaching with a big army; as does Judah to the mysterious Egyptian ruler who's tormenting him.)
The translation is thus:
God said to my master.
The workers in the Temple are singing this about God's choice to have King David prepare the construction of the Temple. Thus, Joe Temple Worker sings, "God said to my master, King David ..."
The translation "the Lord said to the Lord" does not fit the text.
