There are a couple of different types of intercollegiate sports. I'll assume the question here is geared towards varsity sports, though.
There are two major governing bodies of intercollegiate sports: the National Collegiate Athletic Association (NCAA) and the National Association of Intercollegiate Athletics (NAIA). Within the NCAA there are three levels of competition, or divisions, Division I, II, and III.
Most of the time what you see when you turn on your TV is an NCAA Division I sport. Bowl games, March Madness, the Frozen Four are all NCAA D. I offerings. Most college athletic scholarships are given by NCAA D. I schools, although D. II schools offer athletic scholarships as well.
Playing a D. I sport can be like having a full-time job while also being a full-time student. Student-athletes often, though not always, have scholarships so there is an expectation that those will be earned through continued commitment to the school and the program. In short, the school is paying your tuition and expects you will work hard for it on the court, field, track what have you.
The balance between athletics and academics can be more even at a D. III school. Many students make the mistake of assuming, though, that the competition at the D. III level is not all that challenging. Depending on the school and program, the competition can be at a very high level.
The same goes for NAIA programs. Don't assume they aren't competitive. Make sure you do your homework. It might be that an NAIA or D. III school is the perfect place for you. You won't know unless you look.