For simple casual use, I think Winamp (with modern skin) is good, particularly for those with large libraries as it has a search bar right in the middle of your screen as the primary way of finding stuff in your library (if your library is huge, searching is the way to find your music). You can define your own library views and so on using queries, so it allows for some customization. It also supports a lot of file formats which is nice.
It's easy to rip CDs and so on with it as well. It basically does what you need from a media player and some things you probably don't need as well.
The absolute best media player I've had the pleasure of using is Foobar2000, but the problem there is that it requires a lot more from the user before he/she can reap the rewards from using it. The core principle of foobar is that it's customizable; you build your own media player and define yourself what it should do using foobar's simple scripting language as well as plugins. After just installing Foobar, it doesn't look that nice or have that much functionality, but by building your own theme or using someone else's and getting more plugins and so on, you can basically add almost whatever functionality you'd like your media player to have. Hence, it is of course brilliant
Foobar2000 is only for windows computers though!