DB_2023_7

1.

Базы данных
(часть 7)
Киселев Денис Викторович

2.

SQL: оператор выборки SELECT: подзапросы
SELECT …(SELECT…)…
FROM …(SELECT…)…
WHERE …(SELECT…)…
-- результат подзапроса - одно значение
-- результат подзапроса - таблица
-- результат подзапроса зависит от
использования
Некоррелированный подзапрос – это оператор SELECT
вложенный в другой оператор SQL, не связанный с внешним
запросом (может быть выполнен отдельно от него).
Результат подзапроса одинаковый для всех строк внешнего
запроса.
Коррелированный подзапрос - это оператор SELECT,
вложенный в другой оператор SQL, и ссылающийся на один или
несколько столбцов внешнего запроса.

3.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
Некоррелированный подзапрос не связан с внешним запросом
и может быть выполнен отдельно от него.
SELECT
Номер,
AVG(Оценка),
(SELECT AVG(Оценка) FROM Оценки)
FROM Оценки
GROUP BY Номер
SELECT Фамилия, Имя,
(SELECT Sum(Стипендия)
FROM Студенты WHERE Группа = ‘МП-21’)
FROM Студенты
WHERE Группа = ‘МП-21’ And Староста = True;

4.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
SELECT Фамилия, Имя, Оценка
FROM Студенты С Left Join
(SELECT * FROM Оценки WHERE Предмет = ‘М/А’) О
On С.Номер = О.Номер
WHERE Группа = ‘МП-21’;
SELECT Фамилия, Имя, Оценка
FROM Студенты С Left Join
Оценки О On С.Номер = О.Номер
WHERE Предмет = ‘М/А’ And Группа = ‘МП-21’;

5.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
SELECT Фамилия, Имя, Оценка FROM Студенты С Left Join
(SELECT * FROM Оценки WHERE Предмет = ‘М/А’) О On С.Номер = О.Номер
WHERE Группа = ‘МП-21’;
Номер
Группа
...
111
МП-21
...
112
МП-21
...
113
МП-21
...
114
МП-22
...
Номер
Предмет
Оценка
111
М/А
...
111
ОФ
...
112
М/А
...
113
ОФ
...
114
М/А
...
С.Номер
С.Группа
О.Номер
О.Предмет
...
111
МП-21
111
М/А
...
112
МП-21
112
М/А
...
113
МП-21
NULL
NULL
...
114
МП-22
114
М/А
...

6.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
SELECT Фамилия, Имя, Оценка FROM Студенты С Left Join Оценки О
On С.Номер = О.Номер
WHERE Предмет = ‘М/А’ And Группа = ‘МП-21’;
Номер
Группа
...
111
МП-21
...
112
МП-21
...
113
МП-21
...
114
МП-22
...
Номер
Предмет
Оценка
111
М/А
...
111
ОФ
...
112
М/А
...
113
ОФ
...
114
М/А
...
С.Номер
С.Группа
О.Номер
О.Предмет
...
111
МП-21
111
М/А
...
111
МП-21
111
ОФ
...
112
МП-21
112
М/А
...
113
МП-21
113
ОФ
...
114
МП-22
114
М/А
...

7.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
SELECT Фамилия, Имя FROM Студенты WHERE Дата_рождения =
(SELECT Min (Дата_рождения) FROM Студенты);
SELECT Фамилия, Имя FROM Студенты WHERE Номер In
(SELECT Номер FROM Оценки WHERE Предмет = ‘М/А’
And Оценка = 5);
SELECT Фамилия, Имя, СБ FROM Студенты С Left Join
(SELECT Номер, Avg (Оценка) As СБ
FROM Оценки GROUP BY Номер) О
On С.Номер = О.Номер
WHERE Группа = ‘МП-21’;

8.

SQL: оператор выборки SELECT:
некоррелированные подзапросы
SELECT Фамилия, Имя FROM Студенты WHERE Факультет =
ANY(SELECT Факультет FROM Факультеты
WHERE Форма = ‘Дневная’);
SELECT Фамилия, Имя FROM Студенты WHERE Факультет =
SOME(SELECT Факультет FROM Факультеты
WHERE Форма = ‘Дневная’);
SELECT Фамилия, Имя FROM Студенты WHERE Факультет In
(SELECT Факультет FROM Факультеты WHERE Форма = ‘Дневная’);
Для получения TRUE достаточно соответствия
условию хотя бы одной записи в подзапросе!

9.

SQL: оператор выборки SELECT:
коррелированные подзапросы
Коррелированный подзапрос - это оператор SELECT,
вложенный в другой оператор SQL, и ссылающийся на один или
несколько столбцов внешнего запроса
SELECT Фамилия, Имя,
(SELECT Sum(Стипендия)
FROM Студенты WHERE Группа = С.Группа)
FROM Студенты С WHERE Староста = True;
Фамилия
Имя
Группа
Староста
SELECT Sum(Стипендия) FROM Студенты
WHERE Группа = <?>
...
...
МП-21
True
SELECT Sum(Стипендия) FROM Студенты WHERE
Группа = ‘МП-21’
...
...
МП-22
True
SELECT Sum(Стипендия) FROM Студенты WHERE
Группа = ‘МП-22’
...
...
МП-23
True
SELECT Sum(Стипендия) FROM Студенты WHERE
Группа = ‘МП-23’

10.

SQL: оператор выборки SELECT:
коррелированные подзапросы
Коррелированный подзапрос - это оператор SELECT,
вложенный в другой оператор SQL, и ссылающийся на один или
несколько столбцов внешнего запроса
SELECT Фамилия, Имя,
(SELECT Sum(Стипендия)
FROM Студенты WHERE Группа = С.Группа)
FROM Студенты С WHERE Староста = True;
SELECT Фамилия, Имя FROM Студенты С WHERE
(SELECT AVG(Оценка) FROM Оценки WHERE Номер = С.Номер) >
(SELECT AVG(Оценка) FROM Оценки WHERE Номер In
(SELECT Номер FROM Студенты WHERE Группа = С.Группа))

11.

SQL: оператор выборки SELECT:
коррелированные подзапросы
SELECT Фамилия, Имя FROM Студенты С WHERE 5 =
ANY(SELECT Оценка FROM Оценки WHERE Номер = С.Номер)
SELECT Фамилия, Имя FROM Студенты С WHERE 5 =
ALL(SELECT Оценка FROM Оценки WHERE Номер = С.Номер)
SELECT Фамилия, Имя FROM Студенты С WHERE 4 <=
ANY(SELECT Оценка FROM Оценки WHERE Номер = С.Номер)

12.

SQL: оператор выборки SELECT:
коррелированные подзапросы
SELECT Фамилия, Имя FROM Студенты С WHERE EXISTS
(SELECT Оценка FROM Оценки WHERE Номер = С.Номер
And Оценка = 5)
Номер
Предмет
Дата
Оценка
111
М/А
05.01.10
2
111
М/А
20.01.10
2
111
М/А
07.02.10
3
SELECT Номер, Предмет, Оценка FROM Оценки О WHERE Дата =
(SELECT Max (Дата) FROM Оценки
WHERE Номер = О.Номер And Предмет = О.Предмет)
SELECT * FROM Оценки О WHERE EXISTS
(SELECT * FROM Оценки WHERE Дата < О.Дата
And Номер = О.Номер And Предмет = О.Предмет)

13.

SQL: оператор выборки SELECT:
копирование данных в новую таблицу
SELECT Фамилия, Имя, Отчество
INTO Студенты_В1
FROM Студенты
WHERE Факультет = ‘Вечерний’ And Курс=1

14.

SQL: оператор выборки SELECT
Объединение:
SELECT Фамилия, Имя, Отчество FROM Студенты
UNION ALL
SELECT Фамилия, Имя, Отчество FROM Сотрудники;
Объединение (уникальные строки в результате):
SELECT Фамилия, Имя, Отчество FROM Студенты
UNION
SELECT Фамилия, Имя, Отчество FROM Сотрудники;

15.

SQL: оператор выборки SELECT
Пересечение:
SELECT Фамилия, Имя, Отчество FROM Студенты
INTERSECT
SELECT Фамилия, Имя, Отчество FROM Сотрудники;
SELECT С.Фамилия, С.Имя, С.Отчество
FROM Студенты Ст inner join Сотрудники С
on Ст.Фамилия = С.Фамилия
and Ст.Имя = С.Имя
and Ст.Отчество = С.Отчество;

16.

SQL: оператор выборки SELECT
Исключение:
SELECT Фамилия, Имя, Отчество FROM Студенты
EXCEPT
SELECT Фамилия, Имя, Отчество FROM Сотрудники;
SELECT Ст.Фамилия, Ст.Имя, Ст.Отчество
FROM Студенты Ст
WHERE NOT EXISTS (SELECT * FROM Сотрудники С
WHERE Ст.Фамилия = С.Фамилия
and Ст.Имя = С.Имя
and Ст.Отчество = С.Отчество);

17.

Объекты БД: представления
Представление (view) – именованная динамически
поддерживаемая CУБД выборка из одной или
нескольких таблиц (виртуальная таблица,
содержание которой определяется запросом).
Актуальные данные.
Простой доступ к сложным данным.
Способ обеспечения безопасности доступа и
скрытия структуры.

18.

Объекты БД: представления
CREATE VIEW MP_VIEW AS
SELECT Номер, Фамилия, Имя, Отчество
FROM Студенты
WHERE Факультет = ‘МП’;
SELECT Фамилия, Имя FROM MP_VIEW;
SELECT * FROM MP_VIEW M inner join Оценки О
On M.Номер = О.Номер
WHERE Оценка = 5;

19.

SQL: оператор INSERT
INSERT INTO Table_1 (ID, Ф, И, О)
VALUES (111111, ‘Иванов’, ‘Иван’, ‘Иванович’);
INSERT INTO Table_1 VALUES
(111112, ‘…’, ‘…’, ‘…’),
(111113, ‘…’, ‘…’, ‘…’),
(111114, ‘…’, ‘…’, ‘…’);
INSERT INTO Table_1 (ID, Ф, И, О)
SELECT Номер, Фамилия, Имя, Отчество
FROM Студенты WHERE Группа = ‘МП-21’;

20.

SQL: оператор UPDATE
UPDATE Table_1 SET Ф=‘Петров’ WHERE Ф=‘Иванов’;
UPDATE Table_1
SET Ф=‘Иванов’, И=‘Иван’, О = ‘Иванович’
WHERE Ф=‘Петров’;
UPDATE Table_1 SET Ф=‘Петров’
UPDATE Table_1 SET Ф=‘Петров’
FROM Table_1 T inner join Студенты С
On T.ID = С.Номер WHERE Группа = ‘МП-21’;

21.

SQL: оператор DELETE
DELETE FROM Студенты;
DELETE FROM Студенты WHERE Группа = ‘МП-21’;
DELETE FROM Студенты WHERE Номер In
(SELECT Номер FROM Оценки WHERE Оценка = 2);