Запросы (OpenOffice.org BASE) Ахмедова Е.В. МОУ «СОШ 1» г.Осташков Лекция 18 Часть 2 Лекция 18 Часть 2

ЗапросыЗапросы нам требуется механизм эффективного поиска информации в БД через запросы все СУБД (OOoBase здесь не исключение, хотя и не является лидером) обладают исключительно мощным движком поиска и извлечения информации. Запросы – второй по счету тип объектов, доступных для создания и использования в OOoBase. В области задач доступны три пути создания новых запросов: через дизайнер; через мастер; с помощью SQL-команд. Начинаем работу с дизайнером. Для этого в главном окне программы в области типов объектов должны быть выбраны Запросы, а справа, в области задач, нужно щелкнуть по пункту Создать запрос в режиме дизайна.

Реализация запросов Открывается окно дизайнера и, прежде всего, нам предлагается выбрать таблицы, из которых будет производиться выборка данных. В списке следует выбрать нужную нам таблицу и нажать кнопку Добавить. После этого можно добавить вторую таблицу, третью и т.д Один запрос может работать с любым количеством таблиц и извлекать данные из любых их колонок. Таким образом, запросы обладают известной двойственностью: с одной стороны, это механизм извлечения данных, а с другой – способ представления извлеченных данных в виде временных таблиц. Поэтому вполне можно реализовать такую цепочку: запрос Запрос1 извлекает данные из физической таблицы; запрос Запрос2 извлекает данные из временной таблицы, сгенерированной как результат работы запроса Запрос1; запрос Запрос3 извлекает данные из временной таблицы, сгенерированной как результат работы запроса Запрос2 и т.д. Для поставленной задачи (все строки таблицы Отдел) необходимо выбрать эту таблицу, щелкнуть по кнопке Добавить и сразу же Закрыть.

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

Запуск запроса Давайте воспользуемся первой (самой левой) ячейкой строки Поле редактора и выберем из нее специальный метасимвол * (звездочка), который Вы видите на рисунке. Комбинация.* имеет специальный смысл: мы сообщаем, что нам нужны все колонки из таблицы, имена которых указаны слева от точки. Это как раз соответствует нашему текущему заданию – извлечь всю информацию из таблицы. Поскольку никакого дополнительного анализа данных не требуется, мы можем просто сохранить запрос и покинуть дизайнер. Для этого нужно выбрать меню Файл > Сохранить, поменять предложенное имя запроса Запрос1 на что-то описывающее суть работы запроса (например, Отдел_ВсяИнформация) и нажать OK, завершить работу с дизайнером (меню Файл > Выход). Для запуска существующего запроса на исполнение можно: нажать правой кнопкой мыши на значок данного запроса в области объектов и выбрать из контекстного меню пункт Открыть; произвести двойной щелчок левой кнопкой мыши по тому же значку. Результат будет одинаковым: мы увидим ту самую временную таблицу, сгенерированную как результат работы запроса.

Временная таблица запроса Мы можем использовать временную таблицу для внесения новых записей. У нее тоже есть специальная строка, помеченная желтой звездочкой. Она располагается всегда ниже всех прочих записей. Ее назначение – предоставить нам место, куда бы мы могли внести информацию для новой записи. Если мы внесем новую запись, то эти данные будут сохранены не в запросе, несмотря на то, что заголовок окна имеет вид :. Запрос никогда не хранит никаких записей, а лишь извлекает их из таблиц. Так же и в этом случае: новая запись будет сохранена в той таблице, с которой работает запрос. Помимо добавления новых записей, в том же окне можно изменить существующие записи и даже удалить их. Если мы хотим обезопасить себя от каких-либо модификаций данных, то нам достаточно выключить («отжать») кнопку Правка данных на панели инструментов. Перед Вами на иллюстрации данная кнопка выделена прямоугольником, и можно видеть, что по умолчанию она нажата, т.е. редактирование данных включено. После ее «отжатия» мы переходим в режим «только чтение» и можем лишь просматривать строки, но не можем вставлять/редактировать/удалять их.

Просмотр данных запроса Для просмотра данных удобно использовать статусную строку внизу окна. Слева от этой строки находится конструкция вида Запись X из Y. Для увеличения рисунка щелкните на нем левой кнопкой мыши X означает порядковый номер строки, на который в данный момент указывает зеленый маркер, а Y - общее количество строк, возвращенных запросом и, как следствие, находящихся в данный момент в окне. При этом не важно количество строк, визуально наблюдаемых нами в окне: если запрос вернул 200 тыс. строк, Y всегда будет показывать , как бы мы не расширяли/сжимали окно. Число Y не редактируется, а X доступен для корректировки. Мы можем ввести любое число из диапазона 1…Y (включительно). После нажатия на Enter мы моментально переместимся на строку с указанным номером.

Быстрая навигация по таблице Справа от рассмотренной конструкции находятся 5 кнопок быстрой навигации по таблице, возвращенной как результат работы запроса. Слева-направо: перейти к первой строке в таблице; перейти к предыдущей строке в таблице; перейти к следующей строке в таблице; перейти к последней строке в таблице; перейти к спец-строке Новая строка (доступна, только если кнопка Правка данных на панели инструментов нажата). Для того чтобы закрыть окно результатов запроса, следует выполнить команду Файл > Выход. Если при этом появится окно сохранения изменений в базе, следует ответить Не сохранять, т.к. редактирование данных не входило в наши планы. Таким образом, элементарный запрос составляется очень быстро, а его выполнение не вызывает никаких проблем.

Редактирование запросов Новая задача – вывести все строки из таблицы Отдел, но упорядочить ее по возрастанию количества сотрудников в каждом отделе. ля этого требуется: убедиться, что в области типов объектов по-прежнему выбраны Запросы; нажать правой кнопкой мыши на значок нашего единственного запроса в области объектов и выбрать из контекстного меню пункт Правка. Мы вернулись в уже знакомый нам дизайнер запросов. Сейчас новая задача выполнена лишь наполовину – вся нужная информация извлекается, но не в нужном нам порядке. Порядок извлечения информации задается сортировкой, за которую отвечает строка Сортировка нижнего редактора дизайнера. Очевидно, в нашем случае нам требуется сортировка по полю ЧислоСотрудников. Для начала работы с ним требуется выбрать вторую (слева) ячейку строки Поле и из выпадающего списка данной ячейки выбрать нужную колонку. Ниже строки Сортировка, с которой мы работали только что, располагается ячейка строки Видимый. Флажок в этой ячейке показывает, что колонка выводится в результирующую таблицу; отсутствие флажка говорит о том, что колонка принимает участие в запросе, но в результирующую таблицу не попадает. Сохранить запрос и выйти.

Создание простых запросов с помощью мастера Поставим себе такую цель: создать запрос, возвращающий таблицу, состоящую из 4-х колонок: Имя сотрудника, Фамилия сотрудника, его Оклад и Название (но не код!) того отдела, где данный сотрудник работает. При этом: запрос не должен учитывать сотрудников Административного отдела (код отдела 0); формируемый результат должен быть отсортирован по возрастанию фамилий сотрудников (Астахов займет первую строчку в генерируемой таблице, Ярцев – последнюю). Сразу понятно, что только таблицы Сотрудник здесь не достаточно. Эта таблица не содержит названия отделов, лишь их коды. А по условию нужны именно названия. Приходим к выводу, что нам необходимы 2 таблицы: Сотрудник и Отдел. Для этого мы начнем построение этого непростого запроса в мастере, а доведем его до рабочего состояния в дизайнере. Для начала нужно запустить мастер формирования отчетов. Для этого требуется убедиться, что в главном окне программы в области типов объектов выбран тип Запросы, а затем справа, в области задач, щелкнуть по пункту Использовать мастер для создания запроса.

Мы попадаем на первый шаг мастера – выбор таблиц и полей. В выпадающем списке Таблицы расположены все таблицы текущей базы. При выборе таблицы из этого списка другой список, Доступные поля, обновляется именами колонок выбранной таблицы. Кнопки [>], [>>], [

На этом шаге нам предстоит определиться с правилами сортировки. Мастер предлагает до 4- х критериев сортировки. Например, мы могли бы указать запросу: сортировать сотрудников по фамилии; однофамильцев дополнительно сортировать по имени; однофамильцев с одинаковыми именами дополнительно сортировать по названию отдела, где они работают; однофамильцев с одинаковыми именами, работающих в одном и том же отделе, дополнительно сортировать по дате поступления на работу. Но, согласно нашему заданию, нам требуется лишь сортировка по фамилиям по возрастанию. Поэтому из списка Сортировка выбираем Сотрудник.Фамилия и оставляем переключатель в положении По возрастанию. На третьем шаге мы можем задать условие фильтрации отбираемых для вывода строк. Согласно нашему заданию, мы обязаны это сделать. Нас не интересуют сотрудники Административного отдела, а это уже фильтрация. Логически мы делаем такое заключение: если в таблице Сотрудник у данного сотрудника в колонке КодОтдела стоит 0 (это как раз код Административного отдела), то нам он не интересен. Но третий шаг мастера требует сказать, какие строки нам интересны. А поэтому мы инвертируем предыдущее умозаключение: если в таблице Сотрудник у данного сотрудника в колонке КодОтдела стоит не 0 (это как раз код Административного отдела), то нам он интересен. Переводим последнюю фразу для мастера: из выпадающего списка Поля выбираем Сотрудник.КодОтдела; из выпадающего списка Условие выбираем не равно; в поле Значение вносим 0.

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

Выбор таблиц и полей На финальном шаге мы можем произвести краткий обзор того функционала, что мы вложили в создаваемый запрос (большое текстовое поле Обзор справа). Также мы можем (и должны) присвоить разумное имя создаваемому запросу (текстовое поле Название запроса). Назовем наш запрос Сотрудники_и_их_отделы. С помощью переключателя мы можем выбрать одну из двух опций: Показать запрос. Сформировать запрос, выполнить его и отобразить результат; Изменить запрос. Сформировать запрос, не выполнить его, а отобразить его в дизайнере запросов для последующей «доводки». Давайте посмотрим, готов ли наш запрос? Можно увидеть явное нарушение задания: нам не требуется код отдела, а только его название. А в запросе, который почти готов, он будет выведен, да еще и дважды (колонка Сотрудник.КодОтдела и колонка Отдел.КодОтдела).

Выбор таблиц и полей Вторая проблема гораздо менее очевидна, но гораздо более существенна. Как раз те две колонки, о которых мы говорили только что, связывают две таблицы Сотрудник и Отдел, позволяя узнавать характеристику отдела, где трудится указанный сотрудник. Но дело в том, что связь эта есть лишь у нас в голове и нашем дизайн-проекте. СУБД абсолютно не в курсе, что эта связь существует. Тот факт, что две таблицы содержат одноименные и однотипные колонки, не значит ровным счетом ничего. Связь возникает только после ее явного создания. Таким образом, наш еще несформированный запрос обладает двумя существенными недостатками: показывает 2 колонки КодОтдела, в то время как не должен этого делать; не понимает логической взаимосвязи между таблицами, хотя должен понимать. Обе проблемы возможно решить через дизайнер, и поэтому наш выбор из двух опций очевиден – Изменить запрос. Таким образом, экран финального шага мастера запросов принимает следующий вид:

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

Связь между таблицами Связь между двумя таблицами устанавливается по принципу «колонка к колонке». Т.е. с каждой стороны должна быть колонка (в общем случае – сочетание колонок), значения в которой и позволяют соотнести строки из одной таблицы со строками из другой. В нашем случае таковыми выступают колонки КодОтдела в обеих таблицах, надо лишь выбрать их из списка Включенные поля. Перед Вами список с правильным выбором колонок. Теперь можно нажать OK для создания заявленной связи. Если все было сделано правильно, в верхней части дизайнера между двумя таблицами возникает тонкая черная линия. Небольшие квадраты по концам этой линии соединяют именно те колонки, которые отвечают за поддержание связи. Заметим, что связь, созданная только что, является классической связью один-ко-многим. Если мы берем один отдел, то в нем работает много сотрудников.

Связь между таблицами Подавляющее большинство связей между реальными таблицами в промышленных БД являются связями один-ко-многим. Теперь все проблемы решены, и мы можем сохранить наш запрос (Файл > Сохранить) и покинуть дизайнер (Файл > Выход). В области объектов у нас теперь должно быть 2 запроса. Любым способом (например, двойным щелчком по значку) запускаем на выполнение запрос Сотрудники_и_их_отделы. Самое интересное, что движок СУБД (подпрограмма, ответственная за выполнение запросов) ничего не знает ни о том, ни о другом. Единственное, что она умеет, - это выполнять SQL- скрипты. SQL-скрипт – это обычно небольшая (хотя чисто синтаксически нет никаких ограничений) программа, составленная на специальном языке программирования. Движок СУБД берет эту программу, анализирует ее команды и выполняет предписанные ею действия. Таким образом, можно утверждать, что запрос – это всегда SQL-скрипт, короткий фрагмент текста, написанный на языке программирования. А дизайнер и мастер – не более чем инструменты, позволяющие создать такой скрипт, не зная правила и синтаксис этого языка. Если мы хорошо овладеем SQL-языком, то, скорее всего, мы будем писать запросы «напрямую», не прибегая к помощи этих двух вспомогательных модулей.

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

Контрольный вопрос 1. Может ли запрос вернуть информацию сразу из колонок трех таблиц? Нет, запрос работает с колонками максимум одной таблицы. Нет, запрос работает с колонками максимум двух таблиц. Да, может. Да, может, при условии, что общее количество колонок в запросе будет меньше или равно При создании запроса мы включили в него колонку коддиска. Означает ли это, что в результате запуска такого запроса на исполнение информация из этой колонки будет отображена на экране? Да, информация всех колонок, включенных в запрос, безоговорочно визуализируется на экране. Да, если КодДиска является первичным ключом; информация всех колонок первичного ключа безоговорочно визуализируется на экране. Зависит от дизайна запроса; мы вправе как отобразить эту колонку вместе с ее значениями, так и скрыть ее. Нет, если КодДиска является первичным ключом; все колонки первичного ключа безоговорочно пропускаются при визуализации результатов работы запроса на экране.

Контрольный вопрос 3. Укажите метасимвол, при использовании которого в дизайнере запросов будут выведены все колонки данной таблицы. % ^ # * ! 4. Чем физически является любой запрос независимо от способа его создания? Двоичным файлом на диске. Короткой программой на языке SQL. Короткой программой в двоичных кодах. Шифрованным текстовым файлом.

Контрольный вопрос 5. Может ли быть выполнен запрос, извлекающий данные не из физической таблицы, а из другого запроса? Нет, не может. Может, если этот другой (базовый) запрос сам извлекает данные из таблицы. Может в любом случае. Может, если этот другой (базовый) запрос извлекает не более 5000 строк данных. 6. Укажите ячейку дизайнера запросов, выпадающий список которой позволит включить сортировку по полю ДатаПриобретения. 4

Контрольный вопрос 7. Мы решили создать новый запрос для извлечения информации из таблицы МояКоллекцияСD. Укажите, какие элементы интерфейса мы должны выделить для того, чтобы выбрать нужную таблицу? Таблицы. Запросы. Закрыть. Добавить. Ближайшие задачи. Кандидат. МояКоллекцияCD. Отдел. Сотрудник. 8. Укажите ячейку дизайнера запросов, с помощью которой мы можем отключить визуализацию колонки ДатаПриобретения в запросе. 3

Контрольный вопрос 9. Укажите в интерфейсе седьмого шага Мастера запросов текстовое поле, с помощью которого мы можем изменить название колонки НазваниеКниги на заголовок для формируемого запроса. КодКниги КодИздательства НазваниеКниги НазваниеИздательства 10. Укажите опции, благодаря которым сразу после окончания работы Мастера запросов будет запущен Дизайнер запросов, где мы сможем создать связь между двумя таблицами. (2, 3) 11. Укажите пункт меню в окне дизайнера запросов, с выбором которого начинается создание связи между двумя таблицами. Файл. Правка. Вид. Вставка. Сервис. Окно.

Контрольный вопрос 12. Какую строку мы должны выбрать, чтобы связать эти две таблицы? КодКниги. КодИздательства. НазваниеКниги. ЦеныКниги. 13. Мы хотим запустить запрос на выполнение и проверить его работу. С этой целью мы щелкнули правой кнопкой мыши по значку данного запроса в области объектов. Укажите, какой пункт в появившемся контекстном меню реализует это намерение. Копировать. Удалить. Переименовать. Правка. Редактировать в режиме SQL… Открыть. Создать представление. Мастер форм… Мастер отчетов…

Контрольный вопрос 14. Мы хотим изменить порядок фильтрации запроса. Укажите необходимый пункт в контекстном меню запроса, который откроет этот запрос в дизайнере. Копировать. Удалить. Переименовать. Правка. Редактировать в режиме SQL… Открыть. Создать представление. Мастер форм… Мастер отчетов…