There are a few attacks in the Smash Bros. series that are water-based. Squirtle is the only character to have water damage in its attacks (and Kirby, as he can copy one of said moves). Mario also has a water attack through F.L.U.D.D., but it deals no damage.
Due to the mechanics of Brawl's hitboxes, water can be both a type and an effect. It is currently believed that the difference is this:
The distinction between these two states is a result of the separation of "attacks that use water to deal damage" and "attacks that should be involved in type effectiveness"; this seems to be done so that certain moves will not participate in type effectiveness without being excluded from being powered up by water stickers (for example, so weak fixed-knockback moves are no easier or harder to escape).
Out of all the effects in Brawl, water is one of the most useful in the Subspace Emissary - it deals extra damage to fire-based enemies, some mechanical enemies (particularily the R.O.B. Squad), and about half of the bosses.
 List of water attacks