Манипулирование данными II:
Дальнейшие операции выборки данных

Подзапросы

Говоря нестрого, подзапрос представляет собой выражение SELECT-FROM-WHERE, которое вложено в другое такое предложение. (Подзапрос может включать также фразу GROUP BY. Однако комбинация ORDER BY и UNION недопустима.) Обычно подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, как иллюстрируется в следующем примере.

Простой подзапрос

Выбрать фамилии поставщиков, поставляющих деталь “винт”.

SELECT   Фамилия

FROM      Поставщики

WHERE   Номер_Поставщика IN

               (SELECT   Номер_Поставщика

                FROM      Детали

                WHERE  Название = “винт”);

Результат:

Фамилия

Блейк

Кларк

Пояснение. При обработке полного запроса система обрабатывает прежде всего вложенный подзапрос. Поэтому первоначальный запрос эквивалентен следующему простому запросу:

SELECT ФАМИЛИЯ
FROM     Поставщики
WHERE  НОМЕР_ПОСТАВЩИКА IN (3,4);

и, следовательно, получаем приведенный ранее результат.
Неявное уточнение фамилии в этом примере требует дополнительного обсуждения. Заметим, в частности, что «НОМЕР_ПОСТАВЩИКА» слева от IN неявным образом уточняется именем таблицы Поставщики, в то время как «НОМЕР_ПОСТАВЩИКА» в подзапросе неявно уточняется именем таблицы Детали. Справедливо следующее общее правило: предполагается, что неуточненное имя поля должно уточняться именем таблицы (или псевдонимом таблицы), указанным в той фразе FROM, которая является непосредственной частью того же самого запроса или подзапроса. В случае поля НОМЕР_ПОСТАВЩИКА слева от IN этой фразой является «FROM Поставщики», а в случае поля НОМЕР_ПОСТАВЩИКА в подзапросе - это фраза «FROM Детали». Для большей ясности повторим первоначальный запрос с явно указанными предполагаемыми уточнениями:

SELECT   Фамилия

FROM      Поставщики

WHERE   Поставщики.Номер_Поставщика IN

               (SELECT   Детали.Номер_Поставщика

                FROM      Детали

                WHERE  Деталиазвание = “винт”);

Неявные уточнения всегда можно отвергнуть путем задания явных уточнений. Вопрос о том, какой из этих формулировок отдать предпочтение,— исключительно дело вкуса данного пользователя.

Подзапрос с несколькими уровнями вложенности также может быть приемлем для решения более сложных поставленных задач.

Коррелированный подзапрос

Выбрать фамилии поставщиков, поставляющих деталь “винт”. Этот пример уже рассматривался. Однако для иллюстрации проблемы, рассматриваемой в данном разделе, приведем иное решение этой задачи.

 

SELECT ФАМИЛИЯ

FROM Поставщики

WHERE 'винт' IN

(SELECT Название

FROM Детали      

WHERE НОМЕР_ПОСТАВЩИКА = Поставщики. НОМЕР_ПОСТАВЩИКА);

Пояснение. В последней строке приведенного запроса неуточненная ссылка на НОМЕР_ПОСТАВЩИКА уточняется неявным образом именем таблицы Детали. Другая ссылка явно уточняется именем таблицы Поставщики. Этот пример отличается от предыдущих тем, что внутренний подзапрос не может быть обработан раз навсегда прежде, чем будет обрабатываться внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, а именно от ПоставщикиОМЕР_ПОСТАВЩИКА, значение которой изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом.

а) Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика 1. Тогда переменная ПоставщикиОМЕР_ПОСТАВЩИКА в данный момент имеет значение 1, и система обрабатывает внутренний запрос

SELECT  Название
FROM       Детали
WHERE    НОМЕР_ ПОСТАВЩИКА = 1;

получая в результате множество ('гайка', 'болт'). Теперь она может завершить обработку для 1 поставщика. Выборка значения ФАМИЛИЯ для 1, а именно Смит, будет произведена тогда и только тогда, когда 'винт' принадлежит этому множеству, что, очевидно, несправедливо.

б) Далее система будет повторять обработку такого рода для
следующего поставщика и т. д. до тех пор, пока не будут
рассмотрены все строки таблицы Поставщики.

Такой подзапрос, как в этом примере, называется коррелированным. Коррелированный подзапрос — это такой подзапрос, результат которого зависит от некоторой переменной. Эта переменная принимает свое значение в некотором внешнем запросе. Обработка такого подзапроса, следовательно, должна повторяться для каждого значения переменной в запросе, а не выполняться раз навсегда. Далее будет приведен другой пример коррелированного подзапроса.

Подзапрос с оператором сравнения, отличным от IN

Выдать номера поставщиков, находящихся в том же городе, что и поставщик №1.

SELECT

FROM

WHERE

*
Поставщики
ГОРОД =

 

(SELECT

FROM

WHERE

ГОРОД
Поставщики
НОМЕР_ПОСТАВЩИКА = 1);

Результат:

Номер_Поставщика

Фамилия

Город

1

Смит

Лондон

4

Кларк

Лондон

 

Иногда пользователь может знать, что заданный подзапрос должен возвратить в точности одно значение, как в рассматриваемом примере. В таком случае можно использовать вместо обычного IN более простой оператор сравнения (например, =, > и т. д.). Однако, если подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка. Ошибка не возникнет, если подзапрос не возвратит вообще ни одного значения. При этом сравнение интерпретируется в точности так, как если бы подзапрос возвратил неопределенное значение. Более того, подзапрос не может включать фразу GROUP BY, если он используется с простым оператором сравнения, например с =, > и т. д.

Стандартные функции

Хотя и весьма мощное во многих отношениях, предложение SELECT в том виде, как оно было до сих пор описано, остается все еще неадекватным для многих практических задач. Например, даже настолько простои запрос как «Сколько имеется поставщиков?» нельзя выразить, используя только введенные до сих пор конструкции. Для того чтобы усилить его основные возможности по выборке данных, в SQL предусматривается ряд специальных стандартных функций. В настоящее время доступны функции COUNT (число значений), SUM (сумма), AVG (среднее), МАХ (максимум) и MIN (минимум). Кроме специального случая «COUNT (*)» (см. ниже) каждая из этих функций оперирует совокупностью значений в одном столбце некоторой таблицы, возможно, производной, т. е. сконструированной некоторым образом из заданных базовых таблиц, и продуцирует в качестве ее результата единственное значение, определенное следующим образом:

COUNT — число значений в столбце
SUM — сумма значений по столбцу
AVG — среднее значение в столбце
МАХ — самое большое значение в столбце
MIN — самое малое значение в столбце

UPPER – перевод строки в верхний регистр

 

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения. В общем случае аргументу функции может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Однако для функций МАХ и MIN ключевое слово DISTINCT не имеет отношения к делу и должно быть опущено. Для функции COUNT ключевое слово DISTINCT должно быть специфицировано. Специальная функция COUNT (*), для которой использование DISTINCT не допускается, предусмотрена для подсчета всех строк в таблице без исключения каких-либо дубликатов. Если DISTINCT специфицируется, то аргумент должен состоять только из имени столбца, например ВЕС. Если DISTINCT не специфицировано, аргумент может представлять собой арифметическое выражение, например Вес*.1000.
В столбце-аргументе всегда перед применением функции исключаются все неопределенные значения, независимо от того, специфицировано ли DISTINCT, за исключением случая COUNT (*), при котором неопределенные значения обрабатываются точно так же, как и значения, не являющиеся неопределенными. Если оказывается, что аргумент — пустое множество, функция COUNT принимает значение нуль. Все другие функции принимают в этом случае неопределенное значение.

ФУНКЦИЯ COUNT ВО ФРАЗЕ SELECT

Выдать общее количество поставщиков

SELECT COUNT (*)
FROM
Поставщики;

Результат:5

ФУНКЦИЯ ВО ФРАЗЕ SELECT СО СПЕЦИФИКАЦИЕЙ DISTINCT

Выдать общее количество поставщиков, поставляющих в настоящее время детали:

SELECT COUNT (DISTINCT НОМЕР_ПОСТАВЩИКА)
FROM    
Детали;

Результат:4

СТАНДАРТНЫЕ ФУНКЦИИ ВО ФРАЗЕ SELECT

·        Выдать самую тяжелую деталь

SELECT MAX (Вес)

FROM     Детали;

Результат: 20

·        Выдать среднее значение веса деталей

SELECT AVG (Вес)

FROM    Детали;

Результат: 15,8

ИСПОЛЬЗОВАНИЕ ФРАЗЫ GROUP BY

Оператор GROUP BY (группировать по) перекомпоновывает таблицу, представленную фразой FROM, в разделы или группы таким образом, чтобы в каждой группе все строки имели одно и то же значение поля, указанного во фразе GROUP BY.

SELECT   Название

FROM      Детали

GROUP   BY Название;

Результат:

Название

болт

гайка

винт

шуруп

Каждое выражение во фразе SELECT должно принимать единственное значение для группы, т. е. оно может быть либо самим полем, указанным во фразе GROUP BY, либо арифметическим выражением, включающим это поле, либо константой, либо такой функцией, как SUM, которая оперирует всеми значениями данного поля в группе и сводит эти значения к единственному значению. Заметим, что фраза GROUP BY не предполагает ORDER BY (упорядочить по). Также можно использовать фразу Where с Group by.Строки, не удовлетворяющие фразе WHERE, исключаются до того, как будет осуществляться какое-либо группирование.

Объединение

Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим исходным множествам. Поскольку отношение — это множество (множество строк), можно построить объединение двух отношений. Результатом будет множество, состоящее из всех строк, входящих в какое-либо одно или в оба первоначальных отношения. Если, однако, этот результат сам по себе должен быть другим отношением, а не просто разнородной смесью строк, то два исходных отношения должны быть совместимыми по объединению. Нестрого говоря, строки в обоих отношениях должны быть одной и той же «формы». Что касается SQL, то две таблицы совместимы по объединению (и к ним может быть применен оператор UNION) тогда и только тогда, когда:
а) они имеют одинаковое число столбцов, например, m;
б) для всех i (i=l,2,..., m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных:

  • если тип данных — DECIMAL (p, q), то р должно быть одинаковым для обоих столбцов и q должно быть одинаковым для обоих столбцов;
  • если тип данных —CHAR (n), то n должно быть одинаковым для обоих столбцов;
  • если тип данных — VARCHAR (n), то n должно быть одинаковым для обоих столбцов;
  • если NOT NULL специфицировано для какого-либо из этих столбцов, то такая же спецификация должна быть для другого столбца.

Запрос, требующий использования UNION

Выдать номера поставщиков, которые проживают в Лондоне или поставляют детали с весом большим 18.

SELECT  Номер_Поставщика

FROM     Поставщики 

WHERE   Город = 'Лондон'

UNION

SELECT  Номер_Поставщика

FROM     Детали 

WHERE   Вес > 18;

 

Результат:

Номер_Поставщика

1

4

Из этого простого примера следует несколько соображений:

  • Избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере деталь выбирается обеими из двух составляющих предложений SELECT, в окончательном результате она появляется только один раз.
  • Любое число предложений SELECT может быть соединено операторами UNION.
  • Любая фраза ORDER BY в запросе должна входить как часть только в последнее предложение SELECT и должна указывать столбцы, по которым осуществляется упорядочение, путем указания их порядковых позиций, т. е. их номеров.
  • В связи с оператором UNION часто оказывается полезной возможность включения констант во фразу SELECT. Например, можно указать, какому из двух условий WHERE удовлетворяет каждая из отдельных деталей.

Заключение

Теперь мы рассмотрели все возможности предложения SELECT языка SQL, которые были намерены проиллюстрировать в этой книге. Чтобы завершить эту главу, приведем весьма изощренный пример, который показывает, каким образом многие (но отнюдь не все) эти средства могут быть использованы вместе в едином запросе. Рассмотрим также концептуальный алгоритм обработки SQL — запросов общего вида.

МНОГОАСПЕКТНЫЙ ПРИМЕР

Выдать номер поставщика, вес в граммах детали, цвет для всех красных и голубых деталей, таких, что средний их вес больше, чем 15. Результат упорядочить по убыванию номеров поставщиков.

SELECT  Деталиомер_Поставщика, 'вес в граммах = ', Детали.ВЕС.1000, Детали.ЦВЕТ,                                                                  

                'средний вес = ', AVG (Деталиес)

FROM     Детали

WHERE 

AND        ДеталиВЕТ IN ('красный', 'голубой')

AND        AVG(Деталиес) > 15

ORDER BY Детали.НОМЕР_Поставщика DESC;

 

 

 

||>ИНФОРМАТИКА<||>СПИСОК<||

Copyright © ForStu

 

 

ForStu / Лекции / Информатика / SQL/PL SQL (Стандарт) /

Copyright © 2004-2017, ForStu

Яндекс.Метрика