In the case of self destruct, The warp core 'overloads' creating a large explosion destroying the ship.Typically, it works one of two ways: 1 - A set of explosives, implanted throughout key points of the vessel, will be set off, destroying a majority of the ship and any critical components; OR 2 - The primary reactor (Warp core, Romulan Singularity drive, etc.) will be overloaded, causing a breach that destroys the vessel.It depends on the ship. Most on most Federations ships, self destruct is achieved by combining the ship's matter and antimatter stores. Another method is explosive devices based all over the ship.
Klingon ships self destruct by means of a warp core breach.
The power grid on Borg vessels feeds back on itself, causing an explosion.
TOS-era Romulan vessels used a nuclear explosive.
Read more here.