Definition:
Application Pages : An application page is deployed once per Web server and cannot be customized on a site-by-site basis.
Site Pages : These are pages that make up the site interface and are specific to one site or site collection.
Custom aspx Pages : These are the pages that are added via feature and\or solution package for a specific purpose or function.
Developers usually deploy them in Layouts folder.
Storage:
Application Pages : They stay in 12 hive folder structure, mostly under layouts folder and aare
used by all the sites and site collections for that server.
Site Pages : They mostly get stored in the content database of the site collection.
Custom aspx Pages : If deployed in Layouts folder the remain uncustomized thus do not get stored in the Content database.
Code Behind :
Application Pages : They do support code behind and inline scripts.
Site Pages : They do not support code behinds. We can only add WebPart zones to the site pages.
Custom aspx Page : They can have a code behind and inline scripts. The code behind file is not deployed though.
Look and Feel :
Application Pages : They do not inherit websites current master page. They will always inherit from application.master.
Site Pages : They inherit the custom look and feel of the site.
Cutsom aspx Pages : They do inherit the Look and feel of the site they are deployed in. You will have to specify the url of the Custom master page while deploying them.