Получение данных из баз данных

В Excel вы можете получать данные из баз данных следующих ти­пов:

О Microsoft SQL Server OLAP Services;

О Microsoft Access 2000;

О dBASE;

О Microsoft FoxPro;

О Microsoft Excel;

О Oracle;

О Paradox;

О SQL Server;

О из баз данных, хранящих текстовые файлы.

Для получения данных из баз данных используется программа Mi­crosoft Query, окно которой показано на рис. 8.1.

Рис. 8.1. Окно программы Microsoft Query

Рассмотрим назначение инструментов Microsoft Query и порядок ра­боты с ними.

— Создать запрос. Создает новый запрос.

— Открыть запрос. Открывает запрос, сохраненный на диске.

— Сохранить запрос. Сохраняет созданный запрос на диске.

- Вернуть данные. Возвращает данные в Excel.

- Режим SQL. Открывает окно с текстом запроса SQL.

- Отображение таблиц. Отображает или скрывает список выбран­ных таблиц.

- Отображение условий. Отображает или скрывает заданные условия запроса

— Добавить таблицу. Добавление таблицы к запросу.

— Фильтр по выделенному. Добавляет в таблицу условий значение из выделенной ячейки.

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

—Сортировать по возрастанию. Сортировка в выбранном столбце по возрастанию.

—Сортировать по убыванию. Сортировка в выбранном столбце по убыванию.

—Выполнить запрос. Выполняет запрос

—Автоматический режим. Включение автоматического режима вы­полнения запроса.

Для создания запроса нужно выбрать команду Данные ► Внешние дан­ные ► Создать запрос в Excel или щелкнуть на кнопке Создать запрос панели инструментов Microsoft Query. После этого на экран выводит­ся окно выбора источника данных (рис..8.2).

Рис. 8.2. Выбор источника данных

Из трех вкладок в этом окне в данный момент вам нужна только одна -Базы данных. В ней имеется список баз данных, доступных в данный момент в вашей системе, непосредственно на вашем компьютере или же по локальной сети. В этом списке вы можете выбрать необходи­мую базу данных. Список доступных баз данных хранится в реестре Windows. Кнопка Обзор позволяет выбрать источники данных, сохра­ненные в специальных файлах с расширением.dsn. Такие источники данных обычно поставляются производителями баз данных, источники для которых не входят в стандартную поставку Windows. Кноп­ка Параметры позволяет задать постоянный путь к каталогам, где у вас хранятся файлы источников данных. Флажок Использовать мастер зап­росов позволяет составлять запрос к базе данных в интерактивном режиме. Если вы снимете этот флажок, то усложните себе жизнь, поскольку тогда весь запрос придется формировать самостоятельно. Снимать флажок Использовать мастер запросов нужно только в том слу­чае, если вы хотите создать очень сложный запрос, а мастер запросов не справляется с этой задачей.

Для примера выберем в качестве источника данных базу данных MS Access, поскольку она входит в состав Microsoft Office и подключение к ней не требует настройки никаких дополнительных параметров. После того как вы выберете пункт MS Access Database в списке источ­ников данных и щелкнете на кнопке ОК, на экран будет выведено окно выбора файла с данными (рис. 8.3).

Рис. 8.3. Выбор файла с данными

Отдельное окно (показанное в нижней части рисунка) сообщает, что происходит подключение к источнику данных. В это время необходимо произвести это подключение, то есть найти на диске соответ­ствующий файл с расширением.mdb. Обычно подобные файлы мож­но найти в каталоге C:\Program Files\Microsoft 0ffke\0ffice\Samples. В случае, если вы установили Microsoft Office в другой каталог, вам придется внести соответствующую поправку в путь поиска. Выбе­рите в каталоге Samples файл Борейлпс1Ь (или Northwind.mdb в некото­рых версиях Microsoft Office) и щелкните на кнопке ОК. После от­крытия источника данных в работу вступает мастер создания запросов. В качестве первого действия для создания запроса он предлагает вы­брать таблицы и столбцы, из которых будет отбираться информация (рис. 8.4).

Щелчок на кнопке Параметры позволяет выбрать таблицы, данные из которых должны присутствовать в списке Имеющиеся таблицы и столб­цы (это могут быть только таблицы пользовательской базы данных или кроме них еще и другие таблицы, в частности системные). Каж­дую из таблиц в списке Имеющиеся таблицы и столбцы можно раскрыть щелчком на значке «плюс» слева от названия таблицы. В этом случае под названием таблицы отобразятся входящие в состав таблицы столб­цы. Выбрав один из столбцов и щелкнув на кнопке Просмотр, вы ото­бражаете в списке Данные выбранного столбца содержимое этого столб­ца (рис. 8.5).

Рис. 8.4. Выбор столбцов

Рис. 8.5. Просмотр содержимого выбранного столбца

Щелчок на кнопке с направленной вправо стрелкой добавляет выб­ранный столбец в список Столбцы запроса. Щелчок на кнопке с направ­ленной влево стрелкой удаляет из списка Столбцы запроса выбранный там столбец, а щелчок на кнопке со сдвоенной направленной влево стрелкой удаляет из этого списка все столбцы. Кнопки с направлен­ными вверх и вниз стрелками позволяют устанавливать порядок сле­дования столбцов в тексте запроса. После того как вы определили, из каких столбцов нужно отбирать данные, и щелкнули на кнопке Next, вы переходите в следующее окно мастера построения запросов — окно отбора данных (рис. 8.6).

Рис. 8.6. Окно отбора данных

В этом окне для каждого из отобранных столбцов вы можете задать условия отбора записей. Для этого в списке Столбцы для отбора нужно выбрать столбец (в нашем случае это ContactTitle) и в области спра­ва — задать условия отбора. Сами условия нужно выбирать, пользу­ясь раскрывающимися списками, причем в левых списках можно толь­ко выбирать значения, а в правых можно как выбирать значения в списках, так и вводить значения вручную в поля списков. После того как вы задали условия отбора для одного столбца, можно в списке Столбцы для отбора выбрать другой столбец и задать условия для его значений. Щелкнув после этого на кнопке Next, вы перейдете в окно задания порядка сортировки (рис. 8.7).

Следующий шаг позволяет вам решить, что именно вы будете де­лать с выбранными при помощи мастера запросов данными — пере­дадите их в Excel, продолжите редактирование запроса внутри Mi­crosoft Query или сформируете на их основе куб OLAP (см. раздел «Технология OLAP»). Поскольку данный раздел посвящен изуче­нию Microsoft Query, установите в завершающем диалоговом окне переключатель Просмотр или изменение данных в Microsoft Query и щелк­ните на кнопке Finish.

Рис. 8.7. Задание порядка сортировки

Таблица, состоящая из отобранных вами полей и данных, будет от­крыта в Microsoft Query (рис. 8.8).

Рис. 8.8. Таблица в Microsoft Query

Данные в этой таблице отобраны при помощи мастера запросов Текст созданного им запроса можно увидеть, щелкнув „а кнопке Режим SQL На экран будет выведено окно, в котором можно не только просмот­реть текст запроса, но и отредактировать его вручную (рис 8 9) Если редактирование запроса вручную для вас затруднительно вы можете щелкнуть „а кнопке Отображение условий, которая отображает

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

Рис. 8.9. Текст запроса в режиме SQL

Инструменты Microsoft Query дублируют только наиболее часто ис­пользуемые команды и не дают представления обо всех возможнос­тях этой программы. Восполним этот пробел, рассмотрев команды меню.

Ниже перечислены команды меню Файл.

o Создать запрос — выводит на экран окно мастера запросов и создает новый запрос.

o Открыть запрос — открывает файл запроса, сохраненного на диске.

o Закрыть запрос — закрывает текущий запрос, оставляя активным

окно Microsoft Query, но удаляя из него все данные, относящиеся

к текущему запросу.

o Сохранить запрос — сохраняет на диске текущий запрос.

o Сохранить как — позволяет сохранить текущий запрос с новым име­нем.

o Определение таблицы — вызывает на экран окно настройки, позво­ляющее создавать новые таблицы в любой из баз данных, источни­ки данных которых позволяют выполнять эту операцию, а также создавать новые поля в существующих таблицах или переопреде­лять свойства уже имеющихся полей. Кроме того, вы можете соз­давать такие объекты базы данных, как индексы по одному или не­скольким полям (рис. 8.10).

o Выполнить запрос SQL — выводит на экран окно, в котором можно ввести с клавиатуры запрос SQL к любой доступной базе данных

Рис. 8.10. Создание индекса

и затем выполнить его. В запрос можно встраивать хранимые про­цедуры, если таковые определены в запрашиваемой базе данных. Хранимую процедуру можно выбрать в списке, выводимом щелч­ком на кнопке Процедуры (рис. 8.11).

Рис. 8.11. Выбор запроса и хранимой процедуры

О Создать куб О LAP — эта команда выводит на экран окно мастера соз­дания кубов OLAP, при помощи которого вы можете создать, со­хранить и вернуть в Excel в виде сводной таблицы или диаграммы куб OLAP на основе текущего запроса. К вопросу, что такое OLAP и как применять эту технологию, мы еще вернемся позже в этой главе (см. раздел «Технология OLAP»).

О Отмена и возврат в Microsoft Excel — эта команда отменяет редакти­рование запроса и закрывает программу Microsoft Query, не воз­вращая данные в Excel.

О Вернуть данные в Microsoft Excel — эта команда закрывает Microsoft Query, возвращая данные, выбранные запросом, в Excel.

Меню Правка содержит стандартный набор команд правки. Среди других команд этого меню интерес представляют только две. Ко­манда Специальное копирование позволяет скопировать в буфер об­мена не только данные из выделенного столбца или диапазона записей, но и заголовок столбца, а также номера строк. Команда Параметры открывает одноименное диалоговое окно, которое с по­мощью флажков позволяет настроить некоторые из параметров Microsoft Query.

О Разрывать связь при отсутствии активности через — при установлен­ном флажке Microsoft Query будет осуществлять попытку подклю­чения к заданному источнику данных в указанное время (время в секундах вводится в расположенное рядом поле), после чего, если подключение не состоится, выведет сообщение об этом. Если фла­жок снять, то Microsoft Query будет осуществлять подключение все время своей активности. Прервать этот процесс можно будет лишь нажатием клавиши Esc.

О Максимальное число возвращаемых записей — установив этот флажок, вы получаете возможность ограничить число записей, получаемых в результате запроса (число записей вводится в расположенное рядом поле). При снятом флажке запрос вернет все записи, соот­ветствующие параметрам запроса.

О Поддерживать открытые каналы связи до закрытия Microsoft Query — при установке этого флажка связь с источником данных будет сохра­няться до тех пор, пока вы не закроете Microsoft Query, даже если запрос, использующий этот источник данных, будет закрыт. Это ускоряет повторное открытие запросов к тому же самому источни­ку данных.

О Запретить изменение результатов запроса — в случае, когда этот фла­жок не установлен, вы можете редактировать поля записей, и эти изменения будут внесены в базу данных, из которой получены дан ные.

Установка флажка предотвращает внесение изменений в ис­ходные данные.

ВНИМАНИЕ-----------------------------------------------------------------------------------

До тех пор пока вы работаете с запросом в Microsoft Query, вы работаете с данными той базы данных, к которой производился запрос. Таким образом, редактирование данных в таблицах Microsoft Query приводит к изменению этих данных в исходной базе данных. После передачи данных в Microsoft Excel связь с исходным источником разрывается. Внутри таблицы Microsoft Excel вы можете редактировать данные, по­лученные в результате запроса, не опасаясь повлиять на исходные данные.

О Проверять запросы перед сохранением или возвратом данных — если этот флажок установлен, то при получении команды на возврат данных в Microsoft Excel или на сохранение запроса Microsoft Query сначала выполнит запрос и только при успешном его завершении сохранит этот запрос на диске или передаст данные в Microsoft Excel. Если флажок снять, то такая проверка выполнена не будет.

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

Ниже перечислены команды меню Вид.

О Таблицы и Условия — установка флажков рядом с именами этих ко­манд в меню приводит к отображению соответствующих областей в окне Microsoft Query (рис. 8.12).

Рис. 8.12. Области таблиц и условий в окне Microsoft Query

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

О Свойства запроса — выводит на экран окно с двумя флажками, по­зволяющими показывать в запросе только записи с уникальными значениями, а также осуществлять группировку записей по опре­деленному признаку (соответствует команде SQL — GROUP BY).

О Запрос SQL — выводит на экран окно с текстом запроса, который в этом окне можно отредактировать вручную.

Команды меню Формат не нуждаются в особых комментариях, они позволяют установить параметры шрифта, которым будут отображать­ся данные в таблице Microsoft Query, ширину и высоту ячеек, а также предоставляют возможность скрывать и отображать столбцы.

Ниже перечислены команды меню Таблица.

О Добавить таблицу — позволяет добавить таблицу к текущему запросу.

О Удалить таблицу — удаляет таблицу из текущего запроса.

О Объединение — выводит на экран окно настройки (рис. 8.13), по­зволяющее задать параметры объединения записей из двух таблиц (соответствующая команда SQL — INNER JOIN).

Рис. 8.13. Задание параметров объединения записей из двух таблиц

Ниже перечислены команды меню Условия.

• Добавить условие - добавление в запрос условия с помощью специального окна (рис. 8.14). Например, код показанного на рисунке условия выглядит так:

HAVING (Customers.CustomerID>'PICCO') AND

(Customers.CustomerID='PRINI')

Рис. 8.13. Добавление условия в запрос

• Удалить все условия - удаляет из запроса все условия.

Ниже перечислены команды меню Записи.

• Добавить столбец - добавляет к запросу столбец (поле) с заданны­ми параметрами.
• Удалить столбец — удаляет из запроса выделенный столбец. О Изменить столбец — изменяет параметры выделенного столбца. О Сортировать — задает сортировку записей по убыванию или возрас­танию для выделенного поля.
• Перейти — переводит указатель на запись с указанным номером.
• Разрешить правку — разрешает внесение изменений в данные зап­роса, которые приведут к изменениям данных в источнике (в базе) данных.
• Выполнить запрос — выполняет текущий запрос с заданными пара­метрами.
• Автоматический режим — включает автоматическое выполнение зап­роса после каждого изменения параметров запроса или данных.