This is a purely and relentlessly thematic/Doylist set of categories.
The question is: What is the magic for, in this universe that was created to have magic?
Or, even better: What is nature of the fantasy that’s on display here?
Because it is, literally, fantasy. It’s pretty much always someone’s secret desire.
(NOTE: "Magic" here is being used to mean "usually actual magic that is coded as such, but also, like, psionics and superhero powers and other kinds of Weird Unnatural Stuff that has been embedded in a fictional world.")
(NOTE: These categories often commingle and intersect. I am definitely not claiming that the boundaries between them are rigid.)
I. Magic as The Gun That Can Be Wielded Only By Nerds
Notable example: Dungeons & Dragons
Of all the magic-fantasies on offer, I think of this one as being the clearest and most distinctive. It’s a power fantasy, in a very direct sense. Specifically, it’s the fantasy that certain mental abilities or personality traits – especially "raw intelligence" – can translate directly into concrete power. Being magical gives you the wherewithal to hold your own in base-level interpersonal dominance struggles.
(D&D wizardry is "as a science nerd, I can use my brainpower to blast you in the face with lightning." Similarly, sorcery is "as a colorful weirdo, I can use my force of personality to blast you in the face with lightning," and warlockry is "as a goth/emo kid, I can use my raw power of alienation to blast you in the face with lightning.")
You see this a lot in media centered on fighting, unsurprisingly, and it tends to focus on the combative applications and the pure destructive/coercive force of magic (even if magic is notionally capable of doing lots of different things). It often presents magic specifically as a parallel alternative to brawn-based fighting power. There’s often an unconscious/reflexive trope that the heights of magic look like "blowing things up real good" / "wizarding war."
II. Magic as The Numinous Hidden Glory of the World
Notable examples: Harry Potter, The Chronicles of Narnia, H.P. Lovecraft’s Dream Cycle
The point of magic, in this formulation, is that it is special. It is intrinsically wondrous and marvelous. Interacting with it puts you in a heightened-state-of-existence. It is – ultimately – a metaphor for The Secret Unnameable Yearnings of Your Soul, the glorious jouissance that always seems just out of reach.
It doesn’t so much matter how the magic actually functions, or even what outcomes it produces. The important thing is what magic is, which is…magical.
This is how you get works that are all about magic but seem entirely disinterested in questions like "what can you achieve with magic?," "how does the presence of magic change the world?," etc. One of the major ways, anyway.
The Numinous Hidden Glory fantasy often revolves around an idea of the magic world, the other-place where everything is drenched in jouissance. [Sometimes the magic world is another plane of existence, sometimes it’s a hidden society within the "real world," doesn’t matter.] The real point of magic, as it’s often presented, is being in that magic world; once you’re there, everything is awesome, even if the actual things you’re seeing and doing are ordinary-seeming or silly. A magic school is worlds better than a regular school, because it’s magic, even if it’s got exactly the same tedium of classes and social drama that you know from the real world.
Fantasies of this kind often feature a lot of lush memorable detail that doesn’t particularly cohere in any way. It all just adds to the magic-ness.
III. Magic as the Atavistic Anti-Civilizational Power
Notable examples: A Song of Ice and Fire, Godzilla
According to the terms of this fantasy, the point of magic is that it doesn’t make sense. It doesn’t make sense within the logic of civilized human thought, anyway. It is nature and chaos given concrete form; it is the thing that tears away at the systems that we, in our [Promethean nobility / overweening hubris], try to build.
There’s not a baked-in value judgment here. This kind of magic can be presented as good, bad, or some of both. Same with civilization, for that matter.
It’s often presented as Old Myths and Folkways that have More Truth and Power Than Seems Reasonable. Narratively, it often serves as a dramatized version of the failure of episteme,and of the kind of entropic decay that in real life can take centuries to devour empires and ideologies.
This kind of magic is almost always the province of savages, actual inhuman monsters, or (occasionally) the very downtrodden.
(I think it is enormously telling that in A Song of Ice and Fire – a series that is jammed full of exotic cults and ancient half-forgotten peoples, all of whom have magic that seems to work and beliefs that at least touch on mysterious truths – only the Westerosi version of High Medieval Catholicism, the religion to which most of the people we see notionally adhere, is actually just a pack of empty lies.)
Or, sometimes, we care about what magic actually does. More than that – sometimes we want to see magic doing really interesting things, and then other magic intersecting with it in ways that are even more interesting.
The fantasy here, in simplest terms, is "magic can achieve any arbitrary cool effect." There doesn’t tend to be an overarching system that explains how it’s all supposed to come together, or if there is, it tends to be kind of lame and hand-wavey – a rigorous system of Magic Physics, delineating the limits of the possible, would get in the way of all the cool effects we want to show!
Once again, this shows up a lot in combat-heavy narratives. Less with the genericized D&D-style "magic is a fist that can punch harder than your regular meat fist," and more with people throwing weird and wacky powers at each other in order to show how those powers can be used creatively to overcome opposition. Sometimes, instead of combat, you get magicians using their cool-effects magic to MacGuyver their way out of problems or even trying to resolve large-scale social problems. Issues of magic usage within the narrative being "fair" or "unfair" or "cheesy" are important here in ways that they generally aren’t elsewhere, since the fantasy on offer comes close to being a game.
(Ratfic often falls into this category.)
V. Magic as Alternate-Universe Science
Notable examples: the Cosmere books
This covers most of what gets called "hard fantasy." The fantasy on offer is a pretty straightforward one – "magic has actual rules, you can learn them, and once you’ve learned them you can make predictions and achieve outcomes." It’s puzzle-y in the way that the previous fantasy was game-y. It’s often a superstimulus for the feeling of learning a system in the way that video game grinding is a superstimulus for the feeling of rewarding labor.
The magic effects on offer tend to be less ridiculous and "broken" than toybox magic, because any logic you can use to achieve a ridiculous effect is going to influence the rest of the magic system, and special cases that aren’t grounded in sufficiently-compelling logic will ruin the fantasy.
Not super common.
VI. Magic as Psychology-Made-Real
Notable examples: Revolutionary Girl Utena, Persona
This kind of magic makes explicit, and diagetic, what is implicit and metatextual in most fantasy settings. The magic is an outgrowth of thought, emotion, and belief. Things have power in the world because they have power in your head. The things that seem real in the deepest darkest parts of your mind are actually real.
This is where you get inner demons manifested as actual demons (servile or hostile or anything in between), swords forged from literal hope, dungeons and labyrinths custom-tailored to reflect someone’s trauma, etc.
The fantasy, of course, is that your inner drama matters.
My personal favorite.
VII. Magic as Pure Window Dressing
Notable examples: later Final Fantasy games, Warhammer 40K
This one is weird; it doesn’t really make sense on its own, only metatextually. I think of its prevalence as an indicator of the extent to which fantasy has become a cultural staple.
The fantasy on offer in these works is that you are in a fantasy world that is filled with fantasy tropes. And that’s it.
Because the important thing here is that the magic doesn’t really do anything at all, or at least, it doesn’t do anything that non-magic can’t do equally well. It doesn’t even serve as an indication that Things are Special, because as presented in-setting, magic isn’t Special. Being a wizard is just a job, like being a baker or a tailor or something – or, usually, like being a soldier, because the magic on offer is usually a very-simple kind of combat magic. And unlike in D&D, it’s not like magic is used only or chiefly by a particularly noteworthy kind of person. It’s just…there.
The great stories of the world, in these works, don’t tend to feature magic as anything more than a minor element. The point is to reassure the audience that this is the kind of world, the kind of story, that has magic.