SQL CRUD: читання та безпечна зміна даних

Що ви опануєте

Навчитеся створювати, читати, оновлювати й видаляти рядки, будувати фільтри, сортування та агрегати. CRUD означає Create, Read, Update, Delete. У PostgreSQL ці дії виконують INSERT, SELECT, UPDATE і DELETE. Кожен запит має передумови, набір рядків, до яких він застосовується, та очікуваний результат.

Від таблиці до результату

SELECT описує результат читання. FROM задає джерело, WHERE відбирає рядки, GROUP BY утворює групи, HAVING фільтрує групи, ORDER BY задає порядок. Це логічна модель розуміння запиту; оптимізатор може обрати інший фізичний порядок виконання. Не робіть висновків про порядок рядків без ORDER BY.

WHERE price >= 100 залишає лише рядки з відповідною ціною. Якщо price є NULL, результат порівняння unknown, тому рядок не проходить умову. IS NULL дозволяє шукати відсутні значення. AND і OR поєднують умови; дужки допомагають показати очікуваний пріоритет. Наприклад, active AND (category = ‘book’ OR category = ‘manual’) обмежує обидві категорії активними рядками.

Виконуваний сценарій каталогу

sql

CREATE SCHEMA lecture_crud;
SET search_path TO lecture_crud;
CREATE TABLE item (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  category text NOT NULL,
  price numeric(10,2) NOT NULL CHECK (price >= 0),
  active boolean NOT NULL DEFAULT true
);
INSERT INTO item (title, category, price) VALUES
  ('SQL', 'book', 240.00), ('C++', 'book', 300.00),
  ('Довідник', 'manual', 120.00)
RETURNING id, title;
SELECT id, title, price
FROM item
WHERE active AND price <= 250
ORDER BY price, id;
-- Зміна одного відомого запису з поверненням результату.
UPDATE item SET price = 250.00 WHERE id = 1 RETURNING id, price;
SELECT category, COUNT(*) AS items, AVG(price) AS average_price
FROM item GROUP BY category ORDER BY category;
DELETE FROM item WHERE id = 3 RETURNING id, title;

INSERT з явним переліком стовпців не залежить від їхнього фізичного порядку. RETURNING повертає фактично записані значення, включно зі згенерованим id. Після UPDATE книжкові ціни 250 і 300, тому середнє для book становить 275. DELETE видаляє довідник; повторний DELETE того самого id поверне нуль рядків.

Оновлення на основі умови з ключем має визначений масштаб. Якщо прибрати WHERE, UPDATE змінить усі рядки. Перед операцією над складним фільтром виконайте SELECT з тією самою умовою та перевірте кількість. Для важливих змін використовуйте транзакцію й контроль результату, а не покладайтеся лише на попередній перегляд, між яким і записом дані можуть змінитися.

Агрегування та порожні множини

COUNT(*) рахує рядки; COUNT(column) рахує ненульові значення стовпця. SUM та AVG зазвичай ігнорують NULL. Якщо запит без GROUP BY не має рядків, COUNT дає 0, SUM і AVG — NULL. COALESCE може замінити NULL на значення за замовчуванням, якщо це відповідає змісту. Невідомий середній бал і середній бал 0 означають різні ситуації.

GROUP BY category створює окремий агрегат для кожної категорії. HAVING COUNT(*) >= 2 залишає групи з двома чи більше рядками. WHERE price >= 100 відфільтрує записи перед групуванням. Пояснюйте, до яких об’єктів належить умова: до рядка чи до підсумку групи.

Сортування і сторінки

ORDER BY price, id задає стабільний порядок при однаковій ціні. LIMIT 10 обмежує число рядків. OFFSET пропускає початкові рядки, але великі зміщення можуть бути дорогими. При паралельних змінах звичайна пагінація може пропустити або повторити запис. Для великих каталогів часто обирають пагінацію за останнім значенням ключів сортування.

Запит після пари price=250,id=1 може використати WHERE (price,id) > (250,1) ORDER BY price,id LIMIT 10. Таке порівняння відповідає порядку двох стовпців, коли price обов’язковий. Для NULL потрібна додаткова явна семантика. Порядок та курсор повинні бути узгодженими.

Параметри та безпека

Дані користувача передавайте параметрами драйвера. Об’єднання SQL-рядка з введеним текстом може змінити структуру запиту. У PostgreSQL підготовлений запит демонструє розділення:

sql

PREPARE find_item(numeric) AS
  SELECT id, title FROM item WHERE price <= $1 ORDER BY id;
EXECUTE find_item(260.00);
DEALLOCATE find_item;

$1 є позицією параметра; його значення інтерпретується як дані заданого типу. Синтаксис параметрів у Python, C# чи іншому драйвері може відрізнятися. Імена таблиць і стовпців не підставляються звичайними параметрами значень: для динамічного сортування використовуйте перелік дозволених полів і API драйвера.

Практичний сценарій

У каталозі студент фільтрує активні матеріали за ціною й категорією. Визначте граничні випадки: жодного збігу, однакова ціна, ціна 0, невідомий id при редагуванні. Порожній результат є коректною відповіддю, а не помилкою з’єднання. Після UPDATE перевірте число змінених рядків: 0 означає, що умова не знайшла запису або його стан уже змінився.

Якщо ціна залежить від поточного значення, SET price = price + 10 виконує арифметику на сервері. Послідовність «прочитати, додати в клієнті, записати» має ризик втраченої зміни при конкуренції. Для узгодження складного процесу потрібні транзакції та відповідна ізоляція.

Типові помилки і професійні практики

Порівняння = NULL не знаходить пропуски. LIMIT без ORDER BY не гарантує однакової сторінки. SELECT * створює залежність від усієї структури; у прикладному API перелічуйте потрібні поля. Читання зайвих персональних атрибутів підвищує обсяг даних і ускладнює контроль доступу.

Зберігайте запити з тестовими даними, поясненням очікуваного результату й перевіркою порожнього набору. Для масових змін додавайте обґрунтування кількості рядків, транзакційну межу та можливість відтворити операцію в навчальній копії. У виробничій системі право на DELETE має відповідати ролі користувача.

Контроль масштабу зміни та підтвердження результату

Запит UPDATE має два аспекти: які рядки він обирає та які значення обчислює. Для адміністративного редагування за id перевірте, що результат містить один рядок. Для групової зміни кількість може бути більшою, але вона повинна мати предметне пояснення. RETURNING дозволяє записати фактичні id для перевірки виконаної дії.

Коли форма читає запис і пізніше надсилає зміну, інший користувач міг уже його оновити. Оптимістичний контроль може включати номер версії в WHERE та збільшувати його при успіху. Нуль змінених рядків тоді означає конфлікт або відсутність об’єкта. Потрібно перевірити ситуацію та запропонувати користувачу узгодження, замість мовчки перезаписувати чужий результат.

Під час фільтрації text враховуйте визначену семантику регістру, пробілів та локалі. LIKE задає шаблон зі спеціальними символами, тому введення користувача для пошуку потребує узгодженої обробки шаблону. Параметризація захищає структуру SQL, але не визначає, чи знак % має означати шаблон або буквальний символ у цьому інтерфейсі.

Для DELETE поясніть, чи потрібне фізичне видалення, архівування або зміна active. Ці дії мають різний вплив на історію та пов’язані записи. Тестуйте саме погоджений процес на наборі з залежностями. У власному сценарії запишіть передумови, кількість змін, результат для користувача та стан після відмови. Це дозволяє перевірити CRUD на рівні предметного результату.

Схема процесу

SQL CRUD: читання та безпечна зміна данихОберіть елемент, щоб побачити пояснення

Визначаємо таблицю й поля.

Самоперевірка

Як обґрунтувати відповідь своїми словами?

COUNT(*) рахує рядки. COUNT(column) пропускає NULL у зазначеному стовпці.

Підсумок

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