The Royal Fleet is the naval force directly commanded by the King of the Andals and the First Men. It consists of a large number of ships based at King's Landing and Dragonstone. It falls under the command of the Master of Ships, one of the members of the king's Small Council.
During the War of the Five Kings, the fleet becomes divided between the forces of Joffrey Baratheon and Stannis. When their two forces clashed at the Battle of the Blackwater, losses to the old "Royal Fleet" were bound to be high, as its ships were fighting each other, and in the resulting battle the old "Royal Fleet" was devastated. Stannis lost almost all of his ships, and was forced to withdraw on a few auxiliary sellsail ships that he had hired. The smaller number of ships that Joffrey controlled faired only slightly better, as Tyrion gave no more than token resistance before springing his wildfire trap.
As Stannis Baratheon was his brother Robert's Master of Ships, when the War of the Five Kings breaks out upon Robert's death, a disproportionately large portion of the Royal Fleet (primarily based at Dragonstone) joins Stannis's faction when he declares himself king. This then becomes "his" Royal Fleet. However, Stannis has a very small land army and only has the allegiance of a few Houses from the Crownlands holding islands in the Narrow Sea. Thus Stannis's dominance at sea is one of his few advantages at the outset of the war. Stannis then augments the Royal Fleet by recruiting an additional thirty warships belonging to sellsail and pirate Salladhor Saan.
Matthos Seaworth and his father debate the nomenclature of what to call "the Royal Fleet" where there are several kings in the realm. Later, when Stannis's forces approach King's Landing, King Joffrey Baratheon expects the twenty warships of the Royal Fleet squadron based at King's Landing to sail out to meet them. However, Tyrion Lannister, Acting Hand of the King, has ordered the Royal Fleet to fall back so as not to be destroyed by Stannis's numerically superior forces. Instead he commits a single ship packed with wildfire to the attack and has Bronn detonate the vessel when it has passed through the leading elements of Stannis's fleet. The resulting explosion destroys a large portion of Stannis's fleet and kills thousands of men. It is the opening move of the Battle of the Blackwater.
Most of the original "Royal Fleet" from King Robert's reign was destroyed in the Battle of the Blackwater. Almost all of the fleet had sided with Stannis, and they were mostly wiped out in the wildfire explosion. Afterwards Stannis told the Iron Bank of Braavos that he only had 32 ships left, though they later gave him a loan to rehire the thirty sellsail ships of Salladhor Saan.
Stannis later used his small combined fleet (mostly sellsail ships, and a few surviving Baratheon ships) to transport his remaining few thousand men to The Wall, in time to fight off the wildling invasion of Mance Rayder in the decisive Battle of Castle Black.
What little of the fleet sided with the Lannisters (first under Joffrey, then his brother and successor Tommen Baratheon) was also largely destroyed in the battle. Queen Cersei, however, insisted on quickly rebuilding the Royal Fleet - a massive expenditure, ignoring the ongoing massive debt crisis to the Iron Bank of Braavos (apparently not wanting to remain dependent on House Tyrell's fleets). Going over the financial records, the new Master of Coin Mace Tyrell remarks that rebuilding the Royal Fleet was very expensive and only exacerbated the debt crisis, as the Iron Bank has begun calling in their debts, and they only possess one twentieth of the full amount of money they owe the bank.
Because Stannis's campaign against House Bolton at Winterfell led him inland and away from the coasts, he lent his remaining fleet (docked at Eastwatch-by-the-Sea) to Lord Commander Jon Snow so he could lead a rescue mission to Hardhome. Unfortunately, Snow only succeeded in evacuating a few thousand wildlings to the ships before the rest on land were wiped out by the White Walkers.
In the books
According to semi-canon material approved by George R.R. Martin, the Royal Fleet consists of 210 warships, at least 80 of which are full warships dipping 100 oars or more. The Royal Fleet's flagship is the King Robert's Hammer, which dips 400 oars and is the largest warship in Westeros. The Royal Fleet is divided into two squadrons, a smaller one of 50 based at King's Landing and a larger force of around 160 ships based at Dragonstone. The position of Dragonstone and the size of the fleet there makes an attack on King's Landing by sea almost impossible to mount for any foreign power.
When the War of the Five Kings breaks out, Stannis Baratheon seizes control of the ships at Dragonstone and uses them in his attack on King's Landing. Most of the Royal Fleet's crews and captains stay loyal to Stannis because he had been commanding them as Master of Ships during his brother Robert's entire reign, even leading them into a decisive naval victory at the Battle of Fair Isle during the Greyjoy Rebellion. Unlike in the TV series, the Royal Fleet does participate in the Battle of the Blackwater in the books. The fleet engages Stannis's force and draws them into the mouth of the Blackwater Rush. Tyrion raises a massive chain to trap the fleet in the river and detonates barrels of wildfire hidden on the floor of the river and inside several of the royal ships. This move destroys most of Stannis's fleet, but also more than half of Joffrey's fleet. Stannis was forced to retreat on the sellsail ships of Salladhor Saan, which survived the wildfire because Ser Imry Florent contemptuously insisted that they only follow behind the main fleet as a rearguard.
By the conclusion of the battle, the old "Royal Fleet" was functionally destroyed, with only about a dozen ships loyal to Joffrey still afloat in King's Landing. Another dozen or so survived but were severely damaged and forced into dock for repairs - such as the royal flagship King Robert's Hammer. Worse, the King's Landing dockyards were also destroyed in the blaze, forcing the damaged ships to limp up the coast to Duskendale for repairs, making the situation even more desperate. The rest of the Royal Fleet had been obliterated by the wildfire - including the Queen Cersei. All of Stannis' ships are destroyed, forcing him to rely on the thirty Lyseni sellsail ships of Salladhor Saan to retreat his few remaining men (under 2,000) to Dragonstone. Four or so ships were so far upriver that they managed to escape the wildfire, but were then trapped with the city guarding the river's mouth, so their crews decided to sink them rather than let them fall into Lannister hands (two remained active making piratical attacks on river shipping, but they were still trapped on the river with their capture inevitable).
The Royal Fleet is left crippled after the Battle of the Blackwater, reduced to barely a tenth its former size (and even that is being generous, as half of the surviving ships are crippled and laid up in drydock). Given that the ironborn have seceded from the realm and taken the Iron Fleet with them, and the Royal Fleet was functionally destroyed at the Battle of the Blackwater, this leaves the Redwyne Fleet as the one remaining significant naval force under the Iron Throne's control. This results in the Lannisters becoming even more dependent on the Tyrells , much to Cersei's consternation, as the Iron Throne must now rely on the Tyrells and their Redwyne vassals as their main naval force.
At a Small Council meeting in Season 5's "Sons of the Harpy", Mace Tyrell briefly mentions before being cut off that rebuilding the Royal Fleet after the Battle of the Blackwater significantly added to their debts. This is a nod to a larger subplot in the novels: Cersei grows increasingly annoyed that without the Royal Fleet, the Lannisters have to rely on the Redwyne Fleet under the command of the Tyrells (House Redwyne are Tyrell bannermen; Olenna herself was born "Olenna Redwyne"). Therefore, despite the fact that they are already barely able to placate the Iron Bank of Braavos about their massive debts, Cersei orders a new Royal Fleet to be constructed - a huge expenditure, made at kingdom-beggaring cost, done primarily because she is paranoid about being too reliant on the Tyrells. The new fleet consists of ten massive triple-decked war dromunds.
Cersei essentially paid for the new fleet entirely on credit. In the novels, the Crown owes more than six million Gold Dragons to House Lannister and other creditors: two million Gold Dragons are owed to the Iron Bank, the Tyrells and several trading cartels from Tyrosh (together), and nearly one million to the Faith of the Seven. Cersei manages to get the Faith (now led by the High Sparrow) to forgive the debt the crown owes them, but by granting them the right to re-arm the Faith Militant. She simply does not realize that this is only trading one problem for another. Cersei bluntly tells the representative of the Iron Bank that the crown will not repay them for the duration of the ongoing rebellions (i.e. indefinitely), which makes them retaliate by freezing the Iron Throne's assets and calling in their loans across Westeros. Meanwhile, the Faith did not "loan" Cersei one million Gold Dragons but forgave one million Gold Dragons of pre-existing debt - yet Cersei reacted as if it was a loan, and instead of at least using it for breathing room with the Iron Bank debts, "spent" this money she didn't have on a massive new royal fleet. Pycelle objects to Cersei's decision, but his protests fall on deaf ears.
Cersei also foolishly appointed Aurane Waters as the new Master of Ships - a bastard son of House Velaryon who was a commander in Stannis Baratheon's fleet during the Battle of the Blackwater, but afterwards was captured and bent the knee to Joffrey. Aurane is young and handsome, and Cersei thinks he greatly resembles Rhaegar Targaryen in his youth (the young prince she was once promised to marry before Robert's Rebellion). Cersei is paranoid that her attractiveness is fading compared to the young Margaery Tyrell, so she is easily duped by Aurane's flattery, and puts him in charge of the new Royal Fleet (as part of her overall trend of cronyism, stacking the Small Council with flatters and sycophants who are either incompetent or untrustworthy). She even lets him hand-pick the new crews, against Pycelle's advice to crew them with veteran older sailors from the Blackwater. Aurane hires young men convinced of theft or poaching, to save them from exile to the Night's Watch - the result being that they are personally loyal to Aurane, not the Lannisters.
As soon as Cersei is arrested by the Faith of the Seven, however, Aurane promptly abandons both her and the Lannisters, fleeing King's Landing and Westeros altogether in command of the entire new Royal Fleet.
Rumors then begin to circulate that Aurane Waters used his powerful new fleet and loyal crews to set himself up as a potent new pirate-lord in the Stepstones, calling himself "the Lord of the Waters" (as a play on his bastard surname). Aurane's pirate fleet proved very dangerous and could dominate any encounter at sea, given that they are massive military-grade warships (as opposed to the smaller converted galleys most pirates normally have access to).
Cersei's follies resulted in a threefold disaster: she spent a massive amount of borrowed money during what was already a debt crisis on the new Royal Fleet, she ended up losing the fleet anyway (due to handing control of the fleet to a flatterer of obviously questionable loyalty), meaning the crown was only left deeper in debt with nothing to show for it, and sea trade on the Narrow Sea was now crippled due to inadvertently arming a new and powerful pirate-lord.