When somebody mentions 'Scuba diving', they generally mean recreational scuba diving, which is where most divers start their diving career.
Recreational scuba diving
To be classified as a recreational dive, a dive has to be a maximum of 40 meters in depth, a maximum of 40 meters from the surface, a no-decompression dive, and using only compressed air as the breathing gas.
Most students choose to get an Open Water certification, which allows you to dive to 18 meters / 60 ft. If you dive beyond this limit, you're diving beyond your certification, which leads to all sorts of nasties, including not being covered by insurance.
After your Open Water certification, you can go get additional Advanced training, which includes theory and practice of doing a deeper dive. Most things when diving deeper are exactly the same as when you are at shallower depth, but things like Nitrogen Narcosis (basically, feeling drunk due to the surrounding pressure of the water), and the fact that you go through your air four times faster at 30 meters than at the surface, mean that a little bit of extra training helps. With the advanced training, you can go down to 30 meters/100 feet.
If you want to go deeper still, you can take a two-dive Deep Diver speciality, which allows you to dive down to the recreational diving maximum limit - 40 meters.
There are many reasons why you shouldn't dive beyond 40 meters, but the most important reason is that oxygen becomes toxic to the central nervous system at high pressure, and can cause seizures and blackouts when you exceed a partial gas pressure of 1.4. Since normal air has around 21% oxygen, that means that you can't breathe compressed air beyond 56 meters under the water - and since we like to be conservative, the extra 16 meters is your safety buffer.
You can dive beyond 56 meters as a Tec diver, but if you want to do that, you have to breathe a gas mix that has less than 21% oxygen in it. Obviously, you need 21% or more at the surface, so that means switching gas mixes, calibrating gauges etc underwater, at a depth where you are likely to be suffering from (or enjoying, all depends) Nitrogen narcosis, which impairs your motor skills and ability to concentrate. Not for the faint of heart - and definitely beyond the realms of normal recreational diving.
Distance from the surface
Cave, cavern and wreck diving can be part of recreational diving (after additional training), but the 40-meter rule still applies: If you are diving a wreck at 25 meters, you cannot go further than 15 meters into the wreck. If you do, you are further than 40 meters from the surface, and should probably be looking into Tec diving courses.
Without going fully into decompression theory (I will some other time, don't worry), sea water is bloody heavy. At the surface at sea level, we have about 100km of air above us, in the atmosphere. This is defined as 1 atmosphere of pressure, and it's the pressure we're used to in our everyday lives. (If you live on a mountain top, this is slightly less, but that's not really relevant in this discussion)
To get another atmosphere of pressure in sea water, you only have to go down 10 meters. Go down to 20 meters, and you have 3 atmospheres. 30 meters, 4 atmospheres, and 40 meters will take you to 5 atmospheres. So, within the limits of recreational diving, you are exerting 5 times more pressure than usual on your body.
Liquids don't compress much, so your body is perfectly fine under the water. Air spaces, however (your ears, lungs, sinuses, and your diving mask), need to be equalised - but that's pretty easy for all of them as well: Lungs get equalised every time you breathe, your mask gets equalised if you breathe out through your nose, and your ears and sinuses can be equalised by pincing your nose shut gently and blowing gently.
The problem, then, is that we breathe a lot of nitrogen. At the surface, we breathe around 79% nitrogen. No problem: our bodies don't do anything with this nitrogen, so we just breathe it in and out. A typical lungful is about 5 litres - so you'd breathe 3.95 liters of nitrogen.
At depth, however, we are under a lot of pressure. Gas under pressure becomes soluble in liquids (a phenomenon known as Henry's Law - "The solubility of a gas in a liquid is directly proportional to the pressure of that gas above the surface of the solution") - and as we said, your body is a lot of liquid, so when we go down to depth, our body takes on gases. For Oxygen, that's not a problem - your body will use it and burn it off. For Nitrogen, however, it starts to saturate your bloodstream and body tissues.
At 30 meters, you are under 4x the pressure as you were at the surface, so that same 5 litre breath you took now actually takes 20 litres of air from your scuba cylinder, and 15.8 litres of that is Nitrogen. Since your body is now 4x undersaturated of gas compared to at the surface, this nitrogen starts to dissolve into your body tissues. Again, no problem.
The problem happens when you ascend, because as pressure decreases, the gas comes back out of solution. This needs to happen slowly. Why? Well, have you ever shaken a Coke bottle, and then opened it really fast? The same thing happens in your body. The gas comes out of solution, and forms bubbles. If you come up too fast (or have too much gas dissolved in your body), these bubbles can cause serious problems: anything from a mild tingling to strokes, coma or death.
In recreational diving, we use dive tables or dive computers to keep track of how much nitrogen we have in our bodies at a given time - by staying within those limits, we decrease the risk of DCS (Decompression Sickness). By using the tables or computer conservatively, we stay within the no-decompression limits, and are always able to ascend (slowly - at a rate of 9 or 18 meters per minute maximum, depending on which tables you are using) directly to the surface.
If you overstay your no-decompression limits, you have a problem: There is too much gas dissolved in your body's tissues to be able to surface, and you have to make a decompression stop to give your body time to offgas. Dive computers have made these calculations a lot easier, but it's still not good practice for recreational divers.
The original definition of recreational diving stated that you should only breathe normal compressed air. This is air that has been filtered through filters and molecular sieves - but it's still the same air you would breathe at the surface: 21% oxygen, and mostly Nitrogen for the rest.
These days, a lot more people are diving on Nitrox / EAX. This is a gas blend which still contains oxygen and nitrogen, but it has been enriched with additional oxygen - not because you need the oxygen, but because it removes some of the nitrogen. This means that less nitrogen gets dissolved into your body, giving you much longer time under the water without having to worry about DCS. Typical gas blends are Nitrox32 and Nitrox40. The number refers to the percentage of oxygen in the mix.
The big difference between Tec diving and recreational diving in the gas mixes, is that tec divers tend to switch their mixes under the surface, whereas recreational divers keep the same gas mix for the entire dive.
Where do you go from Recreational scuba diving?
Apart from recreational scuba diving, there are a whole series of other dive disciplines you can get into, including Tec diving, Commercial diving, Public safety diving, Scientific diving, Military diving ("Combat swimmer"), and many more.