Matrix diagonalization is more general than the spectral theorem. For instance, you may not be in an inner product space, and it still may be helpful to diagonalize a matrix. Not every matrix can be diagonalized, though; for instance,
$$\left[\begin{matrix} 1 & 1 \\ 0 & 1 \end {matrix}\right]$$
has eigenvalues 1 and 1, but cannot be diagonalized.
The spectral theorem tells you that in a certain situation, you are guaranteed to be able to diagonalize. Even better, the eigenvectors have some extra structure: they are orthogonal to each other.
If a matrix is diagonalized, its diagonal form is unique, up to a permutation of the diagonal entries. This is because the entries on the diagonal must be all the eigenvalues. For instance,
$$\left[\begin{matrix} 1 & 0 & 0\\ 0 & 2 & 0 \\ 0 & 0 & 1 \end {matrix}\right] \text { and }\left[\begin{matrix} 1 & 0 & 0\\ 0 & 1 & 0 \\ 0 & 0 & 2 \end {matrix}\right]$$
are examples of two different ways to diagonalize the same matrix.
Write down a square matrix, $A$. Now, raise it to the power 100. Not so easy, is it? Well, it is if the matrix is diagonal. It's also easy if the matrix is diagonalizable; if $P^{-1}AP=D$ is diagonal, then $A^{100}=PD^{100}P^{-1}$. So, computing high powers of matrices is made easy by diagonalization.
And why would you want to compute high powers of a matrix? Well, many things are modelled by discrete linear dynamical systems, which is a fancy way of saying you have a sequence of vectors $v_0,v_1,v_2,\dots$ where you get each vector (after the first) by multiplying the previous vector by $A$. But then $v_k=A^kv_0$, and voila! there's your high power of a matrix.
Best Answer
The name is provided by Hilbert in a paper published sometime in 1900-1910 investigating integral equations in infinite-dimensional spaces.
Since the theory is about eigenvalues of linear operators, and Heisenberg and other physicists related the spectral lines seen with prisms or gratings to eigenvalues of certain linear operators in quantum mechanics, it seems logical to explain the name as inspired by relevance of the theory in atomic physics. Not so; it is merely a fortunate coincidence.
Recommended reading: "Highlights in the History of Spectral Theory" by L. A. Steen, American Mathematical Monthly 80 (1973) pp350-381