From Automation to Autonomy: A Survey on Large Language Models in Scientific Discovery