Карпова Т.С. Базы данных: модели, разработка, реализация

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

Задания

для

самостоятельной

работы

Наличие

неопределенных

(Null)

значений

повышает гибкость обработки инфор-

мации,

хранящейся

в БД. В

наших примерах

мы

можем предположить

ситуа-

цию,

когда

студент

пришел

на

экзамен,'

но

не

сдавал

его по

некоторой

причине,

этом случае оценка

по

некоторой дисциплине

для

данного

студента

имеет

не-

определенное значение.

данной

ситуации,

можно поставить вопрос:

«Найти

студентов,

пришедших

па

экзамен,

но не

сдававших

его с

указанием названия

дисциплины».

Оператор

SELECT

будет

выглядеть следующим образом:

SELECT

ФИО,

Дисциплина

FROM

WHERE

Оценка

NULL

Результат;

ФИО

Миронов

А. В.

Дисциплина

Теория

информации

Применение агрегатных функций

вложенных запросов

операторе выбора

В SQL

добавлены

дополнительные

функции,

которые позволяют вычислять обоб-

щенные групповые

значения.

Для

применения агрегатных функций предполага-

ется

предварительная

операция

группировки.

чем

состоит

суть

операции

группировки?

При

группировке

все

множество кортежей отношения разбивает-

ся на

группы,

Которых

собираются кортежи, имеющие одинаковые

значения

атрибутов, которые заданы

списке

группировки.

Например,

сгруппируем

отношение

R1 по

значению

столбца

Дисциплина.

Мы по-

лучим

группы,

для

которых можем вычислить

некоторые

групповые

значения»

например

количество

кортежей

группе,

максимальное

или

минимальное

зна-

чение

столбца

Оценка.

Это

делается

помощью

агрегатных

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

функции

вычисляют

одиночное

значение

для

всей

группы таблицы.

Список

этих

функций

представ-

лен

таблице 5.7.

Таблица

5.7.

Агрегатные

функции

Функция

COUNT

SUM

AVG

MIN

MAX

Результат

Количество

строк

пли

непустых

значений

нолей,

которые

ныбрал

запрос

Сумма

всех

выбранных

значений

данного

ноля

Среднеарифметическое

значение

Наименьшее

из

всех

выбранных

Наибольшее

из

иссх

выбранных

всех

выбранных

значений

данного

поля

знамений

данного

поля

значений

данного

ноля

Глава

Язык

SQL

Формирование

запросов

базе

данных

Группа

ФИО

Петров

Ф. И»

Сидоров

К. А.

Миронов

А. В.

Степанова

К. Е

Крылова

Т. С.

Владимиров

В. А.

Сидоров

К. А.

Степанова

К. Е.

Крылова

Т. С.

Миронов

А.

В.

Трофимов

П. А.

Иванова

Е. А.

Уткина

Н. В.

Владимиров

В, А.

Трофимов

П, А,

Иванова

Е. А.

Петров

Ф. И.

Дисциплина

Базы

данных

Базы

данных

Базы

данных

Базы

данных

Базы

данных

Базы

данных

Теория

информации

Теория

информации

Тдория

информации

Теория

информации

Сети

телекоммуникации

Сети

телекоммуникации

Сети

телекоммуникации

Английский

язык

Английский

язык

Английский

язык

Английский

язык

Оценка

Null

Агрегатные

функции

используются

подобно именам полей

операторе

SELECT,

но с

одним исключением:

они

берут

имя

поля

как

аргумент.

функциями

SUM

AVG

могут использоваться только числовые поля.

функциями

COUNT,

MAX

MIN

могут использоваться

как

числовые,

так и

символьные поля.

При

использова-

нии

символьными полями

МАХ

MIN

будут

транслировать

их в

эквивалент

ASCII

кода

обрабатывать

алфавитном порядке, Некоторые СУБД позволя-

ют

использовать

вложенные агрегаты,

но это

является

отклонением

от

стандар-

та

ANSI

со

всеми вытекающими отсюда последствиями.

Например, можно вычислить количество студентов, сдававших экзамены

по

каж-

дой

дисциплине,

Для

этого

надо'выполнить

запрос

группировкой

по

полю

«Дисциплина*

вывести

качестве результата название дисциплины

количе-

ство строк

группе

по

данной дисциплине. Применение символа

* в

качестве

аргумента

функции

COUNT

означает подсчет всех строк

группе.

SELECT

^.Дисциплина,

COUNT(*)

FROM

GROUP

Rl.Дисциплина

Результат;

Применение агрегатных функций

вложенных

запросов

операторе выбора

Дисциплина

Базы

данных

Теория информации

Сети

телекоммуникации

Английский

язык

COUNTH

Если

же мы

хотим сосчитать количество сдавших экзамен

по

какой-либо дисци-

плине,

то

нам

необходимо исключить неопределенные

значения

из

исходного

отношения перед группировкой.

этом случае запрос

будет

выглядеть следую-

щим

образом:

SELECT

R1.Дисциплина,

С01Ш*)

FROM

WHERE

R1.Оценка

IS NOT

NULL

GROUP

Rl.Дисциплина

Получим

результат:

Дисциплина

Базы

данных

Теория

информации

Сети

телекоммуникации

Английский

язык

COUNT(*)

не

попадет

набор кортежей перед

группировкой,

поэтому количество корте-

жей

группе

для

дисциплины

«Теория

информации»

будет

на 1

меньше,

Можно

применять агрегатные функции также

без

операции предварительной

группировки,

этом случае

все

отношение

рассматривается

как

одна группа

для

этой группы

можно

вычислить

одно

значение

на

группу.

Обратившись

снова

базе данных

«Сессия»

(таблицы

Rl,

R2,

R3), найдем

коли-

чество успешно

сданных

экзаменов:

SELECT

COUNT(*)

FROM

WHERE

Оценка

> 2:

Это, конечно,

отличается

от

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

ночное

значение,

независимо

от

того, сколько строк находится

таблице.

Аргу-

ментом

агрегатных

функций могут быть отдельные столбцы таблиц.

Но для

Миронов

А. В.

Теория информации

Null

этом

случае

строка

со

студентом

Глава6.

Язык SQL. Формирование запросов

базе данных

того, чтобы

вычислить,

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

значений

некоторого

столбца

группе, необходимо

применить

ключевое

слово

DISTINCT

совместно

именем

столбца. Вычислим количество различных

оценок,

полученных

по

каж-

дой

дисциплине:

SELECT

^.Дисциплина.

COUNTCDISTINCT

R1.Оценка)

FROM

WHERE

Rl-Оценка

IS NOT

NULL

GROUP

Rl.Дисциплина

Результат:

Дисциплина

Вазы

данных

Теория

информации

Сети

телекоммуникации

Английский

язык

COUNT(DISTINCT

.Оценка)

результат можно включить

значение

поля

группировки

несколько агрегат-

ных

функций,

а в

условиях группировки можно

использовать

несколько полей.

При

этом группы образуются

по

набору заданных полей группировки. Опера-

ции

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

объединению множест-

ва

исходных

таблиц.

Например,

поставим

вопрос:

определить

для

каждой

груп-

пы

каждой дисциплины количество успешно сдавших

экзамен

средний

балл

по

дисциплине.

SELECT

R2.Группа.

R1.Дисциплина.

COUNTt*).

AVR(04eHKa)

FROM

R1.R2

WHERE

Rl.WO

•=

R2.ФИО

AND

Rl.Оценка

IS NOT

NULL

AND

Rl.Оценка

> 2

GROUP

R2.Группа.

К.Дисциплина

Результат:

Дисциплина

Базы

данных

Теория

информации

Сети

телекоммуникации

Английский

язык

COUNTO

АУВ(Оценка)

3.83

3.67

4.66

4.25

Мы

не

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

предложении

WHERE,

потому

что

предикаты

оцениваются

терминах

одиночной

строки,

агрегатные

функ-

ции

— в

терминах

групп строк.

Применение

агрегатных

функций

вложенных запросов

операторе

выбора

Предложение

GROUP

позволяет

определять подмножество

значений

особом

поле

терминах

другого

поля

применять

функцию

агрегата

подмножеству,

Это

дает

возможность

объединять

поля

агрегатные функции

едином

предло-

жении

SELECT.

Агрегатные

функции

могут применяться

как в

выражении вывода

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

SELECT,

так и в

выражении

условия

обработки сформирован-

ных

групп

HAVING.

этом

случае каждая

агрегатная

функция вычисляется

для

каждой

выделенной

группы.

Значения,

полученные

при

вычислении

агрегатных

функций,

могут быть использованы

для

вывода

соответствующих

результатов

или

для

условия

отбора групп.

Построим запрос, который

выводит

группы,

которых

по

одной

дисциплине

на

экзаменах

получено

больше одной двойки:

SELECT

R2.Группа

FROM

R1.R2

WHERE

Rl.OMO

R2.0HO

AND

R1.Оценка

GROUP

R2.Группа

Rl.Дисциплина

HAVING

count(*)>

дальнейшем

качестве примера будем работать

не с БД

«Сессия»,

а с БД

«Банк»,

состоящей

из

одной таблицы

F, в

которой хранится отношение

содер-

жащее

информацию

счетах

филиалах

некоторого

банка:

•=

(N,

ФИО, Филиал,

ДатаОткрытия,

ДатаЗакрытия,

Остаток);

(Филиал,

Город);

поскольку

на

этой базе можно ярче проиллюстрировать работу

агрегатными

функциями

группировкой.

Например,

предположим,

что мы

хотим

найти

суммарный

остаток

на

счетах

филиалах. Можно

сделать

раздельный

запрос

для

каждого

из

них, выбрав

ЗимсОстаток)

из

таблицы

для

каждого филиала.

GROUP

BY,

однако, позволит помес-

тить

их все в

оДну

команду:

SELECT

Филиал.

SUM(OcTaroK)

FROM

GROUP

Филиал;

GROUP

применяет

агрегатные

функции независимо

для

каждой группы, опре-

деляемой

помощью значения поля

Филиал.

Группа

состоит

из

строк

одина-

ковым

значением

поля

Филиал,

функция

SUM

применяется

отдельно

для

каждой

такой

группы,

то

есть

суммарный

остаток

па

счетах подсчитывается отдельно

Для

каждого филиала.

Значение

поля,

которому

применяется

GROUP

BY,

имеет,

по

определению,

только

одно

значение

на

группу

вывода,

Как и

результат рабо-

ты

агрегатной

функции.

Поэтому

мы

можем

совместить

одном запросе агре-

гат и

поле.

Вы

можете также использовать

GROUP

несколькими

полями.

Предположим,

что мы

хотели

бы

увидеть только

те

суммарные значения остат-

ков

на

счетах,

которые

превышают

$5000. Чтобы

увидеть

суммарные

остатки

Глава

Язык SQL.

Формирование

запросов

базе данных

свыше

$5000,

необходимо использовать предложение

HAVING.

Предложение

HAVING

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

из вы-

вода, точно

так же как

предложение

WHERE

делает

это для

индивидуальных

строк.

Правильной командой

будет

SELECT

Филиал,

SUM(QcTaTOK)

FROM

GROUP

Филиал

HAVING

$им(0статок>

5000;

Аргументы

предложении

HAVING

подчиняются

тем же

самым правилам,

что и

предложении

SELECT,

где

используется

GROUP

BY. Они

должны иметь одно

значение

на

группу вывода.

команда

будет

запрещена:

SELECT

Филиал,ЗиМ(Остаток)

FROM

GROUP

ВУ

Филиал

HAVING

ДатаОткрытия

27/12/1999:

Поле

ДатаОткрытия

не

может быть использовано

предложении

HAVING,

потому

что

оно

может

иметь

больше

чем

одно

значение

на

группу

вывода.

Чтобы

избе-

жать

такой ситуации,

предложение

HAVING

должно ссылаться только

на

агрегаты

поля, выбранные

GROUP

BY.

Имеется

правильный

способ сделать

вышеупомяну-

тый

запрос:

SELECT

Филиал,5иМ(Остаток)

-FROM

WHERE

ДатаОткрытия

'27/12/1999'

GROUP

Филиал;

Смысл

данного запроса следующий: найти сумму остатков

по

каждому филиалу

счетов,

открытых

декабря 1999

года.

Как

говорилось

ранее,

HAVING

может

использовать

только

аргументы,

которые

имеют

одно значение

на

группу вывода. Практически, ссылки

па

агрегатные

функции

—

наиболее общие,

но

поля,

выбранные

помощью

GROUP

BY,

также

допустимы. Например,

мы

хотим

увидеть

суммарные остатки

на

счетах филиа-

лов

Санкт-Петербурге,

Пскове

Урюпинске:

SELECT

Филиал,

SUH(OcraTOK)

FROM

F.Q

WHERE

F.Филиал

Q.Филиал

GROUP

Филиал

HAVING Филиал

("Санкт-Петербург".

"Псков",

"Урюпинск");

Поэтому

арифметических

выражениях

предикатов, входящих

условие

вы-

борки раздела

HAVING,

прямо можно

использовать

только

спецификации

столб-

Вложенные запросы

цов,

указанных

качестве

столбцов

группирования

разделе

GROUP

BY.

Осталь-

ные

столбцы

можно

специфицировать только внутри спецификаций агрегатных

функций

COUNT,

SUM,

AVG,

MIN

MAX,

вычисляющих

данном

случае некоторое агре-

гатное значение

для

всей группы строк. Аналогично обстоит

дело

подзапроса-

ми»

входящими

предикаты

условия

выборки

раздела

HAVING:

если

подзапросе

используется характеристика текущей группы,

то она

может задаваться только

путем ссылки

на

столбцы группирования.

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

HAVING

является

сгруппированная

таблица,

со-

держащая

только

те

группы строк,

для

которых

результат вычисления условия

поиска есть

TRUE,

частности, если раздел

HAVING

присутствует

табличном

вы-

ражении,

не

содержащем

GROUP

BY, то

результатом

его

выполнения

будет

либо

пустая

таблица, либо результат

выполнения

предыдущих разделов табличного

выражения,

рассматриваемый

как

одна группа

без

столбцов

группирования.

Вложенные запросы

Теперь вернемся

к БД

«Сессиям

рассмотрим

на ее

примере использование

вложенных запросов,

помощью

SQL

можно вкладывать запросы внутрь

друг

друга. Обычно внут-

ренний

запрос генерирует

значение,

которое проверяется

предикате внешнего

запроса

(в

предложении

WHERE

или

HAVING),'

определяющего,

верно

оно или

нет.

Совместно

подзапросом можно

использовать

предикат

EXISTS,

который возвра-

щает

истицу,

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

не

п^ст.

сочетании

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

как

группи-

ровка,

подзапрос представляет собой мощное средство

для

достижения

нужно-

го

результата.

части

FROM

оператора

SELECT

допустимо применять синонимы

именам

таблицы,

если

при

формировании запроса

нам

требуется

более

чем

один

экземпляр некоторого

отношения.

Синонимы

задаются

использованием

ключевого

слова

AS,

которое может быть вообще

опущено.

Поэтому часть

FROM

может выглядеть следующим образом:

FROM

AS В

или

FROM

Rl A, Rl В;

оба

выражения

эквивалентны

рассматриваются

как

применения

оператора

SELECT

двум экземплярам таблицы

R1.

Например, покажем,

как

выглядят

на SQL

некоторые

запросы

к БД

«Сессия»:

Список

iex,

кто

сдал

все

положенные

экзамены.

SELECT

ФИО

FROM

Rl as a

WHERE

Оценка

> 2

GROUP

BY ФИО

HAVING

COUNTC*)

(SELECT

COUNT**}

Глава

Язык

SQL. Формирование запросов

базе данных

FROM

R2.R3

WHERE

R2.Fpynna=R3.Группа

АШФИО=а.ФИО)

Здесь

во

встроенном

запросе

определяется

общее

число

экзаменов,

которые

должен

сдавать каждый

студент,

обучающийся

группе,

которой

учится

данный

студент,

и это

число

сравнивается

числом

экзаменов,

которые сдал

данный

студент,

Список тех,

кто

должен

был

сдавать

экзамен

по

БД,

но

пока

еще не

сдавал.

SELECTOR

FROM

R2 a, R3

WHERE

R2.rpynna=R3.Группа

AND

Дисциплина

"БД"

AND NOT

EXISTS (SELECT

ФИО

FROM

WHERE

ФИО=а.ФИО

AND

Дисциплина

"БД")

Предикат

EXISTS

(

SubQuery)

истинен,

когда

подзапрос

SubQuery

не

пуст,

то

есть содержит хотя

бы

один кортеж,

противном

случае предикат

EXISTS

ложен.

Предикат

NOT

EXISTS

обратно

—

истинен

только

тогда,

когда

подзапрос

SubQuery

пуст.

Обратите

внимание, каким образом

NOT

EXISTS

вложенным

запросом

позволяет

обойтись

без

операции разности отношений. Например, формулировка запроса

со

словом

«все»

может

быть

выполнена

как бы с

двойным

отрицанием.

Рассмот-

рим

пример

базы,

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

поставщиками,

она

представлена

одним

отношением

«Поставщики—детали»

со

схемой

(Нонер_поставщика.

номер_детали)

(номер^детали,

наименование)

Вот

каким образом формулируется ответ

на

запрос;

«Найти

поставщиков, кото-

рые

поставляют

все

детали».

SELECT

DISTINCT

НОМЕР_ПОСТАВЩИКА

FROM

SP SP1

WHERE

NOT

EXISTS

(SELECT

номер_детали

FROM

WHERE

NOT

EXISTS

(SELECT

FROM

SP SP2

WHERE

5Р2.нонер_пютавщика

ЗР1.нонер_поставщика

AND

зр2.номер_детали

Р.номер_детали));

Фактически

мы

переформулировали этот запрос так:

«Найти

поставщиков

таких,

что

не

существует

детали,

которую

бы

они

не

поставляли».

Следует

отмерить,

что

этот запрос может быть реализован

через агрегатные функции

подзапросом;

Внешние

объединения

SELECT

DISTINCT

Нонер_постаещика

FROM

GROUP

ВУ'Нонер_поставщика

HAVING

CounUDISTINCT

номер_детали)

(SELECT

CountC

нонер^детали)

FROM

стандарте

SQL92

операторы сравнения расширены

до

многократных сравне-

ний с

использованием

ключевых

слов

ANY

ALL.

Это

расширение используется

при

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

со

столбцом

данных,

возвра-

щаемым

вложенным запросом.

Ключевое

слово

ANY,

поставленное

любом предикате

сравнения,

означает,

что

предикат

будет

истинен,

если

хотя

бы для

одного значения

из

подзапроса пре-

дикат

сравнения

истинен.

Ключевое слово

ALL

требует, чтобы предикат сравне-

ния

был бы

истинен

при

сравнении

со

всеми строками подзапроса.

Например,

найдем

студентов,

которые сдали

все

экзамены

на

оценку

не

ниже

чем

«хорошо». Работаем

с той же

базой

«Сессия»,

но

добавим

ней

еще

одно

от-

ношение

R4,

которое

характеризует

сдачу лабораторных работ

течение семестра:

(ФИО,

Дисциплина,

Оценка);

(ФИО,

Группа);

Кз

(Группы,

Дисциплина

)

(ФИО,

Дисциплина,

Номер_лаб_раб,

Оценка);

Select

Rl-Фио

From

Where

4 >

All

(Select

Rl.Оценка

From

Rl as

Rll

Where

Rl-Фио

Ш.Фио)

Рассмотрим

еще

один

пример:

Выбрать

студентов,

которых оценка

по

экзамену

не

меньше,

чем

хотя

бы

одна

оценка

по

сданным

им

лабораторным работам

по

данной

дисциплины:

Select

В1.Фио

From

Where

Rl.Оценка

ANY

(Select

R4.Оценка

From

Where

Rl.Дисциплина

•=

R4.

Дисциплина

AND

Rl-Фио

R4.*iio)

Внешние объединения

Стандарт

SQL2

расширил

понятие условного

объединения.

стандарте

SQL1

при

объединении

отношений

использовались только

условия,

задаваемые

час-

Глава

Язык SQL.

Формирование

запросов

базе данных

ти

WHERE

оператора

SELECT,

и в

этом случае

результирующее

отношение

попада-

ли

только сцепленные

по

заданным

условиям

кортежи исходных

отношений,

для

которых

эти

условия были определены

истинны.

Однако

действитель-

ности

часто необходимо объединять таблицы таким образом, чтобы

результат

попали

все

строки

из

первой

таблицы,

вместо

тех

строк второй таблицы,

для

которых

не

выполнено условие

соединения,

результат попадали

бы

неопреде-

ленные

значения.

Или

наоборот,

включаются

все

строки

из

правой

(второй)

таблицы,

отсутствующие части строк

из

первой таблицы дополняются неопре-

деленными

значениями.

Такие

объединения были названы внешними

проти-

воположность

объединениям,

определенным стандартом

SQ.L1,

которые

Стали

называться внутренними.

общем случае синтаксис части

FROM

стандарте

SQ.L2

выглядит следующим

образом:

FROM

<список исходных

таблиц>

выражение

естественного объединения

выражение объединения

> |

выражение

перекрестного объединения

выражение

запроса

на

объединение

<список

исходных

таблицм;»

<имя_таблицы_1>

[

имя

синонима

таблицы_1]

[

...]

[,<имя_таблицы_п>[

<иня

синонима

таблицы_п>

] ]

выражение

естественного

объединениям:

<иня_таблицы

NATURAL

{

INNER

FULL [OUTER]

LEFT

[OUTER]

RIGHT

[OUTER]}

JOIN

<имя_таблицы_2>

выражение

перекрестного

объединениям:

<имя_таблицы_1>

CROSS

JOIN

<имя_таблицы_2>

выражение

запроса

на

объединением

<имя_таблицы_1>

UNION

JOIN

<иня_таблицы_2>

выражение

объединениями

<иия таблицы

1> {

INNER

FULL

[OUTER]

LEFT [OUTER]

RIGHT

TAUTER]}

JOIN

(ON

условие

[

[USING (список

столбцов)]}

<имя_таблицы_2>

этих

определениях

INNER

—

означает

внутреннее

объединение,

LEFT

—

левое

объединение,

то

есть

результат

входят

все

строки таблицы

части резуль-

тирующих

кортежей,

для

которых

не

было соответствующих значений

таб-

лице

дополняются

значениями

NULL

(неопределено). Ключевое слово

RIGHT

означает

правое внешнее

объединение,

и в

отличие

от

левого объединения

этом

случае

результирующее

отношение

включаются

все

строки таблицы

а не-

достающие части

из

таблицы

дополняются

неопределенными

значениями, Клю-

чевое слово

FULL

определяет полное внешнее объединение:

левое

правое.

При

полном

внешнем объединении выполняются

правое

левое

внешние

объеди-

нения

и в

результирующее отношение включаются

все

строки

из

таблицы

1»

до-

полненные неопределенными значениями,

и все

строки

из

таблицы

также

до-

полненные

неопределенными значениями.