CRUD операции с данными

CRUD операции

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

• Create — создать запись
• Read — прочитать данные
• Update — обновить запись
• Delete — удалить запись

Мы уже разбирали синтаксис создания таблиц:

CREATE TABLE <имя таблицы> (
    <имя столбца1> <тип столбца> [дополнительные условия],
    <имя столбца2> <тип столбца> [дополнительные условия],
    ...
);

Но чтобы не было путаницы CREATE TABLE хоть и содержит в своем названии слово Create является синтаксисом создания таблиц, а не создания записей.

На самом деле новая запись в таблицу добавляется с помощью SQL команды INSERT:

INSERT INTO <имя таблицы> (<имя столбца>[, <имя столбца2>, ...])
VALUES (<значение>[, <значение2>, ...]);

Read — Чтение

Для того чтобы читать сохраненные данные надо воспользоваться командой SELECT. Полный синтаксис команды очень большой (PostgreSQL, SQLite), поэтому мы сначала будем разбираться с его краткой версией:

SELECT 
    <перечень стобцов>
FROM
    <перечень таблиц>
WHERE
    <условия>
LIMIT <количество результатов>
OFFSET <сколько результатов пропустить>

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

Пример запроса без пункта FROM:

SELECT 2+2;

2+2
---
4  

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

Результирующая выборка

Когда вы задаете вопрос, то от базы вы получаете ответ. Это может быть пустое множество или перечень записей. Вот они и есть результирующая выборка.

Пример запроса, который возвращает только название продукта и его вес:

SELECT 
    title,
    amount
FROM
    groceries;

title          amount    
-------------  ----------
Морковка       2 большие 
Молоко         1 литр    
Масло          1 пачка   
Бананы         1-2 кг    
Сыр            200 грамм 
Яблоки         500 грамм 
Куриные ножки  500 грамм 
Лук            4 луковицы

Обратите еще раз внимание, что каждый запрос заканчивается на точку с запятой ;.

Если вы хотите получить все доступные данные из базы, то вместо указания названия столбцов можно использовать символ *:

SELECT 
    *
FROM
    groceries;

id  title          amount      is_done
--  -------------  ----------  -------
1   Морковка       2 большие   0      
2   Молоко         1 литр      0      
3   Масло          1 пачка     0      
4   Бананы         1-2 кг      1      
5   Сыр            200 грамм   0      
6   Яблоки         500 грамм   1      
7   Куриные ножки  500 грамм   0      
8   Лук            4 луковицы  1  

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

SELECT 
    *
FROM
    groceries
WHERE
    is_done = FALSE;

id  title          amount     is_done
--  -------------  ---------  -------
1   Морковка       2 большие  0      
2   Молоко         1 литр     0      
3   Масло          1 пачка    0      
5   Сыр            200 грамм  0      
7   Куриные ножки  500 грамм  0  

Запрос чтобы получить список купленных продуктов:

SELECT
    *
FROM 
    groceries
WHERE 
    is_done = TRUE;

id  title   amount      is_done
--  ------  ----------  -------
4   Бананы  1-2 кг      1      
6   Яблоки  500 грамм   1      
8   Лук     4 луковицы  1      

Чтобы получить конкретную запись можно обратиться к ней по номеру записи, хранящейся в поле id:

SELECT
    *
FROM 
    groceries
WHERE 
    id = 4;

id  title   amount  is_done
--  ------  ------  -------
4   Бананы  1-2 кг  1   

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

SELECT
    is_done
FROM 
    groceries
WHERE 
    title = 'Бананы';

is_done
-------
1     

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

Некоторые пример:

• логические OR, AND, NOT
• математические >, <, >=, <= и т.д.
• вхождение во множество IN или нахождение в диапазоне BETWEEN
• сопоставление с образцом LIKE и ILIKE
• текстовый поиск
• поиск по дате, времени и периодам

Более подробно в документации Postgres: Функции и Операторы.

Пример запроса для продуктов, которые еще надо купить и которые начинаются с букв "Мо":

SELECT
    title,
    amount,
    is_done
FROM
    groceries
WHERE 
    title LIKE "Мо%" AND is_done=FALSE;

title     amount     is_done
--------  ---------  -------
Морковка  2 большие  0      
Молоко    1 литр     0  

Update — Модификация данных

Для модификации записей синтаксис выглядит таким образом:

UPDATE <таблица>
SET <столбец> = <значение>[, <столбец2> = <значение2>, ...]
WHERE <условие>;

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

UPDATE groceries
SET is_done=TRUE
WHERE title="Молоко";

Результат надо будет проверить отдельно:

select * from groceries where title="Молоко";

id  title   amount  is_done
--  ------  ------  -------
2   Молоко  1 литр  1   

Теперь молоко куплено.

Условие WHERE

Обратите внимание, что если не написать условия WHERE для UPDATE то запрос перезапишет каждую строку таблицы.

Условия могут быть достаточно сложными. Точно такими же, как и запроса SELECT. Если надо обновить только одну запись, то проще всего к ней обращаться по ключу id=N.

Delete — Удаление

Удаление очень похоже на обновление, только проще:

DELETE FROM <таблица> WHERE <условие>;

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

Если надо очистить список покупок и удалить все купленные товары:

DELETE FROM groceries WHERE is_done=TRUE;

LIMIT и OFFSET

Для того чтобы управлять количеством записей, получаемых из базы используются LIMIT и OFFSET.

Значение LIMIT указывает сколько максимум может вернуть база, OFFSET пропускает нужное количество записей в начале результирующей выборки.

Синтаксис:

SELECT 
    <перечень стобцов>
FROM
    <перечень таблиц>
WHERE
    <условия>
LIMIT <количество результатов>
OFFSET <сколько результатов пропустить>

Пример:

SELECT * FROM groceries;

id  title          amount      is_done
--  -------------  ----------  -------
1   Морковка       2 большие   0      
2   Молоко         1 литр      1      
3   Масло          1 пачка     0      
4   Бананы         1-2 кг      1      
5   Сыр            200 грамм   0      
6   Яблоки         500 грамм   1      
7   Куриные ножки  500 грамм   0      
8   Лук            4 луковицы  1      

SELECT * FROM groceries LIMIT 2;

id  title     amount     is_done
--  --------  ---------  -------
1   Морковка  2 большие  0      
2   Молоко    1 литр     1      

SELECT * FROM groceries LIMIT 2 OFFSET 2;

id  title   amount   is_done
--  ------  -------  -------
3   Масло   1 пачка  0      
4   Бананы  1-2 кг   1      

SELECT * FROM groceries LIMIT 2 OFFSET 4;

id  title   amount     is_done
--  ------  ---------  -------
5   Сыр     200 грамм  0      
6   Яблоки  500 грамм  1    

Комбинируя LIMIT и OFFSET, можно организовать эффект переключения страницы, когда выводится ограниченный список элементов на экран, а, для того чтобы увидеть следующие надо нажать кнопку "Далее".