База SQLite и основы SQL. Часть 3

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

Делается это с помощью ключевого слова WHERE, которое размещается сразу после FROM. Я думаю, что WHERE — наиболее часто используемая конструкция, которая по смыслу совпадает с программистским IF. С помощью WHERE можно задавать разнообразные условия для фильтрации данных.

В общем случае SQL будет строиться по такой схеме:

SELECT поля 
FROM таблица 
WHERE условия
ORDER BY порядок
LIMIT смещение, количество;

Такова последовательность команд в любом SQL-запросе SELECT. Чуть позже мы добавим ещё команды группировки.

Условия в WHERE будут знакомы любому программисту, поскольку здесь работают все те же правила: больше, меньше, равно, логические AND, OR, NOT и т.п.

SELECT * FROM firstTable WHERE id > 2 ORDER BY id;
 
id  name
--------
3   Саша
4   Вася

Условие id > 2 отфильтрует данные и оставит только те, где id больше 2. Можно комбинировать разные условия.

SELECT * FROM firstTable WHERE id > 1 AND id <= 3 ORDER BY name;
 
id  name
--------
2   Петя
2   Петя
3   Саша

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

Условия для текста

Для поиска по текстовым полям применяется два условия LIKE и GLOB (реже). Аргументом LIKE является регулярное выражение, где символ % указывает на любую последовательность символов, а _ указывает на одиночный символ.

SELECT DISTINCT name FROM firstTable WHERE name LIKE 'П%';
 
name
----
Петя

В данном запросе будет выведены все имена, которые начинаются на «П». А вот так можно получить все имена, где вторая буква «а»:

SELECT DISTINCT name FROM firstTable WHERE name LIKE '_а%';
 
name
----
Вася
Саша

Здесь важный момент в том, что поиск в LIKE регистрозависимый (точнее для строк юникода, куда входит кириллица). Эта особенность SQLite серьёзно ограничивает возможности регистронезависимого поиска. :-(

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

В некоторых случаях вместо LIKE можно использовать команду GLOB. Она работает аналогично, только использует спецзамены как это принято для файлов. Например * указывает на любую последовательность, _ — одиночный символ. Здесь же можно задать диапазон символов. Например так мы сможем найти все имена начинающиеся на «П» и «п»;

SELECT DISTINCT name FROM firstTable WHERE name GLOB '[пП]*';

Диапазоны значений

В некоторых случаях удобней использовать условия IN и BETWEEN, с помощью которых можно задать диапазон значений. Это заменяет многочисленные условия AND/OR.

SELECT * FROM firstTable WHERE id IN (1, 4) ORDER BY id;
  
id  name
--------
1   Вася
4   Вася

В данном случае IN указывает список возможных значений поля.

Условие BETWEEN задаёт диапазон «от-до»:

SELECT * FROM firstTable WHERE id BETWEEN 2 AND 4 ORDER BY id;
   
id  name
--------
2   Петя
2   Петя
3   Саша
4   Вася

Здесь мы ищем записи, где id будет иметь значение между 2 и 4.

C помощью логической команды NOT можно инвертировать условие. Например здесь мы получаем все записи, вне диапазона 2 и 4:

SELECT * FROM firstTable WHERE id NOT BETWEEN 2 AND 4 ORDER BY id;
 
id  name
--------
1   Вася

Обновление данных

После того, как мы познакомились с WHERE, можно поговорить о команде обновления UPDATE. Чаще всего мы обновляем только одну запись. Именно поэтому используется условие WHERE, где мы указываем какие именно строки нужно обновить. Например у нас в таблице два Пети и два Васи. При этом Пети у нас с одним id. Давайте их исправим.

Общая структура запроса UPDATE такая:

UPDATE таблица 
SET поле = значение,
    поле = значение
WHERE условие;

Изменим второго Васю (id = 4) на Борю.

UPDATE firstTable 
SET name = 'Боря'
WHERE id = 4;
 
id  name
---------
1   Вася
2   Петя 
3   Саша
4   Боря
2   Петя

Теперь давайте изменим второго Петю. Но есть нюанс — у обоих одинаковый id, то есть когда мы укажем его в условии WHERE, то обновятся обе записи — база просто не сможет их различить. Это произошло из-за того, что поле id не является уникальным, а представляет собой произвольное число. Так мы создали таблицу. В будущем мы вернемся к этому вопросу, но для таких ситуаций SQLite предоставляет автоматически генерируемый уникальный номер каждой записи. Он называется ROWID и по сути представляет собой просто скрытое поле.

SELECT rowid, * FROM firstTable ORDER BY rowid;
 
rowid  id  name
--------------
1      1   Вася
2      2   Петя 
3      3   Саша
4      4   Боря
5      2   Петя

И здесь мы видим, что у второго Пети ROWID отличается от первого, а значит мы можем использовать его для обновления.

UPDATE firstTable 
SET
   id = 5, 
   name = 'Миша'
WHERE rowid = 5;
 
SELECT * FROM firstTable ORDER BY id;
 
id  name
---------
1   Вася
2   Петя 
3   Саша
4   Боря
5   Миша

Команда UPDATE достаточно проста для понимания, но как и INSERT требует соблюдения структуры таблицы.

Удаление с условием

Мы уже знаем команду DELETE, которая очищает таблицу. Но мы можем точно также использовать WHERE, для того, чтобы указать выборочные записи для удаления.

Например сделаем новую запись, а потом её удалим.

INSERT INTO firstTable VALUES (6, 'Антон'); -- добавили новую запись
 
id  name
---------
1   Вася
2   Петя 
3   Саша
4   Боря
5   Миша
6   Антон
 
DELETE FROM firstTable WHERE id = 6; -- удалили
 
id  name
---------
1   Вася
2   Петя 
3   Саша
4   Боря
5   Миша

Итого

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