There's alot of good water pokemon out.Starmie,Slowbro,Cloyster,Kingdra,Lanturn,Lapras,Slowking,and Tentacruel are all great water pokemon to use.A few of these are actually some of my favorite water pokemon too.
Starmie:A great special sweeper.Starmie has high special attack and speed stat to sweep most pokemon.Starmie has a great movepool to as it is able to learn such move as Surf,Ice Beam,Thunderbolt,Psychic and Recover effectively.
Slowbro:A great bulky water pokemon with great defense stat.Slowbro can also learn Thunder Wave to paralyze the opposing pokemon along with other great move such as Psychic,Ice Beam,Fire Blast,Rest,Thunder Wave,etc.
Cloyster:The best water Spiker in G/S/C era with great defense stat too.
Kingdra:Kingdra makes a great all around tank pokemon.A unique trait combo of water/dragon make it the only dragon pokemon that is not weak to ice attack in G/S/C.Kingdra is only weak to dragon moves such as Dragonbreath or Outrage.
Lanturn:Lanturn makes a great annoyer pokemon with it's unique traits of water/electric.Being able to learn Parafusion(Confuse Ray+Thunder Wave)makes it a good candidate for a annoyer type of pokemon.Also,Lanturn is one of the very few water pokemon that can't be effected much by the most common weakness of electric types.
Lapras:Lapras makes a great tank pokemon.Lapras has high HP and Special Defense stat that makes it tank effectively.
Slowking:A pretty good special defense pokemon for a water type.Pretty much similar to Slowbro,but the defense and special defense stats are switched around.
Tentacruel:Tentacruel can make a good special wall pokemon that can also attack away effectively too.A pretty cool trait combo of water/poison makes it not very effected by grass weakness.