How to create a custom search form and handler?

To change the search form, filter get_search_form. You get the form as a string here, and you can change it as you need.

add_filter( 'get_search_form', function( $form )
{
    // Replace the form, add additional fields
    return $form;
});

To change the search query to the database, filter posts_search. You get the query as a string and the current WP_Query object which provides more information. See wp-includes/query.php for context.

add_filter( 'posts_search', function( $search_query, $wp_query )
{
    // change the SQL
    return $search_query;
}, 10, 2 );

If you don’t want to filter the search query, change the name attribute in the search form, example foo, and inspect the $_POST request:

if ( ! empty ( $_POST['foo'] ) )
{
    $global $wpdb;
    $results = $wpdb->get_results( /* custom sql */ );
}