Базы данных в профессиональной деятельности государственных служащих Практическое занятие 1
1. Зачем нужна подстановка? Таблица Договор содержит поля Код_сотрудник и Код_подразделение, Код_должность, по которым осуществляется связь с таблицами Сотрудник, Подразделение, Должность. Значения кодов - это числа, что очень неудобно для восприятия и понимания. Для того чтобы в таблице Договор вместо кодов сотрудников и подразделений подставлялись их полные имена и названия из таблиц Сотрудник, Должность и Подразделение необходимо осуществить подстановку 2
Практическое задание Осуществите подстановку из таблицы Сотрудник фамилий и имен сотрудников в таблицу Договор 1. Откройте таблицу Договор в режиме Конструктора Это можно сделать, щелкнув правой кнопкой мыши на таблице Договор и выбрав из списка Конструктор 2. Установите указатель мыши на пункт Код_сотрудник и выберите вкладку Подстановка 3. В раскрывающемся списке выбрать Поле со списком 3
Практическое задание 4. В появившихся строках установить указатель мыши на Источник строк и щелкнуть на три точки 5. В окне Добавление таблицы выбрать таблицу Сотрудник, добавить ее в построитель запросов, закрыть окно 4
Практическое задание 6. Переместить три поля Код_сотрудник, Фамилия сотрудника, Имя сотрудника из верхней части построителя запросов в нижнюю Это можно сделать двойным щелчком левой кнопки мыши на каждом из полей 7. Закрыть окно построителя запросов и нажать кнопку Да 8. В строке Число столбцов заменить 1 на 3, в строке Ширина столбцов установить 0 5
Практическое задание Осуществите подстановку из таблицы Подразделение названий подразделений в таблицу Договор 7. Сохраните структуру таблицы Договор и закройте окно проектирования структуры таблицы 1. Установите указатель мыши на пункт Код_подразделение и выберите вкладку Подстановка 2. Выполнить пункты 3 и 4 со слайдов 3 -4 3. В окне Добавление таблицы выбрать таблицу Подразделение, добавить ее в построитель запросов, закрыть окно 4. Переместить два поля Код_подразделение, Название подразделения из верхней части построителя запросов в нижнюю 5. Закрыть окно построителя запросов и нажать кнопку Да 6. В строке Число столбцов заменить 1 на 2, в строке Ширина столбцов установить 0 6
Практическое задание Самостоятельно осуществите подстановку из таблицы Должность наименований должностей в таблицу Договор 7
2. Запросы в СУБД MSAccess 8
Что такое запросы? Главным объектом в базе данных MS Access являются таблицы, именно в них содержатся все данные о предметной области. Все остальные объекты являются производными от таблиц Запрос является производным объектом от таблицы. Результатом выполнения запроса является также таблица, т. е. запросы могут использоваться вместо таблиц. Запросы позволяют отобрать те записи, которые удовлетворяют заданным условиям. В процессе создания запроса можно отбирать не только записи, но и поля, которые будут присутствовать в запросе Запрос – объект базы данных, который позволяет проводить основные операции по обработке данных – сортировку, фильтрацию, объединение данных из разных источников – и сохранять результаты с некоторым именем, чтобы в дальнейшем применять эти операции по 9 мере необходимости
Типы запросов в СУБД MS Access Запросы в СУБД MS Access Запрос на выборку Запрос с параметром Запрос на изменение Перекрестный запрос Запрос на языке SQL 10
Типы запросов в СУБД MS Access Запрос на выборку обеспечивает отбор данных по определенному условию Запрос с параметром позволяет менять условие отбора в одном запросе Запрос на изменение меняет содержимое исходных таблиц Перекрестные запросы позволяют анализировать данные и создавать таблицу аналогичную таблице MS Excel со строками и столбцами Запросы на языке SQL позволяют пользователю сформулировать сложные по структуре критериев и вычислений запросы 11
Способы создания запросов в MS Access 1. В окне базы данных выбрать вкладку Запросы 2. Щелкнуть по кнопке Создать MS Access выведет окно Новый запрос с пятью вариантами создания запросов: Конструктор предназначен для самостоятельного создания нового запроса Простой запрос – для создания запроса на выборку из определенных полей Перекрестный запрос – создание запроса, выводящего данные в компактном формате, подобном формату электронной таблицы Повторяющиеся записи – создание запроса на поиск повторяющихся записей в простой таблице или запросе Записи без подчиненных – создание запроса на поиск записей, которым не соответствует ни одна запись в 12 подчиненной таблице
Запуск Конструктора запросов Самый удобный способ создания запросов – это создание запросов в режиме Конструктора 3. Выбрать пункт Конструктор, ОК 1. В окне базы данных выбрать вкладку Запросы 2. Щелкнуть по кнопке Создать 4. В окне Добавление таблицы кнопкой Добавить выбрать таблицы необходимые для запроса 13 5. Закрыть окно Добавление таблицы
Окно запроса Находятся таблицы, представленные в форме списков полей QBE – область проектирования (запрос по образцу) Здесь содержится спецификация запроса и запросы описываются наглядными элементами Поля перетаскиваются из верхней области запроса в QBE – область проектирования Информация о каждом поле в запросе 14
Практическое задание Создайте запрос на выборку: Вывести информацию о сотрудниках организации по подразделениям, где они работают Поля для запроса: Фамилия сотрудника, Наименование подразделения; Таблицы для запроса: Сотрудник, Договор, Подразделение 1. Запустить Конструктор 2. Поместите в окно запроса из окна Добавление таблицы Сотрудник, Договор, Подразделение 3. Установите указатель мыши на поле Фамилия сотрудника (верхняя часть окна запроса) и двойным щелчком переместите его в нижнюю часть окна 4. Проделайте действия, описанные в пункте 3 для поля Наименование подразделения 5. Сохраните созданный запрос под именем Сотрудники по подразделениям, закройте окно запроса Результат запроса 15
Практическое задание При создании запросов таблицу Договор необходимо добавлять всегда, так как это связующая таблица Сохранять запросы необходимо под именами, отражающими содержание запроса Можно выводить данные, отсортированные по какому-либо полю. Запрос будет выглядеть следующим образом Сортировка по возрастанию для поля Название подразделения 16
Практическое задание 1. Вывести информацию о должностях сотрудников в тех подразделениях, где они работают (Поля для запроса: определить самостоятельно; Таблицы для запроса: Сотрудник, Должность, Подразделение, Договор). 2. Вывести информацию, когда какой сотрудник был принят на работу в свое подразделение (Поля для запроса и Таблицы для запроса определить самостоятельно) 17
Практическое задание Создайте запросы с параметром: 1. Вывести информацию о сотрудниках мужского пола, абонентов сотовой сети МТС 2. Вывести информацию о сотрудниках-женщинах, с заработной платой свыше 20000 рублей 3. Вывести информацию о сотрудниках-пенсионерах с должностью «работник» 4. Вывести информацию о сотрудниках, принятых в Подразделение_1 за введенный с клавиатуры период 5. Вывести информацию о всех сотрудниках, работающих в подразделении, название которого вводится с клавиатуры 6. Вывести номер телефона, должность и подразделение по введенной с клавиатуры фамилии сотрудника 18
Как создать запросы с параметром? Запросы с параметром создаются на основе запросов на выборку, только лишь указывается определенное условие отбора Так, для Запроса 2. Вывести информацию о сотрудниках-женщинах, с заработной платой свыше 20000 рублей, Условие отбора: пол – Ж, заработная плата - >20000 В Конструкторе запрос будет выглядеть следующим образом: Условие отбора 19
Рекомендации по созданию запросов с параметром При создании условий отбора для запросов Вам может пригодиться информация: К Запросу 1: функция like” 8910*”, для отбора абонентов сети МТС К Запросу 3: выражение Возраст: Year(Now()) – Year([Дата рождения]) для отбора пенсионеров К Запросу 4: функция Between [Начало периода]and[Конец периода] для отбора данных за период, введенный с клавиатуры К Запросу 5 и 6: [Введите значение] для создания диалогового окна при отборе данных, удовлетворяющих условию вводимому с клавиатуры 20
Практическое задание Создайте итоговые запросы: 1. Определите сколько зарабатывают все мужчины 2. Посчитайте среднюю зарплату у женщин 3. По введенной фамилии сотрудника определить, сколько должностей он занимает 21
Как создать итоговые запросы? Итоговый запрос – это запрос на выборку, который предназначен для выполнения итоговых вычислений по данным Создание итогового запроса с помощью Конструктора: 1. Создать запрос на выборку 2. Щелкнуть по кнопке Групповые операции на панели инструментов 3. В бланке Запроса появится строка Групповые операции Во всех полях автоматически устанавливается значение группировка Созданный итоговый запрос к заданию 1 со слайда 20 22
Групповые операции в MS Access Sum – вычисляет сумму всех значений заданного поля в каждой группе (для числовых и денежных полей) Avq – вычисляет среднее арифметическое значений данного поля в каждой группе (для числовых и денежных полей) Min (Max) – возвращает наименьшее (наибольшее) значение, найденное в этом поле внутри каждой группы (для числовых, денежных и текстовых полей) Count – возвращает число записей, в которых данное поле имеет значение, отличное от Null. Для того чтобы подсчитать число записей каждой группы с учетом значения Null, надо ввести выражение Count(*) в строку поле, т. е. определить вычислимое поле (для числовых, текстовых, денежных полей, полей типа дата/время) Stdev – подсчитывает статистическое стандартное отклонение для всех значений в каждой группе данного поля Var – подсчитывает статистическое стандартное отклонение для всех значений в каждой группе данного поля First – возвращает первое значение поля в группе Last – возвращает последнее значение поля в группе Выражение – позволяет создать вычислимое поле с использованием итоговых функций Условие – позволяет создать в поле условия отбора по группам, если требуется в итоговый запрос включать не все записи 23
Спасибо за внимание! 24
Скачать презентацию Базы данных в профессиональной деятельности государственных служащих Практическое
BD_part2.ppt