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

SQL — это язык запросов для управления реляционными базами данных. «Реляционные» означает, что все данные хранятся в виде взаимосвязанных таблиц. А SQL как раз используют для того, чтобы как-то влиять на элементы внутри этих таблиц: добавлять, удалять, изменять и так далее.

Язык SQL лежит в основе систем управления реляционными базами данных, таких как MySQL, PostgreSQL, Oracle и т.д. Таким образом, чтобы работать, скажем, с базой данных MySQL, нужно сперва изучить язык запросов SQL.

По синтаксису SQL-запросы максимально похожи на обычные предложения:

SELECT (Name, Age) FROM Clients WHERE Age > 20

Если перевести на русский, получится что-то вроде:

Выбрать Имя и Возраст из Таблицы с клиентами, где Возраст больше 20

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

Виды запросов в SQL

Перед тем как писать команды, разберёмся, какие есть виды запросов в SQL. Всего их четыре — DDL, DML, DCL и TCL. Каждый из них выполняет определённые действия — давайте разберём каждую категорию подробнее.

DDL, или data definition language, нужен, чтобы определять данные. Эти запросы позволяют настраивать базу данных — создавать с нуля и прописывать её структуру.

DML, или data manipulation language, нужен, чтобы управлять данными в таблицах. Эти запросы помогают добавлять, обновлять, удалять и выбирать данные.

DCL, или data control language, нужен, чтобы выдавать или отзывать права доступа для пользователей.

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

Теперь перейдём к тому, как SQL-запросы составляются и из каких элементов состоят.

Как выглядит структура SQL-запроса

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

• SELECT — выбирает отдельные столбцы или всю таблицу целиком (обязательный);
• FROM — из какой таблицы получить данные (обязательный);
• WHERE — условие, по которому SQL выбирает данные;
• GROUP BY — столбец, по которому мы будут группироваться данные;
• HAVING — условие, по которому сгруппированные данные будут отфильтрованы;
• ORDER BY — столбец, по которому данные будут отсортированы;

Давайте разберём каждую из частей этого запроса по порядку.

SELECT

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

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

В качестве примера выберем столбцы Name и Age из таблицы Clients:

SELECT (Name, Age) FROM Clients

На выходе будут все строки таблицы, принадлежащие столбцам Name и Age.

FROM

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

Например, ранее мы уже выбирали данные из таблицы Clients:

SELECT (Name, Age) FROM Clients

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

WHERE

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

Например, этот запрос вернёт все строки из таблицы, где значения Age больше 20:

SELECT (Name, Age) FROM Clients WHERE Age > 20

GROUP BY

Этот оператор помогает нам сгруппировать данные по определённым столбцам. В результате получим новую таблицу, составленную на основе выбранных данных.

Например, сгруппируем результат предыдущего запроса по городам:

SELECT (Name, Age) FROM Clients WHERE Age > 20 GROUP BY City

Запрос вернёт клиентов старше 20 лет и сгруппирует их по городам. Главное — чтобы столбец City присутствовал в таблице.

HAVING

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

Например, укажем, чтобы в группы добавлялись только клиенты с суммой заказа от 1000 рублей:

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000

ORDER BY

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

Допустим, если хотим отсортировать клиентов по возрасту — от младшего к старшему, — добавляем команду ORDER BY Age:

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000
ORDER BY Age

А чтобы отсортировать по убыванию, просто добавляем слово DESC:

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000
ORDER BY Age DESC

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

Примеры SQL-запросов: создаём первую базу данных

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

CREATE DATABASE

Первым делом создаём базу данных. Делается это с помощью команды CREATE DATABASE:

CREATE DATABASE CatsCatsCats;

Внутри пока ничего нет. Но это пока.

CREATE TABLE

Запрос создаёт таблицу в базе данных. В общем виде команда выглядит так:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

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

В SQL много типов данных. Вот примеры самых популярных:

• INT — целое число;
• DATETIME — дата;
• VARCHAR — строка;
• FLOAT — десятичное число.

В нашей таблице используется два типа: строки (VARCHAR) и целые числа (INT):

CREATE TABLE CatsAndOwners (
CatID int(6) NOT NULL,
CatName varchar(255) NOT NULL,
CatAge int(6) NOT NULL,
CatColor varchar(255) NOT NULL,
CatOwnerName varchar(255) NOT NULL
);

В примере выше мы добавили пять столбцов: уникальный номер кота CatID, его имя CatName, возраст CatAge, цвет CatColor и имя владельца CatOwnerName. А ещё задали, чтобы ни одно из полей не было пустым — NOT NULL.

Цифры рядом с типами данных обозначают, сколько бит выделяется для поля. Например, varchar (255) значит, что строка может принимать размер от 0 до 255 бит — по объёму данных это приблизительно соответствует фразе «Я люблю язык SQL».

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

ALTER TABLE

Если вдруг забыли добавить столбец во время создания таблицы — ничего страшного. Новые колонки можно добавлять с помощью команды ALTER TABLE. Давайте добавим город проживания кота:

ALTER TABLE CatsAndOwners
ADD City varchar(255);

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

Теперь таблица выглядит так:

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

INSERT

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

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(1, 'Мурка', 3, 'Чёрная', 'Дмитрий', 'Москва');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(2, 'Белла', 7, 'Белая', 'Максим', 'Саратов');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(3, 'Симба', 5, 'Рыжий', 'Екатерина', 'Санкт-Петербург');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(4, 'Лео', 2, 'Полосатый', 'Александр', 'Екатеринбург');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(5, 'Мася', 1, 'Серый', 'Анна', 'Москва');

Блеск! Таблица наконец-то обзавелась данными:

SELECT

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

SELECT CatName, CatOwnerName
FROM CatsAndOwners;

Результат:

Если нужно выбрать все столбцы из таблицы, после слова SELECT добавим символ *. В этом случае на выходе получим всю таблицу целиком.

SELECT *
FROM CatsAndOwners;

WHERE

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

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge > 5

Результатом будет одна строка с двумя столбцами:

AND, OR, BETWEEN

Оператор WHERE интересен тем, что внутри него можно указывать условия — причём сразу несколько. Делается это с помощью логических конструкций AND, OR и BETWEEN.

AND — это логическое И. Оно означает, что должны выполняться оба условия запроса одновременно. Например, кошка должна быть чёрной И проживать в Москве.

SELECT CatName
FROM CatsAndOwners
WHERE CatColor = 'Чёрная'
    AND City = 'Москва'

Результат:

OR — это логическое ИЛИ. Оно означает, что должно выполниться или одно условие, или второе. Например, кошка должна быть ИЛИ старше пяти лет, ИЛИ быть чёрной.

SELECT CatName
FROM CatsAndOwners
WHERE CatAge > 5
    OR CatColor = 'Чёрная'

Результат:

BETWEEN — это оператор, который выбирает все элементы внутри заданного диапазона. Например, можно запросить всех кошек в возрасте от двух до шести лет.

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge BETWEEN 2 AND 6

Результат:

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

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge BETWEEN 2 AND 8
    AND (City = 'Саратов' OR City = 'Санкт-Петербург')
    OR CatName = 'Мурка'

Результат:

ORDER BY

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

SELECT CatName, CatAge
FROM CatsAndOwners
ORDER BY CatAge DESC

Результат:

Чтобы отсортировать записи по возрастанию, нужно просто убрать из запроса параметр DESC:

SELECT CatName, CatAge
FROM CatsAndOwners
ORDER BY CatAge

Результат:

GROUP BY

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

SELECT City, COUNT(*) AS CatCount 
FROM CatsAndOwners
GROUP BY City;

В этом примере мы применили агрегатную функцию COUNT, которая посчитала количество строк в каждой группе. К функциям-агрегаторам мы вернёмся позже, а пока — насладимся результатом:

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

LIMIT

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

SELECT *
FROM CatsAndOwners
LIMIT 2;

Результат:

UPDATE

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

UPDATE CatsAndOwners 
SET CatColor = 'Пурпурный'
WHERE CatName = 'Симба';

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

Результат:

DELETE

Удаляет строку. Например, можем удалить из таблицы всех кошек, которые живут в Саратове:

DELETE FROM CatsAndOwners 
WHERE City = 'Саратов';

Результат:

DROP COLUMN

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

ALTER TABLE CatsAndOwners 
DROP COLUMN CatOwnerName;

Заметьте, что сначала нужно применить команду ALTER TABLE. Как мы помним, она заточена на то, чтобы добавлять, менять или удалять колонки в таблице.

Результат:

DROP TABLE

Если таблица больше не нужна, можем удалить её. Сделать это просто:

DROP TABLE CatsAndOwners;

Применяйте команду на свой страх и риск. Предварительно советуем всё-таки сохранить таблицу — вдруг пригодится.

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

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

В SQL доступны пять агрегатных функций:

• COUNT — посчитать количество строк;
• SUM — посчитать сумму значений в столбце;
• AVG — получить среднее значение в столбце;
• MIN — получить минимальное значение в столбце;
• MAX — получить максимальное значение в столбце.

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

SELECT SUM(CatAge) AS TotalAge
FROM CatsAndOwners;

Результат:

Теперь найдём наименьший возраст кошки:

SELECT MIN(CatAge) AS MinAge
FROM CatsAndOwners;

Результат:

А теперь высшая математика — вычислим средний возраст кошек для каждого города:

SELECT City, AVG(CatAge) AS AverageAge
FROM CatsAndOwners
GROUP BY City;

Результат:

Что запомнить

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

• SQL — это язык структурированных запросов. Он нужен, чтобы управлять информацией в реляционных базах данных — то есть тех, которые состоят из связанных между собой таблиц.
• Каждый запрос нацелен на то, чтобы совершать какое-то действие с данными в таблице: выводить на экран, добавлять новые, считать средние значения, удалять и так далее.
• Все запросы делятся на четыре группы: DDL, DML, DCL и TCL. DDL отвечает за определение данных. DML — за управление данными. DCL — за выдачу прав доступа. TCL — за управление транзакциями.
• Классический запрос состоит из шести операторов. Два из них обязательные: SELECT и FROM. Остальные четыре — используются в зависимости от задачи: WHERE, GROUP BY, HAVING и ORDER BY.
• Помимо базовых команд, в SQL существует множество дополнительных — изучить их можно, например, в шпаргалке от W3Schools. А ещё лучше — берите эти команды на вооружение и экспериментируйте, ведь теория без практики мертва.