лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 20 из 47

model
type
1121
PC
1750
Laptop
Решение на основе UNION соответствует несколько иной задаче, которую можно было бы сформулировать следующим образом.
Выяснить, какие модели производителя 'B' имеются в
наличии. Вывести номер модели и тип.

Пересечение
и разность
В стандарте языка SQL имеются предложения оператора SELECT для выполнения операций пересечения и разности результатов запросов- операндов. Этими предложениями являются INTERSECT [ALL] (пересечение) и EXCEPT [ALL] (разность), которые работают аналогично предложению UNION. В результирующий набор попадают только те строки, которые присутствуют в обоих запросах (INTERSECT) или только те строки первого запроса, которые отсутствуют во втором (EXCEPT). При этом оба запроса, участвующих в операции, должны иметь одинаковое число столбцов, и соответствующие столбцы должны иметь одинаковые (или неявно приводимые) типы данных. Имена столбцов результирующего набора формируются из заголовков первого запроса.
Если не используется ключевое слово ALL (по умолчанию подразумевается DISTINCT), то при выполнении операции автоматически устраняются дубликаты строк. Если указано ALL, то количество дублированных строк подчиняется следующим правилам (n1 - число дубликатов строк первого запроса, n2 - число дубликатов строк второго запроса):

INTERSECT ALL: min(n1, n2)

EXCEPT ALL: n1 - n2, если n1>n2.
Пример 5.7.3.
Найти корабли, которые присутствуют как в таблице Ships,
так и в таблице Outcomes.
1.
SELECT
name
FROM
Ships
2.
INTERSECT
3.
SELECT
ship
FROM
Outcomes;

В реляционной алгебре операция пересечения является коммутативной, поскольку она применима к отношениям с одинаковыми заголовками. Мы и в
SQL
можем поменять запросы местами. Вышеприведенное решение даст тот же результат, что и следующее:
1.
SELECT
ship
FROM
Outcomes
2.
INTERSECT
3.
SELECT
name
FROM
Ships; за исключением заголовка. В первом случае единственный столбец будет иметь заголовок name, а во втором - ship. Поэтому запрос
1.
SELECT
name
FROM
(
2.
SELECT
ship
FROM
Outcomes
3.
INTERSECT
4.
SELECT
name
FROM
Ships
5.
)
x;
приведет к ошибке:
Invalid column name 'name'.
(неверное имя столбца 'name').
Пример 5.7.4
Найти корабли из таблицы Outcomes, которые отсутствуют в
таблице Ships.
Задача легко решается при помощи оператора EXCEPT:
1.
SELECT
ship
FROM
Outcomes
2.
EXCEPT
3.
SELECT
name
FROM
Ships;
Операция разности не является коммутативной, поэтому если переставить местами запросы, то мы получим решение совсем другой задачи:

"Найти корабли из таблицы Ships, которые отсутствуют в таблице
Outcomes".
Эта задача на языке предметной области звучит так: "Найти корабли, которые не принимали участие в сражениях".
Заметим, что если какой-либо корабль принимал участие в сражениях несколько раз, то по причине исключения дубликатов он будет присутствовать только один раз в результирующем наборе. У нас есть такой корабль -
California, но он присутствует также и в таблице Ships, а потому не выводится рассмотренным выше запросом. Поэтому, чтобы продемонстрировать сказанное, исключим его из результатов второго запроса в операции разности:
1.
SELECT
ship
FROM
Outcomes
2.
EXCEPT
3.
SELECT
name
FROM
Ships
WHERE
name <>
'California'
;
ship
Bismarck
California
Duke of York
Fuso
Hood
King George V
Prince of Wales
Rodney
Schamhorst
West Virginia
Yamashiro

Для имеющегося набора данных тот же результат мы получим при выполнении следующего запроса:
1.
SELECT
ship
FROM
Outcomes
2.
EXCEPT
ALL
3.
SELECT
name
FROM
Ships;
(2 дубликата для 'California' в таблице Outcomes минус 1 - в Ships)
Соответственно, запрос
1.
SELECT
ship
FROM
Outcomes
2.
EXCEPT
ALL
3.
SELECT
name
FROM
Ships
WHERE
name<>
'California'
; даст нам два вхождения корабля 'California' в результирующем наборе (2 - 0
= 2):
ship
Bismarck
California
California
Duke of York
Fuso
Hood
King George V
Prince of Wales
Rodney
Schamhorst
West Virginia
Yamashiro

Следует сказать, что не все СУБД поддерживают эти предложения в операторе SELECT. Нет поддержки INTERSECT/EXCEPT, например, в MySQL, а в MS SQL Server она появилась, лишь начиная с версии 2005, и то без ключевого слова ALL. ALL с INTERSECT/EXCEPT также еще не реализована в Oracle. Следует отметить, что вместо стандартного EXCEPT в
Oracle используется ключевое слово MINUS.
Поэтому для выполнения операций пересечения и разности могут быть использованы другие средства. Здесь уместно заметить, что один и тот же результат можно получить с помощью различных формулировок оператора
SELECT. В случае пересечения и разности можно воспользоваться предикатом существования
EXISTS
В заключение рассмотрим пример на использование операции INTERSECT
ALL.
Пример 5.7.5
Найти производителей, которые выпускают не менее двух
моделей ПК и не менее двух моделей принтеров.
1.
SELECT
maker
FROM
(
2.
SELECT
maker
FROM
Product
WHERE
type=
'PC'
3.
INTERSECT
ALL
4.
SELECT
maker
FROM
Product
WHERE
type =
'Printer'
5.
)
X
GROUP
BY
maker
HAVING
COUNT
(
*
)
>
1
;
INTERSECT ALL в подзапросе этого решения оставит минимальное число дубликатов, т.е. если производитель выпускает 2 модели ПК и одну модель принтера (или наоборот), то он будет присутствовать в результирующем наборе один раз. Далее мы выполняем группировку по производителю, оставляя только тех из них, кто присутствует в результатах подзапроса более одного раза.
Конечно, мы можем решить эту задачу, не используя явно операцию пересечения. Например, одним подзапросом найдем производителей, которые выпускают не менее 2-х моделей ПК, другим - тех, кто выпускает не менее 2- х моделей принтеров. Решение задачи даст внутреннее соединение этих подзапросов. Ниже этот алгоритм реализован на основе еще одного стандартного типа соединений - естественного соединения:

1.
SELECT
PC.maker
FROM
(
2.
SELECT
maker
FROM
Product
3.
WHERE
type=
'PC'
GROUP
BY
maker
4.
HAVING
COUNT
(
*
)
>
1
)
PC
5.
NATURAL
JOIN
6.
(
7.
SELECT
maker
FROM
Product
8.
WHERE
type=
'Printer'
GROUP
BY
maker
9.
HAVING
COUNT
(
*
)
>
1
)
Pr;
Естественное соединение (NATURAL JOIN) – это эквисоединение по столбцам с одинаковыми именами. SQL Server не поддерживает этот тип соединения, поэтому последний запрос можно выполнить, например, с помощью PostgreSQL.
Тот факт, что операция EXCEPT убирает из результата строки-дубликаты, даёт нам еще один способ исключения дубликатов. Продемонстрируем имеющиеся варианты на примере следующей задачи (используется база данных "
Окраска ").
Перечислить цвета имеющихся баллончиков с краской.
1. Использование DISTINCT
1.
SELECT
DISTINCT
v_color
FROM
utV;
2. Использование GROUP BY
1.
SELECT
v_color
FROM
utV
GROUP
BY
v_color;
3. Использование EXCEPT
Идея решения состоит в том, чтобы "вычесть" из имеющегося набора несуществующий цвет, например, 'Z':
1.
SELECT
v_color
FROM
utV
2.
EXCEPT
3.
SELECT
'Z'
;
Поскольку столбец v_color не допускает NULL-значений, последний запрос можно переписать в универсальной форме:
1.
SELECT
v_color
FROM
utV

2.
EXCEPT
3.
SELECT
NULL
;
SQL Server оценивает стоимость всех этих запросов равной. В этом нет ничего удивительного в силу того, что каждый из запросов выполняет чтение таблицы и сортировку, а это наиболее "тяжелые" операции плана запроса.
В связи с упражнением 6 (SELECT)
рейтингового этапа возник вопрос относительно старшинства операций UNION, EXCEPT и INTERSECT.
Логический порядок выполнения этих операций, который приводится в книге
Мартина Грабера [4]
"Справочное руководство по SQL", выглядит так:

UNION, EXCEPT

INTERSECT
В предположении, что логический порядок выполнения операций соответствует их старшинству, получается, что старшинство операций UNION и EXCEPT идентично и, следовательно, они должны выполняться в том порядке, в котором записаны, если этот порядок не изменяется скобками. При этом обе операции выполняются раньше, чем INTERSECT, т.е. они старше.
Рассмотрим три простых запроса, которые будем комбинировать различными способами, чтобы убедиться в этом:
1.
--Модели и типы продукции производителя B
2.
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
;
model
type
1121
PC
1750
Laptop
1.
--Модели ноутбуков
2.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
;

model
type
1298
Laptop
1321
Laptop
1750
Laptop
1752
Laptop
1.
--Модели ПК
2.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
;
model
type
1121
PC
1232
PC
1233
PC
1260
PC
2111
PC
2112
PC
Давайте сначала проверим первое утверждение. Если операция EXCEPT старше операции UNION, то запросы
1.
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
2.
UNION
3.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
4.
EXCEPT
5.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
;
И
1.
(
SELECT
model, type
FROM
Product
WHERE
maker=
'B'

2.
UNION
3.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
)
4.
EXCEPT
5.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
; должны нам дать разные результаты. Однако это не так, и мы получаем один и тот же результирующий набор:
model
type
1298
Laptop
1321
Laptop
1750
Laptop
1752
Laptop
Аналогично, если операция UNION старше операции EXCEPT, то запросы
1.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
2.
EXCEPT
3.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
4.
UNION
5.
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
;
И
1.
(
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
2.
EXCEPT
3.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
)
4.
UNION
5.
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
; должны нам дать разные результаты. И тут мы получаем одинаковый результат:
model
type
1121
PC

1298
Laptop
1321
Laptop
1750
Laptop
1752
Laptop
Итак, операции UNION и EXCEPT эквивалентны по старшинству.
Проверим теперь старшинство операции INTERSECT по отношению к другим операторам (в тестах можно взять любую из них, т.к. они имеют один и тот же порядок).
Если INTERSECT "младше" или эквивалентен UNION, то запросы
1.
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
2.
UNION
3.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
4.
INTERSECT
5.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
; и
1.
(
SELECT
model, type
FROM
Product
WHERE
maker=
'B'
2.
UNION
3.
SELECT
model, type
FROM
Product
WHERE
type=
'Laptop'
)
4.
INTERSECT
5.
SELECT
model, type
FROM
Product
WHERE
type=
'PC'
; должны дать одинаковые результаты. Однако мы получаем разные результирующие наборы. Первый запрос дает

model
type
1121
PC
1750
Laptop в то время как второй
model
type
1121
PC
Вывод. Логический порядок, приведенный в начале статьи не соответствует старшинству операций, и, на мой взгляд, его следует поменять на обратный:

INTERSECT

UNION, EXCEPT
Предикат EXISTS
Синтаксис:
1.
EXISTS
::=
2.
[
NOT
]
EXISTS
(
<табличный подзапрос>
)
Предикат EXISTS принимает значение TRUE, если подзапрос содержит любое количество строк, иначе его значение равно FALSE. Для NOT
EXISTS все наоборот.
Этот предикат никогда не принимает значение UNKNOWN.

Обычно предикат EXISTS используется в зависимых (коррелирующих) подзапросах. Этот вид подзапроса имеет внешнюю ссылку, связанную со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оцениваться отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для разных строк основного запроса.
Пример на пересечение.
Найти тех производителей портативных компьютеров, которые
также производят принтеры:
1.
SELECT
DISTINCT
maker
2.
FROM
Product
AS
lap_product
3.
WHERE
type =
'laptop'
AND
4.
EXISTS
(
SELECT
maker
5.
FROM
Product
6.
WHERE
type =
'printer'
AND
7. maker = lap_product.maker
8.
)
;
В подзапросе выбираются производители принтеров и сравниваются с производителем, значение которого передается из основного запроса. В основном же запросе отбираются производители портативных компьютеров.
Таким образом, для каждого производителя портативных компьютеров проверяется, возвращает ли подзапрос строки (которые говорят о том, что этот производитель также выпускает принтеры). Поскольку два условия в предложении WHERE должны выполняться одновременно (AND), то в результирующий набор попадут нужные нам строки. DISTINCT используется для того, чтобы каждый производитель присутствовал в выходных данных только один раз. В результате получим
maker
A
Пример на разность.
Найти производителей портативных компьютеров, которые не
производят принтеров:
1.
SELECT
DISTINCT
maker
2.
FROM
Product
AS
lap_product

3.
WHERE
type =
'laptop'
AND
4.
NOT
EXISTS
(
SELECT
maker
5.
FROM
Product
6.
WHERE
type =
'printer'
AND
7. maker = lap_product.maker
8.
)
;
В этом случае достаточно заменить в предыдущем примере EXISTS на NOT
EXISTS. То есть выходные данные составят только те уникальные строки основного запроса, для которых подзапрос не возвращает ни одной строки. В итоге получим:
maker
B
C
Реляционное
деление
Рассмотрим следующую задачу.
Определить производителей, которые
выпускают модели всех типов (схема
"Компьютерная фирма").
Ключевым словом здесь является "всех", т.е. производитель в таблице Product должен иметь модели каждого типа, т.е. и PC, и Laptop, и Printer.
Как раз для решения подобных задач в реляционную алгебру Коддом была введена специальная операция реляционного деления (DIVIDE BY).
С помощью этой операции наша задача решается очень просто:
1. Product
[
maker, type
]
DIVIDE
BY
Product
[
type
]

Здесь квадратными скобками обозначается операция взятия проекции на соответствующие атрибуты.
Операция реляционного деления избыточна, т.е. она может быть выражена через другие операции реляционной алгебры. Возможно, поэтому ее нет в языке SQL.
На примере решения сформулированной задачи я хочу показать несколько приемов реализации операции реляционного деления на языке SQL.
Группировка
Если использовать тот факт, что, согласно описанию предметной области, типов продукции всего три, то мы можем выполнить группировку по производителю и подсчитать количество уникальных типов. Затем мы отберем только тех производителей, у которых это число равно трем.
Итак,
1.
SELECT
maker
2.
FROM
Product
3.
GROUP
BY
maker
4.
HAVING
COUNT
(
DISTINCT
type
)
=
3
;
Однако если число типов продукции произвольно, то такое решение будет правильным только при нынешнем состоянии базы данных, а не при любом возможном. А это значит, что мы должны константу заменить "переменной", значением которой будет текущее число типов, т.е. подзапросом:
1.
SELECT
maker
2.
FROM
Product
3.
GROUP
BY
maker
4.
HAVING
COUNT
(
DISTINCT
type
)
=
5.
(
SELECT
COUNT
(
DISTINCT
type
)
FROM
Product
)
;
Разность
Если взять операцию разности ВСЕХ имеющихся типов моделей и типов у конкретного производителя, то результирующая выборка не должна содержать строк.
1.
SELECT
DISTINCT
maker
2.
FROM
Product Pr1

3.
WHERE
0
=
(
SELECT
COUNT
(
*
)
FROM
4.
(
SELECT
type
FROM
Product
5.
EXCEPT
6.
SELECT
type
FROM
Product Pr2 7.
WHERE
Pr2.maker = Pr1.maker
8.
)
X
)
;
Этот запрос можно написать короче, если воспользоваться тем свойством
, что истинностное значение предиката