1
Язык запросов по образцу QBE
Хранимые в базе данные можно обрабатывать вручную, последовательно просматривая и редактируя данные в таблицах с помощью имеющихся в СУБД соответствующих средств. Для повышения эффективности применяют запросы, позволяющие производить множественную обработку данных, то есть одновременно вводить, редактировать и удалять множество записей, а также выбирать данные из таблиц.
Запрос представляет собой специальным образом описанное требование, определяющее состав производимых над БД операций по выборке, удалению или модификации хранимых данных.
Для подготовки запросов с помощью различных СУБД чаще всего используются два основных языка описания запросов:
∙язык QBE (Query By Example) – язык запросов по образцу;
∙SQL (Structured Query Language) – структурированный язык запросов.
По возможностям манипулирования данными при описании запросов указанные языки практически эквивалентны. Главное отличие между ними, по-видимому, заключается в способе формирования запросов: язык QBE предполагает ручное или визуальное формирование запроса, в то время как использование SQL означает программирование запроса.
Характеристика языка QBE
Теоретической основой языка QBE является реляционное исчисление с переменными-доменами. Язык QBE позволяет задавать сложные запросы к БД путем заполнения предлагаемой СУБД запросной формы. Такой способ задания запросов обеспечивает высокую наглядность и не требует указания алгоритма выполнения операции – достаточно описать образец ожидаемого результата. В каждой из современных реляционных СУБД имеется свой вариант языка QBE.
На языке QBE можно задавать запросы однотабличные и многотабличные (выбирающие или обрабатывающие данные из нескольких связанных таблиц).
С помощью запросов на языке QBE можно выполнять следующие основные операции:
∙выборку данных;
∙вычисление над данными;
∙вставку новых записей;
∙удаление записей;
∙модификацию (изменение) данных.
2
Результатом выполнения запроса является новая таблица, называемая ответной (первые две операции), или обновленная исходная таблица (остальные операции).
Выборка, вставка, удаление и модификация могут производиться безусловно или в соответствии с условиями, задаваемыми с помощью логических выражений. Вычисления над данными задаются с помощью арифметических выражений и порождают в ответных таблицах новые поля, называемые вычисляемыми.
Запросная форма имеет вид таблицы, имя и названия полей которой совпадают с именем и названиями полей соответствующей исходной таблицы. Чтобы узнать имена доступных таблиц БД, в языке QBE предусмотрен запрос на выборку имен таблиц. Названия полей исходной таблицы могут вводиться в шаблон вручную или автоматически. Во втором случае используется запрос на выборку заголовков столбцов.
В современных СУБД, например в Access и Visual FoxPro, многие действия по подготовке запросов с помощью языка QBE выполняются визуально с помощью мыши. В частности, визуальное связывание таблиц при подготовке запроса выполняется не элементами примеров, а просто «протаскиванием» мышью поля одной таблицы к полю другой.
Первоначальный вариант QBE
Рассмотрим основные возможности QBE, опираясь на его первоначальный вариант.
Для иллюстрации средств и возможностей языка QBE используем таблицы БД, которая относится к торговой сфере и используется в фирме, продающей товары нескольких видов. База данных включает четыре следующие таблицы с полями:
∙ЕМР (служащие): ФИО – фамилия и инициалы служащего, ЗАРПЛАТА – размер должностного оклада, РУКОВОДИТЕЛЬ – фамилия
иинициалы руководителя, ОТДЕЛ – название отдела, в котором работает служащий;
∙SALES (продажи): ОТДЕЛ – название отдела, ТОВАР – название
товара;
∙SUPPLY (поставщики): ТОВАР – название поставляемого товара, ПОСТАВЩИК – название организации, поставляющей товар;
∙TYPE (типы товаров): ТОВАР – название товара, ЦВЕТ – его цвет, СТОИМОСТЬ – стоимость товара.
В таблицах приведены неполные и упрощенные сведения. Так, в таблице TYPE указаны не все виды товаров, приведенные в таблице SALES.
Для описания переменных в условиях отбора записей, а также для связывания шаблонов в запросах используются элементы примера.
3
Элемент примера играет роль идентификатора переменной (как в языке программирования) и задается с помощью символьно-цифровой последовательности. Элементы примера в шаблонах выделим подчеркиванием. Вид (длина и состав) элемента примера роли не играют: главное чтобы при использовании в нескольких местах шаблона он был одинаков. Таким образом, как элементы примера, в частности, можно использовать идентификаторы example, х или у.
Для указания системе необходимости включения в ответную таблицу того или иного поля используется «Р.», что означает «напечатать».
EMP |
ФИО |
ЗАРПЛАТА |
РУКОВОДИТЕЛЬ |
ОТДЕЛ |
|
|
|
|
|
|
Киселев В.М. |
1800 |
Белкин Б.Н. |
хозтовары |
|
Гурский СИ. |
1600 |
Томилов А.Н. |
игрушки |
|
Андреева Е.А. |
2000 |
Петров А. С. |
косметика |
|
Левин П.Г. |
2200 |
Петров А.С. |
канцтовары |
|
Носов А.П. |
1600 |
Томилов А.Н. |
игрушки |
|
Гофман В.Э. |
2600 |
Андреева Е.А. |
косметика |
|
Сорокина Т.В. |
1700 |
Андреева Е.А. |
косметика |
|
Белкин Б.Н. |
1800 |
Петров А. С. |
хозтовары |
|
Семин С.В. |
2200 |
Левин П.Г. |
канцтовары |
|
Григорьев А.Н. |
1900 |
Томилов А.Н. |
игрушки |
|
Томилов А.Н. |
2000 |
Петров А.С. |
игрушки |
SALES |
ОТДЕЛ |
ТОВАР |
|
канцтовар |
бумага |
|
хозтовары |
мыло |
|
канцтовар |
карандаш |
|
косметика |
помада |
|
игрушки |
самолет |
|
игрушки |
машина |
|
игрушки |
кукла |
|
косметика |
духи |
|
канцтовар |
чернила |
|
хозтовары |
посуда |
|
канцтовар |
ручка |
SUPPLY |
ТОВАР |
ПОСТАВЩИК |
|
ручка |
Pencraft |
|
бумага |
Pencraft |
|
мыло |
Procter & Gamble |
|
карандаш |
Flic |
|
чернила |
Pencraft |
|
духи |
Beautex |
|
чернила |
Flic |
|
посуда |
Cremco |
|
помада |
Beautex |
|
самолет |
Signal |
|
машина |
Signal |
|
кукла |
Signal |
|
посуда |
Flic |
|
ручка |
Beautex |
|
карандаш |
Pencraft |
4
TYPE |
ТОВАР |
ЦВЕТ |
СТОИМОСТЬ |
|
посуда |
белый |
30 |
|
помада |
красны |
17 |
|
духи |
|
42 |
|
ручка |
зеленый |
6 |
|
карандаш |
синий |
2 |
|
чернила |
зеленый |
4 |
|
чернила |
синий |
3 |
|
карандаш |
красны |
2 |
|
карандаш |
синий |
2 |
Пример 1. Запрос на выборку.
С учетом изложенного запрос на выборку всех зеленых товаров можно записать в следующем виде:
TYPE |
ТОВАР |
ЦВЕТ |
СТОИМОСТЬ |
|
Р. XX |
зелены |
|
Словесно запрос можно сформулировать следующим образом: «Вывести все товары XX, цвет которых зеленый». В приведенном шаблоне элемент примера XX не обязателен, и его можно опустить. Элементы примера указываются обязательно при записи логических условий, а также при связывании таблиц в запросах.
Пустые колонки из шаблона можно удалять. Пример 2. Удаление колонок.
У нас неиспользуемым столбцом является СТОИМОСТЬ. Исходя их этого, для приведенного шаблона можно записать следующий эквивалентный шаблон:
TYPE |
ТОВАР |
ЦВЕТ |
|
Р. |
зеленый |
После заполнения шаблона для получения результата нажимается соответствующая клавиша, например <Enter>, и начинается выполнение запроса. Результатом выполнения приведенного запроса является следующая таблица:
TYPE ТОВАР
ручка
чернила
Рассмотрим основные возможности перечисленных выше типов операций, используемых в запросах.
Выборка данных
Простая выборка. Примером простой выборки является запрос: «Вывести все возможные цвета товаров из таблицы TYPE».
5
Пример 3. Простая выборка.
Заполненный шаблон в этом случае будет выглядеть так: TYPE ТОВАР ЦВЕТ СТОИМОСТЬ
Р.
Ответная таблица имеет единственный столбец ЦВЕТ, содержащий значения: белый, красный, пусто (значение не задано), зеленый и синий. Дублируемые значения при этом пропадают.
Если требуется вывести данные из нескольких полей исходной таблицы, в каждом из соответствующих столбцов шаблона записывается «Р.». Занесение «Р.» во все столбцы шаблона можно заменить записью «Р.» в первом столбце шаблона под именем таблицы.
Простая выборка с упорядочиванием. Для упорядочения выводимых значений по возрастанию и по убыванию используются конструкции «АО.» и «DO.» соответственно. Если требуется выполнить упорядочивание по нескольким столбцам, применяют конструкции вида: «АО(1).», «АО(2).»
Выборка с квалификаторами (условиями). Выбор записей из исходной таблицы в общем случае может быть основан на: точном совпадении, частичном совпадении, сравнении.
Точное совпадение задается вводом констант в соответствующих полях шаблона, как в случае запроса по товарам зеленого цвета.
Частичное совпадение задается с помощью элементов примера. В частности, для формулировки запроса о выводе всех видов товаров, названия которых начинаются с буквы «и», можно воспользоваться конструкцией «Р.иke», записанной в поле ТОВАР таблицы TYPE. Здесь: «Р.» задает вывод, «и» константа, а «ke» – элемент примера, играющий роль переменной.
Используя элементы примера, можно задавать различные варианты частичного совпадения со значениями данных из таблиц: в начале «иke», в конце «ха», в середине «xlox2» и в произвольном месте.
Поскольку элементу примера сопоставим любой символ, в том числе и пустой (отсутствие символа), то условию частичного совпадения «xlox2» удовлетворяют слова, не только имеющие символ «о» в середине, но начинающиеся и заканчивающиеся на «о».
Пример 4. Частичное совпадение.
Шаблон запроса с выбором товаров синего цвета, в середине названий которых имеется буква «р», выглядит так:
TYPE |
ТОВАР |
ЦВЕТ |
СТОИМОСТЬ |
||
|
Р.хру |
синий |
|
||
|
|
|
|
|
|
Результат в этом случае будет следующий:
6
TYPE ТОВАР
карандаш
чернила
3. Условие сравнения записывается с помощью операций сравнения: равно (=), больше (>), меньше (<), больше или равно (>=), меньше или равно (<=), не равно (¬= или просто ¬), не больше (¬>), не меньше (¬<).
Пример 5. Условия сравнения.
Запрос имен сотрудников, работающих в отделе игрушек и получающих зарплату больше 1800, выглядит так:
ЕМР |
ФИО |
ЗАРПЛАТА |
РУКОВОДИТЕЛЬ |
ОТДЕЛ |
|
Р. |
>1800 |
|
игрушки |
Результатом запроса является таблица вида: ЕМР ФИО
Григорьев А.Н.
Томилов А.Н.
В операциях сравнения можно использовать и элементы примера. Пример 6. Сравнения с элементами примера.
Ниже приведен вид шаблона запроса выборки имен и зарплат сотрудников, получающих больше, чем Левин П.Г. По-другому запрос можно сформулировать так: «Пусть Левин П.Г. получает зарплату в количестве s. Найти всех сотрудников, получающих больше, чем s, и вывести их зарплаты». Порядок строк в шаблоне несущественен.
ЕМР |
ФИО |
ЗАРПЛАТА |
|
Р. |
R> s |
|
Левин П.Г. |
s |
Сотрудников, получающих больше Левина П.Г., в таблице ЕМР не оказалось (вероятно, таковым является Петров А.С., но из таблицы ЕМР нам этого не узнать). В подобных случаях результирующая таблица оказывается пустой:
ЕМР ФИО ЗАРПЛАТА
Условия в запросе могут задаваться по одному или по нескольким столбцам. При этом происходит объединение отдельных условий по схеме логического И (AND).
Пример 7. Объединение условий.
Для формулировки запроса выборки имен и зарплат служащих, получающих больше Левина П.Г. и работающих в отделе игрушек, достаточно в предыдущую запросную форму в первую строку столбца ОТДЕЛ вставить слово «игрушки».
Запрос вида: «Найти имена и зарплаты служащих, получающих больше, чем Белкин Б.Н., и работающих в отделе, продающем ручки», выглядит следующим образом:
7
ЕМР |
|
ФИО |
|
ЗАРПЛАТА |
РУКОВОДИТЕЛЬ |
ОТДЕЛ |
|
|
|
R |
|
Р>§ |
|
department |
|
|
|
Белкин |
|
s |
|
|
|
|
|
|
|
|
|
||
SALES |
ОТДЕЛ |
|
ТОВАР |
|
|
||
|
|
department |
|
ручка |
|
|
|
Результатом выполнения этого запроса будет таблица вида:
ЕМР |
ФИО |
ЗАРПЛАТА |
|
Левин П.Г. |
2200 |
|
Семин С.В. |
2200 |
Здесь элемент примера department связывает две исходные таблицы по полю ОТДЕЛ, а элемент примера s используется для связи условий выбора в рамках одной исходной таблицы ЕМР.
Пример 8. Запрос, в шаблоне которого имеются две связи.
Пусть необходимо найти всех служащих, получающих больше своих руководителей. Этот запрос с помощью элементов примеров можно сформулировать так: «Вывести всех служащих, чьи руководители являются head и кто получает больше, чем s, где s – зарплата head». Шаблон соответствующего запроса имеет следующий вид:
ЕМР ФИО |
ЗАРПЛАТА |
РУКОВОДИТЕЛЬ ОТДЕЛ |
Р. |
>s |
head |
head s
Здесь элемент head используется для связи руководителя в первой строке шаблона и имени из второй строки, а элемент s применяется для сравнения зарплат. Для нашего примера результирующая таблица окажется такой:
ЕМР |
ФИО |
|
Гофман В.Э. |
В качестве условия выбора записей из таблиц можно использовать операцию отрицания.
Пример 9. Отбор с операцией отрицания.
Пусть необходимо вывести все отделы, продающие товары, не поставляемые компанией Pencraft. Этот запрос можно перефразировать: «Вывести названия отделов, продающих товары t, такие, что компания Pencraft не поставляет товары t».
SALES |
ОТДЕЛ |
ТОВАР |
|
P. |
t |
SUPPLY |
ТОВАР |
ПОСТАВЩИК |
|
¬t |
Pencraft |
Ответная таблица для сформулированного запроса имеет вид: SALES ОТДЕЛ
хозтовары
8
косметика
игрушки
В случаях, когда условия отбора записей для выборки представляют большие выражения, которые неудобно или трудно задать в шаблоне, можно использовать блок условий. Он по виду напоминает пустой шаблон с одним полем и именем CONDITIONS. Блок условий предназначен для записи логических выражений.
Записанные в одном шаблоне логические выражения, в общем случае, могут включать в себя операции логического умножения (операция AND) и логического сложения (операция OR).
Пример 10. Использование блока условий.
Шаблон запроса вывода фамилий сотрудников, чья зарплата составляет от 2000 до 2500, но не равна 2300, будет выглядеть так:
ЕМР ФИО ЗАРПЛАТА
P. >2000
Jon <2500
Jon ¬2300
Используя блок условий с явным заданием операции AND (символ &), этот же запрос можно сформулировать проще:
ЕМР |
ФИО |
ЗАРПЛАТА |
|
Р. |
s |
CONDITIONS
s = = (>2000 & <2500 & ¬2300)
Шаблон запроса вывода фамилий сотрудников, чья зарплата составляет 2000, 2300 или 2600, можно сформировать так:
ЕМР |
ФИО |
ЗАРПЛАТА |
|
P. Jon |
2000 |
|
Р. |
2300 |
|
P. Nik |
2600 |
В каждой из строк шаблона используются различные элементы примера, и поэтому условия действуют независимо. С помощью блока условий, в котором операция OR (символ |) задана явно, этот же запрос будет выглядеть более наглядно:
ЕМР |
ФИО |
ЗАРПЛАТА |
|
Р. |
s |
CONDITIONS
s = (2000 | 2300 | 2600)
При записи логических выражений на языке QBE могут применяться встроенные функции, такие как: CNT. (счетчик или количество), SUM. (сумма), AVG. (среднее), MIN. (минимум), МАХ. (максимум), UN. (уникальный) и ALL. (все значения, в том числе повторяющиеся). Первые пять из них являются статистическими, а последние две определяют характер выборки: включать или не включать в выборку повторяющиеся