Funny. But not so much. Even during the era where black powder muzzle loaders saw their greatest level of development and use, sinking enemy ships was unusual and not even a primary goal.
It's common sense, really. Firing randomly at a large timber object doesn't cause it to magically fill with water.
Quite correct. Usually, the aim of the cannon was to eliminate crew, and to destroy sails and masts.
For the former, graqeshot at close range works well to kill surface deck hands manning and trimming sails.
Round shot at distance to eliminate crew belowdecks with wood splinters, but requires heavy cannon to be effective unless being used against a light cruise ship, like a pinnace.
For the latter, chainshot at close range also works.
The problem with these tactics in a world filled with magic, is that a few mages on deck could fire area-effect firestorm spells at YOUR ship before you get into range, and end up doing several forms of damage at once: It ignights your sails and rigging, kills crew, and catches the deck on fire-- which, if you are armed with cannons, can get to the powder magazine, and make your ship explode.