Язык SQL (Structured Query Language, язык структурированных запросов) используется для взаимодействия с самыми распространёнными базами данных — реляционными. С помощью SQL можно создавать базы данных и таблицы, записывать, извлекать и изменять данные, управлять структурой и доступом к базе данных. Из-за гибкости, удобства и простоты SQL часто используют не только программисты, но и бизнес-аналитики, руководители подразделений, специалисты по информационной безопасности, тестировщики и, конечно, системные администраторы.
Команды SQL называются запросами. Их можно разделить на 4 вида:
- DDL (Data Definition Language, язык определения данных). DDL-запросы позволяют создавать и удалять базы данных, описывать их структуру и задавать правила расположения информации.
- DCL (Data Control Language, язык контроля данных). Используются для управления доступом к базам данных их объектам. С помощью этих запросов обеспечивается безопасность хранимой информации.
- DML (Data Manipulation Language, язык манипулирования данными). Позволяют работать с самими данными — добавлять, извлекать, изменять и удалять записи.
- TCL (Transaction Control Language, язык управления транзакциями). Применяются для выполнения последовательностей DML-запросов. Транзакции обеспечивают целостность и согласованность данных. В случае, если любой из запросов не завершён, транзакция не выполняется.
Рассмотрим основные SQL-запросы на простом примере. Допустим, нам нужно создать зоомагазин. Для этого понадобится создать базу данных «zoo», в которую мы поместим таблицы «animal» для хранения животных, «client» для хранения покупателей и «order» для обработки заказов. Приступим.
DDL
Запрос на создание базы данных будет выглядеть так:
CREATE DATABASE название_базы_данных;Здесь и далее для наглядности мы будем писать операторы SQL заглавными буквами, а названия объектов — строчными. Результат выполнения этого запроса на примере системы управления базами данных MySQL или MariaDB будет выглядеть так:
Если в дальнейшем нам понадобится перенести базу данных на другой сервер или восстановить её, мы можем добавить в запрос дополнительный параметр IF NOT EXISTS:
CREATE DATABASE IF NOT EXISTS название_базы_данных;Теперь, если база данных уже существует, запрос не вызовет ошибку.
Для выбора созданной базы данных используется SQL-команда «USE название_базы_данных»:
Посмотреть все доступные базы данных можно по запросу «SHOW DATABASES»:
Для удаления базы данных целиком существует запрос «DROP DATABASE»:
После успешного создания базы данных можно создать нужные таблицы. Базовый синтаксис очень простой:
CREATE TABLE название_таблицы (
название_столбца_1 тип_данных,
название_столбца_2 тип_данных,
название_столбца_3 тип_данных,
...
);В диалекте MySQL мы можем сразу установить первичный ключ (PRIMARY KEY) для поля «id» и при необходимости увеличивать его значение автоматически (AUTO_INCREMENT). Также обратите внимание, что поле с первичным ключом не может быть пустым (NOT NULL):
Название животных мы будем хранить в поле «name», цену (в копейках) — в поле «price», а количество — в поле «quantity». Про типы данных можно прочитать в статье Типы данных в MySQL.
Получить подробные сведения о созданной таблице можно по запросу «DESCRIBE название_таблицы»:
Аналогично создадим таблицы «client» и «order»:
В таблице «order» для простоты мы принимаем заказ только на одно животное из таблицы «animal». Поле «client_id» является внешним ключом (FOREIGN KEY), указывающим на поле «id» таблицы «client». Заметьте, что в SQL «order» — ключевое слово, поэтому название таблицы в запросе мы взяли в обратные кавычки.
Также обратите внимание, что в реальном интернет-магазине по соображениям безопасности не рекомендуется использовать автоинкрементные поля, если они доступны клиентам. В таком случае для исключения возможности перебора лучше использовать уникальные случайные числа.
Если вы ошиблись при создании таблицы или в вашем проекте изменилась бизнес-логика, существующую таблицу можно изменить запросом «ALTER TABLE». Например, если мы хотим добавить в таблицу заказов колонку с количеством товаров, сделать это можно так:
Для удаления колонки в простейшем случае используется запрос:
ALTER TABLE название_таблицы DROP COLUMN название_колонки;а для изменения типа данных колонки:
ALTER TABLE название_таблицы MODIFY COLUMN название_колонки новый_тип данных;Дополнительные параметры и другие полезные запросы для модификации колонок можно найти, например, в этой документации.
Для полного удаления таблицы используется запрос «DROP TABLE название_таблицы».
Обратите внимание, что для работы с каждой базой данных лучше создать отдельного пользователя:
CREATE USER 'имя_пользователя' IDENTIFIED BY 'пароль';DCL
В реальных проектах к базе данных обычно обращается не только администратор, но другие члены команды, а также различные программы. Для информационной безопасности настоятельно рекомендуется разрешать им доступ только к нужным объектам и действиям.
DCL-запросы применяются для предоставления и отзыва прав доступа. Например, для предоставления всех прав на операции с базой данных пользователю, которого мы создали в предыдущем разделе, используется запрос
GRANT ALL PRIVILEGES ON название_базы_данных TO имя_пользователя;а для отзыва прав доступа
REVOKE ALL PRIVILEGES ON название_базы_данных TO имя_пользователя;DML
Теперь, когда подготовка базы данных завершена, можно поместить в наш зоомагазин несколько животных, пригласить покупателей и попробовать оформить заказы.
Давайте добавим в таблицу animal два кролика, десять хомячков и семь попугаев, а затем проверим результат:
Здесь для добавления животных мы использовали запрос INSERT:
INSERT INTO название_таблицы (название_колонки_1, название_колонки_2, ...)
VALUES (значение_поля_1, значение_поля_2, ...);а для выборки всех записей из таблицы — запрос SELECT:
SELECT * FROM название_таблицы;Как видите, мы не указали поле «id», система заполнила его автоматически. Также обратите внимание, что значение текстовых полей нужно заключить в кавычки.
Аналогичным образом можно заполнить таблицу покупателей:
Теперь у нас есть покупатели Вася, Петя и Маша. Поля «amount», в которых мы будем хранить сумму всех заказов, пока пустые (NULL).
Если клиент оформит покупку, нам нужно будет выполнить запись в таблицу заказов, уменьшить доступное количество животных (animal.quantity) и увеличить сумму заказов покупателя (client.amount).
Допустим, Маша оформила заказ на четырёх хомячков:
Изменить значение поля существующей записи можно запросом UPDATE:
UPDATE название_таблицы
SET название_колонки = значение_поля
WHERE условие;Давайте изменим значение полей «animal.quantity» и «client.amount»:
Теперь количество животных в магазине изменилось. Чтобы узнать сумму колонки «animal.quantity», воспользуемся агрегатной функцией «SUM», синтаксис которой выглядит так:
SELECT SUM (название_колонки)
FROM название_таблицы;Проверяем:
Всё верно, у нас осталось 15 животных, доступных для заказа.
Аналогично можно узнать, например, минимальную (MIN) и максимальную (MAX) цену товара, среднее значение (AVG) или посчитать количество (COUNT).
До сих пор по запросу «SELECT» мы выводили простые значения. Но язык SQL не был бы таким популярным, если бы не предоставлял широкие возможности для фильтрации, группировки и сортировки результатов выборки.
Например, чтобы получить информацию о клиентах с именем Вася, достаточно добавить в запрос к таблице «client» условие «WHERE name = 'Вася'». Если мы хотим получить сведения по нескольким именам, можно воспользоваться условием «WHERE name = 'Вася' OR name = 'Петя'» или «WHERE name IN ('Вася', 'Петя')».
С помощью выражения «NOT» можно исключить из результатов указанные значения:
SELECT * FROM название_таблицы
WHERE name NOT IN (значение_1, значение_2);Также в условии «WHERE» можно использовать логические операторы «AND» и «OR».
Теперь давайте узнаем, сколько заказов оформлено на каждое животное. Для этого воспользуемся выражением «GROUP BY»:
SELECT COUNT(название_колонки_1), название_колонки_2
FROM название_таблицы
GROUP BY название_колонки_2;Посмотрим:
Отличный результат! Пока мы разбирались с SQL-командой «SELECT», в нашем магазине оформили ещё несколько заказов! Но что это за заказы? Пока выборка совсем не информативная. Давайте выясним. Для этого воспользуемся соединением (join), которое позволяет комбинировать записи из нескольких таблиц:
SELECT название_колонки_1, название_колонки_2, название_колонки_3
FROM название_таблицы_1, название_таблицы_2
WHERE название_таблицы_1.идентификатор = название_таблицы_2.идентификатор;В нашем случае SQL-запрос будет такой:
Обратите внимание, что для наглядности мы использовали в запросе переименование нескольких столбцов с помощью оператора «AS».
Теперь результат стал гораздо понятней. Но обычно список всех заказов не нужен. Важнее получить, например, выборку по клиентам с максимальным количеством заказов или заказы с максимальной суммой. Для сортировки выборки используется выражение «ORDER BY название_колонки», а для ограничения числа строк — «LIMIT число_строк»:
Здесь мы видим три самых дорогих заказа, отсортированные в порядке убывания суммы (DESC).
В примере получения данных из нескольких таблиц для соединения мы использовали простое перечисление. На практике обычно используются более точные варианты JOIN:
- INNER JOIN — возвращает данные, которые имеют совпадающие значения в обеих таблицах. Именно этот тип соединения мы неявно использовали в нашем запросе.
- LEFT JOIN — возвращает все записи из левой таблицы и соответствующие записи из правой таблицы.
- RIGHT JOIN — возвращает все записи из правой таблицы и соответствующие записи из левой таблицы.
- FULL JOIN — возвращает все записи, если есть совпадение в левой или правой таблице.
Подробнее о соединениях можно узнать, например, в этой документации.
Оператор «GROUP BY» мы использовали для группировки результатов выборки по нужной колонке (в нашем случае «animal_id»). Группировку также часто используют совместно с агрегатными функциями COUNT(), MAX(), MIN(), SUM() и AVG() для получения сводных данных.
Гибкости агрегатных функций достаточно, чтобы при правильной организации архитектуры базы данных получить практически любую информацию об объектах. Например, узнать список клиентов с максимальным числом заказов можно, соединив таблицы клиентов и заказов с использованием функции «COUNT» и выполнив группировку по ID клиента:
Аналогичным образом можно посчитать сумму заказов клиентов, используя функцию «SUM». Для фильтрации выборки по результату работы «GROUP BY» можно использовать условие «HAVING». Однако следует помнить, что агрегатные функции могут создавать существенную нагрузку на сервер, обслуживающий базу данных. Поэтому в нашем примере мы продублировали сумму заказов в отдельной колонке «client.amount», чтобы выводить списки простым запросом к таблице клиентов. Для этого после добавления нового заказа командой «INSERT» мы обновляли нужную строку связанной таблицы с помощью запроса «UPDATE».
Но в SQL «INSERT» — не единственная команда для вставки строк. Бывают ситуации, когда запись с указанным уникальным ключом уже существует. Тогда можно воспользоваться командой «REPLACE», которая заменит существующую строку. Синтаксис команды в простейшем случае такой же, как у INSERT:
REPLACE INTO название_таблицы (название_поля, ...)
VALUES (выражение,...), (...), ...;Однако обратите внимание, что перед вставкой эта SQL-команда удаляет старую строку. Если у вас есть внешние ключи, объявленные с функцией «ON DELETE CASCADE», связанные данные также удалятся. Поэтому, во избежание рассогласования или потери данных использовать этот запрос нужно с осторожностью.
То же относится и к запросу на удаление данных из таблицы:
DELETE FROM название_таблицы
WHERE условие;Этот запрос удалит одну или несколько строк из указанной таблицы. Очистить таблицу полностью можно SQL-командой «TRUNCATE»:
TRUNCATE TABLE название_таблицы;TCL
Поскольку в SQL -данные, хранящиеся в разных таблицах, могут быть связаны между собой, при изменении записи в одной таблице часто возникает необходимость обновлять данные других объектов. Например, при оформлении заказа на какое-либо животное в нашем зоомагазине, нам нужно одновременно уменьшить количество «quantity» в таблице «animal». Если этого не сделать сразу, возможна ситуация, когда несколько покупателей оформят заказ на один товар, что приведёт к возникновению дефицита на складе. Для решения этой проблемы в SQL существует механизм транзакций.
Транзакция — это последовательность операций, выполняемая как единое целое. В случае, если хотя бы одна из операций не выполнена, вся транзакция отменяется. Важно, что транзакции не зависят друг от друга и от других SQL-запросов.
В простейшем случае синтаксис транзакции выглядит так:
BEGIN или START TRANSACTION;
SQL_запросы;
COMMIT;Изменения, выполненные транзакцией, сохраняются по команде COMMIT. Например, пусть Маша купит ещё одного хомячка:
Как видите, здесь после сохранения заказа мы также уменьшаем количество животных в таблице «animal» и увеличиваем сумму в таблице «client».
Отменить изменения, вносимые транзакцией, можно командой ROLLBACK:
BEGIN;
SQL_запросы;
ROLLBACK;Как видите, транзакции важны для обеспечения безопасности и достоверности данных в соответствии с принципами ACID --- атомарностью (Atomicity), согласованностью (Consistency), изоляцией (Isolation) и долговечностью (Durability). Соблюдение этих правил может быть критически важным во многих сценариях, например, при проведении финансовых операций.
Заключение
В этом обзоре мы лишь бегло рассмотрели основные SQL-команды и запросы, необходимые для начала работы с реальными базами данных. Конечно, возможностей языка SQL гораздо больше. За рамками статьи остались такие важные понятия, как подзапросы, временные таблицы, триггеры, хранимые процедуры, оконные функции и многое другое. С полным списком SQL-команд лучше всего ознакомиться в официальной документации по диалекту вашей системы управления базой данных, например https://dev.mysql.com/doc для MySQL, https://www.postgresql.org/docs для PostgreSQL и так далее.
Надеемся, нам удалось показать, насколько удобным и гибким может быть язык SQL.
