Here's a quick list followed by some comments:
Homeworld, Homeworld:Cataclysm, Homeworld 2
Dungeon Keeper, Dungeon Keeper 2
Populous: The Beginning
Defcon
It has been mentioned twice so far, but I'm going to mention it again:
Homeworld
Resource gathering is strictly for building more ships. Follow that up with Homeworld:Cataclysm and Homeworld 2. Homeworld 2 is easily the most accessible of the three so if you're going to play just one, I'd pick that one, but the original and Cataclysm are superior in terms of atmosphere.
Then there is Dungeon Keeper and Dungeon Keeper 2. These are excellent games. There is base building in a sense, you dig out rooms for your minions/treasure/torture/food/etc, so you build your base, but I always enjoyed the whole experience of building your dungeon, funding it and then ravaging the goodly heroes!
I really like Populous, kind of Black and White light without creatures. You command your tribe, train them as warriors, priests and a few other types and battle other tribes. Your character is a shaman who can cast increasingly powerful spells, but must be in range to do so. You do have to build a base but it never really feels tedious in my opinion.
Along with Darwinia, Defcon (also by Introversion) is a great game too. Any base building is just quick setup of your missle silos, radar, navy and air force, followed by mutually assured destruction.

Z by The Bitmap Brothers
http://www.mobygames.com/game/dos/z
http://www.youtube.com/watch?v=E_j1MlzjiZo

How could I possibly forget that game?!
Z is absolutely awesome game, too addictive and fun to be true :D

I have Z2 lying around somewhere... been meaning to play it since i bought it when it was released. Probably won't work on my new pc now.
Dungeon Keeper 2 is awesome... though you could argue it is ALL base building ;-)
as someone who hates base building, I love it.

BattleForge also has no base building. It's sort of a mix between a collectable card game and a rts. Haven't played it myself though, so I can't comment on the gameplay. (But it's recommended by a friend :) )

I also add to list few games I think no one mentioned before:
Nexus Jupiter Incident (earlier Imperium Galactica) - game focuesd on menaging the fleet, customize ship. I can say it's more complex Homeworld style game
Star Wolves 1 & 2 - one of biggest suprises for me. I expected nothing special but this game is awasome. It's sandbox style game; you have only few ships and base-ship which are gaining experience, choosing weapons to mount on them etc.
And games which I love most : Homeworld series, Ground Control 1&2

Somebody was mentioning that in Supreme Commander you can save your base building configurations and then just click once to build the same base every time. That sounds cool, especially for someone who really doesn't care for base building.
Stardock is having both Supreme Commmander and its sequel, Forged Alliance, on sale for $19.99 this weekend. I'd get it myself, but I don't know whether it has DRM or not at this point. I understand they took it off the first one, but don't know if they've done so for the second one.

I saw the thread title and Myth is the first game that came to my mind before reading the thread.
I definitely recommend sticking with it. Mainly because the sequel, Myth II, is a great game and imo surpasses the first some.
BTW if you don't know you can ply both Myth and Myth II online still, there's a fairly good die hard community for it still left playing it.
Just have to d/l a smal patch to point the game toward the new servers (Since the bungie ones are long gone). You can find al the info at http://www.mariusnet.com/ for it.
The onilne part of tyhe Myth series was one of the main things I loved about it back in the day. Just so many oddball game ttypes and a ton of user made mods/conversions (it was like the half life of the RTS games).
Apart from the Myth series/sacrifice and the others menitoned in this thread already I would add both the Soldiers of WWII/it's sequel and the recent Men of War games (WWII strategy games focused on combat) as well as World in Conflict.

Thanks for all the suggestions. I didn't know there were so many real-time tactics games out there.
I will say that multiplayer doesn't interest me. I generally don't compete very well with the sugar-addicted zerglings that do nothing but play the game 24 hours a day, and I dislike playing with strangers in any capacity, anyway. Since I don't have any friends who would want to play with me (most of them don't even play PC games at all), I'll never touch the multiplayer mode.

Apparently the uninstaller that came with the original release of the game outright deleted the directory in which you'd installed it, so if you installed it to, say, C:\ instead of C:\Games\Myth2, it would delete everything it found on C:\ if you uninstalled it. Supposedly this was fixed.
The game definitely shows its age. Although the gameplay is sophisticated enough to hold up pretty well in most respects, and the tactical battles are fun (if somewhat difficult), the interface and camera controls, as you'd expect from an RTS of that era, are kind of awkward by today's standards. Managing your units can be irritating, because they're so rock-stupid and sluggish (they move about as quickly as Warcraft 2 units on the lowest possible speed setting). Moving so slowly is particularly irritating on the missions where you're supposed to go find something, since it takes a long time to explore the maps.
Myth 3 is probably more modern, but it doesn't seem very well liked by the Myth community.

You know, I really wanted to like Nexus. I really, really did. I love space combat. I'm more of a fighter pilot than a cap ship driver, but I can still enjoy a good space navy slugfest. And I really -really- wanted to like Nexus. The idea of fighting an actual starship sounded great, and it's the reason why I bought games like Star Trek: Bridge Commander.
But while Nexus wasn't bad, it... wasn't exactly what I expected, either. It was pretty, but the combat just felt so... flat, to me. It didn't feel as if any "tactics" I might use made much of a difference at all, the actual gunplay felt really weak. Stuff like that.

Myth II
Homeworld
Ground Control 1 + Addon | 2
World in Conflict
WH 40k: DoW2
Soldiers
Faces of War
Men of War

What about a (apparently) forgotten game? From a time where the mystical and magical "tabletop" game known as Warhammer made it upon our machines?
I am of course talking about... Warhammer: Dark Omen. Instant unit creation, no base building and a head on collisions with RTS brilliance! Is it Amazing? yes!
You could always go with the late Warhammer: Mark Of Chaos. But the loading times are... less the adequate.

It's also one of those games that really grows on you as you play it. Definitely keep your eyes peeled for it. It's well worth it!