Какие SQL-запросы должен знать каждый?

Базовые SQL-запросы

Мы проводили вебинар вместе с Аналитиком Яндекс.Кью Анной Морозовой про SQL-запросы. В видео разобрали все тонкости работы для новичков. Все запросы сможете повторить в SQLite Online

CREATE TABLE

Создает первичную таблицу. Нам нужно создать таблицу «users» с тремя столбцами «id», «name» и «age»:

CREATE TABLE users (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   age INT,
   PRIMARY KEY (id)
);

Этот запрос создаст новую таблицу «users» с столбцами «id», «name» и «age». Столбец «id» является первичным ключом, обозначенным ключевым словом PRIMARY KEY, что гарантирует уникальность значения каждой записи в этом столбце.

Ключевое слово NOT NULL используется для указания того, что значение этого столбца должно быть обязательно заполнено (не может быть пустым). В примере выше столбец «age» может быть пустым, так как он не имеет ключевого слова NOT NULL.

Типы данных для столбцов определяются после имени столбца: в нашем примере для столбца «id» это INT, для столбца «name» это VARCHAR(50) (максимальное количество символов в строке – 50).

SELECT

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

Пример для выбора всех записей из таблицы «users», где значение в столбце «city» равно «New York»:

SELECT * FROM users WHERE city = 'New York';

Этот запрос вернет все записи из базы данных «users», в которых значение в столбце «city» равно «New York». Оператор «*» после ключевого слова SELECT означает, что мы выбираем все столбцы в выбранных записях. Знак равенства (=) используется для фильтрации по заданному условию.

INSERT

Используется для добавления новых записей в таблицу базы данных

Пример кода SQL-запроса INSERT для вставки новой записи в таблицу «users»:

INSERT INTO users (id, name, age) 
VALUES (1, 'John', 25);

Он добавит новую запись в «users» со значением «1» в столбце «id», «John» в столбце «name» и «25» в столбце «age». Ключевое слово VALUES используется для перечисления значений, которые мы хотим вставить в каждый столбец.

UPDATE

нужен для обновления существующих записей в таблице базы данных. Код для обновления записи в таблице «users», где значение в столбце «id» равно 1:

UPDATE users 
SET age = 26 
WHERE id = 1;

Этот запрос обновит запись, у которой значение в столбце «id» равно 1. Он установит значение 26 в столбце «age» для этой записи. Ключевое слово SET используется для установки нового значения для указанного столбца. Знак равенства (=) используется для указания значения.

Ключевое слово WHERE используется для фильтрации записей по заданному условию (в данном случае по значению в столбце «id», равному 1).

DELETE

используется для удаления записей из таблицы базы данных. Удаляем записи из таблицы «users», где значение в столбце «id» равно 2:

DELETE FROM users 
WHERE id = 2;

Этот код удалит запись из таблицы «users», у которой значение в столбце «id» равно 2. Ключевое слово DELETE используется для удаления записей из базы данных. Ключевое слово FROM используется для указания имени таблицы, из которой необходимо удалить записи. Ключевое слово WHERE используется для фильтрации записей в соответствии с заданным условием.

CREATE

Еще CREATE применяют для добавления новых таблиц в базе данных

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    age INT,
    job_title VARCHAR(50)
); 

Создание таблицы «employees» с полями «id», «name», «email», «age» и «job_title».
id задан как первичный ключ.

DROP

используется для удаления существующих таблиц в базе данных

DROP TABLE employees;

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

ALTER

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

ALTER TABLE employees 
ADD COLUMN salary FLOAT;

В данном примере, ADD COLUMN указывает на добавление нового столбца «salary» в таблицу «employees» с типом данных FLOAT.

Если в «employees» уже были какие-то данные, то после этого запроса значение нового столбца для всех строк будет установлено по умолчанию как NULL. Кроме того, ALTER позволяет изменять типы данных столбцов, добавлять/удалять индексы.

JOIN

Применяется для объединения двух или более таблиц в базе данных на основе общих полей

SELECT customers.name, orders.order_date
FROM customers
JOIN orders
ON customers.id = orders.customer_id;

Например, есть две таблицы «customers» и «orders». В данном примере, JOIN соединяет их, используя поле «id» из таблицы «customers» и «customer_id» из таблицы «orders».

SELECT выбирает поля «name» из «customers» и «order_date» из «orders».

Результат запроса будет содержать только те строки, которые имеют совпадающие значения полей «id» и «customer_id» в таблицах «customers» и «orders».

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

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

COUNT() — возвращает количество строк или число значений в столбце
SUM() — возвращает и вычисляет сумму всех значений в столбце

SELECT SUM(salary) as total_salary, department
FROM employees
WHERE department = 'Sales';
GROUP BY department

Этот запрос суммирует значения столбца «salary» по всем строкам в таблице «employees», где значение столбца «department» равно ‘Sales’. 

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

В результате, этот код возвращает единственное значение — сумму зарплат всех работников в департаменте ‘Sales’, которое будет представлено в столбце с именем «total_salary».

• AVG() — возвращает и вычисляет среднее значение в столбце
• MAX() — возвращает максимальное значение в столбце
• MIN() — возвращает минимальное значение в столбце
• GROUP_CONCAT() — возвращает список значений, разделенных запятыми (или другим разделителем), в пределах группы

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

GROUP BY

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

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

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

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

При работе с вложенными запросами важно понимать, что они могут иметь дополнительные ограничения, такие как использование операторов WHERE, ORDER BY и GROUP BY. Кроме того, вложенные SQL-запросы могут использоваться для выполнения сложных вычислений и преобразований данных внутри основного кода.

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

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

В SQL обычно выделяют 4 вида:

DDL (Data Definition Language — язык описания данных). Они используются для создания, изменения или удаления общей схемы базы данных. К ним относятся CREATE, ALTER, DROP

DML (Data Manipulation Language — язык управления данными). Они нужны для манипулирования данными внутри таблиц и отношений. К этой категории относятся SELECT, INSERT, UPDATE, DELETE. Именно с этим типом вы будете работать больше всего.

DCL (Data Control Language — язык контроля данных). Они применяются для управления доступом к данным в базе данных. К ним относятся GRANT и REVOKE

TCL (Transaction Control Language — язык управления транзакциями). Они используются для управления транзакционными операциями в базе данных. К этой категории относятся COMMIT, ROLLBACK, и SAVEPOINT.