Вставка и модификация данных — Основы SQL

Оператор INSERT
Оператор UPDATE
- Операции сравнения
- Логические операции
Оператор DELETE
Выводы

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

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

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

Оператор INSERT

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

Имя — Will
Фамилия — Smith
Электронная почта — wsmith09@gmail.com
Псевдоним — AgentJ
Дата рождения — 1968-09-25
Пол — male

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

Идентификатор пользователя — 100
Дата создания записи — 2023-05-01
Пароль пользователя в системе — 11111

Чтобы внести все эти данные в таблицу, напишем запрос с оператором INSERT:

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('AgentJ', 'wsmith09@gmail.com', 'Will', 'Smith', '1968-09-25', 'male', 100, '2023-05-01', '11111');

View on DB Fiddle

Рассмотрим этот запрос подробнее. На первой строке мы написали оператор INSERT INTO, указали имя таблицы, а далее в скобках перечислили поля таблицы, в которые будем добавлять значения.

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

Важно следить за порядком полей в запросе на добавление. Для примера представим, что мы перепутали местами поля gender и id. В таком случае запрос не выполнится:

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('wsmith09@gmail.com', 'AgentJ', 'Will', 'Smith', '1968-09-25', 100, 'male', '2023-05-01', '11111');

View on DB Fiddle

Запрос выдаст ошибку, потому что в этих полях хранятся данные разных типов. Другими словами, мы не сможем записать текст male в поле id, потому что в этом поле должно храниться число.

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

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('wsmith09@gmail.com', 'AgentJ', 'Will', 'Smith', '1968-09-25', 'male', 100, '2023-05-01', '11111');

View on DB Fiddle

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

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('AgentJ', 'wsmith09@gmail.com', 'Will', 'Smith', '1968-09-25', 'male', 100, '2023-05-01', '11111'),
       ('MrBatman', 'benaff@gmail.com', 'Ben', 'Affleck', '1973-08-15', 'male', 101, '2023-05-01', '22222');

View on DB Fiddle

При работе с INSERT важно помнить, что в таблицу нельзя добавить две одинаковые записи. Правильно спроектированная база данных не позволит это сделать, потому что проверяет уникальность заданных полей. Но даже если условия уникальности не заданы, две абсолютно одинаковые строки не получится добавить из-за ограничений самой СУБД.

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

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('Bond007', 'tsconnery30@yahoo.com', 'Sean', 'Connery', '1930-08-25', 'male', 102, '2023-05-01', '33333');

INSERT INTO users (username, email, first_name, last_name, birthday, gender, id, created_at, password_digest)
VALUES ('Bond007', 'tsconnery30@yahoo.com', 'Sean', 'Connery', '1930-08-25', 'male', 102, '2023-05-01', '33333');

View on DB Fiddle

Оператор UPDATE

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

UPDATE users SET username = 'Neo' WHERE email = 'theone@yahoo.com';

View on DB Fiddle

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

SET username = 'Neo' — описывается, какое значение установить в какое поле
WHERE email = 'theone@yahoo.com' — указывается ограничение на то, к каким строкам применить обновление

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

Поскольку в нашей таблице только одна запись удовлетворяет условию WHERE email = 'theone@yahoo.com', то и обновится одна запись. Если бы их было больше, то обновились бы все соответствующие записи.

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

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

UPDATE users SET first_name = 'Thomas', last_name = 'Anderson' WHERE email = 'theone@yahoo.com';

View on DB Fiddle

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

UPDATE users SET first_name = 'John', last_name = 'Doe' ;

View on DB Fiddle

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

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

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

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

UPDATE users SET first_name = 'John' WHERE birthday > '1970-01-01';

View on DB Fiddle

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

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

UPDATE users SET email = 'johndoe@yahoo.com'
  WHERE first_name = 'John' AND last_name = 'Doe';

UPDATE users SET first_name = 'John'
  WHERE birthday BETWEEN '1950-01-01' AND '2000-01-01';

View on DB Fiddle

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

UPDATE users SET username = 'secretagent'
WHERE
    (first_name = 'Sean' AND last_name = 'Connery') OR (birthday = '1968-09-25' AND gender = 'male');

View on DB Fiddle

Оператор DELETE

Чтобы удалить данные, можно воспользоваться оператором DELETE. Следующий запрос удалит из базы строку с пользователем 'MrBatman':

DELETE FROM users WHERE username = 'MrBatman';

View on DB Fiddle

Рассмотрим запрос подробнее:

Сначала мы указываем оператор DELETE FROM
После него идет имя таблицы, из которой удаляем данные
В конце мы указываем условие через оператор WHERE

Можно задавать любые условия, поддерживаемые языком SQL. Например, следующий запрос удалит из таблицы users пользователя с идентификатором 100, а также все записи с датой, где дата рождения пользователя с 1968 года по 2000:

DELETE FROM users
WHERE
    (birthday BETWEEN '1968-01-01' AND '2000-01-01')
    OR (id = 100);

View on DB Fiddle

Если никакое условие указано не будет, то будут удалены все записи из указанной таблицы:

DELETE FROM users;

View on DB Fiddle

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

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

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

TRUNCATE courses;

View on DB Fiddle

Выводы

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

С помощью оператора UPDATE можно обновить все данные или только определенные, подходящие под условие.

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

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