MySql. Сложные sql запросы

Данная публикаци содержит рецепты сложных SQL запросов, которые можно выполнять для обращения к MySql серверу для получения данных.


Все примеры будут разобраны на основании таблицы users и таблицы clients, таблицы состоят из 4х колонок - id (Идентификатор - auto increment), login Логин), pass (Пароль) и male (Пол).
Таблица users:

Таблица clients:

Запрос для создания таблицы users:
create table users
(
    id    int auto_increment
        primary key,
    login varchar(255) null,
    pass  varchar(255) null,
    male  tinyint      null
);

Запрос на создание таблицы clients:
create table clients
(
    id    int auto_increment
        primary key,
    login varchar(255) null,
    pass  varchar(255) null,
    male  tinyint      null
);

Далее необходимо наполнить таблицы данными.
Вы можете наполнить таблы самостоятельно или воспользоваться запросами для создания списка пользователей и клиентов.
Запрос на создание списка пользователей для таблицы users:

Запрос на создание списка пользователей для таблицы clients:


Список сложных sql запросов к базе данных MySql:
  • Объедениение нескольких запросов (c исключением повторений в результате)


    Объединение нескольких запросов можно достич при помощи оператора UNION.
    Оператор UNION объединяет несколько запросов, но исключает повторяющиеся значениея из результата выдачи.
    Пример SELECT запроса с применением оператора UNION:
    (SELECT login, pass, male FROM users) UNION (SELECT login, pass, male FROM clients);

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


  • Объедениение нескольких запросов (с выводом всех данных)


    Аналогично примеру выше, объединение нескольких запросов с выводом всех данных, можно произвести с использованием оператора UNION.
    Только не простооператора UNION, а оператора UNION ALL.
    Пример SELECT запроса с применением оператора UNION ALL:
    (SELECT login, pass, male FROM users) UNION ALL (SELECT login, pass, male FROM clients);

    Выполнение данного запроса вернет все значения из таблиц clients и users.
    Результат:


  • Запрос на получение количества клиентов с преминением условия.


    Для выполнения запроса подобного типа необходимо воспользоваться функцией count() и добавлением условия.
    В примере будет приведен пример с использованием условия WHERE.
    В результате выполнения запроса мы должны получить количество клиентов мужского пола.
    Запрос:
    SELECT count(id) FROM clients WHERE male = 1;

    Результат:
    mysql> SELECT count(id) FROM clients WHERE male = 1;
    +-----------+
    | count(id) |
    +-----------+
    | 7 |
    +-----------+
    1 row in set (0.00 sec)


  • Запрос с использованием нескольких таблиц.


    Для описание запросов подобного типа будет создана еще одна таблица.
    Данная таблица будет предствляеть из себя список публикаций, которые опубликовали клиенты.
    Таблица будет состоять из полей:
    id - идентификатор пуликации (auto increment),
    title - заголовок,
    full_text - полное описание,
    user_id - идентификатор пользователя, опубликовавшего запись.

    Запрос на создание данной таблицы:
    create table posts
    (
        id        int auto_increment
            primary key,
        title     varchar(255) null,
        full_text text         null,
        user_id   int          null
    );

    Записи для данной таблицы вы можете создать сами или воспользоваться заросом на создание тестовых, пробных записей для этой таблицы:

    В рамках запроса мы должны получить список публикаций в виде заголовка, текста публикации, логин и идентификатор клиента.
    Разберем несколько примеров для получения этих данных:
    1. Выборка данных из 2х или более таблиц с применением условия.


      Данный запрос возвращает заголовок публикации, текст с описанием, идентификатор клиента опубликовавшего публикацию и логин данного клиента.
      Запрос выполняется к двум таблицам и выводит записи, которые удовлетворяют условию WHERE.
      SELECT p.title, p.full_text, c.id, c.login FROM posts AS p, clients AS c WHERE p.user_id = c.id;

      Результат:

    2. Запрос типа LEFT JOIN


      Запрос LEFT JOIN делает объединения по двум таблицам и возвращает результат в соотвествии с условием.
      Объединения типа LEFT - указывает, что "внешняя" таблица будет находящаяся слева (в нашем примере это таблица posts).
      Запрос:
      SELECT p.title, p.full_text, c.id, c.login FROM posts AS p LEFT JOIN clients AS c ON p.user_id = c.id;
      Таким образом мы получаем список публикаций и всех клиентов (пользователей), которые опубликовали данные публикации.
      Результат:

    3. Запрос типа INNER JOIN


      INNER JOIN[/b] - внутреннее объединения, которое объединяет совпадающие данные из обоих таблиц.
      ([b]INNER
      - можно опустить и использовать только JOIN).
      Для того, чтобы понять как аботает этот тип запроса, я отредактирую таблицу posts.
      Данные таблицы posts до редактирования:

      Данные таблицы posts после редактирования:

      Как должно быть видно из скриншотов, я отвязал у 3х публикаций пользователей, присвоив полю user_id значение null.
      INNER JOIN запрос:
      SELECT p.title, p.full_text, c.id, c.login FROM posts AS p INNER JOIN clients AS c ON p.user_id = c.id;

      Резльтат выполнения запроса:

      В результате выполнения запроса отсеялись публикации, у которых поле user_id равно null.
      Так как у этих публикаций отсутствует связь с таблицей clients.
    4. Запрос типа RIGHT JOIN


      Для демонстрации работы RIGHT JOIN вернем значения колонки user_id, таблицы posts.

      Запрос RIGHT JOIN делает объединения по двум таблицам и возвращает результат в соотвествии с условием.
      Объединения типа RIGHT - указывает, что "внешняя" таблица будет находящаяся справа (в нашем примере это таблица clients).
      Пример запроса:
      SELECT p.title, p.full_text, c.id, c.login FROM posts AS p RIGHT JOIN clients AS c ON p.user_id = c.id;

      Данный запрос вернет список пользователей и их публикации (если они есть).
      Если публикаций нет, то результатом вернется список пользователей и поля из таблицы posts примут значение null.
      Результат:


  • Вложенные запросы.


    Смысл этого запроса в том, что в теле одного запроса исполняется код другого запроса.
    SELECT * FROM posts WHERE user_id = (SELECT id FROM clients WHERE login = 'Margaret');

    В днном запросе делается выборка по публикациям у которых автором является клиент "Margaret".
    Для получения идентификатора пользователя "Margaret" в условии WHERE выполняется вложенны запрос:
    user_id = (SELECT id FROM clients WHERE login = 'Margaret')

    Вложенный SELECT запрос выполняет поиск идентификатора пользователя по его логину:
    SELECT id FROM clients WHERE login = 'Margaret'

    Таким образом, выполнение запроса должно вернуть список публикаций пользователя "Margaret".
    Результат выполнения запроса:

    Внимание!
    Вложенные запросы не стоит использовать где попало, а лучше заменить их на JOIN запросы Так как выполнение вложенных запросов, осуществляет повышенную нагрузку на сервер и может привести к рекурсивным высовам запроса.

  • Выбор пар с одинаковым значением.


    Для демонстрации работы данного запроса понадобится расширить количество пользователей в таблице clients.
    Запрос на добаление новых пользователей:
    INSERT INTO clients (id, login, pass, male) VALUES (12, 'alexander', '$2y$10$6SzbBCMENklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH14y', 1);
    INSERT INTO clients (id, login, pass, male) VALUES ('Tom', '$2y$20$6SzbBCNRNklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH20y', 1);
    INSERT INTO clients (id, login, pass, male) VALUES ('Dmitry', '$2y$20$6SzbBCNRNklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH20y', 1);
    INSERT INTO clients (id, login, pass, male) VALUES ('Leonid', '$2y$10$6SzbBCMENklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH14y', 1);
    INSERT INTO clients (id, login, pass, male) VALUES ('alex', '$2y$10$6SzbBCMENklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH14y', 1);
    INSERT INTO clients (id, login, pass, male) VALUES ('Mikle', '$ws$10$6SzbBCMENklStIgTqBKIluijJUnbeZ5WqIu4RJgkksnFZon5kH14y', 1);

    После добавление новых клиентов таблица clients должна выглядеть следующи образом:

    После чего выполняем к таблице "clients" запрос на получение списка всех клиентов, у которых одинаковые логины (login).
    SELECT c1.id AS id1, c1.login FROM clients AS c1 INNER JOIN clients c2 ON c1.login = c2.login WHERE c1.id <> c2.id GROUP BY c1.id ORDER BY c1.id;

    Данный запрос выполняет вывод идентификаторов и логино клиентов, обращаясь в эту же таблицу с помощью конструкции INNER JOIN для поиск одинаковых логинов с сортировкой по идентификатору.
    Результат выполнения запроса:

    В результате мы получили список пользователей с одинаковыми иенами и их идентификаторами.


В данной статье опубликована часть наиболее популярных сложных запросов.
Публикуйте свои варианты сложных SQL запросов в комментариях.
Пользователи будут очень вам благодарны!
Информация
Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.