Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Мы уже научились делать выборку данных с условием и делать группировку данных, но иногда бывают ситуации, когда необходимо сделать дополнительную фильтрацию данных после группировки, клаузула WHERE в этом случае нам не поможет. Для выполнения дополнительной фильтрации данных после группировки в стандарте языка SQL есть специальная уточняющая фраза HAVING. Которая позволяет нам выполнять фильтрацию данных после их группировки.

HAVING очень часто используется в запросах SELECT и работа этой клаузулы очень сильно похожа на работу WHERE с той лишь разницей, что HAVING фильтрует данные после группировки, а WHERE задает условия на выбор строк до нее.

Дополнительная фильтрация выборки: HAVING и SELECT в SQLite

В этой записи мы научимся производить дополнительную фильтрацию данных в запросах на выборку данных: сначала мы рассмотрим особенности реализации HAVING в SQL и базах данных SQLite, а затем мы попробуем реализовать несколько примеров с использование команды SELECT и HAVING. И, как всегда для данной темы, мы посмотрим: с какими еще командами языка SQL можно использовать HAVING.

Как реализовать дополнительную фильтрацию при выборке данных из таблиц в SQLite: HAVING и SELECT

SQL запросы SELECT довольно объемные и мы уже рассмотрели простые примеры выборки данных из базы данных и рассмотрели выборку данных с условием, которое налагается уточняющей фразой WHERE. Но бывает так, что при осуществлении выборки из базы данных у нас есть необходимость в том, чтобы провести дополнительную фильтрацию. Дополнительная фильтрация данных выборки осуществляется при помощи клаузулы (предиката) HAVING.

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

Суть предиката HAVING заключается в том, что он используется после группировки выборки данных и условие, накладываемое HAVING, накладывается на результаты работы группировки. Таким образом мы еще больше усложняем SQL запрос и теперь будем рассматривать комбинацию: SELECT, WHERE, GROUP BY и HAVING.

Именно в таком порядке нужно использовать ключевые слова, чтобы получить результат: SELECT, WHERE, GROUP BY и HAVING. Дополнительная фильтрация HAVING может быть реализована сколь угодно сложным выражением при помощи различных SQL операторов, как и условие WHERE.

Разница между WHERE и HAVING заключается в том, что WHERE накладывает условие на выборку, а HAVING создает дополнительное условие после группировки, другими словами: HAVING фильтрует результаты группировки в запросе SELECT. Ключевое слово HAVING часто встречается в предложениях SELECT, поэтому нужно разобраться с его использованием.

Разбираться с HAVING в запросах SELECT лучше всего на примерах, поэтому давайте к ним и перейдем.

Примеры дополнительной фильтрации данных при выборке из баз данных SQLite3: SELECT и HAVING

Пример дополнительной фильтрации данных из базы данных с SQLite3 не будет оригинальным, мы воспользуемся теми данными, что у нас были ранее. Мы каждый раз создаем новые таблицы и наполняем их похожими данными из-за специфики публикаций, так как они ориентированы на блог и гонять посетителей по страницам, чтобы посмотреть исходные данные – не самая лучшая идея на наш взгляд, так же никто не отменял правила: повторение – мать учения. Поэтому создаем таблицу в базе данных SQLite при помощи команды CREATE TABLE:

[php]

CREATE TABLE table1 (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

pro TEXT NOT NULL,

sex TEXT NOT NULL,

sal REAL CHECK (sal > 15000)

);

[/php]

Таблица table1 довольно простая: она состоит из четырех столбцов: столбец id имеет ограничение уровня таблицы - ограничение первичного ключа PRIMARY KEY, другие столбцы имеют ограничения уровня столбца для поддержания целостности данных. Мы задали класс данных столбцам, чтобы SQLite корректно выполнял операции сравнения значений при фильтрации данных. Напомним, что в SQLite у столбцов есть аффинированный тип данных, который не служит в целях обеспечения целостности, а нужен лишь для сравнения значений, так как в SQLite динамическая типизация данных.

Наполняем таблицу данными, для этого у нас есть команда INSERT:

[php]

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Пупкин Матвей’, 'Дантист', ‘м’, 55000.00);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Cумкин Денис’, 'Юрист', ‘м’, 35040.90);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Иван Иванов’, 'Младший юрист', ‘м’, 16000.00);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Ирина Маркова’, 'Бухгалтер', ‘ж’, 31200.10);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Алина Петрова’, 'Менеджер продаж', ‘ж’, 21200.10);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Любовь Михайлова’, 'Секретарь', ‘ж’, 16200.10);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Инна Сидорова’, 'Руководитель отдела обслуживания', ‘ж’, 66200.10);

INSERT INTO table1 (name, pro, sex, sal)

VALUES (‘Любовь Михайлова’, 'Документооборотчик', ‘ж’, 21200.10);

[/php]

Мы наполнили таблицу данными и самое время реализовать пример дополнительной фильтрации данных при выборке из таблиц баз данных, воспользовавшись SELECT и HAVING:

[php]

SELECT id, name, sex, sum (sal) FROM table1

WHERE sex = 'ж'

GROUP BY name

HAVING sum (sal)>30000;

id      name    sex     sum (sal)

8       Любовь Михайлова        ж       37400.2

7       Инна Сидорова   ж       66200.1

4       Ирина Маркова   ж       31200.1

— Для сравнения напишем запрос без использования HAVING

SELECT id, name, sex, sum (sal) FROM table1

WHERE sex = 'ж'

GROUP BY name;

id      name    sex     sum (sal)

5       Алина Петрова   ж       21200.1

8       Любовь Михайлова        ж       37400.2

7       Инна Сидорова   ж       66200.1

4       Ирина Маркова   ж       31200.1

[/php]

Мы видим, что комбинация SELECT, WHERE, GROUP BY и HAVING дала нам меньше строк, чем второй вариант, так как была выполнена дополнительная фильтрация данных после группировки. Вместе с SELECT и HAVING мы можем использовать SQL операторы, чтобы усложнить или уточнить условия дополнительной фильтрации:

[php]

SELECT id, name, sex, sum (sal) FROM table1

WHERE sex = 'ж'

GROUP BY name

HAVING sum (sal)>30000 AND id > 4;

id      name    sex     sum (sal)

8       Любовь Михайлова        ж       37400.2

7       Инна Сидорова   ж       66200.1

[/php]

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

Использование дополнительной фильтрации с другими SQL запросами в SQLite

Уточняющая фраза HAVING используется исключительно в запросах SELECT, но запросы SELECT могут быть использованы вместе с другими командами SQLite. Давайте посмотрим, в каких командах SQLite мы можем встретить HAVING. Не берите в расчет команды управления транзакциями и команды определения доступа к данными.

Команды группы определения данных и клаузула HAVING:

1. Создание таблицы в базе данных выполняется командой CEATE TABLE, если мы создаем таблицу на основе другой таблицы с использованием ключевых слов AS и SELECT, то мы можем использовать клаузулу HAVING.
2. Если мы хотим модифицировать таблицу в базе данных, то мы не сможем воспользоваться клаузулой HAVING, так как команда ALTER в SQLite не предполагает использование SELECT.
3. Если нам нужно удалить таблицу при помощи команды DROP, то мы не можем использовать клаузулу HAVING, так как не можем удалять таблицы с использованием подзапроса SELECT.

Команды манипуляции данными и предикат HAVING:

1. Команда SELECT может содержать другую команду SELECT в виде подзапроса, объединение двух запросов или же объединения таблиц. Поэтому мы можем использовать HAVING столько раз, сколько сочтем нужным.
2. При добавлении строк в таблицу базы данных SQLite мы можем использовать предикат HAVING, если команда INSERT выполняется с подзапросом SELECT.
3. Предикат HAVING мы не можем использовать с командой UPDATE, если нужно изменить данные в таблице базы данных, так как HAVING выполняется после группировки, а группировка значений никак не позволит однозначно идентифицировать строку, значение которой мы хотим модифицировать.
4. Уточняющая фраза HAVING не может быть использована при удалении строк из таблицы базы данных по тем же причинам, что и модификация данных. Даже в том случае если удаляемые строки для команды DELETE определяются запросом SELECT.

Итак, мы разобрались как сделать дополнительную фильтрацию при выборке данных из таблиц баз данных SQLite3 или фильтрацию результатов группировки данных при выборке. Важно понять последовательность и некоторые правила совместного использования SELECT, HAVING, GROUP BY, WHERE.