Презентация Лекция 12. Тема 2.2. Манипулирование данными. Язык SQL. Обновление отношений. Внешние соединения. (1)

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

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

2. Лекция 12. Тема 2.2. Манипулирование данными. Язык SQL. Обновление отношений. Внешние соединения.

3. Операции обновления отношений

UPDATE, DELETE, INSERT
Предложение UPDATE – изменить:
UPDATE <таблица>
SET
<поле> = <выражение> [, <поле> = <выражение>]….
[WHERE <условие>];
Обновление единственной записи
Пример 28. Изменить тип детали Р2 на тип 'Каленый', увеличить ее
вес на 5 и установить значение города как неизвестный (NULL):
UPDATE P
SET
Type = 'Каленый',
Ves = Ves + 5,
City = NULL
WHERE NP = 'P2'

4. Операции обновления отношений

Обновление множества записей
Пример 29. Удвоить состояние всех поставщиков, находящихся в Питере:
UPDATE S
SET
Status = 2 * Status
WHERE City = 'Питер'
Обновление с подзапросом
Пример 30. Установить объем поставок равным нулю для всех
поставщиков из Питер:
UPDATE SP
SET
Kol = 0
WHERE 'Питер' =
(SELECT City
FROM
S
WHERE
NS = SP.NS);

5. Операции обновления отношений

UPDATE SP
SET
Kol = 0
WHERE NS IN
(SELECT
NS
FROM
S
WHERE
City ='Питер');
Предложение DELETE – удалить:
DELETE
FROM
<таблица>
[WHERE <условие>]

6. Операции обновления отношений

Удаление единственной записи
Пример 32. Удалить поставщика S1:
DELETE
FROM
S
WHERE NS = 'S1'
Удаление множества записей
Пример 33. Удалить всех поставщиков из Питера:
DELETE
FROM
S
WHERE Сity = 'Питер'
Пример 34. Удалить все поставки:
DELETE
FROM
SP

7. Операции обновления отношений

Удаление с подзапросом
Пример 35. Удалить все поставки для поставщиков из города Рига:
DELETE
FROM
SР
WHERE NS IN
(SELECT NS
FROM
S
WHERE City='Рига');
Предложение INSERT – включить:
INSERT INTO
< таблица> [(<поле> [, <поле>] …)]
VALUES (<константа> [,< константа>] …)
i-ая константа соответствует i-му полю.

8. Операции обновления отношений

Вставка единственной записи
Пример 36.
INSERT INTO
P (NP, Name, Ves)
VALUES ('Р15', 'Подшипник',12)
Можно с пропущенными именами полей:
INSERT INTO
P
VALUES ('Р15', 'Кулачок', 'Каленый', 12, 'Пермь')

9. Операции обновления отношений

Вставка множества записей
Пример 36.1. Для каждой поставляемой детали получить ее номер и
общий объем поставок, сохранить результат в БД:
CREATE
TABLE
TEMP (NP
CHAR(6), V INTEGER);
INSERT INTO
TEMP (NP, V)
SELECT
NP, SUM (Kol)
FROM
SP
GROUP
BY
NP
Здесь предложение SELECT выполняется точно так же, как обычно,
но результат не возвращается пользователю, а копируется в таблицу
TEMP.

10. Операции обновления отношений

Использование команды SELECT ….INTO
Позволяет создать новую таблицу и загрузить в нее результат
выполнения запроса.
Пример 37. Создать новую таблицу с именем S_M и записать в эту
таблицу всех поставщиков из Москвы, добавить вычисляемое поле
New_status, значение которого равно увеличенному в десять раз
значению поля Status из таблицы S:
SELECT Name, Status, Status*10 as New_status, City
INTO S_M
FROM S
WHERE City='Москва'

11. Представления

Представление – это SQL- запрос на выборку, результат которого
пользователем воспринимается как отношение.
Представление - это виртуальная таблица, содержащая результаты
выполнения запроса к одной или нескольким таблицам. Можно сказать,
что представление является разновидностью хранимого запроса.
Представления получают имя, которое сохраняется в схеме БД, при
обращении к этому представлению выполняется запрос, его
создающий.
По одним и тем же таблицам можно построить несколько
представлений.
Синтаксис:
CREATE
VIEW
имя
[(имя_столбца [, имя_столбца] …)]
AS <SQL запрос>

12. Представления

Пример 38. Представление «Хорошие поставщики»:
CREATE
VIEW
Good_S
AS
SELECT
NS, Status, City
FROM
S
WHERE
Status > 15
Горизонтальное представление загружает в виртуальную таблицу только
строки, удовлетворяющие условию запроса.
Пример 38.2. Представление «Группы 2-ого и 3-его курсов»:
CREATE VIEW Potok
AS
SELECT *
FROM Grupp
WHERE Kurs in ('2', '3')
Вертикальное представление загружает в виртуальную таблицу только
столбцы, указанные в запросе, причем некоторым столбцам можно задать
другие имена.

13. Представления

Пример 39. Представление по объемам поставок каждой детали:
CREATE
VIEW
Temp (NP, V)
AS
SELECT
NP, SUM (Kol)
FROM
SP
GROUP
BY NP
Здесь из стандартной функции образуется новое поле – V (объем).
Далее может последовать запрос к представлению:
SELECT
*
FROM
Temp WHERE
V > 500

14. Представления

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

15. Объединение отношений

16. Объединение отношений

17. Объединение отношений

18. Объединение отношений

При таком соединении, если студент не сдавал какой-то экзамен, о нем
в результирующем отношении не будет информации.
Такие объединения называются внутренними и они определены в
стандарте SQL1.
Но если нам нужно узнать, какие экзамены студент должен был
сдавать, но еще не сдал их, такую информацию мы не получим.
Стандарт SQL2 допускает так называемые внешние
объединения, изменяется часть FROM оператора SELECT:
FROM <список таблиц> |
<выражение объединения> |
<выражение естественного объединения> |
<выражение запроса на объединение>
Вариант список таблиц означает операцию декартова произведения, к
которому можно добавить условия в части WHERE запроса.

19. Объединение отношений

Выражение объединения имеет вид
< имя Т1>
{INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
JOIN
< имя Т2>
{ ON < условие > | [USING (список столбцов)]}
INNER
означает внутреннее объединение,
FULL, LEFT или RIGHT - внешнее,
OUTER - внешнее, указывать необязательно, т.к. если указано
FULL, LEFT или RIGHT, то уже обязательно внешнее.

20. Объединение отношений

Результат внутреннего объединения включает сцепленные строки исходных отношений,
где заданные условия соединения были истинны.
Внешнее объединение возвращает все записи, которые возвращает внутреннее соединение,
плюс все записи из одного или обоих отношений, участвующих в соединении, даже если в
связанной таблице отсутствуют значения, соответствующие условию соединения.
Отсутствующие значения результирующего кортежа будут принимать значение NULL.
LEFT
- левое объединение: в результат будут включены все строки из
отношения R1, независимо от того есть для них соответствующая строка в отношении R2 или
нет, отсутствующие части результирующего кортежа, для которых не было значений в
отношении R2, соответствующих условию соединения, дополнены значением NULL.
RIGHT
- правое объединение: в результат будут включены все строки из
отношения R2, отсутствующие части результирующего кортежа, для которых не было значений
в отношении R1, соответствующих условию соединения, дополнены значением NULL.
FULL - полное левое + правое объединение, полное внешнее объединение, где в
результирующее отношение включаются все записи, которые возвращает внутреннее
соединение, и все оставшиеся строки из отношения R1, дополненные неопределенными
значениями, и все оставшиеся строки из отношения R2, также дополненные неопределенными
значениями.

21. Объединение отношений

Например, Т1 – Сотрудники, а Т2 – Дети сотрудников. Объединим эти таблицы первым способом.
Если у сотрудника нет детей, то строка о сотруднике будет, а имя ребенка будет иметь неопределенное
значение.
Выражение естественного объединения имеет вид
< имя Т1>
NATURAL {INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
JOIN
< имя Т2>
При выполнении операции естественного объединения связываются строки, в которых
значения общих столбцов совпадают. Общие столбцы включаются только один раз.
Выражение запроса на объединение соответствует операции объединения реляционной
алгебры, требует эквивалентности схем отношений, дубликаты кортежей исключаются, если их
нужно сохранить, то указывается ключевое слово ALL.
Имеет вид:
< имя Т1>
UNION [ALL]
< имя Т2>

22. Объединение отношений

Пример 40. Выдать номера деталей, которые производятся в г. Питер, либо поставляются
поставщиком S2 (либо то и другое):
SELECT NP
FROM
P
WHERE City= 'Питер'
UNION
SELECT NP
FROM
SP
WHERE NS = 'S2'
Результат:
НОМЕР_ДЕТАЛИ
P1 …..
P2
P5
Запросы, участвующие в объединении, не должны содержать вычисляемых полей и условия
сортировки. Сортировку можно применять к результату объединения, предложение ORDER BY
записывается после последнего SELECT запроса.

23. Объединение отношений