Мартин Грубер - Понимание SQL (991940), страница 26
Текст из файла (страница 26)
Что для нас абсолютно приемлемо.Логика запроса, естественно, должна просматривать таблицу Порядков, и находить для каждой строки максимум порядка сумм приобретений для этой даты. Еслиэта величина — такая же как у текущей строки, текущая строка является наибольшимпорядком для этой даты, и данные вставляются в таблицу Bonus.ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С DELETEВы можете также использовать подзапросы в предикате команды DELETE. Этодаст вам возможность определять некоторые довольно сложные критерии чтобы установить, какие строки будут удаляться, что важно, так как вы конечно же не захотитепо неосторожности удалить нужную строку.Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос, чтобы удалить всех заказчиков, назначенных к продавцам вЛондоне:DELETEFROM CustomersWHERE snum = ANY ( SELECT snumFROM SalespeopleWHERE city = 'London' );Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Periera (назначенного к Motika).Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалит или изменит строки Peel и Motika.Это важно.
Обычно, когда мы делаем модификацию в базе данных, которая повлечет другие модификации, наше первое желание — сделать сначало основное действие, а затем проследить другие, вторичные. Этот пример, покажет, почему болееэффективно делать наоборот, выполнив сначала вторичные действия.Если, например, вы решили изменить значение поля city ваших продавцов везде, где они переназначены, вы должны рассмотреть всех этих заказчиков более сложным способом.Так как реальные базы данных имеют тенденцию развиваться до значительнобольших размеров, чем наши небольшие типовые таблицы, это может стать серьезной проблемой. SQL может предоставить некоторую помощь в этой области, используя механизм справочной целостности (обсужденной в Главе 19), но это не всегдадоступно и не всегда применимо.Хотя вы не можете ссылаться к таблице из которой вы будете удалять строки впредложении FROM подзапроса, вы можете в предикате, сослаться на текущую строку-кандидат этой таблицы — которая является строкой, которая в настоящее времяпроверяется в основном предикате.
Другими словами, вы можете использовать соотнесенные подзапросы. Они отличаются от тех соотнесенных подзапросов, которые вымогли использовать с INSERT, в котором они фактически базировались на строкахкандидатах таблицы, задействованой в команде, а не на запросе другой таблицы.DELETE FROM SalespeopleWHERE EXISTS ( SELECT *FROM CustomersWHERE rating = 100 ANDSalespeople.snum = Customers.snum );Обратите внимание, что AND часть предиката внутреннего запроса ссылается ктаблице Продавцов. Это означает что весь подзапрос будет выполняться отдельнодля каждой строки таблицы Продавцов, также как это выполнялось с другими соотнесенными подзапросами.
Эта команда удалит всех продавцов которые имели по меньшей мере одного заказчика с оценкой 100 в таблице Продавцов.Конечно же, имеется другой способ сделать то же:DELETE FROM SalespeopleWHERE 100 IN ( SELECT ratingFROM CustomersWHERE Salespeople.snum = Customers.snum);Эта команда находит все оценки для каждого заказчика продавцов и удаляет техпродавцов, заказчики которых имеют оценку = 100.Обычно соотнесенные подзапросы — это подзапросы, связанные с таблицей, ккоторой они ссылаются во внешнем запросе (а не в самом предложении DELETE) — итакже часто используемы.
Вы можете найти наинизший порядок на каждый день иудалить продавцов, которые произвели его, с помощью следующей команды:DELETE FROM SalespeopleWHERE (snum IN ( SELECT snumFROM OrdersWHERE amt = ( SELECT MIN (amt)FROM Orders bWHERE a.odate = b.odate ));Подзапрос в предикате DELETE, берет соотнесенный подзапрос. Этот внутренний запрос находит минимальный порядок суммы приобретеий для даты каждой строки внешнего запроса. Если эта сумма такая же, как сумма текущей строки, предикатвнешнего запроса верен, что означает, что текущая строка имеет наименьший порядок для этой даты. Поле snum продавца, ответственного за этот порядок, извлекаетсяи передается в основной предикат команды DELETE, которая затем удаляет все строки с этим значением поля snum из таблицы Продавцов (так как snum — это первичныйключ таблицы Продавцов, то естественно там должна иметься только одна удаляемаястрока для значения поля snum выведенного с помощью подзапроса.
Если имеетсябольше одной строки, все они будут удалены.)Поле snum = 1007, которое будет удалено, имеет наименьшее значение на 3 Октября; поле snum = 1002, наименьшее на 4 Октября; поле snum = 1001, наименьшее впорядках на 5 Октября (эта команда кажется довольно резкой, особенно когда онаудаляет Peel, создавшего единственный порядок на 5 Октября, но зато это хорошаяиллюстрация).Если вы хотите сохранить Peel, вы могли бы добавить другой подзапрос, который бы это делал:DELETE FROM SalespeopleWHERE snum IN ( SELECT snumFROM Orders aWHERE amt = ( SELECT MIN (amt)FROM Orders bWHERE a.odate = b.odate )AND 1 < ( SELECT COUNT onumFROM Orders bWHERE a.odate = b.odate ));Теперь для дня, в котором был создан только один порядок, будет произведенCOUNT = 1 во втором соотнесенном подзапросе.
Это сделает предикат внешнего запроса неправильным, и поля snum следовательно не будут переданы в основнойпредикат.ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С UPDATEUPDATE использует подзапросы тем же самым способом что и DELETE — внутри этого необязательного предиката. Вы можете использовать соотнесенные подзапросы или в форме пригодной для использования с DELETE — связаной или смодифицируемой таблицей или с таблицей вызываемой во внешнем запросе. Например, с помощью соотнесенного подзапроса к таблице которая будет модифицироваться, вы можете увеличить комиссионные всех продавцов которые были назначеныпо крайней мере двум заказчикам:UPDATE SalespeopleSET comm = comm + .01WHERE 2 <= ( SELECT COUNT (cnum)FROM CustomersWHERE Customers.snum = Salespeople.snum );Теперь продавцы Peel и Serres, имеющие многочисленых заказчиков, получатповышение своих комиссионных.Имеется разновидность последнего примера из предыдущего раздела сDELETE.
Он уменьшает комиссионные продавцов, которые произвели наименьшиепорядки, но не стирает их в таблице:UPDATE SalespeopleSET comm = comm - .01WHERE snum IN ( SELECT snumFROM Orders aWHERE amt = ( SELECT MIN (amt)FROM Orders bWHERE a.odate = b.odate ));СТОЛКНОВЕНИЕ С ОГРАНИЧЕНИЯМИ ПОДЗАПРОСОВКОМАНДЫ DMLНеспособность сослаться к таблице, задействованой в любом подзапросе из команды модификации (UPDATE), устраняет целые категории возможных действий.Например, вы не можете просто выполнить такую операцию, как удаление всехзаказчиков с оценками ниже средней. Вероятно лучше всего вы могли бы сначала(Шаг 1.), выполнить запрос, получающий среднюю величину, а затем (Шаг 2.), удалитьвсе строки с оценкой ниже этой величины:Шаг 1.SELECT AVG (rating)FROM Customers;Вывод = 200.Шаг 2.DELETEFROM CustomersWHERE rating < 200;РЕЗЮМЕТеперь вы овладели тремя командами, которые управляют всем содержаниемвашей базы данных.
Осталось только несколько общих вопросов относительно вводаи стирания значений таблицы, когда, например, эти команды могут выполниться данным пользователем в данной таблице и когда действия сделанные ими, становятсяпостоянными.Подведем итог: Вы используете команду INSERT чтобы добавлять строки в таблицу. Вы можете или дать имена значениям этих строк в предложении VALUES (когдатолько одна строка может быть добавлена), или вывести значения с помощью запроса(когда любое число строк можно добавить одной командой).
Если используется запрос, он не может ссылаться к таблице, в которую вы делаете вставку, каким бы способом Вы ее ни делали, ни в предложении FROM, ни с помощью внешней ссылки (какэто делается в соотнесенных подзапросах). Все это относится к любым подзапросамвнутри этого запроса.Запрос, однако, оставляет вам свободу использования соотнесенных подзапросов или подзапросов, которые дают в предложении FROM имя таблице, которое ужебыло указано в предложении FROM внешнего запроса (это — общий случай для запросов).DELETE и UPDATE используются чтобы, соответственно, удалить строки из таблицы и изменить в них значения.
Оба они применимы ко всем строкам таблицы, еслине используется предикат, определяющий, какие строки должны быть удалены илимодифицированы. Этот предикат может содержать подзапросы, которые могут бытьсвязаны с таблицей, удаляемой, или модифицированой, с помощью внешней ссылки.Эти подзапросы, однако, не могут ссылать к таблице модифицируемой любым предложением FROM.Может показаться, что мы прошли материал SQL, который обладает не самымпонятным логическим порядком. Сначала мы сделали запрос таблицы, которая ужезаполнена данными. Потом мы показали как можно фактически помещать эти значения изначально. Но, как вы видете, полное ознакомление с запросами здесь неоценимо.Теперь, когда мы показали вам как заполнять значениями таблицы, которые ужебыли созданы (по определению), мы покажем (со следующей главы), откуда появились эти таблицы.РАБОТА С SQL1. Предположите, что имеется таблица, называемая Multicust, с такими же именамистолбцов, что и таблица Продавцов.
Напишите команду, которая бы вставила всехпродавцов (из таблицы Продавцов) имеющих более чем одного заказчика в этутаблицу.2. Напишите команду, которая бы удаляла всех заказчиков, не имеющих текущих порядков.3. Напишите команду которая бы увеличила на двадцать процентов комиссионныевсех продавцов, имеющих общие текущие порядки выше чем $3,000.(См. Приложение A для ответов.)17СОЗДАНИЕ ТАБЛИЦВПЛОТЬ ДО ЭТОГО МЕСТА, МЫ ЗАПРАШИВАЛИ ТАБЛИЦЫ данных и выполняли команды по извлечению этих данных, считая, что эти таблицы уже были созданыкем-то до нас.
Это действительно наиболее реальная ситуация, когда небольшое количество людей создают таблицы, которые затем используются другими людьми.Наша цель состоит в том, чтобы охватив информацию сначала более широко, перейти затем к более узким вопросам.В этой главе, мы будем обсуждать создание, изменение и удаление таблиц.
Всеэто относится к самим таблицам, а не к данным, которые в них содержатся. Будетеили не будете Вы выполнять эти операции самостоятельно, но их концептуальное понимание увеличит ваше понимание языка SQL и природу таблиц, которые вы используете. Эта глава вводит нас в область SQL называемую — DDL (Язык ОпределенияДанных), где создаются объекты данных SQL.Эта глава также покажет другой вид объекта данных SQL — Индекс.
Индексыиспользуются, чтобы делать поиск более эффективным и, иногда, заставлять значения отличаться друга от друга.Они обычно работают незаметно для Вас, но если вы попробуете поместитьзначения в таблицу и они будут отклонены из-за их неуникальности, это будет означать что другая строка имеет то же самое значение для этого поля, и что это полеимеет уникальный индекс или ограничение, которое предписывает ему уникальность.Обсуждение вышеупомянутого, продолжится в Главе 18.КОМАНДА СОЗДАНИЯ ТАБЛИЦЫТаблицы создаются командой CREATE TABLE. Эта команда создает пустуютаблицу — таблицу без строк.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
