Deploying and using it may not be completely painless, but Drupal is a great content management system (CMS) framework that is lightweight, modular, and not too difficult to navigate. There is nothing exotic or excessively "creative" in its construction it's just a nice sensible implementation of PHP and either PostgreSQL or MySQL on the back end. What can you do with Drupal?
The Top Drupal Sites page demonstrates what people are doing with Drupal. In a nutshell, anything that can delivered over the Web can be managed with Drupal: standard HTML pages, RSS feeds, blogs, threaded discussion forums, polls, software distribution, image galleries, multimedia, documentation ... you name it.
Although Drupal works just fine for a lone control-freak administrator, it is designed to allow many people to contribute and manage content. Its appearance is customizable. Don't be one of the lazy ones who doesn't bother to change the logo when setting up a Drupal site. Presumably, you want visitors to see your identity, not Drupal's.
Drupal even comes with tools to help the site survive a Slashdotting, should you be so "fortunate" as to be discovered by the Slashdot crowd. It makes clever use of caching and throttling to keep the site alive until the tidal wave retreats.
Because of Drupal's modular design, you can start small with just the core features, then add or create more as you need them. Visit Drupal.org to find downloads, excellent documentation, and a busy, helpful user community.