Aquatic Gallery in Milpitas is nice, nice displays.
If you happen to be eating on Clement Street in San Francisco, wander by 6th Ave Aquarium just to gawk... now I'm not saying that it's a good store, actually far from it. It's crammed and I think the livestock is not well cared for, but it's a bit of a curiosity. Plus "Burma Superstar" is a great restaurant not far from it!
Of course, you have to make your way to the Monterey Bay Aquarium to look around, if you can plan a day trip. Or if you can't, then wander down to the Steinhart's Aquarium on Howard St. (Also called Californa Academy of Sciences). The "Underwater World" on Pier 39 is not really worth it, in my opinion. It has non-tropical fish and is expensive for what it is.
Others will pipe up, mostly to tell you not to go to 6th Ave Aquarium. I'm just saying it's interesting, not that it's particularly good.
V