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

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Реляционные базы данных и язык SQL не получили бы такого широкого распространения если бы не несколько очень важных факторов. Первый фактор заключается в том, что реляционные базы данных — это естественный способ хранения информации, второй фактор заключается в том, что РБД очень надежны, а третий фактор заключается в том, что РСУБД позволяют выполнять очень сложные операции с данными. К таким операциям можно отнести группировку данных выборки, которая реализуется при помощи команды SELECT и уточняющей фразы GROUP BY.

Группировка данных выборки: GROUP BY и SELECT в SQLite

Выполнить группировку SQL запросом, использующим GROUP BY и SELECT не так уж и сложно, как и для любого другого запроса, нам нужно просто соблюдать некоторые правила и порядок ключевых слов в SQL предложение. Данная публикация поможет вам разобраться с этими правилами и порядком ключевых слов при составлении запроса на выборку с группировкой данных. Здесь вы найдете описание того, как работают запросы на группировку выборки данных в SQL в целом и в SQLite3 в частности, а так же увидите несколько примеров того, как можно комбинировать SELECT и GROUP BY.

Как сделать группировку данных при выборке данных из базы данных SQLite: GROUP BYи SELECT

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

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

Группировка выборки данных используется для того, чтобы уменьшит код приложения, которое использует базу данных. Если бы не было группировки, нам бы пришлось усложнять алгоритмы программ и писать свой код, который выполняет аналогичную функцию гораздо дольше, нежели СУБД.

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

Примеры группировки данных при выборке данных из таблиц SQLite3: примеры GROUP BY и SELECT

Для примера группировки выборки данных в запросах SELECT мы будем использовать таблицу из предыдущей части, давайте создадим таблицу, воспользовавшись командой CREATE:

[php]

CREATE TABLE table1 (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

pro TEXT NOT NULL,

sex TEXT NOT NULL,

salREALCHECK (sal> 15000)

);

[/php]

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

Теперь добавим данные в таблицу базы данных при помощи команды 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);

[/php]

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

[php]

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

WHERE sex = 'ж'

GROUP BY name;

id      name    sex     sum (sal)

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

5       Любовь Михайлова        ж       16200.1

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

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

— Для примера напишем этот же запрос, но не будем использовать

— GROUP BY

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

WHERE sex = 'ж';

id      name    sex     sum (sal)

6       Инна Сидорова   ж       134800.4

[/php]

Как видите, результаты разнятся, в данном случае мы осуществляли группировку выборки по столбцу name, но можно использовать и несколько столбцов для группировки, для этого после ключевого слова GROUP BY нужно указать столбцы через запятую. На самом деле пример довольно неинформативный, давайте лучше посчитаем сколько должностей занимает каждый из мужчин в компании, скомбинировав SELECT, WHERE и GROUP BY:

[php]

SELECT id, name, sex, count (pro) FROM table1

WHERE sex = 'м'

GROUP BY name;

id      name    sex     count (pro)

2       Cумкин Денис    м       1

1       Пупкин Матвей   м       1

3       Иван Иванов     м       1

— попробуем сгруппировать выборку по столбцу sex

SELECT id, name, sex, count (pro) FROM table1

WHERE sex = 'м'

GROUP BY sex;

id      name    sex     count (pro)

3       Иван Иванов     м       3

[/php]

Обязательное условие при группировке выборки данных заключается в том, что столбцы, указанные после GROUP BY, должны быть указаны и после SELECT. Сверху у вас два примера группировки. Один пример группирует выборку по столбцу name, в нашем случае с учетом условия в столбце name три уникальных значения, и мы получаем три строки с новым столбцом, которого нет в таблице: count (pro) – это функция агрегации, которая считает количество повторений значения pro в данном случае.

Второй пример группирует выборку по столбцу sex, значение этого столбца повторяется три раза, поэтому в результате мы получаем одну строку и значение 3 в столбце count (pro). Давайте посмотрим еще один пример, сделав его чуть более сложным, например, секретарь занял вторую должность и теперь является документооборотчиком и секретарем:

[php]

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

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

[/php]

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

[php]

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

— Для сравнения сделаем обычную выборку всех женщин

SELECT id, name, sex, sal FROM table1

WHERE sex = 'ж' ;

id      name    sex     sal

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

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

6       Любовь Михайлова        ж       16200.1

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

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

— И вместо имени попробуем сгруппировать по профессии

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

WHERE sex = 'ж'

GROUP BY pro;

id      pro     sex     sum (sal)

4       Бухгалтер       ж       31200.1

5       Менеджер продаж ж       21200.1

7       Руководитель отдела обслуживания        ж       66200.1

6       Секретарь       ж       16200.1

8       Документооборотчик      ж       21200.1

[/php]

Как видите, результаты везде разные, всё зависит от того, используется ли группировка и по какому столбцу она реализована. Надеемся, что вы разобрались с тем, как работает группировка данных при выборке и как можно скомбинировать WHERE, SELECT и GROUP BY в одном SQL запросе.

Использование GROUP BY с другими SQL командами

Ключевое слово GROUP BY может быть использовано с другими SQL запросами. Мы не берем в расчет команды управления транзакциями, так как внутри транзакций мы можем использовать в принципе любые SQL предложения. Так же мы не берем в расчет команды группы определения доступа к данным, во-первых, с ними мы не сможем использовать группировку, во-вторых, их нет в SQLite.

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

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

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

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

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