Mod_rewrite can do lots of things - serve up virtual HTML pages, require no extensions, etc. For example, I can have a directory on my site called "rewrite", and in that directory ONLY one file - index.php. The contents are below:
<?php
echo "{$GET['id']} is really cool, especially when combined with {$GET['id2']}";
?>
So obviously you can do stuff like http://webserver/rewrite/?id=test&id2=test2 or http://webserver/rewrite/index.php?id=test&id2=test2. But with mod_rewrite, I can construct a powerful REGEX query to allow me to do something like this: http://webserver/rewrite/text.html/cool.gif and PHP will see it as "index.php?id=text&id2=cool". That's how powerful mod_rewrite is.
BTW, if you install Wikipedia without mod_rewrite, the installation will notice this and disable Wikipedia's mod_rewrite features.