- •Iso (международной организацией по
- •2 Понимание sql
- •Что такое - реляционная база данных?
- •Порядок строк произволен
- •4 Понимание sql ___________________________________________________________________
- •Идентификация строк ( первичные ключи )
- •Столбцы именуются и нумеруются
- •8 Понимание sql
- •************** Работа с sql **************
- •Sql : обзор
- •Что делает ansi ?
- •Интерактивный и вложенный sql
- •14 Понимание sql
- •Субподразделения sql
- •16 Понимание sql
- •Sql несогласованности
- •Что такое - пользователь?
- •18 Понимание sql
- •Условия и терминология
- •************** Работа с sql **************
- •24 Понимание sql
- •26 Понимание sql
- •Переупорядочение столбца
- •28 Понимание sql
- •Параметры distinct
- •30 Понимание sql
- •32 Понимание sql
- •************* Работа с sql ***************
- •38 Понимание sql
- •40 Понимание sql
- •42 Понимание sql
- •44 Понимание sql
- •Использование специальных операторов в условиях
- •50 Понимание sql
- •52 Понимание sql
- •54 Понимание sql
- •56 Понимание sql
- •************** Работа с sql **************
- •Обобщение данных с помощью агрегатных функций
- •64 Понимание sql
- •66 Понимание sql
- •Включение дубликатов в агрегатные функции
- •Предложение group by
- •68 Понимание sql
- •Предложение having
- •70 Понимание sql
- •72 Понимание sql
- •************** Работа с sql **************
- •Формирование выводов запросов
- •Помещение текста в вашем выводе запроса
- •78 Понимание sql
- •80 Понимание sql
- •82 Понимание sql
- •Упорядочение вывода по номеру столбца
- •84 Понимание sql
- •************** Работа с sql **************
- •Запрашивание многочисленых таблиц также как одной
- •90 Понимание sql
- •92 Понимание sql
- •94 Понимание sql
- •************** Работа с sql **************
- •Объединение таблицы с собой
- •Псевдонимы
- •100 Понимание sql
- •Устранение избыточности
- •102 Понимание sql
- •Больше псевдонимов
- •104 Понимание sql
- •106 Понимание sql
- •************** Работа с sql **************
- •Вставка одного запроса внутрь другого
- •112 Понимание sql
- •114 Понимание sql
- •116 Понимание sql
- •In определяет набор значений, одно из которых должно совпадать с другим
- •118 Понимание sql
- •In является подходящим, если запрос может ограниченно производить одно
- •120 Понимание sql
- •122 Понимание sql
- •*************** Работа с sql *************
- •Соотнесенные подзапросы
- •130 Понимание sql
- •132 Понимание sql
- •Соотнесенные подзапросы в предложении having
- •134 Понимание sql
- •*************** Работа с sql *************
- •Использование оператора exists
- •140 Понимание sql
- •142 Понимание sql
- •144 Понимание sql
- •146 Понимание sql
- •************** Работа с sql **************
- •Использование оператора exists
- •152 Понимание sql
- •154 Понимание sql _____________________________________________________________________
- •156 Понимание sql
- •158 Понимание sql
- •160 Понимание sql
- •162 Понимание sql
- •Использование count вместо exists
- •166 Понимание sql
- •************** Работа с sql **************
122 Понимание sql
______________________________________________________________________
ГЛ. 10
=============== 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 |
| -------- -------- |
| 200 2 |
================================================
Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose
Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose.
Так как имеются другие оценки отличные от 300, они должны быть выведены
с числом номеров заказчиков которые имели эту оценку.
=============== РЕЗЮМЕ ===============
Теперь вы используете запросы в иерархической манере. Вы видели, что
использование результата одного запроса для управления другим, расширяет
возможности позволяющие выполнить большее количество функций.
Вы теперь понимаете как использовать подзапросы с реляционными опера-
торами также как и со специальным оператором IN, или в предложении
WHERE или в предложении HAVING внешнего запроса.
В следующих главах, мы будем разрабатывать подзапросы.
Сначала в Главе 11, мы обсудим другой вид подзапроса, который выполняется
отдельно для каждой строки таблицы вызываемой во внешнем запросе.
Затем, в Главе 12 и 13, мы представим вас нескольким специальным операто-
рам которые функционируют на всех подзапросах, как это делает IN, за иск-
лючением когда эти операторы могут использоваться только в подзапросах.
ПОМЕЩЕНИЕ ОДНОГО ЗАПРОСА ВНУТРИ ДРУГОГО 123
______________________________________________________________________
*************** Работа с sql *************
1. Напишите запрос, который бы использовал подзапрос для получения
всех порядков для заказчика с именем Cisneros.
Предположим, что вы не знаете номера этого заказчика, указываемого
в поле cnum.
2. Напишите запрос который вывел бы имена и оценки всех заказчиков
которые имеют усредненые порядки.
3. Напишите запрос который бы выбрал общую сумму всех приобретений
в порядках для каждого продавца, у которого эта общая сумма больше
чем сумма наибольшего порядка в таблице.
( См. Приложение A для ответов. )
11
Соотнесенные подзапросы
126 ПОНИМАНИЕ SQL
______________________________________________________________________
ГЛ. 11
В ЭТОЙ ГЛАВЕ, МЫ ПРЕДСТАВИМ ВАС ТИПУ
подзапроса о котором мы не говорили в Главе 10 - посвященной соотне-
сенному подзапросу. Вы узнаете как использовать соотнесенные подзапросы
в предложениях запросов WHERE и HAVING.
Сходства и различия между соотнесенными подзапросами и обьединениями
будут обсуждаться далее, и вы сможете повысить ваше знание псевдонимов
и префиксов имени таблицы - когда они необходимы и как их использовать.
КАК СФОРМИРОВАТЬ
========= СООТНЕСЕННЫЙ ПОДЗАПРОС ===========
Когда вы используете подзапросы в SQL, вы можете обратиться к внутрен-
нему запросу таблицы в предложении внешнего запроса FROM , сформи-
ровав - соотнесенный подзапрос. Когда вы делаете это, подзапрос выполня-
ется неоднократно, по одному разу для каждой строки таблицы основного
запроса.
Соотнесенный подзапрос - один из большого количества тонких понятий
в SQL из-за сложности в его оценке.
Если вы сумеете овладеть им, вы найдете что он очень мощный, потому что
может выполнять сложные функции с помощью очень лаконичных указаний.
Например, имеется один способ найти всех заказчиков в порядках на 3-е
Октября ( вывод показывается в Рисунке 11.1 ):
SELECT *
FROM Customers outer
WHERE 10/03/1990 IN
( SELECT odate
FROM Orders inner
WHERE outer.cnum = inner.cnum );
КАК РАБОТАЕТ СООТНЕСЕННЫЙ ПОДЗАПРОС
В вышеупомянутом примере, "внутренний"(inner) и "внешний"(outer), это
псевдонимы, подобно обсужденным в Главе 9. Мы выбрали эти имена для
большей ясности; они отсылают к значениям внутренних и внешних зап-
росов, соответственно. Так как значение в поле cnum внешнего запроса
меняется, внутренний запрос должен выполняться отдельно для каждой
строки внешнего запроса. Строка внешнего запроса для которого внутрен-
СООТНЕСЕННЫЕ ПОДЗАПРОСЫ 127
______________________________________________________________________
=============== SQL Execution Log ============
| |
| 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 |
=============================================
Рисунок 11.1: Использование соотнесенного подзапроса
ний запрос каждый раз будет выполнен, называется - текущей
строкой-кандидатом. Следовательно, процедура оценки выполняемой
соотнесенным подзапросом - это:
1. Выбрать строку из таблицы именованной в внешнем запросе.
Это будет текущая строка-кандидат.
2. Сохранить значения из этой строки-кандидата в псевдониме с именем
в предложении FROM внешнего запроса.
3. Выполнить подзапрос. Везде, где псевдоним данный для внешнего
запроса найден ( в этом случае "внешний" ), использовать значение
для текущей строки-кандидата. Использование значения из строки-
кандидата внешнего запроса в подзапросе называется - внешней
ссылкой.
4. Оценить предикат внешнего запроса на основе результатов подзапроса
выполняемого в шаге 3. Он определяеть - выбирается ли строка-кандидат
для вывода.
5. Повторить процедуру для следующей строки-кандидата таблицы, и так
далее пока все строки таблицы не будут проверены.
128 ПОНИМАНИЕ SQL
______________________________________________________________________
ГЛ. 11
В вышеупомянутом примере, SQL осуществляет следующую процедуру:
1. Он выбирает строку Hoffman из таблицы Заказчиков.
2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом -
"внешним".
3. Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу
Порядков чтобы найти строки где значение cnum поле - такое же как
значение outer.cnum, которое в настоящее время равно 2001, - поле
cnum строки Hoffmanа.
Затем он извлекает поле odate из каждой строки таблицы Порядков для
которой это верно, и формирует набор значений поля odate.
4. Получив набор всех значений поля odate, для поля cnum = 2001,
он проверяет предикат основного запроса чтобы видеть имеется ли зна-
чение на 3 Октября в этом наборе. Если это так(а это так), то он выбирает
строку Hoffmanа для вывода ее из основного запроса.
5. Он повторяет всю процедуру, используя строку Giovanni как строку-канди-
дата, и затем сохраняет повторно пока каждая строка таблицы Заказчиков
не будет проверена.
Как вы можете видеть, вычисления которые SQL выполняет с помощью этих
простых инструкций - это полный комплекс. Конечно, вы могли бы решить ту
же самую проблему используя обьединение, следующего вида ( вывод для
этого запроса показывается в Рисунке 11.2 ):
SELECT *
FROM Customers first, Orders second
WHERE first.cnum = second.cnum
AND second.odate = 10/03/1990;
Обратите внимание что Cisneros был выбран дважды, по одному разу для
каждого порядка который он имел для данной даты. Мы могли бы устранить это
используя SELECT DISTINCT вместо просто SELECT. Но это необязательно
в варианте подзапроса. Оператор IN, используемый в варианте подзапроса, не
делает никакого различия между значениями которые выбираются подзапросом
один раз и значениями которые выбираются неоднократно. Следовательно
DISTINCT необязателен.
СООТНЕСЕННЫЕ ПОДЗАПРОСЫ 129
______________________________________________________________________
=============== SQL Execution Log ============
| |
| SELECT * |
| FROM Customers first, Orders second |
| WHERE first.cnum = second.cnum |
| (SELECT COUNT (*) |
| FROM Customers |
| WHERE snum = main.snum; |
| ============================================= |
| cnum cname |
| ----- -------- |
| 1001 Peel |
| 1002 Serres |
=============================================
Рисунок 11. 2 Использование обьединения вместо соотнесенного
подзапроса
Предположим что мы хотим видеть имена и номера всех продавцов которые
имеют более одного заказчика. Следующий запрос выполнит это для вас
( вывод показывается в Рисунке 11.3 ):
SELECT snum, sname
FROM Salespeople main
WHERE 1 <
( SELECT COUNT (*)
FROM Customers
WHERE snum = main.snum );
Обратите внимание что предложение FROM подзапроса в этом примере не
использует псевдоним. При отсутствии имени таблицы или префикса псев-
донима, SQL может для начала принять, что любое поле выводится из таблицы
с именем указанным в предложении FROM текущего запроса. Если поле с этим
именем отсутствует( в нашем случае - snum ) в той таблице, SQL будет проверять
внешние запросы. Именно поэтому, префикс имени таблицы обычно необходим в
соотнесенных подзапросах - для отмены этого предположения. Псевдонимы также
часто запрашиваются чтобы давать вам возможность ссылаться к той же самой
таблице во внутреннем и внешнем запросе без какой-либо неоднозначности.