The ocarina songs that you can learn in The Legend of Zelda: Ocarina of Time are listed below. Not listed is Scarecrow's Song, because obviously that is a song that you make up by yourself.
If you attempt to play these songs before learning them at the right points in the game, they won't do anything special. But once you learn the song from the right place, the song will have a special effect. The first six songs in this list are songs that do some special thing, and the six songs after that are warp songs, which will warp you to different places in Hyrule.
For lack of a better word, the following songs are "spell songs," each having some magical effect. Their effects and the place where you can learn them are listed along with the song.
LURLUR / (3DS: XAYXAY)
Effect: If you play this song while standing on or near a Triforce Symbol, you can open doors and make other good things happen.
Where to Learn: When you meet Zelda in her garden, she teaches you this song.
DRLDRL / (3DS: RYXRYX)
Effect: Playing this song will contact Saria, who will give you some helpful information about what to do next in the game. There are also some other places in the game where you need to play this song in order to proceed.
Where to Learn: At one point in the game, Navi will tell you to go talk to Saria, and when you do, she will teach you this song.
URLURL / (3DS: AXYAXY)
Effect: If you have an empty bottle with you, play this song near a cow, and your empty bottle will be filled with milk. If you're an adult, and you've done the Epona quest, then playing this song will summon Epona to you.
Where to Learn: While you're young, go to Lon Lon Ranch and talk to Malon until she says "let's sing together," then play your ocarina while standing near Malon, and she'll teach you the song.
RDURDU / (3DS: YRAYRA)
Effect: If it's daytime, playing this song will turn it to night time. If it's night time, playing this song will turn it to day time. Also, playing this song near Gibdos (mummies) or ReDeads (zombies) will temporarily freeze them in their tracks.
Where to Learn: Go to the gravestone all the way in the back of the Kakariko graveyard, and play Zelda's Lullaby. Some lightning will break open the grave. Drop in, and go to the back room, and read the wall. It teaches you Sun's Song.
RADRAD / (3DS: YLRYLR)
Effect: Play this song near a blue Time Block (which has the same symbols on it as the Doors of Time that you open to get the Master Sword) and the block will move or disappear or appear. There are places where there is a hidden Time Block. Try the Song of Time if Navi turns green for no apparent reason, and you might find a hidden Time Block.
Where to Learn: After you get the ocarina that Zelda throws into the moat, you will see a cut scene and learn this song.
ADUADU / (3DS: LRALRA)
Effect: Play this song to make it rain. This empties the Kakariko Well, and it's useful in a bunch of other places. For example, play it near a bean sprout to see what happens. Try playing it if Navi turns green for no apparent reason. Also, keep in mind that frogs like rain.
Where to Learn: As an adult, go into the Kakariko windmill and use your ocarina. The angry guy there will see it and teach you the Song of Storms.
The following songs will transport you to the temples of Hyrule. You learn them when you reach each temple (with the exception of the Prelude of Light, which you only learn if you leave and come back to the Temple of Time as an adult, after completing the first temple).
AULRLR / (3DS: LAXYXY)
DADARDRD / (3DS: RLRLYRYR)
ADRRL / (3DS: LRYYX)
LRRALRD / (3DS: XYYLXYR)
ADARDA / (3DS: LRLYRL)
URURLU / (3DS: AYAYXA)