First, I'm just barely old enough to remember using DOS on a machine without Windows -- which I believe is the last time that a GUI/DE wasn't viewed as 'essentially essential' for an end-user computing environment. However, I'm just young enough to remember growing up with Windows for most of my computing life, so it's not like I was indoctrinated into the ways of the command line from an early age. Far from it - I'd forgotten all the DOS commands I knew by the time I turned 10, and I actually only began learning Unix very recently (while in college).
I don't think command-line tools have a UX problem. If anything, we have a teaching problem. Command-line tools have a slight learning curve, but really nowhere near as much as many people think. The story of how I got into using the command-line for all (well, almost all) my daily tasks is a bit too long to post here (unless people are interested), but in short: I went from not knowing the difference between cd and dd all in the span of a month. (Thankfully, I did not have to learn those two commands the hard way!)
Why was I able to do it? Because Unix has a ridiculously uniform design, and is unbelievably modular. UI designers today should strive for the uniformity that my GNU system has. (I use GNU, but this comment applies for the most part to other systems, like the BSD-based varieties, and is largely, though not completely, true even when comparing two different systems!). There are a few wrinkles around the edges, but when you consider the very distributed/patchwork way that modern POSIX systems came to be, it's almost miraculous that the single-dash switches vs. double-dash options distinction works in most cases the way you'd expect. The difference between required arguments and optional parameters is implemented very well in most utilities (it's rare that I have to specify unnecessary flags for required parameters, for example). Combining switches also works the way you'd expect on most utilities, and even the naming is consistent (-h, -v, -l, -a, --help, --verbose, etc. follow the Principle of Least Surprise (POLS) in all but very few cases). Even if there are differences between, say, ls -G on BSD and ls --color on GNU, that's a matter of the way those basic utilities were implemented, and the interface itself is still fairly uniform. (And I'm willing to grant some leeway for such fundamental utilities as 'ls', since these tools develop over a period of time).
That's not to say that everything has to follow the Unix-like model. I'm not saying that Opa's efforts aren't valuable simply because they're not standard POSIX. On the contrary, I think that tailoring a command-line tool to the task at hand can be very valuable - SQL queries fall into this category as well.
But they're valuable because they provide a modular (scriptable) tool that modify a familiar interface to make it easier to use in a domain-specific context with minimal violations of the POLS. That's fundamentally different from improving the UX of the command line; instead, it's applying the general UI principles of command line tools to a specific context. All instances of a general UI violate the POLS in some way by definition; the goal is to minimize that.
In the end, I can't think of any other UI that follows the Principle of Least Surprise as well as Unix does, and it's the most fundamental command-line tool in my life. We may have a problem with the command line, but the command line does not have a problem with UI.
(Edit: Does someone know how to write an asterisk without causing it to be parsed as the opening of an italicized block, as in <asterisk>-nix to refer to the wildcard 'Unix-like'? I'm on choppy in-flight internet and can't figure this out reliably with trial-and-error).