As a Mage (playing normal), at the later stages of the game, I have found that Magic Trap is the most valuable spell for me. Upgraded to lvl 3 it does cca 3000 points of damage and, even more important, stops the enemy in its tracks.
What is also great is that Trap even works with Dragons (and other flying creatures) and Archdevils (which teleport themselves). You will have to guess where a dragon or archdevil would land for it to be effective but if you are carefull on how you position your troops you might 'lead' them where you want them.
Magic chains (or shackles I am not sure now) on lvl 3 were essential in the deamon land and when facing enemies which rely on their special abilities (giants, necros, demoness
. But you must upgrade them to lvl 3 when they are 'mass'.
Gate is another cool spell because, not only does it give you more units in the battle (which is allways good) but they also block the square were they are located for 2 turns so the enemy can not get past it.
From destructive spells the favourites are Geyser and Fire arrow (or flame arrow, can't remember). Ice serpent is also great because it slows down the enemy