Мартин Грубер - Понимание SQL
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.
Это даст вам возможность определять некоторые довольно сложные критерии чтобы установить, какие строки будут удаляться, что важно, так как вы конечно же не захотите по неосторожности удалить нужную строку.
Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос чтобы удалить всех заказчиков назначенных к продавцам в Лондоне:
DELETE
FROM Customers
WHERE snum=ANY
( SELECT snum
FROM Salespeople
WHERE city='London' );
Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Periera (назначенного к 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 следовательно не будут переданы в основной предикат.
ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С UPDATEUPDATE использует подзапросы тем же самым способом что и 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.odate=b.odate ));
СТОЛКНОВЕНИЕ С ОГРАНИЧЕНИЯМИ ПОДЗАПРОСОВ КОМАНДЫ DMLНеспособность сослаться к таблице задействованой в любом подзапросе из команды модификации (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 который обладает не самым понятным логическим порядком. Сначала мы сделали запрос таблицы которая уже заполнена данными. Потом мы показали как можно фактически помещать эти значения изначально. Но, как вы видете, полное ознакомление с запросами здесь неоценимо. Теперь, когда мы показали вам как заполнять значениями таблицы которые уже были созданы (по определению), мы покажем(со следующей главы) откуда появились эти таблицы.
