Вставка и модификация данных — Основы реляционных баз данных

Работаем с данными в таблице
Выводы

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

hexlet=> \d courses
               Table "public.courses"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 name          | character varying(255) |
 slug          | character varying(255) |
 lessons_count | integer                |
 body          | text                   |

Работаем с данными в таблице

С данными в SQL работают с помощью подмножества DML (Data Manipulation Language). Оно включает:

INSERT — запрос на вставку данных
UPDATE — запрос на обновление данных
DELETE — запрос на удаление данных

Разберем каждый запрос подробнее.

Вставка данных в таблицу

Чтобы вставить данные в таблицу, нужно выполнить INSERT INTO запрос:

-- Числовое значение указывается без дополнительных символов,
-- а строковые взяты в одинарные кавычки
INSERT INTO courses (name, slug, lessons_count, body)
  VALUES ('basics of programming', 'basics', 10, 'this is theory');

INSERT INTO courses — запрос на вставку в таблицу courses
(name, slug, lessons_count, body) — список полей, которые нужно вставить
('basics of programming', 'basics', 10, 'this is theory') — значения вставленных полей в том же порядке

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

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

INSERT INTO courses (name, slug) VALUES ('Bash', 'bash');

NULL — это специальное значение и оно одно. Пустая строка, 0 и тому подобные вещи не являются NULL.

INSERT позволяет вставить сразу несколько записей в рамках одного запроса. Для этого достаточно перечислить все необходимые значения в круглых скобках через запятую после ключевого слова VALUES:

-- В результате в таблице добавится три записи
INSERT INTO courses (name, slug) VALUES
  ('Bash', 'bash'), ('PHP', 'php'), ('Ruby', 'ruby');

Поля можно не указывать, тогда это равносильно их полному перечислению:

-- Значения идут в том же порядке, в котором определены поля в таблице
INSERT INTO courses VALUES ('linux', 'linux', 3, 'something about linux');

Чтобы извлечь данные и посмотреть содержимое таблицы, используем запрос SELECT:

hexlet=> SELECT * FROM courses;
         name          |  slug  | lessons_count |         body
-----------------------+--------+---------------+-----------------------
 basics of programming | basics |            10 | this is theory
 bash                  | bash   |               |
 linux                 | linux  |             3 | something about linux
(3 rows)

Запрос SELECT не является частью DML. Подробнее о нем поговорим в следующем уроке.

Обновление или изменение данных

Обновление записей в таблице выполняется UPDATE запросом:

UPDATE courses SET body = 'updated!' WHERE slug = 'bash';

Обычно UPDATE запрос состоит из двух частей:

SET body = 'updated!' — описывается, какое значение установить в какое поле
WHERE slug = 'bash' — указывается ограничение на то, к каким строкам применить обновление

Символ «=» в WHERE — это не присваивание, а проверка на совпадение. В SET равно используется как обычное присваивание.

Поскольку в нашей таблице только одна запись удовлетворяет условию slug = 'bash', то и обновится одна запись. Если бы их было больше, то обновились бы все соответствующие записи.

UPDATE в примере выше можно выполнять безопасно любое количество раз. После первого запроса дальнейшие его вызовы не сделают никаких изменений.

За одну операцию можно обновить несколько полей. Для этого достаточно перечислить каждое присваивание через запятую в части SET. Порядок, в котором изменяются поля, — не важен:

UPDATE courses SET body = 'updated!', name = 'Bash' WHERE slug = 'bash';

Часть WHERE можно вообще не указывать:

UPDATE courses SET body = 'oops';

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

Проверка на точное соответствие это самый простой способ сравнения, которого не всегда достаточно. SQL позволяет выполнять значительно более хитрые проверки. Рассмотрим несколько примеров:

Операции сравнения

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

-- Количество уроков больше трех
UPDATE courses SET name = 'new name' WHERE lessons_count > 3;
-- Количество уроков меньше двух
UPDATE courses SET name = 'another new name' WHERE lessons_count < 2;

Логические операции

Мы можем использовать логические операции для объединения условий: И и ИЛИ.

-- slug равен 'bash' И количество уроков больше 3
UPDATE courses SET name = 'new name'
  WHERE slug = 'bash' AND lessons_count > 3;

-- количество уроков меньше 2 ИЛИ количество уроков больше 8
UPDATE courses SET name = 'another new name'
  WHERE lessons_count < 2 OR lessons_count > 8;

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

-- slug равен 'linux' ИЛИ
-- количество уроков должно быть от 2 до 8
UPDATE courses SET name = 'another new name'
  WHERE (lessons_count > 2 AND lessons_count < 8) OR slug = 'linux';

Удаление данных

Последний запрос из DML — DELETE. С его помощью можно удалить данные из таблицы:

DELETE FROM courses WHERE slug = 'bash';

Этот запрос работает практически так же, как и UPDATE. Только здесь ничего не надо указывать после DELETE.

Такой запрос опасно выполнять. Он отработает, даже если не указана секция WHERE. Секция WHERE в разных видах запросов работает идентично — все, что описывалось для UPDATE, справедливо и для DELETE.

В базах данных есть еще один способ удалять данные в таблице — TRUNCATE. Это не часть стандарта, но реализуется большинством баз данных. У этого запроса есть две особенности:

Он предназначен для полной очистки таблиц
В отличие от DELETE, он выполняется очень эффективно, так как у TRUNCATE нет возможности задавать условия. В этом случае СУБД не нужно ничего дополнительно анализировать. Она сразу очищает место на диске без промежуточных этапов:

TRUNCATE courses;

Выводы

В этом уроке мы рассмотрели этап добавления данных в таблицу. Кроме того, их можно обновлять и удалять. С последними двумя запросами стоит быть осторожными, так как при обновлении всех записей могут быть серьезные последствия. А с помощью DELETE можно удалить всю информацию безвозвратно.