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

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

Сортировку выборки данных стоит проводить после применения всех необходимых клаузул и предикатов, это обязательное правило SQL и библиотеки SQLite. Модификатор пишется после ключевого слова ORDER BY, примерно так: SELECT ORDER BY DESC или SELECT ORDER BY ASC. Данная запись поможет вам разобраться с уточняющей фразой ORDER BY и тем, как она реализована в базах данных под управлением SQLite. Из нее вы узнаете: как реализована конструкция ORDER BY в SQL и SQLite в частности, посмотрите несколько примеров работы SELECT и ORDER BY, а так же узнаете с какими SQL командами можно использовать ORDER BY помимо SELECT.

Как реализована сортировка выборки данных в базах данных SQLite: ORDER BY и SELECT

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

Сортировка выборки данных в базах данных SQLite реализуется при помощи клаузулы ORDER BY (предиката ORDER BY), выполнить сортировку во время выборки данных из базы данных иногда бывает полезно, но особенно она полезна в тех случаях, когда мы не хотим усложнять логику приложения, в которое встраиваем SQLite.

Сортировка выборки при помощи ключевого слова ORDER BY может быть выполнена после любого предиката, рассмотренного ранее. Библиотека SQLite позволяет выполнить сортировку ORDER BY и при этом задать правила сравнения значений COLLATE (если правило не будет задано, то будет использовано правило сравнение, заданное для столбцов, если у столбцов не задано правило, то будет использовано правило сравнения данных по умолчанию), а также ORDER BY имеет два модификатора, которые позволяют задать порядок сортировки: ASC и DESC. Если мы делаем выборку данных командой SELECT и сортируем данные ORDER BY, используя модификатор ASC, то данные будут отсортированы в прямом порядке, если мы выполняем SELECT ORDER BY DESC, то данные выборки будут отсортированы в обратно порядке.

Важным правилом сортировки выборки данных в базах данных SQLite является то, что клаузула ORDER BY должна идти после всевозможных фильтраций и группировок. После ORDER BY может идти только уточняющая фраза LIMIT, которая ограничивает выборку данных определённым числом строк.

Вам стоит быть аккуратными, используя SELECT и ORDER BY в базах данных SQLite3, так как SQLite – это СУБД с динамической типизацией данных, поэтому данная библиотека может преобразовывать типы данных из одного в другой. Мы подробно описали на словах то, как работает сортировка выборки данных в базах данных SQLite, теперь давайте рассмотрим несколько примеров использования ORDER BY и SELECT в SQLite3.

Примеры сортировки выборки данных из базы данных: примеры SELECT, ORDER BY, DESC, COLLATE в SQLite3

Данные примеры помогут вам разобраться, как работает сортировка выборки данных в SQLite3 и в какой последовательности нужно использовать ключевые слова и уточняющие фразы: SELECT, ORDER BY, DESC, ASC и COLLATE. Примеры довольно простые. Давайте создадим таблицу в базе данных при помощи команды 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]

Простая таблица, состоящая из четырех столбцов, для которых заданы ограничения уровня столбца и ограничения уровня таблицы, хотя для столбца id ограничение первичного ключа и задано, как ограничение уровня столбца. Вообще, обеспечение целостности данных – это очень важный аспект любой реляционной базы данных, и об этом мы говорили ранее очень подробно. Для ускорения выборки данных из базы данных любая СУБД используется индексы. В нашем случае индексом таблицы является столбец id, но у этой таблицы есть еще и внутренний индекс ROWID, который в данном случае совпадает со столбцом PRIMARY KEY.

А теперь давайте добавим данные в таблицу базы данных, для этого воспользуемся командой INSERT INTO:

[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 и уточняющую фразу ORDER BY:

[php]

SELECT * FROM table1 ORDER BY sal DESC;

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

1|Пупкин Матвей|Дантист|м|55000.0

2|Cумкин Денис|Юрист|м|35040.9

4|Маркова Ирина|Бухгалтер|ж|31200.1

5|Петрова Алина|Менеджер продаж|ж|21200.1

8|Михайлова Любовь|Документооборотчик|ж|21200.1

6|Михайлова Любовь|Секретарь|ж|16200.1

3|Иванов Иван|Младший юрист|м|16000.0

— Для примера сделаем выборку без сортировки

SELECT * FROM table1;

1|Пупкин Матвей|Дантист|м|55000.0

2|Cумкин Денис|Юрист|м|35040.9

3|Иванов Иван|Младший юрист|м|16000.0

4|Маркова Ирина|Бухгалтер|ж|31200.1

5|Петрова Алина|Менеджер продаж|ж|21200.1

6|Михайлова Любовь|Секретарь|ж|16200.1

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

8|Михайлова Любовь|Документооборотчик|ж|21200.1

[/php]

Разница выборки данных без сортировки и выборки данных с использованием ORDER BY видна невооруженным глазом. Вы можете не использовать модификаторы ASC и DESC, когда сортируете выборку, в этом случае в SQLite будет использовано значение по умолчанию ASC:

[php]

SELECT * FROM table1 ORDER BY sal;

3|Иванов Иван|Младший юрист|м|16000.0

6|Михайлова Любовь|Секретарь|ж|16200.1

5|Петрова Алина|Менеджер продаж|ж|21200.1

8|Михайлова Любовь|Документооборотчик|ж|21200.1

4|Маркова Ирина|Бухгалтер|ж|31200.1

2|Cумкин Денис|Юрист|м|35040.9

1|Пупкин Матвей|Дантист|м|55000.0

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

[/php]

Так же стоит заметить, что нужно обязательно указывать предикату ORDER BY столбец, по которому необходимо произвести сортировку, при этом SQLIte3 позволяет использовать столбец для сортировки, который не указан после команды SELECT:

[php]

SELECT id, name, sal FROM table1 ORDER BY sex;

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

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

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

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

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

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

2|Cумкин Денис|35040.9

3|Иванов Иван|16000.0

[/php]

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

[php]

SELECT id, name, sal FROM table1 ORDER BY id DESC;

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

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

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

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

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

3|Иванов Иван|16000.0

2|Cумкин Денис|35040.9

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

SELECT name, id, sal FROM table1 ORDER BY 2 DESC;

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

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

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

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

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

Иванов Иван|3|16000.0

Cумкин Денис|2|35040.9

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

[/php]

Видим, что результаты выборки одинаковы, во втором случае мы произвели сортировку по столбцу id, воспользовавшись его порядковым номером, в данном случае это двойка, так как столбец id в перечисление идет вторым. Но не нужно думать, что комбинация SELECT и ORDER BY может использовать только один столбец для сортировки, их может быть несколько, поэтому мы можем сортировать выборку данных по нескольким столбцам, давайте реализуем такой пример SELECT и ORDER BY:

[php]

SELECT id, name, sex, sal FROM table1 ORDER BY sex ASC, sal DESC;

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

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

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

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

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

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

2|Cумкин Денис|м|35040.9

3|Иванов Иван|м|16000.0

[/php]

SQLite сперва отсортировала данные по столбцу sex, а затем выполнила сортировку по столбцу sal. Поэтому в результате выборки мы получили таблицу, в которой сперва идут женщины с отсортированной зарплатой, а затем идут мужчины, для которых так же выполнена сортировка данных по зарплате.

Когда мы делаем сортировку выборки данных, SQLite начинает сравнивать значения, которые хранятся в столбце или столбцах, которые указываются после предиката ORDER BY, как вы помните, SQLite имеет несколько правил сравнения данных, мы можем указать нужное для нас правило сравнения при помощи ключевого слова COLLATE:

[php]

SELECT * FROM table1 ORDER BY sal COLLATE RTRIM DESC;

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

1|Пупкин Матвей|Дантист|м|55000.0

2|Cумкин Денис|Юрист|м|35040.9

4|Маркова Ирина|Бухгалтер|ж|31200.1

5|Петрова Алина|Менеджер продаж|ж|21200.1

8|Михайлова Любовь|Документооборотчик|ж|21200.1

6|Михайлова Любовь|Секретарь|ж|16200.1

3|Иванов Иван|Младший юрист|м|16000.0

[/php]

Обратите внимание: если вы делаете сортировку данных, то необходимо указать правило сравнения до того, как вы укажите порядок сортировки. Последним пример использования ORDER BY и SELECT в базах данных SQLite3 будет пример сортировки выборки данных с использованием других уточняющих фраз:

[php]

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

WHERE sex = 'ж'

GROUP BY name

HAVING sum (sal)>30000 AND id > 4

ORDER BY id DESC;

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

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

[/php]

Теперь вы знаете, как сделать сортировку данных при выборке данных из таблиц базы данных под управлением SQLite3, надеемся, что примеры использования ORDER BY и SELECT вам помогли с этим разобраться.

Использование сортировки ORDER BY с другими командами SQLite

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

Уточняющую фразу ORDER BY можно использовать с командами определения данных в SQLite3:

1. Если мы создаем таблицу, то можем воспользоваться ключевым словом AS, а затем написать SQL запрос SELECT, в результате получится таблица на основе запроса SELECT, соответственно, мы можем воспользоваться предикатом ORDER BY.
2. Для удаления объектов базы данных мы можем воспользоваться командой DROP, но с командой DROP мы не сможем использовать предикат ORDER BY.
3. Для изменения объектов базы данных мы можем использовать команду ALTER, но с командой ALTER мы не сможем использовать клаузулу ORDER BY.

Предикат ORDER BY мы можем использовать с командами манипуляции данными в базах данных SQLite:

1. Мы можем добавлять строки в таблицы, используя подзапрос SELECT, поэтому можно сказать, что для добавления строк можно использовать предикат ORDER BY.
2. Когда мы удаляем данные из таблицы базы данных SQLite, то мы можем отсортировать строки перед их удалением, поэтому клаузулу ORDER BY мы можем использовать с командой DELETE.
3. В процессе изменения данных в таблицах базы данных мы можем сортировать строки, которые собираемся модифицировать, у нас есть возможность использовать команду UPDATE и ORDER BY.

Итак, мы разобрались как сделать сортировку данных в базах данных SQLite. Важно понять последовательность и некоторые правила совместного использования SELECT, ORDER BY, DESC, ASC, COLLATE и других ключевых слов, тогда вы без труда сможете составлять запросы на выборку данных из базы данных и при этом сортировать полученные результаты.