Разное

Sql запрос: основные операторы, виды, синтаксис, написание, создание базы данных, примеры простых и сложных команд

28.08.2023

Сложные SQL-запросы

Сложные SQL-запросы Пожалуйста, включите JavaScript в браузере!

Сложные SQL-запросы

С помощью строки поиска вы можете вручную создавать SQL-запросы любой сложности для фильтрации событий.

Чтобы сформировать SQL-запрос вручную:

  1. Перейдите в раздел События веб-интерфейса KUMA.

    Откроется форма с полем ввода.

  2. Введите SQL-запрос в поле ввода.
  3. Нажмите на кнопку .

Отобразится таблица событий, соответствующих условиям вашего запроса. При необходимости вы можете отфильтровать события по периоду.

Поддерживаемые функции и операторы

  • SELECT – поля событий, которые следует возвращать.

    Для SELECT в программе поддержаны следующие функции и операторы:

    • Функции агрегации: count, avg, max, min, sum.
    • Арифметические операторы: +, -, *, /, <, >, =, !=, >=, <=.

      Вы можете комбинировать эти функции и операторы.

      Если вы используете в запросе функции агрегации, настройка отображения таблицы событий, сортировка событий по возрастанию и убыванию, получение статистики, а также ретроспективная проверка недоступны.

  • FROM – источник данных.

    При создании запроса в качестве источника данных вам нужно указать значение events.

  • WHERE – условия фильтрации событий.
    • AND, OR, NOT, =, !=, >, >=, <, <=
    • IN
    • BETWEEN
    • LIKE
    • ILIKE
    • inSubnet
    • match (в запросах используется синтаксис регулярных выражений re2)
  • GROUP BY – поля событий или псевдонимы, по которым следует группировать возвращаемые данные.

    Если вы используете в запросе группировку данных, настройка отображения таблицы событий, сортировка событий по возрастанию и убыванию, получение статистики, а также ретроспективная проверка недоступны.

  • ORDER BY – столбцы, по которым следует сортировать возвращаемые данные.

    Возможные значения:

    • DESC – по убыванию.
    • ASC – по возрастанию.
  • OFFSET – пропуск указанного количества строк перед выводом результатов запроса.
  • LIMIT – количество отображаемых в таблице строк.

    Значение по умолчанию – 250.

    Если при фильтрации событий по пользовательскому периоду количество строк в результатах поиска превышает заданное значение, вы можете отобразить в таблице дополнительные строки, нажав на кнопку Показать больше записей. Кнопка не отображается при фильтрации событий по стандартному периоду.

    Примеры запросов:

    • SELECT * FROM `events` WHERE Type IN ('Base', 'Audit') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events с типом Base и Audit, отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE BytesIn BETWEEN 1000 AND 2000 ORDER BY Timestamp ASC LIMIT 250

      Все события таблицы events, для которых в поле BytesIn значение полученного трафика находится в диапазоне от 1000 до 2000 байт, отсортированные по столбцу Timestamp в порядке возрастания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE Message LIKE '%ssh:%' ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events, которые в поле Message содержат данные, соответствующие заданному шаблону %ssh:% в нижнем регистре, и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE inSubnet(DeviceAddress, '10.0.0.1/24') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events для хостов, которые входят в подсеть 10.0.0.1/24, отсортированные по столбцу Timestamp в порядке убывания.

      Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE match(Message, 'ssh.*') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh.*, и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT max(BytesOut) / 1024 FROM `events`

      Максимальный размер исходящего трафика (КБ) за выбранный период времени.

    • SELECT count(ID) AS "Count", SourcePort AS "Port" FROM `events` GROUP BY SourcePort ORDER BY Port ASC LIMIT 250

      Количество событий и номер порта. События сгруппированы по номеру порта и отсортированы по столбцу Port в порядке возрастания. Количество отображаемых в таблице строк – 250.

      Столбцу ID в таблице событий присвоено имя Count, столбцу SourcePort присвоено имя Port.

Если вы хотите указать в запросе специальный символ, вам требуется экранировать его, поместив перед ним обратную косую черту (\).

Пример:

SELECT * FROM `events` WHERE match(Message, 'ssh:\'connection.*') ORDER BY Timestamp DESC LIMIT 250

Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh: 'connection', и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

При переключении на конструктор параметры запроса, введенного вручную в строке поиска, не переносятся в конструктор: вам требуется создать запрос заново. При этом запрос, созданный в конструкторе, не перезаписывает запрос, введенный в строке поиска, пока вы не нажмете на кнопку Применить в окне конструктора.

После обновления KUMA до версии 1.6 при фильтрации событий с помощью SQL-запроса, содержащего условие inSubnet, может возвращаться ошибка Code: 441. DB::Exception: Invalid IPv4 value.

В таких случаях необходимо на серверах хранилища (на каждой машине кластера ClickHouse) в файле /opt/kaspersky/kuma/clickhouse/cfg/config.d/users.xml в разделе profiles → default добавить директиву <cast_ipv4_ipv6_default_on_conversion_error>true</cast_ipv4_ipv6_default_on_conversion_error>.

Подробнее об SQL см. в справке ClickHouse.

В начало

Нормализация данных через запрос в SQL

Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены данные, зависят результаты всей вашей работы.

Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.

В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().

Для работы возьмем таблицу students с данными о росте учащихся.

nameheight
Иван174
Петр181
Денис199
Ксения158
Сергей179
Ольга165
Юлия152
Кирилл188
Антон177
Софья165

Min-Max масштабирование

Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.

Выполним масштабирование по формуле:

Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.

SQL-запрос с подзапросом:

SELECT height, 
       1.0 * (height-t1.min_height)/(t1.max_height - t1.min_height) AS scaled_minmax
  FROM students, 
      (SELECT min(height) as min_height, 
              max(height) as max_height 
         FROM students
      ) as t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - MIN(height) OVER ()) * 1.0 / (MAX(height) OVER () - MIN(height) OVER ()) AS scaled_minmax
  FROM students;

В результате мы получим переменные в диапазоне [0…1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.

nameheightscaled_minmax
Иван1740. 46809
Петр1810.61702
Денис1991
Ксения1580.12766
Сергей1790.57447
Ольга1650.2766
Юлия1520
Кирилл1880.76596
Антон1770.53191
Софья1650.2766

Масштабирование для заданного диапазона

Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.
Формула для вычисления в этом случае такая:

Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.

SQL-запрос с подзапросом:

SELECT height, 
       ((height - min_height) * (20.0 - 10.0) / (max_height - min_height)) + 10 AS scaled_ab
  FROM students,
      (SELECT MAX(height) as max_height, 
              MIN(height) as min_height
         FROM students  
      ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       ((height - MIN(height) OVER() ) * (20. 0 - 10.0) / (MAX(height) OVER() - MIN(height) OVER())) + 10.0 AS scaled_ab
  FROM students;

Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.

nameheightscaled_ab
Иван17414.68085
Петр18116.17021
Денис19920
Ксения15811.2766
Сергей17915.74468
Ольга16512.76596
Юлия15210
Кирилл18817.65957
Антон17715.31915
Софья16512.76596

Нормализация с помощью z-score

В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.

Вычисляется z-score по формуле:

SQL-запрос с подзапросом:

SELECT height, 
       (height - t1.mean) * 1.0 / t1.sigma AS zscore
  FROM students,
      (SELECT AVG(height) AS mean, 
              STDDEV(height) AS sigma
         FROM students
        ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - AVG(height) OVER()) * 1.0 / STDDEV(height) OVER() AS z-score
  FROM students;

В результате мы сразу заметим выбросы, которые выходят за пределы стандартного отклонения.

nameheightzscore
Иван1740.01488
Петр1810.53582
Денис1991.87538
Ксения158-1.17583
Сергей1790.38698
Ольга165-0.65489
Юлия152-1. 62235
Кирилл1881.05676
Антон1770.23814
Софья165-0.65489

Чтение данных из баз данных SQL

Чтение данных из баз данных SQL

Узнайте, как писать SQL-запросы в Retool для чтения данных.

Для чтения из базы данных вы можете написать операторы SQL. Например, этот запрос выбирает всех активных или неактивных пользователей в зависимости от того, установлен ли флажок.

 выберите * из пользователей, где активно = {{checkbox1.value}};

Назовите запрос getUsers , сохраните его и получите доступ к его результатам в другом месте как getUsers.data . Запросы SQL возвращают данные в формате на основе столбцов:

 {
  "столбец1": [1, 2, 3],
  "столбец2": [1, 2, 3],
  ...
}

Данные запросов SQL , а не возвращаются в виде массива объектов. Чтобы получить доступ к данным в виде массива объектов, используйте метод formatDataAsArray .

Примеры более сложных SQL-запросов в Retool см. в памятке по SQL.

Чтобы предотвратить внедрение SQL, Retool преобразует запросы SQL в подготовленные операторы. Точное поведение запроса зависит от вашего конкретного драйвера базы данных. Однако большинство баз данных не поддерживают подготовленные операторы с динамическими именами столбцов или динамическими именами таблиц, поскольку в таких случаях трудно предотвратить внедрение SQL.

Это означает, что следующий запрос не работает в PostgreSQL, поскольку имя таблицы является динамическим, а PostgreSQL не поддерживает динамические имена таблиц в подготовленных операторах.

 выберите * из {{textinput1.value === 'getUsers' ? 'пользователи' : 'платежи'}}

Вместо этого следует написать два запроса:

 select * from users
 
 выбрать * из платежей

Затем вы можете сослаться на правильный оператор SQL в зависимости от значения textinput1 :

 {
  {
    textinput1. value === "getUsers" ? пользователи.данные : платежи.данные;
  }
}

Администраторы могут отключить подготовленные операторы для ресурса, установив флажок Отключить преобразование запросов в подготовленные операторы на странице сведений о ресурсе. Это отключает защиту от SQL-инъекций и подвергает ресурс атакам с SQL-инъекциями, поэтому его следует использовать с осторожностью.

Если вы отключите подготовленные операторы, вам может потребоваться обновить запросы, использующие JavaScript, в течение {{ }} , потому что некоторые базы данных ожидают заполнители подготовленных операторов без кавычек или выполняют преобразования типов в подготовленных операторах.

Например, если разрешены подготовленные операторы, следующий запрос PostgreSQL не должен заключать в кавычки заполнитель подготовленного оператора.

 выберите идентификатор, имя, фамилия
от пользователей ты
где u.id = {{ numberInput1. value }};

Когда подготовленные операторы отключены, заполнитель требует кавычек.

 выберите идентификатор, имя, фамилия
от пользователей ты
где u.id = '{{ numberInput1.value }}'

Обратитесь к документации вашей базы данных по подготовленным операторам, чтобы подтвердить предполагаемое использование.

Обновлено 18 дней назад

sql:query — Drush

Выполнить запрос к базе данных.

Примеры
  • drush sql:query "SELECT * FROM users WHERE uid=1" . Просмотр записи пользователя. Префиксы таблиц, если они используются, должны добавляться к именам таблиц вручную.
  • drush sql:query --db-prefix "SELECT * FROM {users}" . Просмотр записи пользователя. Префиксы таблиц учитываются. Внимание: все фигурные скобки будут удалены.
  • $(drush sql:connect) < example.sql . Импорт операторов SQL из файла в текущую базу данных.
  • drush sql:query --file=example. sql . Альтернативный способ импорта операторов SQL из файла.
  • drush ev "return db_query('SELECT * FROM users')->fetchAll()" --format=json . Получить данные обратно в формате JSON. См. https://github.com/drush-ops/drush/issues/3071#issuecomment-347929777.
  • драш sql: подключение -e "выбрать * из лимита пользователей 5;" . Результаты отформатированы в красивую таблицу с границами и заголовками столбцов.
Аргументы
  • [запрос] . SQL-запрос. Игнорируется, если указан --file.
Опции
  • --файл-результата[=ФАЙЛ-РЕЗУЛЬТАТА] . Сохранить в файл. Файл должен относиться к корню Drupal.
  • --file=ФАЙЛ . Путь к файлу, содержащему SQL для запуска. Файлы Gzip принимаются.
  • --файл-удалить . Удалите --file после его запуска.
  • --extra=ДОПОЛНИТЕЛЬНО . Добавьте пользовательские параметры в строку подключения (например, --extra=--skip-column-names)
  • --db-префикс . Включите замену фигурных скобок в вашем запросе.
  • --база данных[=БАЗА ДАННЫХ] . Ключ подключения к БД при использовании нескольких подключений в settings.php. [по умолчанию: по умолчанию ]
  • --цель[=ЦЕЛЬ] . Имя цели в указанном соединении с базой данных. [по умолчанию: по умолчанию ]
  • --db-url=DB-URL . URL-адрес базы данных в стиле Drupal 6. Например, mysql://root:pass@localhost:port/dbname
  • --показать-пароли . Показать пароль в CLI. Полезно для отладки.
Общие параметры
  • -v|vv|vvv, --verbose . Увеличьте уровень детализации сообщений: 1 для обычного вывода, 2 для более подробного вывода и 3 для отладки
  • -у, --да . Автоматически принимать значения по умолчанию для всех пользовательских подсказок.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *