Основы SQL и реляционных баз данных: подготовка к экзамену

1. Основы реляционных баз данных

Структура и логика реляционных баз данных: от теории к первым запросам

Представьте, что вы управляете крупным университетом. У вас есть информация о 15 000 студентов, 500 преподавателях, расписании занятий, оценках и стипендиях. Если попытаться хранить все эти данные в обычных текстовых файлах или даже в огромных таблицах электронных таблиц, вы быстро столкнетесь с хаосом. Поиск нужной информации будет занимать часы, а при обновлении данных неизбежно возникнут ошибки.

Именно для решения проблемы надежного хранения, быстрого поиска и безопасного изменения огромных массивов информации были созданы специализированные системы.

> Данные — это новая нефть. Но, как и нефть, они бесполезны, пока не очищены и не структурированы. > > Wired

Для работы с информацией в IT-индустрии используются базы данных (БД) — структурированные хранилища информации. Однако сама по себе база данных — это просто набор файлов на жестком диске. Чтобы с этими файлами можно было безопасно и быстро работать, используется Система управления базами данных (СУБД) — сложный программный комплекс, который выступает посредником между пользователем и физическими файлами.

Анатомия реляционной модели

Исторически существовало множество способов организации данных: иерархические (в виде дерева), сетевые (в виде графа). Но стандартом де-факто стала реляционная модель.

Слово реляционный происходит от английского термина relation (отношение). В математике и теории баз данных под «отношением» понимается то, что мы в быту называем обычной двумерной таблицей.

Реляционная база данных состоит из множества взаимосвязанных таблиц. Каждая таблица имеет строгую структуру:

* Столбец (атрибут или поле) — описывает одно конкретное свойство объекта. Например, в таблице студентов могут быть столбцы «Имя», «Фамилия», «Год поступления». * Строка (кортеж или запись) — содержит информацию об одном конкретном экземпляре объекта. Одна строка — это один конкретный студент со всеми его характеристиками.

Как таблицы связываются между собой

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

Для организации таких связей используются ключи:

Первичный ключ (Primary Key) — уникальный идентификатор записи в таблице. Это может быть номер паспорта, но чаще всего это просто порядковый номер (ID). Двух строк с одинаковым первичным ключом быть не может.

Внешний ключ (Foreign Key) — столбец в одной таблице, который ссылается на первичный ключ в другой таблице.

Пример с числами: в таблице Faculties факультет «Информатика» имеет ID = 5. В таблице Students у студента Ивана в столбце faculty_id (внешний ключ) будет стоять число 5. СУБД понимает эту связь и по запросу может объединить данные.

Язык SQL: инструмент общения с СУБД

Для управления реляционными базами данных используется язык SQL (Structured Query Language — язык структурированных запросов). Это декларативный язык. В отличие от классического программирования, где вы пишете, как получить результат (шаг за шагом), в SQL вы описываете, что хотите получить, а СУБД сама решает, как это сделать наиболее эффективно.

Команды SQL делятся на несколько логических групп. Для успешной сдачи экзамена критически важно различать две основные группы: DDL и DML.

DDL: Проектирование структуры (создание и изменение таблиц)

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

Создание таблицы (CREATE)

Допустим, нам нужно создать таблицу для хранения информации о книгах в библиотеке.

Разберем логику этого запроса: * CREATE TABLE books — команда СУБД создать новую таблицу с именем books. * Внутри скобок перечисляются столбцы и их типы данных. * INT означает целое число. Приписка PRIMARY KEY указывает, что book_id — это уникальный идентификатор. * VARCHAR(100) — текстовая строка переменной длины, максимум 100 символов. * DECIMAL(8, 2) — точное дробное число, где всего 8 цифр, из которых 2 находятся после запятой (идеально для хранения денег, например, 1500.50 руб.).

Изменение структуры (ALTER)

Жизнь меняется, и требования к базе данных тоже. Представьте, что через год работы библиотеки руководство решило выдавать книгам рейтинг. Нам нужно добавить новый столбец в уже существующую таблицу. Для этого используется команда ALTER.

Эта команда аккуратно добавит новый столбец rating ко всем существующим строкам, заполнив их пустотой (специальным значением NULL, которое означает отсутствие данных), не повредив при этом уже сохраненную информацию.

DML: Манипулирование данными

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

Добавление данных (INSERT)

Чтобы добавить новую книгу в нашу таблицу, мы используем оператор INSERT INTO.

Здесь мы сначала указываем, в какие именно столбцы хотим вставить данные, а затем через ключевое слово VALUES передаем сами значения в строгом соответствии с порядком столбцов. Текст обязательно берется в одинарные кавычки, а числа пишутся без них.

Чтение данных (SELECT)

Команда SELECT — абсолютный чемпион по частоте использования. Она позволяет извлекать данные, фильтровать их и производить вычисления.

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

Сначала выполняется фаза FROM books. СУБД находит нужную таблицу на диске.

Затем применяется фильтр WHERE publish_year >= 2020. СУБД отбрасывает все книги, изданные до 2020 года. В математическом смысле здесь проверяется условие , где — год издания конкретной строки.

Только в самом конце выполняется SELECT title, price. СУБД «отрезает» лишние столбцы (например, автора и ID) и возвращает пользователю только названия и цены.

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

Если цена книги изменилась, нам нужно обновить существующую запись.

Важное правило безопасности: оператор UPDATE почти всегда должен сопровождаться условием WHERE. Если вы напишете просто UPDATE books SET price = 1500.00;, СУБД послушно установит эту цену абсолютно для всех книг в библиотеке, что приведет к катастрофе.

Удаление данных (DELETE)

Когда книга списана, ее нужно удалить из базы.

Как и в случае с обновлением, отсутствие WHERE приведет к полному удалению всех строк из таблицы. Сама таблица (ее структура) при этом останется, но будет пустой.

Итоги

* Реляционная база данных хранит информацию в виде двумерных таблиц (отношений), состоящих из строк (записей) и столбцов (атрибутов). * Связь между таблицами обеспечивается за счет совпадения значений Первичного ключа (Primary Key) одной таблицы и Внешнего ключа (Foreign Key) другой. * Язык SQL делится на подмножества: DDL отвечает за структуру (создание и изменение таблиц), а DML — за сами данные (добавление, чтение, обновление, удаление). * Логический порядок обработки запроса SELECT начинается с определения источника данных (FROM), затем идет фильтрация строк (WHERE), и лишь затем выборка нужных столбцов (SELECT).

2. Создание и изменение структуры таблиц (DDL)

Создание и изменение структуры таблиц (DDL)

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

В мире реляционных баз данных этот архитектурный этап реализуется с помощью DDL (Data Definition Language — язык определения данных). Это подмножество команд SQL, которое отвечает за создание, изменение и удаление структур базы данных: таблиц, столбцов и связей между ними.

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

> Правильное проектирование базы данных на этапе DDL экономит тысячи часов отладки на этапе DML. > > sql-tutorial.ru

Фундамент: типы данных

Прежде чем создавать таблицу, необходимо строго определить, какая именно информация будет храниться в каждом ее столбце. Реляционные базы данных используют строгую типизацию. Это означает, что если вы выделили столбец под числа, СУБД физически не позволит записать туда текст.

Выбор правильного типа данных влияет на скорость работы базы и объем занимаемого места на жестком диске. Рассмотрим основные типы, которые чаще всего встречаются в экзаменационных задачах:

INT (или INTEGER*): Целые числа. Используется для идентификаторов, количества предметов, годов. Например, количество страниц в книге — 350. * VARCHAR(n): Текстовая строка переменной длины, где — максимальное количество символов. Если вы зададите VARCHAR(50) для фамилии, а запишете туда «Иванов» (6 символов), база данных займет ровно столько места, сколько нужно для 6 символов, а не для 50. DECIMAL(p, s) (или NUMERIC*): Точные дробные числа. Здесь — общее количество цифр, а — количество цифр после запятой. Если стипендия студента составляет 15500.50 руб., идеальным выбором будет DECIMAL(7, 2). * DATE: Календарная дата в формате «Год-Месяц-День».

Правила дорожного движения для данных: ограничения (Constraints)

База данных должна защищать себя от логических ошибок. Для этого на этапе создания таблиц прописываются ограничения целостности (constraints) — строгие правила, которым должны подчиняться данные.

PRIMARY KEY (Первичный ключ): Уникальный идентификатор строки. Гарантирует, что в таблице не будет двух абсолютно одинаковых записей. Автоматически запрещает пустые значения.

FOREIGN KEY (Внешний ключ): Обеспечивает связь между таблицами. Гарантирует, что вы не сможете сослаться на несуществующий объект.

NOT NULL: Запрещает оставлять ячейку пустой. Например, у студента обязательно должна быть фамилия.

UNIQUE: Гарантирует уникальность значений в столбце, но, в отличие от первичного ключа, таких столбцов в таблице может быть несколько (например, номер паспорта и адрес электронной почты).

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

Возведение стен: команда CREATE TABLE

Теперь, зная типы данных и ограничения, мы можем написать наш первый DDL-запрос. Создадим структуру для хранения информации о факультетах и студентах.

В реляционных базах данных порядок создания таблиц имеет значение. Сначала мы должны создать независимую таблицу (факультеты), и только потом — зависимую (студенты), которая будет на нее ссылаться.

Здесь мы создали таблицу faculties. Столбец faculty_id стал первичным ключом, а название факультета не может быть пустым и не может повторяться.

Теперь создадим таблицу студентов:

Разберем логику связи: строка FOREIGN KEY (faculty_id) REFERENCES faculties(faculty_id) — это сердце реляционной модели. Она говорит СУБД: «Следи за тем, чтобы любое число, которое попытаются записать в столбец faculty_id таблицы students, обязательно уже существовало в столбце faculty_id таблицы faculties». Если кто-то попытается зачислить студента на факультет с номером 999, которого нет в базе, СУБД выдаст ошибку и заблокирует операцию.

Перепланировка: команда ALTER TABLE

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

Нам не нужно удалять таблицу и создавать ее заново (что привело бы к потере всех данных). Мы используем команду ALTER TABLE, которая позволяет изменять структуру «на лету».

Добавление нового столбца:

Эта команда добавит столбец email ко всем существующим строкам. Поскольку старые студенты еще не предоставили свои адреса, СУБД заполнит эти ячейки специальным маркером пустоты — NULL.

Если какой-то столбец больше не нужен (например, год поступления решили перенести в другую систему), его можно удалить:

Снос здания: команды DROP и TRUNCATE

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

Команда DROP TABLE полностью уничтожает таблицу. Удаляются и все строки внутри нее, и сама структура (столбцы, типы данных, ограничения). Таблица стирается с жесткого диска.

Иногда возникает другая задача: нужно быстро удалить абсолютно все данные из таблицы, но саму пустую структуру оставить для будущих записей. Для этого используется команда TRUNCATE TABLE. Хотя она удаляет данные, в большинстве СУБД она относится к группе DDL, так как работает на уровне пересоздания файлов таблицы, а не построчного удаления.

Для наглядности сравним эти команды с оператором DELETE (который относится к DML и будет подробно разобран в следующих статьях):

| Оператор | Группа | Что делает | Оставляет ли структуру? | | :--- | :--- | :--- | :--- | | DROP TABLE | DDL | Уничтожает таблицу полностью | Нет | | TRUNCATE TABLE | DDL | Мгновенно очищает все строки | Да | | DELETE | DML | Удаляет строки по заданному условию | Да |

Важно помнить о реляционных связях: вы не сможете сделать DROP TABLE faculties, пока существует таблица students, ссылающаяся на нее внешним ключом. СУБД защищает вас от создания «висячих» ссылок. Сначала нужно удалить зависимую таблицу, а затем — главную.

Итоги

* DDL (Data Definition Language) — это набор команд SQL для проектирования структуры базы данных, а не для работы с самими данными. * При создании таблицы (CREATE TABLE) каждому столбцу жестко назначается тип данных (INT, VARCHAR, DECIMAL, DATE), который определяет формат хранимой информации. * Ограничения целостности (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) выступают в роли строгих правил, защищающих базу от логически некорректных данных. * Структуру существующей таблицы можно гибко изменять с помощью команды ALTER TABLE (добавлять или удалять столбцы). * Команда DROP TABLE полностью уничтожает таблицу вместе со структурой, тогда как TRUNCATE TABLE лишь мгновенно очищает ее от данных, оставляя «каркас» нетронутым.