19.3. РЕЛЯЦИОННЫЕ ЯЗЫКИ МАНИПУЛИРОВАНИЯ ДАННЫМИ

• Реляционные модели

• Классификация реляционных языков

• Основные характеристики языка SQL

• Организация запросов к базе данных на языке SQL

РЕЛЯЦИОННЫЕ МОДЕЛИ

Основные понятия

Реляционные модели данных (РМД), в отличие от сетевых и иерархических (см- гл. 15), характеризуются простотой структуры данных, удобным для пользователя табличным представлением, а также возможностью использования формального аппарата алгебры отношений и реляционного исчисления для обработки данных. Отношение, или реляционная таблица, имеет прямоугольную структуру ("плоский файл") (рис. 19.28).

Рис. 19.28. Структура данных реляционной таблицы

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

Условимся считать, что указанные термины имеют полноправное употребление и являются синонимами.

Схема реляционной таблицы образована именами полей, образующих структуру ее записи, например:

(Поле1, Поле2,.... Поле n)

Пример 19.42. Схема отношения:

СТУДЕНТ(Имя, Фамилия, Дата рождения, N зач.книжки, ...).

В заполненном виде этой схеме соответствует таблица (рис. 19.29).

Рис. 19.29. Фрагмент реляционной таблицы

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

Для определенности изложения рассмотрим реляционную таблицу (рис. 19.29).

Таблица СТУДЕНТ содержит основные сведения о студентах учебного заведения. Каждое поле принимает определенное значение в строке таблицы, обладает определенным форматом, или типом данных: Фамилия - строка символов. Дата рождения - дата в формате ДД.ММ.ГГ.

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

Если ключей несколько, один из них в объявлении схемы принимается за первичный (основной). Прочие ключи называются возможными. По первичному ключу выполняется автоматическое упорядочение записей (сортировка в порядке возрастания значений ключа).

Пример 19.43. Таблица СТУДЕНТ содержит записи; для идентификации отдельной записи указывается значение ключа. В данном случае ключей несколько:

• N зач. Книжки;
• [Имя]+[Фамилия]+[Дата рождения].

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

Для эффективной реализации поиска и обработки данных создаются индексы, обеспечивающие формирование дополнительных индексных файлов (рис. 19.30).

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

Рис. 19.30. Индекс реляционной таблицы

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

Работа с данными осуществляется на уровне:

• отдельной таблицы;
• нескольких таблиц.

Типовые операции по обработке записей реляционной таблицы

Классический состав операций с реляционными таблицами определяет требования к реляционным языкам. Рассмотрим типовые операции по обработке записей в изолированной реляционной таблице.

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

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

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

Выборка (селекция) - выбор подмножества записей таблицы по условию, которое формулируется как логическое выражение. Результирующая таблица имеет ту же схему, что и исходная; эту операцию называют "горизонтальной" выборкой. Частным случаем выборки является пустая выборка, если исходные записи не соответствуют условию отбора.

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

Пример 19.44.

∙ Ввод сведений о новом студенте:

ВКЛЮЧИТЬ в СТУДЕНТ [Игорь; Смирнов; 23.02.1980; 126543)

∙ Изменить номер зачетной книжки студента:

ОБНОВИТЬ в СТУДЕНТ: [Игорь; Смирнов; 23.02.1980] поле [N зач книжки] = 345234

∙ Удаление сведения о студенте:

УДАЛИТЬ в СТУДЕНТ: [Игорь; Смирнов; 23.02.1980]

∙ Удаление сведения о студентах, которые родились в 1979 г.:

УДАЛИТЬ в СТУДЕНТ: если [Дата рождения] >=01.01.1980 AND [Дата рождения] <01.01.1981

∙ Отобрать студентов, у которых номера зачетных книжек находятся в интервале 100000≈200000:

ВЫБОРКА в СТУДЕНТ: если [N зач.книжки]>=100000 AND [N зач. Книжки] < 200000

Результат выборки

∙ Сформировать список имен студентов на основании таблицы СТУДЕНТ

ПРОЕКЦИЯ СТУДЕНT [Имя]:

СТУДЕНТ

Результат проекции

Типовые операции совместной обработки реляционных таблиц

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

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

Пример 19.45. Имеются таблицы СТУДЕНТ (дневная форма обучения) и СТУДЕНТ-ЗАОЧНИК (заочная форма обучения) одной схемы Они объединяются в одну таблицу СТУДЕНТЫ той же схемы (но без дублирования первичных ключей записей). Так. студентка ПЕТРОВА Марина присутствует в таблице СТУДЕНТЫ в единственном числе:

CТУДЕНТ

СТУДЕНТ √ ЗАОЧНИК

Итог объединения: СТУДЕНТЫ

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

Пример 19.46. Имеются таблицы СТУДЕНТ (дневная форма обучения) и СТУДЕНТ-ЗАОЧНИК (заочная форма обучения). При пересечении таблиц выявляются студенты, находящиеся в двух таблицах одновременно:

СУДЕНТ

СТУДЕНТ-ЗАОЧНИК

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

Пример 19.47. Имеются таблицы СТУДЕНТЫ (весь контингент студентов - дневная и заочная формы обучения) и СТУДЕНТ-ЗАОЧНИК (заочная форма обучения). При вычитании таблиц выявляются студенты очной формы обучения:

СУДЕНТ-ЗАОЧНИК

Итог вычитания таблиц: СТУДЕНТ

Между записями реляционных таблиц устанавливаются отношения следующих типов:

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

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

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

Пример 19.48. Если таблица СТУДЕНТ содержит первичный ключ [N зач.книжки], а таблица ОЦЕНКА имеет схему ([N зач.книжки], [Код дисциплины], [Результат]), то поле [N зач.книжки] таблицы ОЦЕНКА выступает как внешний ключ для организации связи с таблицей СТУДЕНТ. Связь возможна, если тип данных и значение данного поля в обеих таблицах будут одинаковыми, при этом эти поля могут иметь и Произвольные названия. Так, в таблице ОЦЕНКА может использоваться поле [Код студента], к примеру.

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

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

Пример 19.49. Имеются таблицы СТУДЕНТ и ДИСЦИПЛИНА ([Код дисциплины], [Наименование дисциплины]). Декартово произведение таблиц дает новую таблицу - УСПЕВАЕМОСТЬ со схемой ([Имя], [Фамилия]. Дата рождения], [N зач.книжки], [Код дисциплины], [Наименование дисциплины]). Если какой-либо студент не связан с определенной дисциплиной либо по дисциплине не предполагается учет успеваемости, результирующая запись, полученная как декартово произведение, не имеет смысла.

СТУДЕНТ

ДИСЦИПЛИНА

УСПЕВАЕМОСТЬ

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

Соединение - две таблицы, имеющие общие поля - внешние ключи, участвуют в создании новой таблицы, схема которой строится объединением всех полей исходных отношений, а результирующие записи формируются по определенным условиям:

• для одинаковых значений внешних ключей - симметричное соединение;
• для всех записей одной из таблиц и соответствующих им записей другой таблицы - внешнее соединение.

Пример 19.50. Соединить таблицы СТУДЕНТЫ и ОЦЕНКА при условии, что включены все записи таблицы СТУДЕНТ и соответствующие им записи таблицы ОЦЕНКА. Объединение таблиц возможно по общему полю - [N зач. книжки].

СТУДЕНТЫ

ОЦЕНКА

РЕЗУЛЬТАТЫ

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

Пример 19.51. Результат деления таблицы УСПЕВАЕМОСТЬ на таблиц ОЦЕНКА дает таблицу СГУДЕНТ (см. предыдущий пример).

КЛАССИФИКАЦИЯ РЕЛЯЦИОННЫХ ЯЗЫКОВ

Типы реляционных языков

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

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

Выделяют следующие разновидности языков реляционной алгебры:

• dBASe-подобные языки приближены к языкам структурного программирования, обеспечивают создание интерфейса пользователя и типовые операции обработки;
• графические реляционные языки, которые ориентированы на конечных пользователей;
• SQL-подобные языки запросов, реализованные а большинстве многопользовательских и распределенных систем управления базами данных.

dBASe-подобные реляционные языки

СУБД реляционного типа, такие, как dBASe, Paradox, FoxPro, Clipper, Rbase и др., используют языки манипулирования данными, обеспечивающие основные операции обработки реляционных баз данных, образующих класс dBASE-подобных (X-Base).

Рассмотрим данный класс языков на примере СУБД класса dBASe.

С помощью полноэкранных команд, вызываемых через главное меню (Управляющий центр, режим Assist и т.п.), осуществляются создание и редактирование схемы реляционной таблицы (файла), ввод и редактирование данных. Для реализации тех же действий на программном уровне имеются соответствующие команды языка (CREATE, MODIFY, UPDATE, DELETE и др.).

Работа с реляционной таблицей (файлом базы данных) организуется в отдельной рабочей области, которой присваивается имя (алиасное имя или номер). После активизации файла к нему можно перейти - указав номер рабочей области. СУБД запоминает указатель на последней обрабатываемой записи (при первоначальном открытии файла текущий номер записи - 1). Позиционирование в файле на запись выполняется:

• непосредственно, указанием номера записи (начало или конец файла, определенный номер записи);
• при поиске записи по заданному логическому условию.

Язык обеспечивает выполнение всех рассмотренных типовых операций над отдельным файлом, а именно:

APPEND BLANK, BROWSE, CHANGE, EDIT, INSERT - добавление, редактирование записей (в режиме полноэкранного ввода);

DELETE - удаление записей (в программном режиме);

SEEK, FIND, LOCATE - поиск записи по условию;

COPY - копирование всех или части записей активного файла в новый файл;

CONTINUE - продолжение поиска записи по ранее сформулированному условию и т.п.

Границы области действия команды принимают значения:

• RECORD n - определенная запись с номером n;
• ALL - все записи файла;
• NEXT n - следующие n записей, начиная с текущей;
• REST - все записи, начиная с текущей и до конца файла.
• Условия выполнения команд задаются с помощью формата ключевых слов FOR и WHILE.

Условие1 действует в качестве фильтра (ВЫБОРКА) записей исходного файла: если записи соответствуют условию, они участвуют в операции. Условие формулируется применительно к полям записи, например:

FOR [N зач.книжки]>=1000000 AND [N зач.книжки] < 2000000

Условие2 позволяет прекратить операцию в случае его нарушения, например:

WHILE [Дата рождения] <1.1.80

Многие команды включают список полей, указываемых за ключевым словом FIELDS, на которые распространяется действие операции.

Пример 19.52.

DELETE границы FOR условие1 WHILE условие2

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

COPY TO нов_файл границы FIELDS список_полей FOR условие1 WHILE условие2

Записи активного файла, удовлетворяющиеся условию1, если истинно условие2, используются для формирования нового файла, схема которого задается как список_полей.

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

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

Пример 19.53.

JOIN WITH псевдоним FOR условие FIELDS список_полей

Запись исходного файла объединяется с записью файла, открытого под именем псевдоним, если выполняется условие. Формируется новый файл, схема которого задается списком полей.

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

Кроме того, данный класс реляционных языков реализует типовые конструкции языков структурного программирования:

• циклы (DO WHILE . . .END DO);
• условные операторы (IF. . .ELSE. . .ENDIF);
• альтернативные операторы (DO CASE. . .OTHERWISE. . .ENDCASE) и др.

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

Графические (схематичные) реляционные языки

Типичным представителем является язык QBE(Query By Example), реализованный в среде электронных таблиц, в ряде СУБД, в пакете Microsoft Query.

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

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

Пример 19.54. Получить список имен и фамилий студентов

Для горизонтальной выборки (селекции) задаются логические условия (критерии) отбора записей в поисковых полях.

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

Арифметические операторы - используются для выполнения вычислений с числами в качестве констант выражения:

*Умножения двух чисел

+ Сложения двух чисел

- Вычитания одного числа из другого

/ Деления одного числа на другое

Операторы сравнения - используются для сравнения двух значений:

> (больше)

>= (больше или равно)

< (меньше)

<= (меньше или равно)

<> (не равно)

=(равно).

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

И (AND) - должны выполняться оба критерия.

Или (OR) - должен выполняться один из критериев.

Не (NOT) - этот критерий не должен выполняться.

Могут использоваться специальные операторы типа:

BETWEEN - значение в заданном диапазоне.

IN - одно из значений списка.

IS - с ключевым словом Null определяет, является ли величина нулем (нет значения) или нет (есть значение).

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

Условия задаются в следующих вариантах:

• для одного поля;
• в одной строке для нескольких полей, считая все условия совместными;
• в разных строках для одного или разных полей, считая их альтернативными.

Пример 19.55. Для получения фамилий студентов с именем Иван и датой рождения в диапазоне 1.1.79- 1.1.80 создастся запрос:

Для получения фамилий студентов с именем Иван с датой рождения в диапазоне 1.1.79 -1.1.80 и фамилий студентов с именем Петр создается запрос:

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

или

или

Язык QBE позволяет вычислять групповые функции (по группе выделенных строк) с помощью функций:

Avg -среднее арифметическое значение поля;

Count - число выбранных записей;

Мах - максимальное значение поля;

Min - минимальное значение поля;

Sum - сумма значений поля.

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

Пример 19.56. Для подсчета количества студентов с именем Иван и датой рождения в диапазоне 1.1.79 - 1.1.80 создается запрос:

Ключевое слово Calc означает вычисление значений по данному полю.

Выражения используются в запросе и для формирования новых данных.

Пример 19.57. Для каждого студента определить количество прожитых на сегодняшнюю дату дней:

Символ *;идентификатор значения поля, today - встроенная функция вычисления сегодняшней даты.

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

Пример 19.58. Получить сведения о студентах, успевающих на 4 и 5, название дисциплины и результат сдачи экзамена (оценку):

Символы *;(*;*) -идентификаторы ключей связи в различных реляционных таблицах. Выходная структура содержит поля: имя. фамилия, результат, название дисциплины. Выводятся сведения о студентах, которые учатся на 4 и 5.

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

ONLY - второй набор - подмножество первого,

NO - наборы не совпадают,

EVERY - первый набор - подмножество второго,

EXACTLY - наборы совпадают.

Первый набор формируется с помощью ключевого слова SET.

Пример 19.59. Получить сведения о студентах, которые имеют такие же результаты, что и студент с зачетной книжкой 123456

В ряде СУБД кроме выборки записей возможны операции включения новых записей (INSERT), удаления записей (DELETE) или групповой корректировки выбранных записей (CHANGETO).

ОСНОВНЫЕ ХАРАКТЕРИСТИКИ ЯЗЫКА SQL

Краткая характеристика языка

SQL (Structured Query Language) - это язык программирования, который используется при работе с реляционными базами данных в современных СУБД (ORACLE, dBASE IY, dBASE Y, Paradox, Access и др.).

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

Операторы языка SQL для работы с реляционной базой данных

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

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

CREATE TABLE таблица (поле1 тип [(размер)] [индекс1]

[,поле2 тип [(размер)] [индекс2] [,...]] [, составной_индекс [,...]])

где таблица - имя создаваемой таблицы;

поле1, поле2 - имена полей таблицы;

тип - тип поля;

размер - размер текстового поля;

индекс1, индекс2 - директивы создания простых индексов (по отдельному полю);

составной_индекс - директива создания составного индекса.

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

Для создания простого индекса используется фраза (помещается за именем поля):

CONSTRAINT имя_индекса {PRIMARY KEY | UNIQUE |

REFERENCES внешняя_таблица [(внешнее_поле)]}

Директива создания составного индекса (помещается в любом месте после определения его элементов):

CONSTRAINT имя {PRIMARY KEY (ключевое1 [,ключевое2 [, ...]]) | UNIQUE|

(уникальное1 [, уникальное [, ...]]) | FOREIGN KEY (ссылка1[, ссылка2 [, ...]])

REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, ...]])]}

Служебные слова:

UNIQUE - уникальный индекс (в таблице не может быть двух записей, имеющих одно и то же значение полей, входящих в индекс);

PRIMARY KEY - первипчный ключ таблицы (может состоять из нескольких полей; упорядочивает записи таблицы);

FOREIGN KEY - внешний ключ для связи с другими таблицами (может состоять из нескольких полей);

REFERENCES - ссылка на внешнюю таблицу.

Пример 19.60.

CREATE TABLE Студент

([Имя] TEXT,

[Фамилия] TEXT,

[Дата рождения] DATETIME,

CONSTRAINT Адр UNIQUE ([Имя], [Фамилия], [Дата рождения]))

Будет создана таблица СТУДЕНТ, в составе которой:

- два текстовых поля: Имя, Фамилия,

- одно поле типа дата/время - Дата рождения.

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

2. Изменение структуры таблиц. При необходимости можно выполнить реструктуризацию таблицы:

- удалить существующие поля,

- добавить новые поля,

- создать или удалить индексы.

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

ALTER TABLE таблица

ADD {[COLUMN] поле тип [(размер)] [CONSTRAINT индекс] |

CONSTRAINT составной_индекс}|

DROP {[COLUMN] поле i CONSTRAINT имя_индекса} }

Опция ADD обеспечивает добавление поля, опция DROP - удаление поля таблицы, добавление опции CONSTRAINT означает подобные действия для индексов таблицы.

Пример 19.61.

ALTER TABLE Студент ADD COLUMN [Группа] ТЕХТ(5)

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

CREATE [ UNIQUE ] INDEX индекс

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

[WITH {PRIMARY | DISALLOW NULL | IGNORE NULL }]

Фраза WITH обеспечивает наложение условий на значения полей, включенных в индекс:

DISALLOW NULL - запретить пустые значения в индексированных полях новых записей;

IGNORE NULL - включать в индекс записи, имеющие пустые значения в индексированных полях.

Пример 19.62.

CREATE INDEX Гр ON Студент ([Группа]) WITH DISALLOW NULL

3. Удаление таблицы. Для удаления таблицы (одновременно и структуры, и данных) используется команда:

DROP TABLE имя таблицы

Для удаления только индекса таблицы (сами данные не разрушаются) выполняется команда:

DROP INDEX имя_индекса ON имя_таблицы

Пример 19.63.

DROP INDEX Адр ON Студент

- удален только индекс Адр

DROP TABLE Студент

- удалена вся таблица

4. Ввод данных в таблицу. Формирование новой записи в таблице выполняется командой:

INSERTINTO таблица_куда [(поле1[, поле2[,...]])]

VALUES (значение 1[,значение2[,...]);

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

Пример 19.64.

INSERT INTO Студент ([Фамилия]. [Имя], [Дата рождения]) VALUES ("Петров", "Иван", 23/3/80)

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

INSERT INTO таблица.куда [IN внешняя_база_данных]

SELECT [источник.] поле1 [, поле2[,...]

FROM выражение

WHERE условие

Перед загрузкой выполняется оператор подзапроса SELECT, который и формирует выборку для добавления. Фраза SELECT определяет структуру данных источника передаваемых записей - имена таблицы и полей, содержащих исходные данные для загрузки в таблицу_куда; FROM позволяет указать имена исходных таблиц, участвующих в формировании выборки, а фраза WHERE - задает условия выполнения подзапроса. Структура данных выборки должна соответствовать структуре данных таблицы, в которую производится добавление.

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

Пример 19.65.

INSERT INTO Студент SELECT [Студент-заочник].* FROM [Студент-заочник]

Все записи таблицы [Студент-заочник] в полном составе полей будут добавлены в таблицу Студент.

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

Пример 19.66.

INSERT INTO Студент SELECT [Студент-заочник].*FROM [Студент-заочник] WHERE [Дата рождения] >= #01/01/80#

Записи таблицы [Студент-заочник] добавляются в таблицу Студент, если дата рождения студента больше или равна указанной.

5. Операции соединения таблиц. Операцию INNER JOIN можно использовать в любом предложении FROM. Она создает симметричное объединение, наиболее частую разновидность внутреннего объединения: записи из двух таблиц объединяются, если связующие поля этих таблиц содержат одинаковые значения:

FROM таблица1 INNER JOIN таблица2 ON таблица1. поле1 =таблица2.поле2

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

Возможные варианты операции:

LEFT JOIN (левостороннее) соединение - выбираются все записи "левой" таблицы и только те записи "правой" таблицы, которые содержат соответствующие ключи связи;

RIGHT JOIN (правостороннее) соединение - выбираются все записи "правой" таблицы и только те записи "левой" таблицы, которые содержат соответствующие ключи связи.

Пример 19.67.

SELECT Студент*, Оценка.*FROM Студенты INNER JOIN Оценка

ON Студент.[N зач.книжки] = Оценка.[N зач.книжки];

SELECT Студент.*,Оценка.*FROM Студенты LEFT JOIN Опенка

ON Студент.[N зач.книжки] = Оценка.[N зач.книжки];

SELECT Студент.*, Оценка.*FROM Студенты RIGHT JOIN Оценка

ON Студент.[N зач.книжки] = Оценка.[N зач.книжки];

В первом случае создается симметричное соединение двух таблиц по полю [╧ зач.книжки]. Не выводятся записи, если значение их ключей связи (указанное поле) не представлено в двух таблицах. Во втором случае будут выведены все записи таблицы СТУДЕНТ и соответствующие им записи таблицы ОЦЕНКА. В третьем случае - наоборот, все записи таблицы ОЦЕНКА и соответствующие им записи таблицы СТУДЕНТ.

Операции JOIN могут быть вложенными для последовательного соединения нескольких таблиц.

Пример 19.68.

SELECT Студент., .Оценка. Дисциплина. [Наименование дисциплины]

FROM (Студент INNER JOIN (Оценка INNER JOIN

( Дисциплина ON Оценка. [Код дисциплины] =

Дисциплина. [ Код дисциплины])

ON Студент.[N зач.книжки]=Оценка. [N зач.книжки])

Сначала происходит соединение таблиц ОЦЕНКА и ДИСЦИПЛИНА по ключу связи [Код дисциплины]. Соединение .симметричное, то есть если коды дисциплины не совпадают, записи этих таблиц не соединяются. Затем происходит соединение таблиц СТУДЕНТ и ОЦЕНКА по ключу связи [N зач.книжки].

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

6. Удаление записей в таблице. В исходной таблице можно удалять отдельные или все записи, сохраняя при этом структуру и индексы таблицы. При удалении записей в индексированной таблице автоматически корректируются ее индексы:

DELETE [таблица.*] FROM выражение WHERE условия_отбора

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

DELETE * FROM таблица

Пример 19.69.

DELETE * FROM Студент

Все ранее загруженные записи будут удалены.

DELETE * FROM Студент WHERE [Дата рождения]># 1.1.81#

Удаляются только те записи, в которых поле [Дата рождения] больше указанной даты.

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

DELETE таблица.*FROM таблица INNER JOIN др._таблица

ON таблица. [полеN] = др._таблица.[полеМ] WHERE условие

Пример 19.70.

DELETE Студент.*From Студент INner JoIN [Студент заочник]

ON Студент. [Группа] = [Студент заочник]. [Группа]

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

Примечание. Средствами MicrosoftACCESS невозможно восстановить записи, удаленные с помощью запроса на удаление записей.

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

UPDATE таблица SET новое _значение WHERE условия _отбора

новое значение указывается как имя_поля=новое значение

Пример 19.71.

UPDATE Студент SET [Группа] ⁼ "1212"

WHERE [Фамилия] LIKE 'В*' AND [Дата рождения] <= #01/01/81#

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

UPDATE Студент INner JoIN [Студент заочник] ON Студент.[Группа]=

[Студент заочник]. [Группа] SET [Группа]= [Группа]&"а"

В таблице Студент изменить номера групп, если они встречаются в таблице [Студент заочник], добавив букву а.

ОРГАНИЗАЦИЯ ЗАПРОСОВ К БАЗЕ ДАННЫХ НА ЯЗЫКЕ SQL

Синтаксис оператора SELECT

Выборка с помощью оператора SELECT - наиболее частая команда при работе с реляционной базой данных. Этот оператор обладает большими возможностями по заданию структуры выходной информации, указанию источников входной информации, способа упорядочения выходной информации, формированию новых значений и т.п. (табл. 19.9).

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

Универсальный оператор SELECT имеет следующую конструкцию:

SELECT [предикат] {*| таблица.*| [таблица.] поле1[ ,[таблица.] поле2.[,...]]} [AS псевдоним1 [, псевдоним2 [, ...]]]

FROM выражение [,...] [IN внешняя __база_данных]

[WHERE... ]

[GROUP BY...]

[HAVING... ]

[ORDER BY...]

[WITH OWNERACCESS OPTION]

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

Таблица 19.9. Аргументы оператора SELECT

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

Вычисляемое выражение состоит из:

• полей таблиц;
• констант;
• знаков операций;
• встроенных функций;
• групповых функций SQL.

Пример 19.72.

SELECT [Имя],[Фамилия] FROM Студент

SELECT TOP 5 [Фамилия] FROM Студент

SELECT TOP 5 [Фамилия] FROM Студент ORDER BY [Группа]

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

Если используются одноименные поля из нескольких таблиц, включенных в предложение FROM, следует указать перед именем такого поля имя таблицы через . (точку): [Студент заочник].[Группа] и [Студент]. [Группа] - два одноименных поля из разных таблиц.

Для изменения заголовка столбца с результатами выборки используется служебное слово AS.

Пример 19.73.

SELECT DISTINC Т [Дата рождения] AS Юбилей FROM Студент

SELECT [Фамилия] &" "& [Имя] AS ФИО, [Дата рождения] AS Год

FROM Студент

В первом случае будут выведены неповторяющиеся даты рождения студентов, которые имеют новое наименование - Юбилей. Во втором случае в результирующей таблице присутствуют все записи, но вместо [Дата рождения] указан Год и вместо Фамилия и Имя, соединенных вместе через пробел, - ФИО.

Наиболее часто слово AS применяется для именования вычисляемых полей (см. ниже).

Задание условий выборки

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

Таблица 19.10. Логические операторы для построения условий выборки

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

LIKE - выполняет сравнение строковых значений;

BETWEEN...AND - выполняет проверку на диапазон значений;

IN - выполняет проверку выражения на совпадение с любым из элементов списка;

IS - проверка значения на Null (пусто).

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

Пример 19.74.

SELECT Студент.*FROM Студент WHERE [Дата рождения]>= #01.01.79#

SELECT Студент.*FROM Студент WHERE [Дата рождения] >= #01.01.79#

AND [Группа] IN ("1212", "1213")

SELECT Студент.*FROM Студент WHERE [Дата рождения]

BETWEEN #01.0179 AND # 01. 01.81# AND [Группа] IN ("1212", "1213")

SELECT Студент.*FROM Студент INner JoIN [Студент заочник]

ON Студент. [Группа]= [Студент заочник] [Группа]

WHERE Студент.[Дата рождения] > = #01.01.79#

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

Групповые функции SQL

Групповые функции необходимы для определения статистических данных на основе наборов числовых значений:

Avg - вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса;

Count - вычисляет количество выделенных записей в запросе;

Min, Max - возвращают минимальное и максимальное значения из набора в указанном поле запроса;

StDev, StDevPs - возвращают среднеквадратическое отклонение генеральной совокупности и выборки для указанного поля в запросе;

Sum - возвращает сумму значений в заданном поле запроса;

Var, VarPs - возвращают дисперсию распределения генеральной совокупности и выборки для указанного поля в запросе.

Для определения полей группирования указывается ключевое слово GROUP BY. Можно указать также слово HAVING для заданного условия по группе при вычислении групповых значений.

Пример 19.75.

SELECT Фамилия, Аvg(Результат) AS Средиий_балл FROM Результаты

GROUP BY [N зач.книжки]

SELECT [Код дисциплины], Avg(Результат) AS Средний_балл

FROM Результаты GROUP BY [Код дисциплины]

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

Пример 19.76.

SELECT Фамилия, Аvg(Результат) AS Средний_балл FROM Результаты

GROUP BY [╧ зач.книжки] HAVING Avg(Результат) > 4.5

SELECT [Код дисциплины], Аvg(Результат) AS Средний_балл

FROM Результаты GROUP BY[Код дисциплины] HAVING Avg(Рузультат)<4

В первом случае создается список фамилий студентов с указанием среднего балла по каждому студенту, выводятся фамилии тех студентов, которые имеют средний балл выше 4.5. Во втором случае выводится список кодов дисциплин со средним баллом при условии, что он ниже 4.

Подчиненный запрос

В инструкцию SELECT может быть вложена другая инструкция SELECT, SELECT...INTO, INSERT┘INTO, DELETE или UPDATE. Различают основной и подчиненные запросы, которые являются вложенными в основной запрос.

Подчиненный запрос можно использовать вместо выражения в списке полей инструкции SELECT или в предложениях WHERE и HAVING.

Существуют три типа подчиненных запросов:

сравнение (ANY|ALL|SOME) (инструкция);

выражение [NOT] IN (инструкция);

[NOT] EXISTS (инструкция).

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

Ключевые слова:

ANY - каждый (сравнение с каждым элементом подчиненной выборки).

ALL - все (сравнение со всеми элементами подчиненной выборки).

SOME - некоторые (сравнение с некоторыми элементами подчиненной выборки).

Пример 19.77.

SELECT.*FROM Оценка WHERE [Результат] > ANY

(SELECT [Результат] FROM Оценка

WHERE Результат.[N зач.книжки]="123124")

Отбираются только те записи из таблицы Оценка, в которых значение результата больше (выше) каждой оценки студента с N зач.книжки 123124.

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

Пример 19.78.

SELECT*FROM Студент WHERE [N зач.книжки] IN

(SELECT [N зач.книжки] FROM Оценка WHERE [Результат] >=4)

SELECT*FROM Дисциплина WHERE [Код дисциплины] NOT IN

(SELECT [Код дисциплины] FROM Оценка)

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

Во втором случае отбираются дисциплины, которые не встречаются в таблице Оценка. Третий тип - инструкция SELECT, заключенная в круглые скобки, с предикатом EXISTS (с необязательным зарезервированным словом NOT) в логическом выражении для определения, должен ли подчиненный запрос возвращать какие-либо записи.

Пример 19.79.

SELECT*FROM Студент WHERE EXISTS

SELECT*FROM Оценка WHERE Сотрудник.[N зач.книжки]=

Оценка.[N зач.книжки])

Отбираются студенты, которые имеют хотя бы одну оценку.