Just like facebook when anything is entered at the end of url, is searched and viewed for instance.
www.facebook.com/alex -> profile is viewed that has username alex
www.facebook.com/abc -> username with abc, profile is viewed.
How does this work?
This is a multi-stage setup, and a very broad question you've asked.
Primarily it normally works by having a rewriting filter (using
.htaccess or similar functionality), which redirects to something like
profile.php?user=[username] page. In that page you fetch the username with
$_GET['user'], and do a DB lookup based upon that.