UPDATE: Изменение данных

Оператор PostgreSQL UPDATE: Изменение данных

С возвращением в наш учебник по PostgreSQL! В этом уроке мы сосредоточимся на операторе UPDATE в PostgreSQL, который необходим для изменения существующих данных в ваших таблицах базы данных. Понимание того, как эффективно использовать оператор UPDATE, позволяет управлять и поддерживать данные с точностью.

Введение в оператор UPDATE

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

Основной синтаксис

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • UPDATE: Указывает таблицу, в которой будут обновлены данные.
  • SET: Перечисляет столбцы и их новые значения.
  • WHERE: Определяет условие для идентификации строк, которые будут обновлены.

Обновление данных в таблицах PostgreSQL

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

1. Обновление одной строки

Пример: Обновление цены конкретной книги

UPDATE books
SET price = 8.99
WHERE title = '1984';

Просмотреть инициализацию схемы

Объяснение:

  • Цена книги '1984' обновлена до $8.99.
  • Оператор WHERE гарантирует, что будет изменена только строка с названием '1984'.

2. Обновление нескольких строк

Вы можете обновить несколько строк, которые соответствуют определенному условию.

Пример: Увеличить цену всех книг Джейн Остин на $1

UPDATE books
SET price = price + 1
WHERE author = 'Jane Austen';

Объяснение:

  • Цена всех книг, написанных 'Jane Austen', увеличена на $1.

3. Обновление нескольких столбцов

Вы можете обновить несколько столбцов в одном запросе.

Пример: Обновление цены и количества страниц книги

UPDATE books
SET price = 12.99, pages = 200
WHERE title = 'The Great Gatsby';

Объяснение:

  • Книга 'The Great Gatsby' теперь имеет цену $12.99 и 200 страниц.

4. Обновление всех строк

Если не указать условие WHERE, будут обновлены все строки в таблице.

Пример: Применить скидку 10% ко всем книгам

UPDATE books
SET price = price * 0.9;

Объяснение:

  • Цена всех книг снижена на 10%.
  • Будьте осторожны, обновляя все строки.

5. Использование подзапросов в UPDATE

Вы можете использовать подзапросы для назначения значений на основе данных из других таблиц.

Пример: Установить цену книг на уровне средней цены всех книг

UPDATE books
SET price = sub.avg_price
FROM (
    SELECT AVG(price) AS avg_price
    FROM books
) AS sub;

Объяснение:

  • Стоимость каждой книги обновляется до средней цены всех книг.

6. Использование выражения CASE

Выражение CASE позволяет использовать условную логику внутри оператора UPDATE.

Пример: Применение различных скидок в зависимости от цены

UPDATE books
SET price = CASE
    WHEN price > 10 THEN price * 0.85
    ELSE price * 0.95
END;

Объяснение:

  • Книги, стоимость которых превышает $10, получают 15% скидку.
  • Книги, стоимость которых $10 или меньше, получают 5% скидку.

7. Обновление с подзапросами

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

Пример: Увеличить цену книг у авторов, которые написали более одной книги

UPDATE books
SET price = price + 2
WHERE author IN (
    SELECT author
    FROM books
    GROUP BY author
    HAVING COUNT(*) > 1
);

Объяснение:

  • Идентифицируются авторы, которые написали более одной книги.
  • Цены на книги этих авторов увеличены на $2.

8. Использование LIMIT с UPDATE

PostgreSQL позволяет использовать LIMIT с оператором UPDATE, чтобы ограничить количество строк, на которые повлияет изменение.

Пример: Обновление цены первых двух книг в алфавитном порядке

UPDATE books
SET price = price - 1
WHERE ctid IN (
    SELECT ctid
    FROM books
    ORDER BY title
    LIMIT 2
);

Объяснение:

  • Цены первых двух книг в алфавитном порядке снижены на $1.
  • ctid — это системная колонка, уникально идентифицирующая строку в таблице.

9. Возвращение обновленных данных

Оператор RETURNING позволяет возвращать данные из обновленных строк.

Пример: Обновление количества страниц и возврат обновленных книг

UPDATE books
SET pages = pages + 10
RETURNING title, pages;

Объяснение:

  • Ко всем книгам добавлено 10 страниц.
  • Возвращаются обновленные title и pages.

10. Условные обновления с использованием EXISTS

Используйте EXISTS, чтобы условно обновить строки на основе существования связанных данных.

Пример: Обновить книги, только если автор существует в таблице авторов

UPDATE books
SET price = price * 0.95
WHERE EXISTS (
    SELECT 1
    FROM authors
    WHERE authors.name = books.author
);

Объяснение:

  • Книгам предоставляется скидка, только если их автор указан в таблице authors.
  • В нашей схеме все авторы в таблице books существуют в таблице authors, поэтому это обновит все книги.

Заключение

Оператор UPDATE в PostgreSQL — это мощный инструмент для изменения существующих данных. Овладев его различными функциями, вы сможете эффективно управлять и поддерживать данные в вашей базе данных.

Основные выводы:

  • Используйте UPDATE ... SET для изменения данных.
  • Всегда используйте предложение WHERE, чтобы указать, какие строки нужно обновить.
  • Используйте подзапросы и выражения CASE для сложных обновлений.
  • Используйте RETURNING, чтобы получить информацию о обновленных строках.
  • Будьте внимательны при обновлении всех строк или использовании операций, которые затрагивают несколько строк.

Schema Initialization

Чтобы выполнить приведенные примеры, убедитесь, что ваша база данных PostgreSQL настроена с использованием следующей схемы:

-- Создание таблицы books
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Вставка примеров данных в таблицу books
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('Sense and Sensibility', 'Jane Austen', 5.99, 226),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Создание таблицы authors
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Вставка примеров данных в таблицу authors
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Создание таблицы movies
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Вставка примеров данных в таблицу movies
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

Можете смело экспериментировать с оператором UPDATE, используя эту схему, чтобы улучшить ваше понимание того, как изменять данные в PostgreSQL. Удачного кодинга!

INSERT: Добавление новых данных