SUMMARIZE SP BY (M#) ADD SUM (Количество) AS Общее количество

определяется общее количество по каждому виду материала.

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

SUMMARIZE SP BY (M#) ADD SUM (Количество) AS Общее количество, AVG (Количество) AS (Сред_знач).

Оператор реляционногоприсвоения можно представить следующим образом:

<выражение-цель>:= <выражение-источник>,

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

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

S:=S UNION{(<S#:'S6'>, <Поставщик: Донецкая мануфактура>, <Рейтинг:40>, <Город_П: Донецк>)}

Оператор вставки имеет следующий вид:

INSERT <выражение-источник> INTO <выражение-цель>,

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

INSERT (S WHERE Город_П ='Москва') INTO T

Оператор обновления имеет следующий вид:

UPDATE <выражение-цель> <список элементов>,

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

Например, UPDATE M WHERE Тип='п/ш' Город_П:='Ростов'. Эта операция предписывает изменить значение атрибута Город_П (независимо от того, каким оно было) на новое значение – 'Ростов' таких кортежей отношения M, атрибут Тип которыхимеет значение 'п/ш'.

Оператор удаления имеет следующий вид:

DELETE <выражение-цель>,

где <выражение-цель> представляет собой реляционное выражение. Все кортежи в результирующем отношении удаляются. Например, DELETE S WHERE Рейтинг <20.

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

<выражение1> Q<выражение2>,

где выражения представляют совместимые по структуре отношения, а знак Q – это один из следующих операторов сравнения: = (равно), ¹ (не равно), £ (подмножество), < (собственное подмножество), ³ (надмножество), > (собственное надмножество).

Например, сравнение: "Совпадают ли города поставщиков и города, в которых производятся ткани" можно записать так:

S[Город_П] = M[Город_М].

Сравнение "Есть ли поставщики, не осуществляющие поставки материалов?" записывается следующим образом:

S[M#]=SP[M#].

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

1. Получить названия поставщиков, осуществляющих поставки материала M2:

((SP JOIN S) WHERE M#='M2') [Поставщик].

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

((S [S#] MINUS (SP WHERE M#='M2') [S#]) JOIN S) [Поставщик].

3. Получить названия поставщиков, поставляющих все детали:

((SP [S#, M#] DIVIDEBY M [M#] JOIN S [Поставщик].

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

(((M WHERE Тип='п/ш') JOIN SP) [S#] JOIN S) [Поставщик].

В реализациях конкретных реляционных СУБД реляционная алгебра и реляционное исчисление в "чистом" виде не используются. Фактическим стандартом доступа к реляционным данным стал язык SQL (Structured Query Language), представляющий собой смесь операторов реляционной алгебры и выражений реляционного исчисления, использующий синтаксис, близкий к фразам английского языка и расширенный дополнительными возможностями, отсутствующими в реляционной алгебре и реляционном исчислении.

3.3.3. Язык запросов по образцу QBE

Для манипулирования данными в базах данных используются запросы. Запрос представляет собой сообщение конечного пользователя или приложения, направляемое СУБД и активизирующее в базе данных следующие действия: выборку, вставку, удаление или обновление указанных в запросе данных. Запросы описываются с помощью языков запросов: QBE (Query By Example) – язык запросов по образцу; SQL (Structured Query Language) – структурированный язык запросов. Оба языка являются непроцедурными, т. е. описывают свойства результата ("что надо сделать"), а не алгоритм решения задачи ("как это сделать").

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

Язык QBE позволяет создавать запросы к БД путем заполнения предлагаемой СУБД запросной формы. Традиционные компьютерные языки являются текстовыми, в них решение задач формулируется в виде символьных строк. QBE является графическим языком, в котором запросы формулируются посредством графического представления таблиц базы данных. Такой способ задания запросов обеспечивает высокую наглядность и не требует знания программирования – достаточно описать образец ожидаемого результата. В каждой из современных реляционных СУБД имеется свой вариант языка QBE, незначительно отличающийся от первого описания QBE, предложенного М.М. Злуффом в 1975-1977 гг. Помещая символы а определенные места в столбцах таблицы – шаблона запроса, пользователь может определять условия отбора строк для запроса, группировки данных, формат вывода данных, операции обновления данных.

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

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

Для иллюстрации средств и возможностей языков QBEи SQL используем БД небольшой торговой фирмы. В базе данных хранится следующая информация: информация о клиентах, с которыми работает данная фирма; информация о заказах, сделанных клиентами; информация о товарах данной фирмы [ табл. 6-8 ]. В таблицах приведены неполные и упрощенные данные.

В таблице CUST (табл. 6) хранятся данные о клиентах: номер клиента – CUST_NUM; название фирмы-клиента – CUST_NAME; общий объем заказов, сделанных клиентом за год – CUST_SUM.

В таблице PROD (табл. 7) хранятся сведения о товарах фирмы: идентификатор товара – PROD_ID; наименование товара – PROD_NAME; цена товара – PRICE; количество единиц товара на складе – STORE.

Таблица ORDERS (табл. 8) содержит сведения о заказах: номер заказа – ORDER_NUM; номер клиента, сделавшего заказ – CUST_NUM; идентификатор заказанного продукта – PROD_ID; количество заказанного продукта – QTY; дата поставки – DATE_ORDER. Для простоты будем считать, что в одном заказе может упоминаться только один товар.

Таблица 6

CUST

CUST_NUM	CUST_NAME	CUST_SUM
	ООО "PC-Style"
	ООО "Гермес"
	ЧП Федоров В.Г.
	ООО "Формат"
	ЧП Гришин П.В.
	ОАО "Энтрон"
	ЗАО"IT-COM"
	ООО "Омега"
	ОАО " PC-Центр"

Таблица 7

PROD

PROD_ID	PROD_NAME	PRICE	STORE
3P	Процессор Celeron 2400
4P	Процессор Athlon XP 2600+
6P	Процессор Pentium-4 2600
4MB	Материнская плата GA 81 PE 1000
7MB	Материнская плата EPOX 8KRA2+
3V	Видеокарта Nvidia GeForce FX5600 128mb
4V	Видеокарта Ati Radeon 9500 64mb
1M	ОЗУ DDR 256 MB PC 2700
2M	ОЗУ DDR 256 MB PC 3200
3M	ОЗУ DDR 512 MB PC 3200

Таблица 8

ORDERS

ORDER_NUM	CUST_NUM	PROD_ID	QTY	DATE_ORDER
		3P		20.12.02
		4MB		20.12.02
		6P		21.12.02
		3V		5.01.03
		4P		17.01.03
		1M		14.02.03
		7MB		18..02.03
		2M		1.03.03
		3P		5.03.03
		3M		5.03.03
		4MB		7.03.03

QBE предлагает пользователю для создания запроса заполнение таблиц-шаблонов. В первом столбце таблицы-шаблона выводится имя таблицы, во всех остальных – имена столбцов.

Пример 3.8. Пример заполнения шаблона запроса

Запрос "Вывести названия всех клиентов" можно сформулировать с помощью следующего шаблона (рис. 23):

CUST УРЕ

CUST_NUM

CUST_NAME

CUST_SUM

В приведенном шаблоне "P." – это команда вывода, означающая, что выводятся значения заданного столбца. После команды в языке QBE ставится точка.

Результат выполнения приведенного шаблона следующий:

Обратим внимание на то, что результатом запроса является реляционная таблица.

Рассмотрим различные варианты создания запросов.

Пример 3.9. Запрос с простым условием сравнения

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

Результатом запроса является следующий набор данных:

PROD_NAME

ОЗУ DDR 256 MB PC 2700

ОЗУ DDR 256 MB PC 3200

Пример 3.10. Запрос с составным условием сравнения (логическая операция И)

Требуется вывести товары, цена которых больше 100 долларов и количество которых на складе больше 1000 единиц (рис. 25).

Результат запроса:

PROD_ID	PROD_NAME	PRICE	STORE
4P	Процессор Athlon XP 2600+

Выводятся все данные выбранной строки, т.к. в шаблоне запроса команда вывода "P." стоит в первом столбце шаблона.Если два условия стоят на одной строке шаблона, то для выбора строки необходимо выполнение обоих условий (логическая операция И).

Пример 3.11. Запрос с составным условием сравнения (логическая операция ИЛИ)

Вывести все товары, цена которых больше 100 долларов или количество которых на складе больше 1000 единиц (рис. 26).

Условия, записанные на двух разных строках шаблона запроса, соответствуют условиям, объединенным логическим оператором ИЛИ. Команда вывода P. расположена на обеих строках и в каждом из столбцов, которые должны выводится.

Результат запроса следующий:

PROD_NAME	PRICE	STORE
Процессор Athlon XP 2600+
Процессор Pentium-4 2600
Материнская плата GA 81 PE 1000
Материнская плата EPOX 8KRA2+
Видеокарта Nvidia GeForce FX5600 128mb
Видеокарта Ati Radeon 9500 64mb
ОЗУ DDR 256 MB PC 2700
ОЗУ DDR 256 MB PC 3200
ОЗУ DDR 512 MB PC 3200

Пример 3.12. Запрос с использованием блока условий

Например, требуется вывести товары с ценой от 100 до 150 долларов. Для этого введем блок условий с явным заданием операции "И". Этот блок, обозначенный CONDITIONS содержит любые требуемые ограничения данных. В нашем примере к значениям одного и того же столбца должны одновременно применяться два условия. Поэтому удобно применить одно условие в блоке условий (рис. 27).

В этом примере введена переменная _S, которая является элементом-образцом и обозначает неопределенное значение в столбце таблицы. В данном примере элемент-образец обозначает любое возможное значение столбца PRICE.

Результат:

PROD_ID	PROD_NAME	PRICE	STORE
4P	Процессор Athlon XP 2600+
4MB	Материнская плата GA 81 PE 1000
3V	Видеокарта Nvidia GeForce FX5600 128mb
4V	Видеокарта Ati Radeon 9500 64mb

Пример 3.13. Сравнение с элементами-образцами

Определить товар, цена которого больше, чем цена видеокарты Nvidia GeForce FX5600 128mb. Пример шаблона приведен на рис. 28.

По-другому запрос можно сформулировать следующим образом: "Цена видеокарты Nvidia GeForce FX5600 128mb обозначена как _В. Вывести все товары, цена которых больше, чем _В."

Результат:

PROD_NAME	PRICE
Процессор Pentium-4 2600
Видеокарта Ati Radeon 9500 64mb

Рассмотрим создание многотабличных запросов.

Пример 3.14. Объединение двух таблиц

Допустим, необходимо вывести название фирм-клиентов, заказавших количество товара больше 20 единиц. Для создания этого запроса необходимо объединить две таблицы. Элемент-образец _CN связывает две таблицы (рис. 29).

CUST	CUST_NUM	CUST_NAME	CUST_SUM
	_CN	P.

ORDERS	ORDER_NUM	CUST_NUM	PROD_ID	QTY	DATE_ORDER
		_CN		P. >20

Рис. 29. Связывание двух таблиц

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

Результат:

CUST_NAME	QTY
ЧП Гришин П.В.
ЧП Гришин П.В.
ООО "Гермес"

Пример 3.15. Запрос с использованием трех таблиц

Вывести названия клиентов, заказавших процессор Celeron 2400 (рис. 30).

CUST	CUST_NUM	CUST_NAME	CUST_SUM
	_CN	P.

ORDERS	ORDER_NUM	CUST_NUM	PROD_ID	QTY	DATE_ORDER
		_CN	_PI

PROD	PROD_ID	PROD_NAME	PRICE	STORE
	_PI	Процессор Celeron 2400

Рис. 30. Связывание трех таблиц

Результат:

CUST_NAME

ООО "PC-Style"

ЧП Федоров В.Г.

Пример 3.16. Связывание таблиц с вычислениями

Определить объем каждого заказа в таблице ORDERS (рис.31).

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

Результат:

ORDER_NUM

221 Объем заказа=1350

222 Объем заказа=3000

223 Объем заказа=3200

224 Объем заказа=1680

225 объем заказа=2970

226 Объем заказа=450

227 Объем заказа=1710

228 Объем заказа=1050

229 Объем заказа=1530

230 Объем заказа=1552

231 Объем заказа=1000

Пример 3.17. Запрос с частичным совпадением

Вывести номера заказов, в которых требуется поставка только процессоров (рис. 32).

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

При записи выражений на QBEмогут использоваться встроенные функции: CNT (количество), SUM (сумма), AVG (среднее), MIN (минимум), MAX (максимум), UN (уникальный), ALL (все значения, в том числе и повторяющиеся).

Пример 3.18. Запрос с использованием функции MAX

Вывести максимальный объем из годовых заказов клиентов. Шаблон запроса представлен на рис. 33.

Результат запроса, состоящий из одного значения 160 000, тоже можно считать реляционной таблицей из одного столбца и одной строки.

Пример 3.19. Запрос с использованием функции AVG

Каков средний годовой объем заказов клиентов?

Шаблон запроса представлен на рис. 34.

Пример 3.20. Запрос с использованием функции CNT

Сколько клиентов заказало процессор Celeron 2400?

Шаблон запроса показан на рис. 35.

Пример 3.21. Запрос с использованием оператора UNQ

Сколько всего клиентов сделало заказы?

Шаблон запроса приведен ниже (рис. 36).

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

Пример 3.22. Запрос с группировкой

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

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

"G." обозначается столбец, по которому производится группировка. В нашем случае, группировка проводится по CUST_NUM, т. к. мы хотим подсчитать общее количество товара по каждому клиенту. Затем используется целевая таблица, задающая вывод столбца, по которому производится группировка и значений функций, применных к группам (рис. 37)

Результат:

CUST_NUM	QTY

Пример 3.23. Запрос с группировкой и условием

Вывести номера клиентов, сделавших более одного заказа (рис. 38).

Результат:

CUST_NUM

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

Пример 3.24. Вставка данных в таблицу

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

Пример 3.25. Удаление информации из таблицы

Пусть необходимо удалить информацию о заказах клиента 3105 (рис. 40).

Из таблицы ORDERS удаляется вся информация о клиенте 3105.

Пусть необходимо удалить информацию о заказах, сделанных до 17.01.03. Шаблон запроса выглядит следующим образом (рис. 41):

Пример 3.26. Изменение данных

Для изменения цены процессора Athlon XP 2600+ нужно сформировать запрос (рис. 42).

Пример 3.27. Изменение данных с вычислениями

Для того, чтобы повысить цену всех товаров на 5% можно сформировать запрос на изменение информации (рис. 43).

Современные СУБД имеют незначительные изменения от классического варианта QBEв интерпретации отдельных операций, введению дополнительных операций и изменению формы представления языка.

3.3.4. Структурированный язык запросов SQL

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

Ø изменение структуры представления данных;

Ø выборка данных из базы данных;

Ø обработка базы данных, т. е. добавление новых данных, изменение, удаление имеющихся данных;

Ø управление доступом к базе данных;

Ø совместное использование базы данных пользователями, работающими параллельно;

Ø обеспечение целостности базы данных.

SQL – это не полноценный компьютерный язык типа PASCAL, C++, JAVA. Еще раз отметим, что SQL, также как и QBE, является непроцедурным языком. С помощью SQL описываются свойства и взаимосвязи сущностей (объектов, переменных и т. п.), но не алгоритмы решения задачи. Он не содержит условных операторов, операторов цикла, организации подпрограмм, ввода-вывода и т. п. В связи с этим SQL автономно не используется. Инструкции SQL встраиваются в программу, написанную на традиционном языке программирования и дают возможность получить доступ к базам данных (встроенный SQL). Кроме того, из таких языков, С, C++, JAVA инструкции SQL можно посылать СУБД в явном виде, используя интерфейс вызовов функций.

Язык SQL является многофункциональным языком. Во-первых, SQL используется в качестве языка интерактивных запросов пользователей с целью выборки данных и в качестве встроенного языка программирования баз данных. Кроме того, SQL используется в качестве языка администрирования БД для определения структуры базы данных и управления доступом к данным, находящимся на сервере; в качестве языка создания приложений клиент/сервер, доступа к данным в среде Internet, распределенных баз данных.

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

Официальный стандарт языка SQL был опубликован ANSI и ISO в 1986 г. В дальнейшем, он был расширен стандартами SQL-89 (1989 г.) и SQL-92 (1992 г.). Действующая версия стандарта SQL:1999 была принята ANSI и ISO в конце 1999 г. В настоящее время ведется работа над стандартом для SQL3, содержащим объектно-ориентированные расширения.

Кроме перечисленных выше версий языка SQL для универсальных ЭВМ существует множество версий типа "клиент-сервер", а также версий SQL для персональных компьютеров.