Application Pages - An application page is deployed once per Web server and cannot be customized on a site-by-site basis. They stay in 12 hive folder structure, mostly under layouts folder.
Site Pages - These are pages that make up the site interface and are specific to one site or site collection.They mostly get stored in the content database of the site collection.
Content Pages - Site Page which contains webpart or other custom components. This Page is stored in database. They mostly get stored in the content database of the site collection.