От CREATE до JOIN: введение в SQL + шпаргалка

0
8576
Добавить в избранное

Львиная доля мировой информации хранится в реляционных базах данных. Чтобы работать с ней, нужно владеть языком SQL-запросов.

От CREATE до JOIN: введение в SQL + шпаргалка

Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:

  • создание и редактирование таблиц;
  • сохранение и обновление записей;
  • выборка и фильтрация данных;
  • индексирование полей.

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

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

* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.

Терминология

База данных состоит из таблиц, а таблица – из колонок и строк.

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

Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.

Уровень: Новичок

Создание и редактирование таблиц

CREATE

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

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

ALTER

Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:

DROP и TRUNCATE

Оператор DROP удаляет таблицу из базы целиком:

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

Атрибуты и ограничения

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

Самые распространенные в SQL ограничения целостности (CONSTRAINTS):

  • DEFAULT – устанавливает значение по умолчанию;
  • AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
  • NOT NULL – запрещает создавать запись с пустым значением поля;
  • UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
  • PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
  • CHECK – проверяет значение поля на соответствие некоторому условию.

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

Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.

Решим новую задачу – составление списка президентов:

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

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

Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.

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

Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.

Сохранение и обновление записей

INSERT

Добавить в таблицу новую запись (или даже сразу несколько) очень просто:

Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:

UPDATE

Оператор UPDATE используется для изменения существующих записей таблицы.

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

Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.

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

DELETE

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

От CREATE до JOIN: введение в SQL + шпаргалка

Уровень: уверенный пользователь

Выборка и фильтрация данных

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

Вот небольшая демо-база, на которой вы можете попрактиковаться:

SELECT

Можно переименовывать поля для вывода:

Добавление условий:

Сортировка:

Ограничение количества результатов:

Агрегатные функции и группировка

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

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

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

Объединение таблиц

Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.

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

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

Соединение бывает внутреннее (INNER) и внешнее (OUTER).

Внутреннее соединение

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

SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.

Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.

Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.

Внешнее соединение

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

Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:

Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.

Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:

И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.

Декартово произведение

Оператор CROSS JOIN позволяет получить все возможные комбинации записей из двух таблиц:

Автосоединение

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

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

Объединение выборок

SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:

UNION

Объединить штатных и внештатных сотрудников

INTERSECT

Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе

MINUS

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

От CREATE до JOIN: введение в SQL + шпаргалка

Уровень: SQL-мастер

Представления

Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.

У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.

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

Если представление изменяемое, можно использовать при его создании CHECK OPTION для проверки изменений на соответствие некоторому предикату:

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

  • WITH CASCADED CHECK OPTION – проверяет запросы на всех уровнях вложенности;
  • WITH LOCAL CHECK OPTION – проверяет только «верхний» запрос.

Представление даже может ссылаться само на себя.

Чтобы удалить представление, используйте уже знакомый оператор DROP:

Индексы

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

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

Триггеры

Триггеры в SQL – это процедуры, которые автоматически запускаются при выполнении определенной операции (INSERT/UPDATE/DELETE) – до (BEFORE) или после (AFTER) нее.

Удалить существующий триггер можно с помощью оператора DROP:

От CREATE до JOIN: введение в SQL + шпаргалка

Удобные шпаргалки по SQL в pdf-формате.

Еще много интересных статей по SQL

Инетесуетесь базами данных?

Подпишитесь на нашу рассылку, чтобы получать больше интересных материалов:

И не беспокойтесь, мы тоже не любим спам. Отписаться можно в любое время.




Добавить комментарий