Базы данных и SQL: тренировочные вопросы с объяснениями

1Что такое реляционная база данных

Реляционная база данных хранит данные в таблицах. Таблица выглядит как электронная таблица: каждая строка — одна запись, каждый столбец — одно свойство.

В магазине может быть таблица customers со столбцами id, name и city и таблица orders со столбцами id, customer_id и total.

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

Внешний ключ — это столбец, ссылающийся на первичный ключ другой таблицы. В orders столбец customer_id показывает, какой клиент сделал заказ.

Так связываются таблицы: у одного клиента может быть много заказов, но каждый заказ принадлежит одному клиенту. Это связь «один ко многим».

SQL (Structured Query Language) — это язык, на котором создают таблицы, изменяют данные и задают вопросы о них.

2Чтение данных с помощью SELECT

Большинство вопросов касается оператора SELECT. Он состоит из предложений, которые всегда пишутся в таком порядке:

• SELECT перечисляет нужные столбцы. DISTINCT удаляет повторяющиеся строки.
• FROM называет таблицу или объединённые таблицы.
• WHERE оставляет только строки, удовлетворяющие условию, например WHERE total > 20.
• GROUP BY помещает строки с одинаковым значением в одну группу, чтобы можно было считать или суммировать по группам.
• HAVING оставляет только группы, удовлетворяющие условию. В нём можно использовать агрегаты, а в WHERE нельзя.
• ORDER BY сортирует результат, по умолчанию по возрастанию; добавьте DESC, чтобы сначала шли наибольшие.

Агрегатные функции сводят много строк в одно значение: COUNT, SUM, AVG, MIN и MAX.

База данных выполняет предложения не в том порядке, в котором вы их пишете. Сначала выполняется FROM, затем WHERE, GROUP BY, HAVING, SELECT и в конце ORDER BY.

Другие операторы изменяют данные: INSERT добавляет строки, UPDATE изменяет строки, а DELETE удаляет строки. CREATE, ALTER и DROP изменяют сами таблицы.

3Соединения, нормализация и транзакции

Соединение (join) объединяет строки двух таблиц по условию, обычно внешний ключ = первичный ключ:

• INNER JOIN (или JOIN) оставляет только строки, имеющие соответствие в обеих таблицах.
• LEFT JOIN оставляет каждую строку левой таблицы. Где соответствия нет, столбцы правой таблицы равны NULL.
• NULL означает, что значение отсутствует или неизвестно. Проверяйте его с помощью IS NULL, а не = NULL.

Нормализация разбивает таблицы так, чтобы каждый факт хранился один раз. Это позволяет не обновлять один и тот же факт в нескольких местах.

• 1NF: каждая ячейка содержит одно атомарное значение, без повторяющихся групп.
• 2NF: 1NF, и каждый неключевой столбец зависит от всего ключа, а не от части составного ключа.
• 3NF: 2NF, и ни один неключевой столбец не зависит от другого неключевого столбца.

Транзакция объединяет операторы в одну единицу. COMMIT делает её постоянной, а ROLLBACK отменяет. Транзакции подчиняются принципам ACID: атомарность (всё или ничего), согласованность, изолированность и долговечность.

4Метод по шагам

Чтобы написать запрос или выяснить, что возвращает заданный запрос:

1. Найдите таблицы, содержащие данные, и то, как они связаны ключами.
2. Напишите FROM с соединениями. Выберите LEFT JOIN, если строки без соответствия должны остаться.
3. Добавьте WHERE для условий на отдельные строки.
4. Если в вопросе есть «на каждого» или «для каждого», добавьте GROUP BY и агрегат.
5. Добавьте HAVING для условий на агрегат, затем ORDER BY, если важен порядок.
6. Проверьте результат на небольшом примере: перечислите строки после каждого предложения в порядке, в котором их выполняет база данных.

Для теоретического вопроса сначала назовите понятие (ключ, тип соединения, нормальная форма, свойство ACID), затем выберите вариант, соответствующий его определению.

5Разобранный пример

Пройдите каждый шаг. Попробуйте предсказать следующий, прежде чем открыть его.

6Типичные ошибки

• Использовать агрегат в WHERE. Условия на COUNT, SUM или AVG следует писать в HAVING.
• Писать = NULL. Любое сравнение с NULL даёт неопределённость; используйте IS NULL или IS NOT NULL.
• Использовать INNER JOIN, когда строки без соответствия должны остаться. Используйте LEFT JOIN.
• Забыть условие соединения. Без ON каждая строка сопоставляется с каждой строкой (перекрёстное соединение).
• Выбирать столбец, которого нет ни в GROUP BY, ни внутри агрегата.
• Путать DELETE (удаляет строки) и DROP TABLE (удаляет всю таблицу).
• Выполнять UPDATE или DELETE без WHERE. Это изменяет каждую строку таблицы.

7Как выглядят экзаменационные задачи

• Лёгкая: ключевые термины и факты. Что делают SELECT, WHERE, ORDER BY или DELETE, первичные и внешние ключи, что означает NULL.
• Средняя: применение понятий. Правильный запрос для задачи, WHERE и HAVING, внутренние и внешние соединения, нормальные формы, ACID.
• Сложная: случаи и анализ. Подзапросы, уровни изоляции и их аномалии, BCNF, оконные функции, индексы и производительность запросов, SQL-инъекции.

Когда дан запрос, проследите его на нескольких строках в порядке FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Большинство ловушек проявляется уже на трёх строках.