I use it to open links in new tabs in Safari. The current Mighty Mouse does this -- you just set a scrollball press to "Button 3" in prefs.
I am miffed that the new mouse doesn't have it, as it means opening links in a new tab is now a two-handed operation (you have to use cmd+click on the laptops as well, but you can do that with one hand).
Still, hopefully they'll be able to add new gestures to it via the Preferences pane, so it might make a comeback.
I use it for closing tabs in Firefox when I don't have two hands on the keyboard (W is on the right hand's side of the keyboard for us Dvorak folks).
A middle click anywhere on the entire tab is easier that hitting the lil' x button. Plus, you can turn off the 'close tab' button it the options and make your Firefox look a little bit cleaner.
Cmd+click even works on some things that middle click doesn't, for example middle clicking on the home, back/forward, or top sites buttons in Safari will do nothing at all, but cmd+clicking will properly open your home page, previous page, or top sites page in a new tab.
Alt+Middle-click allows one to resize a window in most X11 window managers without moving the cursor to a window border.
Middle-click also pastes the clipboard in most X11 apps.
Finally, I used to bind middle-click to next weapon in Quake 3 (since I bound the mouse wheel directions to the rocket launcher and rail gun), and zoom in other games.
