Довнарь А.И. Информатика и компьютерная техника. Учебное пособие
Подождите немного. Документ загружается.
10.4.6 Работа с запросами
До сих пор мы работали с данными в режиме таблиц. Хотя в этом режиме
доступны самые разные операции с данными (просмотр, сортировка,
фильтрация, обновление и пр.), достаточно часто над ними приходится
проводить вычисления или просматривать данные из нескольких таблиц
одновременно. Отобрать нужные данные мы можем с помощью запросов
(query).
После выполнения запроса на выборку искомые данные отбираются из
таблиц БД и OOBase создает набор записей, называемый ответной таблицей. В
большинстве случаев с ней можно работать как с обычной таблицей: можно
просматривать и отбирать информацию, печатать и даже обновлять данные.
Однако в отличие от реальной, ответная таблица в БД физически не существует
OOBase создает ее из данных таблиц только во время выполнения запроса. Если
ответную таблицу необходимо использовать как источник данных для нового
запроса или других целей, то на ее основании необходимо создать
представление (view). Представление рассматривается СУБД как таблица, хотя
физически в БД она также не существует (собственно поэтому и называется
«представлением»).
Они также могут служить источниками данных для различных элементов
управления, например, для списков, как это было при создании формы для
заполнения таблицы Sheet.
Для создания запросов в современных СУБД предназначены два языка:
QBE (Query By Example) – запрос по образцу;
SQL (Structured Query Language) – структурированный язык запросов.
Наиболее просто и наглядно запросы создаются с помощью языка QBE.
Запросы на этом языке основаны на доменах таблиц и организуются путем
интерактивного заполнения специальных запросных форм.
Различают простые запросы и запросы из двух и более таблиц.
Простые запросы. Для создания простого запроса необходимо
активизировать вкладку Запросы в окне БД и в окне выбора вариантов
(4, рис. 10.4), выбрать пункт Использовать мастер для создания запроса… .
Рассмотрим, как с помощью Мастера построения запросов получить ответ,
например, на вопрос о списочном составе студентов второй группы,
упорядоченном в алфавитном порядке.
1.В первом окне Мастера запросов (рис. 10.43) из раскрывающегося
списка выберем таблицу Students и переместим все Доступные поля в Поля в
запросе.
2.На втором шаге (рис. 10.44) укажем правило сортировки записей в
запросе.
3.На третьем этапе зададим условие отбора записей (рис. 10.45).
4.На четвертом шаге можно задать тип запроса: детальный, когда в
запросе присутствуют все отобранные записи, или итоговый, если необходимо
посмотреть только некоторые статистические данные по отобранным записям,
не показывая сами записи. Тип нашего запроса – детальный (рис. 10.46).
191
Рис. 10.43 Выбор полей запроса
Рис. 10.44 Выбор порядка сортировки
192
Рис. 10.45 Задание условия поиска
Рис. 10.46 Выбор типа запроса
193
На пятом шаге построения можно задать альтернативные названия
столбцов ответной таблицы (псевдонимы, aliases), если Вас не устраивают
названия такие, как в исходной таблице (рис. 10.47).
Рис. 10.47 Задание альтернативных названий столбцов
На последнем шаге, можно проверить описание запроса и решить,
устраивает ли нас созданная формула запроса или его необходимо
скорректировать (рис. 10.48).
В случае согласия с формулой запроса при нажатии на кнопку «Готово»
получим результат запроса (рис. 10.49).
При необходимости коррекции будет вызван Конструктор запросов,
работа с которым будет рассмотрена в следующем пункте.
Рис. 10.48 Окно проверки правильности описания запроса
194
Рис. 10.49 Результат запроса
Запросы из двух и более таблиц. Более сложные запросы удобнее
создавать в режиме дизайна.
Допустим, необходимо просмотреть итоговую ведомость в
«нормальном» виде, с расшифровкой предметов, фамилий студентов и
преподавателей. Информацию упорядочить по группам, а внутри групп – по
фамилиям студентов.
Для этого на вкладке Запросы выберем пункт Создать запрос в режиме
дизайна… . Откроется окно Дизайнер запросов, представленное на рис. 10.50.
Рис. 10.50 Дизайнер запросов. Добавление таблиц
195
На первом шаге создания запроса необходимо выбрать и добавить на
форму нужные таблицы. После добавления всех таблиц кнопкой «Добавить»
запросная форма примет следующий вид (рис. 10.51). Связи между таблицами
берутся из схемы данных, созданной ранее, и устанавливаются автоматически.
Запросная форма состоит из ряда строк:
• Поле – здесь располагается название отобранного для запроса поля;
• Псевдоним – альтернативной название поля для ответной таблицы
(alias);
• Таблица – название таблицы, из которой берется поле;
• Сортировка – указывает нужна ли сортировка по полю, и если
нужна, то какая;
• Видимый – флажок, указывающий на то, будет ли показан данный
столбец в ответной форме, или он введен для иных целей (участвует
только в выражении отбора записей и т.п.);
• Функция – используется для выбора групповой операции;
• Критерий – необходима для задания условия отбора;
• Или – строки для создания сложного условия отбора.
Рис. 10.51 Окно дизайнера запросов после добавления таблиц
196
Для добавления поля в запросную форму необходимо «захватить» его
левой клавишей мыши и «отбуксировать» в Поле. Для создания запроса нашего
примера необходимо сформировать следующую форму (рис.10.52).
Просмотреть результаты запроса и проверить его правильность можно из
окна Дизайнера запросов. Для этого необходимо выполнить команду Правка|
Выполнить запрос. Ответная таблица будет открыта на дополнительной
панели (рис. 10.53).
Запрос можно сохранить, закрыв окно Дизайнер запросов. При закрытии
запроса появится окно сохранения, в котором необходимо задать ему имя для
дальнейшего использования.
Рис. 10.52 Запросная форма примера
Выделив некоторый объект-запрос, правой кнопкой мыши можно вызвать
его контекстное меню (рис. 10.54). В нем собраны все действия, которые можно
произвести с данным запросом. В частности, выбрав пункт Создать
представление, можно на основании этого запроса создать представление –
виртуальную таблицу, которая будет размещена на вкладке Таблицы окна БД.
197
Рис. 10.53 Просмотр результатов выполнения запроса
Рис. 10.54 Контекстное меню объекта Запрос
Для ответа на вопрос, каков средний балл студентов в сессию,
необходимо применить групповую операцию AVG (Average) к столбцу Оценка,
предварительно сгруппировав результаты запроса по фамилии студента.
Запросная форма и результат запроса показаны на рис. 10.55.
Рис. 10.55 Использование групповых операций в запросах
198
Создание запросов на языке SQL.
Структурированный язык запросов SQL основан на реляционном
исчислении с переменными кортежами.
Язык SQL предназначен для выполнения операций над таблицами
(создание, удаление, изменение структуры) и над данными таблиц (выборка,
изменение, добавление и удаление), а также некоторых сопутствующих
операций.
Опишем минимальное подмножество языка SQL. Операторы языка SQL можно
условно разделить на два подъязыка: язык определения данных (Data Definition
Language - DDL) и язык манипулирования данных (Data Manipulation
Language - DML). Основные операторы языка SQL представлены в таблице
10.13.
Таблица 10.13
Операторы языка SQL
Вид Название Назначение
DDL
CREATE TABLE Создание таблицы
DROP TABLE Удаление таблицы
ALTER TABLE Изменение структуры
таблицы
CREATE INDEX Создание индекса
DROP INDEX Удаление индекса
CREATE VIEW Создание представления
DROP VIEW Удаление представления
DML
SELECT Выборка записей
UPDATE Изменение записей
INSERT Вставка новых записей
DELETE Удаление записей
Для выборки записей из одной или нескольких таблиц используется
оператор SELECT. Это – самый мощный оператор языка SQL, и поэтому он
имеет очень сложную структуру. Рассмотрим наиболее распространенную
форму оператора.
SELECT [ALL|DISTINCT] <список данных> FROM <список таблиц>
[WHERE <условие выборки>] [GROUP BY <имя столбца>[,<имя столбца>]…]
[HAVING <условие поиска>] [ORDER BY <спецификация1>[,<спецификация
2>]…]
Оператор SELECT позволяет производить выборку и вычисления над
данными из одной или нескольких таблиц. Результатом выполнения оператора
является ответная таблица, которая может иметь (ALL) или не иметь
(DISTINCT) повторяющиеся строки. По умолчанию в ответную таблицу
включаются все строки, в том числе и повторяющиеся. В отборе данных
участвуют записи одной или нескольких таблиц, перечисленных в списке
оператора FROM.
199
Список данных может содержать имена столбцов, участвующих в
запросе, а также выражения над столбцами. В простейшем случае в
выражениях можно записывать имена столбцов, знаки арифметических
операций, константы и круглые скобки. Если в списке данных записано
выражение, то наряду с выборкой данных выполняются вычисления, результаты
которого попадают в новый (создаваемый) столбец ответной таблицы.
При использовании в списках данных имен столбцов нескольких таблиц
для указания принадлежности столбца некоторой таблице применяют
конструкцию вида: <имя таблицы>.<имя столбца>.
Операнд WHERE задает условия, которым должны удовлетворять
записи в результирующей таблице. Выражение <условие выборки> является
логическим. Его элементами могут быть имена столбцов, операции сравнения,
арифметические операции, логические связки (И, ИЛИ, НЕТ), скобки,
специальные функции LIKE, NULL, IN и т.д.
Операнд GROUP BY позволяет выделять в результирующем множестве
записей группы. Группой являются записи с совпадающими значениями в
столбцах, перечисленных за ключевыми словами GROUP BY. Выделение
групп требуется для использования в логических выражениях операндов
WHERE и HAVING, а также для выполнения операций (вычислений) над
группами.
В логических и арифметических выражениях можно использовать
следующие групповые операции:
• AVG – среднее значение в группе;
• MAX – максимальное значение в группе;
• MIN – минимальное значение в группе;
• SUM – сумма значений в группе;
• COUNT – число значений в группе.
Операнд HAVING действует совместно с операндом GROUP BY и
используется для дополнительной селекции записей во время определения
групп. Правила записи <условия поиска> аналогичны правилам формирования
<условия выборки> операнда WHERE.
Операнд ORDER BY задает порядок сортировки результирующего
множества. Обычно каждая <спецификация> представляет собой пару вида:
<имя столбца>[ASC|DESC].
Для того, чтобы увидеть, как выглядит в реальности запись оператора
SELECT, достаточно рассмотреть выражения, записанные в поля Содержимое
списка элементов Список при создании формы для заполнения таблицы Sheet
(например, табл. 10.12).
Для создания SQL-запроса необходимо из вкладки Запросы окна БД
выполнить команду Создать запрос в SQL представлении … и в открывшемся
окне редактора записать выражение запроса. При создании SQL-запроса
необходимо иметь в виду, что OOBase различает прописные и строчные буквы,
поэтому имена таблиц и полей в выражении должны быть выписаны с
точностью до регистра. На выполнение запрос запускается таким же образом,
как это делалось с QBE.
200