In order from most to least usefull.
Aquaduct
This is very important, but I tend to not build it because it costs too much shields and I don't really need cities that are over size 6 in the ancient and middle ages. Only my core cities which I use to produce units.
This is definetly one of the most useful city improvements

.
I don't really build these much. Only in my settler factories.
This is very important. I have one on every high-shield-producing coastal town. Because I like naval units and i'm gonna want veteran ones.
This is also very important. I build it in all my cities that produce a reasonable amount of wealth.
I think this should be further up the list because without it, It would take so long to produce any industrial or modern aged unit or improvements. And especially naval units. This is what makes the game exell.
This is in about the right spot. As well as science Lib's also make three culture per turn which is better than a temple.
This can be very important in those pesky towns that are corrupt, though it doesn't help a great deal. However a police station a couthouse and police officers can almost fully reduce corruption. But if the town is corrupt than normally players would irrigate all the squares around it (I do). To get more food. So corrupt towns can support many police officers if the tiles are irrigated.
I don't build this too often because I
think it has only the same research effect as a library and it costs too much shields. I do build them in m y core cities though.
I never build these because I would be building the internet.
This is one of my least useful city improvements. To get culture I would just build a library. I think bank should be in front of this.
Although this costs much it has its uses. I build them in my core cities that produce high amounts of wealth. I need at least eight of these so i can get eight stock exchanges to get the wall street.
These are one of my least favourite improvements. But if a town is in major civil disorder I ocassionally build them. But if I have lots of lux and all my citizens are happy I would never build it.
This is even worse than a cathedral. I would not build these at all.
Now, I agree with you that these are the worst. People always build them. I never build them because...
(a) they knock down if the city is over pop 6.
(b) It makes hardly any improvements in defense.
(c) Say a spearman was defending the town it would only increase the spearmen from 2 to 3. And you wouldn't have these in a town that is producing musketmen because you need more than 6 pop to quickly build the musketman.
(d) I would rather build an archer than waste my shields on this wall.
(e) Most cities don't need defenders on them. The objective is to intercept enemy attacks not wait for them to attack.
These are just some of my opinions

.