Базы данных. Лекции БД. Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5

• Название: Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5
• Анкор: Базы данных
• Дата: 05.01.2022
• Размер: 1.07 Mb.
• Имя файла: Лекции БД.doc
• Тип: Лекция; #324711
• страница: 14 из 24

Лекция 11.

8.6.10.Объединение, пересечение и разность запросов

Иногда отношения комбинируются с помощью теоретико-множественных операций объединения, пересечения и разности. В SQL соответствующие операторы применяются к результатам запросов при условии, что запросы порождают отношения с одним и тем же множеством атрибутов. Применяются ключевые слова UNION (объединение), INTERSECT (пересечение) и EXCEPT (разность). Эти слова ставятся между запросами, заключенными в скобки.

Допустим, нужно найти имена и адреса всех женщин-кинозвезд, которые являются администраторами и имеют чистый доход не менее 10 млн.дол. этот запрос можно сформулировать следующим образом:

(SELECT имя, адрес

FROM звезды

WHERE пол=’ж’)

INTERSECT

(SELECT имя, адрес

FROM продюсер

WHERE доход>10000000)

Можно найти также разность между множествами людей, выбранными из двух отношений. Запрос
(SELECT имя, адрес FROM звезды)

EXCEPT

(SELECT имя, адрес FROM продюсер)
задает имена и адреса кинозвезд, не являющихся администраторами, независимо от дохода.

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

Предположим, что нужно найти все названия и годы выпуска фильмов, входящих в отношения «фильмы» и «звезды_в_фильмах». В идеальном случае эти множества совпадают, но на практике они обычно различаются. Например, могут быть фильмы без кинозвезд или кортеж «звезды_в_фильмах», в котором упоминается фильм, отсутствующий в отношении «фильмы». Итак, можно сформулировать следующий запрос:

(SELECT название, год FROM фильмы)

UNION

(SELECT Название_фильма AS название, год_выпуска AS год FROM звезды_в_фильмах);
результатом будут все фильмы, упомянутые в любом из этих отношений с атрибутами результирующего отношения название и год.
Задания. Напишите следующие запросы

Укажите производителя и скорость ПК-блокнотов с жестким диском объемом не менее 5 Гбайт.

Найдите номера моделей и цены всех продуктов (любого типа), выпущенных производителем В.

Найдите производителя, продающего ПК-блокноты, но не ПК.

! Найдите размеры жестких дисков, совпадающие у двух или более ПК.

! Найдите пары моделей ПК, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (а,б), но не (б,а).

!! Найдите производителей по меньшей мере двух различных компьютеров (ПК или ПК-блокнотов) имеющих скорость не менее 750 Мгц.
Еще один пример: получить информацию обо всех парах поставщиков и деталей, совмещенных в одном городе.

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

1. 
   SELECT S.S#, S.NAME, S.STATUS, S.CITY,
   P.P#, P.NAME, P.COLOR, P.WEIGHT
   FROM S, P
   WHERE S.CITY = P.CITY;
   
2. 
   S JOIN P USING CITY;
   
3. 
   S NATURAL JOIN P
   

Результатом в каждом случае будет естественное соединение таблиц S и P (по городам).

Рассмотрим более подробно первую формулировку. Она единственная из трех допустима в SQL, который был первоначально определен (явная операция Join была добавлена в SQL/92). Концептуально можно рассматривать реализацию этой версии запроса следующим образом:

• 
  Во-первых, после выполнения инструкции FROM мы получаем декартово произведение S TIMES P.
  
• 
  Далее, после выполнения WHERE мы получаем выборку из этого произведения, в которой два значения CITY в каждой строке равны (иначе говоря, построено соединение поставщиков и деталей по эквивалентности городов).
  
• 
  И, наконец, после выполнения оператора SELECT мы получаем проекцию выборки по столбцам, указанным в инструкции SELECT. Конечным результатом будет естественное соединение.
  

Следовательно, нестрого говоря, инструкция FROM в SQL соответствует декартову произведению, инструкция WHERE – выборке, а совместная инструкция SELECT-FROM-WHERE представляет проекцию выборки произведения.

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

SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY

FROM S JOIN SP USING S# JOIN P USING P#;
Обратите внимание, что приведенная ниже инструкция уже будет некорректной, поскольку она включает столбец CITY как присоединяемый столбец во втором соединении:
SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY

FROM S NATURAL JOIN SP NATURAL JOIN P;

8.6.11.Подведение итогов.

Получить общее число поставщиков
SELECT COUNT(*) AS N

FROM S
Результатом будет таблица с одним столбцом, названным N, и одной строкой, содержащей значение общее число поставщиков. Язык SQL поддерживает обычный набор итоговых (агрегатных) функций (COUNT, SUM, AVG, MAX и MIN), но есть несколько специфических для SQL моментов, которые необходимо знать пользователю, а именно:

• 
  Вообще аргументу функции не обязательно должно предшествовать ключевое поле DISTINCT, указывающее, что перед применением функции дублирующиеся строки должны удаляться. А для функций MAX и MIN ключевое слово DISTINCT излишне и не вызывает никакого действия.
  

• 
  Специальная функция COUNT(*), не допускающая использования слова DISTINCT, предназначена для подсчета всех строк в таблице без единого удаления дублирующих строк.
  
• 
  Любые null-значения в столбце аргументе всегда удаляются перед применением функции в зависимости от того, указано ли слово DISTINCT, кроме случая с функцией COUNT(*), когда null-значения обрабатываются точно так же, как обычные значения.
  
• 
  Если аргумент будет пустым множеством, функция COUNT возвратит значение нуль; все другие функции возвращают null-значение (неопределенное значение).
  

Пример: получить максимально и минимальное количество для детали Р2.
SELECT MAX(SP.QTY) AS MAXQ, MIN(SP.QTY) AS MINQ

FROM SP

WHERE SP.P# = ‘P2’
Здесь обе инструкции FROM и WHERE фактически представляют часть аргументов для двух итоговых функций. Следовательно, они должны были по логике вещей записываться в скобках, заключающих аргументы. Тем не менее, запрос записывается именно так. Одно из отрицательных последствий такого подхода состоит в том, что итоговые функции не могут быть вложенными, в результате чего такой запрос, как «Получить среднее итоговое количество деталей», нельзя сформулировать без громоздких выражений (поскольку выражение AVG(SUM(QTY)) недопустимо).

8.6.12.Группировка

Пример: для каждой поставляемой детали получить номер детали и общее количество поставки.
SELECT SP.P#, SUM(SP.QTY) AS TOTQTY

FROM SP

GROUP BY SP.P#
Это выражение SQL является аналогом такого выражения реляционной алгебры:
SUMMARIZE SP BY (p#) ADD SUM(QTY) AS TOTQTY
В частности, стоит отметить, что если указана инструкция GROUP BY, то выражения в инструкции SELECT должны быть однозначными в группе.

Вот альтернативная формулировка того же запроса:

SELECT P.P#, (SELECT SUM(SP.QTY)

FROM SP

WHERE SP.P# = P.P#) AS TOTQTY

FROM P;

Возможность использования вложенных выражений выборки для представления скалярных элементов была добавлена в SQL/92 и является основным усовершенствованием. В рассматриваемом примере это дает возможность генерировать результат, который включает строки для деталей, не поставляемых совсем, а предыдущая формулировка этого не позволяла. (Однако значение TOTQTY для таких деталей будет, к сожалению представлено как null-значение, а не нуль).

8.6.12.1.Предложение HAVING

Допустим, нужно вывести на печать таблицу, перечисляющую сумму продолжительности фильмов, выпущенных каждым продюсером. Нужна информация из двух отношений: «фильмы» и «продюсер». Поэтому мы начинаем с тэта-соединения, сравнивая входящие в них номера сертификатов. В результате получается отношение, в котором каждый кортеж из «продюсер» спарен с кортежами из «фильмы», содержащими все фильмы конкретного продюсера. И, наконец, подсчитывается сумма продолжительности фильмов в каждой группе.

1. 
   SELECT имя, SUM(продолжительность)
   
2. 
   FROM продюсер, фильмы
   
3. 
   WHERE сертП# =серт#
   
4. 
   GROUP BY имя;
   

Теперь предположим, что мы не хотим включать в таблицу всех продюсеров фильмов. Тогда перед группированием можно ограничить кортежи так, чтобы нежелательные группы оказались пустыми. Например, если нужна общая продолжительность фильмов, выпущенных только продюсерами, доход которых превышает 10 млн.дол., строку (3) можно заменить следующей строкой
WHERE сертП#=серт# AND доход > 10000000
Однако иногда нужно выбрать группы, основанные на некотором общем свойстве самой данной группы. Тогда за предложением GROUP BY указывается предложение HAVING, состоящее из ключевого слова HAVING, за ним следует условие, касающееся группы.

Пример. Нужно определить общую продолжительность фильмов только для продюсеров, выпустивших хотя бы один свой фильм до 1930г. в результате получается запрос, удаляющий из отношения такие группы кортежей, в которых каждый кортеж имеет в компоненте «год» значение 1930 и более.
SELECT имя, SUM(продолжительность)

FROM продюсер, фильмы

WHERE сертП# =серт#

GROUP BY имя

HAVING MIN (год) < 1930;
Задания. Запишите перечисленные ниже запросы.

1. 
   Найдите среднюю скорость ПК.
   
2. 
   Найдите среднюю скорость ПК-блокнота, цена которых превышает 2000 дол.
   
3. 
   Найдите среднюю цену ПК, выпущенных производителем А.
   
4. 
   ! Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем В.
   
5. 
   Для каждого значения скорости найдите среднюю стоимость ПК с такой же скоростью процессора.
   
6. 
   ! Для каждого производителя найдите средний размер экрана выпускаемых им ПК-блокнотов.
   
7. 
   ! найдите производителей, выпускающих по меньшей мере три различные модели ПС.
   
8. 
   ! найдите максимальную цену ПК, выпускаемых каждым производителем.
   
9. 
   ! для каждого значения скорости ПК, превышающего 300 Мгц, определите среднюю цену компьютера с такой же скоростью.
   
10. 
    !! найдите средний размер диска ПК тех производителей, которые выпускают и принтеры.
    

8.6.13.Подзапросы

Здесь мы рассмотрим более широкую область выражений, которые могут появляться в предложении WHERE. Ранее в условии сравнивались скалярные значения. Теперь мы будем сравнивать целые кортежи и даже отношения. Сначала рассмотрим, как в условиях применяются подзапросы. Подзапрос – это выражение, приписывающее значение отношению. Например, подзапросом может быть выражение типа select-from-where.

8.6.13.1.Подзапросы, порождающие скалярные значения

Выражения типа могут select-from-where порождать отношения с любым числом атрибутов, и в этих отношениях может быть любое число кортежей. Однако часто нужно знать значения единственного атрибута.

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

Вспомним пример о продюсере фильма «Звездные войны». Для данного запроса потребовалось два отношения:
SELECT имя

FROM фильмы, продюсер

WHERE title=’Звездные войны’ AND Псерт# = серт#
На этот запрос можно посмотреть иначе. Для получения номера сертификата продюсера фильма «Звездные войны» необходимо только отношение «фильмы». Зная этот номер, можно адресовать отношению «продюсер» запрос о человеке с этим сертификатом. Первую проблему можно решить с помощью подзапроса, а его результат, являющийся, по нашему предположению единственным значением, использовать в «главном» запросе для получения того же эффекта, что и в случае с предыдущим запросом.
SELECT имя

FROM продюсер

WHERE серт#=

(SELECT сертП#

FROM фильмы

WHERE название=’Звездные войны’

);
Подзапрос выражен строками (4) – (6). Уже один он показывает, что результатом будет унарное отношение с атрибутом сертП# и единственным кортежем (например, 12345). Если этот подзапрос не порождает ни одного кортежа или порождает более одного кортежа, это ошибка этапа выполнения запроса.

Выполнив подзапрос, можно выполнять строки (1) – (3) так, словно 12345 заменяет целый подзапрос, т.е. «главный» запрос выполняется так, как запрос вида
SELECT имя

FROM продюсер

WHERE серт#=12345;

8.6.13.2.Условия, содержащие отношения

Есть ряд операторов SQL, которые можно применять к отношению Р и получать булевы значения. Обычно Р – результат запроса select-from-where. Некоторые из этих операторов, например IN, ALL и ANY, содержат скалярное значение s, и в этом случае Р должно быть отношением с одним столбцом.

EXISRS Р – это условие, которое истинно, если и только если Р не пусто.

Условие s IN Р истинно, если и только если s эквивалентно одному из значений Р. выражение s NOT IN Р истинно, если и только если s не эквивалентно ни одному из значений Р. при этом предполагается, что Р – унарное отношение. Расширение операторов IN и NOT IN, при котором схема Р имеет более одного атрибута, а s – кортеж, будет рассмотрено в следующем параграфе.

Условие s > ALL Р истинно, если и только если s больше любого значения унарного отношения Р. здесь оператор > можно заменить любым оператором сравнения с аналогичным значением. Например, выражение s <> ALL Р эквивалентно выражению s NOT IN Р.

Условие s > ANY Р истинно, если и только если S больше по крайней мере одного из значений унарного отношения Р. здесь оператор > можно заменить любым из пяти других операторов сравнения. Например, s = ANY P эквивалентно s IN P.
Можно строить отрицания операторов EXISTS, ALL и ANY точно также, как любых булевозначных выражений – ставить перед выражением слово NOT. Значит NOT EXISTS Р истинно, если только Р пусто. NOT s > ALL Р истинно, если и только если s не является максимальным значением Р. NOT s > ANY Р истинно, если и только если s - минимальное значение Р.