Понимание SQL - Мартин Грубер

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

UPDATE Salespeople

SET comm=comm * 2;

Всякий раз, когда вы ссылаетесь к указанному значению столбца в предложении SET, произведенное значение может получится из текущей строки, прежде в ней будут сделаны какие-то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности, и сказать, - удвоить комиссию всем продавцам в Лондоне, таким предложением:

UPDATE Salespeople

SET comm=comm * 2

WHERE city='London';

МОДИФИЦИРОВАНИЕ ПУСТЫХ(NULL) ЗНАЧЕНИЙ

Предложение SET - это не предикат. Он может вводить пустые NULL значения также как он вводил значения не используя какого-то специального синтаксиса (такого например как IS NULL). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение:

UPDATE customers

SET rating=NULL

WHERE city='London';

что обнулит все оценки заказчиков в Лондоне.

РЕЗЮМЕ

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

INSERT - используемой чтобы помещать строки в базу данных;

DELETE - чтобы удалять их;

REFERENCES - чтобы изменять значения в уже вставленных строках.

Вы обучались использованию предиката с командами UPDATE и DELETE чтобы определять, на которую из строк будет воздействовать команда.

Конечно, предикаты как таковые - не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако, вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причем это, вы можете делать со столбцами в любом порядке.

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

Следующая глава расширит ваше познания, показав вам, как использовать подзапросы с этими командами. Эти подзапросы напоминают те, с которыми вы уже знакомы, но имеются некоторые специальные выводы и ограничения, когда подзапросы используются в командах DML, что мы будем обсуждать в Главе 16.

РАБОТА С SQL

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

city - San Jose,

name - Bianco,

comm - NULL,

* cnum - 1100.

* Напишите команду которая бы удалила все порядки заказчика Clemens из таблицы Порядков.

* Напишите команду которая бы увеличила оценку всех заказчиков в Риме на 100.

* Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

Глава 16. ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С КОМАНДАМИ МОДИФИКАЦИИ

В ЭТОЙ ГЛАВЕ, ВЫ УЗНАЕТЕ КАК ИСПОЛЬЗОВАТЬ подзапросы в командах модификации. Вы найдете, что нечто подобное - вы уже видели при использовании подзапросов в запросах. Понимание, как подзапросы используются в командах SELECT, cделает их применение в командах модификации более уверенным, хотя и останутся некоторые вопросы.

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

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

Не делайте ссылки к текущей строке таблицы указанной в команде, которая является соотнесенным подзапросом.

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С INSERT

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

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

INSERT INTO SJpeople

SELECT *

FROM Salespeople

WHERE city='San Jose';

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

INSERT INTO SJpeople

SELECT *

FROM Salespeople

WHERE snum=ANY

( SELECT snum

FROM Customers

WHERE city=' (San (Jose' );

Оба запроса в этой команде функционируют также как если бы они не являлись частью выражения INSERT. Подзапрос находит все строки для заказчиков в San Jose и формирует набор значений snum. Внешний запрос выбирает строки из таблицы Salespeople, где эти значения snum найдены.

В этом примере, строки для продавцов Rifkin и Serres, которые назначены заказчикам в San Jose - Liu и Cisneros, будут вставлены в таблицу SJpeople.

НЕ ВСТАВЛЯЙТЕ ДУБЛИКАТЫ СТРОК

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

Если имеются любые ограничения в таблице SJpeople которые вынуждают ее значения быть уникальными, эта вторая вставка потерпит неудачу (как это и должно было быть). Двойные строки это плохо. (См. Главу 18 для подробностей об ограничениях. )

Было бы лучше если бы вы могли как-то выяснить, что эти значения уже были вставлены в таблицу, прежде чем вы попытаетесь сделать это снова, с помощью добавления другого подзапроса (использующего операторы типа EXISTS, IN, < > ALL, и так далее ) к предикату.

К сожалению, чтобы сделать эту работу, вы должны будете сослаться на саму таблицу SJpeople в предложении FROM этого нового подзапроса, а, как мы говорили ранее, вы не можете ссылаться на таблицу которая задействована (целиком ) в любом подзапросе команды модификации. В случае INSERT, это будет также препятствовать соотнесенным подзапросам, основанным на таблице в которую вы вставляете значения. Это имеет значение, потому что, с помощью INSERT, вы создаете новую строку в таблице. "Текущая строка" не будет существовать до тех пор, пока INSERT не закончит ее обрабатывать.

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ СОЗДАНЫХ ВО ВНЕШНЕЙ ТАБЛИЦЕ ЗАПРОСА

Запрещение на ссылку к таблице которая модифицируется командой INSERT не предохранит вас от использования подзапросов которые ссылаются к таблице используемой в предложении FROM внешней команды SELECT. Таблица из которой вы выбираете значения, чтобы произвести их для INSERT, не будет задействована командой; и вы сможете ссылаться к этой таблице любым способом которыми вы обычно это делали, но только если эта таблица указана в автономном запросе. Предположим что мы имеем таблицу с именем Samecity в которой мы запомним продавцов с заказчиками в их городах.

Мы можем заполнить таблицу используя соотнесенный подзапрос:

INSERT INTO (Samecity

SELECT *

FROM (Salespeople outer

WHERE city IN

( SELECT city

FROM Customers inner

WHERE inner.snum=outer.snum );

Ни таблица Samecity, ни таблица Продавцов не должны быть использованы во внешних или внутренних запросах INSERT. В качестве другого примера, предположим, что вы имеете премию для продавца который имеет самый большой порядок на каждый день. Вы следите за ним в таблице с именем Bonus, которая содержит поле snum продавцов, поле odate и поле amt. Вы должны заполнить эту таблицу информацией которая хранится в таблице Порядков, используя следующую команду:

INSERT INTO Bonus

SELECT snum, odate, amt

FROM Orders a

WHERE amt=

( SELECT MAX (amt)

FROM Orders b

WHERE a.odate=b.odate );

Даже если эта команда имеет подзапрос который базируется на той же самой таблице что и внешний запрос, он не ссылается к таблице Bonus, на которую воздействует команда. Что для нас абсолютно приемлемо.

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