Манипулирование данными. Создание простых запросов. Лабораторная работа № 3.

Краткое описание документа:

 2.1. Создание простых запросов

 Лабораторная работа № 3

Цель работы: Сформировать умения создавать простые запросы для выбора данных.

Запрос в MSAccess – это требование предоставить информацию, накопленную в таблицах базы данных. Запрос можно получить с помощью с помощью инструментов запроса. Запрос может относиться к одной или к нескольким связанным таблицам. На основании запроса MS Access формирует динамический набор записей. Физически он выглядит как таблица, хотя фактически не является ею. Динамический набор записей является временным (или виртуальным) набором записей и не хранится в базе данных. После закрытия запроса динамический набор записей этого запроса прекращает свое существование.

MS Access поддерживает различные типы запросов, которые можно разбить на шесть основных категорий.

Запрос на выборку. Извлекает данные из одной или нескольких таблиц (основываясь на  заданных критериях) и результаты представляет в виде динамического набора записей.

Групповой запрос. Представляет специальную версию запроса на выборку. Позволяет вычислять суммы, подсчитывать количество записей и выполнять расчет итоговых значений. Для этого запроса MS Access добавляет в бланк запроса строку Групповая операция.

Запрос на изменение. Позволяет создавать новые таблицы (команда Создание таблицы) или изменять данные в существующих таблицах (команды Удаление, Обновление и Добавление). Если в наборе результатов запроса на выборку можно вносить изменения только в одну запись за раз, то запрос на изменение разрешает вносить изменения в несколько записей сразу при выполнении этой операции.

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

Запрос SQL. Существуют три типа запросов SQL: запрос на объединение, запрос к серверу и управляющий запрос, которые используются для манипуляций с базами данных SQL. Создаются эти запросы с помощью написания специальных инструкций SQL.

Запрос с ограничением, или Top(n). Этот ограничитель запроса можно использовать только в паре с одним из предыдущих пяти типов запросов. Он позволяет задавать число первых записей или часть общего количества записей в процентах, которую вы хотели бы получить в любом виде запроса.

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

Создание запроса и работа с ним выполняется во вкладке Запросы окна базы данных. Для работы с запросом можно воспользоваться панелью инструментов Конструктор запросов.

Рис. 1. Панель инструментов Конструктор запросов.

MSAccess допускает два способа создания запроса: с помощью мастера и в режиме конструктора. Для того чтобы приступить к созданию запроса с помощью мастера можно выполнить двойной щелчок мышью на строке Создание запроса с помощью мастера во вкладке Запросы окна базы данных или щелчок мышью на кнопке Создать, а затем выбрать вариант Простой запрос в окне диалога Новый запрос.

Создание запроса на выборку для сортировки информации

В работе далее для создания запросов будем использовать режим конструктора. Самый быстрый способ запустить этот режим – выполнить двойной щелчок мышью на строке Создание запроса в режиме конструктора. При этом появится окно диалога Добавление таблицы (см. рис. 2).

Рис. 2. Окно диалога Добавление таблицы.

Создание запроса для сортировки информации рассмотрим на следующем примере. Требуется составить список книг московских издательств, рассортированных по фамилиям авторов. В динамический набор надо включить следующие поля: Автор, Название, Наименование и Год издания.

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

Рис. 3. Окно запроса в режиме конструктора.

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

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

Имена полей, которые будут образовывать динамический набор, должны быть в соответствующем порядке размещены в строке бланка QBE. Сделать это можно несколькими способами. Самый простой способ состоит в двойном щелчке мышью на имени в списке полей. Указанным способом в строке Поле бланка QBE поместите поля: Автор, Название, Наименование,  Год издания и Город. Последнее поле нам понадобилось, чтобы задать условие отбора для выбора для выбора книг московских издательств.

В строку Условие отбора для поля Город наберите текст "Москва" (задание условий отбора подробнее будет рассмотрено ниже). Даже, если вы текст в кавычки не возьмете, MSAccess сам это сделает. Условие отбора нам понадобилось для того, чтобы в запросе выбирались не все книги, а только книги, изданные в Москве.

Поскольку по условию задачи поле Город не надо выводить на экран, то в строке Вывод на экран для этого поля уберите щелчком мыши пометку ("птичку").

Для того чтобы в динамическом наборе записи выводились в алфавитном порядке по фамилиям авторов, надо в строке Сортировка для поля Автор задать направление сортировки. Выполните щелчок мышью на ячейке в строке Сортировка для поля Автор. При этом справа в этой ячейке появится кнопка раскрытия списка направления сортировки. Выберите в этом списке направление сортировки – по возрастанию.

Порядок обработки полей при сортировке по нескольким полям определяется их положением в бланке QBE: сначала сортируются значения в крайнем левом поле и далее слева направо. После указанных действий бланк QBE будет иметь вид, представленный на рисунке 4.

Рис. 4. Вид бланка QBE для решения задачи.

Сейчас выполним созданный нами запрос. Для этого нажмите кнопку Режим таблицы на панели инструментов Конструктор запросов (первая кнопка – см. рис. 1). После нажатия этой кнопки вы увидите список книг московских издательств, рассортированный в алфавитном порядке по фамилиям авторов (см. рис. 5).

Для того чтобы установить оптимальную ширину столбца списка, надо выполнить двойной щелчок мышью на правой границе столбца в строке заголовков полей. Установите оптимальную ширину для всех столбцов списка книг, как это сделано на рис. 5.

Рис. 5. Результат выполнения запроса.

После того как запрос создан, его можно сохранить. Для этой цели надо выполнить команду Сохранить запрос или Сохранить запрос как в меню Файл. Если мы выполняем сохранение первый раз, то выполнение этих команд приводит к одному и тому же результату – на экране появляется окно диалога, приведенное на рис. 6.

Рис. 6. Окно диалога для сохранения запроса.

Сохраните созданный нами запрос под именем Список книг московских издательств. Для этого введите новое имя (старое имя Запрос1, которое предложил Access, после нажатия первой клавиши исчезнет, так что нет необходимости специально его убирать) и нажмите кнопку OK.

Основное назначение запроса состоит в формировании динамического набора, записи которого удовлетворяют некоторым условиям. Условия отбора записей вводятся как выражения. Выражение указывает, какие записи следует включить в динамический набор при выполнении запроса. Выражения могут быть простыми (например, <30) или сложными (например, Between 100 And 500).

Определить условия отбора можно самостоятельно, введя нужное выражение в ячейку Условия отбора, соответствующую данному полю, или воспользоваться построителем выражения. Для определения условия с помощью построителя выражений вначале устанавливают указатель в ячейку Условия отбора в бланке QBE, в которой следует определить выражение, и нажимают кнопку мыши. После этого нажимают кнопку Построить на панели инструментов. На экране появляется диалоговое окно Построитель выражений, приведенное рис. 7.

Если в ячейке бланка QBE, из которой вызывался построитель, содержится значение, то это значение автоматически копируется в поле построения выражения. Используя построитель выражений, можно вводить символы в область ввода или нажимать кнопки для ввода операторов, а также вставлять ссылки на объекты и другие элементы выражения, выбирая их из папок.

Рис. 7. Окно диалога Построитель выражений.

Вставка операторов в выражение из строки операторов, расположенной ниже поля построителя, выполняется щелчком мыши на операторе.

Для вставки элемента поступают следующим образом. В левом нижнем поле построителя выбирают папку, содержащую нужный элемент. В нижнем среднем поле дважды щелкают элемент, чтобы вставить его в поле выражения, или выбирают тип элемента. Если выбран тип в нижнем среднем поле, то значения будут отображаться в нижнем правом поле. Дважды щелкните значение, чтобы вставить его в поле выражения.

Вставьте необходимые операторы в выражение. Для этого поместите указатель мыши в определенную позицию поля выражения и выберите одну из кнопок со знаками операций, расположенных в середине окна построителя. Закончив создание выражения, нажмите кнопку OK.

MSAccess скопирует созданное выражение в ту позицию, из которой был вызван построитель выражений. Если в данной позиции уже содержится значение, то исходное значение будет заменено новым выражением.

Следует иметь в виду, что любая часть выражения или все выражение может быть введено в поле выражения непосредственно с клавиатуры. Может также случиться, что выражение можно быстрее ввести в строку Условие отбора без использования построителя выражений.

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

В выражениях для условий отбора допускается использование символов шаблона. Символами шаблона являются звездочка (*), знак вопроса (?), знак номера (#), восклицательный знак (!), дефис (-) и квадратные скобки ([]). Эти символы можно использовать в запросах, командах и выражениях для включения всех записей, имен файлов или других элементов, которые начинаются с определенной последовательности букв или удовлетворяют указанному шаблону. Назначение и примеры использования символов шаблонов приведены в таблице 1. При вводе шаблонов можно использовать как прописные, так и строчные буквы. Например, шаблон "ст*" эквивалентен шаблону "Ст*".

Таблица 1

Символы шаблона

Символ

Назначение

Пример

Результат отбора

*

Заменяет любую группу символов; может быть первым или последним символом в шаблоне.

ст*

*иск

"стол", "станок" и т.п.

"иск", "диск", "риск" и т.п.

?

Заменяет любой один символ.

ко?а

"кора", "коса", "коза" и т.п.

#

Заменяет любую одну цифру.

5#4

504, 554, 514 и т.п.

[]

Заменяет любой один символ, указанный в скобках.

ко[рс]а

"кора" и "коса", но не коза

!

Заменяет любой один символ, кроме символов, указанных в скобках.

ко[!рс]а

"коза" и "кожа", но не "кора" и "коса"

-

Заменяет любой один символ из указанного диапазона.

ко[к-м]а

"кока", "кола" и "кома"

После завершения ввода выражения в ячейку строки Условие отбора (например, нажатием клавиши Enter, клавиш управления курсором или щелчком мыши в другой ячейке) выполняется синтаксический анализ этого выражения и выражение приводится в соответствие с правилами синтаксиса MSAccess. Например, если введено слово Москва, то добавляются прямые кавычки и это слово выводится как "Москва".

Если выражение не содержит оператор, то подразумевается оператор равняется  (=). Например, если в ячейку Условие отбора для поля Город введено слово Москва, то выражение интерпретируется как Город = "Москва".

Задание

1. Выведите список книг, цена которых находится в диапазоне от 20 до 30 тыс. рублей. Динамический набор этого запроса должен содержать поля: Автор, Название, Год издания, Стоимость. Для задания условия отбора вначале используйте оператор Between … And, а затем операторы  >=, <=, And. Записи в динамическом наборе расположите по возрастанию цены книг. Сохраните первый запрос под именем Цена книг из диапазона, а второй – под именем Операторы сравнения для поиска цены.

2. Выведите список читателей, у которых нет домашнего телефона. В список включите следующие поля: Фамилия, Имя, Отчество, Домашний адрес. Список рассортируйте в алфавитном порядке по фамилии, имени и отчеству. Для поиска требуемых записей в строке Условие отбора для поля Домашний телефон используйте выражение IsNull. Это выражение предназначено для поиска записей, у которых поле не содержит значение (является пустым). Если требуется отобрать записи, у которых поле имеет значение, то можно использовать выражение IsNotNull. Запрос сохраните под именем Читатели без домашних телефонов.

3. Выведите список читателей, у которых в домашнем телефоне вторая цифра есть 5 или 6. В динамический набор включите поля: Фамилия, Имя, Отчество, Домашний телефон. Условие отбора для поля может иметь следующий вид: Like "?[56]*".

Запрос сохраните под именем Использование символов шаблона. Измените условие отбора предыдущего запроса так, чтобы он выводил список всех читателей, в номерах телефонов которых вторая цифра не 5 и не 6. Полученный запрос сохраните под именем Символ отрицания в квадратных скобках.

4. Создайте запрос, который будет выводить список книг, купленных библиотекой в 2012 году. В динамический набор включите следующие поля: Автор, Название, Наименование, Город. Для решения задачи вначале используйте функцию DatePart(interval; date; firstweekday; firstweek), а затем Format(expr; fmt; firstweekday; firstweek). Запросы сохраните под именами Использование функции  DatePart и Использование функции  Format соответственно.

5. В таблицу Выдача книг базы данных Библиотека добавьте поле Дата возврата. В это поле  для записей, приведенных в таблице 2, введите даты возврата. В остальных записях поле Дата возврата должно остаться пустым.

Таблица 2

Учет возврата книг

Код читателя

Код книги

Дата заказа

Дата возврата

1

1

01.09.2007

15.10.2007

1

3

05.07.2008

23.09.2008

4

3

07.01.2008

02.03.2008

5

2

23.04.2008

03.05.2008

7

3

20.01.2008

11.04.2008

9

6

02.02.2008

03.03.2008

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