Московский государственный университет печати

Чертовской В.Д.


         

Базы и банки данных

Учебное пособие


Чертовской В.Д.
Базы и банки данных
Начало
Печатный оригинал
Об электронном издании
Оглавление

Введение

Часть 1. ОСНОВНЫЕ ПОЛОЖЕНИЯ

Раздел 1. Основные понятия

1.

Глава 1. Общие сведения

1.1.

Данные, информация, знания

1.2.

Основные понятия и определения

1.3.

Классификация БД и СУБД

1.4.

Состав СУБД и работа БД

2.

Глава 2. Концепция баз данных

2.1.

Требования, предъявляемые к базам данных

2.2.

Концепция построения БД

2.3.

Методология проектирования баз данных

2.4.

Методология использования баз данных

Раздел 2. Теория баз данных

3.

Глава 3. Общая теория

3.1.

Модели представления данных

3.2.

CASE-технология

3.3.

CASE-средства

4.

Глава 4. Теория реляционных баз данных

4.1.

Математические основы теории

4.2.

Построение БД

4.3.

Использование БД

4.3.1.

Запросы к данным

4.3.2.

Синхронизация процессов доступа

Часть 2. Централизованные базы данных

Раздел 3. Реализация бд (модели БД)

5.

Глава 5. Реляционные БД SQL

5.1.

Логическая структура

5.2.

Создание БД

5.3.

Использование БД

5.3.1.

Язык SQL

5.3.2.

Язык QBE

6.

Глава 6. Сетевые БД

6.1.

Логическая структура

6.2.

Программная реализация

6.2.1.

Создание БД (ЯОД)

6.2.2.

Использование БД (ЯМД)

7.

Глава 7. Иерархические БД

7.1.

Логическая структура

7.2.

Программная реализация

7.2.1.

Создание БД (ЯОД)

7.2.2.

Использование БД (ЯМД)

8.

Глава 8. Взаимосвязь МД

8.1.

Сравнительная характеристика моделей данных

8.2.

Преобразование моделей данных

8.3.

Выбор моделей данных

9.

Глава 9. Физическая БД

9.1.

Вопросы программной реализации БД

9.2.

Организация хранения и доступ

9.3.

Доступ к данным и их обновление

Раздел 4. Современные направления развития БД

10.

Глава 10. Автоматизация проектирования

10.1.

Классический подход к проектированию

10.1.1.

Однопользовательский режим

10.1.2.

Многопользовательский режим

10.2.

Современный подход к проектированию

10.3.

Автоматизация проектирования

11.

Глава 11. Объектно-ориентированные базы данных

11.1.

Недостатки реляционных баз данных

11.2.

Состояние развития ООБД

11.3.

Сущность ООБД

11.4.

Недостатки и перспективы развития ООБД

Часть 3. Распределенные базы данных (РБД)

Раздел 5. Основы теории РБД

12.

Глава 12. Общая характеристика РБД

12.1.

Новые требования, предъявляемые к БД

12.2.

Состав и работа РБД

12.3.

Система клиент/сервер

Раздел 6. Основы теории РБД

13.

Глава 13. Создание РБД

13.1.

Обеспечение целостности

13.2.

Фрагментация и локализация

13.3.

Процесс интеграции

13.4.

Преобразование структуры и данных

13.5.

Однородные и неоднородные РБД

14.

Глава 14. Использование РБД

14.1.

Одновременный доступ

14.2.

Защита

14.3.

Восстановление РБД

14.4.

Запросы

Заключение

Контрольные вопросы

Литература

Указатели
11  именной указатель
360  предметный указатель
163  указатель иллюстраций
25  указатель компаний

Часть 2. Централизованные базы данных

Раздел 3. Реализация бд (модели БД)

5.
Глава 5. Реляционные БД SQL

5.1.
Логическая структура

База данных реляционнаяРеляционные базы данных получили широкое распространение в персональных компьютерах. Наиболее известны такие Система управления базы данныхСУБД, как dBASE, Paradox и особенно FoxPro. СУБД Oracle, Sybase, Informix, BTrieve, Ingress, InterBase были изначально предназначены для работы в сети с большими объемами данных.

В основе реляционной модели лежит математическое понятие теоретико-множественного отношения, которое представляет собой подмножество декартова произведения списка доменов.

ДоменДомен - множество значений (например, множество целых чисел). Декартовым произведением доменов D1 , D2 , .., Dk (обозначается как D1 ×D2 × ...×Dk ) называется множество всех кортежей (V1 , V2 , ..., Vk ) длины k, таких, что Vi Di , i = 1, I. Например, если k = 2, D1 = {0,1} и D2 = {a,b,c}, то D1 ×D2 есть {(0, a), (0, b), (0, c), (1, a), (1, b),(1, c)}, а отношением может быть, например, {(0, a), (0, c), (1, b)}.

Элементы отношения называются Кортежкортежами и имеют арность k (степень отношения), причем i-м компонентом является Vi . Отношение удобно представлять таблицей - совокупностью всех кортежей: каждая строка есть кортеж и каждый столбец соответствует одному компоненту. Кортежи обычно нумеруются и их количество определяет размерность таблицы. Столбцы называются атрибутами, и им часто присваиваются имена. Упорядоченный список имен атрибутов отношения называется схемой отношения. Если отношение называется ИГРОКИ и его схема имеет атрибуты A1 , A2 , ..., Ak , то такую схему будем записывать как ИГРОКИ (A1 , A2 , ..., Ak ).

Совокупность схем отношений называется Схема реляционная базы данныхсхемой (реляционной) БД, а текущие значения соответствующих отношений - (реляционной) БД.

Данные из диаграммы объектов-связей представляются двумя видами отношений.

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

  2. Связь между наборами объектов E1 , E2 , ..., Ek представляется отношением, схема которого состоит из атрибутов ключей каждого из этих наборов.

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

Для реляционных отношений характерны следующие особенности.

  1. Любой тип записи содержит только простые (по структуре) элементы данных.

  2. Порядок кортежей в таблице несуществен.

  3. Упорядочение значащих атрибутов в кортеже должно соответствовать упорядочению атрибутов в реляционном отношении.

  4. Любое отношение должно содержать один атрибут или более, которые вместе составляют уникальный первичный ключ.

  5. Если между двумя реляционными отношениями существует зависимость, то одно отношение является исходным, второе -подчиненным.

  6. Чтобы между двумя реляционными отношениями существовала зависимость, атрибут, служащие первичным ключом в исходном отношении, должны также присутствовать в подчиненном отношении.

Пример 5.1. Представим БД «Учебный процесс»в виде реляционной модели (табл. 5.1).

Таблица 5.1.

а) Отношение ГРУППА

Индекс
ИГ
Название группы
НГ
Количество ответов
КОЛ
Проходной балл
ПБАЛЛ
1
2
3
А1
А2
А3
16
28
18
4,3
4,0
4,3

б) Отношение СТУДЕНТ

Номер зачетной книжки
НЗ
ИГ Фамилия, и,о
СФИО
Год рождения
ГР
Проходной балл
ПБ
А-1746
А-1747
А-1748
А1
А1
А1
Серов А.П.
Киров П.Г.
Сухов П.Н.
1979
1980
1981
4,3
4,0
4,2

в) Отношение КАФЕДРА

Код кафедры
ККАФ
Название кафедры
НКАФ
Телефон
ТЕЛ
Фамилия, и, о. зав
ФЗАВ
К1
К2
К3
ИиУС
АПП
ТПП
154-12-80
171-12-85
212-10-81
Сорокин П.В.
Борисов Б.В.
Степанов И.В.

г) Отношение ПРЕПОДАВАТЕЛЬ

Табельный №
ТБН
Фамилия, и, о
ПФИО
Ученая степень
СТ
Ученое звание
ЗВ
ККАФ
381
401
402
Шаталов А.С.
Сидоров А.Т.
Тараканов П.Т.
д.т.н.
к.т.н.
к.ф.-м. н.
профессор
доцент
доцент
К1
К1
К1

д) Отношение ПРЕДМЕТ

Код предмета
КП
Название предмета
НП
Всего часов
ВЧ
Лекции
ЛЧ
Практ.-лаб. занятия
ПЧ
Семинаров
СЕМ
П1
П2
П3
Информатика
Кибернетика
Математика
350
300
600
130
120
200
К1
К1
К1
2
3
4

е) Отношение ИЗУЧЕНИЕ

Индекс гр.
ИГ
Код предмета
КП
Таб. №

ТБН

Вид занятий
ВЗ
Часы
ЧАС
2
2
1
1
П2
П2
П3
П3
402
381
381
401
практич.
лекции
лекции
практич.
180
120
200
400

ж) Отношение УСПЕВАЕМОСТЬ

Индекс
ИГ
Номер зачетной книжки
НЗ
Код предмета
КП
Таб. №
ТБН
Вид занятий
ВЗ
Оценка
ОЦ
1
1
1
А-1746
А-1747
А-1748
П3
П3
П3
381
381
381
экзамен
экзамен
экзамен
5
4
3
Далее отношения (например, табл. 5.1, а) будем записывать и в другой форме

ГРУППА(Индекс, Название, Количество_студентов, Проходной_балл),

где подчеркнут ключ.

Процедуры создания и использования реляционных БД основываются на теории реляционных БД, подробно рассмотренной в главе 4, результаты которой используем здесь в прикладных целях. При введении структуры данных используют соответствующие форматы данных. Для таблицы ПРЕПОДАВАТЕЛЬ они представлены в табл. 5.2.

Таблица 5.2.

Форматы отношения ПРЕПОДАВАТЕЛЬ

Имя поля Ключ кн. Уникальное поле Обязательное поле Тип данных
ВЗ
Размер Подписи поля
ТБН
ПФИО
СТ
ЗВ
ККАФ
*



+
да



да
да
нет
нет
да
Числовой
Текстовой
Текстовой
Текстовой
Текстовой
Целое
30
12
12
6
Таб. №
ПФИО
Уч. ст.
Уч. зв.
Код каф.
Вся БД (табл. 5.1) представлена в 4НФ, поэтому отразим схему связей между ее отношениями (рис. 5.1),Рис. 05.01. Схема связей БД 'Учебный процесс' где символами *, +, x помечены первичные, внешние и составные ключи соответственно.

5.2.
Создание БД

Основная задача при проектировании База данных реляционнаяреляционных БД - формирование оптимальных отношений.

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

Нормализация осуществляется последовательно с использованием пяти нормальных форм, включая форму Бойса-Кодда.

5.3.
Использование БД

На этом «бумажное»построение БД заканчивается. Компьютерная реализация База данныхБД определяется языками описания Язык описания данных(ЯОД) и манипулирования Язык манипулирования данными(ЯМД) данными.

Они могут базироваться на Алгебра реляционнаяреляционной алгебре (процедурные языки) и Исчисление реляционноереляционном исчислении Кортежкортежей и Домендоменов (Язык декларативныйдекларативные языки). На исчислении кортежей основан язык SQL (§ 5.3.1), на исчислении доменов - язык QBE (§ 5.3.2).

5.3.1.
Язык SQL

Для SQL имеется много вариантов и диалектов. Здесь изложим основные положения базового варианта: более подробное описание языка приведено в ссылка на источники литературы.

Иллюстрацию языка SQL проведем на примере базы данных, представленной в табл. 5.3 - 5.5.

Таблица 5.3.

ПРОДАВЦЫ

ПНОМ ПИМЯ город КОММ
1001
1002
1007
1004
1003
Строков
Кирюшин
Аврорин
Удалов
Козлов
Москва
Пермь
Москва
Курск
Орел
.12
.11
.13
.15
.10

Названия полей:

ПНОМ - уникальный нмер, назначенный каждому продавцу (номер служащего);
ПИМЯ - имя продавца;
город - расположение продавца (город);
КОММ - комиссионные продавцов в десятичной форме

Таблица 5.4.

ЗАКАЗЧИКИ

ЗНОМ ЗИМЯ город рейтинг ПНОМ
3001
3002
3003
3004
3006
3008
3007
Иванов
Петров
Ковров
Сидоров
Крабов
Конкин
Красин
Москва
С.-Петербург
Сочи
Кириши
Русса
Пермь
Луга
100
300
200
300
100
300
100
1001
1003
1002
1003
1001
1007
1004

Названия полей:

ЗНОМ - уникальный номер, назначенный каждому заказчику;
ЗИМЯ - имя заказчика;
город - расположение заказчика (город);
рейтинг - код, указывающий уровень предпочтения данного заказчика перед другими: более высокий номер указывает на большее предпочтение (рейтинг);
ПНОМ - номер продавца, назначенному этому заказчику (из таблицы Продавцов)

Таблица 5.5.

ЗАКАЗЫ

ПРНОМ СУМПР ДАТПР ЗНОМ ПНОМ
3001
3003
3002
3005
3006
3009
3007
3008
3010
3011
18.69
767.19
1900.10
5160.46
1098.16
1713.23
75.75
4783.00
1309.95
9198.88
10/03/1990
10/03/1990
10/03/1990
10/03/1990
10/03/1990
10/04/1990
10/04/1990
10/05/1990
10/06/1990
10/06/1990
3008
3001
3007
3003
3008
3002
3004
3006
3004
3006
1007
1001
1004
1003
1007
1003
1002
1001
1002
1001

Названия полей:

ПРНОМ - уникальный номер, данный каждому приобретению;
СУМПР - значение суммы приобретений;
ДАТПР - дата приобретения;
ЗНОМ - номер заказчика, делающего приобретение (из таблицы Заказчиков);
ПНОМ - номер продавца, продающего приобретения (из таблицы Продавцов).

Для нее схема Access-связей показана на рис. 5.2.Рис. 05.02. Схема связей

В языке SQL возможно выделить три основные группы операций: создание (CREATE), обновление (INSERT, UPDATE, DELETE), запрос (SELECT). Они имеют следующие стандарты, в которых приняты обозначения: | - все, что предшествует символу, можно заменить тем, что следует за ним; {} - единое целое для применения символа; [] - необязательное выражение; ... - повторяется произвольное число раз ; .,... - повторяется произвольное число раз, но любое вхождение отделяется запятой.

CREATE TABLE <имя таблицы>

({<имя столбца><тип данных> [размер]

[<тип столбца> ...]}.,...);

[<тип таблицы>].,..); (5.1)

Типы данных могут быть INTEGER, CHARACTER, DECIMAL, NUMERIC, SMALLINT, FLOAT, REAL, PRECISION, LONG, VARCHAR, DATE, TIME. Четыре последние типа не входят в стандарт SQL, но им могут поддерживаться.

Тип столбца (и тип таблиц) может быть UNIQUE, PRIMARY KEY, CHECK <предикат>, DEFAULT=<список полей>, REFERENCE <имя таблицы> [(<имя столбца>.,...)].

INSERT INTO <имя таблицы>[(<имя столбца>.,...)] (5.2)

{VALUES(<список полей>).,...}

DELETE <имя предиката> (5.3)

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

|WHERE CURRENT OF <имя курсора>

(*только вложенный*)];

SELECT * |[{DISTINCT|ALL]<список полей> (5.4)

FROM <имя таблицы> [<алиас>]} ...

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

[GROUP BY {<имя столбца>|<целое>}.,...]}

[HAVING <предикат>] [ORDER BY {<имя столбца>}]|<целое>}...]

[{UNION}];

Выделяют две разновидности языка SQL: интерактивный и вложенный.

Язык SQL интерактивныйИнтерактивный SQL используется для функционирования непосредственно в базе данных, чтобы производить вывод для использования его заказчиком.

Язык SQL вложенныйВложенный SQL состоит из команд SQL, помещенных внутри программ, которые обычно написаны на некотором другом языке (типа КОБОЛА или Паскаля). Это делает эти программы более мощными и эффективными.

Будет рассматривать преимущественно - при отсутствии упоминаний - интерактивный язык.

В нем возможно выделить:

DDL (Язык Описания Данных) - это язык описания схемы, и в ANSI он состоит из команд, создающих объекты (таблицы, индексы, виды) в базе данных;

DML (Язык Манипулирования Данными) - это набор команд, определяющих, какие значения представлены в таблицах в любой момент времени;

DCD (Язык Управления Данными) состоит из средств, которые определяют разрешение пользователю выполнять определенные действия.

По своей сути язык SQL является специфическим декларативным языком запроса, в связи с чем наибольшее число комбинаций имеет место для процедуры SELECT. Однако более удобно расположить команды по технологическому циклу работы с БД:

    1) создание БД - структуры таблиц, заполнение БД данными, создание видов, обеспечение целостности, система доступа (разрешений), словарь данных, многопользовательский режим;

    2) использование БД - запрос в различных формах (в том числе с обновлением).

Отдельно рассмотрим основы Язык SQL вложенныйвложенного языка SQL.

Создание БД

Структура таблиц.

Таблицы («пустые») создаются командой CREATE TABLE (выражение (5.1)).

CREATE TABLE Продавцы

(пном integer,

пимя char (10), (5.5)

город char (10),

комм decimal);

Команда CREATE TABLE в основном определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных и размеры столбцов. В SQL возможно задание следующих типов данных: integer, character, numeric, decimal, smallint, float, real, double, precision, long, varchar, date, time.

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

Изменение таблицы после ее создания осуществляется командой ALTER TABLE. Обычно она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Чтобы добавить столбец к таблице, используют формат:

ALTER TABLE <имя таблицы> ADD <имя поля>

<тип данных><размер>;

Столбец будет добавлен последним и со значением NULL для всех строк таблицы.

Удаление производится по команде

DROP TABLE <имя таблицы>;

Надо быть собственником (т.е. создателем) таблицы, чтобы иметь возможность удалить ее.

Аналогично создаются и удаляются индексы.

Базовая таблица (5.5)

Таблица 5.5.

ЗАКАЗЫ

ПРНОМ СУМПР ДАТПР ЗНОМ ПНОМ
3001
3003
3002
3005
3006
3009
3007
3008
3010
3011
18.69
767.19
1900.10
5160.46
1098.16
1713.23
75.75
4783.00
1309.95
9198.88
10/03/1990
10/03/1990
10/03/1990
10/03/1990
10/03/1990
10/04/1990
10/04/1990
10/05/1990
10/06/1990
10/06/1990
3008
3001
3007
3003
3008
3002
3004
3006
3004
3006
1007
1001
1004
1003
1007
1003
1002
1001
1002
1001

Названия полей:

ПРНОМ - уникальный номер, данный каждому приобретению;
СУМПР - значение суммы приобретений;
ДАТПР - дата приобретения;
ЗНОМ - номер заказчика, делающего приобретение (из таблицы Заказчиков);
ПНОМ - номер продавца, продающего приобретения (из таблицы Продавцов).

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

Структура и содержание видов. Только что созданная таблица называется базовой. Можно создавать представление (вид, View) - таблицы, содержимое которых берется или выводится из других таблиц. Вид создается командой CREATE VIEW:

CREATE VIEW Москва1

AS SELECT *

FROM Продавцы

WHERE город = "Москва";

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

Большое количество типов представлений доступно только для чтения. Это означает, что их можно запрашивать, но они не могут подвергаться действиям команд модификации.

Имеются некоторые виды запросов, которые недопустимы в определениях представлений: одиночное представление должно основываться на одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNION ALL), агрегатные функции, DISTINCT в определении, вычисляемые поля не разрешаются в работе с представлениями; упорядочение (ORDER BY) никогда не используется в определении представлений.

Удаление представлений осуществляется (его владельцем) командой

DROP VIEW <имя вида>.

Заполнение БД данными. Значения могут быть помещены и удалены из полей командами языка DML (Язык Манипулирования Данными - ЯМД) INSERT (ВСТАВИТЬ), DELETE (УДАЛИТЬ) - выражения (5.2) и (5.3). Так, например, чтобы ввести строку в таблицу Продавцов, можно использовать следующее условие:

INSERT INTO Продавцы

VALUES (1001, «Строков», «Москва», .12);

Можно вставлять и пустое значение (NULL).

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

INSERT INTO Заказчики (город, имя, номер)

VALUES («Москва», «Иванов», 2001);

Отметим, что столбцы «рейтинг» и «пном» - отсутствуют: эти строки автоматически установлены в значение - по умолчанию.

Исключение строк, введенных по ошибке, проводится командой DELETE. Она может удалять только введенные строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным. Чтобы удалить все содержание таблицы Продавцов, надо ввести:

DELETE FROM Продавцы;

Теперь таблица пуста и ее можно окончательно удалить командой DROP TABLE. Обычно нужно удалить только некоторые определенные строки из таблицы, для чего используется предикат. Например, чтобы удалить данные продавца Козлова из таблицы Продавцы, можно ввести

DELETE FROM Продавцы

WHERE пном = 1003;

Команды INSERT, DELETE совместно с командой UPDATE используются в процедуре обновления при эксплуатации БД.

Задание (обеспечение) целостности. Это разновидность команды CREATE TABLE, позволяющая устанавливать ограничения в таблицах.

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

CREATE TABLE <имя таблицы>

(<имя столбца><тип данных><ограничение столбца>,

(<имя столбца><тип данных><ограничение столбца>,

<ограничение таблицы> ( <имя столбца>

[, <имя столбца> ]...);

Перечислим некоторые ограничения.

  1. Исключение пустых (NULL) указателей введением команды NOT NULL.

  2. Уникальность данных и первичные ключи.

    Ограничение столбца UNIQUE в поле при создании таблицы отклонит любую попытку ввода в это поле для одной из строк значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL).

  3. SQL поддерживает первичные ключи непосредственно с ограничением Первичный Ключ (PRIMARY KEY).Первичные ключи не могут иметь значений NULL. Это означает, что, подобно полям в ограничении UNIQUE, любое поле используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL.

  4. Ограничения на значения полей. Для этого используется ограничение CHECK: чтобы предотвратить ошибку неправильного введения значения комм, наложим ограничение столбца - CHECK (комм меньше, чем 1).

    Сказанное может быть представлено в виде

    CREATE TABLE Продавцы

    (пном integer NOT NULL PRIMARY KEY,

    пимя char(10) NOT NULL UNIQUE,

    город char(10),

    комм decimal CHECK (comm < 1));

    Могут быть заданы интервалы и множества значений ограничений.

    Создадим предварительно таблицу Заказы:

    CREATE TABLE Orders

    (прном integer NOT NULL UNIQUE,

    сумпр decimal,

    датпр date NOT NULL,

    зном integer NOT NULL,

    пном integer NOT NULL);

  5. Установка значений по умолчанию.

    Значение DEFAULT (ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца.

    Если офис - в Орле и подавляющее большинство продавцов тоже живут в в Орле, то по умолчанию:

    CREATE TABLE Продавцы

    (пном integer NOT NULL UNIQUE,

    пимя char(10) NOT NULL UNIQUE,

    город char(10) DEFAULT = «Орел»,

    комм decimal CHECK (comm < 1);

  6. Ограничения на внешний ключ (ссылочная целостность). SQL поддерживает ссылочную целостность в команде CREATE TABLE (или ALTER TABLE) ограничением FOREIGN KEY с синтаксисом

    FOREIGN KEY <список полей> REFERENCES

    <таблица с родительским ключом> [ <список полей>.

Создадим таблицу Заказчиков с полем пном, определенным в качестве внешнего ключа, ссылающегося на таблицу Продавцов:

CREATE TABLE Заказчики

(зном integer NOT NULL PRIMARY KEY

зимя char(10), город char(10),

пном integer,

FOREIGN KEY (пном) REFERENCES Продавцы

(пном);

Отметим, что родительский ключ должен быть уникальным и не содержать никаких пустых значений (NULL). Этого недостаточно для родительского ключа при объявлении внешнего ключа. SQL должен быть уверен что двойные значения или пустые значения (NULL) не были введены в родительский ключ, т.е.

CREATE TABLE Продавцы (пном integer NOT NULL PRIMARY KEY, пимя char(10) NOT NULL, город char(10),Дкомм decimal);

CREATE TABLE ЗаказчикиД( зном integer NOT NULL PRIMARY KEY, зимя char(10) NOT NULL, город char(10),Дпном integer рейтинг integer

FOREIGN (пном) REFERENCES Продавцы,

UNIQUE (зном, пном);

CREATE TABLE ЗаказыД(прном integer NOT NULL PRIMARY KEY,

сумпр decimal,

датпр date NOT NULL,

зном integer NOT NULL

пном integer NOT NULL

FOREIGN KEY (зном, пном) REFERENCES

CUSTOMERS (зном, пном);

Таким образом созданы все три таблицы и установлены связи между ними.

Если необходимо изменить или удалить текущее ссылочное значение родительского ключа, имеется три возможности:

    1) ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены (RESTRICTED);

    2) можно сделать изменение в родительском ключе и тем самым автоматические изменения во внешнем ключе, т.е. каскадное изменение (CASCADES);

    3) провести изменение в родительском ключе и установить автоматически внешний ключ в NULL (полагая, что NULLS разрешен во внешнем ключе) - пустое изменение внешнего ключа (NULL).

Например, обновление и уничтожение

CREATE TABLE Заказчики

(зном integer NOT NULL PRIMARY KEY,

зимя char(10) NOT NULL,

город char(10),

рейтинг integer,

пном integer REFERENCES Продавцы,

UPDATE OF CASCADES,

DELETE OF Продавцы RESTRICTED);

Если теперь попробовать удалить Строков из таблицы Продавцов, команда будет недопустима, пока не будет изменено значение поля пном Заказчиков и Иванов и Крабов - для другого назначенного продавца.

В то же время можно изменить значение поля пном для Строкова, а 1009 Иванов и Крабов будут также автоматически изменены.

Возможны и NULL (пустые)-изменения. Например,

CREATE TABLE Заказы

прном integer NOT NULL PRIMARY KEY,

сумпр decimal,

датпр date NOT NULL,

зном integer NOT NULL REFERENCES Заказчики,

пном integer REFERENCES Продавцы,

UPDATE OF Заказчики CASCADES,

DELETE OF Заказчики CASCADES,

UPDATE OF Продавцы CASCADES,

DELETE OF Продавцы NULLS);

Естественно, что в команде DELETE с эффектом NULL в таблице Продавцов ограничение NOT NULL должно быть удалено из поля пном.

Система разрешений. Возможна система разрешения (привилегий) или запрета доступа к данным.

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

ПривилегииПривилегии - это то, что определяет, может ли указанный пользователь выполнить данную команду.

Рассмотрим основные принципы ее построения на базе Язык SQLязыка SQL.

Различают следующие приоритеты (по убыванию): роль, пользователь, группа, общий доступ (public).

Имеются разрешающие и запрещающие действия.

Разрешение дается оператором вида

GRANT <вид операции>

ON <объект>

TO <субъект>

[WITH GRANT OPTION].

Последняя строка говорит о передаче права пользования.

Для уверенного управления необходимо сочетание Администратор базы данныхадминистратора БД и владельца объекта (например, таблицы).

Создание пользователя Илья (которому предоставляется возможность создания баз данных), выполняемое администратором БД, определяется командой

CREATE USER Илья

WITH PRIVILEGES create_db;

Лишение этого пользователя привилегий осуществляется командой

ALTER USER Илья create_db;

или

DROP USER Илья;

Создание роли (задание пароля) проводится командой

CREATE ROLE create_db WITH PASSWORD="12";

а лишение роли -

ALTER ROLE create_db;

или

DROP ROLE create_db;

Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL : GRANT (ДОПУСК) и REVOKE (ОТМЕНА).

Каждый пользователь в среде SQL имеет специальное идентификационное имя (идентификатор - ID) или номер.

ID разрешения - это имя пользователя, и SQL может использовать специальное ключевое слово USER, которое ссылается на идентификатор доступа, связанный с текущей командой. Команда интерпретируется и разрешается (или запрещается).

Привилегии объекта связаны одновременно и с пользователями, и с таблицами. Следует помнить, что пользователь, создавший таблицу (любого вида), является владельцем этой таблицы: он имеет все привилегии в этой таблице и может передавать эти привилегии другим пользователям.

Привилегии относятся к командам SELECT, INSERT, UPDATE, DELETE, REFERENCES (определение внешнего ключа, который использует один или более столбцов этой таблицы как родительский ключ).

К нестандартным командам относятся INDEX (ИНДЕКС), дающий право создавать индекс в таблице, SYNONYM (СИНОНИМ), позволяющий создавать синоним для объекта, и ALTER (ИЗМЕНИТЬ) - для выполнения команды ALTER TABLE в таблице. Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT.

Например, пользователь Илья имеет таблицу Заказчиков и хочет позволить пользователю Петр выполнить запрос к ней:

GRANT SELECT ON Заказчики TO Петр;

Петр может выполнить запросы к таблице Заказчиков, но не может предоставить право SELECT другому пользователю: таблица еще принадлежит Илье.

Возможны и групповые привилегии:

GRANT SELECT, INSERT ON Заказы TO Илья, Петр;

Для команд UPDATE и REFERNCES можно указывать и отдельные поля:

GRANT UPDATE (комм) ON Продавцы TO Илья;

Для привилегии REFERENCES может быть указан список из одного или более столбцов, для которых ограничена эта привилегия. Например, Илья может предоставить Степану право использовать поля таблицы Заказчиков, как таблицу родительского ключа с помощью такой команды:

GRANT REFERENCES (зимя, зном)

ON Заказчики TO Степан;

SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (ВСЕ ПРИВИЛЕГИИ) или просто ALL - для команд и PUBLIC (ОБЩИЕ) - для пользователей.

GRANT ALL ON Заказчики TO PUBLIC;

Возможность передачи пользователем предоставленных ему привилегий осуществляется предложением WITH GRANT OPTION.

Иногда создателю таблицы хочется, чтобы другие пользователи могли получить привилегии в его таблице. Обычно это делается в системах, где один человек или более создает несколько (или все) базовых таблиц в базе данных, а затем передает ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION.

Пусть Илья передает право Петру на привилегию SELECT в таблице Заказчиков:

GRANT SELECT ON Заказчики TO Петр

WITH GRANT OPTION;

Возможно разрешить выполнение процедуры integ_check

GRANT EXECUTE

ON PROCEDURE integ_check

TO PUBLIC;

Привилегия, например, на команду INSERT, может быть отменена:

REVOKE INSERT ON заказы FROM Петр;

Здесь также можно использовать списки:

REVOKE INSERT, DELETE ON Заказчики

FROM Петр, Степан;

Возможно задание (или отмена) привилегий с помощью рассмотренных ранее видов. Например,

CREATE VIEW Москва1

AS SELECT пном, пимя

FROM Продавцы;

Предоставляет Москва1 привилегию SELECT в виде (представлении), а не в самой таблице Продавцов:

GRANT SELECT On Москва1 TO Виктор;

Привилегии возможно ограничивать и строками:

CREATE VIEW Москва2

AS SELECT *

FROM Заказчики

WHERE город = «Сочи»

WITH CHECK OPTION;

GRANT UPDATE ON Москва2 TO Петр;

Предложение WITH CHECK OPTION предохраняет Петр от замены значения поля город на любое значение, кроме Сочи.

Существует целый ряд вариантов работы с видами ссылка на источники литературы.

В системе любого размера всегда имеются некоторые типы суперпользователей - чаще всего Администратор Базы Данных, или DBA. У него имеются такие системные привилегии: CONNECT (Подключить), RESOURCE (Ресурс) и DBA (Администратор Базы Данных).

CONNECT состоит из права зарегистрироваться и права создавать представления и синонимы, RESOURCE состоит из права создавать базовые таблицы, DBA - это привилегия, дающая пользователю высокие полномочия в базе данных.

Некоторые системы имеют специального пользователя, иногда называемого SYSADM или SYS (Системный Администратор Базы Данных), который имеет наивысшие полномочия.

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

GRANT RESOURCE TO Мирон;

Естественно, пользователь Мирон должен быть создан.

У пользователя может быть и пароль (например, Иван). Тогда команда имеет вид

GRANT CONNECT TO Федор IDENTIFIED BY Иван;

что приведет к созданию пользователя с именем Федор, даст ему право регистрироваться и назначит ему пароль Иван.

Если нужно запретить пользователю регистрироваться, следует использовать для REVOKE привилегию CONNECT, которая «удаляет» этого пользователя.

Запрещение (на создание таблиц в БД newa группе clerck) имеет вид

GRANT NOCREATE

ON DATABASE newa

TO GROUP clerk;

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

SQL позволяют создавать синонимы для таблиц (что не является стандартом ANSI).

СинонимСиноним - это альтернативное имя для таблицы. Создатель синонима становится его собственником, так что нет никакой необходимости, чтобы он предшествовал другому пользовательскому идентификатору доступа (имени пользователя). Если имеется по крайней мере одна привилегия в одном или более столбцах таблицы, то можно создать для них синоним.

Петр может создать синоним с именем Москва4 для таблицы с именем Илья.Заказчики с помощью команды

CREATE SYNONYM Москва4 FOR Илья.Заказчики;

Теперь Петр может использовать таблицу с именем Москва4 в команде точно так же, как ее использует Илья.Заказчики.

Чтобы удалить, например, синоним Москва4, когда вместо него уже появился общий синоним Заказчики, Петр может ввести

DROP SYNONYM Москва4;

Как видно, таблицы БД сохраняются в «именной области пользователя», хотя это никак не отражает их физического расположения.

В больших SQL-системах база данных будет разделена на области, так называемые Области Базы Данных или Разделы. Это области сохраняемой информации, которые размещены так, чтобы информация внутри них находилась близко друг к другу для выполнения команд.

Системы, которые используют области базы данных, называемые Data Base Spaces (DBS), позволяют с помощью команд SQL обрабатывать эти области как объекты.

DBS создаются командами CREATE DBSPACE (СОЗДАТЬ DBS), ACQUIRE DBSPACE (ПОЛУЧИТЬ DBS) или CREATE TABLESPACE (СОЗДАТЬ ТАБЛИЧНУЮ ОБЛАСТЬ) в зависимости от используемой реализации. Одна DBS может вмещать любое число пользователей, и отдельный пользователь может иметь доступ к многим DBS. Привилегия создавать таблицы, хотя и может быть передана по всей базу данных, часто передается в конкретной DBS.

Создадим DBS с именем табл1:

CREATE DBSPACE табл1

(pctindex 10,

pctfree 25);

Параметр pctindex определяет, какой процент DBS должен быть оставлен, чтобы сохранять в нем индексы таблиц, рtfree - процент DBS, который оставлен, чтобы расширять таблицам размеры их строк (команда ALTER TABLE может добавлять столбцы или увеличивать размер столбцов, делая каждую строку длиннее).

Большинство программ автоматически будут обеспечивать значения по умолчанию, поэтому можно создавать DBS, не определяя эти параметры. DBS может иметь или определенное ограничение размера, или ей может быть позволено расти неограниченно вместе с таблицами.

Если DBS создалась, пользователям предоставляются права создавать в ней объекты. Можно, например, предоставить Илье право создать таблицу табл1 с помощью команды

GRANT RESOURCE ON табл1 TO Илья;

Первый DBS, назначаемый данному пользователю, указывает обычно область, в которой все объекты этого пользователя создаются по умолчанию.

Пользователи, имеющие доступ к многочисленным DBS, могут определить, где они хотят разместить определенный объект. При разделении базы данных на DBSы надо иметь в виду типы операций, которые будут часто выполняться. Таблицы, которые будут часто объединяться, или которые имеют одну таблицу, ссылающуюся на другую во внешнем ключе, должны находиться вместе в одной DBS.

Команда или группа команд SQL могут быть выполнены или полностью проигнорированы с помощью транзакций. Транзакция начинается всякий раз, когда начинается сеанс с SQL. Все команды, которые введены, будут частью этой транзакции, пока они не завершатся вводом команды COMMIT WORK или команды ROLLBACK WORK. COMMIT может сделать все изменения постоянными с помощью транзакции, а ROLLBACK может откатить их обратно или отменить. Новая транзакция начинается после каждой команды COMMIT или ROLLBACK.

СинтаксисСинтаксис команд соответственно COMMIT WORK; или ROLLBACK WORK;

В большинстве реализаций можно установить параметр, называемый AUTOCOMMIT. Он будет автоматически запоминать все действия, которые будут выполняться. Действия, которые приведут к ошибке,всегда будут автоматически «прокручены» обратно. Для фиксации всех можно использовать команду:

SET AUTOCOMMIT ON;

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

SET AUTOCOMMIT OFF;

Имеется возможность установки AUTOCOMMIT, которую система выполнит автоматически при регистрации.

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

SQL имеет средства управления параллелизмом для точного указания места получения результата: ни одна команда не должна быть выдана, пока предыдущая не будет завершена (включая команды COMMIT или ROLLBACK).

Механизм, используемый SQL для управления параллелизмом операций, называется блокировкой. Блокировки задерживают определенные операции в базе данных. Задержанные операции выстраиваются в очередь и выполняются только после снятия блокировки снята.

Имеется несколько базовых типов блокировок: распределяемые блокировки, специальные и общие блокировки.

Распределяемые (или S-блокировки) могут быть установлены более чем одним пользователем в данный момент времени. Это дает возможность любому числу пользователей обращаться к данным, но не изменять их.

Специальные блокировки (или X-блокировки) не позволяют никому вообще, кроме владельца этой блокировки, обращаться к данным. Специальные блокировки используются для команд, которые изменяют содержание или структуру таблицы. Они действуют до конца транзакции.

Общие блокировки используются для запросов. Насколько они продолжительны, зависит фактически от уровня изоляции (сколько таблиц будет блокировано).

Использование БД

Обновление данных. К командам обновления относят INSERT, DELETE, рассмотренные при заполнении БД данными, и UPDATE, изменяющую некоторые или все значения в существующей строке. В команде названо имя используемой таблицы и предложение SET, указывающее на изменение, которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, можно ввести

UPDATE Заказчики

SET рейтинг = 200;

UPDATE, наподобие DELETE, может работать с предикатами. Например, можно выполнить изменение, одинаковое для всех заказчиков продавца Строков (имеющего пном=1001):

UPDATE Заказчики

SET рейтинг = 200

WHERE пном = 1001;

Можно изменить и несколько столбцов. Пусть продавец Аврорин ушел на пенсию, и надо переназначить его номер новому продавцу:

UPDATE Продавцы

SET пимя = «Суворов»,город = «Клин»,комм = .10

WHERE пном = 1004;

Команда REFERENCES может изменять значения в уже вставленных строках.

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

Запрос формируется командой SELECT (выражение (5.4)).

В самой простой форме команда SELECT просто извлекает информацию из таблицы. Например, вывод всей таблицы Продавцов (табл. 5.3)

Таблица 5.3.

ПРОДАВЦЫ

ПНОМ ПИМЯ город КОММ
1001
1002
1007
1004
1003
Строков
Кирюшин
Аврорин
Удалов
Козлов
Москва
Пермь
Москва
Курск
Орел
.12
.11
.13
.15
.10

Названия полей:

ПНОМ - уникальный нмер, назначенный каждому продавцу (номер служащего);
ПИМЯ - имя продавца;
город - расположение продавца (город);
КОММ - комиссионные продавцов в десятичной форме

SELECT пном, пимя, город, комм

FROM Продавцы; (5.6)

Точка с запятой (;) используется во всех интерактивных командах SQL, чтобы сообщать базе данных, что команда заполнена и готова выполниться. Звездочка (*) в команде может применяться для вывода полного списка столбцов, и предыдущая процедура может выполняться командой

SELECT *

FROM Продавцы;

Имеется большое число вариантов команды SELECT ссылка на источники литературы. Здесь, в силу ограниченного объема данной работы, рассмотрим основные.

В выражении (5.6) можно задать не все поля или переупорядочить поля.

Предложение WHERE команды SELECT позволяет устанавливать предикаты, условие которых может быть или верным, или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которой такое утверждение верно:

SELECT пимя, город

FROM Продавцы

WHERE город = «Москва»;

В предложении WHERE используются не только сравнения вида < , >, ≠, =, ≤, ≥, но и области (множества), определяемые выражениямиДIN, BETWEEN, LIKE, IS (NOT) NULL.

К агрегатным функциям относят COUNT (количество строк в таблице), SUM (арифметическую сумму всех выбранных значений данного поля), AVG (всех выбранных значений данного поля), MAX или MIN (наибольшее или наименьшее из всех выбранных значений данного поля).

Вывод агрегатных функций (вычисляемых полей) имеет такой вид:

SELECT SUM (сумпр)

FROM Заказы;

Он определяет в данном случае сумму всех покупок в таблице Заказов. Аналогична команда для остальных функций.

Предложение GROUP BY команды SELECT позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, найти наибольшую сумму приобретений, полученную каждым продавцом:

SELECT пном, MAX (сумпр)

FROM Заказы

GROUP BY пном;

Получим

пном

1001 767.19

1002 1713.23

1003 75.75

1014 1309.95

1007 1098.16.

GROUP BY применяет агрегатные функции независимо от серий групп, которые определяются с помощью значения поля в целом.

Предложение HAVING определяет критерии, необходимые для удаления определенных групп из вывода. Тогда команда имеет вид

SELECT пном, датпр, MAX ((сумпр))

FROM Заказы

GROUP BY пном, датпр

HAVING MAX ((сумпр)) > 3000.00;

Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящей из команд, использующих GROUP BY. Они должны иметь одно значение на группу вывода. В строгой интерпретации ANSI SQL нельзя использовать агрегат агрегата.

Если надо выполнить простые числовые вычисления данных (вычисляемые выражения) и затем поместить их в форму, более соответствующую потребностям, то SQL позволяет это сделать. Например, если ввести команду

SELECT пном, пимя, город, комм * 100

FROM Продавцы;

то в поле комм появятся значения в процентах.

Возможно упорядочение вывода по одному или нескольким полям, например по убыванию:

SELECT *

FROM Заказы

ORDER BY зном DESC, сумпр DESC;

Вместо имен столбца можно использовать их порядковые номера для указания поля, используемого в упорядочении вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе: поле, упомянутое в предложении SELECT первым, для ORDER BY - это поле 1, независимо от того, каким по порядку оно стоит в таблице. Например, команда упорядочения по убыванию значения комиссионных такова:

SELECT пимя, комм

FROM Продавцы

GROUP BY 2 DESC;

пном комм
Строков
Кирюшин
Удалов
0.17
0.13
0.15

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

Создание объединения чаще всего осуществляется командой вида

SELECT Заказчики.зимя, Продавцы.пимя,

Продавцы.город

FROM Продавцы, Заказчики

WHERE Продавцы.город = Заказчики.город;

Здесь после SELECT указываются через точку имя таблицы и файла:

зимя пимя город
Иванов
Иванов
Ковров
Конкин
Иванов
Крабов
Строков
Строков
Кирюшин
Кирюшин
Аврорин
Аврорин
Москва
Москва
Пермь
Пермь
Москва
Москва

В указании полей могут использоваться и другие условия, например

SELECT пимя, зимя

FROM Продавцы, Заказчики

WHERE пимя < зимя

AND рейтинг < 200;

пимя
зимя
Строков
Красин
Аврорин
Красин
Козлов
Иванов
Козлов
Крабов
Козлов
Красин

Аналогично выглядит и команда соединения трех таблиц и более.

Заметим, что возможно соединение таблицы самой с собой. Эта схема может использоваться для проверки правильности заполнения таблицы.

Для этого используются синонимы таблиц (Алиасалиасы).

Например, найти все пары заказчиков, имеющих одинаковый рейтинг:

SELECT перв.зимя, втор.зимя, перв.рейтинг

FROM Заказчики перв, Заказчики втор

WHERE перв.рейтинг = перв.рейтинг;

Петров Петров 200
Петров Ковров 200
Ковров Петров 200
Ковров Ковров 200
Квакин Квакин 300
Квакин Конкин 300
Крабов Иванов 100
Крабов Крабов 100
Крабов Красин 100
Конкин Квакин 300
Конкин Конкин 300
Красин Иванов 100
Красин Крабов 100
Красин Красин 100

В вышеупомянутой команде SQL ведет себя так, как если бы он соединял две таблицы, называемые «первая» и «вторая». Обе они - фактически таблицы Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы «первый» и «второй» были установлены в предложении FROM запроса, сразу после имени копии таблицы. Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM.

Следующая команда будет определять любые ошибки в заполнении:

SELECT перв.прном, втор.зном, перв.пном,

втор.прном, втор.зном, втор.пном

FROM Заказчики перв, Заказчики втор

WHERE перв.зном = втор.зном

AND перв.пном <> втор.пном;

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

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

Пусть известно имя продавца (Аврорин ), но значение его поля пном неизвестно и надо извлечь все заказы из таблицы Заказов. Тогда

SELECT *

FROM Заказы

WHERE пном =

(SELECT пном

FROM Продавцы

WHERE пимя = «Аврорин»);

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (подзапрос) предложения WHERE. Ответ: пном = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал WHERE пном = 1004.

Для работы с несколькими таблицами может использоваться и операция объединения (UNION). Например, для получения всех продавцов и заказчиков, размещенных в Москве, можно использовать команду:

SELECT пном, пимя

FROM ПродавцыД

WHERE город = «Москва»

UNION

SELECT зном, зимя

FROM Заказчики

WHERE город = «Москва»;

Как можно видеть, столбцы, выбранные двумя командами, выведены так, как если бы она была одна. Заголовки столбца исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно только из одной таблицы. Только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает понять SQL, что имеется еще один запрос или более.

Возможны и запросы с командами обновления, рассмотренными ранее.

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

Чтобы преодолеть эти ограничения, можно включать SQL в программы, написанные на каком-либо процедурном языке. В качестве такого процедурного языка примем Паскаль, поскольку он прост в понимании и имеет полуофициальный стандарт ANSI.

Отметим некоторые ограничения SQL.

Логические конструкции типа if ... then («если ... то»), for ... do («для ... выполнить») и while ... repeat («пока ... повторять»), используемые для структур большинства компьютерных программ, здесь отсутствуют.

Интерактивный SQL не может делать многие операции со значениями, размещением или распределением их, выводом их на какое-то устройство.

Цель вложенного SQL состоит в соединении возможностей декларативного и процедурного языков.

Для этого команды SQL помещаются в исходный текст главной программы, которой предшествует фраза EXEC SQL (EXECute SQL).

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

Когда вставляются команды SQL в текст программы, написанной на другом языке, надо выполнить прекомпиляцию прежде, чем окончательно ее скомпилировать. Программа, называемая прекомпилятором (или препроцессором), будет просматривать текст программы и преобразовывать команды SQL в форму, удобную для использования базового языка. Затем используется обычный транслятор, чтобы преобразовывать программу из исходного текста в выполняемый код.

Иными словами, основная программа вызывает процедуры SQL, которые выбирают параметры из главной программы и возвращают уже обработанные значения в основную программу. Модуль может содержать любое число процедур, каждая из которых состоит из одиночной команды SQL. Идея в том, чтобы процедуры могли работать тем же самым способом, что и процедуры на языке, в который они были вложены.

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

SQL и части базового языка программ будут связываться друг с другом с помощью значений переменных. Разные языки распознают различные типы данных для переменных, и ANSI определяет эквиваленты SQL базового языка Паскаль.

Можно использовать переменные из главной программы во вложенных операторах SQL везде, где будут использоваться выражения значений.

Главные переменные должны:

  • быть объявленными в SQL DECLARE SESSION (РАЗДЕЛ ОБЪЯВЛЕНИЙ);

  • иметь совместимый тип данных с их функциями в команде SQL;

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

  • предшествовать двоеточию (:), когда они упоминаются в команде SQL.

Пусть в программе имеются четыре переменные с именами id_num, salesperson, loc и comm. Они содержат значения, которые нужно вставить в таблицу Продавцов. Можно вложить следующую команду SQL в программу:

EXEC SQL INSERT INTO Продавцы

VALUES (:id_num, :salesperson, :loc, :comm)

Обратите внимание, что точка с запятой в конце команды отсутствует. Это потому, что соответствующее завершение для вложенной команды SQL зависит от языка, для которого делается вложение.

Команду можно включить в цикл:

while not end-of-file (input) do

begin

readln (id_num, salesperson, loc, comm);

EXEC SOL INSERT INTO Salespeople

VALUES (:id_num, :salesperson, :loc, :comm);

end;

Фрагмент программы на ПАСКАЛе определяет цикл, который будет считывать значения из файла, сохранять их в четырех поименованных переменных, сохранять значения этих переменных в таблице Продавцов и затем считывать следующие четыре значения.

Все переменные, на которые имеется ссылка в предложениях SQL, должны сначала быть объявлены в SQL DECLARE SECTION (РАЗДЕЛ ОБЪЯВЛЕНИЙ), использующем обычный синтаксис главного языка. Раздел объявлений должен начинаться и кончаться вложенными командами SQL - BEGIN DECLARE SECTION (Начало Раздела Объявлений) и ENDДDECLARE SECTION (Конец Раздела Объявлений), которым предшествует, как обычно, EXEC SQL (Выполнить).

Чтобы объявить переменные предыдущего примера, можно ввести:

EXEC SQL BEGIN DECLARE SECTION;

Var

id-num: integer;

Salesperson: packed array (1 .. 10) of char;

loc: packed array (1 .. 10) of char;

comm: real;

EXEC SQL END DECLARE SECTION;

где Var - заголовок, который предшествует ряду объявляемых переменных и упакованным (или распакованным) массивам.

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

EXEC SQL SELECT snum, sname, city, comm

INTO :id_num, :salesperson, :loc, :comm

FROM Salespeople

WHERE snum = 1001;

Возможно использовать запросы, выводящие многочисленные строки, применяя курсор. Курсор - это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запросе.

Заметим, что язык SQL может использоваться как для построения, так и для запросов, в то время как язык QBE может применяться только для обновления и - в основном - для запросов.

5.3.2.
Язык QBE

Работа на языке SQL проста, но требует знания языка. Для пользователей, совершенно не знакомых с программированием, запрос может осуществляться одним из следующих способов:

    1) через меню и экранные формы;

    2) с помощью Язык QBEязыка Query By Example - QBE (запрос по примеру).

Первый способ зависит от варианта разработки, второй способ, предложенный Злуд М.М.М.М. Злудом и базирующийся на исчислении доменов, является универсальным и не требует знания программирования. В связи с этим рассмотрим его подробнее.

С помощью этого способа на экран вызывается одна или несколько таблиц. В первом столбце каждой из них указывается имя таблицы (файла). В столбцах, под именами полей могут быть указаны условия запроса. Переход от одной таблицы к другой осуществляется с помощью указания в соответствующих полях одинаковых подчеркнутых переменных, написанных большими буквами. Выводимые на печать поля указываются символом P (print), точкой и подчеркнутой переменной (например, P.A).

Приведем два примера запросов, базирующихся на примере 4.1.

Пример 5.2. Получить фамилии студентов 4-го курса кафедры ИиУС, имеющих балл не менее 4 (рис. 5.3).

Пример 5.3. Получить фамилии студентов, руководителем которых является профессор кафедры ИиУС (рис. 5.4).

Могут быть использованы (в первом столбце запроса) агрегатные функции (SUM, AVG, MAX, MIN, CNT-счетчик). Возможно создавать виды (View).

QBE поддерживает справочник таблиц ссылка на источники литературы, процедуры обновления.

В последнем случае в первом столбце запроса размещают операторы U. (или UPDATE), D. (DELETE), I. (INSERT) для модификации, уничтожения и вставки данных соответственно.

© Центр дистанционного образования МГУП