Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
9
Лекция № 4_2 по Microsoft Access
Запросы и их использование. Что такое запрос?
Обработка данных таблиц с помощью запросов.
Если структура базы данных хорошо продумана, то исполнители, работающие с базой, должны навсегда забыть о том, что в базе есть таблицы, а еще лучше, если они об этом вообще ничего не знают. Таблицы – слишком ценные объекты базы, чтобы с ними имел дело кто-либо, кроме разработчика базы.
Основная функция БД –поиск данных. В отличие от файлов они применяются для хранения больших объемов сложно связанных данных. Найти требуемые данные в подобных системах непросто. Поэтому в БД имеются специальные средства для поиска. Основное средство –это запросы. В Microsoft Access различают бланк запроса и собственно запрос. В бланке запроса задаются условия поиска данных. Запрос –это результат поиска, аналогичный таблице. В Microsoft Access имеется несколько способов создания запросов.
Если исполнителю надо получить данные из базы, он должен использовать специальные объекты —запросы. Слово запрос (query), которое происходит от латинского quoerere, первоначально означало спрашивать или узнавать. Затем оно стало синонимом слов опрос, опознавание, справка и вопрос. Поэтому запрос можно представить как вопрос (или справку), имеющий отношение к информации, которая содержится в таблицах базы данных.
Запрос в Microsoft Access – это требование предоставить информацию, накопленную в таблицах Access. Информацию можно получить с помощью инструментов запроса. Запрос может относиться к одной или к нескольким связанным таблицам. При этом Microsoft Access выдает только ту информацию, которую вы запрашивали.
Запрос можно адресовать одной таблице, однако многие запросы к базам данных извлекают информацию из нескольких таблиц.
В общем случае результирующая таблица может не соответствовать ни одной из базовых таблиц базы данных. Ее поля могут представлять набор из полей разных таблиц, а ее записи могут содержать отфильтрованные и отсортированные записи таблиц, на основе которых формировался запрос. Лишь в тех случаях, когда исполнитель не находит нужных данных в результирующей таблице, возникает необходимость готовить новый запрос —это задача разработчика базы.
Все необходимые запросы разработчик базы должен подготовить заранее. Если запрос подготовлен, надо открыть панель Запросы в окне База данных, выбрать его и открыть двойным щелчком на значке —откроется результирующая таблица, в которой исполнитель найдет то, что его интересует.
Виды запросов.
Запросы в Access можно разбить на шесть основных категорий.
|
Запрос на выборку (Select) |
Самый распространенный тип запроса. Извлекает данные из одной или нескольких таблиц и результаты отображает в виде динамического набора данных в режиме таблицы, в котором допускается изменение записей |
|
Групповой запрос (Total) |
Представляет собой специальную версию запроса на выборку. Позволяет вычислять суммы, подсчитывать количество записей и выполнять расчеты итоговых значений. При выборе этого типа запроса Access добавляет в бланк запроса строку Групповая операция (Total) |
|
Запрос на изменение (Action) |
Позволяет создавать новые таблицы (команда Создание таблицы (Make Tables)) или изменять данные в существующих таблицах (команды Удаление (Delete), Обновление (Update) и Добавление (Append)). Запрос на изменение разрешает вносить изменения в несколько записей сразу при выполнении одной операции |
|
Перекрестный запрос (Crosstab) |
Отображает результаты статистических расчетов (такие, как суммы, количество записей и средние значения). Эти результаты группируются по двум наборам данных в формате перекрестной таблицы. Первый набор выводится в столбце слева и образует заголовки строк, а второй —выводится в верхней строке и образует заголовки столбцов |
|
SQL |
Существуют три типа запросов SQL (Structured Query Language —язык структурированных запросов): запрос на объединение, запрос к серверу и управляющий запрос, которые используются для манипуляций с базами данных SQL (например, при работе с базами данных SQL архитектуры клиент/сервер). Создаются эти запросы с помощью специальных инструкций SQL |
|
Запрос с ограничением, или Тор(п) |
Этот ограничитель запроса можно использовать только в, конъюнкции с другими пятью типами запросов. Он позволяет задавать число первых записей или часть общего количества записей в процентах, которую вы хотели бы получить в любом виде запроса |
Создание и применение запросов разных видов. Возможности запроса.
Запросы достаточно гибки и позволяют просматривать данные так, как удобно пользователю. Большинство систем баз данных постоянно совершенствуется, создавая все более мощные инструменты. Однако информация в базе данных остается информацией, и вы можете со временем представлять ее все более изысканными способами. Инструменты запроса являются мощным средством для работы с базой данных. Вот что можно делать с их помощью.
|
Выбор таблиц |
Вы можете получать информацию из одной или нескольких таблиц, связанных общими данными. Даже при использовании нескольких таблиц Access возвращает собранную информацию в виде одного объединенного набора данных, представленного в табличной форме |
|
Выбор полей |
Вы можете задавать поля из каждой таблицы, которые хотите видеть в динамическом наборе данных. |
|
Выбор записей |
Вы можете выбирать записи для отображения в динамическом наборе данных по заданным критериям. |
|
Сортировка записей |
Часто необходимо просмотреть динамический набор данных, упорядоченный определенным образом, например записи о клиентах, отсортированные по фамилиям и именам. |
|
Выполнение вычислений |
Запросы можно использовать для выполнения вычислений с данными. Вас может интересовать среднее значение, сумма или количество записей |
|
Создание таблиц |
Иногда необходима новая таблица, созданная на основе результатов выполнения запроса. Запрос может создать такую таблицу на основе динамического набора данных |
|
Создание форм и отчётов на основе запроса |
Если в динамическом наборе данных содержатся поля и данные, которые являются основой вашего отчета или формы, то всякий раз при распечатке отчета или открытии формы запрос будет извлекать из таблиц самую свежую информацию |
|
Создание диаграмм на основе запроса |
На основе данных запроса можно строить диаграммы, а затем использовать их в форме или отчете |
|
Использование запроса в качестве источника данных для других запросов (подзапрос) |
Вы можете создавать дополнительные запросы на основе Динамического набора данных предыдущего запроса. Это очень удобно для выполнения непредвиденных запросов, когда приходится постоянно вносить небольшие изменения в условия запроса. Для внесения таких изменений может использоваться вторичный запрос, тогда как первичный запрос и его данные остаются неизменными |
|
Внесение изменений в таблицы |
Запросы в Access позволяют получать информацию из разных источников. Вы можете запросить информацию из баз данных dBASE, Paradox, Btrieve и Microsoft SQL Server |
Что такое динамической набор данных
После создания и запуска запроса Microsoft Access может вернуть и отобразить в виде строк и столбцов множество записей, которые вы запрашивали. Эти записи называются динамическим набором данных (dynaset). Как вы уже знаете, Режим таблицы (Datasheet View) выглядит так же, как электронная таблица, со строками записей и столбцами полей. В режиме таблицы обычно отображается сразу несколько записей. Со стороны динамический набор данных выглядит как таблица, хотя и не является ею. Динамический набор данных является временным (или виртуальным) набором записей и не хранится в базе данных.
После закрытия запроса динамический набор данных этого запроса прекращает свое существование. Хотя сам по себе динамический набор данных больше не существует, помните, что данные, которые в нем содержались, остаются в базовых таблицах.
При сохранении запроса сохраняется только структура запроса —перечень таблиц, список полей, порядок сортировки, ограничения на записи, тип запроса и т.д. Тот факт, что динамический набор данных не сохраняется в физической таблице, имеет ряд преимуществ.
При каждом выполнении запрос обращается к базовым таблицам и снова создает динамический набор данных. Поскольку сам по себе динамический набор данных не сохраняется, запрос автоматически отображает все изменения, произошедшие в базовых таблицах с момента последнего запуска этого запроса (даже в реальном времени в многопользовательской среде).
Язык создания запросов и его использование.
1. В окне базы данных щелкните на вкладке Запросы (Queries).
. Щелкните на кнопке Создать (New).
При этом появится диалоговое окно Новый запрос (New Query), предоставляющее пять способов построения запроса. При выборе первой опции будет отображено окно Конструктора запросов.
. Выберите опцию Конструктор (Design View) и щелкните на кнопке ОК.
Выбор таблицы
В диалоговом окне Добавление таблицы (Show Table) отображаются все таблицы и запросы базы данных. Для добавления таблицы в окно конструктора запросов выполните следующие действия.
1. Выберите таблицу.
2. Щелкните на кнопке Добавить (Add).
3. Щелкните на кнопке Закрыть (Close).
Использование окна конструктора запросов
d1f3f0 e1eb
Окно Конструктора запросов имеет два режима: Конструктор и Режим таблицы. В режиме Конструктор создается запрос, а в Режиме таблицы выводится динамический набор данных запроса.
Окно Конструктора запроса состоит из двух частей:
Область таблиц запроса —это место, где размещаются таблицы и/или запросы и отображается их структура.
Бланк запроса предназначен для определения полей и условий, которые будут использованы для извлечения динамического набора данных. В каждом столбце бланка запроса содержится информация об одном поле из таблицы или запроса в верхней части окна.
Перемещение в окне конструктора запросов
Заголовок в верхней части окна конструктора запроса содержит информацию о конкретном окне, типе запроса и имени запроса. Любой новый запрос называется Заnpoс1(Query1). Заголовок отображает тип запроса и имя в виде
Запрос1 : запрос на выборку (Queryl : Select Query).
Окно разделено на две области горизонтальной полосой раздела. Она используется для изменения размеров этих областей. Для увеличения верхней области щелкните на полосе и перетащите ее вниз, а для увеличения нижней области перетащите ее вверх.
Переключаться между верхней и нижней областями можно с помощью щелчка мышью на соответствующей области окна или клавиши <F6>. Обратите внимание, что в каждой области есть полосы прокрутки.
Конструировать запрос можно, перетаскивая поля из верхней области окна конструктора запроса в нижнюю. После отбора полей для бланка запроса можно изменить порядок их отображения, перетаскивая поля с помощью мыши.
Бланк запроса
В бланке запроса содержится шесть строк.
|
Поле (Field) |
Имя поля |
|
Имя таблицы (Table) |
Имя таблицы, в которой содержится поле (эта информация полезна в запросах, использующих несколько таблиц) |
|
Сортировка (Sort) |
Место ввода инструкций сортировки |
|
Вывод на экран (Show) |
Определяет, будет ли присутствовать поле в динамическом наборе данных |
|
Условие отбора (Criteria) |
Содержит первое условие, ограничивающее набор записей |
|
Или (Or) |
Другие условия ограничения набора записей |
Выбор полей
Когда вы добавляете в запрос все поля, используя пункт "звездочка", запрос автоматически изменяется при внесении изменений в таблицу, на которой он основан.
Имена полей можно переопределять в таблице динамического набора данных, вводя новые имена с двоеточием перед существующими.
Поле можно добавить несколькими способами, например, дважды щелкнув на его имени. При этом имя поля появится в следующем доступном столбце бланка запроса окна конструктора запроса. Поле также можно добавить в бланк запроса окна конструктора запроса, выполнив следующие действия.
1. Выделите имя поля в области таблиц запроса.
2. Щелкните на нужном поле и перетащите пиктограмму поля, которая появится при перемещении мыши.
. Опустите пиктограмму поля в нужном столбце бланка запроса.
Запуск запроса
Для запуска запроса щелкните на кнопке Вид (View) панели инструментов (первая кнопка слева). Для возврата в режим Конструктор (Design View) снова щелкните на кнопке Вид (View) панели инструментов.
Запрос можно запустить, щелкнув на кнопке с изображением восклицательного знака на панели инструментов или выбрав команду Запрос►Запуск (Query►Run).
Отбор записей
Access упрощает отбор записей, задавая условия их отбора.
Условия отбора записей —это набор предопределенных в Access или задаваемых вами правил. Они указывают Access, какие записи вы хотите просмотреть в динамическом наборе данных. Иначе говоря, с помощью условий отбора записей вы создаете ограничивающие фильтры, чтобы указать, какие записи следует найти, а какие —оставить за пределами динамического набора данных.
Условия задаются в строке свойства Условие отбора (Criteria) бланка запроса с помощью выражения. Выражение может задаваться по образцу или использовать сложные функции выбора.
Для текстового поля можно вводить условия символьного типа. Подобные условия задаются по образцу. Обратите внимание, что вы не вводили знак "равенства" и не заключали текст в кавычки, хотя Access автоматически взяла слово в двойные кавычки. (Это пример ее мощности и гибкости.)
Обычно при работе с символьными данными приходится вводить равенства, неравенства или перечислять все допустимые значения.
При вводе простого выражения в поле текстового типа Access берет образец из ячейки свойства Условие отбора (Criteria) и интерпретирует его таким образом, чтобы показать все записи, в которых содержится такой же образец данных.
Это средство является довольно мощным инструментом. Учтите, что вы лишь задали образец данных, a Access не только интерпретировала его, но и использовала для создания динамического набора данных запроса. Это как раз то, что называют запросом по образцу (query by example): вы вводите образец и выполняете запрос на его основе.
Для удаления критерия из ячейки выделите ее содержимое и либо нажмите клавишу <Delete>, либо выберите команду Правка►Удалить (Edit►Delete).
Условия можно также задавать для полей с данными типа Числовой (Numeric), Дата/время (Date/Time) и Логический (Yes/No).
Access может сравнивать значение поля типа Дата/время (Date/Time) с заданным значением, используя следующие операторы сравнения:<, >, = и их комбинацию. Обратите внимание, что Access автоматически размещает значения даты между разделителями (символами #). Разделители позволяют Access отличать данные типа Дата/время (Date/Time) от данных типа Текстовый (Text). Так же, как при вводе текстовых образцов, вам необязательно вводить знаки #, поскольку Access понимает, что вам нужно, и сама преобразует формат ввода.
Запросы подробно
Запрос является объектом БД. Он представляет собой сформулированную информационную потребность.
При работе с запросом можно выделить два этапа: формирование (проектирование) и выполнение. При выполнении запроса выбирается информация из всех таблиц БД в соответствии с критерием запроса.
Формирование запроса производится в Конструкторе запросов командами:
- Вставка –Запрос;
d0.1. .
В верхней части окна Конструктора (рис. 1) размещаются нужные таблицы посредством команды Запрос - Добавить таблицу или та же команда в контекстном меню. В нижней части окна расположен бланк запроса, информация в него заносится путем перетаскивания нужных полей из таблиц в верхней части окна в строку "Поле" или двойным щелчком мыши. При этом имя таблицы в бланке подставляется автоматически.
Наличие "галочки" в строке "Вывод на экран" означает присутствие данного поля в таблице результатов поиска. Критерии запроса устанавливаются в строке "Условие отбора" и последующих строках, связанных логическим оператором OR. Все критерии отбора, указанные в одной строке, объединяются оператором AND.
В качестве "Условия отбора" могут быть выражения (вычисляемое поле), даты, текст, которые вносятся либо вручную, либо инструментом , либо с помощью команды контекстного меню Построить. Константы типа Дата/Время заключаются в #.
Запросы бывают разных типов: на выборку, на создание, на обновление, на добавление, на удаление, перекрестный, итоговый, параметрический и др. По умолчанию формируется запрос на выборку. Тип запроса может быть преобразован в любой другой командой Запрос или инструментом . Выполнение запроса осуществляется командой Запрос –Запуск или инструментом .
Вычисляемые поля в запросах
С помощью запросов можно задать вычисления над данными и сделать вычисляемое поле новым полем в наборе данных. Для создания нового поля в пустой ячейке строки Поле в бланке запроса вводится формула: Имя поля: Выражение
Для построения выражений имеется специальное средство Построитель выражений, вызываемый правой кнопкой мыши на поле или кнопкой Построить.
Рис.2. Окно Построителя выражений
В верхней части Окна Построителя выражений (рис. 2) размещается область ввода. Нижняя содержит три списка для выбора имен полей и функций. В папке Функции размещаются встроенные функции, сгруппированные по категориям.
Параметрические запросы
Условия запроса могут быть включены непосредственно в бланк запроса, но, для того чтобы сделать его более универсальным, можно вместо конкретного значения отбора включить в запрос параметр, т. е. создать параметрический запрос. Для этого в строку "Условие отбора" вводится фраза в квадратных скобках, которая будет выводиться в качестве "подсказки" в процессе диалога, например, [Введите фамилию]. Таких параметров может быть несколько, каждый для своего поля.
Для каждого параметра можно указать тип данных, который корректируется командой Запрос –Параметры. При задании нескольких параметров имя каждого должно быть уникальным.
Итоговые запросы
При выборе данных может понадобиться найти какую-либо функцию, например, сумму значений или максимальное значение в поле. Запросы, выполняющие вычисления над группой записей, называются итоговыми. Для их составления следует войти в Конструктор запросов и выбрать Вид –Групповые операции. В бланке запроса появится новая строка с наименованием "Групповая операция", в ней содержится слово "Группировка". В этой строке следует указать, какое вычисление необходимо выполнить.
Возможные операции в строке "Групповые операции":
SUM - сложение;
AVG - среднее значение;
MIN - минимальное значение;
МАХ - максимальное значение;
COUNT- количество записей со значениями (без пустых значений);
STDEV - стандартное отклонение;
VAR - дисперсия;
FIRST- значение в первой записи;
LAST - значение в последней записи.
Перекрестные запросы
Особый тип итоговых запросов, представляющих результаты поиска в виде матрицы, называется перекрестным. Для его создания нужно в Конструкторе запроса выполнить команду Вставка –Запрос –Перекрестный запрос или кнопка Создать –Перекрестный запрос.
Для каждого поля такого запроса может быть выбрана одна из установок: "Заголовки строк", "Заголовки столбцов", "Значение", которое выводится в ячейках таблицы, и "Не отображается".
Для перекрестного запроса надо обязательно определить хотя бы по одному полю в качестве заголовка строк, заголовка столбцов и значения. Можно использовать дополнительные условия отбора и сортировку.
Модификация БД с помощью запросов на изменение
С учетом того, что запросы на модификацию БД приводят к необратимым изменениям информации, содержащейся в БД, целесообразно выполнять их в два этапа. На первом этапе следует произвести отбор модифицируемых данных запросом на выборку. Проверить правильность выборки. На втором этапе изменить статус запроса на выборку, установив его в соответствии с заданием. Затем повторно выполнить запрос с новым статусом.
Запрос на обновление
Запрос этого типа используется при необходимости внесения изменений во множество записей БД, поэтому целесообразно сделать резервную копию таблицы.
Выполняется этот вид запроса в два этапа: сначала проверяется правильность отбора обновляемых записей с помощью запроса на выборку, затем он преобразуется в запрос на обновление и выполняется повторно.
При обновлении полей следует иметь в виду, что если при проектировании таблицы в свойствах поля было указано "Условие на значение", то при обновлении этого поля условие может быть нарушено, чего не допустит MS Access. Поэтому нужно или изменить условие на значение, или удалить это условие в Конструкторе.
Запрос на добавление
Периодически убирая в архивные таблицы "старые" записи, можно увеличить быстродействие основных частей и улучшить обзорность БД.
Кроме того, при необходимости добавить данные в таблицу БД из другой базы можно также использовать запросы на добавление.
Запрос на удаление
"Старые" или неиспользуемые записи таблиц можно удалить, но обязательно сначала произвести выборку и проверить ее. Целесообразно сделать копию.
Запрос на добавление
2. Отберите в таблицу MEDAL записи обо всех абитуриентах-медалистах. Для этого:
- создайте запрос на выборку, включив в него все поля таблицы CONT (Контингент) в любой последовательности, и критерий по полю mdl (медаль) (Да в строке Условие отбора), запустите запрос для проверки правильности отбора записей;
- измените статус запроса "Выборка" на запрос "Добавление" (Запрос - Добавление…), в появившемся окне задайте имя таблицы для добавления MEDAL, обратите внимание на появление строки "Добавление" в бланке запроса;
- выполните запрос и подтвердите добавление; сохраните запрос, обратив внимание на значок у его имени. Просмотрите результаты добавления данных в таблице MEDAL.
Самостоятельно создайте:
- запрос на добавление записей в таблицу HOSTEL (Общежитие) для всех иногородних (не москвичей);
- запрос на добавление записей в таблицу STUD (Студенты) для тех абитуриентов, у которых суммарный балл по экзаменам больше 30.
Запрос на удаление
- создайте запрос на выборку, включив в него все поля таблицы CONT (Контингент) и критерий по полям с экзаменационными оценками, запустите запрос для проверки правильности отбора записей;
- измените статус запроса "Выборка" на запрос "Удаление". (Запрос - Удаление…)
5