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

Что такое SQL

SQL — язык, на котором разработчики, аналитики и тестировщики делают запросы к базам данных. Исследователи компании IBM создали его в 1974 году, чтобы хранить и обрабатывать данные. Сегодня на языке SQL можно сделать запрос к большинству систем управления базами данных (СУБД): PostgreSQL, MySQL, Microsoft SQL Server, Oracle и другими.

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

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

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

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

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

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

1.  DDL
   Data Definition Language — основные ключевые слова, которые определяют данные. Его основные команды — CREATE, DROP, RENAME и другие. Эти ключевые слова могут создавать базы и описывать их структуру, а также устанавливать правила, как размещать данные.
2. DML
   Data Manipulation Language — ключевые слова, которые могут изменять, получать, обновлять и удалять данные из базы: SELECT, INSERT, UPDATE, DELETE и другие.
3. DCL
   Data Control Language — ключевые слова для управления базами данных. Сюда относят запросы разрешений, прав и настроек, которые ограничивают доступ. Например, GRANT или DENY.
4. TCL
   Transaction Control Language — ключевые слова, которые связаны с передачей данных. В эту группу входят все запросы, которые управляют транзакциями и их жизненными циклами. Например, BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION.

Все особенности языка вы можете изучить на курсе «Аналитик данных». Под руководством наставников вы научитесь делать таблицы и составлять запросы для анализа, соединять и обрабатывать несколько таблиц, использовать оконные функции.

Структура SQL-запросов

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

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

Если мы составим команду словами русского языка, получится что-то вроде этого:

Выбрать Столбец2, Столбец3 из Таблица1, где Столбец1 равен одному

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

SELECT (Column2, Column3) FROM Table1 WHERE Column1 = 1

Простые запросы SQL

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

• ✔️ WHEREWHERE — ключевое слово, которое ограничивает выборку. Мы уже использовали его в примере:
  SELECT (Column2, Column3) FROM Table1 WHERE Column1 = 1
  Здесь WHERE ограничивает выборку строк из Таблицы №1, где значение первого столбца равно единице.
• ✔️ GROUP BY
  Распределяет строки по группам; критерий — значения указанных столбцов. Это ключевое слово должно следовать после WHERE.
  SELECT Column1, COUNT(*) FROM Table1 WHERE Column1 = 1 GROUP BY Column2;
  Здесь мы не только отобрали записи по значению первой колонки, но еще и сгруппировали их по значению второй колонки.
• ✔️ AND, OR и BETWEEN
  AND или OR или расширяют выборку, которая создается с WHERE, или сужают ее, если указать дополнительные значения. Ключевое слово BETWEEN позволяет указать диапазон значений, чтобы создать выборку.
  -- AND сужает выборку до строк, где соблюдается условие сразу по двум колонкам
SELECT Column2, Column3 FROM Table1 WHERE Column1 = 1 AND Column2 = 'abc';
-- OR расширяет выборку до строк, где соблюдается хотя бы одно из двух условий
SELECT Column2, Column3 FROM Table1 WHERE Column1 = 1 OR Column3 = 'xyz';
-- BETWEEN указывает диапазон значений для первой колонки
SELECT Column2, Column3 FROM Table1 WHERE Column1 BETWEEN 1 AND 5;
• ✔️ LIMIT
  Это слово определяет, сколько значений должно попасть в выборку. Например, по указанным фильтрам получено 100 значений, а нужны только первые 10. В этом случае и применяют синтаксис LIMIT 10.
  SELECT Column2, Column3 FROM Table1 WHERE Column1 = 1 LIMIT 10;

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

Все запросы начинаются с команд.

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

• 🚀 CREATE TABLE
  Команда CREATE TABLE создает новую таблицу. В нашем случае нужно задать ей четыре столбца: уникальный идентификатор, имя животного — name, вид — species и признак усыновления — is_adopted.
  CREATE TABLE Pets (
id INTEGER PRIMARY KEY,
name TEXT,
species TEXT,
is_adopted BOOLEAN DEFAULT FALSE
);
• 🚀 INSERT
  С помощью INSERT можно добавить новые записи в таблицу. Добавим трех животных:
  INSERT INTO Pets (name, species) VALUES
('Белка', 'Кошка'),
('Шарик', 'Собака'),
('Кеша', 'Попугай');
  Данные в столбцы надо вставлять по порядку. При этом колонку id можно игнорировать — это первичный ключ, который генерируется сам. Если вы забудете указать значение для is_adopted, оно примет FALSE по умолчанию.
• 🚀 SELECT
  SELECT извлекает данные. Чтобы получить список всех питомцев:
  SELECT * FROM Pets;
  Получите вы примерно такой результат:
  1 | Белка | Кошка | 0
2 | Шарик | Собака | 0
3 | Кеша | Попугай| 0
  Каждая запись будет на новой строке, а значения столбцов разделены вертикальной линией.
  Если нужны не все колонки, а только определенные — замените в запросе звездочку на названия колонок.
  SELECT name, species FROM Pets;
• 🚀 UPDATE
  С помощью UPDATE можно поменять существующие записи. Допустим, Белка была усыновлена: укажите уникальный идентификатор записи и используйте SET, чтобы задать новое значение нужному параметру:
  UPDATE Pets
SET is_adopted = TRUE
WHERE name = 'Белка';
  Теперь можно проверить изменения:
  SELECT * FROM Pets WHERE name = 'Белка';
• 🚀 DELETE
  Команда DELETE удаляет записи. Например, уберем запись о попугае Кеше:
  DELETE FROM Pets
WHERE species = 'Попугай';
• 🚀 DROP TABLE
  DROP TABLE полностью удаляет таблицу и все данные в ней — без возможности восстановления:
  DROP TABLE Pets;
• 🚀 ALTER TABLE
  С ALTER TABLE можно изменить структуру таблицы. Представим, что мы хотим добавить дату поступления животного в приют arrival_date:
  ALTER TABLE Pets
ADD COLUMN arrival_date DATE;
  Теперь мы можем записывать дату попадания в приют каждого питомца.

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

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

Виды агрегатных функций:

• ✔️ COUNT(название_столбца) — подсчитывает количество строк или значений, которые не равны NULL.
• ✔️ SUM(название_столбца) — считает сумму значений в столбце.
• ✔️ AVG(название_столбца) — вычисляет среднее значений столбца.
• ✔️ MIN(название_столбца) — возвращает наименьшее значение столбца.
• ✔️ MAX(название_столбца) — возвращает наибольшее значение столбца.

Научиться работе со всеми видами агрегатных функций можно на курсе «Аналитик данных». Сможете взаимодействовать с синтаксисом и операторами для создания, модификации и удаления таблиц в SQL. В конце обучения у вас будет резюме, портфолио и диплом о профессиональной переподготовке. Это поможет устроиться на хорошую работу.

Вложенные подзапросы

Это SQL-запросы внутри другого SQL-запроса.

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

SELECT DISTINCT club_name
FROM clubs
WHERE game_year = 2010 AND club_id IN
(SELECT club_id
FROM clubs
WHERE game_year = 2020
);

Ключевое слово DISTINCT убирает из выборки дублирующиеся результаты.

Есть два вида вложенных подзапросов.

1. Независимые (простые) подзапросы: не зависят от внешнего запроса, выполняются один раз, и результат используется во внешнем запросе. Пример:
   SELECT * FROM Students WHERE id NOT IN (SELECT DISTINCT student_id FROM Marks);
2. Коррелированные (сложные) подзапросы: зависят от данных внешнего запроса, выполняются для каждой строки внешнего запроса. Пример:
   SELECT * FROM Students
WHERE (SELECT AVG(mark) FROM Marks WHERE Students.id = Marks.student_id) > 4;

Примеры SQL-запросов

Рассмотрим SQL примеры — от создания таблицы до запроса нескольких вариантов нужной выборки. Можете повторить это в инструменте для работы с SQL — PostgreSQL.

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

CREATE TABLE Employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER,
hire_year INTEGER
);

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

INSERT INTO Employees (name, department, salary, hire_year) VALUES
('Анна', 'HR', 55000, 2020),
('Борис', 'IT', 75000, 2018),
('Вера', 'IT', 80000, 2021),
('Григорий', 'Sales', 60000, 2019),
('Дина', 'HR', 57000, 2020),
('Егор', 'Sales', 62000, 2022);

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

SELECT * FROM Employees;

Или имена и отделы всех работников:

SELECT name, department FROM Employees;

Если руководитель попросил вывести всех сотрудников отдела IT, всё что нужно — отфильтровать записи по значению колонки department.

SELECT name FROM Employees WHERE department = 'IT';

Если же ему нужны люди, которые получают больше определенной суммы, то фильтрацию надо настроить по значению третьей колонки, salary.

SELECT name, salary FROM Employees WHERE salary > 60000;

Если руководитель просит настроить более сложный запрос, могут пригодиться операторы AND или OR, которые комбинируют условия. Например, мы можем запросить из таблицы данные по работникам отдела HR, которые получают больше 70000 ₽ в месяц.:

SELECT name FROM Employees
WHERE department = 'HR' OR salary > 70000;

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

SELECT department, AVG(salary) FROM Employees GROUP BY department;

Или — выбрать трех самых высокооплачиваемых сотрудников с помощью MAX:

SELECT MAX(salary) FROM Employees;
ORDER BY salary DESC
LIMIT 3;

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

SELECT name, salary FROM Employees
WHERE salary < (SELECT AVG(salary) FROM Employees);

Когда эйчары одобрят увеличение зарплаты этим сотрудникам на 15%, можете использовать тот же подзапрос внутри UPDATE и установить новую зарплату через команду SET:

UPDATE Employees
SET salary = salary * 1.15
WHERE salary < (SELECT AVG(salary) FROM Employees);

Объединение таблиц с помощью JOIN

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

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

Таблицы, которые упоминаются в одном запросе, в SQL называются «левой» и «правой». Та таблица, которая указана первой, считается левой; которая указана второй — правой.

Например, если запрос начинается с:

SELECT Employees.name, Departments.name
FROM Employees

Левой будет таблица Employees.

Есть несколько видов JOIN, которые могут объединить таблицы:

• ✔️ INNER JOIN — соединяет только те строки, где значения в объединяемых столбцах совпадают в обеих таблицах.
  SELECT Employees.name, Departments.name
FROM Employees
INNER JOIN Departments ON Employees.department_id = Departments.id;
• ✔️ LEFT JOIN — ко всем строкам из левой таблицы добавляет совпадающие строки из правой. Если совпадений нет, добавляются строки с NULL.
  SELECT Employees.name, Departments.name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.id;
• ✔️ RIGHT JOIN — работает наоборот: ко всем строкам из правой таблицы добавляет совпадающие строки из левой.
  SELECT Employees.name, Departments.name
FROM Employees
RIGHT JOIN Departments ON Employees.department_id = Departments.id;
• ✔️ FULL JOIN — соединяет все строки из обеих таблиц. Если совпадений нет, то в соответствующих столбцах будет NULL.
  SELECT Employees.name, Departments.name
FROM Employees
FULL JOIN Departments ON Employees.department_id = Departments.id;


Какие ошибки чаще всего встречаются в SQL

SQL — язык с простым синтаксисом, поэтому пользователи редко ошибаются. Самый распространенный тип ошибок у новичков — это обычные опечатки, например, CRAETE вместо CREATE и т. п.

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

Самая опасная ошибка — использование UPDATE или DELETE без условия в WHERE. Такая команда может перезаписать или удалить все строки в таблице. Тогда данные будут безвозвратно потеряны.

Главное

• SQL — это язык запросов к базам данных. Он помогает получать, менять и удалять данные, хранящиеся в таблицах.
• Есть четыре типа SQL-команд:
  • DDL — создает и меняет структуру баз;
  • DML — работает с данными;
  • DCL — управляет доступом,
  • TCL — управляет транзакциями.
• Простые запросы состоят из ключевых слов, например: SELECT, WHERE, GROUP BY, AND, OR, BETWEEN, LIMIT.
• Команды SQL позволяют:
  • создавать таблицы (CREATE),
  • добавлять записи (INSERT),
  • получать данные (SELECT),
  • обновлять записи (UPDATE),
  • удалять данные или таблицы (DELETE, DROP),
  • менять структуру таблиц (ALTER).
• Агрегатные функции помогают анализировать данные: можно посчитать строки (COUNT), сумму (SUM), среднее (AVG), минимум (MIN) и максимум (MAX).
• Вложенные подзапросы полезны для сложной фильтрации. Бывают простые (выполняются один раз) и коррелированные (выполняются для каждой строки).
• SQL активно применяют в реальных задачах. Он помогает анализировать данные, работать с отчетами и автоматизировать обработку информации.