КР Информационные системы в экономике. 1. Рынок информационных услуг. Правовое регулирование на информационном рынке

Название	1. Рынок информационных услуг. Правовое регулирование на информационном рынке
Анкор	КР Информационные системы в экономике
Дата	06.03.2023
Размер	1.49 Mb.
Формат файла
Имя файла	КР Информационные системы в экономике.docx
Тип	Документы #972494
страница	3 из 5

1 2 3 4 5

2.5.3 Внешнее объединение таблиц

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

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

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

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

Создать связь между таблицами А и В путем «перетаскивания» поля а1 на поле в1.
Двойным щелчком по линии объединения открыть окно Параметры объединения и установить нужный тип объединения (см. рис. 2.13).

Рисунок 2.13 - Окно Параметры объединения

Щелкнуть по кнопке ОК и добавить поля а1 и в1 в окно запроса (см. рис. 2.14)

Рисунок 2.14 - Запрос с левым внешним объединением

Щелкнуть по кнопке Запуск и выполнить запрос (см. рис. 2.15).

Рисунок 2.15 - Его результат

Для внешнего объединения Access добавляет стрелку в конце линии объединения. В нашем примере стрелка направлена слева направо от таблицы А к таблице В, а выбранный второй тип объединения называется левым внешним объединением.

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

Левому внешнему объединению (LEFT JOIN) в нашем примере соответствует инструкция SQL:

SELECT А.a1, В.b1 FROM А LEFT JOIN В ON А.a1 = В.b1;

а правому внешнему объединению (RIGHT JOIN) — инструкция SQL:

SELECT А.a1, В.b1 FROM А RIGHT JOIN В ON А.a1 = В.b1;

Отметим, что любой из описанных выше типов объединения таблиц может быть задан в окне Связи при определении связей между таблицами. Для этого следует щелкнуть по кнопке Объединение. Установленный тип связи будет использован по умолчанию Access при объединении таблиц в окне конструктора запросов [3, c.121].

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

Для этого следует добавить в окно конструктора две таблицы: Студенты и Общежитие, содержащие нужную информацию. Так как поле

Код студента является ключевым в таблице Студенты, между ними будет автоматически установлена связь по этому полю, имеющая внутренний тип объединения. Если оставить эту связь без изменения, то Access отберет лишь записи с информацией о студентах, живущих в общежитии. Нам же нужен полный список студентов, поэтому этот тип объединения следует изменить на тип объединения, отбирающий все записи из таблицы Студенты и соответствующие им записи из таблицы Общежитие.

Затем нужно включить в бланк запроса поля с нужной информацией и сохранить в БД. После выполнения запроса появится список всех студентов курса с их адресами (см. рис. 2.16). Для студентов, не живущих в общежитии, поля Общежитие и Адрес остаются пустыми (содержат значение Null).

Рисунок 2.16 - Запрос Адреса студентов

2.5.4 Использование мастера Записи без подчиненных

Созданный запрос можно легко превратить в запрос, который находит студентов, не живущих в общежитии. Для этого нужно лишь добавить в качестве условия отбора в поле Комната или Общежитие выражение Is Null. Для создания такого рода запросов можно также использовать мастера Записи без подчиненных.

Пример 2.5. Создадим с помощью этого мастера запрос, который находит студентов, не сдававших сессию.

Рисунок 2.17 - Запрос, созданный мастером Записи без подчиненных

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

Указать таблицу, не имеющую связанных записей с таблицей, указываемой в следующем пункте: Студенты.
Указать таблицу с подчиненными записями: Сессия.
Указать поля, используемые для связи таблиц: в обеих таблицах Код студента.
Выбрать поля, отображаемые в результате выполнения запроса: Группа, Фамилия, Имя.
Задать имя запроса: Студенты, не сдававшие сессию.

В результате выполнения созданного мастером запроса (см. рис. 2.17) на экране появятся сведения о студентах, не сдававших сессию (рис. 2.18).

Рисунок 2.18 - Студенты, не сдававшие сессию

2.5.5 Самообъединение

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

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

Рисунок 2.19 - Таблица Служащие

Рисунок 2.20 - Запрос, использующий самообъединение таблицы

Для создания этого запроса нужно включить в окно конструктора таблицу Служащие дважды. Второй раз она будет добавлена под именем Служащие-1, которое во избежание ошибок лучше заменить другим. Для этого нужно щелкнуть по копии таблицы правой кнопкой мыши, выбрать пункт Свойства, а затем в поле Псевдоним ввести текст Служащие_копия. Затем следует установить связь между таблицами, соединив поле Подчиняется таблицы Служащие с полем Код ее копии.

В качестве типа соединения лучше выбрать левое внешнее объединение, так как при внутреннем объединении в результат запроса не будет включена информация о самом главном начальнике. После этого следует включить в бланк запроса поле Сотрудник из обеих таблиц, изменив название этого поля из таблицы Служащие_копия на Подчиняется. Результат запроса представлен на рис.2.21.²

Рисунок 2.21 - Структура подчиненности

Самообъединение можно использовать и в других случаях.

Пример 2.8. Пусть нужно найти студентов, которые учатся в той же группе, что и студент Иванов (код 891294), и являются его тезками. Одним из возможных путей решения этой задачи является создание запроса, использующего самообъединение таблицы Студенты (см. рис. 2.16).³

Рисунок 2.22 - Еще один пример на самообъединение

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

Рисунок 2.23 - Тезки Иванова

2.5.6 Объединение таблиц по отношению неравенства

В большинстве случаев таблицы объединяются по условию равенства значений в полях связи, но допустимо объединение таблиц и по условию «неравенства». В этом случае условием связи может быть любой из операторов сравнения: >, >=, <, <=, <>.

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

Создать в окне конструктора связь типа равенства.
Перейти в режим SQL и заменить знак "=" другим оператором сравнения. Однако такой запрос уже нельзя представить в режиме конструктора.

Пример 2.9. Заменив знак "=" на "<>" можно создать запрос, объединяющий записи таблиц А и В с несовпадающими значениями в связанных полях. Его результат виден на рис. 2.24.

Рисунок 2.24- Связь по неравенству

Соответствующая инструкция SQL имеет вид:

SELECT А.a1, В.b1 FROM А INNER JOIN В ON А.a1 <> В.b1;

Можно поступить иначе: не связывать таблицы в окне конструктора, а записать условие на несовпадение значений полей в строке условий. В нашем примере это условие накладывается на значения в поле a1 и выглядит так: <> [b1].

Соответствующая инструкция SQL имеет вид

SELECT А.a1, В.b1 FROM А, В WHERE (((А.a1)<>[b1]));

2.6 Группировка записей в итоговом запросе

Для вычисления в запросе итоговых значений нужно щелкнуть по кнопке Групповые операции

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

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

Например, если включить в бланк запроса поле Группа таблицы Студенты, щелкнуть по кнопке Групповые операции и выполнить запрос, то Access выдаст на экран список номеров групп.⁴

Однако никаких итогов Access не подведет. Для выполнения этой операции ему нужна следующая информация:

по каким полям нужно подводить итоги;
какие итоги нужно подводить по данному полю.

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

Итоговым полем может быть как поле таблицы (запроса), находящейся в окне конструктора, так и специально созданное для этой цели вычисляемое поле. Для каждого итогового поля в строке Групповая операция должна быть указана статистическая функция, с помощью которой будет проводиться групповая операция — вычисление итогов в этом поле. Имя нужной функции (Sum, Avg, Max и т.д.) можно ввести в соответствующую ячейку в строке Групповая операция или выбрать из раскрывающегося списка.

Пример 2.10. Найти число студентов в каждой учебной группе.

Добавим в окно конструктора таблицу Студенты, затем щелкнем по кнопке Групповые операции и включим в бланк запроса поле Группа. Оно будет использовано для группировки записей (рис. 2.25).

Рисунок 2.25 - Бланк итогового запроса ( пример 5.21)

В качестве итогового поля выберем поле Код студента, а в качестве итоговой функции — функцию Count. Дадим итоговому полю имя Число студентов. Результат выполнения запроса представлен на рис. 2.26.

Рисунок 2.26 - Его результат

Для подсчета числа студентов можно было бы взять в качестве итогового поля любое другое поле таблицы Студенты. Единственное условие, которому оно должно удовлетворять, — отсутствие пустых значений, так как функция Count игнорирует записи со значением Null в итоговом поле. Это условие заведомо выполнено для поля Код студента, так как оно является ключевым. Если же ни для одного поля таблицы нельзя гарантировать отсутствие пустых значений, то в этом случае для подсчета числа всех записей в каждой группе нужно создать вычисляемое поле, введя в строку Поле выражение Count(*) (см. рис.2.30).

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

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

Для построения этого запроса добавим в окно конструктора таблицы Студенты и Сессия и щелкнем по кнопке Групповые операции. Затем включим в бланк запроса поля Код студента, Фамилия, Группа и Оценка. У первых трех полей оставим тип операции Группировка, а в поле Оценка выберем в качестве групповой операции функцию Sum и зададим тип сортировки по убыванию. Построенный запрос сохраним под именем Итоги сдачи сессии.

Группировку записей Access производит для динамического набора,

полученного в результате внутреннего объединения таблиц Студенты и Сессия. Фактически в качестве группового поля используется поле Код студента, задающее самое дробное разбиение на группы: в одну группу объединяются записи об оценках одного студента. Отметим, что даже если бы нам не нужно было выводить на экран код студента, это поле все равно необходимо включить в запрос в качестве группового. Иначе Access объединит в одну группу записи с оценками однофамильцев, и запрос выдаст неверный результат.

2.6.1 Отбор записей, формирующих группы

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

Пример 2.12. Определить число студентов в каждой учебной группе, родившихся в 1973 году или позднее.

Для этого нужно в бланк запроса из предыдущего примера (см. рис.2.25) включить дополнительно поле День рождения, выбрать в строке Групповая операция значение Условие и ввести условие отбора > 31.12.72. Access добавит к дате с двух сторон # и бланк запроса будет иметь вид, представленный на рис. 2.27.

Рисунок 2.27 - Отбор записей в группах

После добавления этого условия в Access включит в формируемые группы лишь те записи, которые относятся к студентам, родившимся позднее 31 декабря 1972 года.

Пример 2.13. Определить средние баллы по информатике девушек в каждой учебной группе.

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

Рисунок 2.28 - Бланк запроса к примеру 2.12

1 2 3 4 5