Понимание SQL. Глава 16. Использование подзапросов с командами модификации. [SQL.RU]

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.

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С 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 следовательно не будут переданы в основной предикат.

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

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.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 который обладает не самым понятным логическим порядком. Сначала мы сделали запрос таблицы которая уже заполнена данными. Потом мы показали как можно фактически помещать эти значения изначально. Но, как вы видите, полное ознакомление с запросами здесь неоценимо. Теперь, когда мы показали вам как заполнять значениями таблицы которые уже были созданы (по определению) , мы покажем( со следующей главы) откуда появились эти таблицы.

РАБОТА С SQL

1. Предположите, что имеется таблица называемая Multicust, с такими же именами столбцов что и таблица Продавцов. Напишите команду, которая бы вставила всех продавцов (из таблицы Продавцов)имеющих более чем одного заказчика в эту таблицу.

2. Напишите команду которая бы удаляла всех заказчиков не имеющих текущих порядков.

3. Напишите команду которая бы увеличила на двадцать процентов комиссионные всех продавцов имеющих общие текущие порядки выше чем $3,000.

( См. Приложение A для ответов. )