First a minor point no one has mentioned: in Pirates of the Caribbean the Black Pearl uses chain shot to take down the Interceptor’s mainmast.
Second, something every movie ever made on naval warfare of the period misses: before a battle, every bit of wood that could be replaced after the battle, including furniture, chests, barrels, what-have-you, was tossed overboard (becoming, literally, “jetsam”) so that it couldn’t become the very lethal splinters mentioned in an earlier post. “The Price of Admiralty” by, I think, Keegan, covers this nicely, together with a wealth of other interesting information.
Third, no one has mentioned the time periods involved. The various comments made, while pretty accurate, are frequently specific to a particular period. For instance, no Parrot rifles existed during the time frame when the Disney film could reasonably have been set, which I would take to be somewhere between 1700 and 1820, roughly (the Interceptor is actually a late-18th Century replica). Master and Commander is set, as I recall (been a while, and haven’t seen the DVD yet) around 1813 (the books themselves cover roughly turn of the century to the end of the Napoleonic wars–although I’ll confess to being at book five, and so, I don’t know where exactly they end).
In this time period, naval gunnery used, for the most part, solid ball shot, as has been observed, and some other specialized shot. Explosive shell shot was fired, again, as has been noted, usually from a mortar or petard from a bomb ketch or similar vessel. You can draw some pretty fair conclusions about what got used, and comparative tactics simply by examining munitions manifests for ships in the various navies.
The major combatants of the period, the Spanish, English, and French (the Dutch navy ceased to be much of a threat after Camperdown, but was similar to the British in many respects) all had different approaches to naval warfare, largely imposed by limitations on personnel and leadership. The Spanish generally had the weakest gunnery of the period, and tended to overman their ships with soldiery, intending to fight boarding actions–and the general design of their ships reflected this until a rather late date, sometime in the mid-1700’s (the Pearl herself).
The French navy, its leadership decimated during the Terror and its common sailors inducted as foot soldiers, suffered from seamanship that was, on average, not the equal of the British, in terms of both ship-handling and gunnery (remember, that’s on average; you could always find good commanders and well-manned ships–just not nearly as many as in other navies). Given this disparity, the French were more likely to use chain- and bar-shot to destroy sails and rigging than, say, the British, in an attempt to prevent the enemy from closing to ranges where superior gunnery or better-trained boarding parties could make the difference. Frequently, however, it had better-found ships of newer design.
The British, in spite of brutal discipline and the unfortunate practice of pressing, generally had better crews, and often, although certainly not always, better leaders. The main British strategy throughout most of this period was to close to 100 yards or less in line-of-battle and attempt to hole the enemy below the waterline. If you’ve ever visited any of the preserved ships of the period, such as the *U.S.S Constitution * or the H.M.S. Victory, among the exhibits is the equipment used to attempt to plug such holes: tapered, circular wooden wedges whittled before the battle, pre-tarred leather patches, hot tar, and wooden mallets.
The “long nines” mentioned by the Commodore in Pirates are nine-pound “chasers,” meant to damage running gear at long range during pursuit. In fact, when he says, “Just get him in range of the long nines,” it’s a bit disingenuous, since at that point the Interceptor is well in range of any gun the Dauntless could bring to bear; it may be that it only had bow chasers, although ships of the line–third-rate and above–certainly had some guns at the stern, and often stern “chasers,” although the term doesn’t make the same sense in that context.
There were other weapons used during the period as well, such as the carronades someone mentioned, which were, essentially, sawed-off guns of major calibre; no range, but heavy weight of fire at close range. But then, getting that close was the problem, as the side with the lee guage could always choose to decline engagement, unless, as at the Chesapeake, external considerations prevented that.
Tactically, a stern or bow rake was certainly the most lethal attack. The U.S.S Chesapeake was defeated in 15 minutes by a lesser frigate that outsailed it, getting in at least two stern rakes. When Nelson broke the Franco-Spanish line at Trafalgar, the French flagship, which Nelson’s column passed astern of, took stern rakes from five British men-of-war at pistol range; nine-tenths of its crew of more than 800 were killed.
As for shot burying itself, that’s exactly what it would do it it struck the ground at too oblique an angle, hence the “skipping” technique mentioned (naval gunners would also sometimes play “ducks and drakes,” skipping shot over the water; how the pirates manage to miss every one of Commodore Norrington’s boats is pure Hollywood–must have been the ancestors of Lucas’ storm troopers 
). And a last comment in an overly-long screed, it’s not generally realized what an incredible amount of firepower a ship-of-the-line carried: more guns, and of heavier calibre than a large field army of the period towed around. Only fortresses such as Port Royal could match them (and that’s where you’d find the guns larger than 32-pounders). Had the Black Pearl actually been what it appears, an obsolete Spanish galleon (with a high castle on the poop, for boarding purposes), its foray into Port Royal would have lasted a very few minutes before it was kindling washing up on the beaches.