Here is a quick summary of the segues and an example for each type.
Show - Pushes the destination view controller onto the navigation stack, sliding overtop from right to left, providing a back button to return to the source - or if not embedded in a navigation controller it will be presented modally
Example: Navigating inboxes/folders in Mail
Show Detail - For use in a split view controller, replaces the detail/secondary view controller when in an expanded 2 column interface, otherwise if collapsed to 1 column it will push in a navigation controller
Example: In Messages, tapping a conversation will show the conversation details - replacing the view controller on the right when in a two column layout, or push the conversation when in a single column layout
Present Modally - Presents a view controller in various animated fashions as defined by the Presentation option, covering the previous view controller - most commonly used to present a view controller that animates up from the bottom and covers the entire screen on iPhone, or on iPad it's common to present it as a centered box that darkens the presenting view controller
Example: Selecting Touch ID & Passcode in Settings
Popover Presentation - When run on iPad, the destination appears in a popover, and tapping anywhere outside of this popover will dismiss it, or on iPhone popovers are supported as well but by default it will present the destination modally over the full screen
Example: Tapping the + button in Calendar
Custom - You may implement your own custom segue and have control over its behavior
The deprecated segues are essentially the non-adaptive equivalents of those described above. These segue types were deprecated in iOS 8: Push, Modal, Popover, Replace.
For more info, you may read over the Using Segues documentation which also explains the types of segues and how to use them in a Storyboard. Also check out Session 216 Building Adaptive Apps with UIKit from WWDC 2014. They talked about how you can build adaptive apps using these new Adaptive Segues, and they built a demo project that utilizes these segues.