База SQLite и основы SQL. Часть 3 (фильтрация данных)
10-11-2021Время чтения ~ 4 мин.SQL 3119
Основная мощь 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 Миша
Итого
Пока мы работаем с одной таблицей. Она простая, но мы уже можем уже делать выборки с разными условиями. Они являются основой для понимания того, как получать данные из нескольких таблиц.