Wanna try the naval thread for the naval question? Mostly said in jest.
To answer your primary question, though, it depends on the payload or warhead of the missile in question.
They can and do run the gamut of penetration types, from airburst HE to contact-fused HE to Fragmentation to Shaped Charges to even SAP, and each have different expectations placed on them.
The most general and broad-brush overview I can give is to split all of them into three major types:
Explosive, Penetration, and Fragmentation.
'Explosive' type generally are intended to blow massive holes in the ship, wherever they are aimed, to cause the maximal amount of devastation possible. Frankly, the simplest and easiest way to sink a ship is
to blow giant gaping holes in one of its sides, after all. The missile penetrating the ship's hull before the warhead explodes is not required, punching a hole inwards
with the raw blast effect is still completely viable and was in fact how the Anti-Ship Tomahawk was expected to work. Against unarmored targets, which is most ships anymore (warships or no), the missiles usually have enough terminal speed for the warhead to penetrate all the way through the ship when in top-attack mode and detonate on or just about to the keel, if you were wondering how top-attack profile was supposed to sink a ship.
'Penetration' type generally are designed to damage (or even detonate) vitals such as the power plants, magazines, or fuel storage of either armored ships (such as a Battleship) or very large ships (such as Aircraft Carriers). It's important to point out that almost all modern 'Penetration' type AShMs were designed as 'Carrier-Killers', and this is where most of their purposes lie. They are overkill against most ship types today except Carriers, Superfreighters, and Supertankers.
'Fragmentation' type, which includes Fragmentation warheads, Shrapnel-based warheads, as well as explosive sub-munition payloads, are usually intended to make an absolute mess of a ship's superstructure, exposed/unarmored weapons systems, and electronics (read: radar), as well as making gory compost of the ship's crew. Conceptually they are designed around getting mission-kills rather than sinking ships as most of their damage profile is superficial in nature beyond the systems mentioned.
Most, but not all, major AShMs fall under one of those categories.
That being said, my memory can be a bit rusty at times and my information is old, so I could be wrong. Take that all with a grain of salt.
>It seems the modern ones just strike from the air. Why is this?
Because, ironically, torpedo-profile missiles cause much less damage than sea-skimming, side-attack missiles.
The most devastating place for a ship to have a large, gaping hole blown into it at is where the center of the hole is just above the waterline, ideally with a yard or two of hole below water.
Reason being, although underwater holes can and do cause serious damage, most underwater sections of ships (ironically including post-WW2 commercial ships that actually meet code) are heavily compartmentalized specifically
to deal with potential underwater flooding, which drastically limits the extent of damage that underwater explosions can cause. They basically have to cause enough damage to sink the ship in one go or they're not really effective, and that's nearly impossible to do with side-hit torpedo profiles, and the guidance system required to make it hit the belly of the ship is unlikely to survive a high-speed impact with the water.
The ideal above-water hit, however, continually increases its influence as the ship continues to take on more water, making adjusting for the flooding a continual nightmare and in effect eventually impossible unless damage control can get on it undisturbed. This is because as the ship takes on more water, the submerged section of the hole gets larger and larger, meaning the surface area making contact with the water increases exponentially, which drags that section of the ship further into the water, increasing the rate of flooding, and so on in a self-feeding cycle. One inattentive moment or stress-induced overreaction and the ship capsizes and goes under right then and there.