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

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3.  При работе с базами данных мы часто выполняем запросы, в которых необходимо выполнить сортировку значений или сравнить данные из строк. Каждая СУБД  имеет свои собственные алгоритмы сравнения данных и сортировки данных. В данной записи мы познакомимся с тем, как реализовано сравнение данных в SQLite3 и в каком порядке SQLite3 сортирует данные.

Порядок сортировки и сравнение данных в SQLite3

Для начала приведем SQL операторы сравнения, которые могут быть использованы в SQLite3:

«=», «==», «<», «<=», «>», «>=», «!=», «<>», «BETWEEN», «IN», «IS», «NOT IN» и «IS NOT»

Типичный набор оператор сравнения для любой СУБД.

В SQLite3, как и в любой другой СУБД, есть порядок сортировки и четкие правила, по которым значения сортируются:

• значения с классом NULL считаются меньше любого другого значения, даже другого значения NULL;
• значения с классом REAL или INTEGER считаются меньше, чем значения TEXT или BLOB, когда происходит сравнение INTEGER или REAL используется числовое сравнение, например: 5 больше 4.97645323223423421.
• значение с классом данных TEXT меньше значения с классом BLOB;
• для сравнения значений с классом BLOB между собой используется функция memcmp ().

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

• если одно значение имеет класс INTEGER, REAL или NUMERIC, а второе значение имеет класс TEXT или BLOB, то SQLite будет пытаться преобразовать второе значение к классу NUMERIC;
• если первое значение имеет класс TEXT, а у второго значения класс не задан, то второе значение будет преобразовано в TEXT;
• если сравниваемые значения не удовлетворяют первым двум правилам, то преобразование не применяется.

Приведем пример сравнений SQLite3 из документации данной библиотеки с переводом комментариев:

[php]

CREATE TABLE t1 (

a TEXT,      — аффинирован с TEXT

b NUMERIC,   — аффинирован с NUMERIC

c BLOB,      — неаффинирован

d            — неаффинирован

);

— Значения сохраняются как TEXT, INTEGER, TEXT и INTEGER соответственно

INSERT INTO t1 VALUES ('500', '500', '500', 500);

SELECT typeof (a), typeof (b), typeof©, typeof (d) FROM t1;

text|integer|text|integer

— Поскольку столбец «a» аффинирован с TEXT, числовые значения с правой стороны

— выражений будут перед сравнением преобразованы в TEXT.

SELECT a < 40,   a < 60,   a < 600 FROM t1;

0|1|1

— К правым операндам должна быть применена аффинированность с TEXT, но

— поскольку они уже принадлежат классу TEXT, это не имеет смысла.

— Никаких преобразований не происходит.

SELECT a < '40', a < '60', a < '600' FROM t1;

0|1|1

— Поскольку столбец «b» аффинирован с NUMERIC, к операндам с правой стороны

— также будет применена аффинированность NUMERIC. Поскольку эти операнды уже

— являются числовыми, применение аффинированности ненужно.

— Никаких преобразований не происходит. Все значения сравниваются как числа.

SELECT b < 40,   b < 60,   b < 600 FROM t1;

0|0|1

— К операндам справа будет применена аффинированность NUMERIC, и они будут

— преобразованы из TEXT в INTEGER. Затем будет произведено числовое сравнение.

SELECT b < '40', b < '60', b < '600' FROM t1;

0|0|1

— Никаких аффинированных преобразований не производится. Значения в правых

— частях принадлежат классу хранения INTEGER и потому меньше значений TEXT

— слева.

SELECT c < 40,   c < 60,   c < 600 FROM t1;

0|0|0

— Никаких аффинированных преобразований не производится.

— Значения сравниваются как значения TEXT.

SELECT c < '40', c < '60', c < '600' FROM t1;

0|1|1

— Никаких аффинированных преобразований не производится. Значения с правой

— стороны принадлежат классу хранения INTEGER и сравниваются со значениями

— INTEGER слева как числа.

SELECT d < 40,   d < 60,   d < 600 FROM t1;

0|0|1

— Никаких аффинированных преобразований не производится.

— Значения INTEGER слева всегда меньше значений TEXT справа.

SELECT d < '40', d < '60', d < '600' FROM t1;

1|1|1

[/php]

Сравнение данных в SQLite3 при выполнении математических операций

SQLite3 ведет себя интересно при использовании математических операторов (+, — , *, / и другие). SQLite всегда преобразовывает значения в класс NUMERIC при выполнении математических операций, даже если преобразование будет происходить с потерями. Если математическая операция будет проводиться со значением NULL, то результатом всегда будет NULL вне зависимости от типа операции. Если SQLite3 не может преобразовать значение в класс NUMERIC, то значение преобразуется в ноль.

Сортирующие последовательности и сравнение строк в SQlite3

В SQLite3 есть сортирующие последовательности, они используются для сравнения двух строк, другие СУБД для этих целей используют значения из таблиц кодировок, а вот в SQLite3 для этого есть сортирующие последовательности. В SQLite3 есть три встроенных функции, которые выполняют сравнение строк: BINARY, NOCASE и RTRIM:

1. Функция BINARY сравнивает строки при помощи функции memcmp (), ее результат не зависит от кодировки, поскольку происходит побайтное сравнение.
2. Принцип работы NOCASE такой же, как и у BINARY, за исключение первых 26-ти прописных букв ASCII, которые перед сравнением преобразуются в свои эквиваленты в нижнем регистре.
3. RTRIM работает, как и BINARY, но откидывает пробелы в конце строки.

Стоит отметить, что каждый столбец имеет функцию сравнения, ее можно указать при создании таблицы, если вы ее не объявили, то по умолчанию используется BINARY. В SQLite3 есть правила применения сортирующих последовательностей, их всего три:

1. Если одному из операндов, расположенному справа, явно назначена сортирующая функция с использованием COLLATE, то для сравнения используется эта явная функция, но с предшествующим выполнением сортирующей функции сопоставленной левому операнду.
2. Если одним из операндов является столбец, то его сортирующая функция является приоритетной и предшествует выполнению сортирующей функции следующего операнда. С целью указания приоритетности выполнения, имя столбца, которое может начинается с одного или нескольких унарных операторов «+». Такая конструкция будет по-прежнему считается именем.
3. В остальных случаях для сравнения используется сортирующая функция BINARY.

Давайте посмотрим несколько примеров применения сортирующих последовательностей в SQLite3:

[php]

CREATE TABLE t1 (

x INTEGER PRIMARY KEY,

a,                 /* сортирующая последовательность BINARY */

b COLLATE BINARY,  /* сортирующая последовательность BINARY */

c COLLATE RTRIM,   /* сортирующая последовательность RTRIM  */

d COLLATE NOCASE   /* сортирующая последовательность NOCASE */

);

/* x   a     b      c       d */

INSERT INTO t1 VALUES (1,'abc','abc', 'abc  ','abc');

INSERT INTO t1 VALUES (2,'abc','abc', 'abc',  'ABC');

INSERT INTO t1 VALUES (3,'abc','abc', 'abc ', 'Abc');

INSERT INTO t1 VALUES (4,'abc','abc ','ABC',  'abc');

 

/* Сравнение строк a=b выполняется с использованием

** сортирующей последовательностью BINARY. */

SELECT x FROM t1 WHERE a = b ORDER BY x;

— результат 1 2 3

 

/* Сравнение строк a=b выполняется с использованием

** сортирующей последовательностью RTRIM. */

SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;

— результат 1 2 3 4

 

/* Сравнение строк a=b выполняется с использованием

** сортирующей последовательностью NOCASE. */

SELECT x FROM t1 WHERE d = a ORDER BY x;

— результат 1 2 3 4

 

/* Сравнение строк a=b выполняется с использованием

** сортирующей последовательностью BINARY. */

SELECT x FROM t1 WHERE a = d ORDER BY x;

— результат 1 4

 

/* Сравнение строк 'abc'=c выполняется с использованием

** сортирующей последовательностью RTRIM. */

SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;

— результат 1 2 3

 

/* Сравнение строк c='abc' выполняется с использованием

** сортирующей последовательностью RTRIM. */

SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;

— результат 1 2 3

 

/* Группировка выполняется с использованием

** сортирующей последовательности NOCASE.

** Значения 'abc', 'ABC' и 'Abc' попадают в одну и ту же группу. */

SELECT count (*) FROM t1 GROUP BY d ORDER BY 1;

— результат 4

 

/* Группировка выполняется с использованием

** сортирующей последовательности BINARY.

** Значения 'abc', 'ABC' и 'Abc' попадают в разные группы */

SELECT count (*) FROM t1 GROUP BY (d || '') ORDER BY 1;

— результат 1 1 2

 

/* Сортировка по столбцу c выполняется с использованием

** сортирующей последовательности RTRIM. */

SELECT x FROM t1 ORDER BY c, x;

— результат 4 1 2 3

 

/* Сортировка по (c||'') выполняется с использованием

** сортирующей последовательности BINARY. */

SELECT x FROM t1 ORDER BY (c||''), x;

— результат 4 2 3 1

 

/* Сортировка по столбцу c выполняется с использованием

** сортирующей последовательности NOCASE. */

SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;

— результат 2 4 3 1

[/php]