In the 38 years since the discovery of cuprate superconductors, the theoretical mechanism of high-temperature superconductivity remains unresolved. Recent experimental advances have focused on exploring microscopic mechanisms 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 applied in studying cuprate superconductors due to its ability to perform bulk measurements, provide energy-momentum resolution, and directly probe various elemental excitations. RIXS can measure both 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 excitations and the highest superconducting transition temperatures, and provides an outlook on future research directions and challenges.