Манипулирование данными

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

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

Только что созданная таблица не содержит данных. Первое, что нужно сделать чтобы база данных начала приносить пользу, — это добавить в таблицу данные. Данные вставляются по одной строке за раз. Хотя, конечно, за одну команду можно вставить и более одной строки, но вставить менее одной строки (неполную строку) невозможно. Даже если известны только некоторые значения столбцов, необходимо создать строку целиком.

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

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

Пример команды для добавления строки:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

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

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

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

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

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

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

Вторая форма является расширением QHB. Она заполняет столбцы слева заданными значениями, а остальные получают значения по умолчанию.

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

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

Можно вставить несколько строк одной командой:

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

Кроме того, можно вставить результат запроса (который может вообще не содержать строк или содержать одну или несколько строк):

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

Это позволяет в полной мере использовать механизм запросов SQL (глава Запросы) для вычисления добавляемых строк.

Совет
При одномоментном добавлении большого объема данных имеет смысл использовать команду COPY. Она не такая гибкая, как команда INSERT, но более эффективна. Дополнительную информацию об увеличении производительности при массовой загрузке данных см. в разделе Заполнение базы данных.

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

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

Чтобы изменить существующие строки, используйте команду UPDATE. Для этого требуется знать три вещи:

  1. Имя таблицы и изменяемого столбца

  2. Новое значение столбца

  3. Какую строку (строки) изменять

Напоминаем, что в главе Определение данных говорилось, что SQL, как правило, не предоставляет уникальный идентификатор строк. Поэтому не всегда возможно напрямую указать, какую строку нужно изменить. Вместо этого указывается, каким условиям должна соответствовать изменяемая строка. Только если в таблице имеется первичный ключ (независимо от того, объявлен он или нет), можно с уверенностью обращаться к отдельным строкам, выбирая условие, соответствующее первичному ключу. На этот факт опираются графические инструменты доступа к базе данных, позволяя изменять строки по отдельности.

Например, эта команда увеличивает цену на все товары с ценой 5 до 10:

UPDATE products SET price = 10 WHERE price = 5;

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

Давайте посмотрим на эту команду подробно. Сначала идет ключевое слово UPDATE, потом имя таблицы. Как обычно, имя таблицы может быть дополнено схемой; в противном случае оно ищется в пути. Далее следует ключевое слово SET, а за ним — имя столбца, знак равенства и новое значение столбца. Новое значение столбца может быть любым скалярным выражением или просто константой. Например, если нужно поднять цену на все товары на 10%, можно использовать:

UPDATE products SET price = price * 1.10;

Как видите, выражение для нового значения может ссылаться на существующие значения в строке. Кроме того, отсутствует предложение WHERE. Если оно опущено, это означает, что будут изменены все строки в таблице. Если условие WHERE указано, изменяются только те строки, которые ему соответствуют. Обратите внимание, что знак равенства в предложении SET является присваиванием, а в предложении WHERE — сравнением, но это не создает никакой двусмысленности. Конечно, условию WHERE не обязательно быть проверкой равенства. Доступны и многие другие операторы (см. главу Функции и операторы). Но выражение должно вычисляться до логического результата.

В команде UPDATE можно изменить несколько столбцов, перечислив присваиваемые им значения в предложении SET. Например:

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

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

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

Для удаления строк используется команда DELETE; ее синтаксис очень похож на команду UPDATE. Например, чтобы удалить все строки из таблицы товаров с ценой 10, напишите следующее:

DELETE FROM products WHERE price = 10;

Если просто написать:

DELETE FROM products;

будут удалены все строки в таблице! БУДЬТЕ ОСТОРОЖНЫ.

Возврат данных из измененных строк

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

Допустимое содержимое предложения RETURNING совпадает с выходным списком команды SELECT (см. раздел Списки выборки). Он может содержать имена столбцов целевой таблицы команды или выражения значений, использующие эти столбцы. Обычным сокращением является RETURNING *, выбирающее все столбцы целевой таблицы по порядку.

В команде INSERT доступные для RETURNING данные образуются из строки в том виде, в котором она была добавлена. Это не очень полезно при обычных добавлениях, поскольку возвращаются данные, предоставленные клиентом. Но это может быть очень удобно при использовании вычисленных значений по умолчанию. Например, при использовании столбца serial, предоставляющего уникальные идентификаторы, RETURNING может вернуть идентификатор, назначенный новой строке:

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

Предложение RETURNING также очень полезно с INSERT ... SELECT.

В команде UPDATE доступные для RETURNING данные образуются из нового содержимого модифицированной строки. Например:

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

В команде DELETE доступные для RETURNING данные образуются из содержимого удаленной строки. Например:

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

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

Copyright © KVANTOM LLC, 2019-2023