In the 38 years since the discovery of cuprate superconductors, the theoretical mechanism of high-temperature superconductivity remains unresolved. Recent experimental progress has focused on exploring microscopic mechanisms by using novel characterization techniques. The development of synchrotron radiation has driven significant progress in spectroscopic methods. Resonant inelastic X-ray scattering (RIXS), based on synchrotron radiation, has been widely used to study cuprate superconductors due to its ability to perform bulk measurements, provide energy-momentum resolution, and directly probe various elemental excitations. The RIXS can measure phonons, which bind Cooper pairs in the BCS theory, and magnetic fluctuations and competing orders predicted by the Hubbard model in strongly correlated systems, allowing for the study of their interrelationships. This paper reviews the progress in using RIXS to measure charge density waves and related low-energy excitations, including phonon anomalies, in cuprate superconductors. It also examines the relationship between magnetic excitation and the highest superconducting transition temperature, and provides prospects for future research directions and challenges.