sour cream....

It changes the texture, for sure, but you have to be careful because you are using it to replace a liquid, i presume.  If you replace milk, it will be like adding butter too, but some recipes don't do well if there is more butter - for others it's fine.

It tends, with other sour things like yoghurt or buttermilk, to make the cake more tender. 

I put them in my pancakes. Maybe slightly off topic but... they definitely make it difficult to handle but the end result is worth it.  Definitely more moist and spongy.

My mum always used soured milk when baking scones - but I don't often have any to hand.  It certainly made the moistest scones ever!

Well I know the high lactic acid content of things like sour cream, buttermilk, yogurt, or soured milk all help neutralize baking power providing extra lift to the flour.

Experiment and find out! Remember that with more acidic ingredients, you should start swapping out some of the baking power with baking soda. Baking soda leavens in response to acidity (double-acting baking powder leavens in response to acid and heat). The more acid you add, the more you can depend on acid-based leavening. Baking soda also neutralizes the acid in the leavening reaction, eliminating the sour flavor.

My mom would add mayo. I'm no pastry cook so I have no idea how it worked but it did make standard recipes more moist.

Mayo might work instead of egg or oil, but definitely not instead of milk, sour cream, buttermilk, etc.  Mayo may seem "creamy" but it has no milk!

I use a kind of sour fermented milk in bread, with very good results.