Квантор существования. Запрос, использующий EXISTS
Пример 5.21. Выдать фамилии поставщиков, которые поставляют деталь Р2:
SELECT ИМЯ
FROM S
WHERE EXISTS
(SELECT *
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = S.НОМЕР_ПОСТАВЩИКА AND НОМЕР_ДЕТАЛИ='Р2')
EXISTS (существует) представляет здесь квантор существования - понятие, заимствованное из формальной логики.
Квантор существования EXISTS (SELECT* FROM… WHERE….) принимает значение истина, если подзапрос выдает непустое множество и наоборот.
Пример 5.22.Запрос, использующий NOT EXISTS. Выдать имена поставщиков, которые не поставляют деталь Р2:
SELECT ИМЯ
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = S.НОМЕР_ПОСТАВЩИКА AND НОМЕР_ДЕТАЛИ = 'P2')
NOT EXISTS (SELECT* FROM… WHERE….)
принимает значение истина, если подзапрос выдает пустое множество и наоборот.
Стандартные функции
В запросах могут использоваться следующие стандартные функции: COUNT - число значений в столбце; SUM - сумма значений какого-либо столбца; AVG - среднее значение; MAX - самое большое значение в столбце; MIN - самое малое значение в столбце.
Пример 5.23. Выдать общее количество поставщиков:
SELECT СOUNT (*)
FROM S
Результат: 5
Пример 5.24. Выдать общее количество поставляемых деталей P2:
SELECT SUМ (Количество)
FROM SP
WHERE НОМЕР_ДЕТАЛИ = ‘P2’
Результат: 1000
Пример 5.25 . Функция в подзапросе. Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем текущее максимальное состояние в таблице S:
|
|
|
SELECT НОМЕР_ПОСТАВЩИКА
FROM S
WHERE СОСТОЯНИЕ <
(SELECT MAX (СОСТОЯНИЕ)
FROM S)
Результат: НОМЕР ПОСТАВЩИКА
S1
S2
S3
Использование группировок (GROUP BY)
Пример 5.26. Вычислить общий объем поставок для каждой детали:
SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)
FROM SP
GROUP BY НОМЕР_ДЕТАЛИ
Результат: НОМЕР ДЕТАЛИ
Р1 600
Р2 1000
РЗ 400
Р4 500
Р5 500
Р6 100
Пример 5.27. Запрос с использованием HAVING (Исключение всех групп, для которых не выполняется заданное условие). Выдать номера деталей, поставляемых более чем одним поставщиком:
SELECT НОМЕР_ДЕТАЛИ
FROM SP
GROUP BY НОМЕР_ДЕТАЛИ
HAVING COUNT (*) > 1
Результат: НОМЕР ДЕТАЛИ
Р1
Р2
|
|
|
Р4
Р5
Объединение с использованием UNION
Пример 5.28. Выдать номера деталей, которые имеют вес больше 16, либо поставляются поставщиком S2 (либо то и другое):
SELECT НОМЕР_ДЕТАЛИ
FROM P
WHERE ВЕС > 16
UNION
SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = 'S2'
Результат: НОМЕР ДЕТАЛИ
Р1
Р2
РЗ
Р6
Операции обновления
UPDATE, DELETE, INSERT. Предложение UPDATE:
UPDATE таблица
SET поле = выражение [, поле = выражение]
[WHERE предикат]
Пример 5.29. Обновление одной записи. Изменить цвет деталиР2 на желтый, увеличить ее вес на 5 и установить значение города "неизвестный" (NULL):
UPDATE P
SET ЦВЕТ = 'Желтый',
ВЕС = ВЕС + 5,
ГОРОД = NULL
WHERE НОМЕР_ДЕТАЛИ = 'Р2'
Пример 5.30. Обновление множества записей. Удвоить состояние всех поставщиков, находящихся в Перми:
UPDATE S
SET СОСТОЯНИЕ = 2 * СОСТОЯНИЕ
WHERE ГОРОД = 'Пермь'
Пример 5.31. Обновление с подзапросом. Установить нулевой объем поставок для всех поставщиков из Перми:
|
|
|
UPDATE SP
SET КОЛИЧЕСТВО = 0
WHERE 'Пермь' =
(SELECT ГОРОД
FROM S
WHERE S.HOMEP_ПОСТАВЩИКА = SP. НОМЕР_ПОСТАВЩИКА)
Пример 5.32. Обновление нескольких таблиц. Изменить номер поставщика S2 на S9:
UPDATE S
SET НОМЕР_ПОСТАВЩИКА = 'S9'
WHERE НОМЕР_ПОСТАВЩИКА = 'S2'
UPDATE SP
SET НОМЕР_ПОСТАВЩИКА ='S9'
WHERE НОМЕР_ПОСТАВЩИКА = 'S2'
Здесь БД становится противоречивой после выполнения первой строчки UPDATE (нарушается целостность). Поэтому требуется второй UPDATE.
Предложение DELETE:
DELETE
FROM таблица
[WHERE предикат]
Пример 5.33. Удаление единственной записи.
Удалить поставщика S1:
DELETE
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = ' S 1'
Пример 5.34. Удаление множества записей. Удалить всех поставщиков из
Перми:
DELETE
FROM S
WHERE ГОРОД = 'Пермь'
Пример 5.35. Удалить все поставки:
DELETE
FROM SP
Пример 5.36. Удаление с подзапросом. Удалить все поставки для поставщиков из Риги:
|
|
|
DELETE
FROM SP
WHERE ‘Рига’=
(SELECT ГОРОД
FROM S
WHERE S .НОМЕР_ПОСТАВЩИКА = SP.HOMEP_ПОСТАВЩИКА)
Предложение INSERT:
INSERT
INTO таблица [(поле [, поле] ...)]
VALUES (константа [, константа] ...)
i - я константа соответствует i-му полю.
Пример 5.37. Вставка единственной записи.
INSERT
INTO P (НОМЕР_ДЕТАЛИ, ГОРОД, ВЕС)
VALUES ('P5', 'Пермь',12)
Можно с опущенными именами полей:
INSERT
INTO P
VALUES ('P5', 'Кулачок', 'Голубой', 12, 'Пермь')
Пример 5.38. Вставка множества записей. Для каждой поставляемой детали получить ее номер и общий объем поставок, сохранить результат в БД:
CREATE TABLE ВРЕМЕННАЯ
(НОМЕР_ДЕТАЛИ CHAR(6),
ОБЪЕМ_ПОСТАВКИ INTEGER);
INSERT
INTO ВРЕМЕННАЯ (НОМЕР_ДЕТАЛИ, ОБЪЕМ_ПОСТАВКИ)
SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)
FROM SP
GROUP BY НОМЕР_ДЕТАЛИ
Здесь предложение SELECT выполняется точно так же, как обычно, но результат не возвращается пользователю, а копируется в таблицу ВРЕМЕННАЯ.
Встроенный язык SQL
При включении операторов SQL в базовый язык программирования необходимо чтобы операторы SQL включались непосредственно в текст программы исходного языка программирования.
Во встроенном SQL, запросы делятся на два типа:
· Однострочные запросы,
· Многострочные запросы
Однострочный запрос во встроенном SQL вызвал необходимость модификации оператора SQL – SELECT. Здесь появляется дополнительный атрибут INTO. С помощью INTO, найденные значения полей базы данных передаются в переменные базового языка.
SELECT [ALL | DISTINCT] <список полей>
INTO <список переменных базового языка>
FROM <список исходных таблиц>
[WHERE <предикат>]
Пример. Пусть имеется отношение: STUD (NS, FIO, GR, SPEC)
DECLARE p1 int,
DECLARE p2 char(20), p3 char(4), p4 char(30)
SET p1=205
SELECT *
INTO p2, p3, p4
FROM STUD
WHERE NS=p1
Для реализации многострочных запросов, вводится новое понятие – понятие курсора. Для работы с курсором добавляется несколько новых операторов SQL.
DECLARE <имя курсора> CURSOR – объявление некоторого курсора, с помощью которого можно проводить обработку записей.
FOR <подзапрос>
OPEN <курсор> - оператор открытия курсора.
По команде OPEN происходит выполнение запроса.
FETCH <курсор> INTO <список переменных>, используется для выборки записей и передачи значений в список переменных.
CLOSE <курсор> - оператор закрытия курсора
Пример:
DECLARE q CURSOR
FOR SELECT *
FROM STUD
WHERE SPEC=”230102”
OPEN q
WHILE .T.
FETCH q INTO A, B, C, D,
DO PROC
END WHILE
CLOSE q
Пример. Пусть имеется БД, связанная с поставками изделий.
Поставщики S(NП, ФИО, Состояние)
Детали P(NД, НазваниеД, Цвет, Вес, Город)
Изделия J(NИ, НазваниеИз, Город)
Поставки SPJ(NП, NД, NИ, Количество)
Необходимо составить программу вывода записей поставщиков в порядке их номеров, при этом за каждой записью поставщика должны непосредственно следовать в порядке номеров изделий все записи изделий, для которых поставляет детали данный поставщик.
MAIN
DEFINE PS.* LIKE S.*
DEFINE PJ.* LIKE J.*
DECLARE CS CURSOR FOR
SELECT NП, ФИО, Состояние, Город
FROM S
ORDER BY NП
DECLARE CS CURSOR FOR
SELECT NИ, НазваниеИз, Город
FROM J
WHERE NИ IN
(SELECT NИ
FROM SPJ
WHERE NП=S.NП)
ORDER BY NИ
OPEN CS
LET V=”1”B
LET R=”1”B
WHILE V
IF STATUS=NOTFOUND
THEN MESSAGE “NO ROW FOUND”
EXIT WHILE
END IF
FETCH CS INTO PS.NP, PS.ФИО, PS.Состояние, PS.Город
DISPLAY PS.NP, PS.ФИО, PS.Состояние, PS.Город
OPEN CJ
WHILE R
FETCH CJ INTO PJ.NИ, PJ.НазваниеИз, PJ.Город
DISPLAY PJ.NИ, PJ.НазваниеИз, PJ.Город
END WHILE
CLOSE CJ
END WHILE
CLOSE CS
END MAIN
Динамический SQL
Если множество команд, которые может принимать программа, сравнительно невелико (например, бронирование мест на авиалиниях), то и множество возможных предложений SQL, выдаваемых программой, также будет небольшим и может быть зашито в программу.
Если же количество вариантов входных данных будет большим, то зашить их в программу не удается. В этом случае удобно динамически формировать предложения SQL.
Для динамического формирования SQL используются два основных предложения:
PREPARE – подготовить
EXECUTE – выполнить
Схема их использования состоит в следующем:
DEFINE Исходный-SQL CHAR
DECLARE Объектный-SQL оператор
Исходный-SQL=”DELETE FROM SP
WHERE количество<100”
PREPARE Объектный-SQL FROM Исходный-SQL
EXECUTE Объектный-SQL
Вопросы
Дата добавления: 2018-04-04; просмотров: 493; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!