What PrivateerKev said for Elephant artillery.

For normal elephants, especially for the Rajputs on BC where elephants are weaker but more plentiful, the key is to use them aggressively and get them into the thick of the melee for a quick rout. If they are mixed up in melee the enemy will be reluctant to fire upon them with javelins or fire arrows for fear of hitting his own troops. Elephants can overcome many times their numbers of infantry or cavalry in melee and even spearmen don't give them much pause. Even better, they only have to kill a few men to get the others running, so usually a single unit of elephants charging into the fray at the height of a melee battle will be enough to swing it decisively in your favour.

In melee, look out for axes and halberds, these are the only things with a reasonable chance of hurting the elephants before they are routed. If it looks like your eles are getting bogged down, double right click somewhere outside the melee (preferably behind the enemy line since the eles are pointing that way anyway) to get them moving again; they will brush the enemy units out of their way and knock them down in the process, which won't kill many but will completely disrupt the enemy unit. Once they are out of the fight, turn them around and charge them back in.

If you suspect one of your ele units is in danger of stampeding (it is under attack with fire arrows or it is starting to take casualties in melee), give them a wide berth. A crazy unit of elephants isn't the end of the world, and can quite happily carry on killing the enemy and scaring the **** out of them, but be aware they will die quicker themselves so expect them to start losing elephants. Never, ever, use the "kill elephants" special ability if they go crazy, you'll get them back after the battle anyway and a unit of eles is invariably far more valuable than whatever unit it might squish while crazy.

For all types of elephants, avoid javelin cavalry like the plague, they will tear through a unit of eles in no time. Same goes for if you are facing eles yourself, send jav cav against them to pick them off relatively cheaply. Bow cavalry by contrast are pretty much helpless against eles except in huge numbers. Javelin infantry are less of a threat so long as you charge them with your eles from outside their range, so that they don't have time to get a volley off before the charge hits them.

I find the best unit setup to use with elephants is to combine them with light cavalry. The elephants do the heavy killing work and break the enemy center, while the light cavalry screen them from the hated javelin cavalry and tie up any enemy foot archers. If the light cav get into melee trouble the elephants come to the rescue.

Be very wary of using eles in a siege, the narrow streets stop them charging properly and they can be something of a liability what with all those fire arrows around and the danger of a elephant running amok and crushing your entire army on the way out of the city.