Занятие 2
 Скачать 6.79 Mb.
|
|
Пошаговые инструкции по изучению базы данных ACCESS. (Продолжение) Занятие №2 Запросы позволяют получать из базы данных только необходимые данные, давая возможность успешно решать задачи и получать ответы на вопросы. С помощью запросов можно отбирать, объединять, многократно использовать и анализировать данные, собранные в нескольких связанных между собой таблицах. При создании запроса используется язык SQL (структурированный язык запросов), который используется также для обновления и управления реляционными базами данных. 1Создание запросов на выборку.Перейти на вкладку Запросы.  Щелкнуть по кнопке Создать и выбрать конструктор для создания запроса. При этом на экране появятся два окна: окно конструктора запросов, на которое наложено окно добавления таблицы.  С помощью последнего окна выполнить операцию добавления таблицы Пациенты (предварительно выделив ее в списке таблиц), после этого закрыть окно добавления.  Изображение таблицы появится в верхней части конструктора в виде прямоугольного окна со списком полей таблицы. Если случайно появились лишние окна, их нужно убрать щелчком правой кнопки по таблице с последующим выбором пункта Удалить из контекстного меню.  В нижней части окна конструктора запросов располагается механизм формирования запроса. Он представляет собой таблицу, в столбцах которой следует расположить требуемые поля одной или нескольких таблиц, а в строках – следующие параметры: Поле – имя поля таблицы. Имя таблицы – имя таблицы, которой принадлежит поле. Сортировка – порядок сортировки записей в запросе – алфавитный или обратный, необязательное поле. Вывод на экран – флажок, управляющий выводом поля на экран при выполнении запроса. Поле с выключенным выводом на экран допускает сортировку и установку условий отбора, но после выполнения запроса в окне запроса это поле не будет отражено. Условие отбора – вводится условие отбора для вывода тех записей, которые удовлетворяют этим условиям.  Создать запрос на выборку пациентов, фамилии которых начинаются на определенную букву (например, на С), а также выбрать их дату рождения и место жительства.. Для этого заполнить первые три ячейки строки поле именами полей таблицы: ФИО, Дата рождения и Место жительства. Это можно сделать следующими способами: Отбуксировать имя поля из таблицы в ячейку строки Поле. Щелкнуть по ячейке в строке Поле, открыть выпадающий список при помощи кнопки со стрелкой, указывающей вниз и выбрать нужное поле. Выполнить двойной щелчок по нужному полю в таблице, находящейся в верхней части конструктора. При этом имя поля появится в ближайшей свободной ячейке. Не следует вводить имя поля с клавиатуры во избежание ошибок!!!!!! В строке Условие отбора в первой ячейке для поля ФИО зададим условие отбора С*
 В строке Условие отбора можно использовать некоторые символы, которые заменяют букву, группу букв, цифру: * - заменяет любое количество любых букв. ? - заменяет одну какую-либо букву. # - заменяет одну какую-либо цифру.  Выполнить запрос. Для этого можно не закрывая его щелкнуть по кнопке на панели инструментов с изображением восклицательного знака или выполнить пункт меню Запрос/Запуск.  После выполнения в окне запроса будут выведены все пациенты, фамилии которых начинаются на букву С , а также их даты рождения и место жительства. Закрыть запрос (Файл/Закрыть) и присвоить ему имя Фамилия. Создать запрос на выборку ФИО пациентов мужского пола в возрасте до 30 лет. Для этого заполнить первые три ячейки строки поле именами полей таблицы: ФИО, Пол и Возраст. Далее ввести условия отбора для полей Пол и Возраст. Схема запроса:
Выполнить запрос, посмотреть есть ли пациенты, удовлетворяющие этим 2 условиям. (если нет, то изменить в условии отбора для пола м на ж). Сохранить под именем Мужчины до 30лет.  Создать следующий запрос, выбрав те же поля ФИО, Пол и Возраст, но добавив еще одно условие отбора или:
Т.е. условие отбора для поля Пол перенесли на строку ниже Или (логическое И, которое дает возможность выполнения и того и другого условия).  После выполнения запроса обратить внимание на различие данных, полученных после выполнения этих двух запросов. (В данном запросе выводятся мужчины любого возраста и пациенты обоих полов, но моложе 30 лет, не выводятся только женщины старше 30 лет). Сохранить под именем И пол и возраст. Создать еще один запрос на выборку пациентов из 2-х связанных таблиц, которые были госпитализированы с определенным диагнозом (например:ОРЗ) и лечившихся у определенного врача (например: у Таблеткина). В окне добавления таблицы добавить таблицы Пациенты и Госпитализация.
Сохранить под именем Врач и Диагноз. Создать запрос Отбор по дате, который отбирает пациентов моложе 30 лет, принимавших определенное лекарство. (Добавляем 2 таблицы).
Сохранить под именем Отбор по дате моложе 30 лет. 2Создание запросов с параметрами.Запрос с параметрами дает возможность не изменять условие отбора для одного поля в конструкторе, создавая несколько запросов, а изменять это условие при запуске запроса. Т.е. при запуске на выполнение одного и того же запроса, задавая разные параметры в окне Введите значение параметра, будут выбираться разные данные, соответствующие тому параметру, который будет введен. Используя те же приемы, создать новый запрос по таблицам Пациенты и Госпитализация. Но в условии отбора ввести следующий текст, заключенный в квадратные скобки: [Введите фамилию врача].
 . При его выполнении вначале возникает диалоговое окно Введите значение параметра, в ячейку которого следует ввести одну из фамилий врача, и будут выведении данные на пациентов, которых он лечил. Преимущество в том, что не надо на каждого врача создавать запрос и в условии отбора вводить его фамилию. Убедиться в правильности выполнения запроса.  Сохранить запрос под именем Параметр - Врач. Создать еще один запрос с параметром, в качестве параметра используя диагноз.
Сохранить запрос под именем Параметр - Диагноз. 3Создание запросов на выборку с использованием групповых операций.Групповым называется такой запрос, в котором выполняется группировка данных по содержимому одного из полей таблицы, а для остальных включенных в запрос полей могут вычисляться значения, общие для каждой группы. Основные из них: Sum – сумма значений в группе Avg – среднее значение Min – минимальное значение Max – максимальное значение Count – число значений StDev – стандартное отклонение Var – дисперсия Создать новый запрос, добавив в него одну таблицу – Пациенты. Выбрать поля Пол и еще раз Пол (или можно выбрать поле, где тип данных – счетчик. Это поле - № пациента).  Установить групповой тип запроса. Для этого выполнить пункт меню Вид/Групповые операции (или щелкнуть по кнопке с изображением знака суммирования на панели инструментов). При этом в конструкторе запросов должна появиться строка Групповая операция, а в обоих полях в этой строке должно появиться название операции Группировка. Это название должно сохраниться в поле Пол, определяя тем самым, что именно по этому полю будет произведена группировка данных для вычисления задаваемых параметров, вместо группировки для второго поля Пол (или для поля № пациента) выбирать значение Count. 
 Выполнить запрос и получить данные по количеству мужчин и женщин. Сохранить под названием Количество мужчин и женщин. Создать новый запрос, добавив в него две таблицы – Госпитализация и Препараты. При помощи этого запроса подсчитать на какую сумму выписал лекарств каждый врач. Схема этого запроса выглядит так:
Сохранить под названием Сумма лекарств. Создать групповой запрос, который вычисляет средний возраст для мужчин и для женщин. 4Создание запросов на обновлениеЗапрос на обновление это такой тип запроса, с помощью которого можно изменить содержимое полей таблиц. Создать новый запрос по таблицам Пациенты, Госпитализация и Препараты. Включить в него то поле, которое будет обновляться - поле Оплата. Установить требуемый тип запроса. Для этого выполнить пункт меню Запрос/Обновление либо на панели инструментов нажать кнопку Тип запросов (на ней пиктограмма в виде двух таблиц). Выбрать тип запроса Обновление.  В появившейся в конструкторе строке Обновление для поля Оплата щелкнуть правой кнопкой мыши, вызвав контекстное меню и выбрать опцию Построить.  В верхнюю часть Построителя выражений, где моргает курсор, необходимо ввести выражение, которое будет просчитывать оплата за вычетом скидки. Для этого в левой нижней ячейке двойным щелчком открыть папку Таблицы, выбрать таблицу Препараты.    Раскрыть ее и двойным щелчком перенести Стоимость в верхнюю ячейку, нажать знак «-», который находится под этой ячейкой в числовом блоке (при вводе математических знаков и скобок пользоваться только этим числовым блоком). Еще раз перенести Стоимость, выбрать знак умножения, затем из таблицы Пациенты перенести двойным щелчком поле Скидка, выбрать знак деления и ввести с клавиатуры 100. (Стоимость - Стоимость * Скидка/100).Закрыть построитель, и выполнить запрос. При выполнении запроса Access предупредит, что будут обновлены данные в таблице.      Убедиться в правильности обновления можно только раскрыв таблицу Госпитализация и посмотрев поле Оплата. Сохранить запрос под именем Оплата. Создать новый запрос на обновление Возраста, включив в него поле, которое будет обновляться – поле Возраст. По аналогии с предыдущем запросом установить требуемый тип запроса. Для этого выполнить пункт меню Запрос/Обновление либо на панели инструментов нажать кнопку Тип запросов (на ней пиктограмма в виде двух таблиц). ). Выбрать тип запроса Обновление.   В появившейся в конструкторе строке Обновление для поля Возраст щелкнуть правой кнопкой мыши, вызвав контекстное меню и выбрать опцию Построить. Откроется окно Построителя выражений. В верхнюю часть Построителя выражений, где моргает курсор, необходимо ввести выражение: Year(Now()) – Year([Дата рождения])  Элементы этого выражения обозначают: Now() – встроенная функция, которая возвращает текущую дату, аргументов не имеет, но пустые круглые скобки обязательны; Year(…) – встроенная функция, в качестве аргумента требует данные в формате даты, возвращает содержащийся в дате год. Year(Now()) – вложенные функции: функция Now() возвращает текущую дату, которая становится аргументом функции Year(…), последняя возвращает выделенный из даты текущий год. Например, если запрос выполняется 25 сентября 2006 года, то результатом выполнения вложенных функций будет число 2006. Year([Дата рождения]) – аргументом функции Year(…) является значение поля таблицы [Дата рождения] (название поля задается в квадратных скобках). Для этого в левой нижней ячейке двойным щелчком открыть папку Функции, и найти папку Встроенные функции, раскрыть. В средней ячейке показаны все типы встроенных функций, выбрать тип Дата и время или Все, затем в правой ячейке найти функцию Year и двойным щелчком по ней перенести ее в верхнюю часть Построителя выражений.   Затем нажать на знак «-» и еще раз двойной щелчок по функции Year.  В верхней ячейке получено выражение: Year(«number») - Year(«number»), щелчком мыши в скобках первого Year(«number») выделить «number» и вместо его выбрать функцию Now() из третьей ячейки.  Вместо второго «number» выбрать из таблицы Пациенты поле Дата рождения.  Получено выражение: Year(Now()) – Year([Дата рождения]). Нажать кнопку ОК, выполнить запрос.  .  Выполнив запрос, согласиться с обновлением и, открыв таблицу Пациенты, проверить правильность изменений в поле Возраст. 5Создание запросов с помощью Построителя выражений.Создать запрос аналогичный предыдущему, рассчитывающий Возраст пациента, но только с помощью Построителя выражений.  Создать новый запрос по таблице Пациенты, включив в него поля Фамилия и Пол.   Определить новое поле, в котором будет вычисляться возраст пациента. Для этого в свободную ячейку (третью по счету) строки Поле щелкнуть правой клавишей мыши, вызвав контекстное меню и выбрать в нем опцию Построитель. Откроется окно Построителя выражений. В верхнюю часть Построителя выражений, где моргает курсор, необходимо ввести выражение: Year(Now()) – Year([Дата рождения]) Для этого в левой нижней ячейке двойным щелчком открыть папку Функции, и найти папку Встроенные функции, раскрыть. В средней ячейке показаны все типы встроенных функций, выбрать тип Дата и время или Все, затем в правой ячейке найти функцию Year и двойным щелчком по ней перенести ее в верхнюю ячейку. Затем нажать на знак «-» и еще раз двойной щелчок по функции Year. В верхней ячейке получено выражение: Year(«number») - Year(«number»), щелчком мыши в скобках первого Year(«number») выделить «number» и вместо его выбрать функцию Now() из третьей ячейки, вместо второго «number» выбрать из таблицы Пациенты поле Дата рождения. Получено выражение: Year(Now()) – Year([Дата рождения]). Нажать кнопку ОК, выполнить запрос.  В запросе поле, в котором просчитан возраст имеет название Выражение1, чтобы это исправить, надо вернуться в конструктор запроса и в ячейке, где прописано выражение, аккуратно вместо Выражение1 напечатать Возраст.  Выполнить запрос и убедиться в правильности вычислений для каждой записи. Сохранить запрос под именем Возраст пациентов. Создать новый запрос по таблицам Пациенты и Госпитализация, включив в него поля: ФИО, № карты, Врач и Диагноз, для выбора пациентов, которые пролежали в стационаре менее 8 дней.  Далее в свободную ячейку (пятую по счету) строки Поле щелкнуть правой клавишей мыши, вызвав контекстное меню и выбрать в нем опцию Построитель. Откроется окно Построителя выражений.  В верхнюю часть Построителя выражений, где моргает курсор, необходимо по аналогии с предыдущим запросом ввести выражение: Day([Дата выписки])-Day([Дата поступления]) Можно упростить: не использовать встроенную функцию Day(…), а сразу ввести [Дата выписки]- [Дата поступления]   Далее в конструкторе запроса в ячейке (пятой по счету) строки Условие отбора записать условие <10.  Выполнить запрос и убедиться в правильности вычислений. Сохранить запрос под именем Число дней.  6Построение диаграммПостроить диаграмму типа гистограммы, по запросу Количество мужчин и женщин, в которой данными являются количество пациентов, а по оси Х отложен пол пациентов. Перейти на вкладку Формы. Щелкнуть по кнопке Создать. В качестве источника данных выбрать из списка созданный ранее запрос Количество мужчин и женщин. Установить тип создаваемой формы Диаграмма и щелкнуть по кнопке ОК. После этого начинает работать мастер построения диаграмм.   На первом шаге мастера следует перенести названия обоих полей из списка Доступные поля в список Поля Диаграммы. По кнопке Далее перейти к следующему шагу.  На втором шаге выбрать простую гистограмму в качестве типа диаграммы.  На третьем шаге проверить установленные по умолчанию параметры диаграммы. По оси Х должен быть отложен пол, по оси Y – количество. Если это не так, то отбуксировать на нужные места названия полей, приведенные в правой части окна мастера.  На этом же шаге откорректировать (только, если это является необходимостью) вид вычислений, которые выполняются при построении диаграммы. Для этого нужно выполнить двойной щелчок по названию поля данных (обычно это Сумма_.....) и выбрать тот вид вычислений, который необходим. На четвертом шаге следует задать имя диаграммы, можно оставить приведенное по умолчанию.  После щелчка по кнопке Готово на экране появится диаграмма. Однако вид диаграммы, как правило, требует коррекции. Перейти в конструктор формы, выполнив пункт меню Вид/Конструктор.  На экране появится окно конструктора формы (серого цвета с черной сеткой) с наложенным на него окном диаграммы (белый фон с гистограммой и фрагментами надписей). Надписи типа 1, 2, 3 и столбцы A, B, C,D представляют общий условный вид таблицы данных для изображения диаграммы в Microsoft Graph, который можно отредактировать (убрать лишние столбцы – ряды данных, добавить необходимые, также поступить со строками, или внести изменения в числовые и текстовые данные) по своему усмотрению.. В данном случае надо оставить две строки, которые будут соответствовать полу – м и ж, и один столбец, который будет соответствовать количеству пациентов.  Для изменения цвета столбцов щелкнуть мышью по столбцу один раз – выделение обоих столбцов, еще щелчок – выделение только одного столбца, двойной щелчок на выделенный столбец – вызов в Microsoft Graph окна для редактирования цвета.    После изменения цвета, хотя бы у одного столбца, сразу в области диаграммы раскрывается легенда, где каждый цвет указывает на определенный пол  Выйти из конструктора диаграмм (Файл/Выход и возврат в Форма1). Затем выйти из конструктора форм (Вид/Режим формы). Убедиться, что все изменения правильны, в противном случае вернуться в конструктор формы, затем в конструктор диаграмм и повторить настройку. Закрыть, сохранив форматирование. 7Контрольное задание по запросам.Создать групповой запрос, который вычисляет средний возраст для мужчин и для женщин. Создать запрос с параметром, взяв в качестве параметра данные поля Дата поступления, а также в запросе должны быть выбраны поля: ФИО, дата рождения, врач и диагноз. Создать запрос, находящий какие лекарства были выписаны песионерам. Создать запрос на обновление данных в поле Скидка. Для мужчин и женщин пенсионного возраста установить скидку 50. (учитывая, что пенсионный возраст женщины – старше 55 лет, а пенсионный возраст мужчин – старше 60 лет). Создать запрос с помощью Построителя выражений, рассчитывающий какое количество дней пролежала в госпитале либо каждая женщина, либо каждый мужчина. Построить диаграмму по среднему возрасту мужчин и женщин. |