КПпоПБД №3-4. Лабораторная работа 3 Создание базы данных

• Название: Лабораторная работа 3 Создание базы данных
• Дата: 08.04.2022
• Размер: 0.84 Mb.
• Имя файла: КПпоПБД №3-4.doc
• Тип: Лабораторная работа; #455657
• страница: 3 из 4

3. Выборка данных в СУБД Access

Фильтрация данных

В СУБД Access предусмотрено несколько механизмов выборки необходимой информации из таблиц БД. Простейшим из них является фильтрация, направленная на отбор группы записей, удовлетворяющих некоторому условию. В СУБД Access существует несколько разновидностей фильтров:

0. 
   Фильтр по выделенному. В режиме просмотра таблицы в некотором поле выделяется значение, которое является условием отбора, например, название факультета в таблице описания студентов вуза. Далее нажимается пиктограмма Фильтр по выделенному на панели инструментов. Все записи таблицы, содержащие выделенное значение, отобразятся на экране. Остальные будут скрыты.
   

Удаление фильтра осуществляется отжатием утопленной кнопки.

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

0. 
   Фильтр по исключенному выделенному. Выделяется значение, используемое в качестве критерия фильтрации, и нажатием правой кнопки мыши вызывается контекстное меню, в котором выбирается опция Исключить выделенное.
   
1. 
   Фильтр по сложному критерию. При фильтрации условия отбора записей могут включать логические операции И и ИЛИ. Для реализации операции ИЛИ необходимо произвести фильтрацию по первому значению, затем нажать кнопку Изменить фильтр на панели инструментов, что приведет к открытию окна диалога по настройке фильтра. Внизу окна находятся ярлыки вкладок. На первой из них уже указано в качестве отбора первое значение. Необходимо перейти на вкладку ИЛИ и, нажав кнопку раскрытия, выбрать следующее значение. Далее нажимается кнопка Применить фильтр .Очевидно, что можно выбрать несколько значений, связанных операцией ИЛИ.
   

Операция И реализуется между значениями различных полей, например, надо выбрать из таблицы СТУДЕНТ женатых студентов определенного факультета. Для этого производят фильтрацию по первому значению, например, факультету, а затем результат фильтруется по второму значению, например, семейному положению.

Запросы на выборку

Вопросы, которые формируются средствами СУБД к одной или нескольким таблицам, называются запросами. Существуют запросы на выборку и запросы, порождающие какое-либо действие: создание новой таблицы, удаление записей в таблице, обновление данных и т.д. В данной лабораторной работе мы познакомимся с созданием запросов на выборку. Они выполняют отбор требуемых пользователю данных, не изменяя содержимого исходных таблиц, а результаты отбора размещают в оперативной памяти. Отобранные данные можно просмотреть на экране, вывести на печать. Чаще всего отобранные запросом данные являются основой для построения отчётов.

Подготовка запросов с помощью мастера

С помощью запроса можно указать:

1. 
   какие поля вы хотите просмотреть;
   
2. 
   в каком порядке должны быть расположены поля;
   
3. 
   критерии фильтрации для каждого поля;
   
4. 
   порядок сортировки каждого поля.
   

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

Конструктор запросов

Для корректировки запроса, созданного мастером, необходимо выполнить следующие действия:

0. 
   Выбрать объект Запросы.
   
1. 
   Выделить запрос, который требует корректировки, затем щёлкнуть на кнопке Конструктор. Появится окно режима конструктора запроса (рис. 3.1.)
   

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

Перейти к объекту Запросы в окне базы данных и вызвать команду СОЗДАНИЕ ЗАПРОСА В РЕЖИМЕ КОНСТРУКТОРА.

Появится диалоговое окно "Добавление таблицы", в котором перечислены все таблицы базы данных.

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

Когда вы закончите добавление таблиц, щёлкните Закрыть. При этом откроется окно режима конструктора запроса.


Рис. 3.1 Окно конструктора запросов

Теперь необходимо добавить те поля, с которыми вам предстоит работать.

0. 
   Щёлкните строку Имя таблицы в первом пустом столбце. Появится стрелка, показывающая наличие спускающегося списка.
   
1. 
   В спускающемся списке выберите таблицу
   
2. 
   Щёлкните строку Поле прямо над именем таблицы, которую вы выбрали. Появится стрелка спускающегося списка.
   
3. 
   В этом списке выделите поле. Его имя появится в строке поле и будет расположено в том столбце, где вы его выделили.
   
4. 
   Добавление условий отбора
   
5. 
   Самое главное в запросе - возможность использования критериев выборки, которые вводятся в строку Условие отбора.
   
6. 
   С помощью условий отбора вы можете выбрать записи, которые будут появляться в результатах запроса. Для этого щёлкните строку Условие отбора в столбце нужного поля и введите условие отбора.
   
7. 
   Для формирования условий отбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ). Для этих же целей используется оператор диапазона Between <нижнее_значение> and <верхнее_значение>. Например, выбор книг стоимостью от 100 до 200 рублей может быть реализован через ввод в запросе условия в поле Стоимость в виде >=100 and <=200 или Between 100 and 200.
   
8. 
   Перечень значений в условии выборки можно задать и оператором In (значение, значение, ...). Например, выбор студентов факультетов МТФ или ФАПУ можно реализовать, указав в поле Факультет запроса условие In ("МТФ", "ФАПУ"). Это же условие можно записать и через операцию ИЛИ: "МТФ" or "ФАПУ". Также можно указать одно название факультета в строке Условие отбора (см. рис. 9.1), а второе - в следующей строке или. Число строк или не ограничено.
   
9. 
   Для выбора записей с пустыми значениями в некотором поле надо в соответствующем поле бланка запроса указать оператор Is Null. Наоборот, записи с непустыми значениями в данном поле выбираются по оператору Is not Null.
   
10. 
    В выражениях отбора также можно использовать знаки математических операций +, -, /, * и неограниченное число круглых скобок. Сложные выражения в условиях отбора могут формироваться с помощью соответствующего построителя, который вызывается кнопкой на панели инструментов.
    
11. 
    
    

Другой способ добавить поля:

0. 
   Просмотрите список полей в окне нужной вам таблицы, которое находится в верхней части окна конструктора запроса, и найдите в нём поле, которое вы хотите добавить.
   
1. 
   Щёлкните имя поля и перетащите его в строку Поле первого пустого столбца.
   

Чтобы добавить записи, которые...	Используйте это условие	Результат запроса
Точно соответствуют определенному значению, например "Китай"	"Китай"	Возвращает записи, в которых поле "СтранаРегион" содержит значение "Китай".
Не соответствуют определенному значению, например "Мексика"	Not "Мексика"	Возвращает записи, в которых значением поля "СтранаРегион" не является "Мексика".
Начинаются с заданной строки символов, например "С"	Like С*	Возвращает записи всех стран или регионов, названия которых начинаются с буквы "С", таких как Словакия и США. ПРИМЕЧАНИЕ : Символ "звездочка" (*) в выражении обозначает любую строку символов. Он также называется подстановочным знаком. Список таких знаков см. в статье Справочные сведения о подстановочных знаках в приложении Access.
Не начинаются с заданной строки символов, например "С"	Not Like С*	Возвращает записи всех стран или регионов, названия которых не начинаются с буквы "С".
Содержат заданную строку, например "Корея"	Like "*Корея*"	Возвращает записи всех стран или регионов, названия которых содержат строку "Корея".
Не содержат заданную строку, например "Корея"	Not Like "*Корея*"	Возвращает записи всех стран или регионов, названия которых не содержат строку "Корея".
Заканчиваются заданной строкой, например "ина"	Like "*ина"	Возвращает записи всех стран или регионов, названия которых заканчиваются на "ина", таких как "Украина" и "Аргентина".
Не заканчиваются заданной строкой, например "ина"	Not Like "*ина"	Возвращает записи всех стран или регионов, названия которых не заканчиваются на "ина", как в названиях "Украина" и "Аргентина".
Содержат пустые значения (или значения отсутствуют)	Is Null	Возвращает записи, в которых это поле не содержит значения.
Не содержат пустых значений	Is Not Null	Возвращает записи, в которых это поле содержит значение.
Содержат пустую строку	"" (прямые кавычки)	Возвращает записи, в которых поле имеет пустое значение (но не значение NULL). Например, записи о продажах другому отделу могут содержать пустое значение в поле "СтранаРегион".
Не содержат пустых строк	Not ""	Возвращает записи, в которых поле "СтранаРегион" имеет непустое значение.
Содержит нулевые значения или пустые строки	"" Or Is Null	Возвращает записи, в которых значение в поле отсутствует или является пустым.
Ненулевые и непустые	Is Not Null And Not ""	Возвращает записи, в которых поле "СтранаРегион" имеет непустое значение, не равное NULL.
При сортировке в алфавитном порядке следуют за определенным значением, например "Мексика"	>= "Мексика"	Возвращает записи с названиями стран и регионов, начиная с Мексики и до конца алфавита.
Входят в определенный диапазон, например от А до Г	Like "[А-Г]*"	Возвращает страны и регионы, названия которых начинается с букв от "А" до "Г".
Совпадают с одним из двух значений, например "Словакия" или "США"	"Словакия" Or "США"	Возвращает записи для США и Словакии.
Содержат одно из значений, указанных в списке	In("Франция", "Китай", "Германия", "Япония")	Возвращает записи всех стран или регионов, указанных в списке.
Содержат определенные знаки в заданном месте значения поля	Right([СтранаРегион], 1) = "а"	Возвращает записи всех стран или регионов, названия которых заканчиваются на букву "а".
Соответствуют заданной длине	Len([СтранаРегион]) > 10	Возвращает записи стран или регионов, длина названия которых превышает 10 символов.
Соответствуют заданному шаблону	Like "Лив??"	Возвращает записи стран или регионов, названия которых состоят из пяти символов и начинаются с "Лив", например Ливия и Ливан. ПРИМЕЧАНИЕ : Символы ? и _ в выражении обозначают один символ. Они также называются подстановочными знаками. Знак _ нельзя использовать в одном выражении с символом ?, а также с подстановочным знаком *. Вы можете использовать подстановочный знак _ в выражении, где есть подстановочный знак %.

Чтобы добавить условия в запрос, необходимо открыть его в Конструкторе. После этого следует определить поля, для которых вы хотите задать условия. Если поля еще нет на бланке запроса, добавьте его, перетащив его из окна конструктора запросов на сетку полей или дважды щелкнув поле (при этом поле автоматически добавляется в следующий пустой столбец в сетке). Наконец, введите условия в строку Условия.

Условия, заданные для разных полей в строке Условия, объединяются с помощью оператора AND. Другими словами, условия, заданные в полях "Город" и "ДатаРождения", интерпретируются следующим образом:

Город = "Воронеж" AND ДатаРождения < DateAdd (" гггг ", -40, Date())



1. Поля "Город" и "ДатаРождения" включают условия.

2. Этому условию соответствуют только записи, в которых поле "Город" имеет значение "Воронеж".

3. Этому условию соответствуют только записи людей, которым не менее 40 лет.

4. В результат будут включены только те записи, которые соответствуют обоим условиям.

Что делать, если требуется, чтобы выполнялось только одно из этих условий? Другими словами, как можно ввести альтернативные условия?

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



1. 1. Условие "Город" указывается в строке "Условие отбора".

2. 2. Условие "ДатаРождения" указывается в строке "или".

Условия, заданные в строках Условие отбора и или, объединяются с помощью оператора OR, как показано ниже.

Город = "Чикаго" OR ДатаРождения < DateAdd("гггг", -40, Date())

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

В таких запросах ввод условий отбора производится пользователем при каждом запуске запроса. Для организации запроса с параметрами необходимо в строке Условие отбора вместо самого условия в квадратных скобках ввести текст приглашения на его ввод:

[<текст приглашения>].

При запуске запроса с параметрами появляется диалоговое окно (рис. 3.2), в котором пользователь должен ввести условие отбора и нажать кнопку ОК.



Рис. 3.2

Вычисления в запросах

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

<Название формируемого поля>:<выражение>

В <выражении> можно использовать знаки арифметических операций, круглые скобки и имена полей в []. Например, стоимость партии можно вычислить по выражению:

Стоимость партии:[количество товара]*[стоимость единицы товара].

Если при вычислениях необходимо использовать данные из другой таблицы, то её имя указывается в префиксе, который отделяется от имени поля знаком !, например:

Стоимостьпартии:[количество_товара]*[Товар]![стоимость_единицы_ товара].

В этой формуле [Товар] – имя таблицы.

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

Для просмотра результатов запроса необходимо вызвать команду ЗАПРОС, ЗАПУСК или щёлкнуть на кнопке ЗАПУСК на панели инструментов. Результаты появятся в окне, похожем на таблицу.

Запросы с групповыми операциями

В СУБД Access существует возможность задать запросы с групповыми операциями, что позволяет находить интегральные показатели для групп записей в таблице. Каждая такая группа характеризуется одинаковым значением по какому-то полю, например, одинаковым названием отдела или семейным положением. Для перехода в данный режим запросов необходимо в панели инструментов нажать кнопку ГРУППОВЫЕ ОПЕРАЦИИ , что приведет к появлению в бланке запроса новой второй строки с одноименным названием. В ячейках данной строки указывается или режим группировки по некоторому полю (опция Группировка), или название групповой операции:

• 
  Sum - сумма значений;
  
• 
  Avg - среднее значение по данному полю для всей группы;
  
• 
  Count - число записей в данной группе;
  
• 
  Max - максимальное значение поля в каждой группе;
  
• 
  Min - минимальное значение поля в каждой группе;
  
• 
  First - первое значение данного поля в каждой группе;
  
• 
  Last - последнее значение данного поля в каждой группе и др.
  

При выполнении запроса СУБД разбивает таблицу на группы, число которых равно числу существующих значений в группируемом поле, и реализует для каждой группы требуемую операцию, т.е. число строк в выборке равно числу групп.

Задание № 3.1

В таблице СЛУЖАЩИЙ своей базы данных осуществить следующие виды фильтрации:

0. 
   выбор служащих одного отдела;
   
1. 
   выбор служащих, название должности которых заканчивается на букву "т";
   
2. 
   выбор служащих, фамилия которых начинается на букву "В";
   
3. 
   выбор всех служащих, кроме одного отдела;
   
4. 
   выбор служащих по двум должностям;
   
5. 
   выбор служащих-женщин одного отдела.
   

Задание № 3.2

Для таблицы СЛУЖАЩИЙ своей базы данных сформировать, проверить корректность и сохранить следующие запросы:

0. 
   Отображающий в выборке поля:
   

- ФИО;

- дата_рождения;

- отдел;

- оклад.

0. 
   Отображающий в выборке все поля таблицы для работников одного отдела. Сделать данный запрос параметризованным;
   
1. 
   Отображающий в выборке поля:
   

- ФИО;

- отдел;

- количество детей,

для семейных сотрудников, фамилии которых начинаются на буквы от "Г" до "Ш";

0. 
   Отображающий в выборке поля:
   

- ФИО;

- должность;

- оклад,

для холостых сотрудников с окладом больше 1000 руб.

0. 
   Рассчитывающий для каждого сотрудника налог в виде выражения (13% от оклада).
   

В выборке выводятся:

ФИО;

отдел;

оклад;

налог.

0. 
   Предусмотреть группировку данных по отделам и суммирование полей оклад и налог для каждого отдела (при этом результат не должен содержать фамилии служащих).