Dwarves with dabbling or no experience as Swimmers will start drowning immediately upon contact with deep surface water (ie surface water of 7/7 depth). Those of novice level experience or greater can be in deep surface water without drowning. Any dwarf will start drowning in 7/7 water if there is more water on levels above them (eg they are at the bottom of a two level deep cistern, or the bottom of the ocean).
Any conscious, uninjured, not stunned dwarf finding themselves in water of depth 4/7 or greater will try to leave if they can find a path out of around 20 tiles in length or less. If they can't find an exit within that distance, they won't move.
Novice swimmers are able to get out of deep water safely without needing a ramp or stairway, but they will start drowning if stunned. Once that happens it can be difficult to get them out, as they lose the ability to exit anywhere and behave just like an untrained or dabbling swimmer. Everyone is stunned by falling into water rather than entering it calmly, which is what normally happens when they aren't entering it of their own free will.
Adequate swimmers do not panic and start drowning in that situation, even when attacked, so training to this level is highly recommended. Higher levels only increase the speed when swimming, with a legendary swimmer being faster than he would be while running.
Note that you can drown in magma as well, but most creatures tend to melt first.
 Learning/Teaching swimming
A dwarf in the water will gain the ability to swim very fast - sadly not fast enough to prevent death from drowning. While water with a depth of 7/7 is deadly for non-swimmers, 6/7 or less will not harm any dwarf. So you can use water from 4/7 to 6/7 safely to teach your little ones how to swim. The speed of learning is independent of the depth, but water with a depth less of than 4/7 is not deep enough to make a dwarf swim, and therefore learn anything.
Training your little ones just requires a place of constantly or temporarily 4-6/7 water. Military orders or making rooms a meeting hall will not entice dwarves into the water, so you may need to prevent them from leaving an area (locked door, etc.) and then fill the area with the required amount of water, or dump them in from above using a retracting bridge.
Swimming, since it involves no activity, can be potentially useful to train physically handicapped dwarves, whose conditions might go away or become manageable with an attribute boost to strength, endurance, willpower etc.
A fully automated method to train idlers is to use water flowing over a 1-z drop, with a 1-wide meeting zone at the top of the ledge, and a swimming pool at the bottom. Idlers will go to the meeting zone, be swept over the side into the pool and swim to the ramp, and repeat this for as long as they are idle. The meeting zone must have a low enough rate of flow that it has unsubmerged tiles, so dwarves voluntarily move into it. This can be accomplished with tricks like restricting flow through diagonal passages (see Pressure for details). Be aware that in recent versions dwarves can now suffer injuries more easily when falling, so this method can cause serious harm when the dwarves are washed over the edge.
Diagram of the 'fully automated' configuration described above: .... - (pool continues as desired) ║≈≈≈≈║ - depth 4-6 swimming pool on Z-1 ║+++▲║ - dropoff / entrance ramp from above ║~~~~║ - meeting hall, depth 0-3 ╚╗%╔═╝ ║%║ - screw pump (S->N) ║.║ - limited pump source (e.g. depth ~4-per-tick tile on Z-1) ╚═╝ Not pictured: exit from the swimming pool, preferably close to the entrance ramp to minimize delays in training.
 Adventure Mode
In adventurer mode, as a novice swimmer, by moving carefully ( +direction) into open space above water and selecting the option to move below (such as West/Below), you can swim about without getting stunned and starting to drown. To get out, alt-move carefully against a shoreline and select the option to move above.
Swimmers can also dive and rise through the z-axis by pressing and respectively. Note that air-breathers will be unable to breathe without air in the tile above them, and without returning to the surface will eventually drown. (Sadly, there's no oxygen meter as of yet, so you'll never know when they're about to expire. Don't linger too long.)
In adventure mode, water preference can be switched between "when possible" and "necessary" by pressing.
If the temperature (press) is "freezing" or if it is "cold" and close to sundown, the water may freeze while you are swimming, which instantly kills you and leaves your frozen corpse encased in ice and a valuable find for archeologists.