Лабораторная работа №3. Проектирование запросов для управления данными

Цели:

• получить навыки конструирования запросов различного типа для выборки данных из реляционных таблиц и управления данными;

• освоить технологию создания запросов QBE;

• изучить основные конструкции реляционного языка запросов SQL.

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

Общие сведения о запросах ms Access

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

Для решения этой задачи предназначен механизм запросов. Этот механизм является стандартным и применяется почти одинаково во всех (или, во всяком случае, подавляющем большинстве) СУБД реляционного типа. Он представляет собой набор команд на языке SQL, определяющих критерий отбора записей в реляционной таблице. Таким образом, чтобы получить необходимую информацию из базы данных, следует записать соответствующие команды на языке SQL или, иначе говоря, - сформировать запрос.

Многие СУБД обладают механизмом автоматизации проектирования запросов. Чаще всего запрос формируется на специальном бланке. Такой метод формирования запроса называется QBE (Query By Example -Запрос по образцу). В MS Access процесс создания запроса подобен процессу создания таблиц Для того, чтобы начать проектирование нового запроса, необходимо перейти на вкладку «Создание».

На этой вкладке в будет предложено выбрать один из вариантов создания запроса.

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

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

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

Если в образец запроса необходимо поместить все поля из таблицы, то можно одним из вышеуказанных способов поместить в бланк символ * («звездочка»).

Если запрос имеет достаточно сложную структуру, то его бывает затруднительно создать в режиме конструктора (например запросы с объединением невозможно создать в режиме конструктора) то можно перейти в режим SQL и редактировать непосредственно SQL запрос. Для этого необходимо выбрать на панели инструментов РежимРежим SQL.

Язык запросов в языке SQL представлен одним оператором: Select. Рассмотрим его подробнее.

Назначение оператора Select сосоит в выборке и отображении данных одной или нескольких таблиц БД. Это наиболее используемый оператор реализующий все операции реляционной алгебры. Один и тот же запрос может быть реализован разными способами, которые могут существенно отличаться по времени выполнения.

Синтаксис

SELECT [DISTINCT] [* или <список полей>]

FROM <список таблиц>

[WHERE <предикат- условие выборки или соединения>]

[GROUP BY <список полей результата>]

[HAVING <предикат- условие для группы>]

[ORDER BY <список полей, по которым требуется упорядочить результат>]

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

К обязательным относятся только select и from.

Рассмотрим каждую фразу:

• Ключевое слово DISTINCT предназначено для приведения таблицы в соответствие с принципами теории отношений, что предполагает отсутствие повторяющихся строк.

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

• Во фразе FROM задается перечень исходных таблиц.

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

1. Сравнения =,<>,>,<,>=,<= для сравнения результатов вычисления двух выражений. Более сложные выражения строятся с помощью логических операторов AND, OR, NOT.

2. BETWEEN A AND B –предикат истинен, когда значение попадает в заданный диапазон. (NOT BETWEEN A AND B –когда не попадает).

3. IN – истинен когда сравниваемое значение входит в множество заданных значений; при этом множество значений может быть задано простым перечислением или встроенным подзапросом (NOT IN- когда значение не входит во множество).

4. LIKE (NOT LIKE)- предикаты смысл которых противоположен, требуют задания шаблона с которым сравнивается заданное значение.

5. IS NULL - предикат применяется для выявления равенства значения атрибута неопределенному значению (IS NOT NULL –для выявления неравенства).

6. EXISTS (NOT EXISTS) – используется во встроенных подзапросах.

• Во фразе GROUP BY задается список полей группировки.

• Во фразе HAVING задаются предикаты- условия, накладываемые на каждую группу.

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

Пример:

SELECT [Адрес электронной почты], Организация

FROM Контакты

WHERE Город="Ростов";

Этот оператор SQL следует читать так: «Выбрать данные из полей "Адрес электронной почты“ и "Компания“ таблицы "Контакты“, а именно те записи, в которых поле "Город“ имеет значение "Ростов“». Квадратные скобки в данном примере означают что имя поля содержит пробелы.

Агрегатные функции.

• COUNT –возвращает количество значений в указанном столбце. Осуществляет подсчет независимо от значений столбца.

• SUM - возвращает сумму значений в указанном столбце.

• AVG - возвращает среднее значение в указанном столбце.

• MIN – возвращает минимальное значение в указанном столбце.

• MAX- возвращает максимальное значение в указанном столбце.

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

Пример: Подсчитать и вывести количество кафедр в университете (информация о кафедрах хранится в таблице kafedry).

Select count(*) from kafedry;

Предположим есть отношение Успеваемость(№зач_книжки, дисциплина, оценка)

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

Select Дисциплина, count(Дисциплина) as “Количество сдавших студентов”

From Успеваемость

Group by Дисциплина;

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

Рассмотрим еще один пример. В условиях предыдущего запроса вывести информацию только о тех дисциплинах, количество сдавших экзамен по которым превосходит 3.

Select Дисциплина, count(Дисциплина) as “Количество сдавших студентов”

From Успеваемость

Group by Дисциплина

Having count(Дисциплина)>3;

Важно отметить, что в запросах без group by некоторые агрегатные функции могут применяться в списке выборки только в сочетании с другими агрегатными функциями. Другими словами если group by не предусмотрено, то оператор выборки не содержать агрегатные функции, парные по отношению к именам столбцов в списке выборки. Например если нет group by функция max может применяться только в сочетании с функциями но не с определенным столбцом.

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