Размещено на http://www.allbest.ru/

1. Операции над отношениями

Одним из основных преимуществ реляционной модели является ее однородность. Все данные рассматриваются как хранимые в таблицах, в которых каждая строка имеет один и тот же формат.

Схемой отношения R называется конечное множество имен атрибутов {A₁,A₂,...,A_N}. Каждому множеству атрибута A_i ставится в соответствие множество D_i , называемое доменом атрибута A_i, 1< i < N . Домены являются произвольными непустыми конечными или счетными множествами.

Обновление отношений. Что можно делать с отношениями? Содержимое отношений изменяется во времени. Предположим, что нужно поместить дополнительную информацию в отношение. Для этого вводится операция добавление, которая для отношения R(A₁,А₂,... ,А_N) имеет вид:

ADD (R; A₁=d₁, A₂₌d₂,.. .,A_N=d_N)

Пример.

ADD (Расписание; НОМЕР=117, ПУНКТ_ОТПРАВЛЕНИЯ=РИГА,

ПУНКТ_НАЗНАЧЕНИЯ=КИЕВ, ВРЕМЯ_ВЫЛЕТА=22.05,

ВРЕМЯ_ПРИБЫТИЯ=0.43)

Когда порядок имен атрибутов фиксирован, допустима более короткая запись:

ADD (Расписание; 117, РИГА, КИЕВ, 22.05, 0.43)

Результат операции может быть ошибочен, если: добавляемый кортеж не соответствует схеме определенного отношения; некоторые значения кортежа не принадлежат соответствующим доменам; описанный кортеж совпадает по ключу с кортежем, уже находящимся в отношении. Во всех случаях операция ADD оставляет отношение R неизменным и сообщает об ошибке.

Операция удаление: DEL (R; A₁=d₁, A₂=d₂, ..., A_N=d_N)

Если имена атрибутов упорядочены, то DEL (R; d₁, d₂, ..., d_i).

Например: DEL (расписание, 305, МОСКВА, СВЕРДЛОВСК, 21.50, 15.10)

Можно произвести удаление по ключу: DEL (расписание; 83).

Операция изменения: СН (R; В₁=d₁; B₂=d₂,..., В_m=d_m; C₁=e₁, C₂ =e₂, ..., C_р=e_р)

Пример.

СН (Расписание; НОМЕР=323, ПУНКТ_ОТПРАВЛЕНИЯ=МОСКВА, ПУНКТ_НАЗНАЧЕНИЯ=СВЕРДЛОВСК, ВРЕМЯ_ВЫЛЕТА=21.30, ВРЕ-МЯ_ПРИБЫТИЯ= 14.50)

Сокращенный вариант:

СН (расписание; ОМЕР=323, ВРЕМЯ_ВЫЛЕТА=21.30, ВРЕМЯ_ПРИБЫТИЯ=14.50)

Выполнение операций над отношениями

Для получения информации из отношений необходим язык манипулирования данными (ЯМД), способный выполнять соответствующие операции над отношениями. Наиболее важной частью ЯМД является его раздел для формулировки запросов.

Были разработаны три типа теоретических языков: реляционная алгебра, реляционное исчисление с переменными-кортежами, реляционное исчисление с переменными-доменами. Реальные языки (ISBL, SEQUEL, QBE и другие).

Реляционная алгебра. Основные операции реляционной алгебры можно разделить на традиционные и специализированные. Операции первой группы: декартово произведение, объединение, пересечение, разность, симметричная разность. Операции второй группы: проекция, ограничение, соединение, деление.

1. Объединение отношений R₁ и R₂

R = R₁ R₂ ⁼ {г | г R₁ r R₂ }

2. Разность отношений R₁ и R₂

R = R₁ - R₂ = {r r R₁ r R₂}

3. Декартово произведение отношений R₁ и R₂

R = R₁ х R₂ = {r₁r₂ r ₁ R₁ r ₂ R₂}

4. Проекция отношения R₁ на компоненты i₁, i₂, ..., i_R:

R = i₁, i₂, …,i_R (R₁ ),

где i₁, i₂, ..., i_R номера столбцов отношения R₁.

Деление - представляет процесс, соответствующий операции обратной к декартовому произведению.

(R₁ R₂ ) R₂ = R₁

2. Реляционные языки запросов

Существуют языки: ISBL - язык системы PRTV, QUEL -язык системы INGRES, SQL - язык системы System R; QBE - язык интерфейса высокого уровня в ряде СУБД, PIQUE - язык экспериментальной системы PITS. ISBL ос-нован на реляционной алгебре. QUEL и SQL - на исчислении кортежей. QBE -язык, основанный на исчислении доменов. PIQUE - похож на исчисление кортежей, но обеспечивает интерфейс со схемой помощью W-функций. квантор реляционный оператор

2.1 Язык SQL (Structured Query Language)

Язык был разработан в компании IBM в начале 70-х годов, его первой реализацией был продукт System R; впоследствии он был реализован в много-численных коммерческих продуктах как фирмы IBM, так и других изготовите-лей. Язык SQL стал Американским национальным стандартом (ANSI), Международным стандартом (ISO), стандартом системы UNIX (X/Open). Он используется для описания реляционных операций. Стандарты: SQL/89, SQL/92.

В реляционных системах выполняются как минимум 2 условия:

1. Данные воспринимаются пользователем как таблицы.

2. В распоряжении пользователя имеются операторы (например, для выборки данных), среди которых есть операторы SELECT, PROJECT и JOIN.

Операция SELECT предназначена для извлечения определенных строк из таблиц

Операция PROJECT предназначена для извлечения определенных столбцов из таблицы.

Операция JOIN предназначена для соединения двух таблиц на основе общих значений в общих столбцах.

Все эти операции могут быть сформулированы на языке SQL.

Пусть имеются 2 таблицы:

ОТДЕЛЫ (№ отдела, Наименование, Бюджет)

СЛУЖАЩИЕ (№ служащего, № отдела, Оклад)

1. SELECT:

SELECT № отдела, Наименование, Бюджет

FROM ОТДЕЛЫ

WHERE Оклад >400р.

2. PROJECT:

SELECT № отдела, Бюджет

FROM ОТДЕЛЫ

3. JOIN:

SELECT ОТДЕЛЫ.*, СЛУЖАЩИЕ.*

FROM ОТДЕЛЫ, СЛУЖАЩИЕ

WHERE ОТДЕЛЫ. № отдела ⁼ СЛУЖАЩИЕ. № отдела

Функции определения данных:

CREATE TABLE - оператор создания таблицы ;

CREATE INDEX - оператор создания индексной таблицы;

DROP TABLE - оператор удаления таблицы ;

DROP INDEX - оператор удаления индексной таблицы.

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

В языке SQL предусмотрено четыре предложения манипулирования данными: SELECT (выдать, выбрать), UPDATE (обновить), DELETE (удалить) и INSERT (включить).

Создадим БД Поставщиков (S), Деталей (Р) и Поставок (SP).

CREATE TABLE S

(НОМЕР_ПОСТАВЩИКА CHAR (5),

ИМЯ CHAR (20),

СОСТОЯНИЕ SMALLINT,

ГОРОД CHAR (15));

CREATE TABLE Р

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

НАЗВАНИЕ_ДЕТАЛИ CHAR (20),

ЦВЕТ CHAR (7),

ВЕС SMALLINT,

Город CHAR (15));

CREATE TABLE SP

(НОМЕР_ПОСТАВЩИКА CHAR (5),

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

КОЛИЧЕСТВО INTEGER);

Содержимое базы данных представлено в табл. 2.1, 2.2, 2.3

Таблица 5.1 Поставщики(Р)

НОМЕР_ПОСТАВЩИКА	ИМЯ	СОСТОЯНИЕ	ГОРОД
S1 S2 S3 S4 S5	Саша Дима Боря Коля Алик	20 10 30 20 30	Липецк Пермь Пермь Липецк Арзамас

Таблица 5.2 Детали (P)

НОМЕР_ДЕТАЛИ	НАЗВАНИЕ	ЦВЕТ	ВЕС	ГОРОД
Р1 Р2 Р3 Р4 Р5 Р6	Гайка Болт Винт Винт Кулачок Блюм	Красный Зеленый Голубой Красный Голубой Красный	12 17 17 14 12 19	Липецк Пермь Рига Липецк Пермь Липецк

Таблица 5.3 Поставки (SP)

НОМЕР_ПОСТАВЩИКА	НОМЕР_ДЕТАЛИ	КОЛИЧЕСТВО
S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4	Р1 Р2 Р3 P4 P5 P6 P1 P2 P2 P2 P4 P5	300 200 400 200 100 100 300 400 200 200 300 400

2.3 Выборки

Основной операцией в языке SQL является отображение, синтаксически представляющее собой блок SELECT - FROM - WHERE (выбрать - из - где).

Структура оператора SELECT:

SELECT [DISTINCT] элементы

FROM таблица

[WHERE <предикат>]

[GROUP BY <поле> [HAVING <предикат>]]

[ORDER BY <поле>]

где: DISTINCT означает, что из таблицы результатов исключается дублирование.

GROUP BY поле - создаются группы, в которых указанное поле имеет одно и то же значение.

HAVING <предикат> - из результата будут исключены некоторые группы.

ORDER BY <поле> - результат будет отсортирован по указанному полю.

Выдать номера и состояния для поставщиков, находящихся в Перми:

SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ

FROM S

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

Результат: НОМЕР ПОСТАВЩИКА СОСТОЯНИЕ

S2 10

S3 30

Простая выборка. Выдать номера для всех поставляемых деталей:

SELECT НОМЕР_ДЕТАЛИ

FROM SP

Результат: НОМЕР ДЕТАЛИ

Р1

Р2

РЗ

Р4

Р5

Р6

Р1

Р2

Р2

P4

Р5

Выборка с исключением дубликатов.

SELECT DISTINCT НОМЕР_ДЕТАЛИ

FROM SP

Результат: НОМЕР ДЕТАЛИ

Р1

Р2

РЗ

Р4

Р5

Р6

SELECT DISTINCT - означает выбрать различные.

Выборка вычисляемых значений. Выдать номер и вес каждой детали:

SELECT НОМЕР_ДЕТАЛИ, ВЕС

FROM P

Результат: НОМЕР ДЕТАЛИ ВЕС

Р1 12

Р2 17

РЗ 17

Р4 14

Р5 12

Рб 19

Фраза SELECT (и фраза WHERE) может включать арифметические выражения, а также прочие имена полей. Можно, кроме того, осуществлять выборку просто констант.

SELECT НОМЕР_ДЕТАЛИ, 'Вес в граммах=', ВЕС

FROM P

Результат: НОМЕР ДЕТАЛИ ВЕС

. Р1 Вес в граммах= 12

Р2 Вес в граммах= 17

РЗ Вес в граммах= 17

Р4 . Вес в граммах= 14

Р5 Вес в граммах= 12

Р6 Вес в граммах= 19

Выдать полные характеристики для всех поставщиков:

SELECT *

FROM S

Результатом будет таблица S со всеми полями.

Ограниченная выборка. Выдать номера поставщиков, которые находятся в Перми и имеют состояние больше, чем 20.

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE ГОРОД='Пермь' AND СОСТОЯНИЕ > 20

Результат: НОМЕР ПОСТАВЩИКА

S3

Условие или предикат, следующий за ключевым словом WHERE, может включать операторы сравнения =, <> (не равно), >, >, >=, , < и <=, булевские операторы AND (и), OR (или) и NOT (нет), а скобки указывают требуемый порядок вычислений.

Выборка с упорядочением. Выдать номера и состояния поставщиков, находящихся в Перми, в порядке убывания их состояния:

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

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

ORDER BY СОСТОЯНИЕ DESC

Результат: НОМЕР ПОСТАВЩИКА СОСТОЯНИЕ

S3 30

S2 10

Параметры:

ASC - возрастание, DECS - убывание, ORDER BY - упорядочить.

При упорядочивании можно указывать номер столбца:

SELECT НОМЕР_ДЕТАЛИ, ВЕС

FROM P

ORDER BY 2, НОМЕР_ДЕТАЛИ

В строке ORDER BY … цифра 2 означает ссылку на второй столбец результирующей таблицы.

Результат: НОМЕР_ДЕТАЛИ ВЕС

Р1 12

Р5 12

Р4 14

Р2 17

РЗ 17

Р6 19

Выборка с использованием BETWEEN (между). Выдать сведения о деталях, вес которых находится в диапазоне от 16 до 19 включительно:

SELECT НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ЦВЕТ, ВЕС, ГОРОД

FROM P

WHERE ВЕС BETWEEN 16 AND 19

Результат: НОМЕР ДЕТАЛИ НАЗВАНИЕ ЦВЕТ ВЕС ГОРОД

Р2 Болт Зеленый 17 Пермь

РЗ Винт Голубой 17 Рига

Р6 Шайба Красный 19 Липецк

Может также использоваться NOT BETWEEN.

Выборка с использованием IN .Выдать детали, вес которых 12,16,17.

SELECT НОМЕР _ДЕТАЛИ, НАЗВАНИЕ, ВЕС, ГОРОД

FROM P

WHERE ВЕС IN (12, 16,17)

Существует также предикат NOT IN (не принадлежит).

Выборка с использованием предиката LIKE (похоже на).Выдать все детали, название которых начинается с буквы В:

SELECT НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ЦВЕТ, ВЕС, ГОРОД

FROM P

WHERE НАЗВАНИЕ LIKE 'B%'

Обычно предикат LIKE имеет форму:

Имя столбца LIKE литерная строковая константа,

где имя столбца имеет тип CHAR.

Литеры:

- Литера " _ " (разрыв или подчеркивание) или " ? " - обозначает любую одиночную литеру.

- Литера " %" (процент) или " * " - обозначает любую последовательность из нескольких литер.

- Все другие литеры обозначают просто сами себя.

Примеры:

АДРЕС LIKE '% Брест %' будет приниматься истина, если АДРЕС содержит строку "Брест".

НОМЕР_ПОСТАВЩИКА LIKE 'S_ _' будет истина, если номер состоит из трех литер и первая из них литера S.

НАЗВАНИЕ LIKE '% К_ _ _' будет истина, если название состоит из четырех и более литер и трем последним из них предшествует литера К.

ГОРОД NOT LIKE '% Е %' будет истина, если значение литера не содержит литеры Е.

2.4 3апросы, использующие соединения

Способность соединять две или более таблицы в одну, представляет собой одну из наиболее мощных средств реляционных систем.

Простое эквисоединение. Выдать все комбинации информации о таких поставщиках и деталях, которые размещены в одном и том же городе: SELECT S.*,P.*

FROM S,P

WHERE S.ГОРОД == Р.ГОРОД

Будут выбраны все строки с одинаковыми городами. Это декартово произведе ние таблиц.

Варианты:

- WHERE S.ГОРОД Б<> Р.ГОРОД

- WHERE S.ГОРОД = Р.ГОРОД

AND S.СОСТОЯНИЕ = 20

При соединении необязательно чтобы было равенство. В случае равенства соединение называется эквисоединением.

В SELECT могут быть указаны выборочные поля, а не все. Выражение:

SELECT S.*, Р.* может быть записано: SELECT *

FROM S,P FROM S,Р

Но может быть записано и с указанием всех полей.

Так как при соединении возникают два одинаковых столбца (S.Город и Р.Город), то один столбец исключается и такое соединение называется естественным. Могут быть соединения и 3, 4 или любого числа таблиц.

Соединение с дополнительным условием. Выдать все комбинации информации о поставщиках и деталях, такие, что рассматриваемые поставщики и детали “соразмещены”. Опустить при этом поставщиков с состоянием 20:

SELECT *

FROM S, Р

WHERE S.ГОРОД = Р.ГОРОД

AND S.СОСТОЯНИЕ <>

Результат: НОМЕР_ПОСТАВЩИКА ИМЯ СОСТОЯНИЕ S.ГОРОД

S2 Дима 10 Пермь

S2 Дима 10 Пермь

S3 Боря 30 Пермь

S3 Боря 30 Пермь

НОМЕР_ДЕТАЛИ НАЗВАНИЕ ЦВЕТ ВЕС Р.ГОРОД

Р2 Болт Зеленый 17 Пермь

Р5 Кулачок Голубой 12 Пермь

Р2 Болт Зеленый 17 Пермь

Р5 Кулачок Голубой 12 Пермь

2.5 Подзапросы

Подзапросы представляют собой вложенные предложения SELECT. Именно такая возможность позволила назвать язык SQL структурированным.

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

SELECT ИМЯ

FROM S

WHERE НОМЕР_ПОСТАВЩИКА IN

(SELECT НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE НОМЕР_ДЕТАЛИ = `P2');

Результат: ИМЯ

Саша

Дима

Боря

Коля

Этот подзапрос возвращает множество поставщиков, поставляющих деталь Р2:

S1, S2, S3, S4. Поэтому первоначальный запрос эквивалентен простому запросу :

SELECT ФАМИЛИЯ

FROM S

WHERE НОМЕР_ПОСТАВЩИКА IN (`S1','S2','S3','S4')

Номер поставщика здесь задан неявно. Для явного задания необходимо указать имя таблицы .

SELECT S.ИМЯ

FROM S

WHERE S.НОМЕР_ПОСТАВЩИКА IN

(SELECT SP.НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE SP.HOMEP_ДЕТАЛИ='P2')

Этот же подзапрос может быть выражен и соединением:

SELECT S.ИМЯ

FROM S, SP

WHERE S.HOМЕР_ПОСТАВЩИКА = P.HOMEP_ПОСТАВЩИКА

AND SP.HOMEP_ДЕТАЛИ = 'P2'

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

Выдать имена поставщиков, поставляющих красные детали:

SELECТ ИМЯ

FROM S

WHERE НО МЕР_ПОСТАВЩИКА IN

(SELECT НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE НОМЕР_ДЕТАЛИ IN

(SELECT НОМЕР_ДЕТАЛИ

FROM P

WHERE ЦВЕТ = 'Красный'))

Результат: ИМЯ

Саша

Дима

Коля

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

Выдать имена поставщиков, которые поставляют деталь P2:

SELECT ФАМИЛИЯ

FROM S

WHERE `P2' IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

В данном случае в предикате подзапроса

WHERE НОМЕР_ПОСТАВЩИКА = S. НОМЕР_ПОСТАВЩИКА задано не какое-то конкретное значение номера поставщика, а название поля S. НОМЕР_ПОСТАВЩИКА и алгоритм работы следующий.

Система проверяет первую строку таблицы S. Предположим, что это строка поставщика "S1". Тогда переменная S.HOMEP_ПОСТАВЩИКА в данный момент имеет значение "S1", и система обрабатывает внутренний запрос:

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

Далее система будет повторять обработку такого рода для следующего поставщика и т. д., пока не будут рассмотрены все строки таблицы S.

Такой подзапрос, как в этом примере, называется коррелированным. Для того чтобы сделать более ясной связь коррелированных подзапросов с внешними запросами, иногда вводят псевдонимы.

Например:

SELECT SX.ИМЯ

FROM S SX

WHERE 'P2' IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

Здесь псевдонимом является имя SX, введенное во фразе FROM как альтернативное имя таблицы S, т. е.

- SX - это переменная, областью определения которой является множество записей таблицы S.

Поочередно для каждого возможного значения SX выполняется следующее:

1) вычисляется подзапрос и получается множество номеров деталей Р;

2) добавляется к результирующему множеству значение SX. ИМЯ, если

только P2 принадлежит множеству Р.

Некоторые считают более ясным использование двух различных символов для того, чтобы различать эти две различные функции.

Использование одной и той же таблицы в подзапросе и внешнем запросе. Выдать номера поставщиков, которые поставляют по крайней мере одну деталь, поставляемую поставщиком S2:

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE НОМЕР_ДЕТАЛИ IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

Результат: НОМЕР ПОСТАВЩИКА

S1

S2

S3

S4

Решение этой задачи с использованием псевдонимов:

SELECT DISTINCT SPX.HOMEP_ПОСТАВЩИКА

FROM SP SPX

WHERE SPX.HOMEP_ДЕТАЛИ IN

(SELECT SPY.HOMEP_ДЕТАЛИ

FROM SP SPY

WHERE SPY.HOMEP_ПОСТАВЩИКА = ' S2')

Эквивалентный запрос с использованием соединения имеет вид:

SELECT DISTINCT SPX.HOMEP_ПОСТАВЩИКА

FROM SP SPX, SP SPY

WHERE SPX.HOMEP_ДЕТАЛИ=SPY.HOMEP_ДЕТАЛИ

AND SPY.HOMEP_ПОСТАВЩИКА = ' S2'

Случай, когда в коррелированном и внешнем запросе используется одна и та же таблица. Выдать номера всех деталей, поставляемых более чем одним поставщиком:

SELECT DISTINCT SPX.HOMEP_ДЕТАЛИ

FROM SP SPX

WHERE SPX.HOMEP_ДЕТАЛИ IN

(SELECT SPY.HOMEP_ДЕТАЛИ

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА<>SPX.НОМЕР_ПОСТАВЩИКА)

Результат: НОМЕР ДЕТАЛИ

Р1

P2

P4

P5

Подзапрос с оператором сравнения, отличным от IN. Выдать номера поставщиков, находящихся в том же городе, что и поставщик S 1:

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE ГОРОД=

(SELECT ГОРОД

FROM S

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

Результат: НОМЕР ПОСТАВЩИКА

Sl

S4

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

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

SELECT ИМЯ

FROM S

WHERE EXISTS

(SELECT *

FROM SP

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

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

Квантор существования EXISTS (SELECT* FROM… WHERE….) принимает значение истина, если подзапрос выдает непустое множество и наоборот.

Запрос, использующий NOT EXISTS. Выдать имена поставщиков, которые не поставляют деталь Р2:

SELECT ИМЯ

FROM S

WHERE NOT EXISTS

(SELECT *

FROM SP

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

NOT EXISTS (SELECT* FROM… WHERE….)

принимает значение истина, если подзапрос выдает пустое множество и наоборот.

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

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

Выдать общее количество поставщиков:

SELECT СOUNT (*)

FROM S

Результат: 5

Выдать общее количество поставляемых деталей P2:

SELECT SUМ (Количество)

FROM SP

WHERE НОМЕР_ДЕТАЛИ = `P2'

Результат: 1000

Функция в подзапросе. Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем текущее максимальное состояние в таблице S:

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE СОСТОЯНИЕ <

(SELECT MAX (СОСТОЯНИЕ)

FROM S)

Результат: НОМЕР ПОСТАВЩИКА

S1

S2

S3

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

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

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

FROM SP

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

Результат: НОМЕР ДЕТАЛИ

Р1 600

Р2 1000

РЗ 400

Р4 500

Р5 500

Р6 100

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

SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

HAVING COUNT (*) > 1

Результат: НОМЕР ДЕТАЛИ

Р1

Р2

Р4

Р5

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

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

SELECT НОМЕР_ДЕТАЛИ

FROM P

WHERE ВЕС > 16

UNION

SELECT НОМЕР_ДЕТАЛИ

FROM SP

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

Результат: НОМЕР ДЕТАЛИ

Р1

Р2

РЗ

Р6

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

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

UPDATE таблица

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

[WHERE предикат]

Обновление одной записи. Изменить цвет детали Р2 на желтый, увеличить ее вес на 5 и установить значение города "неизвестный" (NULL):

UPDATE P

SET ЦВЕТ = 'Желтый',

ВЕС = ВЕС + 5,

ГОРОД = NULL

WHERE НОМЕР_ДЕТАЛИ = 'Р2'

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

UPDATE S

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

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

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

UPDATE SP

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

WHERE 'Пермь' =

(SELECT ГОРОД

FROM S

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

Обновление нескольких таблиц. Изменить номер поставщика 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'

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

DELETE

FROM S

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

Удалить все поставки:

DELETE

FROM SP

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

DELETE

FROM SP

WHERE `Рига'=

(SELECT ГОРОД

FROM S

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

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

INSERT

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

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

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

Вставка единственной записи.

INSERT

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

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

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

INSERT

INTO P

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

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

CREATE TABLE ВРЕМЕННАЯ

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

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

INSERT

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

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

FROM SP

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

Здесь предложение SELECT выполняется точно так же, как обычно, но результат не возвращается пользователю, а копируется в таблицу ВРЕМЕННАЯ.

2.13 Встроенный язык 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

Размещено на Allbest.ru

...