Основы SQL и проектирование баз данных

Введение в реляционные базы данных и основные понятия SQL

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

Зачем нам нужны базы данных?

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

Обычные текстовые файлы или электронные таблицы перестают справляться, когда:

* Объем данных растет. Excel начинает «тормозить» на сотнях тысяч строк. * Нужен многопользовательский доступ. Если два человека одновременно попытаются отредактировать один файл, возникнет конфликт версий. * Требуется безопасность. В файле сложно разграничить права: кому-то можно только читать, а кому-то — удалять записи. * Важна целостность. Нужно гарантировать, что в поле «Год издания» записано число, а не слово «вчера».

Здесь на сцену выходят Базы Данных (БД).

> База данных — это организованная коллекция структурированной информации или данных, обычно хранящаяся в электронном виде в компьютерной системе.

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

!Схема взаимодействия: Пользователь — СУБД — База Данных

Реляционные базы данных

Существует множество типов баз данных, но самыми популярными уже более 50 лет остаются реляционные базы данных (от англ. relation — отношение, связь). Идея была предложена Эдгаром Коддом в 1970 году.

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

Анатомия таблицы

Давайте разберем структуру таблицы на примере списка пользователей интернет-магазина. Назовем эту таблицу Users.

| ID | FirstName | LastName | Email | Age | | :--- | :--- | :--- | :--- | :--- | | 1 | Иван | Петров | ivan@mail.ru | 25 | | 2 | Анна | Сидорова | anna@gmail.com | 30 | | 3 | Дмитрий | Волков | volkov@yandex.ru | 22 |

Основные элементы:

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

Столбец (Column) или Поле (Field): Вертикальная часть таблицы. Каждый столбец имеет имя и определенный тип данных. Например, в столбце Age могут быть только числа, а в Email — строки.

Строка (Row) или Запись (Record): Горизонтальная часть таблицы. Одна строка содержит информацию об одном конкретном объекте. Например, вторая строка — это вся информация об Анне Сидоровой.

Первичный ключ (Primary Key): Уникальный идентификатор записи. В нашем примере это столбец ID. Он гарантирует, что мы сможем отличить одного Ивана Петрова от другого, если у них совпадут имена.

Математическая основа (для любознательных)

Реляционная модель базируется на теории множеств. Формально отношение (таблица) определяется как подмножество декартова произведения доменов (типов данных столбцов).

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

Простыми словами: таблица состоит из строк, где каждая ячейка берет значение из своего допустимого списка значений (домена).

Что такое SQL?

Мы поняли, как данные хранятся. Но как нам «поговорить» с базой данных? Как попросить её: «Покажи мне всех пользователей старше 25 лет»?

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

Важно понимать: SQL — это не база данных. Это язык, на котором вы отдаете приказы СУБД.

Аналогия с рестораном

Чтобы лучше понять роль SQL, представим ресторан:

* Вы — это пользователь или приложение. * Меню — это структура базы данных (список доступных таблиц и полей). * Продукты на складе — это сами данные. * Официант — это SQL. Вы не идете на кухню сами. Вы говорите официанту (пишете SQL-запрос): «Принесите мне стейк». * Повар — это СУБД (например, PostgreSQL, MySQL). Повар получает заказ от официанта, знает, где лежат продукты, как их обработать, и выдает готовое блюдо.

!Аналогия работы SQL: Клиент делает заказ через Официанта (SQL), а Повар (СУБД) выполняет работу

Основные категории команд SQL

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

1. DQL (Data Query Language) — Язык запросов данных

Самая частая операция — чтение данных. Для этого используется всего одна, но очень мощная команда:

* SELECT — выбрать данные из таблицы.

Пример (найти всех пользователей):

2. DML (Data Manipulation Language) — Язык манипулирования данными

Эти команды меняют содержимое таблиц:

* INSERT — добавить новую строку. * UPDATE — изменить существующие данные. * DELETE — удалить строку.

3. DDL (Data Definition Language) — Язык определения данных

Эти команды создают и меняют саму структуру (скелет) базы данных:

* CREATE — создать таблицу или базу данных. * ALTER — изменить структуру таблицы (например, добавить столбец). * DROP — удалить таблицу полностью.

Почему SQL так популярен?

SQL появился в 1970-х годах и до сих пор является стандартом индустрии. Почему его не заменили?

Универсальность. Зная SQL, вы сможете работать с разными СУБД: PostgreSQL, MySQL, Oracle, Microsoft SQL Server, SQLite. Синтаксис может немного отличаться (диалекты), но основа везде одна.

Декларативность. В SQL вы описываете, что хотите получить, а не как это сделать. Вы говорите: «Найди мне пользователей», а не «Возьми первую строку, проверь её, потом вторую...». СУБД сама решает, как выполнить задачу оптимально.

Мощь. SQL позволяет одной строкой кода обрабатывать миллионы записей, фильтровать их, сортировать и объединять данные из разных таблиц.

Заключение

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

Для тех, кто хочет углубиться в историю создания реляционной модели, рекомендую ознакомиться с оригинальной работой Эдгара Кодда: A Relational Model of Data for Large Shared Data Banks (ссылка на архив ACM).

Написание запросов выборки данных: SELECT, фильтрация и сортировка

Мы продолжаем наш курс «Основы SQL и проектирование баз данных». В прошлой статье мы познакомились с концепцией реляционных баз данных и выяснили, что SQL — это язык-посредник, позволяющий нам общаться с данными. Если использовать нашу аналогию с рестораном, то сегодня мы научимся не просто просить меню, а делать конкретный заказ: «Принесите мне, пожалуйста, только десерты, дешевле 500 рублей, и подайте их от самых дорогих к самым дешевым».

Сегодняшняя тема — фундамент работы любого аналитика или разработчика. Мы разберем команду SELECT, научимся отсеивать лишнее с помощью WHERE и упорядочивать хаос с помощью ORDER BY.

Сердце SQL: Команда SELECT

Любой запрос на получение данных начинается с ключевого слова SELECT (выбрать). Это декларативная команда: вы говорите базе данных, какие столбцы хотите увидеть.

Самый простой запрос выглядит так:

Здесь: * SELECT — команда «выбери». (звездочка) — специальный символ, означающий «все столбцы». * FROM Products — указывает источник данных (таблицу «Товары»).

Этот запрос вернет полную копию таблицы Products. Однако в реальной работе таблицы могут содержать сотни столбцов и миллионы строк. Запрашивать всё сразу (SELECT *) — это дурной тон, так как это создает огромную нагрузку на сеть и сервер.

Выбор конкретных столбцов

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

В результате вы получите таблицу, где будут только два столбца: название товара и его цена. Остальная информация (например, количество на складе или дата поставки) будет проигнорирована.

!Визуализация проекции данных: выбор конкретных столбцов из исходной таблицы.

Фильтрация данных: Команда WHERE

Чаще всего нам не нужна вся таблица, а только определенные строки. Например, только товары категории «Электроника». Для этого используется ключевое слово WHERE (где).

Синтаксис:

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

Математическая логика фильтрации

С точки зрения теории множеств, операция фильтрации (селекции) описывается следующей формулой:

Где: * (сигма) — оператор селекции (выборки). * — предикат (логическое условие, которое мы пишем в WHERE). * — отношение (исходная таблица). * — кортеж (строка таблицы). * — знак принадлежности (строка принадлежит таблице). * — читается как «такие, что». * — результат проверки условия для конкретной строки (Истина или Ложь).

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

Операторы сравнения

В SQL используются стандартные операторы для проверки условий:

| Оператор | Описание | Пример | | :--- | :--- | :--- | | = | Равно | Price = 100 | | <> или != | Не равно | Category <> 'Toys' | | > | Больше | Price > 500 | | < | Меньше | Stock < 10 | | >= | Больше или равно | Rating >= 4.5 | | <= | Меньше или равно | Price <= 1000 |

Важно: Строковые значения (текст) в SQL всегда заключаются в одинарные кавычки: 'Apple', 'Samsung'. Числа пишутся без кавычек.

Логические операторы: AND, OR, NOT

Жизнь редко бывает простой, и часто нам нужно проверить несколько условий одновременно. Например: «Найти смартфоны дешевле 20 000 рублей».

Для объединения условий используются логические операторы.

1. Оператор AND (И)

Требует, чтобы оба условия были истинными одновременно.

Если товар — смартфон, но стоит 25 000, он не попадет в выборку. Если товар стоит 10 000, но это пылесос — он тоже не попадет.

Формально это конъюнкция:

Где — первое условие, — логическое И, — второе условие. Результат истинен только если и , и истинны.

2. Оператор OR (ИЛИ)

Требует, чтобы истинным было хотя бы одно из условий.

Этот запрос вернет и все смартфоны, и все ноутбуки.

Формально это дизъюнкция:

Где — первое условие, — логическое ИЛИ, — второе условие. Результат истинен, если истинно , или , или оба сразу.

3. Оператор NOT (НЕ)

Инвертирует условие. Делает истину ложью, а ложь — истиной.

Запрос вернет все товары, кроме игрушек.

Приоритет операций

Как и в математике, у логических операторов есть приоритет. AND имеет более высокий приоритет, чем OR. Это частая ошибка новичков.

Рассмотрим запрос:

Как это прочитает база данных?

Сначала выполнится AND: найти ноутбуки дороже 50 000.

Потом выполнится OR: взять результат шага 1 и добавить к нему ВСЕ смартфоны (любой цены).

Если вы хотели найти и смартфоны, и ноутбуки, которые дороже 50 000, нужно использовать скобки:

Специальные операторы: IN, BETWEEN, LIKE

Чтобы код был чище и понятнее, SQL предлагает «синтаксический сахар» — упрощенные записи сложных условий.

IN (В списке)

Вместо того чтобы писать много OR, используйте IN.

Было:

Стало:

BETWEEN (Между)

Для проверки диапазона (включительно).

Было:

Стало:

LIKE (Похож на)

Используется для поиска по шаблону в строках. Здесь применяются спецсимволы: * % — любое количество любых символов. * _ — ровно один любой символ.

Пример: найти все товары, название которых начинается на "iPhone".

Сортировка данных: ORDER BY

Когда вы делаете SELECT без сортировки, база данных возвращает строки в хаотичном порядке (обычно в том, как они физически лежат на диске). Чтобы упорядочить данные, используем ORDER BY.

Эта команда всегда ставится после WHERE.

Направление сортировки

* ASC (Ascending) — по возрастанию (от А до Я, от 0 до 9). Это значение по умолчанию, его можно не писать. * DESC (Descending) — по убыванию (от Я до А, от 9 до 0).

Сортировка по нескольким столбцам

Можно сортировать сразу по нескольким критериям. Например, сначала по категории, а внутри категории — по цене (от дорогих к дешевым).

!Пример многоуровневой сортировки: сначала группировка по категориям, затем сортировка по цене внутри группы.

Ограничение количества строк: LIMIT

Иногда нам нужны не все данные, а только «топ-5» или «первые 10». Для этого используется LIMIT (в некоторых СУБД, например MS SQL Server, используется TOP).

Пример: Найти 3 самых дорогих товара.

Порядок выполнения запроса (Важно!)

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

Мы пишем:

SELECT

FROM

WHERE

ORDER BY

LIMIT

База данных выполняет:

FROM (Находит таблицу, берет данные).

WHERE (Фильтрует строки, отбрасывает лишнее).

SELECT (Оставляет только нужные столбцы).

ORDER BY (Сортирует оставшееся).

LIMIT (Обрезает результат).

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

Заключение

Сегодня мы освоили «грамматику» языка SQL. Мы научились:

Выбирать данные (SELECT).

Указывать источник (FROM).

Фильтровать ненужное (WHERE, AND, OR).

Сортировать результат (ORDER BY).

Ограничивать выборку (LIMIT).

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

Язык манипулирования данными (DML) и определение структуры данных (DDL)

Мы продолжаем наш курс «Основы SQL и проектирование баз данных». В предыдущих статьях мы научились «читать» данные: выбирать их, фильтровать и сортировать с помощью команды SELECT. Это, безусловно, самый частый сценарий работы аналитика. Однако, прежде чем данные можно будет прочитать, они должны как-то попасть в базу. Более того, сама база данных и таблицы в ней должны быть кем-то созданы.

Сегодня мы переходим на сторону «творцов» и «архитекторов». Мы разберем две фундаментальные группы команд SQL:

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

DML (Data Manipulation Language) — язык манипулирования данными. Это инструменты пользователя, позволяющие наполнять таблицы информацией, менять её и удалять.

Архитектура против Наполнения: DDL vs DML

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

DDL — это работа строительной бригады. Они возводят стены, планируют, где будет кухня, а где ванная, прокладывают трубы. Они создают структуру*. * DML — это жизнь жильцов. Они завозят мебель (INSERT), делают перестановку (UPDATE) или вывозят старый диван (DELETE).

!Слева показан процесс создания структуры (DDL), справа — процесс наполнения и изменения содержимого (DML).

Часть 1. DDL: Создаем скелет базы данных

Команды DDL меняют саму структуру базы данных, а не её содержимое. К ним относятся CREATE, ALTER и DROP.

1. CREATE TABLE — Рождение таблицы

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

Допустим, мы хотим создать таблицу студентов Students.

Разберем, что мы написали: * INT — целое число (для ID). * PRIMARY KEY — первичный ключ (уникальный идентификатор, обязательный для каждой записи). * VARCHAR(50) — строка переменной длины, максимум 50 символов. * DATE — дата. * DECIMAL(3, 2) — дробное число, где всего 3 цифры, из них 2 после запятой (например, 4.55).

2. ALTER TABLE — Перепланировка

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

Теперь в нашей таблице появился новый пустой столбец. Также с помощью ALTER можно удалять столбцы (DROP COLUMN) или менять их тип (ALTER COLUMN).

3. DROP TABLE — Снос здания

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

> Важно: Будьте предельно осторожны с командой DROP. В реальных проектах права на её выполнение есть только у старших администраторов баз данных.

Часть 2. DML: Управляем данными

Теперь, когда у нас есть таблица Students, она пуста. Давайте вдохнем в неё жизнь с помощью DML.

1. INSERT — Добавление данных

Команда INSERT добавляет новые строки в таблицу. С точки зрения теории множеств, это операция объединения множеств.

Математически добавление записи можно выразить так:

Где: * — новое состояние таблицы (отношения). * — старое состояние таблицы. * — оператор объединения множеств (добавляем элементы одного множества к другому). * — множество, состоящее из одной новой строки (кортежа) , которую мы добавляем.

Синтаксис SQL:

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

Можно добавить сразу несколько строк:

2. UPDATE — Обновление данных

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

Здесь: * UPDATE Students — указываем таблицу. * SET AverageScore = 4.75 — указываем, что и на что менять. * WHERE StudentID = 1 — самая важная часть. Условие, какую именно строку менять.

Внимание! Если вы забудете написать WHERE, СУБД послушно обновит средний балл всем студентам в таблице. Это классическая ошибка новичка, которая может стоить компании миллионов.

3. DELETE — Удаление данных

Дмитрий Сидоров решил забрать документы. Нам нужно удалить его запись из таблицы.

Математически удаление описывается как разность множеств:

Где: * — новое состояние таблицы. * — исходное состояние таблицы. * — оператор разности множеств (убираем элементы второго множества из первого). * — строка (кортеж). * — знак принадлежности (строка принадлежит таблице). * — читается как «такие, что». * — предикат (условие), которому удовлетворяет удаляемая строка (например, ID = 3).

Синтаксис SQL:

Как и в случае с UPDATE, если вы забудете WHERE, команда DELETE FROM Students очистит всю таблицу, оставив её пустой.

TRUNCATE — Гибрид DDL и DML

Существует еще одна команда для очистки таблицы — TRUNCATE TABLE Students. Она удаляет все строки, как и DELETE без WHERE, но делает это гораздо быстрее. Технически TRUNCATE относится к DDL, так как она пересоздает структуру хранения данных заново, сбрасывая счетчики автоинкремента, а не удаляет строки по одной.

Транзакции: Ваша страховка

Говоря о DML (изменении данных), нельзя не упомянуть понятие транзакции. Представьте, что вы переводите деньги с карты на карту. Операция состоит из двух шагов:

Списать деньги у вас.

Зачислить деньги другу.

Что будет, если свет выключится ровно посередине? Деньги спишутся, но не зачислятся. Чтобы этого избежать, SQL использует транзакции.

Транзакция — это группа последовательных операций, которая выполняется как единое целое. Либо всё успешно (COMMIT), либо, если произошла ошибка, всё отменяется (ROLLBACK), возвращаясь в исходное состояние.

Резюме

Сегодня мы разделили команды SQL на два лагеря:

DDL (CREATE, ALTER, DROP) — определяют структуру. Это «скелет» базы данных.

DML (INSERT, UPDATE, DELETE) — манипулируют данными. Это «плоть» базы данных.

Мы также усвоили золотое правило безопасности: никогда не запускать UPDATE или DELETE без предварительной проверки условия WHERE (лучше сначала написать SELECT с этим условием и убедиться, что вы выбрали нужные строки).

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

Для более глубокого погружения в стандарты типов данных, которые мы использовали в CREATE TABLE, рекомендую ознакомиться с документацией PostgreSQL: Типы данных.

Работа с несколькими таблицами: типы соединений JOIN и агрегатные функции

Добро пожаловать на четвертую часть курса «Основы SQL и проектирование баз данных». В предыдущих статьях мы научились создавать таблицы («скелет» базы), наполнять их данными («плоть») и делать простые выборки из одной таблицы.

Но сила реляционных баз данных не в том, чтобы хранить списки в изоляции, а в том, чтобы связывать их между собой. Представьте университет. У нас есть список студентов и список учебных групп. Хранить название группы в строке каждого студента — плохая идея (это называется избыточностью). Если группа переименуется, придется менять тысячи записей. Правильный подход — хранить группы в отдельной таблице, а у студентов указывать только ID группы.

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

Часть 1. Соединение таблиц (JOIN)

Операция соединения (JOIN) позволяет объединять строки из двух или более таблиц на основе связанного между ними столбца.

Давайте представим две таблицы:

Таблица Students (Студенты):

| StudentID | Name | GroupID | | :--- | :--- | :--- | | 1 | Иван | 101 | | 2 | Мария | 101 | | 3 | Петр | 102 | | 4 | Анна | NULL |

Таблица Groups (Группы):

| GroupID | GroupName | | :--- | :--- | | 101 | Физики | | 102 | Лирики | | 103 | Биологи |

Обратите внимание: у Анны нет группы (NULL), а у группы «Биологи» нет студентов.

Математическая суть соединения

В реляционной алгебре соединение — это подмножество декартова произведения двух отношений (таблиц), удовлетворяющее определенному условию.

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

Простыми словами: СУБД сначала мысленно соединяет всё со всем, а потом оставляет только те пары, где ключи совпадают.

!Визуализация типов соединений JOIN с помощью диаграмм Венна

1. INNER JOIN (Внутреннее соединение)

Самый частый тип соединения. Он возвращает только те строки, для которых нашлось совпадение в обеих таблицах.

Результат: * Иван — Физики * Мария — Физики * Петр — Лирики

Анна (без группы) и Биологи (без студентов) в результат не попадут. Это пересечение множеств.

2. LEFT JOIN (Левое соединение)

Возвращает все строки из левой таблицы (той, что указана первой, после FROM), и сопоставленные строки из правой. Если совпадения нет, вместо данных правой таблицы будет NULL.

Результат: * Иван — Физики * Мария — Физики * Петр — Лирики * Анна — NULL

Мы видим всех студентов, даже тех, кто пока не распределен по группам.

3. RIGHT JOIN (Правое соединение)

Зеркальное отражение LEFT JOIN. Возвращает все строки из правой таблицы и совпадения из левой.

Результат: * Иван — Физики * Мария — Физики * Петр — Лирики * NULL — Биологи

Мы видим все группы, даже пустые. Студентка Анна сюда не попадет.

4. FULL OUTER JOIN (Полное соединение)

Объединяет результаты левого и правого соединений. Возвращает строки, если есть совпадение хотя бы в одной из таблиц.

Результат: Все студенты и все группы. Где нет связи — будут NULL.

Часть 2. Агрегатные функции

Часто нам не нужны детальные данные («кто именно учится в группе»), а нужна статистика («сколько человек в группе» или «какой средний балл»).

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

Основные функции

COUNT(*) — считает количество строк.

SUM(Column) — считает сумму значений в столбце.

AVG(Column) — считает среднее арифметическое.

MIN(Column) — находит минимальное значение.

MAX(Column) — находит максимальное значение.

Пример: Узнаем средний балл всех студентов.

Математика среднего значения

Функция AVG (Average) вычисляет среднее арифметическое. Формально это выглядит так:

Где: * — среднее значение (результат функции AVG). * — количество записей (строк), участвующих в расчете (COUNT). * — знак суммирования. * и — пределы суммирования (от первой до последней строки). * — значение столбца в -й строке.

Простыми словами: мы складываем все оценки и делим на количество студентов.

Часть 3. Группировка данных (GROUP BY)

Агрегатные функции становятся по-настоящему мощными, когда мы применяем их не ко всей таблице сразу, а к отдельным группам строк. Для этого используется оператор GROUP BY.

Задача: Посчитать количество студентов в каждой группе.

Как это работает:

СУБД берет таблицу Students.

Сортирует или разбивает строки на «кучки» по значению GroupID.

Для каждой «кучки» отдельно запускает функцию COUNT(*).

Возвращает результат для каждой группы.

!Принцип работы группировки данных: разделение на группы и агрегация внутри каждой группы

Фильтрация после группировки: HAVING

Это один из самых сложных моментов для новичков. В чем разница между WHERE и HAVING?

* WHERE фильтрует строки ДО группировки. (Отсеиваем студентов, которых отчислили, перед подсчетом). * HAVING фильтрует результаты ПОСЛЕ группировки. (Оставляем только те группы, где больше 5 студентов).

Пример: Найти группы, в которых средний балл выше 4.0.

Если бы мы написали это условие в WHERE, база данных выдала бы ошибку, потому что на этапе WHERE она еще не знает среднего балла группы — он еще не посчитан.

Порядок выполнения запроса (Обновленный)

Теперь наш список порядка выполнения команд (не написания, а именно выполнения СУБД) стал полным:

FROM + JOIN (Собрать данные из таблиц).

WHERE (Отфильтровать исходные строки).

GROUP BY (Сгруппировать оставшиеся строки).

HAVING (Отфильтровать группы).

SELECT (Вычислить выражения и выбрать столбцы).

ORDER BY (Отсортировать результат).

LIMIT (Обрезать количество строк).

Заключение

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

* JOIN — инструмент для сборки данных из разных таблиц. * Агрегатные функции — математический аппарат для анализа данных. * GROUP BY и HAVING — способы сегментировать данные и анализировать каждый сегмент отдельно.

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

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

Проектирование баз данных, нормализация и основы использования индексов

Мы продолжаем наш курс «Основы SQL и проектирование баз данных». В предыдущих статьях мы прошли путь от создания простых таблиц до написания сложных запросов с использованием JOIN и агрегатных функций. Теперь вы умеете манипулировать данными, как опытный пользователь.

Однако, прежде чем писать SELECT или INSERT, базу данных нужно спроектировать. Если фундамент здания заложен криво, то стены (ваши запросы) рано или поздно треснут. Плохая архитектура приводит к дублированию данных, ошибкам при обновлении и медленной работе системы.

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

Что такое хорошее проектирование?

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

С какими проблемами вы столкнетесь?

Избыточность. Если Иван Петров сделал 10 заказов, его адрес и телефон будут повторены 10 раз. Это тратит память.

Аномалия обновления. Если Иван переедет, вам придется найти все 10 строк и исправить адрес в каждой. Если пропустите хоть одну — данные станут противоречивыми.

Аномалия удаления. Если Иван отменит все заказы, и вы удалите эти строки, вы потеряете его контакты навсегда. Вместе с заказами исчезнет и сам клиент.

Чтобы избежать этого, используется процесс нормализации.

Нормализация баз данных

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

Существует несколько уровней «нормальности», которые называются Нормальными Формами (НФ). Мы разберем три основные, которых достаточно для 99% задач.

Первая нормальная форма (1НФ)

Таблица находится в 1НФ, если:

Все атрибуты (поля) атомарны (неделимы).

В таблице нет повторяющихся групп столбцов.

Пример нарушения: В поле Товары записано: «iPhone 13, Чехол, Зарядка». Это не атомарное значение. SQL не сможет легко найти всех, кто купил «Чехол», так как это слово спрятано внутри строки.

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

Вторая нормальная форма (2НФ)

Таблица находится во 2НФ, если:

Она уже находится в 1НФ.

У нее есть первичный ключ.

Все неключевые столбцы зависят от полного первичного ключа, а не от его части.

Это правило актуально для таблиц, где первичный ключ составной (состоит из двух и более полей).

Пример нарушения: Таблица OrderItems (Состав заказа) с ключом (OrderID, ProductID). Поля: OrderID, ProductID, Quantity (количество), ProductName (название товара).

Здесь Quantity зависит от обоих ключей (сколько конкретного товара в конкретном заказе). А вот ProductName зависит только от ProductID. Название товара не меняется от того, в каком заказе он находится.

Как исправить: Вынести ProductName в отдельную таблицу Products. В таблице OrderItems оставить только ID.

Третья нормальная форма (3НФ)

Таблица находится в 3НФ, если:

Она уже находится во 2НФ.

В ней нет транзитивных зависимостей.

Транзитивная зависимость — это когда неключевое поле зависит от другого неключевого поля.

Формально это можно записать так:

Где — это первичный ключ, — неключевое поле, зависящее от ключа, а — поле, зависящее от . В 3НФ поле должно быть удалено из этой таблицы.

Пример нарушения: Таблица Orders (Заказы). Поля: OrderID (Ключ), CustomerID, CustomerCity.

Здесь CustomerCity (Город) зависит от CustomerID (Клиента), а CustomerID зависит от OrderID (Заказа). Получается цепочка: Заказ -> Клиент -> Город. Город относится к заказу только опосредованно, через клиента.

Как исправить: Оставить в таблице Orders только CustomerID. Информацию о городе (CustomerCity) перенести в таблицу Customers.

!Визуализация процесса перехода от ненормализованной таблицы к структуре в 3НФ

Индексы: Ускоряем работу базы данных

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

Аналогия с книгой

Представьте, что вам нужно найти в учебнике по истории все упоминания «Наполеона».

* Без индекса (Full Table Scan): Вы открываете первую страницу и читаете книгу до конца, ища глазами нужное слово. Если в книге 1000 страниц, это займет часы. * С индексом: Вы открываете предметный указатель в конце книги, находите «Наполеон», видите номера страниц (5, 14, 156) и открываете сразу их.

В базе данных происходит то же самое. По умолчанию, когда вы делаете SELECT * FROM Users WHERE LastName = 'Ivanov', СУБД проверяет каждую строку таблицы. Если строк миллион — это медленно.

Как работают индексы (B-Tree)

Самый популярный тип индекса — сбалансированное дерево (B-Tree). Оно хранит данные в отсортированном виде, что позволяет использовать алгоритм бинарного поиска.

Сравним эффективность поиска. Пусть — количество строк в таблице.

Поиск без индекса (Линейный поиск): Сложность алгоритма:

Где означает, что время выполнения растет прямо пропорционально количеству строк . Если строк 1 000 000, нужно сделать до 1 000 000 сравнений.

Поиск с индексом (Бинарный поиск): Сложность алгоритма:

Где означает, что время растет логарифмически. Логарифм — это степень, в которую нужно возвести основание (здесь 2), чтобы получить число . Для 1 000 000 строк потребуется всего около 20 операций сравнения ().

Создание индекса

В SQL индекс создается командой CREATE INDEX:

Теперь поиск по фамилии будет молниеносным.

Обратная сторона медали

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

Замедление записи (INSERT, UPDATE, DELETE). Когда вы добавляете новую строку в книгу, вам нужно не только вписать текст на страницу, но и обновить предметный указатель в конце. Индексы нужно перестраивать при каждом изменении данных. Чем больше индексов, тем медленнее запись.

Занимаемое место. Индексы — это дополнительные структуры данных, которые занимают место на диске.

Когда создавать индексы?

* Нужно: Для столбцов, по которым часто идет поиск (WHERE), соединение таблиц (JOIN) или сортировка (ORDER BY). Обычно это внешние ключи (ForeignKey) и поля вроде Email, Phone. * Не нужно: Для маленьких таблиц, для столбцов с малым количеством уникальных значений (например, пол: М/Ж) или для столбцов, которые очень часто обновляются, но редко используются для поиска.

Первичные и внешние ключи

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

* Primary Key (Первичный ключ): Уникальный идентификатор строки. Автоматически создает индекс. Не может быть NULL. * Foreign Key (Внешний ключ): Ссылка на первичный ключ другой таблицы. Обеспечивает ссылочную целостность.

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

Пример создания таблицы со связью:

Заключение

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

В этом курсе мы прошли путь от теории к практике:

Поняли, что такое БД.

Научились писать SELECT.

Освоили INSERT, UPDATE, DELETE.

Связали таблицы через JOIN.

И сегодня научились проектировать эти таблицы правильно.

Эти знания — фундамент профессии backend-разработчика, аналитика данных и администратора БД. SQL — это язык, который остается актуальным уже полвека, и владение им открывает двери в мир больших данных.

Удачи в ваших проектах и пусть ваши запросы всегда будут оптимизированы!