Ya know when you ask "most useful", it brings up the YMMV disclaimers. Anyhow, I've been programming shells for a couple dozen years or so and I've used all of the ones listed and about that many more. You can write programs in all the shells, but you'll have the best durability and portability by sticking with Bourne-flavored shells (KornShell, BASH and Bourne if you're hard-core).
Shell comparisons get "religious" and UNIX bigots like me are always going to emphasize why the other shells suck. So, my disdain of the C Shell may be a little overblown, but it's based on many years of trying to work around limitations of C Shell that don't exist on KornShell (et al.).
BASH is becoming more ubiquitious (since it's the standard on Linux), but KornShell is now a standard on AIX, Solaris, HP/UX, and most other major UNIX flavors. BASH is slightly more feature-laden than Kornshell, but at a certain point when you get really tricky, you'll want to use Perl (fancy data structures, infinite variety of modules, more powerful parser and definitely better performance for complex algorithms).
One of the most infamous treatises on shell flavor comparisons is by the illustrious Tom Christiansen. This is somewhat outdated, but really fun to read:
http://www.faqs.org/faqs/unix-faq/shell/csh-whynot/
Anyhow, many of these problems are fixed in T Shell, Z Shell and Whatever Shell, but the point is if you want a standard shell, stick with POSIX (ie KornShell). Anyways,
Real programmers use perl.
If you go the KornShell route, pick up "The New KornShell Command and Programming Language (by Bolsky & Korn). If you're running Linux, keep it easy and start with BASH (various O'Reilly books on shells are superb also).
-Phrazz