8.8. Подзапросы в предложении having
Вы можете также использовать подзапросы внутри предложения HAVING. Подзапросы в предложении HAVING выполняются по одному разу для очередного значения из поля в предложении GROUP BY. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят многочисленых значений, а также использовать GROUP BY или HAVING. Следующий запрос является этому примером: SELECT rating, COUNT ( DISTINCT cnum ) FROM Customers GROUP BY rating HAVING rating > ( SELECT AVG (rating) FROM Customers WHERE city = ‘ San Jose’); =============== SQL Execution Log ============ | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = ‘San Jose’) | |============================ | | rating | | ————————-| | 300 2 | ================ Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Лекция 9. Соотнесенные подзапросы Когда вы используете подзапросы в SQL, вы можете использовать во внутреннем запросе таблицу, указанную в предложении внешнего запроса FROM , сформировав, так называемый, соотнесенный подзапрос. Соотнесенный подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса. Соотнесенный подзапрос — один из большого количества тонких понятий в SQL из-за сложности в его оценке. Если вы сумеете овладеть им, вы найдете, что он очень мощный, потому что может выполнять сложные функции с помощью очень лаконичных указаний. Например, имеется способ найти всех заказчиков в приобретениях на 3-е Октября : SELECT * FROM Customers outer WHERE 10/03/1990 IN ( SELECT odate FROM Orders inner WHERE outer.cnum = inner.cnum ); |====================================| | cnum cname city rating snum | |—————————————————————| | 2001 Hoffman London 100 1001 | | 2003 Liu San Jose 200 1002 | | 2008 Cisneros San Jose 300 1007 | | 2007 Pereira Rome 100 1004 | ==================================== КАК РАБОТАЕТ СООТНЕСЕННЫЙ ПОДЗАПРОС В вышеупомянутом примере, “внутренний”(inner) и “внешний”(outer), это псевдонимы. Эти имена отсылают к значениям внутренних и внешних запросов, соответственно. Так как значение в поле cnum внешнего запроса меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса, для которого внутренний запрос каждый раз будет выполнен, называется текущей строкой-кандидатом. Следовательно, процедура оценки, выполняемая соотнесенным подзапросом, — это: 1. Выбрать строку из таблицы, именованной в внешнем запросе. Это будет текущая строка-кандидат. 2. Сохранить значения из этой строки-кандидата в псевдониме таблицы из предложения FROM внешнего запроса. 3. Выполнить подзапрос. Везде, где псевдоним, данный для внешнего запроса найден ( в этом случае “внешний” ), использовать значение для текущей строки-кандидата. Использование значения из строки-кандидата внешнего запроса в подзапросе называется внешней ссылкой. 4. Оценить предикат внешнего запроса на основе результатов подзапроса, выполняемого в шаге 3. Он определяет, выбирается ли строка-кандидат для вывода. 5. Повторить процедуру для следующей строки-кандидата таблицы, и так далее, пока все строки таблицы не будут проверены. В вышеупомянутом примере, SQL осуществляет следующую процедуру: 1. Он выбирает строку Hoffman из таблицы Заказчиков. 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом “внешний”. 3. Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу Приобретений, чтобы найти строки, где значение cnum — поле такое же, как значение outer.cnum, которое в настоящее время равно 2001, — поле cnum строки Hoffmanа. Затем он извлекает поле odate из каждой строки таблицы Приобретений для которой это истинно, и формирует набор значений поля odate. 4. Получив набор всех значений поля odate, для поля cnum = 2001, он проверяет предикат основного запроса чтобы видеть имеется ли зна- чение на 3 Октября в этом наборе. Если это так(а это так), то он выбирает строку Hoffmanа для вывода ее из основного запроса. 5. Он повторяет всю процедуру, используя строку Giovanni как строку-кандидата, и затем сохраняет повторно, пока каждая строка таблицы Заказчиков не будет проверена. Конечно, вы могли бы решить ту же самую проблему используя обьединение следующего вида: SELECT * FROM Customers first, Orders second WHERE first.cnum = second.cnum AND second.odate = 10/03/1990; При отсутствии имени таблицы или префикса псевдонима, SQL может для начала принять, что любое поле выводится из таблицы с именем указанным в предложении FROM текущего запроса. Если поле с этим именем отсутствует( в нашем случае — snum ) в той таблице, SQL будет проверять внешние запросы. Именно поэтому, префикс имени таблицы обычно необходим в соотнесенных подзапросах — для отмены этого предположения. Псевдонимы также часто запрашиваются чтобы давать вам возможность ссылаться к той же самой таблице во внутреннем и внешнем запросе без какой-либо неоднозначности. ИСПОЛЬЗОВАНИЕ СООТНЕСЕННЫХ ПОДЗАПРОСОВ ДЛЯ НАХОЖДЕНИЯ ОШИБОК Иногда полезно выполнять запросы которые разработаны специально так чтобы находить ошибки. Это всегда возможно при дефектной информации, которую можно ввести в вашу базу данных, и, если она введена, бывает трудно ее определить. Следующий запрос не должен производить никакого вывода. Он просматривает таблицу Приобретений чтобы видеть, совпадают ли поля snum и cnum в каждой строке с данными из таблицы Заказчиков и выводит каждую строку где этого совпадения нет. Другими словами, запрос выясняет, тот ли продавец кредитовал каждую продажу ( он воспринимает поле cnum, как первичный ключ таблицы Заказчиков, который не будет иметь никаких двойных значений в этой таблице ). SELECT * FROM Orders main WHERE snum <> ( SELECT snum FROM Customers WHERE cnum = main.cnum ); СРАВНЕНИЕ ТАБЛИЦЫ С СОБОЙ Вы можете также использовать соотнесенный подзапрос, основанный на той же самой таблице что и основной запрос. Это даст вам возможность извлечь определенные сложные формы произведенной информации. Например, мы можем найти все приобретения со значениями сумм приобретений выше среднего для их заказчиков: SELECT * FROM Orders outer WHERE amt >= ( SELECT AVG(amt) FROM Orders inner WHERE inner.cnum = outer.cnum ); |=================================== | | onum amt odate cnum snum | |———————————————————| | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 5160.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ===================================== СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING Также как предложение HAVING может брать подзапросы, оно может брать и соотнесенные подзапросы. Когда вы используете соотнесенный подзапрос в предложении HAVING, вы должны ограничивать внешние ссылки к позициям которые могли бы непосредственно использоваться в самом предложении HAVING. Вы можете вспомнить, что предложение HAVING может использовать только агрегатные функции, которые указаны в предложении SELECT или поля, используемые в предложении GROUP BY. Они являются только внешними ссылками, которые вы можете делать. Все это потому, что предикат предложения HAVING оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно, подзапрос будет выполняться один раз для каждой группы выведенной из внешнего запроса, а не для каждой строки. Предположим что вы хотите суммировать значения сумм приобретений покупок из таблицы Приобретений, сгруппировав их по датам, удалив все даты, где бы SUM не был по крайней мере на 2000.00 выше максимальной ( MAX ) суммы: SELECT odate, SUM (amt) FROM Orders a GROUP BY odate HAVING SUM (amt) > ( SELECT 2000.00 + MAX (amt) FROM Orders b WHERE a.odate = b.odate ); Подзапрос вычисляет значение MAX для всех строк с той же самой датой что и у текущей агрегатной группы основного запроса. Это должно быть выполнено, как и ранее, с использованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING. СООТНЕСЕННЫЕ ПОДЗАПРОСЫ И ОБЬЕДИНЕНИЯ Как вы и могли предположить, соотнесенные подзапросы по природе близки к объединениям — они оба включают проверку каждой строки одной таблицы с каждой строкой другой ( или псевдонимом из той же ) таблицы. Вы найдете что большинство операций, которые могут выполняться с одним из них, будут также работать и с другим. Однако имеется различие в прикладной программе между ними, такое как вышеупомянутая потребность в использовании DISTINCT с обьединением и его необязательность с подзапросом. Имеются также некоторые вещи которые каждый может делать так, как этого не может другой. Подзапросы, например, могут использовать агрегатную функцию в предикате, делая возможным выполнение операций типа нашего предыдущего примера в котором мы извлекли приобретения усредненные для их заказчиков. Обьединения, с другой стороны, могут выводить строки из обеих сравни- ваемых таблиц, в то время как вывод подзапросов используется только в предикатах внешних запросов. Как правило, форма запроса которая ка- жется наиболее интуитивной будет вероятно лучшей в использовании, но при этом хорошо бы знать обе техники для тех ситуаций, когда та или иная могут не работать. ОПЕРАТОР EXISTS Теперь, когда вы хорошо ознакомлены с подзапросами, мы можем говорить о некоторых специальных операторах которые всегда берут подзапросы как аргументы. Оператор EXISTS используется, чтобы указать предикату, производить ли стандартному или ( обычно ) соотнесенному подзапросу вывод или нет. ========== КАК РАБОТАЕТ EXISTS? ========== EXISTS — это оператор, который производит истинное или неистинное значение. Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями, использующими Булевы операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как истинный, если тот производит любой вывод или как неистинный если тот не делает этого. Он отличается от других операторов тем,что он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose: SELECT cnum, cname, city FROM Customers WHERE EXISTS ( SELECT * FROM Customers WHERE city = ‘San Jose’ ); =============== SQL Execution Log ============ | cnum cname city | |—————————————| | 2001 Hoffman London | | 2002 Giovanni Rome | | 2003 Liu San Jose | | 2004 Grass Berlin | | 2006 Clemens London | | 2008 Cisneros San Jose | | 2007 Pereira Rome | =========================== Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было истинным, делает предикат истинным. Подзапрос( не соотнесенный ) был выполнен только один раз для всего внешнего запроса, и следовательно имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат истинным или неистинным для всех строк сразу, что не так уж полезно для извлечения определенной информации. ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS В вышеупомянутом примере, EXISTS должен быть установлен так, чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборе звезду( SELECT *) В этом состоит его отличие от подзапроса, который мог выбрать только один столбец ) . Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает — выполняется или нет вывод из подзапроса, а не использует выведенные значения. ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЕННЫМИ ПОДЗАПРОСАМИ В соотнесенном подзапросе, предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно также, как и другие операторы предиката, когда вы используете соотнесенный подзапрос. Это дает возможность использовать EXISTS как истинный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе. Следовательно, информация из внутреннего запроса, будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов, которые имеют многочисленых заказчиков: SELECT DISTINCT snum FROM Customers outer WHERE EXISTS ( SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < >outer.cnum ); =============== SQL Execution Log ============ | cnum | | ———| | 1001 | | 1002 | ======== Для каждой строки-кандидата внешнего запроса ( представляющей заказчика, проверяемого в настоящее время ) внутренний запрос находит строки, которые совпадают со значением поля snum ( которое имел продавец ), но не со значением поля cnum ( сответствующего другим заказчикам ). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика, обслуживаемых текущим продавцом ( то-есть продавцом заказчика в текущей строке-кандидата из внешнего запроса ). Предикат EXISTS поэтому верен для текущей строки, и номер про- давца поля (snum) таблицы, указанной во внешнем запросе, будет выведен. Если DISTINCT был не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика к которому он назначен. КОМБИНАЦИЯ ИЗ EXISTS И ОБЬЕДИНЕНИЯ Однако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это объединив таблицу Customers с таблицей Salespeople: SELECT DISTINCT first.snum, sname, first.city FROM Salespeople first, Customers second WHERE EXISTS ( SELECT * FROM Customers third WHERE second.snum = third.snum AND second.cnum < >third.cnum ) AND first.snum = second.snum; =============== SQL Execution Log ============ | cnum cname city | |———————————-| | 1001 Peel London | | 1002 Serres San Jose | ========================== Внутренний запрос здесь, как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос — это обьединение таблицы Salespeople с таблицей Customers, наподобии того что мы видели прежде. Новое предложение основного предиката ( AND first.snum = second.snum ) естественно оценивается на том же самом уровне что и предложение EXISTS. Это функциональный предикат самого обьединения, сравнивающий две таблицы из внешнего запроса в терминах поля snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть истинны для истинности предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях, когда вторая часть запроса истинна, а обьединение выполняемо. Таким образом комбинация объединения и подзапроса может стать очень мощным способом обработки данных. ИСПОЛЬЗОВАНИЕ NOT EXISTS Предыдущий пример дал понять что EXISTS может работать в комбинации с операторами Буля. Конечно, то что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT. Один из способов, которым мы могли бы найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример. SELECT DISTINCT snum FROM Customers outer WHERE NOT EXISTS ( SELECT * FROM Customers inner WHERE inner.snum = outer.snum AND inner.cnum < >outer.cnum ); =============== SQL Execution Log ============ | cnum | | ———-| | 1003 | | 1004 | | 1007 | ======== EXISTS И АГРЕГАТЫ Одна вещь которую EXISTS не может сделать — взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это — значение функции, если же агрегатная функция не находит никаких строк, EXISTS неправилен. Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели ( или который мы будем видеть ). Такие подзапросы, и любые другие в них, позволяют использовать агре- гаты, если нет другой причины по которой они не могут быть использо- ваны. Следующий раздел приводит этому пример. В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат — EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) будет эквивалентен — EXISTS (SELECT sname FROM Salespeople), который был позволен выше. БОЛЕЕ УДАЧНЫЙ ПРИМЕР ПОДЗАПРОСА Возможные прикладные программы подзапросов могут становиться многократно вкладываемыми. Вы можете вкладывать их два или более в одиночный запрос, и даже один внутрь другого. Так как можно рассмотреть небольшой кусок чтобы получить всю картину работаты этой команды, вы можете воспользоваться способом в SQL, который может принимать различные команды из большинства других языков. Имеется запрос который извлекает строки всех продавцов которые имеют заказчиков с больше чем одним текущим порядком. Это не обязательно самое простое решение этой проблемы, но оно предназначено скорее показать улучшенную логику SQL. Вывод этой информации связывает все три наши типовых таблицы: SELECT * FROM Salespeople first WHERE EXISTS ( SELECT * FROM Customers second WHERE first.snum = second.snum AND 1 < ( SELECT COUNT (*) FROM Orders WHERE Orders.cnum = second.cnum )); =============== SQL Execution Log ============ | cnum cname city comm | | ----- -------- ---- -------- | | 1001 Peel London 0.17 | | 1002 Serres San Jose 0.13 | | 1007 Rifkin Barselona 0.15 | ================================= Мы могли бы разобрать вышеупомянутый запрос примерно так: Берем каждую строку таблицы Salespeople как строку-кандидат( внешний запрос ) и выполняем подзапросы. Для каждой строки-кандидата из вне- шнего запроса, берем в соответствие каждую строку из таблицы Заказчи- ков( средний запрос ). Если текущая строка заказчиков не совпадает с те- кущей строкой продавца( т.е. если first.snum < >second.snum ), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе который совдает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число Приобретений текущего заказчика ( из среднего запроса ). Если это число больший чем 1, предикат среднего запроса верен, и строки выбираются. Это делает EXISTS предикат внешнего запроса истинным для текущей строки продавца, и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем одно приобретение. Если это не кажется достаточно понятным для вас в этой точке разбора примера, не волнуйтесь. Сложность этого примера — хороша независимо от того, как часто будете Вы использовать ее в деловой ситуации. Основная цель примеров такого типа состоит в том, чтобы показать вам некоторые возможности которые могут оказаться в дальнейшем полезными. После работы со сложными ситуациями подобно этой, простые запросы которые являются наиболее часто используемыми в SQL, покажутся Вам элементарными. Кроме того, этот запрос, даже если он кажется удобным, довольно извилистый способ извлечения информации и делает много работы. Он связывает три разных таблицы чтобы дать вам эту информацию, а если таблиц больше чем здесь указано, будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно вам нужно увидеть эту информацию относительно регулярной основы — если, например, вы имеете премию в конце недели для продавца, который получил многочисленые приобретения от одного заказчика. В этом случае, он должен был бы вывести команду, и сохранять ее чтобы использовать снова и снова по мере того, как данные будут меняться ( лучше всего сделать это с помощью представления, которое мы будем проходить дальше ). Лекция 10. Cпециальные операторы ALL, ANY и SOME В языке SQL есть три специальных оператора, ориентированных на подзапросы. Операторы ALL(все), ANY(любой) и SOME(некоторый) напоминают EXISTS, который воспринимает подзапрос как аргумент; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении, они напоминают оператор IN, когда тот используется с подзапросами; они берут все значения, выведенные подзапросом и обрабатывают их вместе. Однако, в отличие от IN, они могут использоваться только с подзапросами. Операторы SOME и ANY взаимозаменяемы везде. Различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который наиболее подходит. Покажем еще один способ нахождения продавцов с заказчиками, размещенными в их городах: SELECT * FROM Salespeople WHERE city = ANY (SELECT city FROM Customers ); |============================ | | snum sname city comm | |—————————————————| | 1001 Peel London 0.12 | | 1002 Serres San Jose 0.13 | | 1004 Motika London 0.11 | =============================== Оператор ANY берет все значения, выведенные подзапросом, ( для этого случая — это все значения city в таблице Заказчиков ), и оценивает их как истинные, если любой(ANY) из них равняется значению города текущей строки внешнего запроса. В операторе ANY подзапрос должен выбирать значения такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты или нет, и фактически не использует эти результаты. Оператор ANY может использовать другие реляционные операторы кроме равняется ( = ), и таким образом делать сравнения, которые являются выше возможностей IN. Например, можно найти всех продавцов, имена которых предшествуют именам заказчиков: SELECT * FROM Salespeople WHERE sname < ANY ( SELECT cname FROM Customers); |============================ | | cnum cname city comm | |-------------------------------------------------| | 1001 Peel London 0.12 | | 1004 Motika London 0.11 | | 1003 Axelrod New York 0.10 | ============================= В случае использования оператора ALL предикат является истинным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса. Например, выведем только тех заказчиков, чьи оценки выше чем у заказчиков в Риме: SELECT * FROM Customers WHERE rating >ALL (SELECT rating FROM Customers WHERE city = ‘Rome’ ): | cnum cname city rating snum | |————————————————————| | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | ==================================== Этот оператор проверяет значения оценки всех заказчиков в Риме. Затем он находит заказчиков с оценкой, большей чем у любого из заказчиков в Риме. Самая высокая оценка в Риме у Giovanni ( 200 ). Следовательно, выбираются только значения выше этих 200. ALL используется в основном с неравенствами, а не с равенствами, так как значение “равный для всех” может быть результатом подзапроса только если все результаты идентичны. В SQL выражение “ < >ALL” соответствует “ не равен любому “ результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Например, SELECT * FROM Customers WHERE rating < >ALL ( SELECT rating FROM Customers WHERE city = ‘ San Jose’ ); |============================ | | cnum cname city rating snum | |————————————————————| | 2001 Hoffman London 100 1001 | | 2006 Clemens London 100 1001 | | 2007 Pereira Rome 100 1004 | ==================================== Здесь подзапрос выбирает оценки для города San Jose: 200 ( для Liu ) и 300 (для Cisneros). Затем, основной запрос выбирает все строки с оценкой, не совпадающей ни с одной из них, другими словами, все строки с оценкой 100. Значительное различие между ALL и ANY — способ действия в cитуации, когда подзапрос не возвращает никаких значений. Всякий раз, когда допустимый подзапрос не в состоянии сделать вывод, ALL автоматически верен, а ANY автоматически неправилен. ИСПОЛЬЗОВАНИЕ ПРЕДЛОЖЕНИЯ UNION Вы можете объединить вывод многих запросов используя предложение UNION. Предложение UNION обьединяет вывод двух или более SQL запросов в единый набор строк и столбцов. Например, чтобы получить всех продавцов и заказчиков, размещенных в Лондоне, и вывести их как единое целое, вы могли бы ввести: SELECT snum, sname FROM Salespeople WHERE city = ‘London’ UNION SELECT cnum, cname FROM Customers WHERE city = ‘London’; и получить следующий вывод: | ============ | —— ——— | | 1001 Peel | | 1004 Motika | | 2001 Hoffman | | 2006 Climens | ============= Как вы можете видеть, столбцы, выбранные двумя командами, выведены так, как если бы команда была одна. Заголовки столбцов исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно из только одной таблицы. Когда два ( или более ) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов, и каждый должен иметь тип, совместимый с каждым. Символьные поля должны иметь одинаковое число символов. Типы, не определенные ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа. Другое ограничение на совместимость — если пустые значения (NULL) запрещены в каком-либо столбце объединения, эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения. Кроме того, вы не можете использовать UNION в подзапросах, а также не можете использовать агрегатные функции в предложениии SELECT запроса в объединении. ( Большинство программ пренебрегают этими ограничениями. ) UNION автоматически исключает дубликаты строк из вывода. Вы можете использовать предложение ORDER BY, чтобы упорядочить вывод из объединения, точно так же как это делается в индивидуальных запросах. Операция, которая бывает часто полезна, — это объединение из двух зап- росов, в котором второй запрос выбирает строки, исключенные первым. Это называется внешним обьединением. Предположим, что некоторые из ваших заказчиков еще не были назначены к продавцам. Вы можете захотеть увидеть имена и города всех ваших заказчиков, с именами их продавцов, не учитывая тех, кто еще не был назначен. Вы можете достичь этого, формируя объединение из двух запросов, один из которых выполняет основной вывод, а другой выбирает заказчиков с пустыми (NULL) значениями поля snum. Этот последний запрос должен вставлять пробелы в поля, соответствующие полю sname в первом запросе. Вы можете вставлять текстовые строки в ваш вывод, чтобы идентифицировать запрос, который вывел данную строку. Использование этой методики во внешнем обьединении, дает возможность использовать предикаты для классификации, а не для исключения. Мы использовали пример нахождения продавцов с заказчиками, размещенными в их городах и раньше. Однако, вместо просто выбора только этих строк вы возможно захотите, чтобы ваш вывод перечислял всех про- давцов, и указывал тех, кто не имел заказчиков в их городах, и кто имел. Следующий запрос выполнит это: SELECT Salespeople.snum, sname, cname, comm FROM Salespeople, Customers WHERE Salespeople.city = Customers.city. UNION SELECT snum, sname, ‘ NO MATCH ‘, comm FROM (Salespeople WHERE NOT city = ANY ( SELECT city FROM Customers ) ORDER BY 2 DESC; | ========================= | |——————————————— | | 1002 Serres Cisneros 0.1300 | | 1002 Serres Liu 0.1300 | | 1007 Rifkin NO MATCH 0.1500 | | 1001 Peel Clemens 0.1200 | | 1001 Peel Hoffman 0.1200 | | 1004 Motika Clemens 0.1100 | | 1004 Motika Hoffman 0.1100 | | 1003 Axelrod NO MATCH 0.1000 | =========================== Строка ‘ NO MATCH ‘ была дополнена пробелами, чтобы получить совпадение поля cname по длине ( это не обязательно во всех реализациях SQL ). Второй запрос выбирает даже те строки, которые исключил первый. Всякий раз, когда вы выполняете объединение более чем двух запросов, вы можете использовать круглые скобки, чтобы определить порядок оценки. Другими словами, вместо просто query X UNION query Y UNION query Z; вы можете указать, или ( query X UNION query Y ) UNION query Z; или query X UNION ( query Y UNION query Z ); ВВОД, УДАЛЕНИЕ и ИЗМЕНЕНИЕ ЗНАЧЕНИЙ ПОЛЕЙ Значения могут быть помещены и удалены из полей, тремя командами языка DML ( Язык Манипулирования Данными ): INSERT (ВСТАВИТЬ), UPDATE (МОДИФИЦИРОВАТЬ), DELETE (УДАЛИТЬ). ВВОД ЗНАЧЕНИЙ Все строки в SQL вводятся с использованием команды модификации INSERT. В самой простой форме INSERT использует следующий синтаксис: INSERT INTO VALUES ( , . . .); Так например, чтобы ввести строку в таблицу Salespeople (Продавцов), вы можете использовать следующее условие: INSERT INTO Salespeople VALUES (1001, ‘Peel’, ‘London’, .12); Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того, что данные были использованы. Имя таблицы ( в нашем случае — Salespeople (Продавцы)), должно быть предварительно определено, в команде CREATE TABLE, а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI эти значения не могут составлять выражений, что означает, что 3 доступно, а выражение 2 + 1 нет. Значения вводятся в таблицу в поименном порядке, поэтому первое значение автоматическми попадает в столбец 1, второе в столбец 2 и т. д. Если вам нужно ввести пустое значение(NULL), вы вводите его точно также, как и обычное значение. Предположим, что еще не имелось поля city для господина Peel. Вы можете вставить его строку со значением NULL в это поле, следующим образом: INSERT INTO Salespeople VALUES (1001, ‘Peel’, NULL, .12); Так как значение NULL — это специальный маркер, а не просто символьное значение, он не заключается в одиночные кавычки. Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим, что вы берете значения для таблицы Заказчиков из отчета, выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке: INSERT INTO Customers (city, cnamе, cnum) VALUES (‘London’, ‘Honman’, 2001); Обратите внимание, что столбцы rating и snum отсутствуют. Это значит, что эти строки автоматически установлены в значение по умолчанию. По умолчанию может быть введено или значение NULL или другое значение, определяемое как по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT, которая относится к таблице. Вы можете также использовать команду INSERT, чтобы получать или выбирать значения из одной таблицы и помещать их в другую, чтобы использовать их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос:
10.02.2015 7.66 Mб 337 Батыгин&co.pdf
10.07.2019 5.94 Mб 46 Батырь В.А. Международное гуманитарное право -. rtf
22.04.2019 415.74 Кб 7 бахтин геохимия.doc
25.11.2019 145.34 Кб 4 Бахтин Проблема речевых жанров.doc
10.02.2015 2.62 Mб 11 Баширов_Механика2012.pdf
10.02.2015 834.56 Кб 12 БД основы и ER-диаграммы, кратко о CASE.doc
10.02.2015 876.03 Кб 6 бебебебе.doc
02.05.2019 1.86 Mб 11 Беккер Логопедия.doc
06.11.2018 424.96 Кб 19 Беловик. Введение в сравнительное правоведение. doc
07.07.2019 113.66 Кб 1 беловик.doc
09.08.2019 14.48 Mб 7 Белякова Волоскова Дизартрия.rtf
Ограничение
Для продолжения скачивания необходимо пройти капчу:
Что делает следующий запрос select from salespeople where city any select city from customer
13. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ ANY, ALL, И SOME. ТЕПЕРЬ, КОГДА ВЫ ОВЛАДЕЛИ ОПЕРАТОРОМ EXISTS, Вы узнаете приблизи- тельно три специальных оператора ориентируемых на подзапросы. (Факти- чески, имеются только два, так как ANY и SOME — одно и то же.) Если вы поймете работу этих операторов, вы будете понимать все типы подзапро- сов предиката используемых в SQL . Кроме того, вы будете представлены различным способам где данный запрос может быть сформирован используя различные типы подзапросов предиката, и вы поймете преимущества и не- достатки каждого из этих подходов. ANY, ALL, и SOME напоминают EXISTS который воспринимает подзапросы как аргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении, они напоминают оператор IN когда тот используется с подзапросами; они берут все зна- чения выведенные подзапросом и обрабатывают их как модуль. Однако, в отличие от IN, они могут использоваться только с подзапросами. ========= СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ============ ANY или SOME Операторы SOME и ANY — взаимозаменяемы везде и там где мы используем ANY, SOME будет работать точно так же. Различие в терминологии состоит в том чтобы позволить людям использовать тот термин который наиболее однозначен. Это может создать проблему; потому что, как мы это увидим, наша интуиция может иногда вводить в заблуждение. Имеется новый способ нахождения продавца с заказчиками размещенными в их городах ( вывод для этого запроса показывается в Рисунке 13.1 ): SELECT * FROM Salespeople WHERE city = ANY (SELECT city FROM Customers ); Оператор ANY берет все значения выведенные подзапросом, ( для этого случая — это все значения city в таблице Заказчиков ), и оценивает их как верные если любой(ANY) из их равняется значению города текущей строки внешнего запроса. =============== SQL Execution Log ============ | SELECT * | | FROM Salespeople | | WHERE city = ANY | | (SELECT city | | FROM Customers); | | ============================================= | | cnum cname city comm | | —— ——— —- ——— | | 1001 Peel London 0.12 | | 1002 Serres San Jose 0.13 | | 1004 Motika London 0.11 | ============================================= Рисунок 13. 1: Использование оператора ANY Это означает, что подзапрос должен выбирать значения такого же типа как и те, которые сравниваются в основном предикате. В этом его отли- чие от EXISTS, который просто определяет, производит ли подзапрос ре- зультаты или нет, и фактически не использует эти результаты. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ IN ИЛИ EXISTS ВМЕСТО ОПЕРАТОРА ANY Мы можем также использовать оператор IN чтобы создать запрос анало- гичный предыдущему : SELECT * FROM Salespeople WHERE city IN ( SELECT city FROM Customers ); Этот запрос будет производить вывод показанный в Рисунке 13.2. Однако, оператор ANY может использовать другие реляционные операторы кроме равняется ( = ), и таким образом делать сравнения которые явля- ются выше возможностей IN. Например, мы могли бы найти всех продавцов с их заказчиками которые следуют им в алфавитном порядке ( вывод пока- зан на Рисунке 13.3) SELECT * FROM Salespeople WHERE sname ANY ( SELECT rating FROM Customers WHERE city = Rome ); В английском языке, способ которым мы обычно склонны интерпретиро- вать оценку » больше чем любой ( где city = Rome ) » , должен вам со- общить что это значение оценки должно быть выше чем значение оценки в каждом случае где значение city = Rome. Однако это не так, в случае ANY — используемом в SQL . ANY оценивает как верно, если подзапрос на- ходит любое значение которое делает условие верным. =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating > ANY | | (SELECT rating | | FROM Customers | | WHERE city = ‘Rome’); | | ============================================= | | cnum cname city rating snum | | —— ——— —- —— —— | | 2002 Giovanni Rome 200 1003 | | 2003 Liu San Jose 200 1002 | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | ============================================= Рисунок 13.5 Как оператор «больше чем» (>) интерпретируется ANY Если мы оценим ANY способом использующим грамматику Английского Язы- ка, то только заказчики с оценкой 300 будут превышать Giovanni, кото- рый находится в Риме и имеет оценку 200. Однако, подзапрос ANY также находит Periera в Риме с оценкой 100. Так как все заказчики с оценкой 200 были выше этой, они будут выбраны, даже если имелся другой заказ- чик из Рима(Giovanni) чья оценка не была выше ( фактически, то что один из выбранных заказчиков также находится в Риме несущественно). Так как подзапрос произвел по крайней мере одно значение которое сде- лает предикат верным в отношении этих строк, строки были выбраны. Что- бы дать другой пример, предположим что мы должны были выбирать все по- рядки сумм приоретений которые были больше чем по крайней мере один из порядков на 6-е Октября: SELECT * FROM Orders WHERE amt > ANY ( SELECT amt FROM Orders WHERE odate = 10/06/1990 ); Вывод для этого запроса показывается в Рисунке 13.6. Даже если самая высокая сумма приобретений в таблице (9891.88) — имелась на 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другая строка на 6-е Октября, которая имела значение суммы = 1309.95. Имея реляционный оператор «>=» вместо просто » > «, эта стро- ка будет также выбирана, потому что она равна самой себе. Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой обьединения. Этот запрос будет находить все порядки со зна- чением суммы меньшей чем значение любой суммы для заказчика в San Jo- se (вывод показывается в Рисунке 13.7): SELECT * FROM Orders WHERE amt ALL (SELECT rating FROM Customers WHERE city = Rome ): =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating > ALL | | (SELECT rating | | FROM Customers | | WHERE city = ‘Rome’); | | ============================================= | | cnum cname city rating snum | | —— ——— —- —— —— | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | ============================================= Рисунок 13.9: Использование оператора ALL Этот оператор проверяет значения оценки всех заказчиков в Риме. За- тем он находит заказчиков с оценкой большей чем у любого из заказчиков в Риме. Самая высокая оценка в Риме — у Giovanni( 200 ). Следователь- но, выбираются только значения выше этих 200. Как и в случае с ANY, мы можем использовать EXISTS для производства альтернативной формулировки такого же запроса — ( вывод показан на Ри- сунке 13.10 ): SELECT * FROM Customers outer WHERE NOT EXISTS ( SELECT * FROM Customers inner WHERE outer.rating «. Но учтите что сказанное в SQL что — значение которое не равняется всем результатам подзапроса, — будет отличаться от того же но сказан- ного с учетом граматики Английского языка. Очевидно, если подзапрос возвращает много различных значений, как это обычно бывает, ни одно отдельное значение не может быть равно им всем в обычном смысле. В SQL, выражение — ALL — в действительности соответствует » не равен любому » результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Следователь- но, наш предыдущий пример противоположен по смыслу этому примеру (с выводом показанным в Рисунке 13.11): SELECT * FROM Customers WHERE rating ALL ( SELECT rating FROM Customers WHERE city San Jose’ ); Вы могли бы также использовать оператор ANY: SELECT * FROM Customers WHERE NOT rating = ANY ( SELECT rating FROM Customers WHERE city center»> Создание сайтов и Разработка сайтов
Запросы к базе данных (команда select)
Интернет технологии
Я не ставлю целью углубляться в изучение языка sql, об этом вы можете прочитать в любом руководстве по sql server, mysql в основном поддерживает все основные команды стандарта ansi 92, но команда select заслуживает того, чтобы посвятить ей отдельную главу. Команда select используется для запросов к базе данных с целью извлечения из нее информации. Синтаксис команды следующий:
select [straight_join] [distinct | all] select_expression. [from tables. [where where_definition] [group by column. ] [order by column [asc | desc], . ] having full_where_definition [limit [offset,] rows] [procedure procedure_name]] [into outfile 'file_name'. ]
Как видно из вышеприведенного, вместе с командой select используются ключевые слова, использование которых очень влияет на ответ сервера. Рассмотрим каждое из них.
distinct..
Пропускает строки, в которых все выбранные поля идентичны, то есть устраняет дублирование данных.
where.
Предложение команды select, которое позволяет устанавливать предикаты, условие которых может быть верным или неверным для любой строки таблицы. Извлекаются только те строки, для которых такое утверждение верно. Например:
select u_id,lname from publishers where city ='new york';
Выводит колонки u_id и lname из таблицы publishers для которых значение в столбце city-new york. Это дает возможность сделать запрос более конкретным.
Реляционные операторы.
Реляционный оператор — математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает mysql :
>= Больше или равно
Эти операторы имеют стандартные значения для числовых значений.
Предположим что вы хотите увидеть всех заказчиков с оценкой(rating) выше 200. Так как 200 — это скалярное значение, как и значение в столбце оценки, для их сравнения вы можете использовать реляционный оператор.
select * from customers where rating > 200;
Булевы операторы.
Основные Булевы операторы также распознаются в mysql. Выражения Буля — являются или верными или неверными, подобно предикатам. Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное или неверное значение. Стандартными операторами Буля распознаваемыми в sql являются:and, or и not.
Предположим вы хотите видеть всех заказчиков в Далласе, которые имеют рейтинг выше 200:
select * from customers where city = 'dallas' and rating > 200;
При использовании оператора and, должны быть выполнены оба условия, то есть должны быть выбраны все заказчики из Далласа, рейтинг которых больше 200.
При использовании оператора or, должно выполниться одно из условий. Например:
select * from customers where city = 'dallas ' or rating > 200;
В данном случае будут выбраны все заказчики из Далласа и все имеющие рейтинг больше 200, даже если они и не из Далласа.
not может использоваться для инвертирования значений Буля. Пример запроса с not:
select * from customers where city = 'dallas' or not rating > 200;
При таком запросе будут выбраны все заказчики из Далласа и все заказчики, рейтинг которых меньше 200. В этом запросе оператор not применяется только к выражению rating >200. Можно сделать более сложный запрос:
select * from customers where not( city = 'dallas' or rating > 200 );
В этом запросе not применен к обеим выражениям в скобках. В данном случае, сервер читает выражения в скобках, определяет, соответствует ли истине равенство city = ‘dallas’ или равенство rating > 200. Если любое условие верно, выражение Буля внутри круглых скобок верно. Однако, если выражение Буля внутри круглых скобок верно, предикат как единое целое неверен, потому что not преобразует верно в неверно и наоборот. То есть, будут выбраны все заказчики не находящиеся в Далласе и рейтинг которых меньше 200.
in.
Оператор in определяет набор значений в которое данное значение может или не может быть включено. Например, запрос
select * from salespeople where city = 'barcelona' or city = 'london';
может быть переписан более просто:
select * from salespeople where city in ( 'barcelona', 'london' );
in определяет набор значений с помощью имен членов набора заключенных в круглые скобки и отделенных запятыми. Затем он проверяет различные значения указанного, пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен. Когда набор содержит значения номеров а не символов, одиночные кавычки опускаются.
between.
Оператор between похож на оператор in. В отличии от определения по номерам из набора, как это делает in, between определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово between с начальным значением, ключевое and и конечное значение. В отличие от in, between чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку. Например:
select * from salespeople where comm between .10 and .12; select * from salespeople where city between 'berlin' and 'london';
like.
like применим только к полям типа char или varchar, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки. В качестве условия он использует групповые символы(wildkards) — специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с like:
символ подчеркивания ( _ ) замещает любой одиночный символ.
знак ‘%’, замещающий любое количество символов.
Если мы зададим следующие условия:
select * from customers where fname like 'j%';
то будут выбраны все заказчики, чьи имена начинаются на j:john,jerry,james и т.д.
count.
Агрегатная функция, производит подсчет значений в столбце или числа строк в таблице. При работе со столбцом использует distinct в качестве аргумента:
select count ( distinct snum ) from orders;
При подсчете строк имеет синтаксис:
select count (*) from customers;
group by.
Предложение group by позволяет определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении select. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав max () из таблицы для каждого значения поля. group by позволит Вам поместить их все в одну команду:
select snum, max (amt) from orders group by snum;
having.
having определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение where делает это для индивидуальных строк. Например:
select cid,cname,price,max(price) //max()-это тоже агрегатная функция from customers having max(price)>500;
having действует сходно с where, но с where нельзя использовать агрегатные функции.
order by.
Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого, также как с group by.
exists.
Используется в подзапросах.
select cnum, cname, city from customers where exists (select * from customers where city = " san jose' );
Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в san jose.
union.
union отличается от подзапросов тем что в нем ни один из двух ( или больше ) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их — объединяется.Например:
select snum, sname from salespeople where city = 'london' union select cnum, cname from customers where city = 'london';
Предложение union объединяет вывод двух или более sql запросов в единый набор строк и столбцов.
desc,asc.
desc-descedent, вывод данных в обратном порядке(по алфавиту и численным значениям). По умолчанию используется asc.
Ну вот вкратце и все. mysql поддерживает почти все основные команды sql server, так что более подробно о команде select вы можете прочитать в любом учебнике по языку sql.
ПОДЗАПРОСЫ
С помощью SQL можно вкладывать запросы внутрь друга друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего, истинно оно или нет. Простой подзапрос выполняется только один раз.
Например, предположим, что мы знаем имя продавца: Motika, но не знаем значение его поля snum, и хотим извлечь все приобретения из таблицы Приобретений. Запрос имеет следующую форму:
SELECT * FROM Orders
WHERE snum =( SELECT snum FROM Salespeople
WHERE sname = ‘Motika’);
Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Конечно же, подзапрос должен выбирать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена (в этом случае snum), но это необязательно.
Подзапрос должен возвращать одно и только одно значение. Если в предыдущем подзапросе назначить условие snum “WHERE city = “London” вместо “WHERE sname = ‘Motika”, то можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки истинности или неистинное™, и команда выдаст ошибку.
При использовании подзапросов в предикатах, основанных на реляционных операторах, необходимо убедиться, что использовали подзапрос, который будет выдавать одну и только одну строку вывода. Если используется подзапрос, который не выводит никаких значений вообще, команда не потерпит неудачи; но основной запрос не выведет никаких значений.
Подзапросы, которые не производят никакого вывода (или нулевой вывод), вынуждают рассматривать предикат ни как истинный, ни как неистинный, а как неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и неистинный: никакие строки не выбираются основным запросом.
Можно использовать DISTINCT, чтобы вынудить подзапрос генерировать одиночное значение. Предположим, что мы хотим найти все приобретения для тех продавцов, которые обслуживают Hoffmana (спит = 2001).
Имеется один способ, чтобы сделать это:
(SELECT DISTINCT snum
WHERE cnum = 2001);
Подзапрос установил, что значение поля snum совпало с Hoffman -1001, и затем основной запрос выделил все приобретения с этим значением snum из таблицы Приобретений (не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы знаем, что каждая строка в таблице Приобретений с данным значением спит должна иметь такое же значение snum. Однако, так как там может быть любое число таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений snum для данного поля спит. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных.
Следует обратить внимание, что предикаты, включающие подзапросы, используют выражение типа , а не или .
Другими словами, вы не должны записывать предыдущий пример так:
WHERE (SELECT DISTINCT snum
WHERE cnum = 2001)
Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, — агрегатная функция.
Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение для использования в основном предикате. Например, требуется увидеть все приобретения, имеющие сумму выше средней на 4 октября:
WHERE amt > (SELECT AVG (amt) FROM Orders
WHERE odate = 10/04/2014);
Имейте в виду, что сгруппированные агрегатные функции, которые являются агрегатными функциями, определёнными в терминах предложения GROUP BY, могут производить многочисленные значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Необходимо использовать одиночную агрегатную функцию с предложением WHERE, что устранит нежелательные группы.
Например, следующий запрос, который должен найти среднее значение комиссионных продавца в Лондоне:
SELECT AVG (comm)
GROUP BY city HAVING city = “London”;
не может использоваться в подзапросе!
Другим способом может быть:
SELECT AVG (comm)
WHERE city = “London”;
Можно использовать подзапросы, которые производят любое число строк, если используется специальный оператор IN (операторы BETWEEN, LIKE и IS NULL не могут использоваться с подзапросами). Оператор IN определяет набор значений, одно из которых должно совпадать с другим термом уравнения предиката, чтобы предикат был истинным.
При использовании IN с подзапросом SQL просто формирует этот набор из вывода подзапроса. Следовательно, можно использовать IN, чтобы выполнить такой же подзапрос, который не будет работать с реляционным оператором, и найти все атрибуты таблицы Приобретений для продавца в Лондоне:
WHERE city = “LONDON”);
В ситуации, подобно этой, подзапрос более прост для пользователя, чтобы понимать его, и более прост для компьютера, чтобы его выполнить, чем если бы было использовано объединение:
SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum = Salespeople.snum
AND Salespeople.city = “London”;
Хотя это и произведёт тот же самый вывод, что и в примере с подзапросом.
Конечно, можно также использовать оператор IN, даже когда вы уверены, что подзапрос произведёт одиночное значение. В любой ситуации, где можно использовать реляционный оператор сравнения (=), можно использовать IN. В отличие от реляционных операторов, 1N не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом, или недостатком.
Смысл всех ранее обсуждённых подзапросов тот, что все они выбирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому является то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS.
В предложении SELECT-подзапроса можно использовать выражение, основанное на столбце, а не просто сам столбец. Это может быть выполнено или с помощью реляционных операторов, или с IN. Например, следующий запрос использует реляционный оператор = :
(SELECT snum + 1000
WHERE sname = ‘Serres’);
Он находит всех заказчиков, для которых значение поля спит на 1000 больше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано ограничением UNIQUE;), иначе подзапрос может произвести многочисленные значения. Когда поля snum и спит не имеют такого простого функционального значения, как например, первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен.
Также можно использовать подзапросы внутри предложения HAVING. Подзапросы в предложении HAVING выполняются по одному разу для очередного значения из поля в предложении GROUP BY. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят многочисленных значений, а также использовать GROUP BY или HAVING. Следующий запрос является этому примером:
SELECT rating, COUNT(DISTINCT cnum)
GROUP BY rating
(SELECT AVG (rating)
WHERE city = ‘San Jose’);
Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose.
Когда используются подзапросы в SQL, можно использовать во внутреннем запросе таблицу, указанную в предложении внешнего запроса FROM, сформировав так называемый соотнесённый подзапрос. Соотнесённый подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса.
Например, имеется способ найти всех заказчиков в приобретениях на 3 октября:
FROM Customers outer
WHERE 10/03/2014 IN
FROM Orders inner
WHERE outer.cnum = inner.cnum);
В вышеупомянутом примере «внутренний» (inner) и «внешний» (outer) — это псевдонимы. Эти имена отсылают к значениям внутренних и внешних запросов соответственно. Так как значение в поле спит внешнего запроса меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса, для которого внутренний запрос каждый раз будет выполнен, называется текущей строкой-кандидатом. Следовательно, процедура оценки, выполняемая соотнесённым подзапросом, — это:
- 1. Выбрать строку из таблицы, именованной во внешнем запросе. Это будет текущая строка-кандидат.
- 2. Сохранить значения из этой строки-кандидата в псевдониме таблицы из предложения FROM внешнего запроса.
- 3. Выполнить подзапрос. Везде, где псевдоним, данный для внешнего запроса, найден (в этом случае «внешний»), использовать значение для текущей строки-кандидата. Использование значения из строки-кандидата внешнего запроса в подзапросе называется внешней ссылкой.
- 4. Оценить предикат внешнего запроса на основе результатов подзапроса, выполняемого на шаге 3. Он определяет, выбирается ли строка-кандидат для вывода.
- 5. Повторить процедуру для следующей строки-кандидата таблицы, и так далее, пока все строки таблицы не будут проверены.
В вышеупомянутом примере SQL осуществляет следующую процедуру:
- 1. Он выбирает строку Hoffman из таблицы Заказчиков.
- 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом «внешний».
- 3. Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу Приобретений, чтобы найти строки, где значение спит — поле такое же, как значение outer, спит, которое в настоящее время равно 2001, — поле спит строки Hoffmana. Затем он извлекает поле odate из каждой строки таблицы Приобретений, для которой это истинно, и формирует набор значений поля odate.
- 4. Получив набор всех значений поля odate, для поля спит = 2001, он проверяет предикат основного запроса, чтобы видеть, имеется ли значение на 3 октября в этом наборе. Если это так (а это так), то он выбирает строку Hoffmana для вывода её из основного запроса.
- 5. Он повторяет всю процедуру, используя строку Giovanni как строку-кандидат, и затем сохраняет повторно, пока каждая строка таблицы Заказчиков не будет проверена.
Конечно, ту же самую проблему можно решить, используя объединение следующего вида:
FROM Customers first, Orders second
WHERE first.cnum = second.cnum
AND second.odate = 10/03/2014;
При отсутствии имени таблицы или префикса псевдонима, SQL может для начала принять, что любое поле выводится из таблицы с именем указанным в предложении FROM текущего запроса. Если поле с этим именем отсутствует (в нашем случае — snum) в той таблице, SQL будет проверять внешние запросы. Именно поэтому префикс имени таблицы обычно необходим в соотнесённых подзапросах — для отмены этого предположения. Псевдонимы также часто запрашиваются, чтобы давать возможность ссылаться к той же самой таблице во внутреннем и внешнем запросах без какой-либо неоднозначности.
Так же как предложение HAVING может брать подзапросы, оно может брать и соотнесённые подзапросы. Когда используется соотнесённый подзапрос в предложении HAVING, необходимо ограничивать внешние ссылки к позициям, которые могли бы непосредственно использоваться в самом предложении HAVING. Можно вспомнить, что предложение HAVING может использовать только агрегатные функции, которые указаны в предложении SELECT, или поля, используемые в предложении GROUP BY.
Они являются только внешними ссылками, которые можно делать. Всё это потому, что предикат предложения HAVING оценивается для каждой группы из внешнего запроса, а не для каждой строки.
Следовательно, подзапрос будет выполняться один раз для каждой группы выведенной из внешнего запроса, а не для каждой строки.
Предположим что требуется суммировать значения сумм приобретений покупок из таблицы Приобретений, сгруппировав их по датам, удалив все даты, где бы SUM не был по крайней мере на 2000.00 выше максимальной (МАХ) суммы:
SELECT odate, SUM (amt)
( SELECT 2000.00 + MAX (amt)
WHERE a.odate = b.odate);
Подзапрос вычисляет значение MAX для всех строк с той же самой датой, что и у текущей агрегатной группы основного запроса. Это должно быть выполнено, как и ранее, с использованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING.
Соотнесённые подзапросы по природе близки к объединениям -они оба включают проверку каждой строки одной таблицы с каждой строкой другой (или псевдонимом из той же) таблицы. Большинство операций, которые могут выполняться с одним из них, будут также работать и с другим. Однако имеются некоторые различия между ними, такое как вышеупомянутая потребность в использовании DISTINCT с объединением и его необязательность с подзапросом. Также подзапросы могут использовать агрегатную функцию в предикате, делая возможным выполнение операций типа нашего предыдущего примера, в котором мы извлекли приобретения, усреднённые для их заказчиков. Объединения, с другой стороны, могут выводить строки из обеих сравниваемых таблиц, в то время как вывод подзапросов используется только в предикатах внешних запросов. Как правило, форма запроса, которая кажется наиболее интуитивной, будет вероятно лучшей в использовании, но при этом хорошо бы знать обе техники для тех ситуаций, когда та или иная могут не работать.
Теперь мы можем говорить о некоторых специальных операторах которые всегда используют подзапросы как аргументы.
Оператор EXISTS используется, чтобы указать предикату, производить ли стандартному или (обычно) соотнесённому подзапросу вывод или нет.
EXISTS — это оператор, который производит истинное или неистинное значение. Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями, использующими булевы операторы AND, OR и NOT. Он берёт подзапрос как аргумент и оценивает его как истинный, если тот производит любой вывод или как неистинный, если тот не делает этого. Он отличается от других операторов тем, что он не может быть неизвестным.
Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков, если и только если один или более заказчиков в этой таблице находятся в San Jose:
SELECT cnum, cname, city
( SELECT * FROM Customers WHERE city = ‘San Jose’);
Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведён подзапросом, и поскольку выражение EXISTS было истинным, делает предикат истинным.
Подзапрос (не соотнесенный) был выполнен только один раз для всего внешнего запроса, и, следовательно, имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат истинным или неистинным для всех строк сразу, что не так уж полезно для извлечения определённой информации.
В вышеупомянутом примере EXISTS должен быть установлен так, чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы, используя в выборе звезду (SELECT *). В этом состоит его отличие от подзапроса, который мог выбрать только один столбец.
Однако в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает — выполняется или нет вывод из подзапроса, а не использует выведенные значения.
В соотнесённом подзапросе предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе точно так же, как и другие операторы предиката, когда используется соотнесённый подзапрос. Это даёт возможность использовать EXISTS как истинный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе. Следовательно, информация из внутреннего запроса будет сохранена, если выведена непосредственно, когда используются EXISTS таким способом.
Например, мы можем вывести продавцов, которые имеют многочисленных заказчиков:
SELECT DISTINCT snum
FROM Customers outer
FROM Customers inner
WHERE inner, snum — outer, snum
AND inner.cnum outer.cnum);
Для каждой строки-кандидата внешнего запроса (представляющей заказчика, проверяемого в настоящее время) внутренний запрос находит строки, которые совпадают со значением поля snum (которое имел продавец), но не со значением поля спит (соответствующего другим заказчикам).
Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика, обслуживаемых текущим продавцом (т.е. продавцом заказчика в текущей строке-кандидате из внешнего запроса).
Предикат EXISTS поэтому верен для текущей строки, и номер продавца поля (snum) таблицы, указанной во внешнем запросе, будет выведен. Если DISTINCT был не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика, к которому он назначен.
Однако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это, объединив таблицу Customers с таблицей Salespeople:
SELECT DISTINCT first.snum, sname, first.city
FROM Salespeople first, Customers second
FROM Customers third
WHERE second.snum = third.snum
AND second.cnum third.cnum)
AND first.snum = second.snum;
Внутренний запрос здесь, как и в предыдущем варианте, фактически сообщает, что псевдоним был изменён. Внешний запрос — это объ-134
единение таблицы Salespeople с таблицей Customers, наподобие того что мы видели прежде. Новое предложение основного предиката (AND first.snum — second.snum), естественно, оценивается на том же самом уровне, что и предложение EXISTS. Это функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах поля snum, которое является для них общим. Из-за булева оператора AND оба условия основного предиката должны быть истинны для истинности предиката.
Следовательно, результаты подзапроса имеют смысл только в тех случаях, когда вторая часть запроса истинна, а объединение выполняемо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.
Предыдущий пример дал понять, что EXISTS может работать в комбинации с операторами Буля. Конечно, то, что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT.
Один из способов, которым мы могли бы найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример.
SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS
FROM Customers inner
WHERE inner, snum = outer, snum
AND inner.cnum outer.cnum);
Одна вещь, которую EXISTS не может сделать, — использовать функцию агрегата в подзапросе. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, невзирая на то, что это — значение функции, если же агрегатная функция не находит никаких строк, EXISTS неправилен.
В языке SQL есть три специальных оператора, ориентированных на подзапросы.
Операторы ALL (все), ANY (любой) и SOME (некоторый) напоминают EXISTS, который воспринимает подзапрос как аргумент; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении они напоминают оператор IN, когда тот используется с подзапросами; они берут все значения, выведенные подзапросом, и обрабатывают их вместе. Однако, в отличие от IN, они могут использоваться только с подзапросами.
Операторы SOME и ANY взаимозаменяемы везде. Различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который наиболее подходит.
Покажем ещё один способ нахождения продавцов с заказчиками, размещёнными в их городах:
WHERE city = ANY
(SELECT city FROM Customers);
Оператор ANY берёт все значения, выведенные подзапросом (для этого случая — это все значения city в таблице Заказчиков), и оценивает их как истинные, если любой (ANY) из них равняется значению города текущей строки внешнего запроса.
В операторе ANY подзапрос должен выбирать значения такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты или нет, и фактически не использует эти результаты.
Оператор ANY может использовать другие реляционные операторы, кроме равняется (=), и таким образом делать сравнения, которые являются выше возможностей IN. Например, можно найти всех продавцов, имена которых предшествуют именам заказчиков:
WHERE sname ALL
(SELECT rating FROM Customers WHERE city — ‘Rome’):
ALL используется в основном с неравенствами, а не с равенствами, так как значение «равный для всех» может быть результатом подзапроса, только если все результаты идентичны.
В SQL выражение “ ALL” соответствует «не равен любому» результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Например,
WHERE rating ALL
(SELECT rating FROM Customers WHERE city = ‘San Jose’);
Здесь подзапрос выбирает оценки для города San Jose: 200 (для Liu) и 300 (для Cisneros). Затем основной запрос выбирает все строки с оценкой, не совпадающей ни с одной из них, другими словами, все строки с оценкой 100.
Значительное различие между ALL и ANY — способ действия в ситуации, когда подзапрос не возвращает никаких значений. Всякий раз, когда допустимый подзапрос не в состоянии сделать вывод, ALL автоматически верен, a ANY автоматически неправилен.
Вывод многих запросов можно объединить, используя предложение UNION. Предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов. Например, чтобы получить всех продавцов и заказчиков, размещённых в Лондоне, и вывести их как единое целое, вы могли бы ввести:
SELECT snum, sname
WHERE city = ‘London’
SELECT cnum, cname
WHERE city = ‘London’;
Когда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов, и каждый должен иметь тип, совместимый с каждым. Символьные поля должны иметь одинаковое число символов. Типы, не определённые ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа.
Другое ограничение на совместимость — если пустые значения (NULL) запрещены в каком-либо столбце объединения, эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения.
Кроме того, мы не можем использовать UNION в подзапросах, а также не можем использовать агрегатные функции в предложении SELECT-запроса в объединении. UNION автоматически исключает дубликаты строк из вывода.
Также можно использовать предложение ORDER BY, чтобы упорядочить вывод из объединения, точно так же как это делается в индивидуальных запросах.
Операция, которая бывает часто полезна, — это объединение из двух запросов, в котором второй запрос выбирает строки, исключённые первым. Это называется внешним объединением.
Предположим, что некоторые из ваших заказчиков ещё не были назначены к продавцам. Можно захотеть увидеть имена и города всех ваших заказчиков с именами их продавцов, не учитывая тех, кто ещё не был назначен. Можно достичь этого, формируя объединение из двух запросов, один из которых выполняет основной вывод, а другой выбирает заказчиков с пустыми (NULL) значениями поля snum. Этот последний запрос должен вставлять пробелы в поля, соответствующие полю sname в первом запросе.
Использование этой методики во внешнем объединении даёт возможность использовать предикаты для классификации, а не для исключения.
Мы использовали пример нахождения продавцов с заказчиками, размещёнными в их городах и раньше. Однако вместо просто выбора только этих строк вы возможно захотите, чтобы ваш вывод перечислял всех продавцов, и указывал тех, кто не имел заказчиков в их городах, и кто имел.
Следующий запрос выполнит это:
SELECT Salespeople.snum, sname, cname, comm
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city.
SELECT snum, sname,’ NO MATCH ‘, comm
WHERE NOT city = ANY(SELECT city FROM Customers)
ORDER BY 2 DESC;
Строка ‘NO MATCH’ была дополнена пробелами, чтобы получить совпадение поля cname по длине (это не обязательно во всех реализациях SQL).
Второй запрос выбирает даже те строки, которые исключил первый.
Всякий раз, когда вы выполняете объединение более чем двух запросов, можно использовать круглые скобки, чтобы определить порядок оценки. Другими словами, вместо просто query X UNION query Y UNION query Z;
можно указать, или
(query X UNION query Y) UNION query Z;
query X UNION (query Y UNION query Z);