Максимов Е.М. Структурированный язык запросов

Подождите немного. Документ загружается.

Структурированный язык запросов – SQL, страница 21

Допускается использовать NOT с IN. Например, для вывода данных об

успеваемости студентов, номер билета которых не 3412 и 3413, причем

предложим это сделать двумя способами:

SELECT * FROM USP

WHERE SNUM NOT IN (3412, 3413);

Или

SELECT * FROM USP WHERE NOT SNUM IN (3412, 3413);

что в обоих случаях предоставит следующий вывод:

UNUM OCENKA UDATE SNUM PNUM

1003 3 11/06/1999 3414 2005

1005 5 12/06/1999 3416 2004

Аналогичным способом можно использовать NOT BETWEEN и NOT

LIKE , т.е. можно использовать весь набор стандартных математических и

специальных операторов.

Получение итоговых данных

Вообще говоря, запросы могут производить обобщенную групповую

обработку значений полей, что реализуется с помощью агрегатных функций.

Агрегатные функции производят одиночное значение для всей группы

таблицы. В SQL допускаются следующие агрегатные функции:

 COUNT - производит подсчет количества строк или не-NULL значений

полей, которые выбрал запрос;

 SUM - рассчитывает арифметическую сумму всех выбранных значений

данного поля;

 AVG - производит усреднение всех выбранных значений данного поля:

 МАХ - находит и возвращает наибольшее из всех выбранных значений

данного поля:

 MIN - находит и возвращает наименьшее из всех выбранных значений

данного поля.

Агрегатные функции используются подобно именам полей в предложении

SELECT запроса, но с учетом того, что они берут имена поля в качестве

аргументов. Стоит иметь в виду, что с SUM и AVG используются только

числовые поля, а с COUNT, МАХ и MIN могут использоваться числовые или

символьные поля. Когда функции записываются с символьными полями,

МАХ и MIN используют их в эквивалент ASCII, в соответствии с которым

выбирается максимальное или минимальное значение.

Структурированный язык запросов – SQL, страница 22

Чтобы найти сумму всей выплаченной стипендии в таблице с данными о

студентах, можно использовать следующий запрос:

SELECT SUM (STIP) FROM STUDENTS;

вывод которого состоит из единственного числа 68.00.

Действие агрегатных функций, как видно, отличается от выбора поля,

при котором возвращается, например, одиночное значение. По этой причине

агрегатные функции и поля не могут совместно использоваться без

предложения GROUP BY, о котором речь пойдет ниже.

Функция COUNT несколько отличается от остальных - как уже было

замечено, она считает количество значений в данном столбце, или число

строк в таблице. В частности, для подсчета количества значений в столбце,

она используется с DISTINCT . Например, можно подсчитать количество

студентов, сдававших учебные предметы по таблице успеваемости с

помощью следующего запроса:

SELECT COUNT (DISTINCT SNUM) FROM USP;

В качестве результата этого запроса будет получено значение 4. Обратите

внимание на обязательное требование того, чтобы DISTINCT был помещен в

круглые скобки в отличие от предыдущих примеров. Кроме того,

допускается возможность использования DISTINCT с любыми агрегатными

функциями, но наиболее часто он используется с COUNT.

Для того чтобы подсчитать общее число строк в таблице используют

функцию COUNT со звездочкой вместо имени поля, например как в

следующем примере:

SELECT COUNT (*) FROM STUDENTS;

что в качестве результата даст значение 5.

COUNT со звездочкой включает записи с NULL значениями, а также

дубликаты, по этой причине DISTINCT в данном случае не может быть

использован.

Агрегатные функции в большинстве реализаций допускают

использование аргумента ALL, который помещается перед именем поля

аналогично DISTINCT, однако означает противоположное действие -

включать дубликаты. Необходимо пояснить различия между ALL и * . Когда

они используются с COUNT - ALL использует имя поля как аргумент и не

может подсчитать значения NULL. При этом стоит помнить, что функции,

отличные от COUNT, игнорируют значения NULL в любом случае. Следую-

щий пример подсчитает количество не-NULL значений в поле SNUM,

включая повторения:

SELECT COUNT (ALL SNUM)

Структурированный язык запросов – SQL, страница 23

FROM USP;

В качестве результата этого запроса будет получено значение 5.

В SQL допускается использовать агрегатные функции с аргументами,

которые состоят из выражений, включающих одно или более полей, при этом

команда DISTINCT не разрешается. Предположим, что необходимо найти

максимальную величину проиндексированной (в примере, увеличенной

вдвое) стипендии. Для каждой строки таблицы такой запрос должен

умножать STIP на 2 и выбирать самое большое значение, которое будет

найдено. Для этого можно воспользоваться следующим:

SELECT MAX (STIP*2) FROM STUDENTS;

В качестве результата здесь будет получено число 51.00.

Команда GROUP BY позволяет определять подмножество значений

в поле в терминах другого поля, и применять функцию агрегата к такому

подмножеству. Это даст возможность объединять поля и агрегатные функции

в едином предложении SELECT. Например, если возникает необходимость в

определении наименьшей оценки, полученной каждым студентом, то можно

сделать с использованием GROUP BY следующий запрос:

SELECT SNUM, MIN (OCENKA) FROM USP GROUP BY SNUM;

Вывод для этого запроса показан ниже:

SNUM

3412 4

3413 4

3414 3

3416 5

Команда GROUP BY может применяться с агрегатными функциями

независимо от серий групп, которые определяются с помощью значения поля

в целом. В этом случае каждая группа состоит из всех строк с тем же самым

значением поля SNUM, и MIN функция применяется отдельно для каждой

такой группы. Значение поля, к которому применяется GROUP BY, имеет

только одно значение на группу вывода, так же как это делает агрегатная

функция. Поэтому в результате и появляется совместимость, которая

позволяет агрегатным функциям и полям объединиться.

В принципе, допускается использование GROUP BY одновременно с

несколькими полями. Для примера модифицируем предыдущий случай

таким образом, чтобы выводилось наименьшее значение оценки за каждый

день. При этом запрос будет следующий:

Структурированный язык запросов – SQL, страница 24

SELECT SNUM, UDATE, MIN (OCENKA) FROM USP GROUP BY

SNUM, UDATE;

Вывод для этого запроса будет следующий:

SNUM UDATE

3412 10/06/1999 5

3412 12/06/1999 4

3413 10/06/1999 4

3414 11/06/1999 3

3416 12/06/1999 5

Теперь предположим, что в предыдущем примере необходимо увидеть

только минимальную оценку, меньшую 5. Напрямую использовать

агрегатную функцию в предложении WHERE в этом случае нельзя, потому

что предикаты оцениваются в терминах одиночной строки, а агрегатные

функции - групп строк. Поэтому необходимо воспользоваться предложением

HAVING, определяющим критерии, используемые для удаления определен-

ных групп из вывода, наподобие тому, как это делает предложение WHERE

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

SELECT SNUM, UDATE, MIN (OCENKA) FROM USP

GROUP BY SNUM, UDATE HAVING MIN (OCENKA)<5;

Вывод для этого запроса приведен ниже:

SNUM UDATE

3412 12/06/1999 4

3413 10/06/1999 4

3414 11/06/1999 3

Аргументы в предложении HAVING подчиняются тем же самым

правилам, что и в предложении SELECT, состоящем из команд,

использующих GROUP BY: они должны иметь одно значение на группу

вывода. Например, следующая команда будет запрещена:

SELECT SNUM, MIN (OCENKA) FROM USP GROUP BY SNUM,

HAVING UDATE = 10/06/1999;

Поле UDATE не может быть вызвано предложением HAVING, потому что

оно может иметь больше чем одно значение на группу вывода,

следовательно, для избежания такой ситуации, предложение HAVING

должно ссылаться только на агрегатные функции и поля, выбранные в

GROUP BY. Правильный способ сделать рассмотренный запрос следующий:

Структурированный язык запросов – SQL, страница 25

SELECT SNUM, MIN (OCENKA) FROM USP

WHERE UDATE = 10/06/1999 GROUP BY SNUM;

Этот текст запроса может работать в СУБД, которые имеют поле типа DATA.

В Access нет такого типа поля. Есть только поле типа Дата/время. Поэтому

запишем запрос в виде:

SELECT SNUM, MIN (OCENKA) FROM USP

WHERE UDATE = 36321 GROUP BY SNUM;

Число 36321 соответствует искомой дате в формате TIMESTAMP.

Результат такого запроса следующий:

SNUM

3412 5

3413 4

Поскольку поле UDATE отсутствует в выводе, то для повышения

удобочитаемости в результаты стоит включить поясняющий текст о том, что

это за данные.

Как говорилось выше, HAVING может использовать только аргументы,

которые имеют одно значение на группу вывода. Практически же ссылки на

поля, выбранные с помощью GROUP BY, также допустимы. Например, для

вывода наименьших оценок для студентов с номерами 3412 и 3413 можно

воспользоваться следующим:

SELECT SNUM, MIN (OCENKA) FROM USP GROUP BY SNUM HAVING

SNUM IN (3412, 3413);

Вывод для этого запроса следующий:

SNUM

3412 4

3413 4

В заключение заметим, что следует быть внимательным при

использовании таких функций: нельзя использовать агрегатную функцию от

агрегатной функции.

С учетом вышесказанного, с данного момента можно использовать

запросы для получения определенных значений и формировать вывод

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

Сортировка результатов запроса

Структурированный язык запросов – SQL, страница 26

Большинство БД, работающих с SQL, предоставляют специальные

средства, позволяющие совершенствовать вывод запросов.

Предположим, что есть необходимость выполнить простые числовые

вычисления с данными, выводимыми в качестве результата запроса. SQL

позволяет помещать выражения и константы среди выбранных полей. Эти

выражения могут дополнять или замещать поля в предложениях SELECT,

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

Например, если необходимо просмотреть проиндексированную стипендию,

увеличив ее в два раза, то можно воспользоваться запросом:

SELECT SFAM, SIMA, SOTCH, STIP*2 FROM STUDENTS;

Вывод этого запроса будет такой:

SFAM SIMA SOTCH

Поляков Анатолий Алексеевич 51.00

Старова Любовь Михайловна 34.00

Гриценко Владимир Николаевич 0.00

Котенко Анатолий Николаевич 0.00

Нагорный Евгений Васильевич 51.00

Следует обратить внимание на то, что последний столбец без наиме-

нования, потому что это - столбец вывода. Столбцы вывода это - столбцы

данных, созданные запросом способом, иным, чем просто извлечение их из

таблицы. Такие столбцы создаются каждый раз. когда используются

функции, константы или выражения в предложении SELECT запроса. Т.к.

имя столбца - один из атрибутов таблицы, столбцы которые появляются не из

таблиц, не имеют никаких имен.

Достаточно часто возникает необходимость в размещении текста в

выводе запроса. Например, для повышения удобства работы с результатами

предыдущего запроса, можно вставить сокращенное название единицы

измерения проиндексированной стипендии - условных единиц, что

выполняется следующим запросом:

SELECT SFAM, SIMA, SOTCH, 'y.e.', STIP*2 FROM STUDENTS;

Вывод этого запроса будет следующий:

SFAM SIMA SOTCH

Поляков Анатолий Алексеевич у.е. 51.00

Старова Любовь Михайловна у.е. 34.00

Гриценко Владимир Николаевич у.е. 0.00

Котенко Анатолий Николаевич у.е. 0.00

Нагорный Евгений Васильевич у.е. 51.00

Структурированный язык запросов – SQL, страница 27

Стоит иметь в виду, что все символы, в т.ч. пробелы, в строке текста

также вставляются в вывод, поэтому предложенный способ можно

использовать для маркирования вывода вместе со вставляемыми

комментариями. Однако необходимо помнить, что этот же самый

комментарий будет напечатан в каждой строке вывода, а не просто один раз

для всей таблицы. Предположим, что необходим отчет о количестве

студентов, получающих ту или иную стипендию, тогда можно предложить

следующий запрос:

SELECT COUNT (DISTINCT SNUM), 'студ. получают

стипендию ', STIP, ' у. е.' FROM STUDENTS

GROUP BY STIP;

Запрос неверен. Нужно построить правильный запрос.

В результате будет получено:

STIP

2 студ. получают стипендию 0.00 у. е.

1 студ. получают стипендию 17.00 у. е.

2 студ. получают стипендию 25.00 у. е.

Некорректность вывода текста для стипендии 17.00 нельзя избежать, не

создав более сложной конструкции для вывода, чем предложенная. Как

можно видеть, неизменный комментарий для каждой строки таблицы может

быть очень полезен, но он имеет ряд ограничений. Иногда полезнее вывести

один комментарий для всего вывода в целом или производить свой

собственный комментарий для каждой строки, однако это обеспечивают

различные программы, использующие SQL и имеющие средства генератора

отчетов.

Для упорядочения вывода полей таблиц SQL использует команду

ORDER BY, позволяя сортировать вывод запроса согласно значениям в том

или ином количестве выбранных столбцов. Если указывается несколько

полей, то столбцы вывода упорядочиваются один внутри другого, при этом

можно определять возрастание (ASC) или убывание (DESC) для каждого

столбца. По умолчанию установлено возрастание.

В качестве примера используем запрос, выводящий таблицу с информацией о

студентах в алфавитном порядке фамилий:

SELECT * FROM STUDENTS ORDER BY SFAM ASC;

Вывод этого запроса приведен ниже:

SNUM SFAM SIMA SOTCH STIP

3414 Гриценко Владимир Николаевич 0.00

3415 Котенко Анатолий Николаевич 0.00

Структурированный язык запросов – SQL, страница 28

3416 Нагорный Евгений Васильевич 25.50

3412 Поляков Анатолий Алексеевич 25.50

3413 Огарева Любовь Михайловна 17.00

Приведем пример для упорядочивания информации по нескольким

столбцам. Например, информацию из таблицы с данными о студентах

упорядочим по уменьшению размера стипендии, а для студентов, имеющих

одинаковый ее размер - в алфавитном порядке их фамилий. Для этого

воспользуемся запросом:

SELECT * FROM STUDENTS

ORDER BY STIP DESC, SFAM ASC;

Результаты запроса следующие:

SNUM SFAM SIMA SOTCH STIP

3416 Нагорный Евгений Васильевич 25.50

3412 Поляков Анатолий Алексеевич 25.50

3413 Старова Любовь Михайловна 17.00

3414 Гриценко Владимир Николаевич 0.00

3415 Котенко Анатолий Николаевич 0.00

Аналогичным образом допускается использовать ORDER BY сразу с

любым числом столбцов, однако поля, по которым происходит

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

SELECT SNUM, STIP FROM STUDENTS ORDER BY SFAM ASC;

будет запрещен, т.к. поле SFAM не было выбранным полем, и ORDER BY не

смог его найти для упорядочения вывода.

ORDER BY может использоваться с GROUP BY для упорядочения групп,

при этом ORDER BY должен быть последним. Например, выведем уже

рассмотренный нами отчет о количестве студентов, получающих ту или

иную стипендию, но с упорядочиванием по убыванию размеров их

стипендий:

SELECT count (snum), ' студ. получают стипендию '

, stip, ' у. е. ' FROM STUDENTS

GROUP BY STIP ORDER BY STIP DESC;

В результате будет получено:

STIP

2 студ. получают стипендию 25.00 у. е.

Структурированный язык запросов – SQL, страница 29

1 студ. получают стипендию 17.00 у. е.

2 студ. получают стипендию 0.00 у. е.

Вместо имен столбцов, можно указывать их порядковые номера для

указания поля, используемого в упорядочении вывода. Эти номера могут

ссылаться не на порядок столбцов в таблице, а на их порядок в выводе.

Таким образом, поле, упомянутое в предложении SELECT первым, для

ORDER BY имеет номер 1 независимо от того, каким по порядку оно стоит в

таблице. Например, запрос, выводящий информацию о студентах в алфавит-

ном порядке фамилий, можно записать так:

SELECT SNUM, SFAM, SIMA FROM STUDENTS ORDER BY 2 ASC;

Вывод этого запроса приведен ниже:

SNUM SFAM SIMA

3414 Гриценко Владимир

3415 Котенко Анатолий

3416 Нагорный Евгений

3412 Поляков Анатолий

3413 Старова Любовь

Основная цель ключевого слова ORDER BY - дать возможность

использовать эту команду со столбцами вывода так же, как и со столбцами

таблицы - ведь иногда требуется произвести упорядочивание вывода по

столбцам, производимым агрегатной функцией, константами или

выражениями в предложении SELECT запроса. Например, попробуем

рассмотреть отчет о количестве студентов, получающих ту или иную

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

SELECT COUNT (SNUM), 'студ. получают стипендию ', STIP,

' у. е.' FROM STUDENTS GROUP BY STIP ORDER BY 1 DESC;

В результате будет следующий вывод:

STIP

2 студ. получают стипендию 25.00 у. е.

2 студ. получают стипендию 0.00 у. е.

1 студ. получают стипендию 17.00 у. е.

Следовательно, для упорядочивания вывода по столбцу вывода

необходимо использовать номер столбца, т.к. он не имеет имени и нельзя

использовать саму агрегатную функцию.

Структурированный язык запросов – SQL, страница 30

Таким образом, используя команду ORDER BY, можно заставить

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

ими.

Объединение результатов нескольких запросов

Выше нами рассмотрены запросы, которые использовали в качестве

источника данных только одну таблицу данных - так называемые

однотабличные запросы. Однако часто возникает необходимость в выборе

информации из нескольких таблиц. Одним из вариантов осуществления

такого вывода является объединение результатов нескольких запросов,

выполняющихся независимо друг от друга.

Для размещения нескольких запросов вместе и объединения их

вывода используют предложение UNION. Предложение UNION объединяет

вывод двух или более SQL запросов в единый набор строк и столбцов.

Например, для получения списка всех студентов и преподавателей, фамилии

которых заключены между отавами К' и 'С, можно воспользоваться запросом:

SELECT SFAM, SIMA, SOTCH FROM STUDENTS

WHERE SFAM BETWEEN 'K' AND 'C' UNION SELECT TFAM, TIMA,

TOTCH

FROM TEACHERS

WHERE TFAM BETWEEN ' К ' AND ' С ' ;

Результаты этого запроса показаны ниже:

Казанко Виталий Владимирович

Костыркин Олег Владимирович

Познякова Любовь Алексеевна

Котенко Анатолий Николаевич

Нагорный Евгений Васильевич

Поляков Анатолий Алексеевич

Отсюда можно сделать вывод, что записи, выбранные двумя

командами, выведены так, как если бы она была одна. Следует обратить

внимание на то, что только последний запрос заканчивается точкой с запятой

- отсутствие точки с занятой дает понять SQL, что имеется еще один или

более запросов.

Максимов Е.М. Структурированный язык запросов - SQL

Подождите немного. Документ загружается.