Операторы, используемые в выражениях

Лабораторная работа №5

«Создание простых запросов в СУБД»

Цель работы: Научится создавать простые запросы в СУБД

Содержание отчета:

Цель, название работы

2. Создать запросы для таблиц, созданных в предыдущей лабораторной работе :

А. запрос на выборку (например, сделать вычисление в запросе при помощи функции SUM)

Б. параметрический запрос

В. перекрестный запрос

Г. запросы на изменение:На удаление записи, На обновление записи,На добавление записей, На создание таблицы.

Скриншоты всех созданных запросов.

Теория

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

По способу формирования запросы можно разделить на два вида:

запросы по образцу, или QBE-запросы (Query By Example), при создании которых необходимо указать параметры запроса в окне конструктора, задавая образцы для поиска информации;

структурированные запросы, или SQL-запросы (Structured Query Language), для создания которых необходимо описать запрос с помощью языка запросов SQL.

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

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

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

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

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

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

Каждая строка бланка запроса выполняет определенную функцию:

строка Поле используется для выбора полей;
строка Имя таблицы позволяет определить, какой таблице принадлежит поле;
в строке Сортировкауказывается тип сортировки записей;
строка Вывод на экран позволяет задать те поля, которые должны быть выведены в динамической таблице;
строка Условие отбора используется для ввода условия выбора данных.

Первым шагом построения запроса является выбор полей. Необходимые поля можно выбрать несколькими способами:

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

(кликнуть дважды ЛКМ на нужно поле для запроса)

Иногда требуется включить в запрос все поля исходной таблицы. Для этого необходимо сделать двойной щелчок мыши по строке заголовка соответствующего списка полей, выделяя таким образом сразу все поля, и перенести их одновременно в бланк запроса. При этом каждое поле будет помещено в отдельный столбец. Можно воспользоваться другим способом. В начале каждого списка полей, приведенных в верхней половине окна, находится символ *, который означает «все поля». Для того чтобы включить в запрос все поля таблицы, можно просто перенести * в бланк запроса. В этом случае имя поля в бланке запроса будет содержать имя таблицы, за которым следует точка, а затем — символ * (например, Заказы.*), что означает выбор всех полей исходной таблицы.

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

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

Обычно Microsoft Access выводит записи в том порядке, в каком они выбираются из базы данных. Можно изменить последовательность вывода данных, определив ее порядок в строке Сортировка. При сортировке по нескольким полям порядок обработки полей определяется их положением в бланке запроса: сначала сортируются значения в крайнем левом поле и далее слева направо.

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

Выражение, которое указывает, какие записи необходимо включить в динамическую таблицу при выполнении запроса вводится в строку Условие отбора для поля, по которому это условие необходимо проверить. Для задания условий отбора можно использовать операторы меньше (<), меньше или равно (<=), больше (>), больше или равно (>=), не равно (<>), равно (=), Like (выбор по маске), Between (между), In (в интервале), And(и), Or (или) и другие, а также имена обьектов, константы и функции.

Завершение ввода условия выполняется нажатием клавиши Enter или просто переходом к другой ячейке бланка запроса с помощью клавиш управления курсором или мыши. Мicrosoft Access проводит синтаксический анализ заданного выражения. Например, если было введено значение текстового поля то это выражение будет представлено в кавычках. Если выражение не содержит никакого оператора, Microsoft Access будет исходить из того, что подразумевается оператор = или Like.

Допускается использование нескольких условий отбора, которые можно задать как для разных полей, так и для одного поля. Для создания сложных условий выбора данных используются логические операторы And и Or. Если условия отбора связаны оператором And, запись выбирается только в случае выполнения всех условий. Если же условия отбора связаны оператором Or, запись выбирается при выполнении хотя бы одного из всех условий. При определении нескольких условий отбора, связанных оператором And, для различных полей необходимо просто задать условие в строке Условие отбора для каждого из полей, образующих критерий выбора данных. Если же при определении нескольких условий поместить их в различные строки — строку Условие отбораи строку или — Microsoft Acces будет использовать Or-связь. В результате условия, расположенные в одной строке, связываются оператором And, в разных строках — оператором Or.

Т.о., при формировании условия отбора в запросах могут использоваться:

Подстановочные символы:

Знак	Описание	Пример
*	Соответствует любому количеству знаков. Может использоваться в качестве первого или последнего знака текстовой строки	wh* — поискслов what, white и why.
?	Соответствует любому текстовому знаку	B?ll — поискслов Ball, Bell и Bill.
[ ]	Соответствует любому одному знаку из заключенных в скобки.	B[ae]ll — поиск слов Ball и Bell, но не Bill.
!	Соответствует любому одному знаку, кроме заключенных в скобки.	b[!ae]ll — поиск слов bill и bull, но не bell или ball.
-	Соответствует любому знаку из диапазона. Необходимо указывать этот диапазон по возрастанию (от A до Z, но не от Z до A).	b[a-c]d — поиск слов bad, bbd и bcd.

Операторы, используемые в выражениях

Арифметические

+	сложение	3+2
-	вычитание	3-2
*	умножение	3*2
/	деление	3/2
^	возведение в степень	2^3
\	целая часть от деления с остатком	3\2
mod	остаток от деления	3 mod 2

Логические

Not	отрицание	not "Siemens"
Or	лог. или	"Минск" Or "Брест"
And	лог. и	>3 and <5

Сравнения

<	меньше	<1000
<=	меньше либо равно	<=100
>	больше	>3 and <5
>=	больше либо равно	>=100
between...and...	между	between #02.02.04# and #05.02.05#

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

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

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

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

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

Выражение1: [Цена]*[Количество], где Цена и Количество — имена полей.

Имя вычисляемого поля — Выражение1 — становится заголовком столбца в таблице с результатами выполнения запроса. Это имя можно изменить.

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

Для создания вычисляемого выражения можно использовать построитель выражений.

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

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

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

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

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

Параметрические запросы

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

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

Пример:

В данном примере будут выведены все экскурсии которые будут проводится по музею 31.05.2015

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

2. Вводится дата во время которой происходит экскурсия.

3 Выводятся все экскурсии на данную дату.

Перекрестный запрос

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

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

1 При создании перекрестного запроса нужно выбрать в режиме конструктора перекрестный.

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

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

Запрос на изменение

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

На удаление записи.Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее.
На обновление записи.Запрос на обновление вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов поднимаются цены на все молочные продукты или на 5 процентов увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах.
На добавление записей.Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты».
На создание таблицы.Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создания архивной таблицы, содержащей старые записи.

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

Запрос на создание таблицы

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

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

1 При создании запроса на создание новой таблицы выбирается задается имя новой таблице и выбираются поля которые будут в этой таблице.

2 В новую таблицу будут занесены 3 записи «Выставки», «Зал», «Расписание».

3 Соглашаемся на создание новой таблицы.

4 Сверяем таблицу и внесенные в нее данные.

Запрос на обновление

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

Первоначально Запрос на обновление создается как Запрос на выборку, а затем в окне конструктора запросов превращается в запрос на обновление с помощью кнопки Вид запроса(Обновление) на панели инструментов или команды меню Запрос / Обновление. После выполнения этой команды в бланке запроса появляется строка Обновление.

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

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

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

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

Пример:

В данном запросе мы обновим количество детей в музее.

1 Выбираем в конструкторе таблиц обновление.

Выносим нужные нам таблицы для обновления и в обновлениях пишем изменения.

Соглашаемся с обновление записей.

Запрос на добавление

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

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

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

После выполнения этой команды в бланке запроса появляется строка Добавление.

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

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

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

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

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

Пример:

Добавим в таблицу зал ещё один зал.

1 Выбираем в конструкторе запросов добавление(при создании запроса на добавление не нужно выбирать таблицы).