Continue reading below the jump
Before we start, I should explain a bit about how magic in the game works. Anybody can cast magic, but the strength of the spells you can learn and use is determined by your skill level with each particular school of magic, which make up the magic skills in the game.
Each spell has a certain budget, if you will. Points get allocated based on whether the spell is touch range or can be shot like a missile, how strong the effect is, how long the effect will last, and whether the effect is on one target or a burst. The more points a spell uses, the costly it is to cast and the higher skill required to cast it.
With that in mind, let's discuss some of the individual schools of magic.
Only someone playing an unarmored mage will appreciate the shield spells, but they're never terribly effective. They don't last very long and the protection they give is marginal compared to level-appropriate armor. Plus, they can't supplement armor past a certain level, so there's no reason to use them if you're going to wear armor.
The water spells might seem useful, but they become obsolete very fast, as it's trivial to find equipment which grants you the exact same abilities. The same holds true for the lock opening spells; the skill takes so long to level that you're likely to be unable to keep up (that is, the locks you encounter will be stronger than your skill with the spell). Given that actual lockpicking is easy enough for any character to do, there's no usefulness here.
The only really helpful spell are the Feather spells, which reduce the weight of whatever gear you're currently carrying. Since this lets you cart much heavier loads out of a dungeon, it's very lucrative to invest in such spells.
Since the monsters you summon can fight alongside you, they often provide, if nothing else, a second target for the monsters you fight. The strange thing is, however, that the game makes no secret of the fact that the creatures you summon are evil. If you run into one in the wild, it is a bad thing. In fact, reputable members of the mages' guild will teach you these spells, including spells to summon undead. Now, necromancy is considered a very bad thing in this place, but summoning a zombie is okay? How do you think that zombie came to be? It's just strange.
The summoned equipment is worthless. It's spread out too far over the skill level, meaning that you have to be a master of the skill before you can summon a chest piece or a helmet, but it's also only an improvement over level-appropriate gear early in the game. Add to that the fact that your own gear can be enchanted (while the summoned gear cannot) and you've got spells which have no point. It was a nice idea, anyhow.
The light spell basically replicates a torch. Of course, given that most of the dungeons aren't all that dark in the first place, these are hit and miss. I've already discussed the charm spells. Invisibility is a weird one. Normal invisibility helps you escape detection, though you'll reappear if you do anything besides walk.
However, there's a different invisibility called chameleon. This is like a stealth effect; it enhances your natural stealth skill. What's game-breaking is if you get chameleon up to the maximum score. At that point, you are completely, irreversibly invisible to every NPC in the game. You can hit someone in the face with a sword and they won't so much as crack a frown at you.
Yeah, let's talk about that last one for a second here. In order to enchant your own magic gear, you have to use these items called soul gems. Soul gems start out empty, and in order to fill them, you have to kill a creature while it has the soul-capturing spell on it. You then can use the soul of the trapped creature to make your boots glow in the dark, or whatever you want to do with them.
Now, you can't use any old soul gem to capture a person's soul. You have to use special black soul gems to do so, and the game widely acknowledges that doing such a thing is evil. Duh.
Quick note: In the game, there's a morality system. This is generally represented by points in either "fame" or "infamy." You get fame points by doing good quests. You get infamy by either killing innocents or doing evil quests.
The game's morality system doesn't keep up with the gems, though. If you steal a person's soul, you don't gain infamy. If you have black soul gems on you, the mages' guild doesn't kick you out. They don't even bat an eye. Vendors who should know better will gladly buy them off of you. It's a bizarre lapse, and really creepy at times knowing that my lightning-shooting mace is powered by a troll spirit and a highway bandit's soul.