Основы баз данных и запросов

Запрос выбирает учеников девятого класса, у которых пятёрка по математике или по физике: SELECT name FROM pupils WHERE class = 9 AND math = 5 OR physics = 5. Ученик читает его так, как сказал бы вслух, и ждёт список из одного класса. Приходит вперемешку: в ответе все, у кого пятёрка по физике, из любого класса, потому что И в запросе связывает сильнее, чем ИЛИ, и условие читается как «девятый класс и пятёрка по математике, либо пятёрка по физике». Скобки вокруг условия по оценкам решают дело. Ошибки в синтаксисе нет, база ответила честно, и именно поэтому такая ошибка стоит дороже опечатки: ответ выглядит правдоподобно.

Большая часть темы устроена так же. Язык запросов сам по себе мал, и выучить его можно за несколько занятий. Сложность в том, что он делает ровно то, что написано, а ученик пишет то, что имел в виду.

Таблица, ключ и связь

База данных хранит данные в таблицах, где строка описывает один объект, а столбец одно его свойство. У ученика есть фамилия, класс, оценка; у заказа есть сумма и дата. Чтобы строки можно было различать, у таблицы заводят ключ: столбец, значения в котором не повторяются. Фамилия на роль ключа не подходит, потому что однофамильцы найдутся в любой школе, а вот номер, выданный при записи, подходит. Идея кажется очевидной, пока не придётся объяснять, почему нельзя взять фамилию; история про двух Ивановых в одном классе запоминается лучше определения.

Дальше возникает связь. Заказ принадлежит клиенту, но имени клиента в таблице заказов нет, там лежит его номер: поле, которое ссылается на ключ другой таблицы, называют внешним ключом. Причина практическая. Если клиент поменял фамилию, правка делается в одном месте, и во всех его заказах она видна сразу. Если бы имя копировалось в каждый заказ, часть строк осталась бы со старым. Такая связь называется «один ко многим»: у одного клиента много заказов, а у заказа один клиент.

Для читателя, который уже работал с графами, аналогия лежит на поверхности: таблицы это вершины, связи по внешним ключам это рёбра. Статья про графы объясняет, как такие связи обходят, и у задачи «найти все заказы клиента из такого-то города» тот же скелет: пройти по связи из одной таблицы в другую.

Запрос как условие

Запрос на выборку состоит из нескольких частей, и порядок, в котором их читает база, отличается от порядка записи. Пишем SELECT, потом FROM, потом WHERE, а база сначала берёт таблицу из FROM, потом отбирает строки по WHERE, и только потом показывает столбцы из SELECT. Ученик, который держит этот порядок в голове, перестаёт удивляться, почему в WHERE нельзя использовать имя столбца, придуманное в SELECT.

Условия в WHERE ломаются в нескольких привычных местах. Первое уже описано: приоритет И над ИЛИ, и привычка всегда ставить скобки, даже когда они кажутся лишними, дешевле любой отладки. Второе связано с пустыми значениями. Ячейка, в которой ничего нет, содержит особое значение NULL, и сравнение с ним через знак равенства никогда не выполняется: условие «оценка равна NULL» не истинно ни для одной строки, даже для пустых. Нужна другая запись, IS NULL. Ученик пишет первую, получает пустой ответ и начинает сомневаться в данных.

Третье место — текст. Строковые значения берут в одинарные кавычки, и ошибка бывает двойной: либо кавычек нет и база считает слово именем столбца, либо кавычки стоят, а регистр букв в слове не совпал с тем, что записано в таблице. Об этом рассказывают нечасто, а между тем многие случаи «запрос ничего не вернул» объясняются именно так.

Четвёртое место связано с типом данных. Если числа в столбце хранятся как текст (так бывает после выгрузки из таблицы), то сортировка и сравнение идут по символам: «10» оказывается меньше «9», потому что единица стоит раньше девятки. Запрос выдаёт отсортированный список, который отсортирован неверно, и увидеть это можно только глазами, пробежав по нескольким строкам ответа.

Соединение таблиц и группировка

Когда данные лежат в двух таблицах, их соединяют по ключу: JOIN с условием ON, которое говорит, какой столбец одной таблицы равен какому столбцу другой. Обычное соединение оставляет только строки, нашедшие пару. Клиент, который ничего не заказывал, в результат не попадает, и если вопрос звучал «сколько заказов у каждого клиента», нулевые клиенты потеряются молча. Левое соединение сохраняет всех клиентов из левой таблицы и ставит NULL там, где пары нет.

Здесь же прячется ловушка, которую пропускают чаще других. После левого соединения клиент без заказов представлен одной строкой, где поля заказа пусты. Подсчёт всех строк, COUNT от звёздочки, засчитает эту строку и выдаст единицу. Подсчёт значений поля заказа, COUNT от номера заказа, пустое значение пропускает и выдаст ноль. Ответ «один заказ» у клиента, у которого заказов нет, выглядит абсурдно, но в длинной таблице такая единица теряется среди остальных чисел.

Группировать лучше по ключу, а не по имени. Запрос SELECT clients.name, COUNT(orders.id) FROM clients LEFT JOIN orders ON orders.client_id = clients.id GROUP BY clients.name выглядит верно, но два клиента с одинаковым именем сольются в одну строку, и их заказы сложатся. Если группировать по номеру клиента, а имя выводить рядом, строки останутся разными. Здесь ключ нужен не для порядка в таблице, а для правильности ответа, и тот, кто это увидел один раз, перестаёт считать ключи формальностью.

Группировка собирает строки с одинаковым значением в группы и считает по каждой: количество, сумму, среднее. С ней связано главное правило: всё, что стоит в SELECT и не является подсчётом, должно стоять и в GROUP BY. И ещё различие, которое путают постоянно. WHERE отбирает строки до группировки, HAVING отбирает группы после неё, поэтому условие «клиенты с более чем двумя заказами» записывается через HAVING, а условие «заказы дороже тысячи» через WHERE.

Связанные таблицы в задаче экзамена

В первой части экзамена по информатике бывает задание, где данные лежат в нескольких связанных таблицах электронной таблицы: например, товары, магазины и продажи, и связь между ними идёт по номерам. Какими средствами его решать, подсказывает документ об организации экзамена на ваш год. Идея решения одинакова что в запросе, что в формулах: соединить по номерам, отобрать строки, посчитать.

Теряют здесь на двух вещах. Первая — связь по названию вместо номера: названия повторяются, номера нет. Вторая — приблизительный поиск функцией ВПР: если четвёртый аргумент не указан, она ищет ближайшее значение, а не точное, и при сортировке столбца не по возрастанию возвращает чужую строку. Нужно указывать ноль, то есть точное совпадение. В русской версии таблицы этот аргумент называется интервальным просмотром, и нужное значение для него ноль или ЛОЖЬ.

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

Общую карту заданий первой части даёт статья первая часть ЕГЭ по информатике: карта заданий, а структура экзамена целиком и языки программирования описаны в структуре ЕГЭ по информатике. Базы данных в школьной программе чаще всего изучают на углублённом уровне, и порядок знакомства с темами по классам разобран в статье информатика в школе: что учить и в каком порядке.

Что проверить в запросе до отправки

Прежде чем считать ответ готовым, пройдитесь по короткому списку вопросов к собственному запросу:

• стоят ли скобки вокруг каждого ИЛИ, если рядом есть И;
• сравниваются ли пустые значения через IS NULL;
• совпадает ли регистр и написание текстовых значений с тем, что в таблице;
• хранятся ли числа в столбце как числа, а не как текст, иначе сортировка и сравнение пойдут по символам;
• не теряются ли строки без пары при соединении, и нужно ли это;
• условие по группе стоит в HAVING, условие по строке в WHERE.

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

Как подступиться, если запросы не получаются

Помогает отказаться от компьютера на первые два занятия. Нарисуйте две маленькие таблицы по пять строк, придумайте вопрос и выполните запрос руками: отметьте, какие строки отбирает WHERE, какие остаются после соединения, как группируются. Потом проверьте себя на компьютере: в стандартной поставке Python есть модуль sqlite3, и запрос к маленькой базе занимает несколько строк кода. Сверка ответа на бумаге с ответом базы показывает расхождение сразу, и каждое расхождение даёт объяснимый повод вернуться в тему. Хорошо добавить в маленькую базу строку-ловушку: клиента без заказов, двух однофамильцев, пустую оценку. Тогда каждая из ловушек этой статьи сработает в вашем собственном запросе, и запомнится она лучше, чем после чтения.

Если ученик пишет запросы уверенно, но ошибается в ответах задач, причина нередко в чтении условия: он решил вопрос, который видел, а не тот, который был задан. Репетитор здесь работает с формулировкой: просит пересказать вопрос своими словами и показать, какие строки таблицы должны попасть в ответ, ещё до запроса.

Хорошая привычка для любого разбора: перед запуском записать рядом с запросом, сколько строк должно вернуться. Расхождение между ожидаемым и полученным числом почти всегда указывает на соединение или на условие в WHERE, и дальше остаётся выяснить, на какое из двух. Репетитору этот приём тоже удобен: ему не нужно читать весь запрос, чтобы увидеть, в какую сторону ушла ошибка.

Начать поиск:

• репетиторы по информатике
• репетиторы по информатике для подготовки к ЕГЭ
• репетиторы по информатике с пробным уроком
• репетиторы по информатике для 10–11 класса

Вопросы и ответы

Нужно ли учить SQL, если на экзамене таблицы? Идея общая: связь по ключу, отбор по условию, группировка. SQL закрепляет её лучше любых формул.

Чем реляционная база отличается от одной большой таблицы? Одна большая таблица повторяет данные: имя клиента встречается в каждой строке заказа, и правка в одном месте не доходит до остальных. Разнесённые по таблицам данные и связь по ключам убирают повторы, но платят за это соединением, которое приходится писать в запросе.

Какую программу взять для практики? SQLite: она встроена в Python, и ничего ставить отдельно не нужно.

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

Что делать, если запрос возвращает пустой результат? Убрать условия по одному и смотреть, на каком ответ появляется. Чаще всего виноваты NULL или регистр букв в тексте.