From the Super Mario Wiki
Yellow Coins (sometimes known as Yellow Star Coins) are the primary type of coin featured in Super Mario 64 (and Super Mario 64 DS). A single Yellow Star Coin increases Mario's (or one of his friends') coin total by one when acquired. These coins also recover one HP. If Mario collects fifty coins on a stage, he will be rewarded with an Extra Life after collecting a Power Star. If Mario collects one hundred coins in one course, the player will not only earn a secret Power Star, but will also gain two extra lives upon leaving the level. Weak enemies often yield Yellow Star Coins when defeated.
Yellow Coins also appear in Super Mario Sunshine with the same purpose as in Super Mario 64. However, when the player collects 100 coins, the Shine Sprite will not appear right above Mario, but will instead appear at a set area. In Sirena Beach's hotel, there are some Yellow Coins that are fakes since they don't spin and move. They are actually Boos with coins inside them. Just as with the fake Blue Coins, if the player controlling Mario gets too close to a "coin", the Boo will appear.
Super Mario Galaxy and Super Mario Galaxy 2 also feature Yellow Coins, but in these games, their only use was to restore Mario's health. Yellow Coins do not offer Mario any stars if he collect 100 of them; that task was taken over by the Purple Coins. Yellow Coins in Super Mario Galaxy 2, however, serve a greater role than in Super Mario Galaxy, as they are needed to feed to Hungry Lumas for Mario to advance to a new planet.
The Yellow Coins also appear in the Super Smash Bros. series, when Mario or Dr. Mario perform their B up move, Super Jump Punch. Luigi is also able to make one Yellow Coin appear by not sweetspotting his B up.