Реляционная алгебра. Основы SQL

Реляционная алгебра. Основы SQL

Контрольная работа

Реляционная алгебра. Основы SQL

Содержание

1. Реляционная алгебра

.1 Традиционные реляционные операции

.2 Специальные реляционные операции

.3 Дополнительные реляционные операции

. Основы SQL

.1 Типы данных

.2 Создание и обслуживание таблиц

.3 Запрос на выборку

.4 Статистические функции

.5 Создание соединений

.6 Вложенные запросы

.7 Запрос на объединение

.8 Запросы, выполняющие реляционные операции вычитания, пересечения и деления

.9 Запросы на изменение

.10 Перекрестные запросы

Литература

. Реляционная алгебра

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

По определению Е. Кодда, основу реляционной алгебры составляют восемь операторов. Причем, некоторые из них могут выполняться при определенных условиях. Например, в математике объединение двух множеств является множеством всех элементов, принадлежащих или обоим, или одному из исходных множеств. Объединение множества кортежей поставщиков в отношении ПОСТАВЩИКИ и множества кортежей деталей в отношении ДЕТАЛИ является множеством, но не являются отношением - отношения не могут содержать смесь кортежей разных типов. В реляционной алгебре для объединения требуется, чтобы два исходных отношения имели одну и ту же форму, то есть - были совместимы по типу. Два отношения совместимы по типу, если каждое из них имеет одно и то же множество имен атрибутов и соответствующие атрибуты определены на одном и том же домене.

1.1 Традиционные реляционные операции

К традиционным операциям над множествами относятся операторы объединения, пересечения, вычитания и произведения.

Объединением двух совместимых по типу отношений А и В (A UNION B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих А или В или обоим отношениям. При этом совпадающие кортежи записываются один раз.

Пересечением двух совместимых по типу отношений А и В (A INTERSECT B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, которые принадлежат одновременно обоим отношениям А и В.

Вычитанием двух совместимых по типу отношений А и В (A MINUS B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих отношению А и не принадлежащих отношению В.

Рис. 1

Произведение. Декартово произведение двух отношений А и В (A TIMES B), где А и В не имеют общих имен атрибутов, определяется как отношение с заголовком, который представляет собой сцепление (конкатенацию) двух заголовков исходных отношений А и В, и телом, состоящим из множества всех кортежей t таких, что t представляет собой сцепление кортежа a, принадлежащего отношению А, и кортежа b, принадлежащего отношению В. Кардинальное число результата равняется произведению кардинальных чисел исходных отношений, а степень равняется сумме их степеней.

1.2 Специальные реляционные операции

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

Выборка (RESTRICT или SELECT) - это сокращенное название q-выборки, где q обозначает любой скалярный оператор сравнения (=, ¹, ³, > и т.д.). q-выборкой из отношения А по атрибутам X и Y (A WHERE X q Y) (порядок учитывается!) называется отношение, имеющее тот же заголовок, что и отношение А, и тело, содержащее множество всех кортежей t отношения А, для которых проверка условия «X q Y» дает значение истина. Атрибуты X и Y должны быть определены на одном и том же домене, а оператор сравнения q должен иметь смысл для данного домена.

Проекцией (PROJECT) отношения А по атрибутам X, Y, …, Z, где каждый из атрибутов принадлежит отношению А, называется отношение с заголовком {X,Y,…,Z} и телом, содержащим множество кортежей с атрибутами, совпадающими с соответствующими атрибутами отношения А.

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

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

Естественным (или внутренним) соединением отношений А и В (A JOIN B) с заголовками X,Y и Y,Z соответственно и с атрибутами Y, определенными на одном и том же домене, называется отношение с заголовком {X,Y,Z} и телом, содержащим множество кортежей с атрибутами, совпадающими с соответствующими атрибутами отношений А и В.

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

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

Соединение обладает свойствами ассоциативности и коммутативности. Если отношения А и В не имеют общих имен атрибутов, то естественное соединение превращается в декартово произведение. Пример операции соединения показан на рисунке 2.

Рис. 2

Делением (DIVIDED BY) двух отношений, бинарного и унарного, является отношение, содержащее все значения одного атрибута бинарного отношения, которые соответствуют (в другом атрибуте) всем значениям в унарном отношении.

На рис. 2. показаны два примера деления отношений.

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

1.3 Дополнительные реляционные операции

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

Extend a add expr as z;

Результатом этого выражения будет отношение с заголовком, эквивалентным заголовку отношения А, расширенному новым атрибутом Z, который рассчитывается скалярным выражением expr для кортежа отношения А. При этом отношение А не должно иметь атрибута Z и выражение exp не должно ссылаться на атрибут Z. Кардинальное число результата равно кардинальному числу отношения А, степень равна степени отношения А плюс единица.

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

EXTEND Поставщики ADD COUNT ((Поставки RENAME П№ AS X) WHERE X= П№) AS Кол_П;

Результат действия этого выражения показан на рис. 3.

Рис. 3

Для данного поставщика в отношении ПОСТАВКИ выражение

((Поставки RENAME П№ AS X) WHERE X= П№);

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

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

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

SUMMARIZE A BY (A1,A2,…,An) ADD EXP AS Z;

Здесь A1,A2,…,An - отдельные атрибуты отношения А. Результатом этого выражения будет отношение с заголовком {A1,A2,…,An,Z} и с телом, содержащим все такие кортежи t, которые являются кортежами проекции отношения А по атрибутам A1,A2,…,An, расширенного значением для нового атрибута Z. Такое новое значение Z подсчитывается вычислением итогового значения exp по всем кортежам отношения А, которое имеет те же самые значения для атрибутов A1,A2,…,An, что и кортеж t. Кардинальное число результирующего отношения равно кардинальному числу проекции отношения А по атрибутам A1,A2,…,An, а степень равна степени такой проекции плюс единица. Например, количество поставок, сделанных каждым поставщиком может быть получено при помощи выражения

SUMMARIZE Поставки BY (П№) ADD COUNT AS Кол_П;

Результат операции (рис. 4) не содержит сведений о поставках, сделанных поставщиком П5 (сравните с примером, показанным на рис. 1, где поставщик сделал ноль поставок). Причина состоит в том, что поставщика П5 нет в отношении ПОСТАВКИ.

Рис. 4

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

Поставки := Поставки MINUS (Поставки WHERE Кол = 0);

В реляционных системах существуют также явные операторы обновления INSERT, DELETE и UPDATE:

INSERT (Поставщики where Гор_П = Минск ) into Temp;

(здесь выбранные данные вставлены в отношение Temp, которое должно быть совместимым по типу с отношением Поставщики).

Update (Поставщики where Гор_П = Брест) статус := 40;

DELETE Поставщики where Статус < 20;

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

         Примеры использования реляционной алгебры для выражения словесных запросов в виде формул

1. Получить имена поставщиков, которые поставляют деталь Д2.

((Поставки JOIN Поставщики) WHERE Д№=’Д2’) [Имя_П];

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

(((Детали WHERE Цв = ‘Черный’) JOIN Поставки) [П№] JOIN Поставщики) [Имя_П]

или

(((Детали WHERE Цв = ‘Черный’) [Д№] JOIN Поставки) JOIN Поставщики) [Имя_П]

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

((Поставки [П№,Д№] DIVIDEBY Детали [Д№] JOIN Поставщики) [Имя_П]

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

Поставки [П№,Д№] DIVIDEDBY (Поставки WHERE Имя_П=’П2’) [Д№]

. Получить имена поставщиков, которые не поставляют деталь Д2.

((Поставщики [П№] MINUS (Поставки WHERE Д№=’Д2’) [П№])

JOIN (Поставщики) [Имя_П]

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

SELECT DISTINCT Поставщики.[Имя_П] FROM Поставщики

WHERE Поставщики.[П.№] NOT IN

(SELECT Поставки.[П№] FROM Поставки

WHERE Поставки.[Д№]=’Д2’);

Глава 2. Основы SQL

Познакомимся с синтаксисом и конструкцией языка структурированных запросов SQL (Structured Query Language). Это позволит лучше понимать процесс обработки данных и создавать эффективные запросы.- наиболее распространенный язык управления базами данных архитектуры клиент-сервер. Стандарт современной версии ANSI SQL был принят в 1992 году. Эта версия дает возможность использовать общий набор операторов в любой SQL-совместимой программе управления базами данных. Так, MS ACCESS использует версию Microsoft Jet SQL. СУБД SQL Server 7.0 использует расширенную версию SQL - Transact-SQL. Разработчики SQL-совместимых программ управления базами данных имеют право свободно расширять этот язык, если поддерживается стандартный набор основных команд, описанный в ANSI SQL-92. Мы будем уделять большее внимание версии Jet SQL.

Стандарт ANSI SQL-92 содержит около 200 ключевых слов, а в Jet SQL используется менее половины из них. Кроме того, в Jet SQL зарезервирован набор слов, не имеющих аналогов в ANSI SQL-92.

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

Возможности современных реляционных баз данных позволяют осуществлять эффективное управление данными, используя одновременно два языка - язык реляционных баз данных SQL и язык программирования VBA (Visual Basic for Applications), разработанный для программных продуктов пакета MS Office.

Команда SQL - это последовательность заданных операторами действий, выполненных в определенном порядке.

Синтаксис языка включает слова и символы, которые можно применять, а также правила, по которым эти слова и символы можно использовать при создании команд и программ. Структура команды SQL показана на следующем примере (мы продолжаем использовать базу данных Проекты-Поставщики-Детали, смоделированную в прошлых главах): Найти поставщиков из Минска.

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

Ключевые слова - операторы, условия, модификаторы, итоговые функции - имеют в SQL определенное значение и применение, которое строго регламентировано.

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

Синтаксис SQL предусматривает использование знаков пунктуации:

·        запятые используются для разделения компонентов списка параметров;

·        точки используются для отделения имен таблиц от имен полей;

·        точка с запятой ставится в конце инструкции Jet SQL;

·        квадратные скобки используются для выделения имен полей только тогда, когда в именах используются пробелы или другие знаки пунктуации, не разрешенные в SQL;

·        одинарная (предпочтительней) или двойная кавычка применяется для описания строчных переменных;

·        символы * и ? используются для маскирования окончания или одного символа соответственно;

·        символ # применяется для представления одной цифры в операторе LIKE.

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

Семантика позволяет выяснить реальное значение, смысл любой синтаксически правильной команды (можно написать синтаксически правильную команду, которая будет выражать неправильный смысл).- непроцедурный, или декларативный язык. В нем нужно описывать то, что именно вы хотите сделать, а не то, как вы собираетесь это делать (в отличие от процедурных языков С, Perl, Pascal…). Оптимизатор, который входит в состав программного обеспечения СУБД, самостоятельно рассчитывает как это делать.

Язык SQL может быть встроенным (когда команды SQL как элементы включаются в какие-нибудь программы, написанные на процедурных языках общего назначения C, Java, Pascal… или языках сценариев Perl, PHP, Python…) и/или интерактивным.

2.1 Типы данных

Категории типов данных:

·        Character string - Строки символов;

·        Bit string - Строки битов;

·        Exact numeric - Рациональные (целые и действительные) числа с плавающей десятичной точкой;

·        Approximate numeric - Вещественные числа (с плавающей точкой);

·        Date time - значения даты и времени;

·        Interval - интервалы даты и времени.

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

Character (n) - строка фиксированной длины n. Максимальная длина строки определяется конкретной СУБД. В Access - 256 символов. Если символов меньше чем n, то добавляются пробелы. Синонимы - Char(n).

Character varying (n) - строка переменной длины, длинной менее n. Максимальное n зависит от СУБД. Синонимы: Char varying, Charvar.

National Character (National Char, NChar) - совпадает с типом Char, только хранит лишь стандартизованные многобайтовые или двухбайтовые знаки (Unicode). Записывается: N'a*b'. National Character Varying - то же для строк переменной длины.

Unicode - единое множество чисел (16-разрядных), которое представляет знаки почти всех мировых языков. Содержит 65536 = 216 знаков.

В СУБД Access к строковым типам данных относятся: text и memo.

         Битовые типы данных

BIT (n) - строка фиксированной длины (фиксированные числа битов). Max длина определяется СУБД. Если длина строки меньше n, то получите сообщение об ошибке. В стоке BIT перед первой кавычкой должна стаять латинская В, например, В'01001' - это строка типа BIT(5). Bit varying - аналогично, как Charvar.

Bit - тип данных используется для хранения так называемых больших бинарных объектов (Binary Large Object - BLOB) - например, звук, изображение.

В СУБД Access к bit типу данных относятся: yes, no, binary, OLE object.

         Точные числовые типы данных

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

Имеет фиксированные точность и масштаб:

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

·        масштаб - число цифр справа от десятичной точки (масштаб ≤ точности).

Типы:

Numeric (точность [,масштаб]) - представляет произвольное рациональное число.

Decimal - аналогичен NUMERIC, но только задает нижнюю границу точности, т. е. СУБД может выбрать большую точность, чем заказано пользователем.

Integer (или INT) - представляет произвольное целое число.

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

В СУБД Access: Decimal, Integer, byte, long integer.

Пример: Хранение числа 123,55:

         Вещественные числовые типы данных

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

FLOAT (точность) - представляет произвольное рациональное приближение действительного числа с плавающей точкой. Значение точности представляется не в количестве значащих десятичных цифр, а в количестве битов. Точность не должна быть меньше 1. Максимальная точность зависит от СУБД. Для преобразования десятеричной точности в бинарную надо умножить десятеричную точность на 3.32193. Например, 7 знаков точности дают 24 бита.

REAL - совпадает с FLOAT, но точность вводить не надо, ее автоматически определяет СУБД. Числа типа REAL называют числами одинарной точности с плавающей точкой.

DOUBLE PRECISION - вдвое превышает точность REAL - числа двойной точности с плавающей точкой.

В СУБД Access - single, double.

         Календарные типы данных

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

·        DATE - имеет формат YYYY-MM-DD.

·        TIME - имеет формат HH:MM:SS. Можно добавить аргумент “точность” для долей секунд.

·        TIMESTAMP - имеет формат YYYY-MM-DD_ HH:MM:SS.

·        B СУБД ACCESS: date/time.

·        Интервальные типы данных обозначают расстояние между датами или двумя отметками времени суток. Access не поддерживает.

         Значения NULL

Отображает отсутствующие или неизвестные данные.

·        В SQL ключевое слово NULL определяет значение Null.

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

·        Значения NULL можно найти и идентифицировать предложением IS NULL.

·        Значения NULL не равны друг другу. Поэтому нельзя определить, соответствует ли какое-нибудь значение NULL другому значению в базе данных. Тем не менее, предложение DISTINCT воспринимает все NULL одинаково для удаления повторяющихся строк.

·        При сортировке значения NULL будут либо больше, либо меньше любых значений, в зависимости от СУБД.

·        Значения NULL размножаются в процессе вычислений.

·        Итоговые функции игнорируют NULL в процессе вычислений.

·        При группировке предложением GROUP BY все значения NULL будут помещены в одну группу.

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

1.      Операторы определения данных - определяют содержимое реляционной базы данных в виде таблиц и представлений;

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

.        Операторы управления данными - ограничивают доступ к данным.

2.2 Создание и обслуживание таблиц

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

·        NOT NULL - не разрешает присваивать значения NULL;

·        DEFAULT - задает значения по умолчанию;

·        PRIMARY KEY - задает первичный ключ для таблицы;

·        FOREIGN KEY - задает внешний ключ;

·        UNIQUE - не позволяет вводить в столбец повторяющиеся значения;

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

Пример:

CREATE TABLE Проекты

(Пр№ CHAR(3)  NOT NULL PRIMARY KEY,

Имя_Пр CHAR(15)     UNUQUE,

Гор CHAR(20));

Внешний ключ создается ключевыми словами FOREIGN KEY или REFERENCES в строке описания столбца в команде CREATE TABLE:

CREATE TABLE Поставки

(П№ CHAR(3)    NOT NULL REFERENCE Поставщики,

Пр№ CHAR(5)   NOT NULL REFERENCE Проекты,

Д№ CHAR(3)               NOT NULL REFERENCE Детали,

Кол INTEGER DEFAULT ′???′,KEY (П№ , Пр№, Д№));

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

реляционный запрос sql данные

[CONSTRAINT имя_ограничения] REFERENCES имя_табл (имя_столбца).

Сохранение целостности данных по ссылкам организуется при помощи операторов RESTRICT, CASCADE и SET NULL:

CREATE TABLE Поставки

(П№ CHAR(3)    REFERENCE Поставщики ON UPDATE CASCADE

ON DELETE SET NULL,

Пр№ CHAR(5)   NOT NULL REFERENCE Проекты RESTRICT,

Д№ CHAR(3)               NOT NULL REFERENCE Детали,

Кол INTEGER DEFAULT ′???′,KEY (П№ , Пр№, Д№));

Сейчас при изменении П№ в таблице Поставщики будет изменен и П№ в таблице Поставки, а при его удалении в таблицу Поставки будет внесено значение NULL. Оператор RESTRICT устанавливает, что в таблице Проекты, на которую ссылается внешний ключ, нельзя изменять значение первичного ключа, если в ссылающейся таблице Поставки существует строка с этим значением.

Для обеспечения целостности атрибута предназначен оператор CHECK:

CREATE TABLE Детали

         (Д№ CHAR(3)     NOT NULL PRIMARY KEY,

         Имя_Д CHAR(15)        UNUQUE,

         Цвет CHAR(10) CHECK (Цвет='Черный' OR Цвет ='Красный'           OR Цвет                             ='Желтый' OR Цвет ='???'),

         Вес INTEGER,

         Гор CHAR(20));

Ограничения на данные можно организовать и созданием доменов:

CREATE DOMAIN Color CHAR(10) CHECK (Color='Черный' OR Color='Красный'                    OR Color='Желтый' OR Color='???');

Тогда ограничение на поле «Цвет» вводится следующим образом:

CREATE TABLE Детали

         (Д№ CHAR(3)...,

         Цвет Сolor,

         …);

Внести изменение в структуру таблицы можно оператором ALTER с указанием характера изменения - ADD, MODIFY или DELETE:

ALTER TABLE Детали ADD [Дата изготовления] DATE;

Для удаления объектов базы данных используется оператор DROP:

DROP TABLE Детали;

Доступ к данным в многопользовательской системе регулируется с помощью операторов GRANT и REVOKE. Указываются вид полномочий (SELECT, UPDATE, ALL), таблица или представление, по отношению к которым создается полномочие, и имя пользователя:

GRANT UPDATE ON Поставки TO USER1;

Полномочия для всех пользователей устанавливаются через специального пользователя PUBLIC:

GRANT SELECT ON Поставки TO PUBLIC;

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

Оператор REVOKE аннулирует полномочия:

REVOKE UPDATE ON Поставки FROM USER1;

Если аннулировать полномочия через ...FROM PUBLIC, то полномочий лишаются все пользователи.

2.3 Запрос на выборку

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

SELECT [ALL/DISTINCT] [TOP n [PERCENT]] список полейимена таблиц

[WHERE условие отбора]

[ORDER BY столбцы сортировки [ASC/DESC]];

В списке полей команды SELECT указываются поля, которые должны быть включены в результирующую таблицу запроса и их имена в этой новой таблице (представлении). В этом случае SELECT выполняет операцию проекции, а условие отбора WHERE - операцию выборки). Имена полей разделяются запятыми. Необязательные параметры ALL и DISTINCT определяют способ отбора строк:

·        ALL - включает все строки, соответствующие указанным далее условиям отбора;

·        DISTINCT (ключевое слово из ANSI SQL-92) - исключает строки с повторяющимися данными на основе только данных результирующего набора записей;

Необязательный параметр TOP n [PERCENT] ограничивает количество записей в результирующей таблице первыми n или n% набора.

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

WHERE определяет условие для отбора записей и реализует реляционную операцию выборки. Условие задается текстовым оператором типа LIKE для текстовых полей или числовыми операторами типа >, <, =, < >, >=, BETWEEN для числовых полей. Если WHERE не использован, то запрос возвратит все записи, удовлетворяющие критерию SELECT.

Модификатор ORDER BY определяет порядок сортировки записей в созданной таблице. Ключевыми словами ASC и DESC можно определить сортировку по возрастанию или убыванию соответственно.

Пример инструкции запроса на выборку:

Результатом выполнения запроса будет новая таблица, содержащая два столбца Имя_Д и Вес и множество строк, удовлетворяющих условию Вес>500, отсортированных по убыванию веса.

2.4 Статистические функции

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

SELECT статистическая функция (имя поля) AS заголовок поля [, список полей]

FROM имена таблиц

[WHERE условие отбора]

GROUP BY условие группировки

[HAVING условие для результата]

[ORDER BY столбцы сортировки];

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

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

Примеры команд SQL, применяющих статистические функции:

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

SUM(Поставки.Кол) FROM Поставки;

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

SELECT MIN(Кол), MAX(Кол), SUM(Кол), AVG(Кол) FROM Поставки;

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

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

SELECT COUNT (*) AS Кол_кортежей FROM Поставки;

В результате получим таблицу из одной строки с заголовком “Кол_кортежей”.

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

SELECT Пк.ПN, SUM(Пк.Кол)Поставки AS ПкBY Пк.ПN;

(Здесь показан пример использования псевдонимов, которые вводятся в предложении FROM при помощи оператора AS (его можно и пропустить). Использование псевдонимов упрощает запись команд. Они также используются и для организации некоторых запросов.)

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

На создаваемые оператором GROUP BY результаты можно накладывать ограничения оператором HAVING, например:

SELECT Пк.ПN, SUM (Пк.Кол)Поставки AS ПкBY Пк.ПNCOUNT(*)>2;

Предложение HAVING COUNT(*)>2 выделяет только те группы, в которых количество кортежей больше 2 (поставщики выполнили более 2 поставок).

2.5 Создание соединений

Реляционная операция произведения двух отношений TIMES реализуется в SQL, если указать имена этих отношений в предложении FROM:

* FROM Проекты, Поставки;

Если дополнить эту команду предложением WHERE, сравнивающим значения атрибутов этих отношений, то будет реализована реляционная операция соединения JOIN: Например, соединение отношений Проекты и Поставки:

* Проекты, ПоставкиПроекты.ПрN=Поставки.ПрN;

Подобным образом можно соединить произвольное число отношений:

SELECT DISTINCT П.Имя_П, Д.Имя_Д, Пр.Имя_Пр, Пк.КолПоставщики П, Детали Д, Проекты Пр, Поставки Пк

WHERE Д.ДN=Пк.ДN

AND П.ПN=Пк.ПN

AND Пр.ПрN=Пк.ПрN

AND Пк.Кол>500;

Такое соединение выполняется с конца. Сначала из таблицы Поставки удаляются строки со значениями поля Кол менее или равным 500. Затем соединяются кортежи отношения Поставки с теми кортежами отношения Проекты, у которых совпадают значения атрибута ПрN. После этого кортежи созданного представления соединяются с кортежами отношения Поставщик, у которых совпадают значения атрибута ПN. Далее, полученные кортежи соединяются с кортежами отношения Детали, у которых совпадают значения атрибута ДN.

Для организации соединений между таблицами и их объединения может быть использован и оператор JOIN…ON, который указывает на подключаемую таблицу и связь между полями:

SELECT список полей

FROM имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица

ON условие связи

[WHERE условие отбора]

[ORDER BY столбцы сортировки];

В приведенной инструкции показано, как оператор JOIN окружен именами двух связываемых таблиц, причем вместо правого имени может использоваться повторно конструкция JOIN … ON, называемая вложенной: [имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица ON условие связи]. В этом случае первая таблица соединяется с соединением второй и третьей таблиц. Инструкция SQL может содержать набор нескольких вложенных конструкций JOIN … ON. Их число обычно равно общему количеству таблиц, включенных в запрос, минус один. Перед оператором JOIN должен быть указан тип соединения:

·        INNER - соединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения;

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

Конструкция ON условие связи позволяет описать два поля и связь между ними (одно поле в таблице связанная таблица, второе - в таблице имя таблицы). В выражении условие связи присутствует оператор сравнения значений полей, который возвращает значения True или False. Если значение выражения True, то объединенная запись включается в результирующий набор.

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

SELECT DISTINCT Пр.Имя_Пр, Пр.Гор, Пк.ДN, Пк.Кол(Проекты Пр INNER JOIN Поставки Пк)

ON Пр.ПрN=Пк.ПрN;

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

SELECT F.ПN, S.ПN, F.ДNПоставки AS F, Поставки AS SF.ДN=S.ДN;

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

Добавив условие выборки

AND F.ПN <> S.ПN

убираем строки, содержащие одинаковые номера поставщиков. Модификатор DISTINCT удалит повторяющиеся кортежи. Если бы в полях F.ПN и S.ПN были данные числового типа, то условие выборки AND F.ПN < S.ПN позволило бы получить ответ без последующих операций.

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

2.6 Вложенные запросы

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

Синтаксис записи вложенных запросов:

SELECT список полей

FROM список таблиц

WHERE [имя таблицы.] имя поля

                   IN (SELECT оператор выборки

                            [GROUP BY условие группировки]

                            [HAVING условие отбора]

[ORDER BY столбцы сортировки]);

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

SELECT Пк.ПN FROM Поставки Пк

WHERE Пк.ДN IN

                   (SELECT Д.ДN FROM Детали Д

                            WHERE Д.Цвет='Черный');

Механизм действия вложенного запроса такой. Внутренний запрос вложен во внешний запрос, который последовательно сравнивает значения своего атрибута (Пк.ДN) с возвращенным внутренним запросом множеством этих же атрибутов (Д.ДN). Если рассматриваемый атрибут внешнего запроса есть в указанном множестве, то будет возвращен выбранный в предложении SELECT атрибут (Пк.ПN).

Чтобы в нашем примере найти имена поставщиков, надо добавить еще один вложенный запрос:

SELECT П.Имя_П FROM Поставщики П

WHERE П.ПN IN

                   (SELECT Пк.ПN FROM Поставки Пк

                   WHERE Пк.ДN IN

                            (SELECT Д.ДN FROM Детали Д

                            WHERE Д.Цв='Черный'));

Такой же результат можно получить при помощи операции соединения:

SELECT DISTINCT П.Имя_П

FROM Поставщики П, Поставки Пк, Детали Д

WHERE П.ПN=Пк.ПN

                   AND Пк.ДN=Д.ДN

                   AND Д.Цв='Черный';

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

SELECT * FROM Поставщики П

WHERE П.ПN NOT IN

                   (SELECT Пк.ПN FROM Поставки Пк);

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

2.7 Запрос на объединение

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

SELECT оператор выборки

UNION

SELECT оператор выборки

                   [GROUP BY условие группировки]

                   [HAVING итоговое условие]

[UNION

SELECT оператор выборки

                   [GROUP BY условие группировки]

                   [HAVING итоговое условие]]

[UNION

…]

[ORDER BY столбцы сортировки];

При использовании операторов UNION необходимо задавать одинаковый набор имен полей в списке отбираемых полей, причем, их последовательность должна быть одинакова в каждом предложении UNION SELECT. Модификатор ORDER BY может быть использован только один раз в инструкции за последним оператором UNION SELECT. При необходимости в каждый оператор SELECT и UNION SELECT можно вложить операторы GROUP BY и HAVING. Оператор UNION удаляет из результирующей таблицы все строки-дубликаты. Чтобы это не происходило, используют оператор UNION ALL.

Синтаксис оператора UNION позволяет собирать в одном поле объединенной таблицы значения из различных доменов:

SELECT Имя_П AS Наименование

         FROM Поставщики

         WHERE Гор=’Минск’

UNION SELECT Имя_Пр AS Наименование

         FROM Проекты

         WHERE Гор=’Минск’;

Результатом запроса будет таблица из одного столбца, названного «Наименование», содержащая поставщиков, находящихся в Минске, и проектов, выполняемых в Минске.

2.8 Запросы, выполняющие реляционные операции вычитания, пересечения и деления

В некоторых модификациях языка SQL, например, используемой в СУБД Oracle, реляционная операция пересечения отношений выполняется оператором INTERSECT, который возвращает только те строки, которые присутствуют в обеих таблицах. Вычитание отношений реализуется оператором EXCEPT, который возвращает строки первой таблицы за исключением тех, которые присутствуют и во второй таблице. Как и в случае оператора UNION, обрабатываемые таблицы должны иметь одинаковый набор и последовательность имен полей. В СУБД Access эти операции могут быть реализованы при помощи оператора EXISTS.

Оператор EXISTS в предложении WHERE выполняет проверку на существование данных, которые удовлетворяют критериям соответствующего вложенного запроса, и возвращает булево значение «истина» или «ложь».

Пример. Найти имена поставщиков, которые поставляют деталь Д1:

SELECT DISTINCT П.Имя_П FROM Поставщики AS ПEXISTS

(SELECT * FROM Поставки AS Пк

                   WHERE Пк.ПN=П.ПN

                            AND Пк.ДN='Д1');

Для каждого кортежа отношения Поставщики, которое обрабатывается во внешнем запросе, для вложенного запроса будет возвращено значение “истина”, если существует хотя бы один кортеж в отношении Поставки с тем же значением П№, который рассматривает внешний запрос. Чтобы это определить, выполняется соединение по Пк.П№=П.П№. Во вложенном запросе используется предложение SELECT *, но можно указать имя любого атрибута, т.к. этот вложенный запрос возвращает не данные, а значение истинности.

Чтобы получить имена поставщиков, которые не поставляют деталь Д1, можно использовать отрицание предложения EXISTS:

SELECT DISTINCT П.Имя_П FROM Поставщики AS ПNOT EXISTS

(SELECT * FROM Поставки AS ПК

                   WHERE ПК.ПN=П.ПN

                            AND ПК.ДN='Д1');

Пример решения задачи двумя способами - с оператором EXISTS и без него: Найти номера деталей, поставляемых поставщиком из города, название которого начинается на букву М.

I. SELECT DISTINCT Пк.ДN FROM Поставки AS Пк WHERE Пк.ПN IN (SELECT П.ПN FROM Поставщики AS П WHERE П.Гор LIKE 'М*');. SELECT DISTINCT Пк.Д№ FROM Поставки Пк WHERE EXISTS (SELECT * FROM Поставщики П WHERE П.П№ = Пк.П№ AND Гор LIKE ′М*′);

Если надо указать имя детали, т.е. получить сведения из 3-й таблицы, то команда SQL выглядит так:

SELECT DISTINCT Д.Имя_Д FROM Детали AS ДEXISTS

(SELECT DISTINCT Пк.ДN FROM Поставки AS Пк

                   WHERE EXISTS

                   (SELECT * FROM Поставщики AS П

                            WHERE П.ПN=Пк.ПN

                                      AND Гор LIKE 'М*')

                                      AND Д.ДN=П.ДN);

Покажем, как при помощи оператора EXISTS можно реализовать операции пересечения, разности и деления.

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

SELECT DISTINCT Д.Гор FROM Детали AS ДEXISTS

(SELECT * FROM Поставщики П

                   WHERE Д.Гор=П.Гор);

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

SELECT DISTINCT Д.Гор FROM Детали ДNOT EXISTS

(SELECT * FROM Поставщики П

                   WHERE Д.Гор=П.Гор);

Операция пересечения может быть реализована и без оператора EXISTS:

SELECT DISTINCT Д.Гор FROM Детали Д(SELECT COUNT (*) FROM Поставщики П

                   WHERE Д.Гор = П.Гор) >0;

(если количество найденных строк больше 0, значит они есть и оператор EXISTS принял бы значение “истина”).

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

Пример реализации реляционной операции деления DIVIDED BY: Получить номера поставщиков, поставляющих все детали

SELECT DISTINCT Пк.ПNПоставки AS ПкNOT EXISTS

(SELECT Д.ДN FROM Детали AS Д

                   WHERE NOT EXISTS

                   (SELECT Пк1.ДN FROM Поставки AS Пк1

                            WHERE Пк1.ПN=Пк.ПN

                                      AND Пк1.ДN=Д.ДN));

Внешний запрос исследует каждый кортеж отношения Поставки и возвращает из него ПN, если вложенный запрос возвращает значение «Ложь». Вложенный запрос создает множество всех возможных значений ДN из отношения Детали. Из этого множества удаляются те значения ДN, которые состоят в паре с ПN в кортеже с тем же значением ПN, что и кортеж, рассматриваемый во внешнем запросе. Если рассматриваемый ПN комбинируется со всеми возможными значениями ДN, то результатом будет пустое множество и вложенный запрос возвратит значение «Ложь», т.е., не существует значений ДN, которые не состоят в паре с этим конкретным ПN.

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

SELECT П.Имя_ППоставщики AS ПП.ПN IN

(SELECT DISTINCT Пк.ПNПоставки AS ПкNOT EXISTS

(SELECT Д.ДN FROM Детали AS Д

                   WHERE NOT EXISTS

                   (SELECT Пк1.ДN FROM Поставки AS Пк1

                            WHERE Пк1.ПN=Пк.ПN

                                      AND Пк1.ДN=Д.ДN)));

2.9 Запросы на изменение

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

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

INSERT INTO таблица-получатель

SELECT список полей

FROM таблица-источник;

Если в инструкции на добавление отсутствует предложение WHERE, то в таблицу-получатель будут добавлены все записи таблицы-источника.

Синтаксис запроса на удаление записей:

DELETE FROM имя таблицы [WHERE условие удаления];

В этой инструкции предложение WHERE также не обязательное. При его отсутствии из указанной таблицы будут удалены все записи.

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

SELECT список полей

INTO новая таблица

FROM исходная таблица

[WHERE условие выбора];

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

Запрос на обновление используется для присвоения новых значений отдельным столбцам при помощи операторов UPDATE и SET:

 имя таблицы

SET имя_поля_1=значение [,имя_поля_2=значение[,…]]

[WHERE условие обновления];

2.10 Перекрестные запросы

Перекрестные запросы позволяют создавать различные итоговые запросы, использующие статистические функции SQL. Когда данные группируются с помощью перекрестного запроса, можно выбирать значения из заданных полей или выражений как заголовки столбцов. Это позволяет просматривать данные в более компактной форме, чем при работе с запросом на выборку. <JavaScript:hhobj_3.Click()> Для организации перекрестных запросов используются операторы Jet-SQL TRANSFORM и PIVOT:

TRANSFORM статистическая функция (имя поля) [AS наименование]

SELECT список полей

FROM имя таблицы

PIVOT поле [IN (значение_1[, значение_2[, ...]])];

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

TRANSFORM Sum(Пк.Кол) Пк.ПN, Sum(Пк.Кол) AS [ВСЕГО:]Поставки AS ПкBY Пк.ПNПк.ДN;

Результатом такого запроса будет таблица:

Инструкция TRANSFORM является необязательной, но если она присутствует, то должна быть первой инструкцией в команде <JavaScript:hhobj_4.Click()> SQL. Она должна находиться впереди инструкции SELECT, которая указывает поля, содержащие заголовки строк, и предложения GROUP BY, которое задает группировку по строкам. При желании можно включить и другие предложения, например WHERE для описания дополнительных условий отбора и сортировки. Кроме того, можно использовать подчиненные запросы <JavaScript:hhobj_5.Click()> как предикаты в перекрестном запросе, особенно в предложении WHERE.

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

Используя SQL, можно обновить данные или удалить записи таблиц, относящихся к другим базам данных любого типа (FoxPro, dBase, Paradox,…). Для определения файла внешней таблицы или базы данных используется оператор IN. Средствами MS ACCESS можно создавать или изменять таблицы dBase или Paradox, указав в операторе IN путь к их файлу и тип файла.

Литература

1. Агальцов, В.П. Базы данных. В 2-х т. Т. 2. Распределенные и удаленные базы данных: Учебник / В.П. Агальцов. - М.: ИД ФОРУМ, НИЦ ИНФРА-М, 2013. - 272 c.

. Агальцов, В.П. Базы данных. В 2-х т.Т. 1. Локальные базы данных: Учебник / В.П. Агальцов. - М.: ИД ФОРУМ, НИЦ ИНФРА-М, 2013. - 352 c.

. Голицына, О.Л. Базы данных / О.Л. Голицына, Н.В. Максимов, И.И. Попов. - М.: Форум, 2004. - 352 c.

. Голицына, О.Л. Базы данных: Учебное пособие / О.Л. Голицына, Н.В. Максимов, И.И. Попов. - М.: Форум, 2012. - 400 c.

. Карпова, И.П. Базы данных: Учебное пособие / И.П. Карпова. - СПб.: Питер, 2013. - 240 c.

. Кириллов, В.В. Введение в реляционные базы данных.Введение в реляционные базы данных / В.В. Кириллов, Г.Ю. Громов. - СПб.: БХВ-Петербург, 2012. - 464 c.

. Кошелев, В.Е. Базы данных в ACCESS 2007: Эффективное использование / В.Е. Кошелев. - М.: Бином-Пресс, 2009. - 592 c.

. Кузин, А.В. Базы данных: Учебное пособие для студ. высш. учеб. заведений / А.В. Кузин, С.В. Левонисова. - М.: ИЦ Академия, 2012. - 320 c.

. Ливена, С.В. Практика увольнений за прогул. По материалам базы данных "Пакет кадровика" / С.В. Ливена. - М.: ИНФРА-М, 2008. - 51 c.

. Пирогов, В.Ю. Информационные системы и базы данных: организация и проектирование: Учебное пособие / В.Ю. Пирогов. - СПб.: БХВ-Петербург, 2009. - 528 c.

. Советов, Б.Я. Базы данных: теория и практика: Учебник для бакалавров / Б.Я. Советов, В.В. Цехановский, В.Д. Чертовской. - М.: Юрайт, 2013. - 463 c.

. Фуфаев, Э.В. Базы данных: Учебное пособие для студентов учреждений среднего профессионального образования / Э.В. Фуфаев, Д.Э. Фуфаев. - М.: ИЦ Академия, 2012. - 320 c.