Basically any type of "standing" water (ocean, lakes, rivers, etc) will affect them, while "moving" water (rain, waves, etc) won't. Basically it's a mini solid piece of the sea so that's why Devil Fruit users are affected by that as well...

Why can't Devil Fruit users swim?

Supposedly, if you eat one, you will gain devil powers... but the sea will hate you and take your ability to swim! Gigantic tidal waves created by the power of a Devil Fruit. ... Additionally, all Devil Fruits are known to taste very bad and cause the consumer to permanently lose their ability to swim.

Is there a Devil Fruit that lets you swim?

The Sui Sui no Mi is a Paramecia-type Devil Fruit that allows the user to swim in the ground or walls, making the user a Free-Swimming Human (遊泳自由人間, Yūei Jiyū Ningen^?). It was eaten by Senor Pink.