Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 15. Ввод, удШ1ение и изменеиие з11ачений полей

В этой главе рассматриваются команды, управляющие представленными в таб­

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

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

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

Команды обновления DML

Данные заносятся в поля и исключаются из них с помощью трех команд языка ма­

нипулирования данными (Data Manipulation Language - DML: INSERT (вставить),

UPDATE (обновить) и DELETE (удалить). В SQL их часто называют командами об­

новления (update commands).

Ввод значений

Все строки в SQL вводятся при помощи команды обновления INSERT. В простей­ шем случае команда INSERT имеет такой синтаксис:

INSERT INTO <имя таблицы>

VALUES (<значение>,<значение> ... );

Например, для того чтобы ввести строку в таблицу Salespeople, можно использо­

вать следующее предложение:

INSERT INTO Salespeople

VALUES (1001, "Peel", "London',.12);

Команды языка манипулирования данными не генерируют выходных данных, нр

программа должна уведомлять пользователя о том, что данные добавлены. Имя табли­ цы (в данном случае Salespeople) должно быть определено предварительно (до выпол­ нения команды INSERT) с помощью команды CREATE TABLE (см. главу 17), а

каждое значение в списке значений должно иметь тип данных, соответствующий типу

данных столбца, в который это значение должно быть вставлено. Согласно стандарту ANSI, эти значения не могут включать выражения. Отсюда следует, что значение 3 до­ пустимо, а 1 + 2 - недопустимо. Значения, конечно, вводятся в таблицу в порядке следования столбцов таким образом, что первое из значений, указанных в списке VALUES команды INSERT, вводится автоматически в столбец с номером 1, второе - в столбец с номером 2 и т.д.

160

Ввод значений

Вставка NULL-значений

Если нужно вставить NULL-значение, необходимо указать его как обычное зна­ чение. Предположим, значение поля city для Ms.Peel еще неизвестно. В этом случае для нее можно вставить строку, указав значение NULL в столбце city, следующим образом:

INSERT INTO Salespeople

VALUES (1001, 'Peel', NULL,.12};

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

Именование столбцов для INSERT

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

из напечатанного отчета, в котором интересующие сведения представлены в таком по­

рядке: city,cnaшe,cnum. Для простоты желательно вводить значения в порядке, указан­

ном в напечатанном отчете. Можно воспользоваться командой:

INSERT INTO Customers (city, cname, cnum}

VALUES ('London·, 'Hoffman·, 2001};

Столбцы с именами rating и snuш опущены. Это означает, что для каждого из них автоматически назначаются значения по умолчанию. По умолчанию может быть уста­ новлено либо значение NULL, либо вполне определенное значение. Если ограничения целостности не допускают использования NULL-значения в данном столбце и для столбца не назначено значение "по умолчанию", то в такой столбец можно занести значение с помощью любой команды INSERT (информация по поводу ограничений на NULL-значения и использованию значений "по умолчанию" содержится в главе 18).

Вставка результатов запроса

Команду INSERT можно применить для того, чтобы извлечь значения из одной таб­

лицы и разместить их в другой, воспользовавшись для этого запросом. Для этого доста­

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

INSERT INTO Londonstaff

SELECT •

FROM Salespeople

WHERE city = 'London';

По этой команде все значения, полученные с помощью запроса (т.е. все строки таб­ лицы Salespeople, для которых значение поля city = 'London'), размещаются в таблице с

161

Глава 15. Ввод, удаление и изменение значеиий полей

именем Londonstaff. Чтобы избежать проблем при выполнении команды, таблица Londonstaff должна удовлетворять следующим условиям:

•Она должна быть уже создана с помощью команды CREAТЕ ТABLE.

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

Основное правило, в соответствии с которым столбцы таблицы вставляются, за­ ключается в соответствии столбцов таблицы столбцам выходных данных запроса, в данном случае, таблице Salespeople целиком.

Londonstaff является здесь независимой таблицей, имеющей ряд значений, совпа­ дающих со значениями таблицы Salespeople. Если значения в таблице Salespeople из­ менить, то эти изменения не отразятся на значениях, хранящихся в таблице Londonstaff (хотя эффект согласованного изменения данных можно создать, определив представления). Поскольку и запрос, и команда INSERT могут указывать столбцы по имени, при желании можно переставить выбираемые столбцы (указать имена в коман­ де SELECT) или указать имена вставляемых столбцов в произвольном порядке (ука­ зать имена в команде INSERT).

Предположим, решено создать новую таблицу с именем Daytotals, которая будет отслеживать объемы заказов за каждый день. Допустим, необходимо ввести эти дан­ ные независимо от таблицы Orders, воспользовавшись при этом уже имеющимися в ней данными. Предположим, таблица Orders содержит данные по последнему финан­ совому году, а не за несколько дней, как в нашем примере. В этом случае преимущест­ ва использования предложения INSERT для вычисления и ввода значений вполне

очевидны:

INSERT INTO Daytotals (date, total)

SELECT odate, SUM (amt)

FROM Orders

GROUP ВУ odate;

Заметим, что имена столбцов для двух таблиц Orders и Daytotals не совпадают. Но если date и total являются единственными столбцами таблицы и следуют в ней в ука-

Исключение строк из таблицы

Строки из таблицы можно исключить с помощью команды обновления DELETE. По

этой команде исключаются только целые строки, а не отдельные значения полей; таким

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

162

Из.111еиение з11аче11ий llОлей

воспринимается как ошибочный аргумент. Для исключения всех строк таблицы Salespeople, следует ввести следующее предложение:

DELEТE FROM Salespeople;

В результате выполнения этой команды таблица становится пустой и ее можно уда­ лить по команде DROP TABLE (команда объясняется в главе 17).

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

DELEТE FROM Salespeople WHERE snum = 1003;

Поле sпum используется вместо поля sпame, поскольку наилучший способ при уда­ лении единственной строки - это указать значение ее первичного ключа. Примене­

ние первичного ключа гарантирует удаление единственной строки.

Можно употребить и предикат, выбирающий группу строк, например:

DELETE FROM Salespeople WHERE city = 'London';

Изменение значений полей

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

менение (изменения), которое необходимо выполнить для определенного столбца

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

следует ввести:

UPDATE Customers

SET rating = 200;

Обновление только 011.ределенных строк

Замена значения столбца во всех строках таблицы, как правило, не нужна. Поэтому в команде UPDATE, как и в команде DELETE, можно использовать предикат. Для выполне­ ния указанной замены значений столбца rating, для всех покупателей, которые обслужива­ ются продавцом Peel (snum = 1001), следует ввести:

UPDATE Customers

SЕТ rating 200

WHERE snum 1001;

163

Глава 15. Ввод, удале11ие и изме11е11ие з11аче11ий полей

UPDATE для мно:ж:ества столбцов

Нет нужды оrраничиваться обновлением значения единственного столбца в результате выполнения команды UPDATE. В предложении SET можно указать любое количество значений для столбцов, разделенных запятыми. Все указанные изменения выполняются для каждой строки таблицы, удовлетворяющей предикату. В каждый момент времени об­ рабатывается одна строка таблицы. Предположим, Motika заменена новым продавцом (salesperson), необходимо сохранить ее персональный номер, но в соответствующую стро­ ку таблицы внести данные о новом продавце:

В результате выполнения команды все покупатели продавца Motika со своими зака­ зами перейдут к Gibson, поскольку они связаны с Motika по значению поля snum.

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

... SЕТ Salespeople.sname = 'Gibson'

в команде UPDATE, можно только:

. . . SЕТ sname = ·Gibson · ...

Использование выраJ1сений в UPDATE

В предложении SET команды UPDATE можно использовать скалярные выражения, указывающие способ изменения значений поля в отличие от предложения VALUES команды INSERT, в котором нельзя использовать выражения. Это весьма полезная ха­ рактеристика. Предположим, решено удвоить комиссионные продавцов. Можно ис­

пользовать следующее выражение:

UPDATE Salespeople

SЕТ comm = comm•2;

Поскольку есть ссылка на значение существующего столбца в предложении SET, для каждой текущей строки выбирается значение указанного столбца, над которым выполняется заданная операция (в данном случае значение увеличивается в два раза). Можно комбинировать отдельные компоненты предложения UPDATE. Например,

можно изменить значение комиссионных только для продавцов из Лондона с помо­

щью предложения:

UPDATE Salespeople

SET comm = comm•2

WHERE city = 'London';

164