You are referring to tethering. It is relatively quick, however it is not as fast as a broadband connection. I don't believe you can use tethering for a gaming console, no. Most carriers also have a monthly cap.
The only way it could be done with a gaming console is if you installed the proper drivers for the phone on it and the only console you could do it on would probably be a hacked PS3, which has wifi built in.
Posted from my CrackBerry at wapforums.crackberry.com
Most people tether due to the ability to use their laptops for web surfing and/or remotely accessing email accounts.
Aside from the obvious business application, my tethering is going to be used where I'm in, say, a coffee shop that doesn't offer free Wi-Fi, or if I'm on the road or track and need to access web-based info ASAP.