A long thin spike means you have more surface area and more weight and most of it isn’t going to be useful space to put anything in. For subsonic flight a blunter nose is actually better because it has a lot less surface area to create drag than a pointy nose, and it makes the aircraft overall shorter and lighter.
For supersonic flight pointier noses work better.
Blunt noses are fine if not preferable at low speeds because the airflow can start to move out of the way before it hits the aircraft. The air in front of the craft pushes on the air further ahead to allow for a smooth transition.
Supersonic travel does not have this. The air is ‘notified’ of the oncoming plane right as the plane comes up on it because pressure waves cannot travel out ahead of the plane. This makes pointier designs better in this region.
At very *very* high speed, a blunted nose forces the shockwave to form farther in front of the vehicle, protecting it from the heating of the air that forms at the shock.
A long, pointy nose is great for supersonic travel because it pierces through the air and helps dissipate the shockwaves experienced past the sound barrier (think *Concorde*). However, it’s worse for subsonic speeds because there’s more surface area than on a blunt nose, and therefore more drag. They’re only used on craft expected to spend most of their time travelling faster than the speed of sound.
To your examples: neither rockets nor the space shuttle travelled supersonically for enough time for it to make much of a difference; by the time they’re going fast enough to really get the benefit of a pointed nose, they’re pretty much out of the atmosphere so air resistance is nil anyway. On top of that, weight savings are everything in spacecraft, a few kilogrammes saved on takeoff might equal a few extra tonnes of payload you can get into orbit.
As for missiles, they’re small enough and travel for such a short amount of time that they wouldn’t see much benefit from a pointed nose. Again, not worth it – a missile is fired and hits its target in a matter of seconds.
All of these types of vehicles experience different environments, so it’s important to study them individually.
Regular rockets have to work under their biggest limitation, mass. They don’t spend much time in the thicker parts of the atmosphere, as they launch straight up and only start pitching over to horizontal gradually after a few dozen seconds have gone by. As such they’re better off with the reduced mass of a rounder nose than with marginal gains from a slightly more aerodynamic sharper nose that weighs significantly more.
The shuttle is a special case because, unlike regular rockets, the orbiter itself has to survive re-entry. At the speeds at which it hits the atmosphere, the compression of air forming shockwaves in front of the orbiter causes it to heat up to extremey high temperatures. With a sharp nose, those shocksaves stay very close to the spacecraft, which means the heat transfers faster into it. A rounded nose moves those shockwaves further away, creating a buffer layer of air that slows down the heat transfer. This is also why all space capsules have a rounded, almost blunt bottom.
Subsonic aircraft like passenger jets are built to be as efficient as possible above anything else, because that saves fuel and fuel costs money. Conversely to common intuition, below Mach 1 rounded noses are actually more aerodynamically efficient.
Lastly for missiles and other supersonic aircraft, you’ll find their noses are indeed sharp as you would expect. This is because sharp noses are the most aerodynamically efficient at supersonic speeds, but these aircraft don’t go fast enough for heat to be a major concern.
Latest Answers