How do I register/enqueue a custom CSS file?

Refer to the Codex entry for wp_enqueue_style().

Assuming you have a custom stylesheet, named custom.css, located in your Theme’s root directory:

wp_enqueue_style( 'mytheme-custom', get_template_directory_uri() . '/custom.css' );

You would put this in functions.php, inside a callback, hooked into an appropriate callback, like so:

function wpse87681_enqueue_custom_stylesheets() {
    if ( ! is_admin() ) {
        wp_enqueue_style( 'mytheme-custom', get_template_directory_uri() . '/custom.css' );
    }
}
add_action( 'wp_enqueue_scripts', 'wpse87681_enqueue_custom_stylesheets', 11 );

You can play with the hook (you can use as late as wp_head) or priority (default is 10) to ensure that your custom stylesheet outputs after any other stylesheet you want to override. But, if you’re just trying to override the default stylesheet, any appropriate hook, and any priority, should be fine, because the default stylesheet link is normally hard-coded in the document head in header.php.