Лекция 11. Тема 2.2. Манипулирование данными. Язык SQL. (продолжение)

1. Базы данных

«КАЗАНСКИЙ НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ
ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ ИМ. А.Н. ТУПОЛЕВА-КАИ»
(КНИТУ-КАИ)
ИНСТИТУТ КОМПЬЮТЕРНЫХ ТЕХНОЛОГИЙ И ЗАЩИТЫ ИНФОРМАЦИИ
КАФЕДРА АВТОМАТИЗИРОВАННЫХ СИСТЕМ ОБРАБОТКИ ИНФОРМАЦИИ И УПРАВЛЕНИЯ
Базы данных
Гаптуллазянова Гульшат Ильдусовна
Ст. преподаватель каф. АСОИУ
Казань 2022

2. Лекция 10. Тема 2.2. Манипулирование данными. Язык SQL. (продолжение)

3. Операторы манипулирования данными

Основной смысл оператора
SELECT
собрать строки( поля)
FROM
из таблиц
WHERE
где выполняются заданные условия
Рассмотрим возможности языка SQL на примере БД поставщиков и
деталей:
CREATE
( NS
Name
Status
City
);
TABLE
S
CHAR (5) PRIMARY KEY,
CHAR (20) NOT NULL UNIQUE,
SMALLINT,
CHAR (15)

4. Операторы манипулирования данными

CREATE
( NP
Name
Type
Ves
City
);
TABLE
P
CHAR (6) PRIMARY KEY,
CHAR (20) NOT NULL,
CHAR (10),
SMALLINT,
CHAR (15)
CREATE TABLE
SP
( NS
CHAR (5),
NP
CHAR (6),
Kol
INTEGER,
FOREIGN KEY (NS) references S,
FOREIGN KEY (NP) references P
);

5. Операторы манипулирования данными

База данных поставщиков и деталей

6. Операторы манипулирования данными

База данных поставщиков и деталей

7. Рассмотрим примеры

Пример 11. Выдать все комбинации информации о
таких поставщиках и деталях, которые размещены в одном
и том же городе.
SELECT S.*, P.*
FROM
S, P
WHERE S.City = P.City
Сначала будет построено декартово произведение
таблиц (для БД Поставки 30 строк), затем будут выбраны
все строки с одинаковыми городами. Это не эффективно.

8. Рассмотрим примеры

Стандарт SQL2 позволяет проверять условие во время соединения
отношений.
SELECT S.*, P.*
FROM
S INNER JOIN P ON S.City = P.City
Соединение с дополнительным условием:
SELECT S.*, P.*
FROM
S INNER JOIN P ON S.City = P.City
WHERE S.Status > 20
В SELECT могут быть указаны выборочные поля или все поля:
S.Name, P.Name
или
SELECT S.*, P.*
или
SELECT * (не рекомендуется)
Может быть соединение из 3-х, 4-х или любого числа таблиц.

9. Рассмотрим примеры

Пример 12. Выбрать названия и город изготовления деталей,
которые поставляет поставщик S4.
SELECT P.Name, P.City
FROM P INNER JOIN SP ON P.NP = SP.NP
WHERE SP.NS = 'S4'
Пример 12.1. Выдать информацию о поставщиках (город,
статус и др.), которые поставляют детали, изготавливаемые в
городе Саратов.
SELECT S.*
FROM (S INNER JOIN SP ON S.NS= SP.NS)
INNER JOIN P ON SP.NP= P.NP
WHERE P.City= 'Саратов'

10.

Соединение, в основе которого лежит оператор
равенства называется эквисоединение.
Естественное
соединение
частный
случай
эквисоединения, когда в сравнении на равенство
участвуют все общие поля двух отношений.
При соединении необязательно должно быть
равенство.
Тета-соединением называется соединение, основанное
на любом операторе сравнения, кроме равенства.
SELECT S.Name, S.City, P.Name, P.City
FROM
S INNER JOIN P ON
S.City <> P.City

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

Подзапросы
представляют
собой
вложенные
предложения SELECT. Именно поэтому язык называется
структурированным.
Пример 13. Выбрать названия деталей, которые поставляет
поставщик S4.
SELECT Name
FROM
P
WHERE
NP
IN
(SELECT NP
FROM
SP
WHERE
NS= 'S4');

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

Подзапрос возвращает множество деталей, которые поставляет
поставщик S4, а именно множество ('Р2', 'Р4', 'Р5'). Поэтому
первоначальный запрос эквивалентен простому запросу:
SELECT Name
FROM P
WHERE
NP IN ('Р2', 'Р4', 'Р5')
Можно явно задать имя таблицы.
SELECT P.Name
FROM
P
WHERE
P.NP
IN
(SELECT SP.NP
FROM
SP
WHERE
SP.NS= 'S4')

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

Этот же подзапрос может быть выражен и соединением:
SELECT P.Name
FROM
P INNER JOIN SP ON P.NP = SP.NP
WHERE
SP.NS = 'S4'

14. Подзапросы с несколькими уровнями вложени я

Подзапросы с несколькими уровнями вложения
Пример 14. Выдать информацию о поставщиках, которые
поставляют детали, изготавливаемые в городе 'Саратов'.
SELECT *
FROM S WHERE NS IN
(SELECT NS
FROM SP WHERE NP IN
(SELECT NP
FROM P
WHERE City= 'Саратов') )

15. Подзапросы с несколькими уровнями вложени я

Подзапросы с несколькими уровнями вложения
Пример 14.1. Выдать информацию о поставщиках, которые
поставляют детали типа 'Каленый'.
SELECT *
FROM
S
WHERE
NS
IN
(SELECT NS
FROM
SP
WHERE
NP IN
(SELECT NP
FROM
P
WHERE
Color = 'Каленый'))

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

Коррелированный подзапрос – подзапрос, результат
которого зависит от строки, рассматриваемой главным
запросом.
Пример 15. Выдать фамилии поставщиков, которые
поставляют деталь P2.
Обычный подзапрос:
SELECT Name
FROM S
WHERE NS IN
(SELECT NS
FROM SP
WHERE NP='Р2')

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

После подзапроса
SELECT Name
FROM S
WHERE NS IN ( 'S1', 'S2', 'S3', 'S4')
Коррелированный подзапрос:
SELECT Name
FROM
S
WHERE
'P2'
IN
(SELECT NP
FROM
SP
WHERE
NS = S.NS)
Корреляция на строку внешней таблицы

18. Квантор существования. Запрос, использующий EXISTS.

EXISTS (существует) представляет здесь квантор
существования – понятие, заимствованное из формальной
логики. Можно использовать NOT EXISTS.
Пример 15.1.
SELECT Name
FROM
S
WHERE
EXISTS
существует результат
(SELECT *
FROM
SP
WHERE
NS = S. NS AND NP ='P2')

19. Квантор существования. Запрос, использующий EXISTS.

Пример 16. Выдать номера поставщиков, не поставляющих
деталь Р2.
SELECT NS, Name
FROM
S
WHERE
NOT EXISTS
не существует результат
(SELECT *
FROM
SP
WHERE
NS = S. NS AND NP='P2')
Корреляция на строку внешней таблицы

20. Квантор существования. Запрос, использующий EXISTS.

Система проверяет первую строку таблицы S. Предположим,
что это строка поставщика 'S1'. Тогда переменная S.NS в данный
момент имеет значение 'S1', и система обрабатывает внутренний
запрос:
(SELECT *
FROM
SP
WHERE
NS = 'S1' AND NP='P2')
Далее система будет повторять обработку такого рода для
следующего поставщика и т. д., пока не будут рассмотрены все
строки таблицы S. Такой подзапрос, как в этом примере,
называется коррелированным.

21. Использование одной и той же таблицы в подзапросе и внешнем запросе:

Пример 17. Выдать номера поставщиков, которые поставляют по
крайней мере одну деталь, поставляемую поставщиком S2:
SELECT DISTINCT NS
FROM
SP
WHERE
NP
IN
(SELECT NP
FROM
SP WHERE
После выполнения подзапроса:
SELECT DISTINCT NS
FROM
SP WHERE
NP
NS = 'S2')
IN ('P1', 'P2')

22. Решение этой задачи с использованием псевдонимов (псевдоним – это альтернативное имя таблицы):

SELECT DISTINCT SP1.NS
FROM
SP SP1
WHERE
SP1.NP
IN
(SELECT SP2.NP
FROM
SP SP2
WHERE
SP2.NS = 'S2')

23. Решения задачи соединением таблиц невозможно без использования псевдонимов:

SELECT DISTINCT SP1.NS
FROM SP SP1 INNER JOIN SP SP2 ON SP1.NP = SP2.NP
WHERE
SP2.NS= 'S2'
Упрощенный пример
SP1
S1P1
S1
S1P2
S1
S2P1
S2
S3P1
S3
S3P3
S3
SP2
P1
P2
P1
P1
P3

24. Решения задачи соединением таблиц невозможно без использования псевдонимов:

Результат соединения R:
S1 P1
S1
P1
S1 P1
S2
P1
S1 P1
S3
P1
S1 P2
S1
P2
S2 P1
S1
P1
S2 P1
S2
P1
S2 P1
S3
P1
S3 P1
S1
P1
S3 P1
S2
P1
S3 P1
S3
P1
S3 P3
S3
P3

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

Пример 18. Выдать номера поставщиков, находящихся в
том же городе, что и поставщик S1:
SELECT NS
FROM
S
WHERE City =
(SELECT City
FROM
S WHERE NS = 'S1')
Результат:
S1 S4

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

SUM – сумма значений какого-либо столбца;
AVG – среднее значение какого-либо столбца;
MAX – самое большое значение в столбце;
MIN – самое малое значение в столбце;
COUNT – число значений в столбце.
Функции SUM, AVG применяются к числовым полям.
Функции MAX, MIN, COUNT могут применяться как к
числовым, так и к символьным полям.

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

Пример 19. Выдать общее количество поставщиков (из
города Москва):
SELECT COUNT (*) AS [Количество]
FROM
S
( WHERE City= 'Москва' )
Результат: 2
Пример 19.1. Выдать количество различных поставляемых
деталей:
SELECT COUNT (DISTINCT NP) AS [Количество]
FROM
SP
Результат: 6

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

Пример 20. Выдать информацию о поставщиках, с максимальными
объемами поставки:
SELECT *
FROM
S
WHERE
NS IN
(SELECT NS
FROM SP
WHERE Kol =
( SELECT MAX (Kol)
FROM SP ) )
или
SELECT *
FROM
S INNER JOIN SP ON
S. NS = SP.NS
WHERE Kol =
( SELECT MAX (Kol) FROM SP )

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

Пример 21. Выдать информацию о поставщиках со
значением поля Status большим, чем текущее среднее
состояние в таблице S:
SELECT *
FROM
S
WHERE Status >
(SELECT AVG (Status)
FROM S)

30. Использование группировок GROUP BY

Позволяют вычислять обобщенные групповые значения.
Агрегатные функции вычисляют одиночное значение для
каждой группы. Перед группировкой необходимо исключить
неопределенные значения, если таковые могут повлиять на
результат.
Пример 22. Вычислить общий объем поставок:
SELECT SUM(Kol) AS [Объем поставок]
FROM
SP

31. Использование группировок GROUP BY

Пример 23. Выдать общее количество поставляемых
деталей P2:
SELECT NP, SUM (Kol) AS [Объем поставок]
FROM
SP
WHERE
NP='P2'
GROUP BY NP
Результат: Р2 1000

32. Использование группировок GROUP BY

Пример 23.2. Вычислить общий объем поставок для каждой
детали:
SELECT NP, SUM (Kol) AS [Объем поставок]
FROM
SP
GROUP
BY NP
Результат:
P1
600
P2
1000
P3
400
P4
500
P5
500
P6
100

33. Использование группировок GROUP BY

Пример 23.3. Вычислить количество поставок для каждой
детали:
SELECT NP, COUNT (*) AS [Количество]
FROM
SP
WHERE Kol IS NOT NULL
GROUP
BY NP

34. Инструкция COUNT

Инструкция COUNT (*) позволяет подсчитать количество с учетом
NULL значений, если этого не нужно, то нужно исключить эти
значения явно. Если указано используется инструкция COUNT (<имя
поля>), то подсчитываются только определенные значения.
Например, результатом следующего запроса будет количество всех
поставщиков:
SELECT COUNT (*) AS [Количество поставщиков]
FROM
S
А в результате следующего запроса будет получено количество
поставщиков с непустым значением поля City:
SELECT COUNT (City) AS [Количество поставщиков]
FROM
S

35. Использование HAVING для определения ограничений на группы

Результатом использования HAVING будет сгруппированная таблица,
исключающая все группы, для которых не выполняется условие.
Пример 23.4. Вычислить общий объем поставок для деталей, суммарный
объем, поставок которых больше или равен 500:
SELECT NP, SUM (Kol) AS [Объем поставок]
FROM
SP
GROUP
BY NP
HAVING SUM(Kol) >= 500
Результат:
P1
600
P2
1000
P4
500
P5
500

36. Использование HAVING для определения ограничений на группы

Пример 23.5. Вычислить общий объем поставок для деталей,
суммарный объем поставок которых больше или равен 500, не учитывать
поставки поставщика S2:
SELECT NP, SUM (Kol) AS [Объем поставок]
FROM
SP
WHERE NS < > 'S2'
GROUP
BY NP
HAVING SUM(Kol) >= 500
Результат:
P2
600
P4
500
P5
500

37. Использование HAVING для определения ограничений на группы

Пример 24. Выдать номера деталей и объем их поставок для деталей,
поставляемых более чем одним поставщиком:
SELECT
FROM
GROUP
HAVING
Результат:
Р1 600
Р2 1000
Р4 500
Р5 500
NP, SUM(Kol) AS [Объем поставок]
SP
BY NP
COUNT (*) > 1

38. Использование HAVING для определения ограничений на группы

Пример 25. Выдать город производства и объем поставок
для деталей, изготавливаемых в Москве или Питере:
SELECT P.City, SUM(SP.Kol) AS [Объем поставок]
FROM P INNER JOIN SP ON P.NP= SP.NP
GROUP BY P.City
HAVING P.City IN ('Питер', 'Москва')

39. Использование HAVING для определения ограничений на группы

Пример 26. Найти номера поставщиков, которые поставляют все
детали.
Формулировка может быть такой: это такие поставщики, для которых не
существует детали, которую они не поставляют.
SELECT DISTINCT SP1.NS
FROM SP SP1
WHERE NOT EXISTS
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SP SP2
WHEE (SP1.NS=SP2.NS AND SP2.NP=P.NP)
)
)

40. Использование HAVING для определения ограничений на группы

Пример 26.
Найти номера поставщиков, которые
поставляют все детали.
Но этот запрос может быть реализован и другим способом:
это такие поставщики, которые поставляют столько видов
деталей, сколько всего видов деталей.
SELECT SP.NS, S.Names, S.City
FROM SP INNER JOIN S ON SP.NS= S.NS
GROUP BY SP.NS, S.Names, S.City
HAVING COUNT(SP.NP)=
(SELECT COUNT(NP)
FROM P
)

41. Использование HAVING для определения ограничений на группы

Пример 26.
Найти номера поставщиков, которые
поставляют все детали.
Упростим
SELECT NS
FROM SP
GROUP BY NS
HAVING COUNT(NP)=
(SELECT COUNT(NP)
FROM P
)

42. Спасибо за внимание!

42