Анимированное введение в SQL — учимся делать запросы к реляционным базам данных

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

SQL — это не традиционный язык программирования. В Python или Java вы пишете пошаговые инструкции, которые точно указывают компьютеру, как выполнить то или иное действие. Это называется императивным программированием

SQL работает иначе. Сначала вы понимаете, какие данные хранятся в ваших таблицах. Затем вы пишете запрос, описывающий, какие данные вам нужны, и передаёте его системе управления базами данных (СУБД). СУБД сама разбирается, как их получить. Этот декларативный стиль — часть того, что делает SQL мощным и приятным в использовании

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

Структура руководства

Руководство основано на трёх учебниках с интерактивными примерами кода, которые я использую в курсах по базам данных

• Database Design and SQL for Beginners
• Worked SQL Examples
• Programming with SQLite

Каждый раздел включает проработанные примеры, в которых я показываю, как пишу SQL шаг за шагом, объясняя свой мыслительный процесс по ходу. Вы увидите, как я экспериментирую, уточняю и строю запросы по частям — точно так же, как это делал бы настоящий разработчик. Я активно использую диаграммы «сущность — связь» и схемы, чтобы визуализировать данные, хранящиеся в базах данных

Часть 1: Стремительный обзор SQL

(из книги Database Design and SQL for Beginners)

Я начинаю с изучения простой базы данных для вымышленного центра усыновления животных под названием Paw Prints Adoption Center. Здесь вводятся основные концепции:

• Диаграммы «сущность — связь» (ER-диаграммы)
• Схемы
• Структура таблиц и связи между ними

Этот раздел имеет ключевое значение. Если вы новичок в проектировании баз данных, уделите ему внимание перед переходом к следующему

Часть 2: Основные концепции и ключевые слова SQL

(также из книги Database Design and SQL for Beginners)

Рассматриваются наиболее важные ключевые слова и идеи SQL. Каждая тема объясняется в отдельном воспроизведении с примерами:

• CREATE TABLE, ALTER TABLE
• SELECT, FROM, WHERE, JOIN
• ORDER BY, GROUP BY, HAVING
• INSERT, UPDATE, DELETE
• Вложенные запросы, обобщённые табличные выражения (Common Table Expressions, CTE) и операции над множествами (UNION, INTERSECT, EXCEPT)
• Индексы и транзакции

Этот раздел служит справочником. Если вы застряли на запросе или забыли, что делает то или иное ключевое слово, возвращайтесь сюда

Часть 3: Практические задачи

(из книги Worked SQL Examples)

Практика — вот как вы по-настоящему учитесь. Я включил 36 практических задач с использованием базы данных Paw Prints и новой университетской базы данных. Каждая задача сопровождается анимированным воспроизведением, показывающим, как я прорабатывал решение шаг за шагом

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

Часть 4: Использование SQLite в программах

(из книги Programming with SQLite)

В этом финальном необязательном разделе я показываю, как подключить SQL к реальному коду. Вы узнаете, как использовать базу данных SQLite в:

• C/C++
• Python и Flask
• Java

По этой теме полезно отдельно посмотреть Составьте еженедельное расписание занятий, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание динамических форм в React и Next.js, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Как выполнять запросы

СУБД — это программное обеспечение для управления данными в базе данных и выполнения запросов к ней. Многие СУБД требуют значительной настройки и зачастую отдельного сервера для обработки запросов. Настройка всего этого может быть сложной задачей для новичков

SQLite — это простая СУБД, которая не требует особой настройки. Ей не нужен отдельный сервер, и она хранит всю базу данных в одном файле. Это отличный инструмент для начала работы

Чтобы упростить просмотр и редактирование баз данных, я рекомендую использовать DB Browser for SQLite. Это бесплатный инструмент с открытым исходным кодом, простым интерфейсом и всей функциональностью SQLite. Вы можете открывать файлы баз данных, просматривать таблицы, выполнять запросы и редактировать данные с помощью интуитивно понятного пользовательского интерфейса

Он особенно полезен, когда вы учитесь и хотите быстро увидеть, как ваши запросы влияют на данные

Веб-альтернатива DB Browser

Если вы предпочитаете не устанавливать никакого программного обеспечения, воспользуйтесь веб-инструментом — например, SQLite Viewer или SQLite Online. Они позволяют загрузить файл .sqlite, выполнять запросы и исследовать базу данных прямо из браузера

• SQLite Viewer — простой просмотрщик только для чтения. Подходит для изучения таблиц и тестирования базовых запросов.
• SQLite Online — полнофункциональная IDE для SQLite. Вы можете создавать базы данных, загружать файлы, выполнять запросы и даже сохранять свою работу.

Оба инструмента отлично подходят для быстрых экспериментов или проверки своей работы без установки чего-либо

Воспроизведения кода и платформа Playback Press

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

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

Playback Press — это платформа, на которой я публикую свои интерактивные пошаговые разборы кода. Каждая книга включает пошаговые анимации, обучение с помощью ИИ и встроенные тесты. Я также создал Storyteller — бесплатный инструмент с открытым исходным кодом, который обеспечивает работу этих воспроизведений

Пока вы просматриваете воспроизведение кода, вы можете задавать вопросы об запросах ИИ-репетитору. Он даёт чёткие, сфокусированные ответы и не торопит вас. Вы также можете попросить его создать новые вопросы с самопроверкой в формате множественного выбора для проверки вашего понимания. Чтобы воспользоваться ИИ-репетитором и тестами, создайте бесплатную учётную запись на Playback Press и добавьте одну из книг на свою книжную полку

Часть 1: Стремительный обзор SQL

Когда кому-то поручают управлять данными, первый инстинкт большинства людей — воспользоваться электронной таблицей. Электронные таблицы просты в использовании и гибки. Но по мере того как данные становятся сложнее, начинают проявляться их слабые стороны

Одна из главных проблем — избыточность данных. Когда одна и та же информация встречается в нескольких местах, существует риск, что одна копия изменится, а остальные останутся прежними. Это может привести к несоответствиям, ошибкам и запутанным результатам

Реляционные базы данных помогают решить эту проблему, организуя данные структурированным образом, который по своей природе снижает избыточность. Прежде чем создавать базу данных, полезно смоделировать данные с помощью диаграммы «сущность — связь» (ER-диаграммы, Entity-Relationship diagram)

Диаграммы «сущность — связь»

ER-диаграмма — это инструмент планирования, используемый для визуализации структуры базы данных. Она помогает понять, какие виды данных необходимо хранить и как эти данные соотносятся друг с другом

Сущности — это основные объекты или понятия в вашей системе, например Person, Course или Dog. Сущности имеют атрибуты, которые их описывают. Например, у Person могут быть атрибуты name, date of birth и address

Связи описывают, как сущности соединены между собой. Например, Person может adopt (усыновить) Dog, или Student может enroll in (записаться на) Course

Изобразив всё это на диаграмме, вы можете чётко увидеть, какие данные хранятся и как сущности связаны друг с другом. Это облегчает правильное проектирование таблиц в базе данных

Схемы

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

Вместо линий, соединяющих блоки, схема использует первичные ключи и внешние ключи:

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

Хотя ER-диаграмма более наглядна и концептуальна, схема более конкретна и ближе к реальной реализации в СУБД. На протяжении всего руководства вы будете встречать оба варианта по мере перехода от планирования к написанию SQL

Попробуйте сами: изучите реляционную базу данных в действии

Чтобы увидеть, как эти концепции работают на практике, ознакомьтесь со следующими тремя воспроизведениями кода. Они проводят через проектирование реляционной базы данных для вымышленного центра усыновления животных Paw Prints. Эти примеры помогут вам понять, как сущности, связи и схемы объединяются в реальной базе данных, и как писать простые SQL-запросы для изучения этих данных

Начните с первого воспроизведения и пройдите все три по порядку:

• Database Design and Simple SQL — знакомит с базой данных Paw Prints и показывает, как писать базовые SQL-запросы.
• One-to-Many Relationships and More SQL — охватывает связи «один ко многим» и способы объединения связанных таблиц.
• Many-to-Many Relationships and Even More SQL — показывает, как обрабатывать связи «многие ко многим» с помощью таблиц-связок и более сложных запросов.

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

Часть 2: Справочник по SQL для начинающих

Этот раздел более подробно рассматривает основные команды SQL, представленные в стремительном обзоре. Каждое воспроизведение посвящено одной теме и показывает, как её использовать, на пошаговых примерах. В этих примерах я продолжаю использовать базу данных Paw Prints

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

Вот ключевые концепции, которые рассматриваются в этом разделе:

CREATE TABLE, ALTER TABLE — как определять таблицы в реляционной базе данных. Показано, как создавать таблицы с нуля и как изменять их впоследствии с помощью ALTER TABLE

INSERT — как добавлять новые строки данных в таблицу. Показано, как использовать команду INSERT и убедиться, что ваши данные соответствуют структуре таблицы

SELECT — знакомство с ключевым словом SELECT в SQL. Вы узнаете, как извлекать определённые столбцы из таблицы и увидите, как выглядит результирующий набор

FROM и JOIN — как предложение FROM указывает, из каких таблиц берутся ваши данные. Это закладывает основу для объединения данных из нескольких источников с помощью декартова произведения. Вы также увидите, как объединять таблицы с помощью JOIN

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

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

ORDER BY — сортировка результатов. Вы узнаете, как управлять порядком вывода, используя один или несколько атрибутов

GROUP BY, HAVING и агрегатные функции — группировка строк и вычисление сводных значений с помощью агрегатных функций, таких как COUNT, AVG, MIN, MAX и SUM. Также показано, как использовать GROUP BY и HAVING для работы с сгруппированными результатами

Вложенные запросы и CTE — как использовать вложенные запросы — запросы внутри других запросов — для построения более гибкой логики. Также показано, как писать более чистые запросы с помощью обобщённых табличных выражений (CTE)

UNION, INTERSECT, EXCEPT — как объединять результаты нескольких запросов. Показано, как эти операторы помогают работать с данными из разных запросов как с одним набором

Транзакции — как объединять несколько SQL-команд в одну транзакцию (transaction), чтобы они все либо выполнялись успешно, либо завершались неудачей вместе. Транзакции помогают защитить ваши данные от частичных обновлений

Индексы — как улучшать производительность запросов с помощью индексов. Показано, как создать индекс для одного или нескольких столбцов, и объясняется, почему это ускоряет выполнение определённых запросов

Часть 3: Практические задачи — базы данных Paw Prints и университета

Теперь пришло время применить то, что вы узнали

Ниже представлены шесть практических задач, использующих базу данных Paw Prints из предыдущих примеров. Если вы ещё не воссоздали её самостоятельно, вот ссылка на файл SQLite dogsFinal.sqlite. В каждой задаче задаётся конкретный вопрос, для ответа на который нужно написать SQL-запрос. Попробуйте решить каждую задачу самостоятельно, прежде чем смотреть решение

Не беспокойтесь, если с первого раза не получится. Написание SQL часто предполагает метод проб и ошибок, даже для опытных разработчиков. Цель — обдумать задачу и двигаться вперёд, а не добиться идеального результата с первой попытки. Начинайте с малого и выстраивайте запросы итеративно

• Какие собаки посещались чаще всего? — разберитесь, как подсчитать количество визитов для каждой собаки и отсортировать их, чтобы найти наиболее часто посещаемых.
• Количество усыновлений и средний возраст — найдите общее количество усыновлений и средний возраст усыновлённых собак. Вам потребуется соответствующим образом отфильтровать данные.
• Локации с наименее/наиболее агрессивными собаками — используйте группировку, чтобы сравнить уровни агрессии по локациям и определить, где содержатся наиболее и наименее агрессивные собаки.
• Среднее время до усыновления по локациям — вычислите среднее время, необходимое для усыновления собак, с разбивкой по локациям.
• Поиск доступной вместимости в каждой локации — определите, сколько места осталось в каждом приюте, сравнив общую вместимость с текущей заполненностью.
• Кто посетил, а затем усыновил агрессивную собаку — этот сложный запрос требует отслеживания действий пользователей во времени: сначала визит, затем усыновление агрессивной собаки. Хорошее испытание!

База данных университета

Далее вы будете работать с более сложной базой данных, моделирующей систему курсов и оценок университета. Вы будете использовать её для анализа реальных связей между студентами, преподавателями, курсами и кафедрами

Скачайте версию базы данных в формате SQLite: studentGrades.sqlite

Сущности в базе данных:

• Студенты (Students)
• Секции (Sections)
• Курсы (Courses)
• Преподаватели (Professors)
• Кафедры (Departments)

Связи между ними:

• Каждый студент посещает ноль или более секций (каждый студент получает оценку)
• Каждая секция имеет ноль или более студентов, её посещающих
• Каждая секция является экземпляром курса
• Каждый курс имеет ноль или более секций
• Каждая секция ведётся одним преподавателем
• Каждый преподаватель ведёт ноль или более секций
• Каждый преподаватель принадлежит к нулю или более кафедрам
• Каждая кафедра имеет ноль или более преподавателей
• Каждая кафедра имеет не более одного преподавателя, являющегося её заведующим
• Каждый преподаватель может возглавлять не более одной кафедры
• Каждый курс предлагается кафедрой
• Каждая кафедра предлагает ноль или более курсов

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

• Перечислите название каждого курса, номер секции и имя преподавателя в хронологическом порядке для каждой секции, которая когда-либо предлагалась
• Перечислите название каждого курса и номер секции для каждого курса, предлагаемого кафедрой информатики
• Найдите имя каждого преподавателя, который когда-либо вёл курс CSCI111
• Перечислите все имена преподавателей и их кафедры
• Перечислите имена преподавателей, которые вели как CSCI111, так и CSCI112
• Перечислите имена всех студентов преподавателя Марка Махони, которым 21 год или больше
• Перечислите имена всех студентов, которых обучает заведующий кафедрой
• Перечислите все названия курсов и номера секций каждого курса, когда-либо преподававшегося заведующим кафедрой
• Перечислите все курсы с самым старшим студентом
• Перечислите все курсы и номера секций с наименьшим средним возрастом студентов
• Перечислите все названия курсов и номера секций курсов с менее чем четырьмя кредитами
• Перечислите все названия курсов и номера секций с наименьшим числом записавшихся
• Перечислите имена всех студентов, которые прошли более одного курса с Марком Махони
• Перечислите имена всех студентов, которые прошли курс как с Марком Махони, так и с Эриком Уэндоном
• Перечислите все названия курсов и номера секций, в которых двое или более студентов получили оценку A
• Найдите имена всех студентов, которые прошли курс CSCI111
• Найдите имена всех преподавателей кафедры информатики, которые не являются заведующими кафедрой
• Найдите имена всех преподавателей, которые являются заведующими кафедрой
• Найдите номер социального страхования (ssn), имя и фамилию, название курса и полученную оценку для всех курсов, пройденных весной 2007 года
• Найдите название курса и номер секции всех курсов, которые когда-либо предлагались осенью
• Найдите имена всех преподавателей, ведущих занятия весной 2007 года
• Найдите имена всех студентов, получивших оценку A и оценку B по любому курсу
• Узнайте, сколько студентов когда-либо проходили курс CSCI111
• Найдите средний возраст всех студентов, у которых когда-либо был курс с Марком Махони
• Найдите имена всех преподавателей, которые никогда не вели курс
• Найдите имена всех преподавателей, которые вели занятия у Мэй Джонс
• Найдите имена студентов, у которых был курс осенью 2006 года или весной 2007 года
• Найдите имена студентов, которые прошли курс у преподавателя, имеющего более одного назначения на кафедру
• Найдите средний возраст студентов, прошедших курсы весной 2007 года
• Найдите сумму всех кредитных часов, предлагаемых кафедрой информатики в 2007 году

Часть 4: Использование SQLite в программах

До этого момента вы научились писать SQL-запросы и проектировать реляционные базы данных. В этом необязательном разделе вы увидите, как использовать SQLite в реальном коде. Каждое воспроизведение демонстрирует работающую программу, которая читает данные из базы данных SQLite и записывает их в неё

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

Воспроизведения показывают, как встраивать SQL непосредственно в ваш код, чтобы программы могли хранить и извлекать данные в рамках своего обычного рабочего процесса

C/C++

• The C++ SQLite API — как настроить и использовать SQLite C++ API. Вы откроете базу данных, выполните базовые запросы и обработаете результаты.
• An Object-Oriented Auction Program — создаёт более сложную программу на C++, которая отслеживает ставки, лоты и пользователей. Этот пример показывает, как структурировать реальное приложение с использованием SQLite.
• SQLite Transactions — объясняет, как объединить несколько операций с базой данных в одну транзакцию. Это защищает ваши данные от частичных обновлений.

Python и Flask

• Querying a SQLite Database — подключает Python-скрипт к базе данных SQLite, выполняет запросы и обрабатывает результаты с помощью встроенных библиотек Python.
• Creating SQLite Databases — показывает, как создавать новые базы данных SQLite непосредственно из Python, определяя таблицы и вставляя начальные данные.
• Flask Basics — знакомит с Flask. Вы увидите, как создать простое веб-приложение, которое может отдавать страницы и подключаться к вашей базе данных SQLite.
• Creating an API — развивает тему Flask, создавая RESTful API, взаимодействующий с базой данных SQLite и позволяющий выполнять CRUD-операции.

Java

• Using a SQLite Database in a Java Program — показывает, как интегрировать драйвер SQLite в Java-приложение. Вы узнаете, как открыть соединение, выполнять запросы и правильно обрабатывать исключения.

Типичные ошибки при изучении SQL

Даже когда структура понятна, начинающие регулярно наступают на одни и те же грабли. Вот несколько ситуаций, которые стоит держать в голове

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

Путаница между HAVING и WHERE — WHERE фильтрует строки до группировки, HAVING — после. Если вы пытаетесь отфильтровать результат агрегатной функции вроде COUNT, вам нужен HAVING

Декартово произведение вместо JOIN — если вы перечислите несколько таблиц в FROM без условия объединения, вы получите все возможные комбинации строк. Всегда явно указывайте условие JOIN или связывайте таблицы через WHERE

Игнорирование ER-диаграммы перед написанием запроса — сложные запросы к незнакомой базе данных гораздо проще строить, когда перед глазами есть схема. Я всегда начинаю с диаграммы, а не с кода

Попытка написать весь запрос сразу — гораздо эффективнее строить запрос итеративно: сначала SELECT и FROM, затем добавить WHERE, потом JOIN, и так далее. Каждый промежуточный результат можно проверить

К этому моменту вы научились читать и писать настоящий SQL. Вы увидели, как проектировать базу данных, как писать запросы для извлечения и изменения данных, и как решать всё более сложные задачи с помощью инструментов, которые предоставляет SQL

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

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

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

Ответы на эти вопросы могут быть для вас полезными

Нужно ли устанавливать сервер, чтобы начать работать с SQL? Нет. SQLite хранит всю базу данных в одном файле и не требует отдельного сервера. Для начала достаточно скачать DB Browser for SQLite или воспользоваться веб-инструментом SQLite Online прямо в браузере

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

Чем HAVING отличается от WHERE? WHERE фильтрует строки до группировки данных. HAVING фильтрует уже сгруппированные результаты — например, позволяет оставить только те группы, в которых COUNT превышает определённое значение. Использовать агрегатные функции в WHERE нельзя

Что такое обобщённое табличное выражение (CTE) и зачем оно нужно? CTE — это именованный подзапрос, который определяется в начале запроса с помощью ключевого слова WITH. Он делает сложные запросы более читаемыми, позволяя разбить логику на именованные шаги вместо вложенных подзапросов

Как лучше всего практиковаться в написании SQL-запросов? Начните с малого: берите одну таблицу, пишите простой SELECT, затем добавляйте условия и объединения по одному. Используйте реальные базы данных из этого руководства — dogsFinal.sqlite и studentGrades.sqlite — и пробуйте решить каждую задачу самостоятельно до просмотра решения. Борьба с задачей учит гораздо эффективнее, чем наблюдение за готовым ответом