Топ вопросов по SQL

JOIN в SQL — это операция, которая позволяет объединять строки из двух и более таблиц по связанным столбцам (обычно по ключам). Основные виды: INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN, CROSS JOIN, а также часто выделяют SELF JOIN как отдельный случай.

JOIN используется, когда данные логически связаны, но физически лежат в разных таблицах: мы объединяем строки по условию вида ON table1.key = table2.key. Итоговый набор данных строится в зависимости от типа соединения и того, что делать со строками, для которых нет пары во второй таблице.

Основные виды соединений:

• INNER JOIN — внутреннее соединение: возвращает только те строки, для которых условие соединения выполняется в обеих таблицах (есть «совпавшая пара»).
• LEFT (OUTER) JOIN — левое внешнее соединение: возвращает все строки из левой (первой в запросе) таблицы, а для совпавших по условию строк из правой таблицы подставляет данные; если пары нет, в колонках правой таблицы будут NULL.
• RIGHT (OUTER) JOIN — правое внешнее соединение: симметрично левому, но гарантируется возврат всех строк из правой таблицы; для строк без пары в левой таблице значения её колонок будут NULL.
• FULL (OUTER) JOIN — полное внешнее соединение: возвращает все строки из обеих таблиц; для тех, у кого нет пары в другой таблице, соответствующие поля заполняются NULL.
• CROSS JOIN — перекрёстное соединение: декартово произведение, то есть каждая строка первой таблицы сочетается с каждой строкой второй таблицы, без условия соединения.
• SELF JOIN — самообъединение: соединение таблицы с самой собой (по сути обычный JOIN, где одна и та же таблица используется под разными алиасами).

WHERE фильтрует отдельные строки до выполнения группировки и агрегатных функций, а HAVING фильтрует уже сформированные группы после GROUP BY. В WHERE нельзя использовать агрегатные функции, в HAVING можно. Часто оба оператора используют вместе: сначала отбирают строки через WHERE, затем отфильтровывают группы через HAVING.

Логический порядок выполнения запроса таков: сначала выбираются строки из таблиц (FROM), затем применяется фильтрация по строкам (WHERE), далее выполняется группировка (GROUP BY) и агрегатные функции, после чего к уже сформированным группам применяется фильтрация через HAVING.

Основные отличия:

• Уровень фильтрации: WHERE отбрасывает отдельные строки до группировки; HAVING отбрасывает целые группы (или результат агрегации) после группировки.
• Агрегатные функции: в WHERE нельзя использовать агрегатные функции (например, SUM, COUNT, AVG), а в HAVING это основное назначение.
• Производительность: по возможности условия, не зависящие от агрегации, лучше помещать в WHERE, чтобы уменьшить объём данных для группировки.

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

• Лучшая организация базы данных
• Больше таблиц с небольшими строками
• Эффективный доступ к данным
• Большая гибкость для запросов
• Быстрый поиск информации
• Проще реализовать безопасность данных
• Позволяет легко модифицировать
• Сокращение избыточных и дублирующихся данных
• Более компактная база данных
• Обеспечивает согласованность данных после внесения изменений

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

Не путать с базой данных! База данных — структурированная коллекция данных.

Существует два типа СУБД:

• Реляционная: данные хранятся в отношениях (таблицах). Примеры — PostgreSQL, MySQL.
• Нереляционная: не существует понятия отношений, кортежей и атрибутов. Примеры— MongoDB, Cassandra.

Первичный ключ (primary key) — это столбец или комбинация столбцов, которые уникально идентифицируют каждую строку в таблице. Он обеспечивает целостность и уникальность данных в пределах таблицы. У каждой таблицы может быть только один первичный ключ.

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

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

Например:

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

Важно! Сам запрос в данном случае не исполняется. А вот при использовании EXPLAIN ANALYZE запрос выполняется.

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

Пример использования команды EXPLAIN для простого запроса:

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

Этот вывод показывает, что база данных будет выполнять последовательное сканирование (Seq Scan) таблицы employees и применять фильтр age > 30. Поле cost показывает предполагаемые затраты на выполнение операции в терминах времени и ресурсов.

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

Использование EXPLAIN особенно полезно при работе с сложными запросами или большими объемами данных, так как позволяет заранее понять, как будет выполняться запрос, и какие оптимизации могут понадобиться. В сочетании с EXPLAIN ANALYZE можно получить как план выполнения, так и реальные показатели производительности, что делает процесс оптимизации еще более точным и информативным.

Оператор UNION используется для объединения результатов двух или более операторов SELECT и удаляет дублирующиеся строки. С другой стороны, UNION ALL объединяет наборы результатов без удаления дубликатов. Поскольку UNION ALL не осуществляет дополнительный шаг по удалению дубликатов, он обычно быстрее. Однако если необходимо удалить дубликаты, то следует использовать UNION.

ACID — это аббревиатура, обозначающая четыре ключевых свойства транзакций в базах данных: атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation) и долговечность (Durability).

Набор требований ACID означает:

• атомарность (Atomicity)
• согласованность (Consistency)
• изолированность (Isolation)
• долговечность (Durability).

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

Атомарность

Атомарность означает, что транзакция либо выполняется полностью, либо не выполняется вовсе. Если что-то пошло не так в процессе выполнения, все изменения откатываются, и база данных остается в прежнем состоянии.

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

Согласованность

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

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

Изолированность

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

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

Долговечность

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

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

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

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

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

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