Квантор существования

Лекция 11. Квантор существования

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

Пример 6.21 . Выдать фамилии поставщиков, которые поставляют деталь Р2:

SELECT ИМЯ

FROM S

WHERE EXISTS

(SELECT *

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА = S .НОМЕР_ПОСТАВЩИКА AND НОМЕР_ДЕТАЛИ='Р2')

EXISTS (существует) представляет здесь квантор существования - понятие, заимствованное из формальной логики.

11.2. Стандартные функции

В запросах могут использоваться следующие стандартные функции:

COUNT  число значений в столбце;

SUM  сумма значений какого-либо столбца;

AVG  среднее значение;

MAX  самое большое значение в столбце;

MIN  самое малое значение в столбце.

Пример 6.23. Выдать общее количество поставщиков

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

Пример 6.26. Вычислить общий объем поставок для каждой детали .

Запрос с использованием HAVING (Исключение всех групп, для которых не

выполняется заданное условие).

Пример6.27 Выдать номера деталей, поставляемых более чем одним

п оставщиком .

11.4. Объединение с использованием UNION

Пример 6.28 . Выдать номера деталей, которые имеют вес больше 16, либо поставляются поставщиком S2 (либо то и другое) .

11.5. Многоаспектный запрос

Пример 6.29 . Выдать номер детали, вес, цвет и максимальный объем

поставки для всех красных и голубых деталей, таких, что общий объем и х

поставки больше, чем 350, исключая при этом из общего объема такие поставки,

количество которых меньше или равно 200 деталей. Результат упорядочить по

убыванию номеров деталей:

11.6. Операции обновления

UPDATE , DELETE , INSERT . Предложение UPDATE :

UPDATE таблица

SET поле = выражение [, поле = выражение]

[ WHERE предикат]

Пример 6.30. Обновление одной записи. Изменить цвет детали Р2 на желтый,

увеличить ее вес на 5 и установить значение города "неизвестный" ( NULL ) .

Обновление множества записей.

Пример 6.31. Обновление множества записей. Удвоить состояние всех поставщиков, находящихся в Перми .

UPDATE S

SET СОСТОЯНИЕ = 2 * СОСТОЯНИЕ

WHERE ГОРОД = 'Пермь'

Пример 6.32. Обновление с подзапросом. Установить нулевой объем поставок

для всех поставщиков из Перми:

UPDATE SP

SET КОЛИЧЕСТВО = 0

Пример 6.33 . Обновление нескольких таблиц. Изменить номер поставщика

S 2 на S 9:

UPDATE S

SET НОМЕР _ ПОСТАВЩИКА = 'S9'

WHERE НОМЕР_ПОСТАВЩИКА = 'S2'

UPDATE SP

SET НОМЕР_ПОСТАВЩИКА =' S 9'

WHERE НОМЕР_ПОСТАВЩИКА = 'S2'

Здесь БД становится противоречивой после выполнения первой строчки

UP ­ DATE (нарушается целостность). Поэтому требуется второй UPDATE .

Предложение DELETE :

DELETE

FROM таблица

[ WHERE предикат]

Пример 6.34 . Удаление единственной записи. Удалить поставщика S 1:

DELETE

FROM S

WHERE НОМЕР _ ПОСТАВЩИКА = ' S 1'

Пример 6.35. Удаление множества записей. Удалить всех поставщиков из

Перми :

DELETE

FROM S

WHERE ГОРОД = 'Пермь‘

Пример 6.36 . Удалить все поставки:

DELETE

FROM SP

Пример 6.37 . Удаление с подзапросом. Удалить все поставки для поставщиков из

Риги:

DELETE

FROM SP

WHERE ‘ Рига ’=

(SELECT ГОРОД

FROM S

WHERE S .НОМЕР_ПОСТАВЩИКА = SP . HOMEP _ПОСТАВЩИКА)

Предложение INSERT :

INSERT

INTO таблица [(поле [, поле] ...)]

VALUES (константа [, константа] ...)

i  я константа соответствует i-му полю

Пример 6.38 . Вставка единственной записи.

INSERT

INTO P (НОМЕР_ДЕТАЛИ, ГОРОД, ВЕС)

VALUES ('P5', ' Пермь ',12)

Можно с опущенными именами полей:

INSERT

INTO P

VALUES ('P5', ' Кулачок ', ' Голубой ', 12, ' Пермь ')

Пример 6.39. Вставка множества записей. Для каждой поставляемой детали

получить ее номер и общий объем поставок, сохранить результат в БД:

CREATE TABLE ВРЕМЕННАЯ

(НОМЕР_ДЕТАЛИ CHAR (6),

ОБЪЕМ_ПОСТАВКИ INTEGER );

INSERT

INTO ВРЕМЕННАЯ (НОМЕР_ДЕТАЛИ, ОБЪЕМ_ПОСТАВКИ)

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)

FROM SP

GROUP BY НОМЕР _ ДЕТАЛИ

Представление - это виртуальная таблица, т. е. таблица, которая сама по себе

не существует, но для пользователя выглядит, как будто она существует. В

противоположность этому базовая таблица  это реальная таблица.

Одна из задач это обеспечение логической независимости данных

Система обеспечивает логическую независимость данных, если программы

пользователей независимы от логической структуры БД. Имеются два аспекта

такой независимости - рост и реструктуризация.

Рост - когда БД растет в связи с включением новых видов информации

(расширение существующей базовой таблицы с включением нового поля,

включение новой базовой таблицы).

Реструктуризация - при изменении размещения некоторых полей в БД.

15 " width="640"

Преимущества представлений:

1. Обеспечивается определенная степень логической независимости.

2. Дается возможность различным пользователям по-разному видеть одни и те же

данные. Упрощается пользовательское восприятие. Механизм представ­лений

дает возможность пользователям сосредоточить внимание на данных, которые

представляют для них интерес, и игнорировать остальные данные.

3. Обеспечение секретности для "скрытых данных " через механизм

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

Синтаксис :

CREATE VIEW имя

[(имя_столбца [, имя_столбца] ...)]

AS подзапрос

Пример 6.40 .

CREATE VIEW ХОРОШИЕ_ПОСТАВЩИКИ

AS SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД FROM S

WHERE СОСТОЯНИЕ 15

500 " width="640"

Пример 6.41.

CREATE VIEW PQ (НОМЕР_ДЕТАЛИ, ОБЩЕЕ_КОЛИЧЕСТВО)

AS SELECT НОМЕР_ПОСТАВЩИКА, SUM (КОЛИЧЕСТВО)

FROM SP

GROUP BY НОМЕР _ ДЕТАЛИ

Здесь стандартная функция образует новое поле – ОБЩЕЕ_КОЛИЧЕСТВО.

Далее может последовать запрос:

SELECT *

FROM S

WHERE ОБЩЕЕ _ КОЛИЧЕСТВО 500