The Rogue in World of Warcraft's Cataclysm isn't quite the "stun lock you into oblivioon" class that they were, once upon a long time ago, but they're not bad these days, not at all. While you can no longer faceroll your way to easy wins in all categories the Rogue is still competitive in PvP, PvE, and is fun to play.
The new (with WoW 4.0) Recuperate ability makes leveling even more painless than before. Recuperate is a Heal over Time ability (a HoT) and it practically eliminates downtime.Since then the base ability has been improved a bit and the talents weakened. The overall effect is about the same.
The three specs are Assassination, Combat, and Subtlety.
The Assassination Rogue is the poison master. Many of the abilities in that spec either increase damage done by the Rogue's poisons, enhance its effect, or add additional effects, such as Energy recovery (via Venomous Wounds.) This is the hardest hitting of the Rogue specs with the only downside being no special movement abilities and having to remember to keep the blades poisoned.
The Combat Rogue is a little like a leather wearing Warrior, at least in concept. The talents and abilities generally work towards damage and faster weapon usage. Think of a Combat Rogue as a Swashbuckler. They can do they stealth, but have no special abilities in that area. This is also the only Rogue spec that has any abilities oriented towards fighting more than one opponent at a time.
The Subtlety Rogue is the Ninja type. Her abilities are designed to do massive damage from stealth, but aren't so great at continuing damage. Subtlety has always been the PvP Rogue spec, but it's also good for PvE (though you probably won't top the damage lists) and is great fun while leveling.
Speaking of leveling, I suggest picking Subtlety at level 10. The only downside to this is energy, you won't have a lot until level 33, when you can have three points in the Energetic Recovery talent. The upside is yummy. Your Ambush ability hits so hard that you can use your Shadowstep to Ambush and the opponent is done, just like that. At mid-levels this is positively brutal.
With Improved Ambush and talents that add damage, talents that add more damage when coming out of stealth, and talents that add combo points, well... Shadowstep to Ambush to Eviscerate will leave pretty much destroy anything you encounter while leveling and will get you a lot of kills in PvP. You won't be so hot at fighting several opponents in a row, but by picking your fights you'll be deadly.
If running up and smashing things is more your idea of a good time then the Combat Rogue is a good spec to pick for leveling. The energy recovery is very good and you'll have a fun "flashing swords" style of play. With the 4.1 patch series the Combat spec is a bit behind Assassination as a DPS spec for dungeons and raids, and Subtlety is probalby "better" for PvP. If you like Combat's style then ignore all that and go for it, the difference isn't that much.
As far as gear, gems, and enchants go it's all pretty easy. Anytime you can get more Agility then do it. Everything else is a distant second, especially for Sub. For PvP you'll want Stamina and lots of Resilience. The next best secondary stat for Rogues is Haste, especially if you're going to be switching specs from time to time. Mastery is great for the Assassination spec, but bad for Subtlety. Haste is pretty good for both, as well as for Combat.
In gear that can be gemmed you will usually have some sort of socket bonus. Just stick a red Agility gem in those sockets and forget the bonuses. Try to get Agility and Attack Power for enchants.
Now sneak out there and gank something.