В этой главе вы узнаете, как использовать подзапросы в командах модификации. Вы обнаружите, что нечто подобное вы уже видели при использовании подзапросов в запросах. Понимание того, как подзапросы используются в командах SELECT, сделает их применение в командах модификации более уверенным, хотя и останутся некоторые вопросы. Завершением команды SELECT является подзапрос, но не предикат, и поэтому его использование отличается от использования простых предикатов с командами модификации, которые вы уже выполняли раннее с командами UPDATE и DELETE. Вы использовали простые запросы, чтобы производить значения для INSERT, а теперь мы можем расширить эти запросы, чтобы включать в них подзапросы.
Важный принцип, который надо соблюдать при работе с командами модификации: нельзя в предложении FROM любого подзапроса модифицировать таблицу, к которой обращаетесь с помощью основной команды. Это относится ко всем трём командам модификации. Хотя имеется большое количество ситуаций, в которых будет полезно сделать запрос той таблицы, которую вы хотите модифицировать, причем во время её модификации, это слишком усложняет операцию, чтобы использовать её на практике.
Не делайте ссылки к текущей строке таблицы, указанной в команде, которая является соотнесённым подзапросом.
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.
Вы можете также использовать подзапросы в предикате команды DELETE. Это даст вам возможность определять некоторые довольно сложные критерии, чтобы установить, какие строки будут удаляться, что важно, так как вы, конечно же, не захотите по неосторожности удалить нужную строку. Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос чтобы удалить всех заказчиков, назначенных продавцам в Лондоне:
DELETE FROM Customers WHERE snum = ANY (SELECT snum FROM Salespeople WHERE city = 'London');
Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Pereira (назначенного для Motika). Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалить или изменить строки Peel и Motika.
Это важно. Обычно, когда мы делаем модификацию в базе данных, которая повлечет другие модификации, наше первое желание - сделать сначала основное действие, а затем проследить другие, вторичные. Этот пример, покажет, почему более эффективно делать наоборот, выполнив сначала вторичные действия.
Если, например, вы решили изменить значения полей city ваших продавцов везде, где они переназначены, вы должны рассмотреть всех этих заказчиков более сложным способом.
Так как реальные БД имеют тенденцию разрастаться до значительно больших размеров, чем наши небольшие типовые таблицы, это может стать серьезной проблемой. SQL может предоставить некоторую помощь в этой области, используя механизм справочной целостности (обсуждённый в Главе 19), но это не всегда доступно и не всегда применимо.
Хотя вы не можете обращаться к таблице, из которой вы будете удалять строки, в предложении FROM подзапроса, вы можете в предикате сослаться на текущую строку-кандидат этой таблицы, которая является строкой, которая проверяется в основном предикате. Другими словами, вы можете использовать соотнесённые подзапросы. Они отличаются от тех соотнесённых подзапросов, которые вы могли использовать с INSERT, в котором они фактически базировались на строках-кандидатах таблицы, задействованной в команде, а не на запросе другой таблицы.
DELETE FROM Salespeople
WHERE EXISTS
(SELECT *
FROM Customers
WHERE rating = 100
AND Salespeople.snum = Customers.snum);
Обратите внимание, что часть AND предиката внутреннего запроса обращается к таблице Продавцов. Это означает, что весь подзапрос будет выполняться отдельно для каждой строки таблицы Продавцов, так же, как это выполнялось с другими соотнесенными подзапросами. Эта команда удалит всех продавцов, которые имели по меньшей мере одного заказчика с оценкой 100 в таблице Продавцов. Конечно же, имеется другой способ сделать то же:
DELETE FROM Salespeople WHERE 100 IN (SELECT rating FROM Customers WHERE Salespeople.snum = Customers.snum);
Эта команда находит все оценки для каждого заказчика продавцов и удаляет тех продавцов, заказчики которых имеют оценку = 100.
Обычно соотнесённые подзапросы это подзапросы, связанные с таблицей, к которой они ссылаются во внешнем запросе (а не в самом предложении DELETE), и так же часто используемые. Вы можете найти наименьший заказ на каждый день и удалить продавцов, которые произвели его, с помощью следующей команды:
DELETE FROM Salespeople WHERE snum IN (SELECT snum FROM Orders WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odate = b.odate));
Подзапрос в предикате DELETE принимает соотнесённый подзапрос. Этот внутренний запрос находит минимальный заказ суммы приобретений для даты каждой строки внешнего запроса. Если эта сумма - такая же, как и сумма текущей строки, предикат внешнего запроса верен, что означает, что текущая строка имеет наименьший заказ для этой даты. Поле snum продавца, ответственного за этот заказ, извлекается и передается в основной предикат команды DELETE, которая затем удаляет все строки с этим значением поля snum из таблицы Продавцов (так как snum это первичный ключ таблицы Продавцов, то, естественно, там должна иметься только одна удаляемая строка для значения поля snum, выведенного с помощью подзапроса. Если имеется больше одной строки, все они будут удалены.) Поле snum = 1007, которое будет удалено, имеет наименьшее значение на 3 октября; поле snum = 1002, наименьшее на 4 октября; поле snum = 1001, наименьшее в заказах на 5 октября (эта команда кажется довольно грубой, особенно когда она удаляет Peel создавшего единственный заказ на 5 октября, но зато это хорошая иллюстрация).
Если вы хотите сохранить Peel, вы могли бы добавить другой подзапрос, который сделал бы это:
DELETE FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Orders a
WHERE amt =
(SELECT MIN (amt)
FROM Orders b
WHERE a.odate = b.odate)
AND 1 <
(SELECT COUNT onum
FROM Orders b
WHERE a.odate = b.odate));
Теперь для дня, в котором был создан только один заказ, будет произведен счёт = 1 во втором соотнесённом подзапросе. Это сделает предикат внешнего запроса неправильным, и поля snum, следовательно, не будут переданы в основной предикат.
UPDATE использует подзапросы тем же самым способом, что и DELETE, внутри этого необязательного предиката. Вы можете использовать соотнесённые подзапросы в форме, пригодной для использования с DELETE - связанной или с модифицируемой таблицей, или с таблицей вызываемой во внешнем запросе. Например, с помощью соотнесённого подзапроса к таблице, которая будет модифицироваться, вы можете увеличить комиссионные всех продавцов которые были назначены по крайней мере двум заказчикам:
UPDATE Salespeople
SET comm = comm + .01
WHERE 2 < =
(SELECT COUNT (cnum)
FROM Customers
WHERE Customers.snum =
Salespeople.snum);
Теперь продавцы Peel и Serres, имеющие нескольких заказчиков, получат повышение своих комиссионных. Имеется разновидность последнего примера из предыдущего раздела с DELETE. Он уменьшает комиссионные продавцов которые оформили наименьшие заказы, но не стирает их в таблице:
UPDATE Salespeople
SET comm = comm - .01
WHERE snum IN
(SELECT snum
FROM Orders a
WHERE amt =
(SELECT MIN (amt)
FROM Orders b
WHERE a.odat = b.odate));
Невозможность обратиться к таблице, задействованной в любом подзапросе, из команды модификации (UPDATE) исключает целые категории возможных действий. Например, вы не можете просто выполнить такую операцию как удаление всех заказчиков с оценками ниже средней. Вероятно, лучше всего вы могли бы сначала (Шаг 1.) выполнить запрос, получающий среднюю величину, а затем (Шаг 2.) удалить все строки с оценкой ниже этой величины.
Шаг 1.
SELECT AVG (rating)
FROM Customers;
Вывод = 200.
Шаг 2.
DELETE
FROM Customers
WHERE rating < 200;
Теперь вы овладели тремя командами, которые управляют всем содержимым вашей БД. Осталось только несколько общих вопросов относительно ввода и стирания значений таблицы, когда, например, эти команды могут выполниться данным пользователем в данной таблице и когда действия, сделанные ими, становятся постоянными.
Подведём итог: вы используете команду INSERT, чтобы добавлять строки в таблицу. Вы можете или дать имена значениям этих строк в предложении VALUES (когда только одна строка может быть добавлена), или вывести значения с помощью запроса (когда любое число строк можно добавить одной командой).
Если используется запрос, он не может обращаться к таблице, в которую вы делаете вставку, каким бы способом вы её ни делали, ни в предложении FROM, ни с помощью внешней ссылки (как это делается в соотнесённых подзапросах). Всё это относится к любым подзапросам внутри этого запроса.
Запрос, однако, оставляет вам свободу использования соотнесённых подзапросов или подзапросов, которые дают в предложении FROM имя таблице, которое уже было указано в предложении FROM внешнего запроса (это - общий случай для запросов).
DELETE и UPDATE используются, чтобы, соответственно, удалить строки из таблицы и изменить в них значения. Оба они применимы ко всем строкам таблицы, если не используется предикат, определяющий, какие строки должны быть удалены или модифицированы. Этот предикат может содержать подзапросы, которые могут быть связаны с таблицей, удаляемой или модифицируемой с помощью внешней ссылки. Эти подзапросы, однако, не могут ссылаться на таблицу, модифицируемой любым предложением FROM.
Может показаться, что мы прошли материал SQL, который обладает не самой понятной логикой. Сначала мы сделали запрос таблицы, которая уже заполнена данными. Потом мы показали, как можно фактически помещать эти значения изначально. Но, как вы видите, полное ознакомление с запросами здесь неоценимо.
Теперь, когда мы показали вам, как заполнять значениями таблицы, которые уже были созданы (по определению), мы покажем (начиная со следующей главы), откуда появляются эти таблицы.
1. Предположите, что имеется таблица Multicust с такими же именами столбцов, что и в таблице Продавцов. Напишите команду, которая вставила бы всех продавцов (из таблицы Продавцов), имеющих более чем одного заказчика, в эту таблицу. 2. Напишите команду, которая удаляла бы всех заказчиков не имеющих текущих заказов. 3. Напишите команду, которая увеличила бы на двадцать процентов комиссионные всех продавцов, имеющих сумму текущих заказов выше $3,000. (См. ответы в Приложении A.)