$query->addExpression and $query->orderBy in views Sql

I using this way to solve my problem. Hope to help anybody.

/**
 * Implementation of hook_views_query_alter
 */
function  mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query)  {
  if($view->id() == 'my_view_name') {
    // delete orderby which exist sort by title.
    $query->orderby = [];
    $query->orderby[] = [
      'field' => 'custom_name_of_field',
      'direction' => 'ASC'
    ];
  }
}

function mymodule_views_pre_execute(&$view) {
  if($view->id() == 'my_view_name') {
    $view->build_info['query']->addExpression('CONVERT(node_field_data.title USING GBK)','custom_name_of_field');
  }
}

Just an update on @dravenk's example, here is how the code looks now in D10 + including some WHERE condition examples.

use Drupal\views\ViewExecutable;

/**
 * Implements hook_views_pre_execute().
 */
function mymodule_views_pre_execute(ViewExecutable $view) {
  if($view->id() == 'my_view_name') {
    $query = &$view->build_info['query'];

    // Add a SELECT expression.
    $query->addExpression('CONVERT(node_field_data.title USING GBK)','custom_name_of_field');

    // Add a WHERE expression in MySQL.
    $query->addWhereExpression(0, "COALESCE(some_alias.date, '1900-01-01') < :some_value", [
      ':some_value' => $value_here,
    ]);

    // Add a WHERE expression in SQLite.
    $query->where("COALESCE(some_alias.date, '1900-01-01') < :some_value", [
      ':some_value' => $value_here,
    ]);
  }
}

Thank you @scott_euser and @dravenk for your great examples!

I landed here looking for a way to implement natural sorting of a text field in a view.

My workaround was to use LPAD on the text field and then sort by this field.

/**
 * @file
 * Custom module to implement natural sort order for staff order views.
 */

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;
use Drupal\views\Plugin\views\query\Sql;
/**
 * Implements hook_views_query_alter().
 *
 * Alters the query for the "personalbestellungen" view to sort by
 * the custom sort field.
 *
 * @param \Drupal\views\ViewExecutable $view
 *   The view executable object.
 * @param \Drupal\views\Plugin\views\query\Sql $query
 *   The query object for the view.
 */
function wet_personalbestellung_views_query_alter(ViewExecutable $view, Sql $query) {
  if ($view->id() == "personalbestellungen" && $view->current_display === 'page_2') {
    // Add the custom sorting field to the view's query.
    array_unshift($query->orderby, [
      'field' => 'custom_sort_order',
      'direction' => 'DESC',
    ]);
  }
}

/**
 * Implements hook_views_pre_execute().
 *
 * Adds a custom expression for sorting in the "personalbestellungen" view.
 *
 * @param \Drupal\views\ViewExecutable $view
 *   The view executable object.
 */
function wet_personalbestellung_views_pre_execute(ViewExecutable $view) {
  if ($view->id() == "personalbestellungen" && $view->current_display === 'page_2') {
    // Prepend zeros to the order_number, to mimic natural sorting.
    $view->build_info['query']->addExpression("LPAD(order_number, 100, '0')",'custom_sort_order');
  }
}

Rethinking this problem I ended up with another approach. Instead of prepending the number with endless zeros, I just converted the string value to a number and used this number field for sorting.

 /**
 * Implements hook_views_pre_execute().
 *
 * Adds a custom expression to allow natural sorting in the "personalbestellungen" view.
 *
 * @param \Drupal\views\ViewExecutable $view
 *   The view executable object.
 */
function wet_personalbestellung_views_pre_execute(ViewExecutable $view) {
  if ($view->id() == "personalbestellungen" && $view->current_display === 'page_2') {
    // Convert the number string to a real number.
    $view->build_info['query']->addExpression("CONVERT(order_number, unsigned)",'custom_sort_order');
  }
}