Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 5
Часть 1. Установка. Повторение SQL
Глава 3. Повторяем базовый SQL (CREATE, INSERT, SELECT, UPDATE, DELETE)
ОглавлениеОписание задачи
Повторим основные знакомые команды SQL. Для этого создадим таблицу-пример и заполним её данными. Потом попробуем обновить или удалить некоторые записи.
Так мы сможем потренировать и вспомнить основные команды SQL на готовом примере.
Основные SQL-конструкции
Нам понадобятся следующие команды:
– CREATE TABLE – создаёт новую таблицу
– INSERT INTO – добавляет строки
– SELECT – показывает данные
– UPDATE – изменяет данные
– DELETE – удаляет строки
– WHERE – фильтрует строки для SELECT, UPDATE, DELETE (AND и OR – логика)
– LIKE – ищет по шаблону внутри WHERE
Расскажем кратко о каждой команде:
CREATE TABLE имя_таблицы (колонка1 ТИП, колонка2 ТИП)
Создаёт пустую таблицу с заданной структурой.
INSERT INTO имя_таблицы (колонки) VALUES (значения)
Добавляет одну или несколько строк.
SELECT колонки FROM имя_таблицы WHERE условие
Показывает строки, которые подходят под условие. В условиях бывает логика – AND или OR.
UPDATE имя_таблицы SET колонка = значение WHERE условие
Изменяет заданные колонки, где строки подходят под условие.
DELETE FROM имя_таблицы WHERE условие
Удаляет строки из таблицы, подходящие под условие.
WHERE условие
Используется с SELECT, UPDATE, DELETE.
Возможные примеры:
text
SELECT * FROM test_table WHERE city = «Москва»/* выбираем все строки, где город Москва */
text
UPDATE users SET age = age +1 WHERE age> 18 /* обновляем возраст, где он больше 18 */
text
DELETE FROM temp_orders WHERE status IS NULL /* удаляем строки с пустым статусом */
Типичная ошибка
Забыть WHERE в DELETE или UPDATE. Без WHERE удаляются или изменяются ВСЕ строки таблицы. Всегда сначала напишите SELECT с тем же WHERE, чтобы проверить, какие строки попадут под действие.
LIKE «шаблон»/*like*/
Используется внутри WHERE для поиска по тексту.
Символ % заменяет любую последовательность символов (даже пустую).
Примеры:
– WHERE name LIKE «А%' – имена, начинающиеся на А
– WHERE name LIKE '%ов» – имена, заканчивающиеся на «ов».
– WHERE name LIKE '%а%' – имена, содержащие букву «а» в любом месте
– WHERE email LIKE '%@gmail.com' – адреса gmail
– WHERE name LIKE «А__' – имена из трёх букв, начинающиеся на А
Символ _ заменяет ровно один любой символ.
Примеры:
– WHERE name LIKE «А___' – имена из четырёх букв, начинающиеся на А
– WHERE name LIKE '_а%' – имена, где вторая буква «а».
Совет
LIKE чувствителен к регистру. «А%' не найдёт «алексей». Если регистр не важен, используйте ILIKE. Например: WHERE name ILIKE «а%' найдёт и «Алексей», и «алексей».
Основные типы данных в PostgreSQL
– SERIAL – последовательность, заполняется автоматически (1, 2, 3…). Для первичных ключей.
– VARCHAR (n) – строка длиной до n символов.
– TEXT – большой текст, строка без ограничения длины.
– DATE – хранит даты (год-месяц-день, например 2026-01-01).
– TIMESTAMP – хранит и дату, и время.
– BOOLEAN – истина или ложь (true/false).
– DECIMAL (10,2) – число с двумя знаками после запятой. Подходит для денег (например, 11.22, 10.50, 110.00).
SQL-запросы с объяснением
Создаём таблицу.
text
CREATE TABLE IF NOT EXISTS test_users (
user_id SERIAL PRIMARY KEY,
name VARCHAR (50),
city VARCHAR (50),
status VARCHAR (20)
);
Как это работает
– SERIAL – user_id будет заполняться автоматически (1, 2, 3, 4…).
– PRIMARY KEY – уникальный идентификатор каждой строки, не должен быть пустым и не должен дублироваться.
– VARCHAR (50) – имя и город не длиннее 50 символов.
– VARCHAR (20) – статус не длиннее 20 символов.
Вставляем данные.
text
INSERT INTO test_users (name, city, status) VALUES
(«Алексей», «Москва», ’active’),
(«Мария», «Санкт-Петербург», ’active’),
(«Иван», «Казань», ’inactive’),
(«Ольга», «Москва», ’active’);
Как это работает
Каждая строка в скобках – одна запись. Значения перечислены в том же порядке, что и колонки после INTO: в name – «Алексей», в city – «Москва», в status – ’active’.
Теперь посмотрим, что получилось.
text
SELECT * FROM test_users;
Как это работает
Звёздочка означает «все колонки». Без WHERE – показываем все строки.
Отбор с WHERE и LIKE.
Найдём пользователей из Москвы или Казани:
text
SELECT * FROM test_users WHERE city = «Москва» OR city = «Казань»;
Найдём пользователей, у которых имя начинается на «А»:
text
SELECT * FROM test_users WHERE name LIKE «А%»;
Найдём пользователей, у которых имя заканчивается на «я»:
text
SELECT * FROM test_users WHERE name LIKE '%я»;
Редактируем данные.
Сделаем всех пользователей из Москвы активными.
text
UPDATE test_users
SET status = ’active’
WHERE city = «Москва»;
Как это работает
– WHERE city = «Москва» – выбираем строки, где город Москва.
– SET status = ’active’ – меняем статус на active.
Проверим
SELECT * FROM test_users;
Удаляем данные с условием.
Удалим пользователей со статусом inactive.
text
DELETE FROM test_users WHERE status = ’inactive’;
Как это работает
– WHERE status = ’inactive’ – выбираем строки с неактивным статусом.
– DELETE удаляет их.
Снова смотрим результат.
text
SELECT * FROM test_users;
Остались только активные пользователи.
Вопросы и ответы
В: Зачем нам временная таблица test_users?
О: Чтобы не трогать основные таблицы (users, orders) во время тренировки.
В: Что будет, если в DELETE убрать WHERE?
О: Удалятся все строки таблицы. Будьте осторожны.
В: Как удалить саму таблицу?
О: Команда DROP TABLE test_users;
В: Можно ли вставить сразу несколько строк?
О: Да, как в примере выше – несколько строк через запятую.
В: Что делает SERIAL?
О: Автоматически увеличивает значение при каждой вставке. Последовательность. Не нужно указывать user_id руками.
В: Чем VARCHAR отличается от TEXT?
О: VARCHAR (n) ограничивает длину. TEXT может быть любой длины. Для коротких полей (имя, город) лучше VARCHAR.
В: Что значит % и _ в LIKE?
О: % – любая последовательность символов (может быть и пустой). _ – ровно один любой символ.
Что в итоге
Теперь мы создали таблицу, вставили данные, отредактировали, удалили что не нужно и проверили результат. Все эти команды пригодятся в следующих главах.
* Задание со звёздочкой
Создайте таблицу test_employees с колонками: employee_id SERIAL PRIMARY KEY, name VARCHAR (100), department VARCHAR (50), salary NUMERIC.
Вставьте одного сотрудника: «Иван Иванов», отдел «Аналитика», зарплата 100000.
Напишите запрос, который увеличивает зарплату на 10% для всех сотрудников отдела «Аналитика».
