Лабораторный практикум по курсу “СУБД” для студентов гуманитарных факультетов (стр. 4 )

·  Значения в значениях первичного и внешнего ключа соответствуют

·  В главной таблице в поле первичного ключа значение есть, а в подчиненной таблице записей связанных с данной в главной таблице нет.

·  В подчиненной таблице в поле внешнего ключа значение есть, а в поле первичного ключа это значение отсутствуют(это очень плохо!)

Задание 1. Создайте новую базу данных с двумя таблицами Преподаватели и Должности (содержание приведено ниже). Таблицы создайте методом ввода данных.

1.  Определите, каким типом связи связаны эти две таблицы

2.  Какая таблица главная и какая подчиненная в этой связи

3.  Выполните разными способами объединение двух таблиц

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

Таблица Преподаватели

НомерПреподавателя	Фамилия	НомерДолжности
11	Новицкий	4
21	Сухов	3
31	Леонтьева	3
41	Петров	2
51	Ласточкин	2
61	Водочкина	7

Таблица Должности

НомерДолжности	Должность
1	Преподаватель
2	Доцент
3	Аспирант
4	Профессор
5	декан

Лабораторная работа № 7.

Тема: Фильтрация данных.

В Access предусмотрено такое свойство таблицы, как фильтр.

Фильтр - это специальный механизм,

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

Вариантом фильтра может являться, например, такое условие:

"из таблицы Магазины надо получить информацию о магазинах по названию Абрикос".

Формально, это условие можно записать так,

Tables![Магазины]![Название] = “Абрикос”

Название столбца ограничивающее условие

Простые условия задаются таким образом.

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

Фильтры строятся на основе задания условий. Как строится простое условие, мы рассмотрели выше. Сложное условие строится путем соединения простых условий логическими операторами.

Логические операторы (And, Or, is, Not, Between и Like) возвращают в качестве результата одно из значений "Истина" (True), "Ложь" (False) или пустое значение (Null), если результат вычислить невозможно. Логические операторы используются для создания сложных условий.

Пример.

(Tables![Магазины]![Название] = “Абрикос”) And (Tables![Магазины]!Адрес Like “*Фрунзе*”)

В условиях мы используем выражения. Во втором условии использован оператор Like

Like – оператор, полезный при поиске образцов в текстовых полях. В образец поиска вы можете включать символы шаблона.

? – задание любого одиночного символа в данной позиции

* - задание любого количества символов в данной позиции

# - указывает, что в данной позиции должна быть цифра

[] - допустимый диапазон заключается в квадратные скобки

символ ! используется для указания исключений [!0-9] – любой символ, за исключением цифр от 0 до 9

Примеры использования Like:

[0-9]- цифра в данной позиции

Like “C*” - строка начинается с “С”

Like “*ова” - строка заканчивается на “ова”

Like “[A-D]*” – строка начинается с одной из букв от A до D

Like “*тр*” - внутри строки есть сочетание букв тр

Like “?[a-k]d[0-9]*” – проверяет наличие произвольного символа в первой позиции, a-k во второй, d в третьей, цифра в четвертой

Выражения могут быть арифметическими, логическими, типа дата – в зависимости, от того операнды, какого типа входят в выражение и каков тип результата.

Арифметические выражения используются для вычисления различных величин и содержат, по крайней мере, один оператор (например, арифметические операторы, и т. д.). Чаще всего используются арифметические операторы +, -, * (оператор умножения), / (оператор деления).

Величины, к которым применяется оператор, называются операндами. Различают левый и правый операнд оператора. Например, в выражении 10+12 число 10 является левым операндом оператора сложения, а число 12 правым операндом. В данном случае, выражение является числовым, так как результат этого выражения является числовым типом.

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

Существуют операторы, позволяющие сравнивать значения своих операндов. Эти операторы называются операторами сравнения (например, > (знак больше), = (знак равенства) и т. д).

Оператор & используется для слияния (конкатенации) двух строк в одну.

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

В Access есть еще несколько специальных операторов BETWEEN и IN.

Between – позволяет задавать диапазон значений, например,

IN – позволяет задавать используемый для сравнения список значений:

Примеры.

Between 10 and 20.

IN (“Томск”; “Асино”;”Колпашево”;”Стрежевой”)

На панели инструментов есть группа инструментов, использующихся для фильтрации данных

Первый элемент позволяет вызывать Фильтр по выделенному

Второй элемент позволяет перейти в режим изменения фильтра Фильтр по форме

Третий инструмент действует как переключатель Применить фильтр/Удалить фильтр. На рисунке он не активен (для таблицы нет заданного фильтра)

В Access используется несколько способов фильтрации данных:

-  Фильтр по выделенному

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

Вызывается из контекстного меню или меню Записи -> Фильтр или кнопка на панели инструментов

-  Фильтр по исключенному

Способ отбора записей в форме, объекте в режиме таблицы или на странице доступа к данным, при котором отбираются, не содержащие выделенное значение.

Вызывается из контекстного меню или меню Записи -> Фильтр

-  Обычный фильтр (иногда называют фильтр по форме).

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

Вызывается опцией Изменить фильтр в меню Записи-> Фильтр или кнопка на панели инструментов

-  Фильтр для (Ввод данных для фильтра).

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

Вызывается из контекстного меню.

-  Расширенный фильтр

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

Если вы находитесь в режиме задания фильтра (обычный фильтр или расширенный фильтр)

на панели инструментов появляются следующие кнопки:

Выбор между типами фильтров:

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

·  Фильтр по форме (обычный фильтр) используется для выбора искомых значений из списка без просмотра всех записей или при указании нескольких условий отбора одновременно.

·  Поле «Фильтр для» используется, если фокус ввода находится в поле и нужно ввести конкретное искомое значение или выражение, результат которого будет использоваться в качестве условия отбора.

Для создания сложных фильтров следует использовать окно расширенного фильтра:

·  Поиск записей, удовлетворяющих нескольким условиям.

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

·  Ввод выражений в качестве условий.

Примечание.  После определения обычного фильтра, фильтра по выделенному фрагменту или через поле «Фильтр для», переход в окно расширенного фильтра позволяет увидеть, как указанные значения преобразуются в выражение в условиях отбора в бланке запроса.

Если таблица закрывается при условии действия фильтра, фильтр записывается в свойства таблицы и при повторном открытии можно выполнить операцию “Применить фильтр”.

Задать следующие фильтры:

Используя фильтр по выделенному:

1.  Найти магазины по названию “Копеечка”

2.  Найти магазины, у которых название начинается на “Ю”;

3.  Найти все магазины “Кировского района”;

4.  Найти все магазины, фамилия директора у которого Рубцов.

Режим ФИЛЬТР ПО ВЫДЕЛЕННОМУ. Немедленно отфильтрует записи, сравнивая их с содержимым того поля, в котором находился маркер ввода, то есть мигающая вертикальная черта, обозначающая место, в которое будет вставляться информация с клавиатуры. По окончанию процесса (зависящего от скорости конкретного компьютера и размеров таблицы) таблица переменит свой внешний вид, и, вместо всех записей, в ней останутся лишь итоги фильтрации. Полученный результат является лишь экранным, то есть на самом деле ни одной строки из таблицы никуда не пропало, и обладает всеми свойствами полноценной самостоятельной таблицы. Его можно продолжать фильтровать и сортировать уже по другому критерию.

Когда нужда в отфильтрованных данных отпала, таблице можно вернуть ее начальный вид. Для этого достаточно опять поместить маркер ввода в любую ячейку и в меню, выводимом правой клавишей мыши, выбрать режим УДАЛИТЬ ФИЛЬТР. Все вернется на свои места.

Используя фильтр по исключенному:

1.  Найти все продукты, тип которых не Мюсли.

2.  Найти все магазины, которые не располагаются в Кировском районе

Используя фильтр Для

1.  Определить все продукты, у которых калории превышают 300

Каким образом при помощи фильтра можно отобрать записи, соответствующие условию не строго, например, те, в которых калории превышают 300?

Действительно, обычный фильтр с подобной постановкой вопроса не справится, так как он оперирует строгим равенством. Однако на этот случай существует фильтр, который называется ФИЛЬТР ДЛЯ. В контекстно-зависимом меню непосредственно с названием этого фильтра расположено белое поле ввода того параметра, для которого, собственно, фильтр и будет применяться.

Для его применения, следует "попасть" маркером ввода в любую из ячеек интересующего вас столбца. Так как заданный вопрос касается калорий, то в данном примере маркер ввода размещается в любой ячейке столбца "Калории". Открыв контекстно-зависимое меню и выбрав режим ФИЛЬТР ДЛЯ, вам понадобится ввести в этом самом белом поле, которое расположено справа, интересующее вас условие. Для нашего примера это комбинация символов ">300". Как только вы закончите и нажмете на клавиатуре кнопку " ВВОД", фильтрация выполнится.

Используя обычный фильтр определить

1.  Найти магазины в Советском районе фамилия директоров, у которых начинается на “П”

2.  Найти все магазины, расположенные на улице Фрунзе или на Учебной с фамилией директора на букву А

3.  Найдите магазины, у которых телефон начинается на 24 и заканчивается на 0.

Используя расширенный фильтр найти

1.  Продукты, у которых количество белков > 10 и жиров > 10 и углеводов < 40

2.  Найдите заказы в таблице ПродуктыЗаказы, в которых Количество = 100 и процент скидки = 3

Лабораторная работа № 8.

Тема: Запросы в Access.

Запросы являются мощным средством обработки данных, хранимых в таблицах Access.

С помощью запросов можно

-  просматривать,

-  анализировать,

-  изменять данные.

Они также используются в качестве источника данных для форм и отчетов.

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

Запросы можно создавать самостоятельно и с помощью мастеров.

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

Запросы Access можно классифицировать следующим образом:

ПО СПОСОБУ ЗАДАНИЯ

ЗАПРОСЫ на QBE ЗАПРОСЫ на SQL

(Query by Examle) (Sequence Query Language)

Любой запрос на QBE может быть представлен на языке SQL.

Но, как правило, использование SQL оправдано в следующих случаях:

·  запросы на объединение

·  запросы к серверу

управляющие запросы подчиненные запросы

ЗАПРОСЫ МОГУТ ВКЛЮЧАТЬ ПАРАМЕТРЫ

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

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

ПО ТИПУ ОПЕРАЦИИ:

ЗАПРОСЫ НА ВЫБОРКУ ----ЗАПРОСЫ ДЕЙСТВИЯ

бывают следующих типов: ·  запрос на удаление обновление записей добавление записей создание новой таблицы

среди запросов на выборку выделяются специальные запросы на ГРУППИРОВАНИЕ ДАННЫХ, и специальный тип ПЕРЕКРЕСТНЫЕ ЗАПРОСЫ

ЗАПРОСЫ МОГУТ БЫТЬ ОДНОТАБЛИЧНЫМИ, ЛИБО МНОГОТАБЛИЧНЫМИ.

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

Примечание. Простые запросы на выборку практически не отличаются от фильтров. Более того, фильтры можно сохранять как запросы.

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

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

СОЗДАНИЕ ЗАПРОСОВ.

При создании запроса можно использовать либо мастер запросов, либо построить запрос с помощью конструктора.

Пример создания запроса с помощью мастера:

Рис. 1 Окно мастера построения запросов

Для создания нового запроса надо в окне базы данных выбрать вкладку Запросы и щелкнуть по кнопке <Создать>. Откроется окно "Новый запрос", вид которого представлен на рис. 2.

Рис. 2 Окно “Создать” новый запрос.

В окне вы выбираете один из пяти пунктов: Конструктор, Простой запрос, Перекрестный запрос, Повторяющиеся записи, Записи без подчиненных.

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

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

Перекрестный запрос – специальный запрос на группирование данных.

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

У вас может возникнуть вопрос: как создавать запросы с параметрами и запросы на изменение, если при создании запроса они явно не указаны?

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

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

Для доступа к запросам на изменение надо открыть пункт меню Запрос - в открывшемся списке вы увидите все виды запросов на изменение.

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

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

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

-  на физическом носителе информации (обычно это жесткий диск) требуется меньший объем пространства;

-  запрос может использовать обновленные версии любых записей, измененных со времени последнего запуска запроса.

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

!Для создания запросов перейдите на вкладку Запросы.

!Существуют два вида работы с запросами – режим конструктора и режим просмотра.

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

!Для сохранения запроса следует выполнить следующие действия :

- выполните команду Файл, Сохранить или щелкните по кнопке <Сохранить> на панели инструментов

- если вы впервые сохраняете запрос, введите новое имя запроса в диалоговом окне "Сохранение".

ЗАПРОСЫ НА QBE.

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

Сам бланк запрос представляет собой две части:

- в верхней части указываются таблицы, участвующие в запросе;

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

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

o  В первой строке бланка запроса Поле щелчком мыши вызвать появление кнопки списка и выбрать из списка нужное поле

o  Переместить с помощью мыши требуемое поле из списка полей таблицы в схеме данных запроса в первую строку бланка запроса

o  Дважды щелкнуть на имени поля таблицы в схеме данных запроса

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

ЗАДАНИЯ.

ОДНОТАБЛИЧНЫЕ ЗАПРОСЫ НА ВЫБОРКУ.

Создайте в режиме мастера:

1.  На основе таблицы Магазины создайте простой запрос на выборку, в котором должны отображаться Название магазина, Название улицы и Фамилия директора. Завершите построение запроса опцией “Открыть запрос для просмотра”. Сохраните запрос. После просмотра результата, перейдите в режим Конструктора и посмотрите, как выглядит этот запрос. Данные запроса отсортируйте по Фамилии директора и еще раз посмотрите на таблицу, получающуюся в результате запроса.

Создайте в режиме Конструктора:

1.  На основе таблицы Продукты создать запрос для получения информации о Названиях продуктов с калорийностью между 200 и 350 (Выражение Between 200 and 350).

2.  На основе таблицы Магазины – список магазинов, которые расположены на проспектах Кировского района.

3.  Найти Заказы, у которых ДатаНазначения - январь (Like “*.01.*).

ЗАПРОСЫ С ВЫЧИСЛЯЕМЫМИ ПОЛЯМИ.

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

Имя: Выражение

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

Познакомьтесь с некоторыми функциями:

Название	Описание	Пример
Date()	функция возвращает текущую дату
DAY (дата)	возвращает значение дня месяца в диапазоне от 1 до 31	Day(ДатаРазмещения) – день даты размещения, Если хотите отобрать заказы третьей декады – Day(ДатаРазмещения)>20
Month(дата)	возвращает значение месяца в диапазоне от 1 до 12	Month(ДатаРазмещения)
Year(дата)	возвращает значения года
Weekday(дата)	возвращает значение дня недели
DatePart("параметр", дата)	функция позволяет получить из даты любую часть, первый параметр задает тип того, что хотим получить “YYYY” – год “Q” – квартал “WW” – номер недели в году “M” - месяц “Y” – день года “D” - день “W” день недели	DatePart("M", [ДатаРазмещения]) – возвратит месяц даты размещения
DateAdd("параметр", число, дата)	Функция позволяет прибавить к дате временной интервал и получить новую дату. Типы временного интервала, такие же как у функции DatePart	DateAdd("d"; 10, ДатаРазмещения) - вернет дату на 10 дней большую, чем дата размещения
DateDiff("d", дата1, дата2)	Функция позволяет вычислить разность двух дат. Результат вычисляется во временном интервале, заданном первым параметром	DateDiff("d"; [ДатаРазмещения]; [ДатаИсполнения])
Mounth()	Функция вычисляет номер месяца	Month(ДатаРазмещения)- вернет номер месяца даты размещения заказа
MounthName(число)	Возвращает имя месяца, параметр должен иметь числовое значение	MounthName(Month(Date())) – название месяца из текущей даты
WeekDay(дата)	Возвращает номер дня недели	WeekDay(Date()) – возвращает номер дня недели из даты
WeekDayName(число)	Возвращает имя дня недели	WeekDayName(WeekDay(Date())) – название дня недели из текущей даты

Для обращения к функции запишите ее имя, а в круглых скобках задайте ее параметры. Для каждой функции свой набор параметров!